Packages:
In this post we will be learning concepts of PLSQL Packages,
Tutorial for learning concepts of PLSQL Packages :-
Tutorial for learning concepts of PLSQL Packages :-
Package is declarative part of the functions and procedures which are stored
in
that package. There are two blocks in defining a package.
_
Package Specification
_
Package body
Package
Specification: The package specification contains information about the
contents
of the package. It does not contain code for any subprograms.
Syntax:
CREATE [OR REPLACE] PACKAGE package_name {IS | AS}
Type_definition
|
Procedure_specification
|
Function_specification
|
Variable_declaration
|
Exception_declaration
|
Cursor_declaration
|
Pragma_declaration
END
[package_name];
Package
Body: The package body is separate data dictionary object from the package
header.
It cannot be successfully compiled unless the package header is already been
successfully
compiled.
Syntax:
CREATE [OR REPLACE] PACKAGE BODY package_name {IS | AS}
….
BEGIN
Initialization_code;
END
package_name;
Ex:
SQL> create or replace package vamsi_pack as
procedure
razia_proc(mynum in number);
end
vamsi_pack;
--
Declaration of Package Body and passing value to the procedure
create
or replace package body vamsi_pack as
procedure
razia_proc(mynum in number) as
cursor
mycursor is select ename,empno,sal from emp where empno=mynum;
begin
dbms_output.put_line('
NAME ' || ' NUMBER ' || ' SALARY ');
for
i in mycursor
loop
dbms_output.put_line('
'||i.ename||' '|| i.empno ||' '|| i.sal);
end
loop;
exception
when
others then
dbms_output.put_line('YOU
HAVE DONE AN ILLEGAL OPERATION ');
end
myproc;
end
vamsi_pack;
Execution:
SQL> exec vamsi_pack.razia_proc(7788);
