Packages (Learning notes)

Example One, find all employees in this department based on the department number entered

First create the package, define the package specification

Create or Replace  is  FUNCTION getemp_fun (p_dno dept.deptno%return-- returns weakly typed cursor  end getemp_pkg;

Automatically generate the body of the package after execution

Define the body of the package (Implementation body)

Create or ReplacePackage Body Getemp_pkg asFUNCTIONGetemp_fun (P_dno Dept.deptno%TYPE)RETURNSys_refcursor--returns a weakly typed cursor ascur_emp sys_refcursor;begin   OPENCur_emp for SELECT *  fromEmpWHEREDeptno=P_dno;--Open Cursor   RETURNCur_emp;--returns a cursor   ENDGetemp_fun;Endgetemp_pkg;

Call Package

DECLAREv_emp EMP%ROWTYPE;--defining variables to receive the contents of a cursorCur_emp Sys_refcursor;--to define a cursor to receive a return cursorV_dno DEPT. DEPTNO%TYPE;--Define department number, transfer to parameterBEGINcur_emp:=Getemp_pkg. Getemp_fun (&V_dno); LOOPFETCHCur_emp--Extracting Cursors       intov_emp; EXIT  whenCur_emp%NOTFOUND;--Exit CriteriaDbms_output. Put_Line (cur_emp%ROWCOUNT || 'Employee Number:' ||V_emp. EMPNO||                         'Name:' ||v_emp.  ENAME); ENDLOOP; CLOSECur_emp;--Close CursorsEND;

example Two, there are procedures and functions in the package

Package Specifications

Create or Replace  is PROCEDURE  Number ); FUNCTION  Number RETURN VARCHAR2 ; End Pack_me;

Package body

Create or ReplacePackage Body Pack_me isPROCEDUREEmp_proc (num Number) isV_nameVARCHAR2( -); BEGIN    SELECTEname intoV_name fromEmpWHEREEmpno=num; Dbms_output.put_line ('Employee Number:'||Num||''s name:'||v_name); ENDEmp_proc; FUNCTIONEmp_fun (Eno Number)RETURN VARCHAR2     asV_jobvarchar2( -); BEGIN       SELECTJob intoV_job fromEmpWHEREEmpno=Eno; RETURNV_job; --Dbms_output.put_line (' Employee ID: ' | | empno| | ' position: ' | | V_job);    ENDEmp_fun;EndPack_me;

Perform

--PackageDECLAREv_empno Emp.empno%TYPE:=&empno; V_jobVarchar2( -);BEGINPack_me.emp_proc (v_empno); V_job:=Pack_me.emp_fun (v_empno);d Bms_output.put_line ('Employee Number:'||V_empno||'Position:'||v_job);END;

Query Package

-- Query User_objects Data Dictionary Confirmation Package specification and package body SELECT object_type,object_namefrom theWHEREin ('  Package','packageBODY');

-- querying the contents of a package SELECT *  from WHERE TYPE='package' and NAME='getemp_pkg  ';

Delete Package
Remove Package Specification

DROP Package Name

Delete Package Body

DROP Package Body Pack Name

Deleting the package specification will delete the corresponding package.

The recompilation of the package

ALTER package name COMPILE packages; -- Example ALTER Package getemp_pkg COMPILE Package;

The purity level of the package

Grammar:
PRAGMA restrict_references (function name, wnds[,wnps][,rnds][,rups])

NO	Purity Grade	Description
1	Wnds	function cannot leisurely database table data (cannot be updated with DML)
2	Rnds	function cannot read database table (cannot use select query)
3	Wnps	function does not allow the contents of variables in the package
4	Rnps	function does not allow reading of variable contents in a package

Packages (Learning notes)