首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

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

2013-11-22 
PL/SQL(oracle)6_存储过程一、子程序的概念。?? a.是一个命名的pl/SQL块。?? b.通常能够接收传递的参数,能够

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;

热点排行