oracle存储过程详解.docVIP

oracle存储过程详解 -- 表create table test (names varchar2(12),?????????????????? dates date,?????????????????? num?? int,?????????????????? dou?? double);-- 视图create or replace view vi_test asselect * from test; -- 同义词create or replace synonym aafor dbusrcard001.aa; -- 存储过程create or replace produce dd(v_id in employee.empoy_id%type)asbegin?? enddd; -- 函数create or replace function ee(v_id in employee%rowtype) return varchar(15)isvar_test varchar2(15);beginreturn var_test;exception when others then?? end -- 三种触发器的定义create or replace trigger ffalter deleteon testfor each rowdeclarebegin?? delete from test;?? if sql%rowcount 0 or sql%rowcount is null then????? rais_replaction_err(-20004,错误)?? end ifend create or replace trigger ggalter inserton testfor each rowdeclarebegin?? if :old.names = :new.names then????? raise_replaction_err(-2003,编码重复);?? end ifend create or replace trigger hhfor updateon testfor each rowdeclarebeginif updating then???? if :old.names :new.names thenreaise_replaction_err(-2002,关键字不能修改)???? end ifend ifend -- 定义游标declare?? cursor aa is????? select names,num from test;begin?? for bb in aa?? loop??????? if bb.names = ORACLE then??????? ??????? end if?? end loop;?? end -- 速度优化，前一语句不后一语句的速度快几十倍select names,dates from test,bwhere test.names = b.names(+) and????? b.names is null and????? b.dates date(2003-01-01,yyyy-mm-dd) select names,datesfrom test where names not in ( select names ?????????????????????? from b????????????????????? where dates to_date(2003-01-01,yyyy-mm-dd))?????????????????????? -- 查找重复记录select names,num from test where rowid != (select max(rowid) ???????????????? from test b ??????????????? where b.names = test.names and????????????????????? b.num = test.num) -- 查找表TEST中时间最新的前10条记录select * from (select * from test order by dates desc) where rownum 11 -- 序列号的产生create sequence row_idminvalue 1maxvalue 9999999999999999999999start with 1increment