oracle中的package

oracle中的package
(2006-12-21 20:57:46)
转载
  分类：oracle学习

oracle中将function以及procedure单独运行与将其放入package中有何不同：

package提供了方便的管理，可以把对于一张表的所有操作，都放到一个包中。最大的优势在于你可以在包中声明一个“游标变量”类型，包中的存储过程就可以用该游标类型的变量“返回记录集”，oracle和sql server的最大不同就是不能直接用select语句返回记录集的，必须用游标。

下例emp_actions package包含两个procedures和一个函数：

CREATE OR REPLACE PACKAGE emp_actions AS

  PROCEDURE hire_employee (employee_id NUMBER, last_name VARCHAR2, first_name VARCHAR2, email VARCHAR2, phone_number VARCHAR2, hire_date DATE, job_id VARCHAR2, salary NUMBER, commission_pct NUMBER, manage_id NUMBER, department_id NUMBER);

PROCEDURE fire_employee (emp_id NUMBER);

FUNCTION num_above_salary (emp_id NUMBER) RETURN NUMBER;

END emp_actions;

CREATE OR REPLACE PACKAGE BOOY emp_actions AS

  PROCEDURE hire_employee (employee_id NUMBER, last_name VARCHAR2, first_name VARCHAR2, email VARCHAR2, phone_number VARCHAR2, hire_date DATE, job_id VARCHAR2, salary NUMBER, commission_pct NUMBER, manage_id NUMBER, department_id NUMBER) IS

  BEGIN

    INSERT INTO employees VALUES (employee_id, last_name, first_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id);

    END hire_employee;

  PROCEDURE fire_employee (emp_id NUMBER) IS

    BEGIN

      DELETE FROM employees WHERE employee_id = emp_id;

    END fire_employee;

  FUNCTION num_above_salary (emp_id NUMBER) RETURN NUMBER IS

      emp_sal NUMBER(8,2);

      num_count NUMBER;

  BEGIN

    SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;

    SELECT COUNT(*) INTO num_count FROM employees WHERE salary > emp_sal;

    RETURN num_count;

  END num_above_salary;

END emp_actions;