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;
(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;