1、case表达式

select country_name,region_id, case region_id when 1 then 'europe' when 2 then 'america' when 3

then 'asia' else 'other' end continent from countries where country_name like 'I%';

在9i中提供的case表达式，可以在sql语句中达到if then else逻辑。

2、order by

order by子句中可以使用列名，列别名，或者列的位置。位置必须是select-list表达式的数目

select name，sex from v_table order by 2

3、oracle单行函数

通用函数

nvl(expr1,expr2) 判断第一个字符是否null,是显示expr2

nvl2(expr1,expr2,expr3) 判断第一字符是否null，是显示expr3,不是显示expr2

nullif(expr1,expr2)如果两个字符相等，则返回空，否则返回expr1

coalesce(expr1,expr2,...exprn)判断每个参数是否未空，直到不为空显示出来，如果都是空则报错

 

日期函数

to_number,to_date,

to_char(date,'format-model')

 

format-model:

{

yyyy full year in numbers

year year spelled out

mm two-digit value for month

month full name of the month

mon three-letter abbreviation of the month

dy three-letter abbreviation of the day of the week

day full name of the day of the week

dd numeric day of month

}

to_char(number,'format-model')

{

9 prepresents a number

0 forces a zero to be displayed

$ places a floating dollar sign

L 使用本地货币符号

. prints a decimal point

, prints a thousand indicator

}

 

months_between

add_months

next_day

last_day

round

trunc

 

字符函数

lower，upper，initcap(大小写转化函数)

initcap('SQL Cource') result is Sql Cource

 

concat，substr,length,instr,lpad|rpad,trim,replace

concat字符连接，等于||

substr截断字符串substr(column,1,5)

substr(column,-1,5)从右边开始取

length字符长度

instr字符所在的位置

trim压缩空格

lpad左填充

rpad右填充

数字函数

round四舍五入round(45.926,2)---45.93

trunc截取数字trunc(45.926,2)---45.92

mod求余数mod(1500,200)--100

转换函数

 

4、sql*plus

set wrap off

set linesize 1000

 

5、case when else end

6、decode简化的if then else

 

7、oracle中对层次结构数据的递归查询

create table Dept(

   DepartNO varchar2(10),

   DepartName varchar2(20),

   TopNo    varchar2(10));

 

insert into Dept values('001','董事会','0');

insert into Dept values('002','总裁办','001');

insert into Dept values('003','财务部','001');

insert into Dept values('004','市场部','002');

insert into Dept values('005','公关部','002');

insert into Dept values('006','销售部','002');

insert into Dept values('007','分销处','006');

insert into Dept values('008','业务拓展处','004');

insert into Dept values('009','销售科','007');

 

connect by prior    start with

例子：

select departname,level,sys_connect_by_path(departname,'|') from dept

connect by prior departno = topno

start with departname='董事会'

 

departname   level

市场部       2

公关部      2

销售部      2

或

select departname,level,sys_connect_by_path(departname,'|') from dept

where level=2

connect by departno =prior topno

start with departname='总裁办'

 

Sys_connect_by_path(x,c)x是列，从是单字符，适用层次化查询。

 

 

prior位置表示是往父亲节点搜索还是往子节点搜索

level表示搜索深度

 

8，insert with check option

9、插入的值显示的定义为default值

insert into departments(department_id,name)

values(300,default)

name值由表定义时的default值填充，强制用当前column定义的default值

10、merge语句，要不更新，要不插入

merge into table_name ....

merge into ttt ...

when metched then update set

...

when not matched then

 insert values....

11、隐式事务

没有提交的数据，别人不能修改

通过回滚段实现读一致性

语句级

 

 

 

12、rollup、cube等分析函数

select decode(cust_gender,'F','男','M','女') cust_gender,count(cust_gender)

from sh.customers

group by cust_gender

union select '合计'cust_gender,count(cust_gender) from sh.customers

order by cust_gender desc

普通的写法

select cust_gender gender,nvl(cust_marital_status,'unknow') marital_status,count(*),grouping(cust_gender),grouping(nvl(cust_marital_status,'unknow'))

from sh.customers

group by rollup(cust_gender,nvl(cust_marital_status,'unknow'))

rullup写法

 

 

13、rank、dense_rank函数

select department_id,rank(10000) within group(order by salary desc nulls last) dense_rank_10k

from hr.employees

group by department_id

判断10000在salary中排在第几，按dept分组

select department_id,salary,rank() over(partition by department_id order by salary desc) dense_rank_10k

from hr.employess列出在部门排第几

dence_rank在并列关系是，相关等级不会跳过。rank则跳过。

 

 

14、select length('HELLO WORLD!')-length(replace(translate('HELLO WORLD!','L',chr(13)),chr(13),'')) from dual;

判断字符串中的某个字符的个数

select length('HELLO WORLD!')-length(replace('HELLO WORLD!','L')) from dual;

更简洁

select (length('你好你好LLO WOR你好LD!')-length(replace('你好你好LLO WOR你好LD!','你好')))/length('你好') from dual;

可以同时处理汉字和英文

 

15 alter table add / modify cName

drop column cName

 

涉及到数据删除时所需要的时间，可以使用set unused使column无用，在系统空闲的时候可以drop column

 

delete删除时做很多日志记录，效率低下

truncate是ddl，不能rollback！速度效率都高，同时也清空索引和存储空间还原

comment on table tName is 'comment content';给对象增加注释

查看注释信息：all_col_comments,user_col_comments,all_tab_comments,user_tab_comments

 

16、约束 including constraints 数据完整性 列完整性，实体完整性

约束类型：(列一级的 not null,check),unique,primary key,foreign key

create table test(

id int not null,

lname varchar(20),

rname varchar(20),

rid int not null,

constraint test_u_1 unique(lname,rname)

)

 

alter table test add constraint pk_test primary key(id);增加主键

alter table test add constraint fk_test foreign key(rid) references test1(id);

级联删除 on delete cascade,或者on delete set null

check 不能用系统函数、sequence、查询

alter table drop constraint c_name

alter table disable/enable constraint c_name

alter table drop column col_name cascade constraint删除列的时候连constraint一块删除

查看constaint select * from user_constraints/user_cons_columns

 

17、视图view

view是一个逻辑结构，不含有数据

create force view viewname as ...

force 强制视图编译通过，不管后面的select语句是否成功

with check option constraint ...

with read only constraint ...

透过视图可以发生数据改变，改变基表！带有group by，函数，rownum，distinct等不可改变

 

18、greatest、least

greatest(p1,p2,p3...)找出其中最大的

least(p1,p2,p3...)找出最小的

coalesce计算多个值的非空值

coalesce(null,null,'a')返回a

coalesce(overtime,faultreorttime)如果同一行中overtime为null，faultreporttime不为null返回faultreporttime

greatest、least函数不将日期格式的直接量串作为日期处理，还是当作串处理

extract代替to_char来选择日期值的成分（如从一个日期中选择月份和天）

extract(Month from sysdate)

BUN_TO_NUM(1)二进制转换成十进制

translate(string,if,then)在一个字符串中进行逐字符的替换

translate(7671234,234567890,'BCDEFGHIJ') 结果 GFG1BCD

 

19、not in、not exists和外连接

一般情况下用外部连接代替not in，可以极大的提高性能。not exists也可以替代not in，只是性能提升不够。

select vipid,vipName from t_vip where vipid not in (select v.vipid from t_vip v,t_vip_detail d where v.vipid=d.vipid)

select v.vipid,v.vipName from t_vip v where not exists (select 'x' from t_vip_detail d where v.vipid=d.vipid)

select t_vip_detail.vipid,t_vip.vipName from t_vip left join t_vip_detail on t_vip.vipid=t_vip_detail.vipid where t_vip_detail.vipid is null

以上三个效果一样

 

20、union(并)、intersect(交)和minus(差)

union相同的只显示一次，对这中重复现象可以用union all

intersect包含在两个查询基表都出现的

in子句的优选级比union高

 

 

21、建立临时表

create global temporary table tableName()

on commit preserve rows子句指定是否在整个会话期间都存在

on commit delete rows子句指定是否在事务处理完成时删除它的行

 

 

22、索引编排表(Index-Organized Table)

根据表的主键列对数据进行排序，索引编排表存储数据时就像把整个表存储在一个索引中那样。

create table命令的organization index子句来建立一个索引编排表

 

23、分区表

a、改善表的查询性能

b、使表更容易管理

c、备份和恢复操作会执行得更好

对表分区可以使用create table子句的partition by range

eg:partition by range(CategoryName)(

partition part1 values less than ('B')

 tablespace part1_ts,

partition part2 values less than (MAXVALUE)

 tablespace part2_ts

);

 

by range 是范围分区

by hash 是散列分区

by list 是列表分区

 

24、索引

构造较少的索引但每个索引中列数较多比构造较多索引但每个所以列数较少更好。

索引类型：一般索引（Not Sorted 传统索引（B树索引）对于包含有大量编号数据的列是非常有用的）、

位图索引(bitmap)、唯一索引(unique)、函数索引(function)

表空间：增加表空间的空间，可以扩展相应的数据文件或者增加新的数据文件

簇(Cluster)是一种存储表的方式，这些表密切相关并精彩一起连接进磁盘的同一区域。

例如：表A表B的数据行可以一起插入到称为簇的单个区域中，而不是将两个表放置在磁盘不同扇区。

簇键可以是多列，将这些表在查询中连接起来。

 

CREATE CLUSTER TEST

(

 DDD                             VARCHAR2(21 BYTE)

)

 

CREATE TABLE TTT

(

 TITLE VARCHAR2(21 BYTE),

 NAME   VARCHAR2(22 BYTE)

)

CLUSTER TEST(TITLE);

在使用表ttt之前必须建立簇索引

CREATE INDEX HR.ssss

 ON CLUSTER HR.TEST

 

 

25、数据库连接(DataBase Link 或DB Link)

提供一个特别的途径访问远程数据库，数据库链接允许用户把一组分布式数据库视为一个独立的、一体化的数据库。

使用hr用户访问unicom，select * from t_vip@Romote_connect

建立数据库链接:create database link Romote_connect connect to unicom identified by password using 'ora9i';

使用同义词来隐藏远程链接：create synonym t_vip for t_vip@Romote_connect

这样可以直接访问select * from t_vip;

 

26、实体化视图

实体化视图是基于查询的数据拷贝，基于远程数据的实体化视图也称为快照

间隔刷新、基于事务的刷新

必须有Create materialized view权限

 

27、使用Oracle Text进行文本搜索

可以利用Oracle Text完成通配符搜索、模糊匹配、关联分级、近似搜索，项加权和单词扩展

使用两种类型的索引context、ctxcat

示例脚本：

REM *****************************

REM The BOOK_REVIEW_CONTEXT Table

REM *****************************

 

create table BOOK_REVIEW_CONTEXT

(Title       VARCHAR2(100) primary key,

Reviewer     VARCHAR2(25),

Review_Date DATE,

Review_Text VARCHAR2(4000));

 

insert into BOOK_REVIEW_CONTEXT values

('MY LEDGER', 'EMILY TALBOT', sysdate,

'A fascinating look into the transactions and finances of G. B. Talbot and Dora Talbot as they managed a property in New Hampshire around 1900. The stories come through the purchases - for medicine, doctor visits and gravesites - for workers during harvests - for gifts at the general store at Christmas. A great read. ');

create index Review_Index_Context on BOOK_REVIEW_CONTEXT(Review_Text)

indextype is ctxsys.context;

 

REM ****************************

REM The BOOK_REVIEW_CTXCAT Table

REM ****************************

 

create table BOOK_REVIEW_CTXCAT

(Title       VARCHAR2(100) primary key,

Reviewer     VARCHAR2(25),

Review_Date DATE,

Review_Text VARCHAR2(4000));

 

insert into BOOK_REVIEW_CTXCAT values

('MY LEDGER', 'EMILY TALBOT', sysdate,

'A fascinating look into the transactions and finances of G. B. Talbot and Dora Talbot as they managed a property in New Hampshire around 1900. The stories come through the purchases - for medicine, doctor visits and gravesites - for workers during harvests - for gifts at the general store at Christmas. A great read. ');

create index Review_Index_Ctxcat on BOOK_REVIEW_CTXCAT(Review_Text)

indextype is ctxsys.ctxcat;

 

contains函数--context索引 两个参数，列名和搜索串

select title from BOOT_REVIEW_CONTEXT where CONTAINS(Review_text,'property')>0

catsearch函数应用ctxcat索引 三个参数，列名，搜索串，索引集名

select title from book_review_ctxcat where catsearch(review_text,'property',null)>0

多个单词精确匹配搜索

contains(review_text,'property AND harvests')>0

catsearch(review_text,'property AND harvests',null)>0

 

 

28、pl/sql

游标，

游标属性

%found        可在游标中取一个记录 布尔型

%notfound     不能从游标中再取到记录 布尔型

%isopen       游标已经打开 布尔型

%rowcount     迄今为止从游标中取出的行数

declare pi constant number(9,7):=3.1415927;

radius integer;

area number(14,2);

cursor rad_cursor is select * from AREAS;

rad_val rad_cursor%ROWTYPE;          //声明变量rad_val,用于设定它的数据类型为rad_cursor游标的数据类型

     rowtype,type使用%rowtype什么，变量将继承游标结果集中所有列和数据类型信息

     %type，变量只能集成用于定义它的列的定义

begin

open rad_cursor;                 //打开游标，执行声明该游标的查询，并且标出将要返回的记录

fetch rad_cursor into rad_val;   //取出记录

area:=pi*power(rad_val.radius,2);

insert into AREAS values(rad_val.radius,area);

area:=pi*power(rad_val.area,2);

insert into AREAS values(rad_val.area,area);

close rad_cursor; //关闭游标

end;

/

pl/sql的条件逻辑

if condition

   then condition

   elsif condition

    then

   else

end if;

嵌套if条件

if condition

then

    if condition

    then

    end if;

else condition

end if;

pl/sql循环

三种类型循环：

简单循环         在循环中当执行到exit或exit when时跳出

for循环   指定的循环次数

while循环 在遇到某个条件时停止循环

1、简单循环，以loop关键字开始，exit when确定何时退出，end loop为循环结束标志

loop

    fetch rad_cursor into rad_val;

    exit when rad_cursor%notfound

   

    end loop

2、for循环

for radius in 1..7 loop

end loop

for rad_val in rad_cursor 游标for循环   //该语句隐式的打开了rad_cursor游标，并将值取出放在rad_val中。

loop     //不需要检查游标的%notfound属性，它通过游标for循环自动检测的

end loop    //也不需要显示的定义rad_val变量

3、while循环

while rad_cursor%notfound

loop

end loop

 

case语句控制分支逻辑

case

when condition

then command

when condition

then command

else command

end case;

case子句中的else关键字与if-then子句中的else关键字相同，如果省略else关键字，pl/sql隐式增加else raise CASE_NOT_FOUND

 

29、触发器

触发器类型：

1、行级触发器，对DML语句影响的每个行执行一次，常用于与数据相关的活动，对于同步保存分布式数据很有用

2、语句级出发器，对美国DML语句执行一次，不常用于与数据相关的活动

3、before、after出发器

4、instead of触发器（支持对象视图引入这种类型触发器，例如update视图时，instead of触发器运行处理基表的数据）

5、模式触发器，模式级的操作上建立，eg：create table阻止DDL操作以及在DDL操作时提供额外的安全监控

6、数据库级触发器，在数据库事件上触发的触发器，注册、关闭、启动、错误

 

create or replace trigger test

before insert or update of fName on address   //针对insert和update列fName在表address上触发

for each row                                  //行级触发器

begin

if inserting then                          //有效的事务处理类型inserting、deleting、updating

 

elsif updating

endif

end;

 

eg：

CREATE OR REPLACE TRIGGER HR.TEST

BEFORE INSERT

ON HR.TTT

FOR EACH ROW

DECLARE

tempval number;

BEGIN

 :new.title:=UPPER(:new.title);

 select locations_seq.NEXTVAL into tempval from dual; //取得seq

 :new.id:=tempval;

 :new.ldate:=sysdate;                                  //设置系统时间

               //往表中填充数据

END ;

 

禁用触发器

alter trigger booktest_up_row enable/disable

 

30、过程、函数和程序包

系统权限：create procedure、execute any procedure

过程、程序包和函数所需的权限不能来自角色，必须直接授予过程、程序包或者函数的拥有者

函数可以返回一个值，可以在查询中直接使用

 

建立程序包，包括程序包说明和程序包体

程序包说明

create or replace package ... as

程序包体

create or replace package body ... as

程序包体可以包括没有在程序包说明中列出的对象，这些是私有对象，程序包的用户不能使用

初始化程序包：在程序包体声明的变量，以及直接begin开始的pl/sql块会在用户会话第一次执行包的函数或过程时执行

begin没有自己的end子句，他使用程序包体的end子句