11-Oracle函数与局部子程序

本文章主要讲解如下内容：

1、函数的创建

2、函数的调用

3、函数的管理

（1）函数的创建

基本语法为

  CREATE [OR REPLACE] FUNCTION function_name

  (parameter1_name [mode] datatype

      [DEFAULT|:=value]

  [, parameter2_name [mode] datatype

      [DEFAULT|:=value], … ])

  RETURN return_datatype

  AS|IS

       /*Declarative section is here */

  BEGIN

      /*Executable section is here*/

  EXCEPTION

      /*Exception section is here*/

  END [ function_name ];

注意

  在函数定义的头部，参数列表之后，必须包含一个 RETURN 语句来指明函数返回值的类型，但不能约束返回值的长度、精度、刻度等。如果使用 %TYPE ，则可以隐含地包括长度、精度、刻度等约束信息；

  在函数体的定义中，必须至少包含一个 RETURN 语句，来指明函数返回值。也可以有多个 RETURN 语句，但最终只有一个 RETURN 语句被执行。

创建一个以部门号为参数，返回该部门最高工资的函数。

CREATE OR REPLACE FUNCTION return_maxsal
(p_deptno emp.deptno%TYPE)
RETURN emp.sal%TYPE
AS
    v_maxsal emp.sal%TYPE;
BEGIN
   SELECT max(sal) INTO v_maxsal FROM emp 
                           WHERE deptno=p_deptno;
   RETURN v_maxsal;
EXCEPTION 
   WHEN NO_DATA_FOUND THEN
       DBMS_OUTPUT.PUT_LINE('The deptno is invalid!');
END return_maxsal;

如果需要函数返回多个值，可以使用 OUT 或 IN OUT 模式参数。

创建一个函数，以部门号为参数，返回部门名、部门人数及部门平均工资。

CREATE OR REPLACE FUNCTION ret_deptinfo(
p_deptno dept.deptno%TYPE,
p_num OUT NUMBER,
p_avg OUT NUMBER)
RETURN dept.dname%TYPE
AS
  v_dname dept.dname%TYPE;
BEGIN
  SELECT dname INTO v_dname FROM dept 
  WHERE deptno=p_deptno;
  SELECT count(*),avg(sal) INTO p_num,p_avg 
  FROM emp WHERE deptno=p_deptno;
  RETURN v_dname;
END ret_maxsal; 

（2）函数的调用

在 SQL 语句中调用函数

在 PL/SQL 中调用函数

注意

  函数只能作为表达式的一部分被调用。

示例

  通过 return_maxsal 函数的调用，输出各个部门的最高工资；通过 ret_deptinfo 函数调用，输出各个部门名、部门人数及平均工资。

DECLARE
  v_maxsal emp.sal%TYPE;
  v_avgsal emp.sal%TYPE;
  v_num    NUMBER;
  v_dname  dept.dname%TYPE;
BEGIN
  FOR v_dept IN (SELECT DISTINCT deptno FROM emp) LOOP
      v_maxsal:=ret_maxsal(v_dept.deptno);
      v_dname:=ret_deptinfo(v_dept.deptno,v_num,v_avgsal);
      DBMS_OUTPUT.PUT_LINE(v_dname||' '||v_maxsal||' '|| v_avgsal||' '||v_num);
  END LOOP;
END;

函数可以在 SQL 语句的以下部分调用：

  SELECT 语句的目标列；

  WHERE 和 HAVING 子句；

  CONNECT BY ， START WITH ， ORDER BY ， GROUP BY 子句；

  INSERT 语句的 VALUES 子句中；

  UPDATE 语句的 SET 子句中。

如果要在 SQL 中调用函数，那么函数必须符合下列限制和要求：

  在 SELECT 语句中的函数不能修改（ INSERT ， UPDATE ， DELETE ）调用函数的 SQL 语句中使用的表；

  函数在一个远程或并行操作中使用时，不能读 / 写封装变量；

  函数必须是一个存储数据库对象（或存储在包中）；

  函数的参数只能使用 IN 模式；

  形式参数类型必须使用数据库数据类型；

  返回的数据类型必须是数据库数据类型；

（3）函数的管理

• 函数的修改

  CREATE OR REPLACE FUNCTION function_name

• 查看函数及其源代码

  查询数据字典视图 USER_SOURCE

  SELECT name,text FROM user_source

   WHERE type='FUNCTION';

• 函数重编译

  ALTER FUNCTION … COMPILE

  ALTER FUNCTION ret_maxsal COMPILE;

• 删除函数

  DROP FUNCTION

  DROP FUNCTION ret_maxsal ;

局部子程序

  嵌套在其他 PL/SQL 块中的子程序。

  只能在其定义的块内部被调用，而不能在其父块外被调用。

 使用局部子程序时需要注意：

  局部子程序只在当前语句块内有效；

  局部子程序必须在 PL/SQL 块声明部分的最后进行定义；

  局部子程序必须在使用之前声明，如果是子程序间相互引用，则需要采用预先声明；

  局部子程序可以重载。

        在一个块内部定义一个函数和一个过程。函数以部门号为参数返回该部门的平均工资；过程以部门号为参数，输出该部门中工资低于部门平均工资的员工的员工号、员工名。

     DECLARE
         v_deptno emp.deptno%TYPE;
         v_avgsal emp.sal%TYPE;
     FUNCTION return_avgsal(p_deptno emp.deptno%TYPE)
         RETURN emp.sal%TYPE
     AS
        v_sal emp.sal%TYPE; 
     BEGIN
        SELECT avg(sal) INTO v_sal FROM emp
        WHERE  deptno=p_deptno;
        RETURN v_sal;
     END return_avgsal;  

    PROCEDURE show_emp(p_deptno emp.deptno%TYPE)
    AS 
        CURSOR c_emp IS
            SELECT * FROM emp WHERE deptno=p_deptno;
    BEGIN
        FOR v_emp IN c_emp LOOP
            IF v_emp.sal<return_avgsal(v_emp.deptno) THEN
                 DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '|| v_emp.ename);
            END IF;
        END LOOP;
   END show_emp;



   BEGIN
       v_deptno:=&x;
       v_avgsal:=return_avgsal(v_deptno);
       show_emp(v_deptno);
   END;

存储子程序与局部子程序区别在于：

  存储子程序己经编译好放在数据库服务器端，可以直接调用，而局部子程序存在于定义它的语句块中，在运行时先进行编译；

  存储子程序不能重载，而局部子程序可以进行重载；

  存储子程序可以被任意的 PL/SQL 块调用，而局部子程序只能在定义它的块中被调用。

        在一个PL/SQL块中重载两个过程，一个以员工号为参数，输出该员工信息；另一个以员工名为参数，输出员工信息。利用这两个过程分别查询员工号为7902，7934，以及员工名为SMITH，FORD的员工信息 。

DECLARE
    PROCEDURE show_empinfo(p_empno emp.empno%TYPE)
    AS
        v_emp emp%ROWTYPE; 
    BEGIN
        SELECT * INTO v_emp FROM emp 
        WHERE empno=p_empno;
        DBMS_OUTPUT.PUT_LINE(v_emp.ename||' '|| v_emp.deptno);
    EXCEPTION 
        WHEN NO_DATA_FOUND THEN
           DBMS_OUTPUT.PUT_LINE('There is not such an employee!');
    END show_empinfo; 

PROCEDURE show_empinfo(p_ename emp.ename%TYPE)
  AS
     v_emp emp%ROWTYPE; 
  BEGIN
     SELECT * INTO v_emp FROM emp 
     WHERE ename=p_ename;
     DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '|| v_emp.deptno);
  EXCEPTION  
      WHEN NO_DATA_FOUND THEN
       DBMS_OUTPUT.PUT_LINE('There is not such an employee!');
      WHEN TOO_MANY_ROWS THEN
       DBMS_OUTPUT.PUT_LINE('There are more than one employee!');
  END show_empinfo;

BEGIN
  show_empinfo(7902);
  show_empinfo(7934);
  show_empinfo('SMITH');
  show_empinfo('FORD');
END ;