Shulou(Shulou.com)06/01 Report--

1. An overview of the package a. What is a package? A package is a combination of PL/SQL programming elements such as related procedures, functions, variables, constants, and cursors. B. what are the characteristics of the bag? It has the characteristics of object-oriented programming language and is the encapsulation of PL/SQL programming elements (processes, functions, variables, etc.). It modularizes programming. C, program elements in the package are divided into two kinds: public elements (public components) and private elements (private components). E, package composition A package consists of two separate parts: the package specification (package definition): the common components used to define the package, including constants, variables, cursors, procedures, and functions. Package body (package body): used to implement common procedures and functions defined by the package specification. The package body can be used not only to implement public procedures and functions, but also to define the private components of the package (variables, cursors, procedures, functions, etc.). F, simple code sample create package specification and sample code for package body-create package specification CREATE OR REPLACE PACKAGE first_packageIS v_no emp.deptno%TYPE: = 10;-- expired PROCEDURE query_emp (v_deptno IN NUMBER DEFAULT v_no, v_avgsal OUT NUMBER, v_cnt OUT NUMBER); END first_package -- create packet CREATE OR REPLACE PACKAGE BODY first_packageIS 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 ('no department'); WHEN OTHERS THEN dbms_output.put_line (SQLERRM); END;END first_package; calls the stored procedure in the package: DECLARE v_avgsal NUMBER; v_cnt NUMBER BEGIN first_package.query_emp (20, v_avgsal, v_cnt); DBMS_OUTPUT.put_line ('average salary:' | | v_avgsal); DBMS_OUTPUT.put_line ('Total number:' | | v_cnt); END;2, package creation a, package creation specification syntax format is as follows: CREATE [OR REPLACE] PACKAGE package_nameIS | AS-- defines END for common constants, variables, cursors, procedures, functions, etc. [package_name]

Sample code:

CREATE OR REPLACE PACKAGE emp_packageIS-A stored procedure 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) to add employee information -- delete the stored procedure PROCEDURE del_emp_proc (v_empno IN emp.empno%TYPE) of employee information; END emp_package;b, create the package syntax format as follows: CREATE [OR REPLACE] PACKAGE BODY package_name IS | AS-- defines private constants, variables, cursors, procedures and functions, etc.-- implement public procedures and functions END [package_name]

Sample code:

CREATE OR REPLACE PACKAGE BODY emp_packageIS-add stored procedures PROCEDURE add_emp_proc (v_empno IN emp.empno%TYPE, v_ename IN emp.ename%TYPE, v_sal IN emp.sal%TYPE) for employee information V_deptno IN emp.deptno%TYPE) IS eBay 2291 EXCEPTION PRAGMA EXCEPTION_INIT (eBay 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, 'employee ID cannot be duplicated') WHEN eBay 2291 THEN RAISE_APPLICATION_ERROR (- 20002, 'Department number does not exist'); END -- delete the stored procedure PROCEDURE del_emp_proc (v_empno IN emp.empno%TYPE) IS BEGIN of employee information-- delete the specified employee information DELETE FROM emp WHERE empno = v_empno based on the employee number -determine whether the IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR was deleted successfully (- 20009, 'the specified deleted employee does not exist'); ELSE DBMS_OUTPUT.PUT_line ('deleted successfully'); END IF; END;END emp_package C. The case code for the process of package creation

Check the salary according to the employee number. If the salary is less than or equal to 3000, the salary will rise by 500.

-- create package specification CREATE OR REPLACE PACKAGE emp_sal_pkgIS FUNCTION get_sal (eno NUMBER) RETURN NUMBER; PROCEDURE upd_sal (eno NUMBER, salary NUMBER); END emp_sal_pkg;-- package body CREATE OR REPLACE PACKAGE BODY emp_sal_pkgIS 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 vault salar; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (- 20010 'this employee number does not exist'); END PROCEDURE upd_sal (eno NUMBER, salary NUMBER) IS BEGIN IF salary VAR v_empno NUMBERSQL > EXEC: v_empno: = & noPL/SQL procedure successfully completedv_empno-1234SQL > VAR v_salary NUMBERSQL > EXEC: v_salary: = emp_sal_pkg.get_sal (: v_empno) begin: v_salary: = emp_sal_pkg.get_sal (: v_empno); end ORA-20010: this employee number does not exist ORA-06512: in "SCOTT.EMP_SAL_PKG", line 11ORA-06512: in line 1v_salary-v_empno-1234

Example 2: call the emp_sal_pkg package in the SQLPlus window to update the employee's salary information: (the variable v_empno has been defined in example 1)

SQL > EXEC: v_empno: = & noPL/SQL procedure successfully completedv_empno-7369SQL > EXEC: v_salary: = emp_sal_pkg.get_sal (: v_empno) PL/SQL procedure successfully completedv_salary-800v_empno-7369SQL > EXEC emp_sal_pkg.upd_sal (: v_empno : v_salary) PL/SQL procedure successfully completedv_empno-7369v_salary-800SQL > SELECT ename, sal from emp WHERE empno = 7369 ENAME SAL--G_EASON 1300.00 in the SQLPlus environment, you can use the [VAR variable name variable Type] command to define variables. E. Delete packages can use the DROP PACKAGE command to delete unwanted packages. The syntax is as follows: DROP PACKAGE [BODY] [user.] Package_name;4, subroutine overloading a, what is subroutine overloading? Overloading means that two or more subroutines have the same name, but have different parameter variables, parameter order, or parameter data types. B, sample code

Example: 1. Get employee information according to employee number or employee name; 2. Delete employee information according to employee number or employee name.

-- create package specification CREATE OR REPLACE PACKAGE overload_pkgIS 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 the package CREATE OR REPLACE PACKAGE BODY overload_pkgIS 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, 'this employee does not exist'); 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, 'this employee does not exist'); END PROCEDURE del_emp (eno NUMBER) IS BEGIN DELETE FROM emp WHERE empno = eno; IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR (- 20020, 'this employee does not exist'); END IF; END PROCEDURE del_emp (name VARCHAR) IS BEGIN DELETE FROM emp WHERE ename = name; IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR (- 20020, 'this employee does not exist'); END IF; END;END overload_pkg Example of calling overload_pkg package:-- query employee information by employee number 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 ('employee number:' | emp_record.empno | |, name:'| | emp_record.ename |, salary:'| | emp_record.sal) EXCEPTION WHEN e_no_emp THEN DBMS_OUTPUT.put_line (SQLERRM); END;-- queries employee information based on employee name 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 ('employee number:' | emp_record.empno | |', name:'| | emp_record.ename | |', salary:'| | emp_record.sal); EXCEPTION WHEN e_no_emp THEN DBMS_OUTPUT.put_line (SQLERRM); END;-- deletes employee information DECLARE e_no_emp EXCEPTION; PRAGMA EXCEPTION_INIT (e_no_emp,-20020); BEGIN overload_pkg.del_emp (& no) based on employee number COMMIT;EXCEPTION WHEN e_no_emp THEN DBMS_OUTPUT.put_line (SQLERRM); ROLLBACK;END;-- deletes employee information DECLARE e_no_emp EXCEPTION; PRAGMA EXCEPTION_INIT (e_no_emp,-20020) based on employee name; BEGIN overload_pkg.del_emp ('& ename'); COMMIT;EXCEPTION WHEN e_no_emp THEN DBMS_OUTPUT.put_line (SQLERRM); ROLLBACK;END

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.