参考:一个存储过程例子: create or replace procedure p1 as i number:=2; begin select count(*) into i from all_tables where table_name='t1'; if i=0 then dbms_output.put_line('good1!'); elsif i>0 then dbms_output.put_line('good2!'); else --dbms_output.put_line('good3!'); execute immediate 'drop table t1'; end if; end; 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 --同时三种触发器 create or replace trigger tr before update or delete or insert on t for each row begin if inserting then insert into tt values(:new.id,:new.name,:new.age,to_char(sysdate,'yyyy'),'insert',user); elsif updating then insert into tt values(:new.id,:new.name,:new.age,to_char(sysdate,'yyyy'),'update',user); elsif deleting then insert into tt values(:new.id,:new.name,:new.age,to_char(sysdate,'yyyy'),'delete',user); end if; end; --触发器例子 CREATE OR REPLACE TRIGGER temp_aiur AFTER INSERT OR UPDATE ON TEMP FOR EACH ROW BEGIN CASE WHEN inserting THEN dbms_output.put_line ('executing temp_aiur - insert'); WHEN updating THEN dbms_output.put_line ('executing temp_aiur - update'); END CASE; END; -- 定义游标 declare cursor aa is select names,num from test; begin for bb in aa loop if bb.names = "ORACLE" then
create or replace procedure p1
as
i number:=2;
begin
select count(*) into i from all_tables where table_name='t1';
if i=0 then
dbms_output.put_line('good1!');
elsif i>0 then
dbms_output.put_line('good2!');
else
--dbms_output.put_line('good3!');
execute immediate 'drop table t1';
end if;
end;
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
--同时三种触发器
create or replace trigger tr
before update or delete or insert
on t
for each row
begin
if inserting then
insert into tt values(:new.id,:new.name,:new.age,to_char(sysdate,'yyyy'),'insert',user);
elsif updating then
insert into tt values(:new.id,:new.name,:new.age,to_char(sysdate,'yyyy'),'update',user);
elsif deleting then
insert into tt values(:new.id,:new.name,:new.age,to_char(sysdate,'yyyy'),'delete',user);
end if;
end;
--触发器例子
CREATE OR REPLACE TRIGGER temp_aiur
AFTER INSERT OR UPDATE ON TEMP
FOR EACH ROW
BEGIN
CASE
WHEN inserting THEN
dbms_output.put_line
('executing temp_aiur - insert');
WHEN updating THEN
dbms_output.put_line
('executing temp_aiur - update');
END CASE;
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
csdn 下载区里面有下载的东东!