PL/SQL中的procedure和function编程

PL/SQL中的procedure和function编程 procedure:存储过程 eg: create or replace procedure p --这儿是和plsql的区别 is curso

PL/SQL中的procedure和function编程

[日期：2013-06-11] 来源：Linux社区 作者：zhaoming [字体：]

PL/SQL中的procedure和function编程

procedure:存储过程

eg:

create or replace procedure p --这儿是和plsql的区别

is

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;

执行过程的命令：exec p; begin p end;

带参数的存储过程；

create or replace procedure p --in表示传入参数 out传出参数，，

(v_a in number,v_b number ,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;

函数：function

create or replacefunctionsal_tax

(v_sal number) --声明了一个函数

return number

is

begin

if(v_sal < 2000) then

return 0.10;

elsif(v_sal < 2750) then

return 0.15;

else

return 0.20;

end if;

end;

函数的调用：：select lower(ename),sal_tax(sal) from emp;

触发器：trigger

不能直接执行，依赖于表

create table emp2_log

(

uname varchar2(20),

action varchar2(10),

atime date

);

创建触发器

create or replacetrigger trig

after insert or delete or update on emp2 for each row

begin

ifinserting then

insert intoemp2_log values (USER,'insert',sysdate);

elsifupdating 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=sal*2 where deptno = 30;

drop triggle trig;(删除)

create or replace trigger trig

after update on dept

for each row

begin

end;

update dept set deptno = 99 where deptno = 10;

树状结构的存储于显示：

create table ariticle

(

id number primary key,

cont varchar2(4000),

pid number,

isleaf number(1), -- 0代表非叶子节点，1代表叶子节点

alevel number(2)

);

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 procudure 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);