子程序和程序包

 

--子程序和程序包

--1.过程

--更新员工的薪水

create or replace procedure updateSal(var_empno number,var_per number)

as

myname emp.ename%type;

oldsal emp.sal%type;

newsal emp.sal%type;

begin

     select ename,sal into myname,oldsal from emp where empno=var_empno;

     newsal := oldsal + oldsal*var_per;

     update emp set sal = newsal where empno=var_empno;

     dbms_output.put_line(myname||'的薪水从'||oldsal||'涨到'||newsal);

     commit;

end updateSal;

--测试程序

begin

updateSal(7566,0.2);

end;

 

--带out类型参数

create or replace procedure getSalByNo(var_empno number,var_sal out number)

is

 

begin

select sal into var_sal from emp where empno=var_empno;

end;

--测试

declare

mysal emp.sal%type;

begin

getSalByNo(7566,mysal);

dbms_output.put_line(mysal);

end;

--带 inout类型参数

create or replace procedure AddSal(var_empno number,var_thesal in out number)

is

mysal emp.sal%type;

begin

     select sal into mysal from emp where empno=var_empno;

     if mysal<var_thesal then

        update emp set sal=var_thesal where empno=var_empno;

     else

     var_thesal := mysal;

     end if;

     commit;

end;

 

--测试

declare

thesal emp.sal%type;

begin

thesal:=7000;

AddSal(7566,thesal);

dbms_output.put_line('当前薪水：'||thesal);

end;

--2.函数

create or replace function mycal (a number,b number) return number

is

begin

     return a*b;

end;

 

declare

myret number;

begin

myret := mycal(10,2);

dbms_output.put_line(myret);

end;

 

--必须带返回值

--如果没有参数，不需要括号

create or replace function f1 return int

as

c int;

begin

c:=1;

return c;

end;

 

--自主事务处理:让被调用的过程开启新事务

create or replace procedure p2

is

mysal number;

pragma autonomous_transaction;

begin

     select sal into mysal from emp where empno=7566;

     dbms_output.put_line('过程P2中：'||mysal);

     rollback;

end;

 

create or replace procedure p1 is

mysal number;

begin

     select sal into mysal from emp where empno=7566;

     dbms_output.put_line('过程P1中：'||mysal);

     update emp set sal = 9000 where empno=7566;

     --commit;

     p2;

     select sal into mysal from emp where empno=7566;

     dbms_output.put_line('最后的值：'||mysal);

end;

 

begin

p1;

end;

 

--程序包(包声明/包主体)

--创建包头（包声明）

create or replace package mypack

is

procedure mysub (n int);

function myfunc (m int) return int;

end mypack;

 

create or replace package body mypack

is

procedure  mysub (n int) is

begin

     dbms_output.put_line(n);

end mysub;

function myfunc(m int) return int is

begin

     return m;

end myfunc;

end mypack;

 

begin

mypack.mysub(20);

end;

 

 

--程序包中声明游标变量

--必须声明游标的返回类型

create or replace package cur_pack

is

cursor my_cur return emp%rowtype;

procedure my_sub;

end cur_pack;

 

create or replace package body cur_pack

is

cursor my_cur return emp%rowtype is select * from emp;

procedure my_sub

is

my_rec emp%rowtype;

begin

     open my_cur;

     loop

         fetch my_cur into my_rec;

         exit when my_cur%notfound;

         dbms_output.put_line(my_rec.ename||'的薪水是'||my_rec.sal);

     end loop;

     close my_cur;

end my_sub;

end cur_pack;

 

begin

cur_pack.my_sub;

end;

 

 

 

--过程的定义

--给某个员工，加薪水，参数：员工编号，加薪幅度

create or replace procedure updatesal(par_empno number,par_per number)

as

myname emp.ename%type;

oldsal emp.sal%type;

newsal emp.sal%type;

begin

     select ename,sal into myname,oldsal from emp where empno=par_empno;

     newsal := oldsal*(1+par_per);

     update emp set sal = newsal where empno=par_empno;

     commit;

     dbms_output.put_line(myname||'的薪水涨到了'||newsal);

     exception

              when no_data_found then

              dbms_output.put_line('查无此人');

end;

 

--命令模式下执行

--exec updatesal(7369,0.2);

 

--PL/SQL程序中执行

begin

     updatesal(7900,0.1);

end;

 

--分析过程的参数

--参数的分类：in/out/in out

--演示使用out类型的参数

--使用过程：根据编号取薪水

create or replace procedure getsal(par_empno in number,par_sal out number)

is

begin

     select sal into par_sal from emp where empno=par_empno;

end;

 

--测试

declare

myempno emp.empno%type := 7900;

mysal emp.sal%type;

begin

     getsal(myempno,mysal);

     dbms_output.put_line(mysal);

end;

 

--演示in out类型参数

--给指定人加薪水到指定的数目，如果已经达到指定数目，则不加薪水

create or replace procedure addsal(par_empno number,par_sal in out number)

is

mysal emp.sal%type;

myname emp.ename%type;

begin

     select sal,ename into mysal,myname from emp where empno=par_empno;

     if mysal<par_sal then

        update emp set sal=par_sal where empno=par_empno;

        commit;

     else

        par_sal := mysal;

     end if;

end;

 

--测试程序

declare

myempno emp.empno%type;

thesal emp.sal%type;

begin

     myempno := 7566;

     thesal := 3000;

     addsal(myempno,thesal);

     dbms_output.put_line(myempno||'当前的薪水是：'||thesal);

end;

 

select * from emp;

 

 

--函数

create or replace function myadd (a number,b number) return number

is

begin

     return a+b;

end;

 

--函数的调用

--在命令模式下

select myadd(1,2) from dual;

--在PL/SQL中使用

begin

     dbms_output.put_line(myadd(1,2));

end;

 

--定义一个函数，作用：根据部门编号取部门名称

create or replace function getDeptName(par_deptno number) return

varchar2

is

mydept dept.dname%type :='暂无';

begin

     select dname into mydept from dept where deptno=par_deptno;

     return mydept;

     exception

              when no_data_found then

              return mydept;

end;

 

select empno,ename,sal,getdeptname(deptno) as deptname from emp;

--注意：如果函数没有参数，不要省略括号

 

--自主事务处理

create or replace procedure p2

as

mysal number;

pragma autonomous_transaction;

begin

     select sal into mysal from emp where empno=7369;

     dbms_output.put_line('过程p2中：'||mysal);

     rollback;

end;

 

create or replace procedure p1

as

mysal number;

begin

     select sal into mysal from emp where empno=7369;

     dbms_output.put_line('过程p1中：'||mysal);

     update emp set sal = 8888 where empno=7369;

     p2();

     select sal into mysal from emp where empno=7369;

     dbms_output.put_line('过程p1中：'||mysal);

end;

 

begin

p1();

end;

 

update emp set sal = 800 where empno=7369;