oracle学习笔记(6)-函数和包

函数:
函数的创建:
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;
IS 
    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 DEPARTMENT DOES NOT EXISTS !')
END return_maxsal;

函数的管理:
函数的修改
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;




包:
包是包含一个或多个子程序单元（过程、函数等）的容器。
包是一种全局结构 。
包类型
数据库内置包 ,
用户创建的包
包构成
包规范,
包体两
包规范声明了软件包中所有内容，如过程、函数、游标、类型、异常和变量等，其中过程和函数只包括原型信息，不包含任何子程序代码。  
包体中包含了在包头中的过程和函数的实现代码。包体中还可以包括在规范中没有声明的变量、游标、类型、异常、过程和函数，但是它们是私有元素，只能由同一包体中其他过程和函数使用。


创建一个软件包，包括2个变量、2个过程和1个异常。
包规范:

CREATE OR REPLACE PACKAGE pkg_emp
AS
  minsal   NUMBER;
  maxsal   NUMBER;
  e_beyondbound  EXCEPTION;
  PROCEDURE update_sal(
           p_empno NUMBER, p_sal NUMBER);
  PROCEDURE add_employee(
           p_empno NUMBER,p_sal NUMBER);
END pkg_emp;

包体:

CREATE OR REPLACE PACKAGE BODY pkg_emp
AS
    PROCEDURE update_sal(p_empno NUMBER, p_sal NUMBER)
    AS
    BEGIN
      SELECT min(sal), max(sal) INTO minsal,maxsal FROM emp;
      IF p_sal BETWEEN minsal AND maxsal THEN
        UPDATE emp SET sal=p_sal WHERE empno=p_empno;
        IF SQL%NOTFOUND THEN
          RAISE_APPLICATION_ERROR(-20000,'The employee 
                                                                 doesn''t exist');
        END IF;
      ELSE
        RAISE e_beyondbound;
      END IF;
   EXCEPTION
      WHEN e_beyondbound THEN
         DBMS_OUTPUT.PUT_LINE('The salary is beyond bound! ');
    END update_sal; 
	PROCEDURE add_employee(p_empno NUMBER,p_sal NUMBER)
	AS
	BEGIN
      SELECT min(sal), max(sal) INTO minsal,maxsal FROM emp;
      IF p_sal BETWEEN minsal AND maxsal THEN
         INSERT INTO emp(empno,sal) VALUES(p_empno,p_sal);
      ELSE
         RAISE e_beyondbound;
      END IF;
	EXCEPTION
    WHEN e_beyondbound THEN
      DBMS_OUTPUT.PUT_LINE('The salary is beyond bound! ');
	END add_employee;
END pkg_emp;