1、表

create table test (names varchar2(12),dates date,numint,doudouble);

2、視圖

create or replace view vi_test asselect * from test;

3、同義詞

create or replace synonym aafor dbusrcard001.aa;

4、存儲過程

create or replace produce dd(v_id in employee.empoy_id%type)asbeginenddd;

5、函數(shù)

create or replace function ee(v_id in employee%rowtype) return varchar(15)isvar_test varchar2(15);beginreturn var_test;exception when others thenend

6、三種觸發(fā)器的定義

create or replace trigger ffalter deleteon testfor each rowdeclarebegindelete from test;if sql%rowcount < 0 or sql%rowcount is null thenrais_replaction_err(-20004,'錯誤')end ifend

create or replace trigger ggalter inserton testfor each rowdeclarebeginif :old.names = :new.names thenraise_replaction_err(-2003,'編碼重復');end ifend

create or replace trigger hhfor updateon testfor each rowdeclarebeginif updating thenif :old.names <> :new.names thenreaise_replaction_err(-2002,'關鍵字不能修改')end ifend ifend

7、定義游標

declarecursor aa isselect names,num from test;beginfor bb in aaloopif bb.names = 'ORACLE' thenend ifend loop;end

8、速度優(yōu)化，前一語句不后一語句的速度快幾十倍

select names,dates from test,bwhere test.names = b.names(+) andb.names is null andb.dates > date('2003-01-01','yyyy-mm-dd')select names,datesfrom test where names not in ( select names from bwhere dates > to_date('2003-01-01','yyyy-mm-dd'))

9、查找重復記錄

select names,num from test where rowid != (select max(rowid) from test b where b.names = test.names andb.num = test.num)

10、查找表TEST中時間最新的前10條記錄

select * from (select * from test order by dates desc) where rownum < 11

11、序列號的產(chǎn)生

create sequence row_idminvalue 1maxvalue 9999999999999999999999start with 1increment by 1

insert into test values(row_id.nextval,....)