Oracle基本语法集锦

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、函数

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、三种触发器的定义

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、速度优化，前一语句不后一语句的速度快几十倍

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、序列号的产生

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

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