PL/SQL中的procedure和function编程
procedure:存储过程
eg:
cursor c
is
select * from emp2 for update;
--v_temp c%rowtype;
begin
for v_temp in c
loop
if(v_temp.sal< 2000) then
update emp2 set sal=sal*2 where current of c ;
elsif(v_temp.sal = 5000) then
delete from emp2 where current of c;
end if;
end loop;
commit;
end;
带参数的存储过程；
create or replace procedure p
is
begin
if(v_a > v_b) then
t :=v_a;
else
:=v_b;
end if;
end;
调用过程：
declare
begin
p(v_a,v_b,v_ret,v_temp);
dbms_output.put_line(v_ret);
dbms_output.put_line(v_temp);
end;
函数：function
functionsal_tax
is
begin
if(v_sal < 2000) then
elsif(v_sal < 2750) then
else
end if;
end;
触发器：
不能直接执行，依赖于表
create table emp2_log
(
uname varchar2(20),
action varchar2(10),
atime date
);
create or replace
begin
if
emp2_log values (USER,'insert',sysdate);
elsif
insert into emp2_log values(USER,'update',sysdate);
elsif
insert into emp2_log values (USER,'delete',sysdate);
end if;
end;
update emp2 set sal=sal*2 where deptno = 30;
drop triggle trig;(删除)
create or replace trigger trig
after update on dept
begin
:OLD.deptno
end;
update dept set deptno = 99 where deptno = 10;
create table ariticle
(
id number primary key,
cont varchar2(4000),
pid number,
);
insert into ariticle values(1,'ahshdhshd',0,0,0);
insert into ariticle values(2,'bhshdhshd',1,0,1);
insert into ariticle values(3,'chshdhshd',2,1,2);
insert into ariticle values(4,dhshdhshd',2,0,2);
insert into ariticle values(5,'ehshdhshd',4,1,3);
insert into ariticle values(6,'fhshdhshd',10,1);
insert into ariticle values(7,'ghshdhshd',6,1,0);
insert into ariticle values(8,'hhshdhshd',3,0,4);
insert into ariticle values(9,'ihshdhshd',7,0,0);
insert into ariticle values(10,'jhshdhshd',9,0,3);
commit;(提交)
create or replace p (v_pid ariticle.pid%type,v_level binary_integer)
is cursor c
is select * from ariticle where pid = v__pid;
v_preStr varchar2(1024) :='';
begin
for i in 0..v_level loop
v_preStr :=v_preStr || ' ';
for v_ariticle in c
loop
dbms_output.put_line(v_preStr || v_ariticle.cont);
if (v_ariticle.isleaf = 0) then
p(v_ariticle.id,v_level+1);
end if;
end loop;
end;
exec p(0);