1、包的概述
a、什么是包?
@H_301_4@b、包的特点?
@H_301_4@c、包中的程序元素
@H_301_4@e、包的组成
@H_301_4@f、简单代码示例
@H_301_4@--创建包规范 CREATE OR REPLACE PACKAGE first_package IS v_no emp.deptno%TYPE := 10; --过期 PROCEDURE query_emp(v_deptno IN NUMBER DEFAULT v_no,v_avgsal OUT NUMBER,v_cnt OUT NUMBER); END first_package; --创建包体 CREATE OR REPLACE PACKAGE BODY first_package IS PROCEDURE query_emp(v_deptno IN NUMBER DEFAULT v_no,v_avgsal) OUT NUMBER,v_cnt OUT NUMBER) IS BEGIN SELECT avg(sal),count(*) INTO v_avgsal,v_cnt FROM emp WHERE deptno = v_deptno; EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('没有此部门'); WHEN OTHERS THEN dbms_output.put_line(sqlERRM); END; END first_package; ``` - 调用包中的存储过程:
DECLARE v_avgsal NUMBER; v_cnt NUMBER; BEGIN first_package.query_emp(20,v_avgsal,v_cnt); DBMS_OUTPUT.put_line('平均工资:' || v_avgsal); DBMS_OUTPUT.put_line('总人数:' || v_cnt); END;
# 2、包的创建 ## a、创建包规范 - 语法格式如下:
CREATE [OR REPLACE] PACKAGE package_name IS | AS -- 定义公用常量、变量、游标、过程、函数等 END [package_name];
- 示例代码: ``` CREATE OR REPLACE PACKAGE emp_package IS --添加员工信息的存储过程 PROCEDURE add_emp_proc (v_empno IN emp.empno%TYPE,v_ename IN emp.ename%TYPE,v_sal IN emp.sal%TYPE,v_deptno IN emp.deptno%TYPE); --删除员工信息的存储过程 PROCEDURE del_emp_proc (v_empno IN emp.empno%TYPE); END emp_package;
b、创建包体
@H_301_4@CREATE [OR REPLACE] PACKAGE BODY package_name IS | AS --定义私有常量、变量、游标、过程和函数等 --实现公用过程和函数 END [package_name];@H_301_4@
CREATE OR REPLACE PACKAGE BODY emp_package IS --添加员工信息的存储过程 PROCEDURE add_emp_proc (v_empno IN emp.empno%TYPE,v_deptno IN emp.deptno%TYPE) IS e_2291 EXCEPTION; PRAGMA EXCEPTION_INIT(e_2291,-2291); BEGIN INSERT INTO emp(empno,ename,sal,deptno) VALUES(v_empno,v_ename,v_sal,v_deptno); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN RAISE_APPLICATION_ERROR(-20001,'员工号不能重复'); WHEN e_2291 THEN RAISE_APPLICATION_ERROR(-20002,'部门号不存在'); END; --删除员工信息的存储过程 PROCEDURE del_emp_proc (v_empno IN emp.empno%TYPE) IS BEGIN --根据员工号删除指定的员工信息 DELETE FROM emp WHERE empno = v_empno; --判断是否删除成功 IF sql%NOTFOUND THEN RAISE_APPLICATION_ERROR(-20009,'指定删除的员工不存在'); ELSE DBMS_OUTPUT.PUT_line('删除成功'); END IF; END; END emp_package;
c、包创建的过程案例代码
@H_301_4@--创建包规范 CREATE OR REPLACE PACKAGE emp_sal_pkg IS FUNCTION get_sal(eno NUMBER) RETURN NUMBER; PROCEDURE upd_sal(eno NUMBER,salary NUMBER); END emp_sal_pkg; --包体 CREATE OR REPLACE PACKAGE BODY emp_sal_pkg IS FUNCTION get_sal(eno NUMBER) RETURN NUMBER IS v_sal emp.sal%TYPE := 0; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = eno; RETURN v_sal; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20010,'此员工号不存在'); END; PROCEDURE upd_sal(eno NUMBER,salary NUMBER) IS BEGIN IF salary <=3000 THEN UPDATE emp SET sal = sal + 500 WHERE empno = eno; END IF; END; END emp_sal_pkg;
3、包的调用和删除
a、包的调用
@H_301_4@b、 包的调用示例1
@H_301_4@DECLARE v_empno emp.empno%TYPE := &empno; v_ename emp.ename%TYPE := '&name'; v_sal emp.sal%TYPE := &salary; v_deptno emp.deptno%TYPE := &deptno; e_dup_val EXCEPTION; e_no_dept EXCEPTION; PRAGMA EXCEPTION_INIT(e_dup_val,-20001); PRAGMA EXCEPTION_INIT(e_no_dept,-20002); BEGIN emp_package.add_emp_proc(v_empno,v_deptno); COMMIT; EXCEPTION WHEN e_dup_val THEN DBMS_OUTPUT.put_line(sqlERRM); WHEN e_no_dept THEN DBMS_OUTPUT.put_line(sqlERRM); ROLLBACK; END;
b、包的调用示例2
@H_301_4@DECLARE v_empno emp.empno%TYPE := &empno; e_no_emp EXCEPTION; PRAGMA EXCEPTION_INIT(e_no_emp,-20009); BEGIN emp_package.del_emp_proc(v_empno); COMMIT; EXCEPTION WHEN e_no_emp THEN DBMS_OUTPUT.put_line(sqlERRM); ROLLBACK; END;
c、包的调用示例3
@H_301_4@DECLARE v_empno emp.empno%TYPE := &empno; v_salary emp.sal%TYPE; e_no_emp EXCEPTION; PRAGMA EXCEPTION_INIT(e_no_emp,-20010); BEGIN v_salary := emp_sal_pkg.get_sal(v_empno); emp_sal_pkg.upd_sal(v_empno,v_salary); COMMIT; EXCEPTION WHEN e_no_emp THEN DBMS_OUTPUT.put_line(sqlERRM); END;
d、如何在sqlPlus窗口中进行包的调用
@H_301_4@sql> VAR v_empno NUMBER sql> EXEC :v_empno := &no PL/sql procedure successfully completed v_empno --------- 1234 sql> VAR v_salary NUMBER sql> EXEC :v_salary := emp_sal_pkg.get_sal(:v_empno) begin :v_salary := emp_sal_pkg.get_sal(:v_empno); end; ORA-20010: 此员工号不存在 ORA-06512: 在 "SCOTT.EMP_SAL_PKG",line 11 ORA-06512: 在 line 1 v_salary --------- v_empno --------- 1234@H_301_4@
sql> EXEC :v_empno := &no PL/sql procedure successfully completed v_empno --------- 7369 sql> EXEC :v_salary := emp_sal_pkg.get_sal(:v_empno) PL/sql procedure successfully completed v_salary --------- 800 v_empno --------- 7369 sql> EXEC emp_sal_pkg.upd_sal(:v_empno,:v_salary) PL/sql procedure successfully completed v_empno --------- 7369 v_salary --------- 800 sql> SELECT ename,sal from emp WHERE empno = 7369; ENAME SAL ---------- --------- G_EASON 1300.00@H_301_4@
e、包的删除
@H_301_4@DROP PACKAGE [BODY] [user.] package_name;
4、子程序重载
a、什么是子程序重载?
@H_301_4@b、示例代码
@H_301_4@--创建包规范 CREATE OR REPLACE PACKAGE overload_pkg IS FUNCTION get_info(eno NUMBER) RETURN emp%ROWTYPE; FUNCTION get_info(name VARCHAR) RETURN emp%ROWTYPE; PROCEDURE del_emp(eno NUMBER); PROCEDURE del_emp(name VARCHAR); END; --创建包体 CREATE OR REPLACE PACKAGE BODY overload_pkg IS FUNCTION get_info(eno NUMBER) RETURN emp%ROWTYPE IS emp_record emp%ROWTYPE; BEGIN SELECT * INTO emp_record FROM emp WHERE empno = eno; RETURN emp_record; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20020,'不存在此员工'); END; FUNCTION get_info (name VARCHAR) RETURN emp%ROWTYPE IS emp_record emp%ROWTYPE; BEGIN SELECT * INTO emp_record FROM emp WHERE ename= name; RETURN emp_record; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20020,'不存在此员工'); END; PROCEDURE del_emp(eno NUMBER) IS BEGIN DELETE FROM emp WHERE empno = eno; IF sql%NOTFOUND THEN RAISE_APPLICATION_ERROR(-20020,'不存在此员工'); END IF; END; PROCEDURE del_emp(name VARCHAR) IS BEGIN DELETE FROM emp WHERE ename = name; IF sql%NOTFOUND THEN RAISE_APPLICATION_ERROR(-20020,'不存在此员工'); END IF; END; END overload_pkg;@H_301_4@
--根据员工号查询员工信息 DECLARE emp_record emp%rowtype; e_no_emp EXCEPTION; PRAGMA EXCEPTION_INIT(e_no_emp,-20020); BEGIN emp_record := overload_pkg.get_info(&no); DBMS_OUTPUT.put_line('员工号:' || emp_record.empno || ',姓名:' || emp_record.ename || ',工资:' || emp_record.sal); EXCEPTION WHEN e_no_emp THEN DBMS_OUTPUT.put_line(sqlERRM); END;
--根据员工姓名查询员工信息 DECLARE emp_record emp%rowtype; e_no_emp EXCEPTION; PRAGMA EXCEPTION_INIT(e_no_emp,-20020); BEGIN emp_record := overload_pkg.get_info('&ename'); DBMS_OUTPUT.put_line('员工号:' || emp_record.empno || ',姓名:' || emp_record.ename || ',工资:' || emp_record.sal); EXCEPTION WHEN e_no_emp THEN DBMS_OUTPUT.put_line(sqlERRM); END;
--根据员工号删除员工信息 DECLARE e_no_emp EXCEPTION; PRAGMA EXCEPTION_INIT(e_no_emp,-20020); BEGIN overload_pkg.del_emp(&no); COMMIT; EXCEPTION WHEN e_no_emp THEN DBMS_OUTPUT.put_line(sqlERRM); ROLLBACK; END;
--根据员工姓名删除员工信息 DECLARE e_no_emp EXCEPTION; PRAGMA EXCEPTION_INIT(e_no_emp,-20020); BEGIN overload_pkg.del_emp('&ename'); COMMIT; EXCEPTION WHEN e_no_emp THEN DBMS_OUTPUT.put_line(sqlERRM); ROLLBACK; END;原文链接:https://www.f2er.com/oracle/206713.html