存储过程 带名字的PL/SQL程序块
create or replace procedure p
is
cursor c is
select * from emp2 for update;
begin
for v_emp in c loop
if(v_emp.deptno = 10) then
update emp2 set sal = 888 where current of c;
end if;
end loop;
commit;
end;
编译出错时,show error; 可显示出错信息
执行过程:exec p; 或者 begin p;end;
删除过程:drop procedure p;
--带参数的存储过程,参数默认是输入参数
create or replace procedure p
(v_a in number, v_bnumber, v_ret out number, v_temp in out number)
is
begin
if(v_a > v_b) then
v_ret := v_a;
else
v_ret := v_b;
end if;
v_temp := v_temp + 1;
end;
declare
v_a number := 3;
v_b number := 4;
v_ret number;
v_temp number :=5;
begin
p(v_a, v_b, v_ret, v_temp);
dbms_output.put_line(v_ret);
dbms_output.put_line(v_temp);
end;
函数
create or replace function sal_tax
(v_sal number)
return number
is
begin
if(v_sal < 2000) then
return 0.10;
elsif(v_sal < 3000) then
return 0.15;
else
return 0.20;
end if;
end;
select sal_tax(sal)from emp;
删除函数 drop function sal_tax;
触发器 trigger 依附于某张表上执行
create table emp2_log
(
uname varchar2(20),
action varchar2(10),
atime date
);
create or replace trigger trig
after insert or delete or update on emp2 for each row
begin
if inserting then
insert into emp2_log values(USER,'insert',sysdate);
elsif updating then
insert into emp2_log values(USER,'update',sysdate);
elsif deleting then
insert into emp2_log values(USER,'delete',sysdate);
end if;
end;
update emp2 set sal =2000 where deptno = 20;
select * fromemp2_log;
删除触发器 drop trigger trig;
触发器副作用
执行update dept2set deptno = 11 where deptno = 10;时会违反外键约束
create or replace trigger trig
after insert or delete or update on emp2 for each row
begin
update emp2 set deptno = :NEW.deptno wheredeptno = :OLD.deptno;
end;
建立触发器后再执行update dept2 set deptno = 11 where deptno = 10;不会出错,因为更新时先执行触发器,再检查约束条件。