PL/SQL(oracle)6_存储过程

方秀隽

　　一、子程序的概念。

   a.是一个命名的pl/SQL块。

   b.通常能够接收传递的参数，能够被别人调用。

   c.基于标准的PL/SQL块结构，包括声明部分，可执行呢部分，异常处理部分，结束部分。

二、子程序的类型.

   a.存储过程：通常执行的是某个动作。

   b.函数：通常执行的某个计算。

三、存储过程

   是一种执行某项动作的子程序，被当做一个数据库对象保存在数据库内，能够被反复调用。

四、创建存储过程的语法：

    Create [Or Replace] Procedure 存储过程名[(参数列表)] Is|As PL/SQL块。

    说明：参数列表形式为 参数名 [参数模式] 参数的数据类型，如果有多个参数，每个参数之间用,分割。

    参数模式：包括三种，分别为in、out 、in Out,当参数模式省略时，表示该参数为in模式。

    参数数据类型：此处数据类型只能写类型名，不能写长度。

    Is|as后面为变量声明，不需要加declare关键字。

    结束部分即可以用end;也可以用end 存储过程名;来结束。

    Create Or Replace Procedure p_1
Is
v_deptname Varchar2(20) :='c';
Begin
Insert Into dept Values(16,v_deptname,'newyork');
Commit;
End p_1;   
　　

五、存储过程的调用。

    1、在另一个块里调用:直接写存储过程名，传递相应的参数即可。

    2、在Sql-plus里调用：exec 存储过程名(参数列表);   

六、 如何编辑一个存储过程

    1、可以直接sql-window写一个，写完之后按F8，查看是否有编译错误。 

    2、通过new-program window-Procedure来创建，写完之后按F8编译，如果有编译错误，在存储过程名

    上点击右键，edit修改，重新编译。

    3、直接通过command_window创建，如果有编译错误，执行show errors命令查看。

七、存储过程的三种参数模式：

    in:默认模式，相当于一个常数，该模式的参数在存储过程中不能被修改。在参数列表中可以指定默认值。

    Out:该模式的参数相当于一个变量，不可以在参数列表中指定默认值。

    In Out:该模式的参数相当于一个初始化的变量，不可以在参数列表中指定默认值。

   练习：

   1、创建一个存储过程，该存储过程的功能是通过指定的员工编号及工资，

   更新相应编号的员工工资，在原基础上增加，并把更新后的员工工资返回。

   Create Or Replace Procedure raise_sal3(p_empno emp.empno%Type,p_sal In Out emp.sal%Type) Is
Begin
Update emp Set sal = sal + p_sal Where empno = p_empno;
Select sal Into p_sal From emp Where empno = p_empno;
End;   
　　

   2、写一个匿名块，调用上述存储过程。


   Declare
v_sal emp.sal%Type;   
Begin
v_sal :=200;
raise_sal3(7788,v_sal);
dbms_output.put_line(v_sal);
End;  
 　　
八、调用存储过程时传递参数的方式。

    1、按照位置方式传递。

    2、按名称方式传递。

    Declare
v_sal emp.sal%Type;   
Begin
v_sal :=200;
raise_sal3(p_sal => v_sal,p_empno => 7788);
dbms_output.put_line(v_sal);
End;   
　　

   3、组合方式传递：第一个参数按照位置传递，其余参数按照名称方式传递。

    Declare
v_sal emp.sal%Type;   
Begin
v_sal :=200;
raise_sal3(7788,p_sal => v_sal);
dbms_output.put_line(v_sal);
End;  
　　

   练习：

   1、为部门表添加一个字段maxnum,整型，该字段表示部门编制。

      Alter Table dept Add maxnum Number;

   2、为该字段赋予不同的值。

     Select * From dept For Update

   3、创建一个存储过程add_emp,该存储过程实现添加员工的功能，参数为emp%rowtype类型。

      当某个部门的员工数超过该部门的最大编制时，使用异常处理方式提示用户"该部门人数编制已满，不能再添加员工"。

      Create Or Replace Procedure add_emp(p_emp emp%Rowtype) Is
v_maxnum Number;
v_currnum Number;
e_too_many Exception;
Begin
Select maxnum Into v_maxnum From dept Where deptno = p_emp.deptno;--查询部门编制
Select Count(empno) Into v_currnum From emp Where deptno = p_emp.deptno ;--查询部门当前人数
If v_currnum >=v_maxnum Then
Raise e_too_many;
Else
Insert Into emp Values(p_emp.empno,p_emp.ename,p_emp.job,p_emp.mgr,p_emp.hiredate,p_emp.sal,p_emp.comm,p_emp.deptno);
dbms_output.put_line('录入成功');
End If;
Exception
When e_too_many Then
dbms_output.put_line('该部门编制已满');
End;
 　　

   4、写一个存储过程call_addemp,验证上述存储过程的正确性。

      Create Or Replace Procedure call_addemp Is
v_emp emp%Rowtype;
Begin
Select * Into v_emp From emp Where empno= 7788;
v_emp.empno := 7789;
add_emp(v_emp);
End;     
　　
  

九、本地子程序

   :声明在另一个存储过程中的存储过程，使用procedure 存储过程名 方式来声明，本地子程序的有效范围是

   被限制在他们定义的父块中。而且必须写在所有的变量声明之后。

十、删除存储过程。


练习：编写一个简单的Oracle存储过程：要求根据主管编号显示出其直接下级员工和所有下级员工（提示：入参为主管编号）

Create Or Replace Procedure show_nextlevel(p_empno emp.empno%Type) Is
Cursor emp_cursor Is Select Level,ename From emp Where empno <> p_empno Start With empno = p_empno Connect By Prior empno=mgr ;
Begin
For emp_record In emp_cursor Loop
If emp_record.Level =2 Then
dbms_output.put_line('员工'||emp_record.ename||'为直接下级');
Else
dbms_output.put_line('员工'||emp_record.ename||'为间接下级');
End If;      
End Loop;
End;   
--调用存储过程块
Begin
     show_nextlevel(p_empno => 7839);
End;