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 
increment by 1 

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