PACKAGESA package is a schema object that groups logically related PL/SQL types, variables, and subprograms. Packages usually have two parts: Enable the server to read multiple objects into memory at once. A PL/SQL Packages construct is referenced for first time, the whole package is loaded into memory. Subsequent access to constructs in the same package does not require disk input/output(I/O). Advantages of Package : Package : package specification Package package_name variable Procedure1 declaration; Procedure 2 declaration; End:Package body : Package body package_body name variable Procedure 1 definition begin statement: end Procdure 2 definition begin statement; end; End;Package : Specification : SQL> create or replace package pg as function ff return char; procedure pp; end; / Pacakagebody SQL> create or replace package body pg as function ff return char as begin return ('welcome to package function'); end; procedure pp as begin dbms_output.put_line('package.procedure'); end; end; /Call procedure from package : SQL> exec pg.pp(); package.procedure PL/SQL procedure successfully completedRaise function from package SQL> select pg.ff() from dual; PG.FF() ------------------------------------ welcome to package function SQL> create or replace package pkg2 as procedure p3 (dd in dept.deptno%type); function f3 (eid in emp.empno%type) return number; end pk g2; / Package created. SQL> create or replace package body pkg2 as procedure p3 (dd in dept.deptno%type) is begin update dept set loc='CHENNAI' where deptno=dd; end p3; function f3 (eid in emp.empno%type) return number is fsal emp.sal%type; begin select sal into fsal from emp where empno=eid; return fsal; end f3; end pkg2; / Package body created.Call procedure from package : SQL> exec pkg2.p3(50); PL/SQL procedure successfully completed.Raise function from package : SQL> select pkg2.f3(7788) from dual; PKG2.F3(7788) ------------- 300Oracle supports packages : dbms_output utl_file utl_mail dbms_alert dbms_lock dbms_session htp dbms_ scheduler « Previous Next Topic » (PL/SQL - Oracle Supplied packages) |