Function,procedure & package in oracle pl/sql

Subprograms
ANONYMOUS BLOCK
So far, you have only written anonymous blocks. Anonymous blocks are much like modules,
except that anonymous blocks do not have headers. There are important differences, though. As
the name implies, anonymous blocks have no name and thus cannot be called by another block.
They are not stored in the database and must be compiled and then run each time the script is
loaded.
Named block -The PL/SQL block in a subprogram is a named block that can accept parameters and that can be invoked
from an application that can communicate with the Oracle database server. A
subprogram can be compiled and stored in the database. This allows the programmer to reuse
the program. It also allows for easier code maintenance. Subprograms are either procedures or
functions.
Subprograms are named PL/SQL blocks that can take parameters and be invoked. PL/SQL has two types of subprograms
called procedures and functions. Generally, you use a procedure to perform an action and a function to compute a value.
Like unnamed or anonymous PL/SQL blocks, subprograms have a declarative part, an executable part, and an optional
exception-handling part.
The declarative part contains declarations of types, cursors, constants, variables, exceptions, and nested subprograms.
These items are local and cease to exist when you exit the subprogram. The executable part contains statements that
assign values, control execution, and manipulate Oracle data. The exception-handling part contains exception handlers,
which deal with exceptions raised during execution.
Simple PL/SQL Procedure
CREATE OR REPLACE PROCEDURE MyBonus
AS
CURSOR DeptCursor IS
SELECT Deptno FROm Dept;
BEGIN
FOR R_GroupBonus IN DeptCursor LOOP
UPDATE Emp
SET Sal = Sal * 0.95
WHERE Deptno = R_GroupBonus.DeptNo;
DBMS_OUTPUT.PUT_LINe('The Bonus Information is '||R_GroupBonus.Deptno);
END LOOP;
END MyBonus;
/
Simple PL/SQL Function
The following example shows a numeric function that declares a local variable to hold
temporary results, and returns a value when finished:
CREATE OR REPLACE FUNCTION square(original NUMBER)
RETURN NUMBER

AS
original_squared NUMBER;
BEGIN
original_squared := original * original;
RETURN original_squared;
END;
/
Sql>SELECT Object_Name, Object_Type, Status
FROM USER_OBJECTS
WHERE OBJECT_NAME = 'MYBONUS
Sql> SELECT TO_CHAR(Line, 99)||'>', Text
FROM USER_SOURCE
WHERE NAME = 'MYBONUS'
/
A stored procedure or function is a block of PL/SQL with a name that is stored in the database. This named PL/SQL block
can be called from any other PL/SQL block, whether it be an anonymous block or another procedure or function.

You can partition the logic of your application very easily using stored procedures and functions. When
developing an application, try to keep all logic that requires data access within the database itself, all
client activity should reside in the client, this can drastically reduce network traffic.
Advantages of Subprograms
Subprograms let you extend the PL/SQL language. Procedures act like new statements. Functions act like new expressions
and operators.
Subprograms let you break a program down into manageable, well-defined modules. You can use top-down design and the
stepwise refinement approach to problem solving.

Subprograms promote reusability. Once tested, a subprogram can be reused in any number of applications. You can call
PL/SQL subprograms from many different environments, so that you do not have to reinvent the wheel each time you use a
new language or API to access the database.
Subprograms promote maintainability. You can change the internals of a subprogram without changing other subprograms
that call it. Subprograms play a big part in other maintainability features, such as packages and object types.
Dummy subprograms (stubs) let you defer the definition of procedures and functions until after testing the main program.
You can design applications from the top down, thinking abstractly, without worrying about implementation details.
PL/SQL Procedures-
A procedure is a subprogram that performs a specific action. You write procedures using the SQL CREATE PROCEDURE
statement. You specify the name of the procedure, its parameters, its local variables, and the BEGIN-END block that
contains its code and handles any exceptions.
You write procedures using the syntax:
[CREATE [OR REPLACE]]
PROCEDURE procedure_name[(parameter[, parameter]...)]
[AUTHID {DEFINER | CURRENT_USER}] {IS | AS}
[PRAGMA AUTONOMOUS_TRANSACTION;]
[local declarations]
BEGIN
executable statements
[EXCEPTION
exception handlers]
END [name];
where parameter stands for the following syntax:
parameter_name [IN | OUT [NOCOPY] | IN OUT [NOCOPY]] datatype
[{:= | DEFAULT} expression]
Every procedure has three parts: the header portion, which comes before AS (sometimes you see IS; they are
interchangeable); the keyword, which contains the procedure name and parameter list; and the body, which is
everything after the AS keyword. The word REPLACE is optional. When REPLACE is
not used in the header of the procedure, to change the code in the procedure, you must drop and then re-create
the procedure. Because it is very common to change a procedure’s code, especially when it is under
development, it is strongly recommended that you use the OR REPLACE option..
When you invoke a procedure, you may provide parameters, these are referred to as Actual Parameters. The
argument list in the procedure definition what is known as the Formal Parameter list. At run time, the Actual Parameter
values are copied into the Formal Parameters that are then used throughout the procedure body. Any
arguments specified as OUT or IN OUT are then copied from the Formal Parameters back into the Actual Parameters when
the procedure ends. Anything declared within the procedure is only within scope for the duration of the procedure and
cannot be used by anything other than the procedure.
Its parameter mode (IN, OUT, or IN OUT). If you omit the mode, the default is IN. The optional NOCOPY keyword
speeds up processing of large OUT or IN OUT parameters.
A procedure may have zero to many parameters,
Its datatype. You specify only the type, not any length or precision constraints.
■ Optionally, its default value.
The pragma AUTONOMOUS_TRANSACTION instructs the PL/SQL compiler to mark a procedure as autonomous
(independent). Autonomous transactions let you suspend the main transaction, do SQL operations, commit or roll back those
operations, then resume the main transaction.
You can specify whether the procedure executes using the schema and permissions of the user who defined it, or the user
who calls it.
A procedure has two parts: the specification (spec for short) and the body. The procedure spec begins with the keyword
PROCEDURE and ends with the procedure name or a parameter list. Parameter declarations are optional. Procedures that
take no parameters are written without parentheses.

The procedure body begins with the keyword IS (or AS) and ends with the keyword END followed by an optional
procedure name. The procedure body has three parts: a declarative part, an executable part, and an optional exceptionhandling
part.
Creating a Stored Procedure
CREATE OR REPLACE PROCEDURE OddNumber(Num1 NUMBER, Num2 NUMBER)
IS
MyNum NUMBER(4);
BEGIN
MyNum := Num1;
WHILE MyNum < Num2 LOOP
IF MOD(MyNum,2) != 0 THEN
DBMS_OUTPUT.PUT_LINE('The Odd Number : '||MyNum);
End IF;
MyNum := MyNum +1;
END LOOP;
END;
/
Executing a Stored Procedure
Open SQL*Plus, and connect to the database schema that contains the procedure you are interested in
executing. Execute the procedure by issuing the following command:
EXEC procedure_name([param1, param2,...]);
Or
BEGIN
procedure_name([param1, param2,…]);
END;
examples
CREATE OR REPLACE PROCEDURE FindEmp
(I_Empno IN NUMBER,
O_Ename OUT VARCHAR2,
O_Job OUT VARCHAR2)
AS
BEGIN
SELECT Ename, Job INTO O_Ename, O_Job
FROM Emp WHERE Empno = I_Empno;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Error in Finding the Details of Employee Number : '|| I_Empno);
END FindEmp;
/
DECLARE
V_Ename Emp.Ename%TYPE;
V_Job Emp.Job%TYPE;
BEGIN
FindEmp(7839, V_Ename, V_Job);
DBMS_OUTPUT.PUT_LINE('Employee 7839 is : '|| V_Ename||', '||V_Job||'.');
END;
/

CREATE OR REPLACE PROCEDURE EmpInfo(I_Deptno IN NUMBER)
AS
CURSOR EmpInfoCursor IS
SELECT Ename, Job, Sal, Comm
FROM Emp
WHERE Deptno = I_Deptno;
EmpRecord EmpInfoCursor%ROWTYPE;
NEmployees NUMBER := 0;
TSalary NUMBER := 0;
AVGSalary NUMBER(7,2) := 0;
MAXSalary NUMBER(7,2) := 0;
BEGIN
OPEN EmpInfoCursor;
LOOP
FETCH EmpInfoCursor INTO EmpRecord;
EXIT WHEN EmpInfoCursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee Name : '||EmpRecord.Ename);
DBMS_OUTPUT.PUT_LINE('Employee Job : '||EmpRecord.Job);
DBMS_OUTPUT.PUT_LINE('Employee Salary : '||EmpRecord.Sal);
DBMS_OUTPUT.PUT_LINE('Employee Comission : '||EmpRecord.Comm);
DBMS_OUTPUT.PUT_LINE('************************************');
TSalary := TSalary + EmpRecord.Sal;
NEmployees := NEmployees + 1;
IF EmpRecord.Sal > MAXSalary THEN
MAXSalary := EmpRecord.Sal;
END IF;
END LOOP;
AVGSalary := TSalary / NEmployees;
DBMS_OUTPUT.PUT_LINE('Number of Employees : '||NEmployees);
DBMS_OUTPUT.PUT_LINE('Total Salary : '||TSalary);
DBMS_OUTPUT.PUT_LINE('Maximum Salary : '||MAXSalary);
DBMS_OUTPUT.PUT_LINE('Average Salary : '||AVGSalary);
CLOSE EmpInfoCursor;
END EmpInfo;
/
Positional & Named Parameters
Actual parameters are matched with formal parameters by their position in the argument list, for example, if we have a
procedure that accepts 3 parameters, then the first formal parameter is matched with the first actual parameter when the
procedure is invoked, the second with the second and so on.
Each actual parameter value is copied into its positional formal parameter equivalent, wholly based on its position in the
parameter list. Any parameter with default values you do not wish to specify MUST be at the end of the list so as not to
affect the position of the parameters.
Another method of providing parameters is to use Named Notation, this is where you give the actual Formal
parameter names when you invoke the procedure or function. This allows you to specify any parameters in any order.

When invoking the procedure, you prefix each actual parameter with its formal parameter name, for example,
BEGIN
SomeProc( p_param2 => 10
, p_param4 => l_string);
END;
Both notations can be mixed but if you do so you must ensure all positional parameters appear before any named
parameters.
Creating a Stored Function
A function is a subprogram that computes a value. Functions and procedures are structured alike, except that functions have
a RETURN clause.
Using the RETURN Statement
The RETURN statement immediately ends the execution of a subprogram and returns control to the caller. Execution
continues with the statement following the subprogram (Do not confuse the RETURN statement with the RETURN clause
in a function spec,which specifies the datatype of the return value.)
A subprogram can contain several RETURN statements. The subprogram does not have to conclude with a
RETURN statement. Executing any RETURN statement completes the subprogram immediately.
In procedures, a RETURN statement does not return a value and so cannot contain an
expression. The statement returns control to the caller before the end of the procedure.
In functions, a RETURN statement must contain an expression, which is evaluated when the RETURN statement is
executed. The resulting value is assigned to the function identifier, which acts like a variable of the type specified in the
RETURN clause.
Sql>CREATE OR REPLACE FUNCTION Factorial(Num NUMBER)
RETURN NUMBER
IS
Fact NUMBER(4) := 1;
BEGIN
FOR MyIndex IN REVERSE 1..Num
LOOP
Fact := Fact * MyIndex;
END LOOP;
RETURN Fact;
END;
/
DECLARE
V_Factorial NUMBER(4) := 0;
BEGIN
V_Factorial := Factorial(5);
DBMS_OUTPUT.PUT_LINE('The Factorial is : '||V_Factorial);
END;
/
Or
Select factorial(4) from dual;
A function inside a function or a plsql block.

CREATE OR REPLACE FUNCTION Combination(Num1 NUMBER, Num2 NUMBER)
RETURN NUMBER
IS
Combi NUMBER(4,2) := 1;
BEGIN
Combi := (Factorial(Num1) /( Factorial(Num1-Num2) * Factorial(Num2)));
RETURN Combi;
END;
/
CREATE OR REPLACE FUNCTION EmpExp(V_Empno NUMBER)
RETURN NUMBER
IS
V_HireDate Emp.HireDate%TYPE;
V_Exp NUMBER(4,2) := 1;
BEGIN
SELECT HireDate INTO V_HireDate
FROM Emp
WHERE Empno = V_Empno;
V_Exp := MONTHS_BETWEEN(SYSDATE,V_HireDate) / 12;
RETURN V_Exp;
END;
/
sql>select empexp(7839) from dual;
The following example shows that the expression in a function RETURN statement can be arbitrarily complex:
Sql>FUNCTION compound (
years NUMBER,
amount NUMBER,
rate NUMBER) RETURN NUMBER IS
BEGIN
RETURN amount * POWER((rate / 100) + 1, years);
END compound;
/
In a function, there must be at least one execution path that leads to a RETURN statement. Otherwise, you get a function
returned without value error at run time.
/*
PLSQL Program to create a FUNCTION to add two numbers.
*/
CREATE OR REPLACE FUNCTION Fun_AddNum ( P_Num1 NUMBER , P_Num2 NUMBER )
RETURN NUMBER
IS
BEGIN
RETURN ( P_Num1 + P_Num2 ) ;
END;
/
Declaring Nested PL/SQL Subprograms
You can declare subprograms in any PL/SQL block, subprogram, or package. The subprograms must go at the end of the
declarative section, after all other items. You must declare a subprogram before calling it. This requirement can make it
difficult to declare several nested subprograms that call each other.
You can declare interrelated nested subprograms using a forward declaration: a subprogram spec terminated by a
semicolon, with no body.
Although the formal parameter list appears in the forward declaration, it must also appear in the subprogram
body. You can place the subprogram body anywhere after the forward declaration, but they must appear in the same
program unit.
Forward Declaration for a Nested Subprogram
DECLARE
PROCEDURE proc1(arg_list); -- forward declaration
PROCEDURE proc2(arg_list); -- calls proc1
PROCEDURE proc1(arg_list) IS BEGIN proc2; END; -- calls proc2

BEGIN
NULL;
END;
/
SQL> SHOW ERRORS
Optimizing a Function That Will Always Return the Same Result
for a Given Input
Problem ---You want to create a function that will return the same result whenever a given input, or set of inputs, is
presented to it. You want the database to optimize based upon that deterministic nature.
Solution
Specify the DETERMINISTIC keyword when creating the function to indicate that the function will always
return the same result for a given input. For instance, you want to return a specific manager name based
upon a given manager ID. Furthermore, you want to optimize for the fact that any given input will
always return the same result. The following example demonstrates a function that does so by specifying
the DETERMINISTIC keyword:
CREATE OR REPLACE FUNCTION manager_name(mgr_id IN NUMBER)
RETURN VARCHAR2
DETERMINISTIC IS
empname emp.ename%TYPE;
BEGIN
IF mgr_id IS NOT NULL THEN
SELECT ename INTO empname FROM EMP
WHERE empno= mgr_id;
RETURN empname;
ELSE
RETURN 'N/A';
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'N/A';
END;
A deterministic function is one that always returns the same resulting value as long as the parameters
that are passed in are the same. This type of function can be useful for improving performance. The function will be executed only once
for any given set of parameters. This means that if the same parameters are passed to this function in subsequent calls, then the function
will be bypassed and return the cached value from the last execution using those parameters
Procedures & Functions - The Difference
So what is the difference between a procedure and a function? Basically, a procedure can be thought of as a PL/SQL or SQL
command, like the COMMIT command for instance. COMMIT is a command that performs some action, it is used by itself,
not as part of any other statement, whereas a function is part of an expression, a function has a value, its return value.
Oracle itself comes with many functions, NVL, DECODE, SUBSTR,…etc, are all functions, they usually accept data
and return some data, this data is then used as part of another statement. Procedures and functions are created in almost
the same way, the major difference being that a function requires a return value
1. Procedure can performs one or more tasks where as function performs a specific task.
2. Procedure may or may not return value where as function should return one value.
3. we can call functions in select statement where as procedure we cant.
4. A FUNCTION must be part of an executable statement, as it cannot be executed independently where as
procedure represents an independent executable statement.

5. Function can be called form SQL statement where as procedure can not be called from the SQL statement.
6. Function are normally used for computation where as procedure are normally used for executing business
logic.
7. Stored procedure supports deferred name resolution where as function wont support.
8. Stored procedure returns always integer value by default zero. whrer as function returns type could be scalar or
table or table value.
9. Stored procedure is precompiled execution plan where as function are not.
10. Function returns 1 value only. Procedure can return multiple values (max 1024).
11. You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL
query.
Overloading Subprogram Names
PL/SQL lets you overload subprogram names and type methods. You can use the same name for several
different subprograms as long as their formal parameters differ in number, order, or datatype family.
Use overloading to create multiple functions that are named the same and perform similar functionality but accept a different number of
parameters, different ordering of parameters, or parameters of different types.
-- Returns the square of the number passed in
CREATE OR REPLACE FUNCTION squared (in_num IN NUMBER)
RETURN NUMBER AS
BEGIN
RETURN in_num * in_num;
END;
-- Returns the squared sum of two numbers
CREATE OR REPLACE FUNCTION squared (in_num IN NUMBER,
in_num_two IN NUMBER)
RETURN NUMBER AS
BEGIN
RETURN (in_num + in_num_two) * (in_num + in_num_two);
END;
Executing Stored Code Under Your Own Privilege Set
Use invoker’s rights by providing the AUTHID property within the declaration of your program. If the AUTHID property is specified
when defining a package, procedure, or function, then you have the ability to specify whether the program should be invoked using the
CURRENT_USER privileges or the DEFINER privileges. In the case of this solution, you would rather use the CURRENT_USER
privileges to ensure that the user does not have the same level of access as the schema owner. The default is DEFINER.
CREATE OR REPLACE PROCEDURE change_password(username IN VARCHAR2,
new_password IN VARCHAR2)
AUTHID CURRENT_USER IS
sql_stmt VARCHAR2(100);
BEGIN
sql_stmt := 'ALTER USER ' || username || ' IDENTIFIED BY ' || new_password;
EXECUTE IMMEDIATE sql_stmt;
END;
When the user executes this procedure, it will be executed using their own set of permissions. This
will prevent them from changing anyone else’s password unless they have the ability to do so under their
allotted permission set.
Controlling Side Effects of PL/SQL Subprograms
To be callable from SQL statements, a stored function (and any subprograms called by
that function) must obey certain "purity" rules, which are meant to control side effects:
■ When called from a SELECT statement or a parallelized INSERT, UPDATE, or
DELETE statement, the function cannot modify any database tables.
■ When called from an INSERT, UPDATE, or DELETE statement, the function cannot
query or modify any database tables modified by that statement.
■ When called from a SELECT, INSERT, UPDATE, or DELETE statement, the function
cannot execute SQL transaction control statements (such as COMMIT), session
control statements (such as SET ROLE), or system control statements (such as
ALTER SYSTEM). Also, it cannot execute DDL statements (such as CREATE)
because they are followed by an automatic commit.
If any SQL statement inside the function body violates a rule, you get an error at run

time (when the statement is parsed).
To check for violations of the rules, you can use the pragma (compiler directive)
RESTRICT_REFERENCES. The pragma asserts that a function does not read or write
database tables or package variables. For example, the following pragma asserts that
packaged function credit_ok writes no database state (WNDS) and reads no package
state (RNPS):
CREATE PACKAGE loans AS
FUNCTION credit_ok RETURN BOOLEAN;
PRAGMA RESTRICT_REFERENCES (credit_ok, WNDS, RNPS);
END loans;
Passing Large or Complex Collections as OUT Parameters
Problem
You have a procedure or function that accepts one or more large or complex collections that are also OUT
parameters, and you need a more efficient method to pass these variables.
Solution
Pass the parameters to your procedure or function by reference using the NOCOPY option on the
procedure or function declaration.
CREATE OR REPLACE PACKAGE no_copy_test AS
TYPE rec_type IS TABLE OF all_objects%ROWTYPE INDEX BY BINARY_INTEGER;
PROCEDURE test;
END no_copy_test;
/
show error
CREATE OR REPLACE PACKAGE BODY no_copy_test AS
PROCEDURE proc1 (rec_list IN OUT rec_type) IS
BEGIN
FOR i IN 1..rec_list.COUNT LOOP
rec_list(i) := rec_list(i);
END LOOP;
END;
PROCEDURE proc2 (rec_list IN OUT NOCOPY rec_type) IS
BEGIN
FOR i IN 1..rec_list.COUNT LOOP
rec_list(i) := rec_list(i);
END LOOP;
END;
PROCEDURE test IS
CURSOR driver IS
SELECT *
FROM all_objects;
recs rec_type;
rec_count integer;
BEGIN
OPEN driver;
FETCH DRIVER BULK COLLECT INTO recs;
CLOSE driver;
rec_count := recs.COUNT;
DBMS_OUTPUT.PUT_LINE (systimestamp);
proc1 (recs); -- parameter passed by value
DBMS_OUTPUT.PUT_LINE (systimestamp);
proc2 (recs); -- paramter passed by reference
DBMS_OUTPUT.PUT_LINE (systimestamp);
END test;
END no_copy_test;
set serverout on -- Enable output from DBMS_OUTPUT statements
EXEC no_copy_test.test;
Running the procedure produced the following output:
03-NOV-10 05.05.14.865000000 PM -05:00
03-NOV-10 05.05.14.880000000 PM -05:00
03-NOV-10 05.05.14.880000000 PM -05:00
How It Works
The recipe utilizes the NOCOPY feature within PL/SQL. It begins by defining two procedures within the test
package. The first procedure, PROC1, accepts a collection of records using the default parameter-passing

method, which is by VALUE. The second procedure, PROC2, is an exact copy of PROC1; however, its
parameter is passed using the NOCOPY option. In PROC1, the parameter is passed in by VALUE, which means
a copy of the entire collection is created in the REC_LIST variable within PROC1. In PROC2, the parameter
data is passed by REFERENCE. Passing a parameter by reference does not copy the data; rather, it uses the
existing data structure passed to it by the calling program. This method is more efficient for very large
collections in both running time and in memory usage.
The output from the test shows the first procedure, which passed its parameter by VALUE took longer
to run than the second procedure, which passed its parameter by REFERENCE. In this example, the
USER_OBJECTS table was used as the data for the parameter, which retrieved only 6,570 records. Larger
performance gains can be realized with more records and more complex data structures.
Listing the Functions, Procedures, and Packages in a Schema
SELECT OBJECT_NAME
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'PROCEDURE;
WHERE OBJECT_NAME like 'EMPTIME%';
Viewing Source Code for Stored Programs
SELECT DBMS_METADATA.GET_DDL('FUNCTION','CALC_QUARTER_HOUR') FROM DUAL;
A procedure can become invalid if the table it is based on is deleted or changed.You can recompile
an invalid procedure using this command:
alter procedure procedure_name compile;
Granting the Ability to Create and Execute Stored Programs
GRANT CREATE PROCEDURE TO user;
GRANT EXECUTE ON schema_name.program_name TO schema;
Creating a Public Name for a Stored Program
Eg--- CREATE PUBLIC SYNONYM pub_synonym_name FOR schema.stored_program;
Dropping Procedures and Functions
A standalone procedure, a standalone function, a package body, or an entire package can be dropped
using the SQL statements DROP PROCEDURE, DROP FUNCTION, DROP PACKAGE BODY, and DROP PACKAGE,
respectively. A DROP PACKAGE statement drops both a package's specification and body.
The following statement drops the Old_sal_raise procedure in your schema:
DROP PROCEDURE Old_sal_raise;

Packages
Apackage is a collection of PL/SQL objects grouped under one package name. Packages include
procedures,functions, cursors, declarations, types, and variables. Collecting objects into a package offers
numerous benefits
All the code (parse tree and pseudocode [p-code]) is loaded into memory (the Shared Global Area [SGA] of the
Oracle Server) on the package’s first call. This means that the first call to the package is very expensive (it involves a lot of
processing on the server), but all subsequent calls result in improved performance. Therefore, packages are often used in
applications that use procedures and functions repeatedly.
Using packages offers an additional level of security. When a user executes a procedure in a package (or stored
procedures and functions), the procedure operates with the same permissions as its owner. Packages let you create
private functions and procedures, which can be called only from other functions and procedures in the package. This
enforces information hiding. The package’s structure thus encourages top-down design.
A Package has two parts, a Specification (or header) and a Body, each part is stored separately within the
database. The Specification of a Package contains details of what can be found in the Package, though it does
not contain any of the code that implements the Package, this code is stored within the Package Body.
THE PACKAGE SPECIFICATION
The package specification contains information about the package’s contents, but not the code
for the procedures and functions.
All objects placed in the package specification are called public objects…
Any function or procedure not in the package specification but coded in a package body is called a private
function or procedure.
When public procedures and functions are called from a package, the programmer writing the “calling” process
needs only the information in the package specification.
The syntax for the package specification is as follows; note that optional information is enclosed in square
brackets:
PACKAGE package_name
IS
[declarations of variables and types]
[specifications of cursors]
[specifications of modules]
END [package_name];
THE PACKAGE BODY
The package body contains the actual executable code for the objects described in the package specification.
The package body contains code for all procedures and functions described in the specification. It also may
contain code for objects not declared in the specification.
packaged object is invisible outside the package and is referred to as hidden.
When creating stored packages, you can compile the package specification and body separately.
syntax for the package body is as follows;
PACKAGE BODY package_name
IS
[declarations of variables and types]
[specification and SELECT statement of cursors]
[specification and body of modules]
[BEGIN
executable statements]
[EXCEPTION
exception handlers]
END [package_name];
A typical package will consist of many public functions and procedures, these are declared in the
specification and defined in the body. A typical package may also contain many private variables,
cursors, exceptions, functions and procedures, all defined in the body.

RULES FOR THE PACKAGE BODY
You must follow several rules in the package body code:
. There must be an exact match between the cursor and module headers and their definitions
in the package specification.
. Do not repeat in the body the declaration of variables, exceptions, types, or constants in
the specification.
. Any element declared in the specification can be referenced in the body.
REFERENCING PACKAGE ELEMENTS
Use the following syntax when calling packaged elements from outside the package:
package_name.element
Packages and Scope
Any object, whether it is a variable or function, that is declared in the specification is visible and within
scope outside of the package so long as the object name is prefixed with package name, for example,
assume we have a package called bank_account with a public function called withdraw, we can call
the withdraw function from a PL/SQL block as follows:-
BEGIN
BANK_ACCOUNT.withdraw(l_account_no,l_amount);
END;
The above function call is almost the same as calling a stored function, the only difference being the
function is qualified with the owning package name. Any object declared within the package body only, is
only visible and in scope to the package itself. You may, for example, create private variables used only
by the package itself, or you may create helper procedures, as in the p procedure created earlier.
Benefit of packages
 All related function and procedure can be grouped together in a single unit called packages.
 Package is reliable to granting privileges.
 All function and procedure within a package can share variable among them.
 Package enables to perform "overloading" of functions and procedures.
 Package improve performance by loading the multiple object into memory at once, therefore,
subsequent calls to related program do not required physical I/O.

 Package is reduce the traffic because all block execute all at once
Diff between procedure and package
- break the dependency chain (no cascading invalidations when you install a new package body -- if you have procedures that call
procedures -- compiling one will invalidate your database)
- support encapsulation -- I will be allowed to write MODULAR, easy to understand code -- rather then MONOLITHIC, nonunderstandable
procedures
-increase my namespace measurably. package names have to be unique in a schema, but I can have many procedures across packages
with the same name without colliding
-support overloading
-support session variables when you need them
- promote overall good coding techniques, stuff that lets you write code that is modular, understandable, logically grouped together....