ORACLE数据库基本语法集锦.docVIP

ORACLE数据库的基本语法集锦 -- 表 create table test (names varchar2(12), dates date, num int, dou double); -- 视图 create or replace view vi_test as select * from test; -- 同义词 create or replace synonym aa for dbusrcard001.aa; -- 存储过程 create or replace produce dd(v_id in employee.empoy_id%type) as begin end dd; -- 函数 create or replace function ee(v_id in employee%rowtype) return varchar(15) is var_test varchar2(15); begin return var_test; exception when others then end -- 三种触发器的定义 create or replace trigger ff alter delete on test for each row declare begin delete from test; if sql%rowcount 0 or sql%rowcount is null then rais_replaction_err(-20004,错误) end if end create or replace trigger gg alter insert on test for each row declare begin if :old.names = :new.names then raise_replaction_err(-2003,编码重复); end if end create or replace trigger hh for update on test for each row declare begin if updating then if :old.names :new.names then reaise_replaction_err(-2002,关键字不能修改) end if end if end -- 定义游标 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,b where test.names = b.names(+) and b.names is null and b.dates date(2003-01-01,yyyy-mm-dd) select names,dates from 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_id minvalue 1 maxvalue 9999999999999999999999 start with 1 in