PACKAGES IN ORACLE

PACKAGE:
                        A package is named collection of variables,cursors,types,procedures and functions.

  • package does not accepts parameters.
  • can not be invoked.
  • can not be nested.
package having two parts

                           package specification
                          package body

 package specification: we are defining global data and also declare subprograms

 package body: here we are implementing subprograms

 syntax: package specification

 create or replace package ( package_name )
is
global_variable declaration;
cursor declaration;
procedure declaration;
function declaration;
types declaration;
end (package_name);
/

 package body:

 create or replace package body (package_name)
is
procedure implementation;
function implementation;
end (package_name);
/
----------------------------------------------------------------------

 invoking packaged subprograms:

 exec package_name.procedure_name(actual parameters);

 select package_name.function_name(actual parameters) from dual;
examples:
-------------------------------------------------------------------------------------
------package specification

 create or replace package pkg1
is
   procedure p1;
   procedure p2;
end pkg1;
/ 

 ---- package body

 create or replace package body pkg1
is
procedure p1
is
    dbms_output.put_line('procedure p1');
end p1;
procedure p2
is
  dbms_output.put_line('procedure p2');
end p2;
end pkg1;
/

 exec pkg1.p1;
output is: procedure p1
exec pkg1.p2;
output is: procedure p2

 --------------------------------------------------------------------

 global variable declaration :

   in plsql we are defining global variables in  package specification only

 example:

 create or replace package pkg2
is
--global variable declaration
   g number(3):=500;
   procedure p3;
   function f1(a number) return number;
end pkg2;
/

 --package body
create or replace package body pkg2
is
   procedure p3
   is
        z number(3);
   begin
        z :=g/2;
       dbms_output.put_line(z);
   end p3;
   function f1 (a number)
   return number
   is
       n number(5):=0;
  begin
       n:=g*a;
       return n;
   end f1;
end pkg2;
/

 ----exec procedure p3
exec pkg2.p3;
output:250

 ---exec function f1 is
select pkg2.f1(4) from dual;
output:2000.
----------------------------------------------------------------------------------


Comments

Post a Comment

Popular posts from this blog

PRAGMA SERIALLY_REUSABLE

PRAGMA SERIALLY_REUSABLE : We want to maintain state of the global variable we are using pragma serially_reusable in packages. ------------with out serially reusable pragma in packages SQL> create or replace package pkg3            is                   g number(3):=27;            end pkg3;           / Package created. SQL> begin                pkg3.g :=30;           end;          / PL/SQL procedure successfully completed. SQL> begin                  dbms_output.put_line(pkg3.g);           end;          / 30 PL/SQL procedure successfully completed. -------------------------------------------- serially reusable in packages SQL> create...
Read more

COLLECTION METHODS

Collection methods: A collection method is a built-in function or procedure that operates on collections and is called using dot notation. Following are the functions used with collections. EXISTS COUNT LIMIT FIRST AND LAST PRIOR AND NEXT EXTEND TRIM DELETE Syntax: collection_name.method_name [(parameters)] Collection methods cannot be called from SQL statements. Only the EXISTS method can be used on a NULL collection. All other methods applied on a null collection raise the COLLECTION_IS_NULL error. 1.COUNT: COUNT method returns the number of elements with the space allocated in Varrays and Nested tables. The COUNT method displays number of elements in associate arrays. COUNT can be smaller than LIMIT for Varrays. Example: DECLARE --Index by table type    TYPE etab IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;    emptab  etab; --Nested Table Type    TYPE tab_type IS TABLE OF VARCHAR2 (10);    t...
Read more

CURSOR PARAMETERS

CURSOR WITH  PARAMETERS: A cursor can be declare with parameters. Note: cursor parameters can be assigned default values. the scope of the cursor  parameters is local to the cursor the mode of the  parameters can only be IN. Example: Declare   Cursor c1 (p_deptno  emp.deptno%type) is   select ename,sal,deptno   from emp   where deptno=p_deptno; begin     for i in c1 (10) loop     dbms_output.put_line(i.ename||' '||i.sal||'  '||i.deptno);     end loop;         for i in c1(20) loop         dbms_output.put_line(i.ename||' '||i.sal||'  '||i.deptno);         end loop;     dbms_output.put_line('hai'); end; /
Read more