PACKAGES


A package is a schema object that groups logically related PL/SQL types, variables, and subprograms.
Packages usually have two parts:
  • A specification(spec)
  • A body
  • The specification is the interface to the package. It declares the types, variables, constants, etc that can be referenced from outside the package.
    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 :
  • Modularity
  • Easier maintenance
  • Easier application design
  • Hiding information
  • Added functionality
  • Better performance
  • Overloading

  • 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 completed
    
    Raise 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)
        -------------
        300
    
    Oracle supports packages :
        
        dbms_output
        utl_file
        utl_mail
        dbms_alert
        dbms_lock
        dbms_session
        htp
        dbms_ scheduler
    


    (PL/SQL - Oracle Supplied packages)