Oracle笔记(7)---存储过程

存储过程

存储过程可以简单的理解为一段可以执行某个活动/动作的子程序，可以作为一个系统对象被存储在数据库中，可以重复调用。

 

创建语法：

CREATE [OR REPLACE] PROCEDURE procedureName

[(param1 [{IN|OUT|IN OUT] data_type

[,(param2 [{IN|OUT|IN OUT}] data_type

.......

)]

 

IS | AS

       Declaration section

BEGIN

       action section

EXCEPTION              

       Exception section

END [procedureName];

 

注解：

IN:输入参数，向存储过程传递值，默认类型，可以不写，同时可以使用DEFAULT 指定默认值。

OUT:输出参数，用于返回结果。

IN OUT：作为IN参数向存储过程传递值，同时作为OUT参数返回值。

REPLACE选项指明若已有同名的存储过程存在，那么将被替换成当前创建的版本。

以上蓝色字体部分为可选项。

 

 

无参存储过程：

例如：

CREATE OR REPLACE PROCEDURE proc

IS

BEGIN

DBMS_OUTPUT.PUT_LINE('test return');

………….

RETURN;

………….   -- 不会被执行

END;

 

注意：存储过程可以不带任何参数，不能直接用RETURN返回任何值，若需要获得返回结果应当使用OUT或IN OUT参数，但是可以用RETURN语句退出存储过程。

 

SHOW ERRORS

在编译过程中可能会产生错误，可以使用SHOW ERRORS来查看错误信息。当前只有一个对象，要显示错误可以用SHOW ERRORS,当有多个对象时，可以精确的指明要查看那个对象，例如此处可写：SHOW ERRORS PROCEDURE myproc,同样的要查看函数、包、触发器的错误，用SHOW ERRORS FUNCTION/PACKAGE/TRIGER 名称

 

 

带参数的存储过程：

CREATE OR REPLACE PROCEDURE myproc(no IN number,name OUT varchar2)

IS

BEGIN

SELECT dname INTO name FROM dept WHERE deptno=no;

DBMS_OUTPUT.PUT_LINE(name);

END;

注意：参数不能给出精度，否则会编译错误。

默认参数：执行时若未传输参数，则参数为相应的默认值，只有IN参数可以指定默认值，OUT 和IN OUT参数不能指定默认值。

例如：

CREATE OR REPLACE PROCEDURE myproc(no IN number DEFAULT 10)

IS

BEGIN

DBMS_OUTPUT.PUT_LINE(no);

END;

 

 

存储过程的执行/调用：

存储过程建立完成后，只要通过授权，用户就可以在 SQLPLUS 、ORACLE开发工具或第 ORACLE 使用 EXECUTE 语句来实现对存储过程的调用：

EXEC[UTE]  Procedure_name( parameter1, parameter2 );

 

执行上述存储过程：

（1）  EXEC myproc(10)

（2）  DECLARE

name varchar2(10);

BEGIN

myproc(10);

END;

 

注意：在BEGIN....END中间不能写成EXECUTE myproc(10);当存储过程无参数或者只需要常量作为输入参数时，可以直接写EXECUTE procedure_name或EXECUTE procedure_name(常量类型的输入参数）。

例如：

CREATE OR REPLACE PROCEDURE myproc(no IN number)

IS

name varchar2(10);  //不需要DECLARE关键字

BEGIN

SELECT dname INTO name FROM dept WHERE deptno=no;

DBMS_OUTPUT.PUT_LINE(name);

END;

 

执行：EXECUTE myproc(10)

 

 

本地存储过程：

在 PL/SQL 程序中还可以在块内建立本地函数和过程，这些函数和过程不存储在数据库中，但可以在创建它们的 PL/SQL 程序中被重复调用。 本地函数和过程在PL/SQL 块的

声明部分定义，它们的语法格式与存储函数和过程相同，但不能使用 CREATE  OR

REPLACE 关键字。

 

例如：

DECLARE

V_num NUMBER;

V_sum NUMBER(8, 2);

PROCEDURE proc_demo(

    Dept_no NUMBER DEFAULT 10,

    Sal_sum OUT NUMBER,

    Emp_count OUT NUMBER)

IS

BEGIN

    SELECT SUM(sal), COUNT(*) INTO sal_sum, emp_count  

FROM emp WHERE deptno=dept_no;

EXCEPTION

   WHEN NO_DATA_FOUND THEN 

      DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');

   WHEN OTHERS THEN 

      DBMS_OUTPUT.PUT_LINE('发生其它错误!');

END proc_demo;

BEGIN  Proc_demo(30, v_sum, v_num);

DBMS_OUTPUT.PUT_LINE('30 号部门工资总和：'||v_sum||，人数：||v_num);  Proc_demo(sal_sum => v_sum, emp_count => v_num);

DBMS_OUTPUT.PUT_LINE('10 号部门工资总和：'||v_sum||，人数：||v_num);

END;

 

 删除存储过程：

 DROP PROCEDURE procedurename