表
create table test (names varchar()
dates date
num int
dou double);
视图
create or replace view vi_test as
select * from test;
同义词
create or replace synonym aa
for dbusrcardaa;
存储过程
create or replace produce dd(v_id in employeeempoy_id%type)
as
begin
end
dd;
函数
create or replace function ee(v_id in employee%rowtype) return varchar()
is
var_test varchar();
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 < or sql%rowcount is null then
rais_replaction_err(错误)
end if
end
create or replace trigger gg
alter insert
on test
for each row
declare
begin
if :oldnames = :newnames then
raise_replaction_err(编码重复);
end if
end
create or replace trigger hh
for update
on test
for each row
declare
begin
if updating then
if :oldnames <> :newnames then
reaise_replaction_err(关键字不能修改)
end if
end if
end
定义游标
declare
cursor aa is
select namesnum from test;
begin
for bb in aa
loop
if bbnames = ORACLE then
end if
end loop;
end
速度优化前一语句不后一语句的速度快几十倍
select namesdates
from testb
where testnames = bnames(+) and
bnames is null and
bdates > date(yyyymmdd)
select namesdates
from test
where names not in ( select names
from b
where dates > to_date(yyyymmdd))
查找重复记录
select namesnum
from test
where rowid != (select max(rowid)
from test b
where bnames = testnames and
bnum = testnum)
查找表TEST中时间最新的前条记录
select * from (select * from test order by dates desc) where rownum <
序列号的产生
create sequence row_id
minvalue
maxvalue
start with
increment by
insert into test values(row_idnextval)
