当前位置:数据库 > SQLServer >>

plsql中的procedure和function编程

plsql中的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 replace  function sal_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 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=sal*2 where deptno = 30;
 
drop triggle trig;(删除)
 
create  or replace trigger    trig
        after update on dept
        for each row 
begin 
        update emp set deptno =:NEW.deptno where deptno = :OLD.deptno;
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);
 
Oracle
MySQL
Access
SQLServer
DB2
Excel
SQLite
SYBASE
Postgres
如果你遇到数据库难题:
请访问www.zzzyk.com 试试
CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,