存储过程、自定义函数和程序包

一  存储过程

    存储过程是Oracle中最常用的程序块。它可以被赋予参数，存储在数据库中，可以被用户反复调用。由于存储过程保存在数据库时已经被编译过，因此在执行时无需再编译，运行的效率比普通PL/SQL块更高。同时，可以将一部分业务通过存储过程的方式放在数据库中，实现程序的模块化设计。

--注意：1.参数类型后面不要加精度 比如theName varchar2(20)是错误的

      2. 在命名的存储过程中不要用&符号输入

--第一个存储过程

create or replace procedureproc_find_emp(empno in number)

is

theRow employee%rowtype;

begin

 select * into theRow from employee where empid=empno;

 dbms_output.put('职工编号:'||empno);

 dbms_output.put('  职工名称:'||theRow.Empname);

 dbms_output.put_line('  职工邮箱:'||theRow.Email);

 exception

   when no_data_found then

     dbms_output.put_line('没有找到编号为'||empno||'的职工信息');

end proc_find_emp;

 

--执行

--1.直接在命令行执行

exec proc_find_emp(1005);

 

--利用匿名块

begin

 proc_find_emp(1005);

end;

--给员工涨工资

create or replace procedureproc_add_sal(theId in int, amount in number)

is

 old_ename varchar2(20);

 old_sal number;

begin

 select empname,sal into old_ename,old_sal from employee

 where empid=theId;

  update employee set sal=sal+amount whereempid=theid;

 commit;

 dbms_output.put_line('员工'||old_ename||'的工资达到'||(old_sal+amount));

 exception

   when others then

     dbms_output.put_line('发生了异常,请检查代码');

     rollback;

end proc_add_sal;

 

--可以不按顺序来传递参数

exec proc_add_sal(amount=>-500,theId=> 1005);

 

--out参数的存储过程

create or replace procedure

proc_out_test(amount out number) is

begin

 select sum(sal) into amount from employee; 

end proc_out_test;

--out 参数执行必须采用匿名块

declare

out_amount number;

begin

  proc_out_test(out_amount);

 dbms_output.put_line('每月需要支出的工资总额为:'||out_amount);

end;

-- in out类型的参数,既要传进去使用，又要传出来

create or replace procedure

proc_inout_test(phone in out varchar2) is

begin

 phone:='0731-'||phone;

end proc_inout_test;

--执行

declare

 thephone varchar2(20);

begin

 thephone:='&tt';

 proc_inout_test(thephone);

 dbms_output.put_line('新的号码为:'||thephone);

end;

 

--子过程(存储过程里面嵌套存储过程)
--查询比【最晚入职员工的最低工资】还要低的老员工信息
create or replace procedure proc_deal_sal
as
   minSal  float;
   theDate  date;
   --子过程start---
   procedure  proc_old_sal(theSal  out  float,maxDate  out  date)
   as
   begin
      select  max(hiredate) into maxDate from emp;
      select  min(sal) into theSal from emp where hiredate=maxDate;
   end  proc_old_sal;
   --子过程end-- 
begin
   proc_old_sal(minSal,theDate);
   for  theRow  in  (select * from emp where sal<minSal and hiredate<theDate)
   loop
     dbms_output.put('员工名称'||theRow.eName);
     dbms_output.put('  员工岗位'||theRow.job);
     dbms_output.put_line('  员工工资'||theRow.sal);
   end loop;    
end proc_deal_sal;

二  自定义函数

       存储函数和存储过程非常的相似，也是一种存储在数据库中的命名的程序块，也可以接收输入参数，进行逻辑处理以后将处理结果返回给调用者。函数和过程之间的区别主要在于，函数必须有返回值，并且可以作为一个表达式的一部分，但不能象存储过程一样作为一个单独的语句使用。

create or replace functionfunc_getEmpById(theId number)

return number

is

 Result number;

begin

 select sal into Result from employee where empId=theId;

 return (Result);

 exception

 when no_data_found then

   dbms_output.put_line('没有找到相关数据');

   return (NULL);

 WHEN others then

   dbms_output.put_line('发生了其他异常');

   return (NULL);

end func_getEmpById;

 

--函数的调用

exec dbms_output.put_line('调用结果'||func_getEmpById(1005));

 

--补充 decode函数

select empname,

 decode(deptidd,10,'人事部',11,'财务部',12,'研发部',13,'销售部',14,'后勤部')

from employee;

三 程序包

    Oracle中的程序包是一组相关的过程、函数、变量、常量和游标等PL/SQL程序设计元素的组合，它存储于数据字典中。在PL/SQL程序设计中，使用程序包来组织过程、函数等的结构，可使程序设计实现模块化，从而提高程序的编写效率，也可以提高程序的执行效率。因为在程序中第一次调用包中的函数或者存储过程是，程序会将整个包引入内存，那么再次调用包中的成员时，将直接从内存中引用，从而加快程序的运行效率。

   一个程序包由两个部分组成：包头(PACKAGE)和包体(PACKAGE BODY。其中包头用于声明包内的数据类型、变量、常量、游标、过程、函数等元素。而包体是对规范部分声明的过程、函数的具体实现。

--包头

create or replace package pkg_myemp is

  --先申明一个函数

 function func_getEmpById(theId number) return number;

  --获得工资最低的员工的信息

 procedure proc_getMinSalEmp;

  --获得工资最高的员工信息

 procedure proc_getMaxSalEmp;

end pkg_myemp;

 

--包体

create or replace package body pkg_myemp

is

  --定义函数，根据编号获得工资

 function func_getEmpById(theId number)

 return number

  is

   Result number;

 begin

   select sal into Result from employee where empId=theId;

   return(Result);

   exception

   when no_data_found then

     dbms_output.put_line('没有找到相关数据');

     return (NULL);

   WHEN others then

     dbms_output.put_line('发生了其他异常');

     return (NULL);

  endfunc_getEmpById;

  --定义过程，获得工资最低的员工的信息

 procedure proc_getMinSalEmp

  is

   theRow employee%rowtype;

   theSal number;

 begin

   select min(sal) into theSal from employee;

   select * into theRow from employee where sal=theSal;

   dbms_output.put(' 员工编号'||theRow.Empid);

   dbms_output.put(' 员工姓名'||theRow.Empname);

   dbms_output.put_line(' 员工工资'||theRow.Sal);

   exception

     when too_many_rows then

       dbms_output.put_line('有多人工资并列最低');

  endproc_getMinSalEmp;

  --定义过程，获得工资最高的员工的信息

 procedure proc_getMaxSalEmp

  is

   theRow employee%rowtype;

   theSal number;

 begin

   select max(sal) into theSal from employee;

   select * into theRow from employee where sal=theSal;

   dbms_output.put(' 员工编号'||theRow.Empid);

   dbms_output.put(' 员工姓名'||theRow.Empname);

   dbms_output.put_line(' 员工工资'||theRow.Sal);

   exception

     when too_many_rows then

       dbms_output.put_line('有多人工资并列最高');

  endproc_getMaxSalEmp;

end pkg_myemp;

 

执行函数:dbms_output.put_line(‘9527的工资:’|| pkg_myemp.func_getEmpById(9527));

执行过程：exec pkg_myemp.proc_getMinSalEmp

         Exec pkg_myemp. proc_getMaxSalEmp

 

--子程序与数据库对象的依赖性

Select object_name,status  from user_objects ;