Package (study notes) and study notes

Package (study notes) and study notes

Example 1,Locate all employees in the department according to the entered Department number.

First, create a package and define the package specifications.

Create or replace package getemp_pkg is FUNCTION getemp_fun (p_dno dept. deptno % type) RETURN SYS_REFCURSOR; -- RETURN weak type cursor end getemp_pkg;

Automatically generate the subject of the package after execution

Define the subject of the package (implementation body)

Create or replace package body getemp_pkg ASFUNCTION getemp_fun (p_dno dept. deptno % TYPE) RETURN SYS_REFCURSOR -- RETURN weak TYPE cursor AScur_emp SYS_REFCURSOR; begin OPEN cur_emp for select * FROM emp WHERE deptno = p_dno; -- OPEN the cursor RETURN cur_emp; -- RETURN the cursor END getemp; end getemp_pkg;

Call package

DECLARE V_EMP EMP % ROWTYPE; -- defines the variable to receive the cursor content CUR_EMP SYS_REFCURSOR; -- defines the cursor to receive the returned cursor V_DNO DEPT. DEPTNO % TYPE; -- Define the Department number. The transfer parameter BEGIN CUR_EMP: = GETEMP_PKG.GETEMP_FUN (& V_DNO); loop fetch CUR_EMP -- extract the cursor INTO V_EMP; exit when CUR_EMP % NOTFOUND; -- exit condition DBMS_OUTPUT.PUT_LINE (CUR_EMP % ROWCOUNT | 'employee ID: '| V_EMP.EMPNO | 'name:' | V_EMP.ENAME); end loop; CLOSE cur_emp; -- CLOSE the cursor END;

 

Example 2,Procedures and functions in the package

Package specification

create or replace package pack_me isPROCEDURE emp_proc(num NUMBER);FUNCTION emp_fun(eno NUMBER) RETURN VARCHAR2;end pack_me;

Package subject

Create or replace package body pack_me isPROCEDURE emp_proc (num NUMBER) IS v_name VARCHAR2 (50); begin select ename INTO v_name FROM emp WHERE empno = num; dbms_output.put_line ('employee ID: '| num |' name: '| v_name); END emp_proc; FUNCTION emp_fun (eno NUMBER) RETURN VARCHAR2 AS v_job varchar2 (50 ); begin select job INTO v_job FROM emp WHERE empno = eno; RETURN v_job; -- dbms_output.put_line ('employee ID: '| empno |' position: '| v_job ); END emp_fun; end pack_me;

Run

-- Package DECLARE v_empno emp. empno % TYPE: = & empno; v_job Varchar2 (50); BEGIN pack_me.emp_proc (v_empno); v_job: = pack_me.emp_fun (v_empno); dbms_output.put_line ('employee No: '| v_empno |' position: '| v_job); END;

Query package

-- Query the user_objects data dictionary validation PACKAGE specification and package body select object_type, object_name, status FROM User_Objects WHERE object_type IN ('package', 'package body ');

 

-- Query the PACKAGE content SELECT * FROM user_source where type = 'package' and name = 'getemp _ PKG ';

Delete package
Delete package specification

Drop package name

Delete a package

Drop package body package name

Deleting a package will delete the corresponding package together.

Package re-Compilation

Alter package name compile package; -- Example: alter package GETEMP_PKG compile package;

Purity level of Package

Syntax:
PRAGMA restrict_references (function name, WNDS [, WNPS] [, RNDS] [, RUPS])

NO	Purity level	Description
1	WNDS	Functions cannot affect database table data (cannot be updated using DML)
2	RNDS	Functions cannot read database tables (SELECT queries cannot be used)
3	WNPS	The function does not allow variable content in the bag.
4	RNPS	The function cannot read the variable content in the package.