PL/SQL程序设计（六）

第六章  存储函数和过程

§6.1  引言

ORACLE 提供可以把PL/SQL 程序存储在数据库中，并可以在任何地方来运行它。这样就叫存储过程或函数。过程和函数统称为PL/SQL子程序，他们是被命名的PL/SQL块，均存储在数据库中，并通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据。在本节中，主要介绍：

1．  创建存储过程和函数。

2．  正确使用系统级的异常处理和用户定义的异常处理。

3．  建立和管理存储过程和函数。

§6.2  创建函数

1. 建立内嵌函数

语法如下：

CREATE [OR REPLACE] FUNCTION function_name

[ (argment [ { IN | IN OUT }] Type,

      argment [ { IN | OUT | IN OUT } ] Type) ]

       [ AUTHID DEFINER | CURRENT_USER ]

RETURN return_type

{ IS | AS }

<类型.变量的说明>

BEGIN

FUNCTION_body

EXCEPTION

其它语句

END;

 

 

例1.       获取某部门的工资总和：

 

CREATE OR REPLACE FUNCTION get_salary(

       Dept_no NUMBER,

       Emp_count OUT NUMBER)

       RETURN NUMBER

IS

       V_sum NUMBER;

BEGIN

       SELECT SUM(sal), count(*) INTO V_sum, emp_count

              FROM emp WHERE deptno=dept_no;

       RETURN v_sum;

EXCEPTION

   WHEN NO_DATA_FOUND THEN

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

   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE(SQLCODE||’---‘||SQLERRM);

END get_salary;

2. 内嵌函数的调用

函数声明时所定义的参数称为形式参数，应用程序调用时为函数传递的参数称为实际参数。应用程序在调用函数时，可以使用以下三种方法向函数传递参数：

 

第一种参数传递格式称为位置表示法，格式为：

       argument_value1[,argument_value2 …]

 

例3：计算某部门的工资总和：

 

DECLARE

       V_num NUMBER;

       V_sum NUMBER;

BEGIN

       V_sum :=get_salary(30, v_num);

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

END;

 

第二种参数传递格式称为名称表示法，格式为：

       argument => parameter [,…]

其中：argument 为形式参数，它必须与函数定义时所声明的形式参数名称相同。Parameter 为实际参数。

在这种格式中，形式参数与实际参数成对出现，相互间关系唯一确定，所以参数的顺序可以任意排列。

例4：计算某部门的工资总和：

 

DECLARE

       V_num NUMBER;

       V_sum NUMBER;

BEGIN

       V_sum :=get_salary(emp_count => v_num, dept_no => 30);

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

END;

 

第三种参数传递格式称为混合表示法：

即在调用一个函数时，同时使用位置表示法和名称表示法为函数传递参数。采用这种参数传递方法时，使用位置表示法所传递的参数必须放在名称表示法所传递的参数前面。也就是说，无论函数具有多少个参数，只要其中有一个参数使用名称表示法，其后所有的参数都必须使用名称表示法。

 

例5：

CREATE OR REPLACE FUNCTION demo_fun(

       Name VARCHAR2,

       Age INTEGER,

       Sex VARCHAR2)

       RETURN VARCHAR2

AS

       V_var VARCHAR2(32);

BEGIN

       V_var := name||’：‘||TO_CHAR(age)||’岁，’||sex;

       RETURN v_var;

END;

 

DECLARE

       Var VARCHAR(32);

BEGIN

       Var := demo_fun(‘user1’, 30, sex => ‘男’);

       DBMS_OUTPUT.PUT_LINE(var);

 

       Var := demo_fun(‘user2’, age => 40, sex => ‘男’);

       DBMS_OUTPUT.PUT_LINE(var);

 

       Var := demo_fun(‘user3’, sex => ‘女’, age => 20);

       DBMS_OUTPUT.PUT_LINE(var);

END;

 

无论采用哪一种参数传递方法，实际参数和形式参数之间的数据传递只有两种方法：传址法和传值法。所谓传址法是指在调用函数时，将实际参数的地址指针传递给形式参数，使形式参数和实际参数指向内存中的同一区域，从而实现参数数据的传递。这种方法又称作参照法，即形式参数参照实际参数数据。输入参数均采用传址法传递数据。

       传值法是指将实际参数的数据拷贝到形式参数，而不是传递实际参数的地址。默认时，输出参数和输入/输出参数均采用传值法。在函数调用时，ORACLE将实际参数数据拷贝到输入/输出参数，而当函数正常运行退出时，又将输出形式参数和输入/输出形式参数数据拷贝到实际参数变量中。

 

3. 参数默认值

在CREATE OR REPLACE FUNCTION 语句中声明函数参数时可以使用DEFAULT关键字为输入参数指定默认值。

 

例6：

CREATE OR REPLACE FUNCTION demo_fun(

       Name VARCHAR2,

       Age INTEGER,

       Sex VARCHAR2 DEFAULT ‘男’)

       RETURN VARCHAR2

AS

       V_var VARCHAR2(32);

BEGIN

       V_var := name||’：‘||TO_CHAR(age)||’岁，’||sex;

       RETURN v_var;

END;

 

具有默认值的函数创建后，在函数调用时，如果没有为具有默认值的参数提供实际参数值，函数将使用该参数的默认值。但当调用者为默认参数提供实际参数时，函数将使用实际参数值。在创建函数时，只能为输入参数设置默认值，而不能为输入/输出参数设置默认值。

 

DECLARE

       Var VARCHAR(32);

BEGIN

       Var := demo_fun(‘user1’, 30);

       DBMS_OUTPUT.PUT_LINE(var);

 

       Var := demo_fun(‘user2’, age => 40);

       DBMS_OUTPUT.PUT_LINE(var);

 

       Var := demo_fun(‘user3’, sex => ‘女’, age => 20);

       DBMS_OUTPUT.PUT_LINE(var);

END;

§6.3  存储过程

§6.3.1  创建过程

 

建立存储过程

    在 ORACLE SERVER上建立存储过程,可以被多个应用程序调用,可以向存储过程传递参数,也可以向存储过程传回参数.

 

创建过程语法:

CREATE [OR REPLACE] PROCEDURE Procedure_name

[ (argment [ { IN | IN OUT }] Type,

      argment [ { IN | OUT | IN OUT } ] Type ]

       [ AUTHID DEFINER | CURRENT_USER ]

{ IS | AS }

<类型.变量的说明>

BEGIN

<执行部分>

EXCEPTION

<可选的异常错误处理程序>

END;

 

例7．用户连接登记记录；

 

CREATE table logtable (userid VARCHAR2(10), logdate date);

 

CREATE OR REPLACE PROCEDURE logexecution

IS

BEGIN

INSERT INTO logtable (userid, logdate) VALUES (USER, SYSDATE);

END;

 

例8．删除指定员工记录；

 

CREATE OR REPLACE PROCEDURE DelEmp

(v_empno IN emp.empno%TYPE)

AS

No_result EXCEPTION;

BEGIN

   DELETE FROM emp WHERE empno=v_empno;

   IF SQL%NOTFOUND THEN

      RAISE no_result;

   END IF;

   DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'的员工已被除名!');

EXCEPTION

   WHEN no_result THEN

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

   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE(SQLCODE||’---‘||SQLERRM);

END DelEmp;

 

例9．插入员工记录；

 

CREATE OR REPLACE PROCEDURE InsertEmp(

   v_empno  in emp.empno%TYPE,

   v_name   in emp.ename%TYPE,

   v_deptno  in emp.deptno%TYPE)

AS

   empno_remaining EXCEPTION;

   PRAGMA EXCEPTION_INIT(empno_remaining, -1);

   /* -1 是违反唯一约束条件的错误代码 */

BEGIN

   INSERT INTO emp(empno, ename, hiredate, deptno)

            VALUES(v_empno, v_name, sysdate, v_deptno);

   DBMS_OUTPUT.PUT_LINE('插入数据记录成功!');

EXCEPTION

   WHEN empno_remaining THEN

      DBMS_OUTPUT.PUT_LINE('违反数据完整性约束!');

   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE(SQLCODE||’---‘||SQLERRM);

END InsertEmp;

 

§6.3.2  调用存储过程

 

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

EXEC[UTE]  Procedure_name( parameter1, parameter2…);

 

例10：

EXECUTE logexecution;

 

例11：查询指定员工记录；

 

CREATE OR REPLACE PROCEDURE QueryEmp

(v_empno IN emp.empno%TYPE,

     v_ename OUT emp.ename%TYPE,

     v_sal OUT emp.sal%TYPE)

AS

BEGIN

     SELECT ename, sal INTO v_ename, v_sal FROM emp WHERE empno=v_empno;

     DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'的员工已经查到!');

EXCEPTION

     WHEN NO_DATA_FOUND THEN

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

      WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE(SQLCODE||’---‘||SQLERRM);

END QueryEmp;

 

调用方法:

 DECLARE

    v1 emp.ename%TYPE;

    v2 emp.sal%TYPE;

 BEGIN

   QueryEmp(7788, v1, v2);

   DBMS_OUTPUT.PUT_LINE('姓名:'||v1);

   DBMS_OUTPUT.PUT_LINE('工资:'||v2);

   QueryEmp(7902, v1, v2);

   DBMS_OUTPUT.PUT_LINE('姓名:'||v1);

   DBMS_OUTPUT.PUT_LINE('工资:'||v2);

   QueryEmp(8899, v1, v2);

   DBMS_OUTPUT.PUT_LINE('姓名:'||v1);

   DBMS_OUTPUT.PUT_LINE('工资:'||v2);

END;

 

例12．计算指定部门的工资总和，并统计其中的职工数量。

 

CREATE OR REPLACE 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(SQLCODE||’---‘||SQLERRM);

END proc_demo;

 

调用方法:

 DECLARE

V_num NUMBER;

V_sum NUMBER(8, 2);

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;

 

       在PL/SQL 程序中还可以在块内建立本地函数和过程，这些函数和过程不存储在数据库中，但可以在创建它们的PL/SQL 程序中被重复调用。本地函数和过程在PL/SQL 块的声明部分定义，它们的语法格式与存储函数和过程相同，但不能使用CREATE OR REPLACE 关键字。

 

例13：建立本地过程，用于计算指定部门的工资总和，并统计其中的职工数量；

 

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(SQLCODE||’---‘||SQLERRM);

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;

 

§6.3.3  AUTHID

 

过程中的AUTHID 指令可以告诉ORACLE ，这个过程使用谁的权限运行．默任情况下，存储过程会作为调用者的过程运行，但是具有设计者的特权．这称为设计者权利运行．

 

例14：建立过程，使用AUTOID DEFINER；

 

Connect scott/tiger

 

DROP TABLE logtable;

CREATE table logtable (userid VARCHAR2(10), logdate date);

 

CREATE OR REPLACE PROCEDURE logexecution

       AUTHID DEFINER

IS

BEGIN

   INSERT INTO logtable (userid, logdate) VALUES (USER, SYSDATE);

END;

 

GRANT EXECUTE ON logexecution TO PUBLIC;

 

CONNECT / AS SYSDBA

GRANT CONNECT TO JERRY IDENTIFIED BY JERRY;

 

CONNECT JERRY/JERRY

INSERT INTO SCOTT.LOGTABLE VALUES (USER, SYSDATE);

EXECUTE scott.logexecution

 

CONNECT SCOTT/TIGER

SELECT * FROM scott.logtable;

 

 

例15：建立过程，使用AUTOID CURRENT_USER；

 

CONNECT SCOTT/TIGER

 

CREATE OR REPLACE PROCEDURE logexecution

       AUTHID CURRENT_USER

IS

BEGIN

   INSERT INTO logtable (userid, logdate) VALUES (USER, SYSDATE);

END;

 

GRANT EXECUTE ON logexecution TO PUBLIC;

 

CONNECT JERRY/JERRY

INSERT INTO SCOTT.LOGTABLE VALUES (USER, SYSDATE);

EXECUTE scott.logexecution

 

 

§6.3.4  PRAGMA AUTONOMOUS_TRANSACTION

 

ORACLE8i 可以支持事务处理中的事务处理的概念．这种子事务处理可以完成它自己的工作，独立于父事务处理进行提交或者回滚．通过使用这种方法，开发者就能够这样的过程，无论父事务处理是提交还是回滚，它都可以成功执行．

 

例1６：建立过程，使用自动事务处理进行日志记录；

 

DROP TABLE logtable;

 

CREATE TABLE logtable(

       Username varchar2(20),

       Date_time date,

       Message varchar2(60)

);

 

CREATE TABLE temp_table( N number );

 

CREATE OR REPLACE PROCEDURE log_message(p_message varchar2)

       AS

       PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

       INSERT INTO logtable VALUES ( user, sysdate, p_message );

       COMMIT;

END log_message;

 

 

BEGIN

       Log_message (‘About to insert into temp_table‘);

       INSERT INTO temp_table VALUES (1);

       Log_message (‘Rollback to insert into temp_table‘);

       ROLLBACK;

END;

 

SELECT * FROM logtable;

SELECT * FROM temp_table;

 

例17：建立过程，没有使用自动事务处理进行日志记录；

 

CREATE OR REPLACE PROCEDURE log_message(p_message varchar2)

       AS

BEGIN

       INSERT INTO logtable VALUES ( user, sysdate, p_message );

       COMMIT;

END log_message;

 

BEGIN

       Log_message (‘About to insert into temp_table‘);

       INSERT INTO temp_table VALUES (1);

       Log_message (‘Rollback to insert into temp_table‘);

       ROLLBACK;

END;

 

SELECT * FROM logtable;

SELECT * FROM temp_table;

 

 

§6.3.5  开发存储过程步骤

    开发存储过程、函数、包及触发器的步骤如下：

 

§6.3.5.1  使用文字编辑处理软件编辑存储过程源码

    使用文字编辑处理软件编辑存储过程源码，要用类似WORD 文字处理软件进行编辑时，要将源码存为文本格式。

 

§6.3.5.2  在SQLPLUS或用调试工具将存储过程程序进行解释

    在SQLPLUS或用调试工具将存储过程程序进行解释；

    在SQL>下调试，可用START 或GET 等ORACLE命令来启动解释。如：

SQL>START c:\stat1.sql

    如果使用调式工具，可直接编辑和点击相应的按钮即可生成存储过程。

 

§6.3.5.3  调试源码直到正确

    我们不能保证所写的存储过程达到一次就正确。所以这里的调式是每个程序员必须进行的工作之一。在SQLPLUS下来调式主要用的方法是：

l  使用 SHOW ERROR命令来提示源码的错误位置；

l  使用 user_errors 数据字典来查看各存储过程的错误位置。

 

§6.3.5.4  授权执行权给相关的用户或角色

如果调式正确的存储过程没有进行授权，那就只有建立者本人才可以运行。所以作为应用系统的一部分的存储过程也必须进行授权才能达到要求。在SQL*PLUS下可以用GRANT命令来进行存储过程的运行授权。

 

GRANT语法：

GRANT system_privilege | role

TO user | role | PUBLIC [WITH ADMIN OPTION]

 

GRANT object_privilege | ALL ON schema.object

TO user | role | PUBLIC [WITH GRANT OPTION]

 

例子：

 

CREATE OR REPLACE PUBLIC SYNONYM dbms_job FOR dbms_job

 

GRANT EXECUTE ON dbms_job TO PUBLIC WITH GRANT OPTION

 

§6.3.5.5  与过程相关数据字典

 

USER_SOURCE, ALL_SOURCE, DBA_SOURCE, USER_ERRORS

 

相关的权限:

CREATE ANY PROCEDURE

DROP ANY PROCEDURE

 

在SQL*PLUS 中，可以用DESCRIBE 命令查看过程的名字及其参数表。

 

DESCRIBE Procedure_name;

 

§6.3.6  删除过程和函数

 

1．删除过程

可以使用DROP PROCEDURE命令对不需要的过程进行删除，语法如下：

 

DROP PROCEDURE [user.]Procudure_name;

 

2．删除函数

可以使用DROP FUNCTION 命令对不需要的函数进行删除，语法如下：

 

DROP FUNCTION [user.]Function_name;

 

DROP PROCEDURE logexecution;

DROP PROCEDURE delemp;

DROP PROCEDURE insertemp;

DROP PROCEDURE fireemp;

DROP PROCEDURE queryemp;

DROP PROCEDURE proc_demo;

DROP PROCEDURE log_message;

DROP FUNCTION demo_fun;

DROP FUNCTION get_salary;