1

2
3
Certification material Download Link

Please refer the latest certification material for this course at

http://chddlf3014d:1111/sites/enr_portal_certifications/Lists/OS%20Technology
%20101%20Download/AllItems.aspx

4
5
Introduction to PL/SQL
PL/SQL is a database programming language from Oracle, which
extends SQL by adding procedural constructs to it. PL/SQL intends to fill the gap
between database technology and procedural programming languages. Basically
an application development tool, PL/SQL uses the sophisticated ORACLE
RDBMS facilities, extends the standard SQL and is generally used to create
stored objects and to add control functionality to the other ORACLE products like
oracle forms developer.
St d Objects
Stored Obj t refers
f to
t information
i f ti that
th t resides
id or is
i partt off the
th
database. ex. Tables, Views, etc.

•6
7
PL/SQL Program Constructs
Anonymous Block
PL/SQL block without a name for identification
It has to be brought to the server’s memory every time it is
required.It can’t be accessed/referred neither by oracle nor by
user for execution
Has tto be
H b compiled
il d every titime we need
d th
thatt bl
block
k off
statements.Both compilation and execution are done at the
server end.
Stored Procedures
Named PL/SQL Block
j
Stored as Database Objects like Tables,, Views,, Indexes,,
Sequences, Synonyms
Stored in the ready to executable form
Can be referred by other PL/SQL block
One copy is brought to the buffer for sharing by multiple
users

8
Packages
Collection of related objects
The components can be variables, Procedures or Functions
Collectively identified as one Database object
Triggers
The PL/SQL Block which gets fired automatically whenever some
specified event happens
The chain of actions to be performed can be written in the trigger
body for continual execution
Will be invoked by oracle automatically

9
10
Structure of anonymous block
A standard PL/SQL code segment is called a block. Conceptually
a block is made up of three sections:

1) A declaration section for variables, constants, exceptions and

cursors, which is optional.
2) A section of executable statements, which is a must.
3) A section
ti off exceptional
ti l statements
t t t or exception
ti handlers,
h dl which
hi h iis
optional.

The order of the blocks is logical. The executable and exception handling
sections can contain the nested blocks and not the other sections. Each block
can contain nested blocks only in the executable or exception handling parts of
the PL/SQL block. Once you nest a block the scoping rules for the variables,
constants etc are ph
physical
sical from the point at which
hich the
they are declared ii.e.
e a
variable defined in an inner block ceases to exist outside that block.

11
Data Types
PL/SQL offers a comprehensive set of predefined scalar data types. The
variables are to be declared as part of the declare section using the following syntax,

<identifier name> <data type> :=<initialization>;

Scalar types are classified into four categories:

Number  BINARY_INTEGER, DECIMAL, FLOAT, INTEGER,
NUMBER, REAL, etc.
Character  CHAR, VARCHAR2, LONG, etc.
Boolean  BOOLEAN
Date-time  DATE
PL/SQL also supports following data types:
Composite  RECORD, TABLE, VARRAY
Large Object  CLOB, BLOB, NCLOB, BFILE

12
PL/SQL Identifier
A PL/SQL identifier is a name of a PL/SQL object that includes:
Variable, Constant, Function, Procedure, Record, PL/SQL Table,
Cursor, Exception, Package, etc.Properties of an identifierAn identifier
Must start with an alphabet,Should not be of length more than 30
characterss
Can include $, _ and #,Cannot contain spaces
Using
U i VVariables
i bl iin PL/SQL
PL/SQL: PL/SQL allows
ll to
t declare
d l variables
i bl and
d use
them anywhere within the PL/SQL block, where an expression can be used. We
can use variables in PL/SQL for the following purposes:
a) Storing data temporarily during program execution and use them for
validating input or for later processing
b) Manipulating the stored values
c) Reuse: Reusing the same values in many parts of the program by referring
them wherever required
d) Improved maintenance: Anchored variable declarations (variables
declared using %TYPE and %ROWTYPE) can automatically change and
adapt, if the underlying database columns change in their data type
definitions.

13
Using Variables in PL/SQL

Declaration
The PL/SQL variables can be declared and initialized in the
declaration section of any PL/SQL block, subprogram or package. The variable
declarations specify name and data type for the variable and allocate storage
space according to their size. It is also possible to assign an initial value or
g the DEFAULT keyword
default value to the variable using y and can also impose
p a
not null constraint on a variable using the NOT NULL keyword. The variables
declared NOT NULL and the variables that are declared CONSTANT must
compulsorily be followed by initialization.
PL/SQL does not allow forward references of variables and hence
they need to be declared before they are referenced in any other statement,
including other declarative statements.

14
Assigning Values
The variables can be assigned values in the executable section of
the PL/SQL block using := operator.

Passing Values into subprograms through parameters

Procedures and functions accept three types of parameters:
1) IN - Used to pass input to the subprogram like procedure or
function
2) OUT - Used to read output from the subprogram like procedure or
function
3) IN OUT - Used to pass an initial value and then get updated value
in the same variable

15
Guidelines to be followed when declaring variables

Follow proper naming conventions when declaring variables. For

example, v_name to represent a variable and c_name to represent a
constant.

Declare only one identifier per line, which will make the code easier to
read and maintain.

The variables with NOT NULL constraint and the constants must be
assigned an initial value.

The CONSTANT keyword must precede the type specifier, when you
declare the constants.

For example,

v_commission CONSTANT REAL := 500.00;

16
It is recommended to initialize all variables in the declare section using
the assignment operator (:=)( ) or DEFAULT keyword. Variables that are
not initialized will contain NULL by default.
If a variable is declared with the same name as that of a column of a
table used in the subprogram, then there will be ambiguity when the
Oracle server accesses this variable. It may assume it to be the column
of the table that is being referenced. Consider the following code for
example,
DECLARE
eno NUMBER(6);
BEGIN
SELECT eno INTO eno FROM emp WHERE ename = ‘Sanjay';
END;
This code may not work as expected in all the situations. Hence it is
al a s better to follo
always follow proper naming con
conventions
entions when
hen naming the
database objects.
We can have same name for the two object if they are in different
blocks. Instead if one of them is present in the enclosed block, then the
object which is declared in the current scope only will be considered.

17
Displaying information from a PL/SQL block:
DBMS_OUPUT is a package supplied by Oracle. The PUT_LINE
function of this package can be used for displaying information from a PL/SQL
block. This function takes the string to be displayed as its parameter. But before
using this package, it must be enabled in SQL*Plus using the command SET
SERVEROUTPUT ON.
Example:
SET SERVEROUTPUT ON
DECLARE
v_sal NUMBER(9,2) := 6000;
BEGIN
v_sal := v_sal/12;
DBMS_OUTPUT.PUT_LINE ((‘Salaryy of the employee
p y
is||TO_CHAR(v_sal));
END;

18
Commenting Code
Comments are used in PL/SQL blocks and subprograms to
document the application and is very important for readability and maintenance
of the application. There are two types of comments
a) Single line comments  Using two dashes (--) in the beginning of
the line
b) Multi line comments  Using /* and */ by enclosing the lines
between them

19
Anchored Declarations
PL/SQL provides two declaration attributes to anchor the data type of a
variable to another PL/SQL variable of or a column of a table. Anchored data
type can be declared using the following syntax,

<variable_name> <type_attribute> %TYPE | %ROWTYPE

[optional default value assignment]

- variablee_name
i bl  name off the
th variable
i bl th
thatt iis b
being
i d declared
l d
- type_attribute  is any one of the following:
1) Previously declared PL/SQL variable name
Example:
tot_sales NUMBER(20,2);
mon_sales tot.sales%TYPE;

•20
 2) Table column in format ‘table.column’
Example:
company_id company.com_id%TYPE;
3) If a PL/SQL variable is to refer to an entire record structure of
the table then %ROWTYPE is used.
Example:
emp_rec emp%ROWTYPE;
This declares a record of type
yp equivalent
q to a row in the table
emp. Accessing individual members of the record is by
specifying emp_rec.member, where member refers to the
column name in this case.
Tips when using variable declaration :
• Always code which establish a clear understanding of what data a variable is
holding.
• Use named constants to avoid hard coding values.
• Remove unused variables from programs.
• Use %TYPE or %ROWTYPE when ever references are made to a database
table.

21
•22
Nested Blocks
PL/SQL provides the ability to nest blocks and this can be done
wherever an executable statement is allowed including the exception section.
Therefore the executable section of the subprogram or PL/SQL block can be
broken down to smaller blocks making it more readable.
Scope of the Identifiers
Scope of the identifier is the region of the block from which we can
reference
f it.
i Any
A identifier
id ifi d declared
l d iin a bl
block
khhas a llocall scope to that
h blblock
k and
d
has a global scope to all its sub-blocks. If a global identifier is re-declared in the
sub block (that is an identifier with same name as that in the enclosing block is
declared), the local identifier is considered when referenced. The global
identifier can be referenced from the sub block only using a qualified name. But
identifiers with same name but declared in different blocks are considered to be
entirely different identifiers.

23
In nested blocks, a block can look up to the enclosing block for identifiers but a
block cannot look down to the enclosed block for
f accessing identifiers.
f In the
following example, y := x is valid whereas x := y is invalid.

x BINARY_INTEGER;
BEGIN
DECLARE
y NUMBER;;
BEGIN
y:= x;
END;
END;

24
25
26
IF-THEN-ELSE: The IF statement is used for decision making
based on a given condition. The syntax off the IF statement is,Type 1:
1
IF condition THEN
…..
END IF
The condition between the IF and THEN evaluates to true then code
between the THEN and END IF (i.e. true part) will be executed. If
condition evaluates to FALSE
FALSE, then the code is not executed
executed.
Type 2:
IF condition THEN
………..
ELSE
……….
END IF;

•27
The condition between the IF and THEN evaluates to true then code between
the THEN and END IF (i.e.
( true part)) will be executed. Iff condition evaluates to
FALSE, then the code between ELSE and END IF (i.e. false part) will be
executed.

28
IF-THEN-ELSE

Type 3:
IF condition THEN
……
ELSIF condition THEN
….
ELSE
….
END IF;
In this type of IF construct, a series of conditions are checked and
either true or false part of the corresponding IF statement is executed based on
the outcome of the condition.

29
Using CASE Expression statement
CASE Expression
E i statement
t t t is
i usedd to
t make
k selection
l ti from
f a sett
of alternatives available and return that as result. The expression used in the
CASE Expression statement is called the selector which is used to determine the
alternative to be selected and returned.
The syntax of the CASE Expression statement is:
CASE selector
WHEN expression1 THEN result1
WHEN expression2 THEN result2
...
WHEN expression THEN result
[ELSE resultN+1]
END;
The WHEN clauses are sequentially evaluated and the first WHEN clause whose
expression evaluates to a value equal to that of the selector, is selected and the
result corresponding to that WHEN clause is returned. The subsequent WHEN
clauses are not evaluated. If none of the WHEN clauses are selected,, then the
ELSE part will return the result. If the ELSE part is not present, then NULL value
is returned from the CASE Expression statement.

30
•31
Using CASE Statement
Alt
Alternatively,
ti l ththe CASE statement
t t t can be
b usedd for
f scenariosi
where one of the alternative multiple available alternatives has to be selected. In
the CASE statement, there is no selector and each WHEN clause can be an
entire PL/SQL block.

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
WHEN conditionN THEN result
[ELSE resultN+1]
END;

32
33
Loops
Loops are used to execute a given set of statements repeatedly.
Three types of Loop constructs available in PL/SQL are:
The simple or infinite loop
The FOR loop
The WHILE loop
The simple or infinite LOOP
Syntax: LOOP
<executable statements>
END LOOP;
The body must consist of at least one executable statement.
Example: LOOP rem_balance := account_balance(accno);
IF rem_balance < 100 THEN
EXIT;
ELSE apply_balance(accno,rem_balance);
END IF;
END LOOP;

34
EXIT statement inside a loop is used to terminate the loop. If the EXIT is
to be executed after checking a condition, then we can use EXIT WHEN
statement, which is an alternative to using IF-THEN with EXIT statement.
Example:
LOOP
rem_balance := account_balance(accno);
( );
EXIT WHEN rem_balance < 100;
apply_balance(accno,rem_balance);
END LOOP;

35
Using a FOR loop
The syntax of the FOR loop is:

FOR <loop_index> IN [REVERSE] <low_number>..<high_number>

LOOP
<executable statement(s)>
END LOOP;
Following points have to considered when using the FOR loop:
a) Loop index should not be declared. The loop index is scoped within the
loop.
b) The evaluation of expressions in low_number and high_number happens
only once at the start of the loop
c) The loop index or the low_number or the high_number should never be
changed within the loop
d) Usage of EXIT statement in the FOR loop should be avoided
e) To perform iteration in reverse order of the range (i.e from high_number to
low_number), use the REVERSE keyword. The range values should not be
reversed.

•36
WHILE Loop
The WHILE loop is used to iterate a set of statements repeatedly
as long as the condition evaluates to true. Once the condition evaluates to false,
the loop terminates.

Syntax:
WHILE <condition>
LOOP
<executable statements>
END LOOP;
Note:
All the values required to evaluate the condition in WHILE loop should
be known before the loop execution starts.
If the condition is evaluated to false,
false before the loop starts
starts, then the
loop will not be executed even once.

•37
38
39
Varrays:Varrays are collection of elements of same data type. These have an
upper bound within which their size can vary. These can never be sparse. That
is there can never be empty element locations within the collection.
There are two steps in creating a varray collection.
Step 1:First the varray type has to be created specifying the size and data type of
the elements to be stored in the varray. The syntax for creating a varray type
is:TYPE varray_type_name IS VARRAY (size) OF element_type [NOT NULL];
In this syntax:
y
varray_type represents any valid name that will be used for declaring a
collection.
VARRAY shows that you are declaring a collection of type VARRAY.
You can also use VARYING ARRAY in place of VARRAY.
(size) represents a positive integer value, which is used to set the upper
bound of the elements that this collection can contain.
element t pe represents valid
element_type alid SQL or PL/SQL data ttype
pe

40
Step 2:
Once the varray type is created, then varray collection has to
be created using this varray type.

varray_name varray_type_name;

41
Nested Tables
Nested Tables are collection of elements of same data type. But
they do not have an upper bound. Nested tables can be sparse. That is there
can be empty element locations within the collection.
There are two steps in creating a nested table.

Step 1:
Firstt the
Fi th nested
t d table
t bl type
t has
h tot be
b created
t d using
i the
th following
f ll i
syntax::TYPE nested_table_type IS TABLE OF element_type [NOT NULL];

In this syntax:
nested_table_type is the name of the nested table type created. This
will be used for declaring nested table collections
TABLE keyword indicates that a nested table type is being created
element_type represents valid SQL or PL/SQL data type

42
Step 2:
Once the nested table type is created, then nested table
collection has to be created using this nested table type.

nested_table_name nested_table_type;

43
Collection Methods
Collections provide built in methods for easier application
development and better maintenance. The common methods provided by
collections are COUNT, DELETE, EXISTS, EXTEND, FIRST, LAST, LIMIT,
NEXT, PRIOR, and TRIM.

Following points need to be considered when using the collection methods:

These methods cannot be called from SQL statements
COUNT, LIMIT, EXISTS, FIRST, LAST, PRIOR, and NEXT are
functions
DELETE, EXTEND and TRIM are procedures
EXISTS, PRIOR, NEXT, TRIM, EXTEND, and DELETE take
parameters corresponding to collection subscripts.

•44
PL/SQL Records
These are collection of elements with different data types.
There are two steps to be followed to create a Record
Step 1:First a record type has to be created using following syntax:

TYPE rec_type_name is RECORD (

field_name DATA TYPE NOT
NULL:=default_value, field2_name DATA TYPE,…..);
In this syntax:
rec_type_name represents the type name that will be used for declaring
records
field_name represents the field name of the record
DATA TYPE represents SQL or PL/SQL data type except REF
CURSOR
NOT NULL is optional , but whenever it is used , it must be initialized
with a default value.

45
Step 2:
Once the record type is created, then the record can be created
using the record type.

rec_name rec_type_name;

46
47
48
FORALL and BULK COLLECT
When a PL/SQL program is run, the PL/SQL statements are
executed with the PL/SQL statement execution engine and the SQL statements
within the program are passed over to SQL engine. This is known as context
switch. The overhead of context switch degrades the performance. This problem
can be solved using FORALL and BULK COLLECT statements.
FORALL and BULK COLLECT processes multiple rows of data in
a single request(context switch) to the Oracle database. The SQL engine
processes this
thi requestt and
d returns
t allll th
the data
d t together
t th (one
( context
t t switch)
it h) to
t
the PL/SQL engine, which deposits the data into one or more collections.

49
FORALL Statement
FORALL is used with Insert, Update or Delete statements that
references collections elements. This instructs the PL/SQL engine to bulk bind
the input collections, before sending them to the SQL engine. FORALL statement
is not a FOR loop even though it has an iteration scheme.In the example shown
in the slide, although there are three DELETE operations performed, the
DELETE statement is sent only once to the SQL Engine. Thus FORALL reduces
the number of context switches. FORALL statement can also be used for bulk
binding a part of a collection as shown:

•50
DECLARE TYPE NumberList
IS VARRAY(20) OF NUMBER;
departments NumberList := NumberList(20,40,60,85,97,60,74,78,90,96);
BEGIN
FORALL i IN 3..8
UPDATE emp SET sal = sal * 1.10
WHERE departmentno = departments(i);
END;

51
BULK COLLECT
BULK COLLECT is used with the SELECT statements. This
clause is used to instruct the SQL engine to bulk bind the output collections
before returning them to the PL/SQL engine.

52
53
Cursors
Cursor provides a technique to fetch information from database into the PL/SQL
program and process them row by row.A private work area is created and
assigned by Oracle, when an SQL statement from a PL/SQL program is
executed. This area consists information about the SQL statement that got
executed and also the data returned or that which is affected by that statement.
This private work area can be named using PL/SQL cursor which in turn can be
used to manipulate the information within it.Cursor gives the programmer a
complete control over opening,
opening closing and fetching the rows
rows. Fetching from a
cursor will never raise a NO_DATA_FOUND or a TOO_MANY_ROWS
exception.

54
Types of Cursors
a) Implicit Cursors
b) Explicit Cursors
Implicit cursors are automatically created and destroyed by ORACLE. In order to
reference an implicit cursor with an attribute, refer to it as
SQL%<attribute_name>.
Explicit cursors are explicitly declared in the PL/SQL declare section. The
attributes of the cursor can be accessed using the syntax
<cursro_name>%<attricute_name>.

55
Implicit Cursors
Automatically created if any SQL statement is executed.

•56
57
Explicit Cursors
A multiple row query will return a set of rows which is called active
set.are used for accessing and processing the records from this active set one by
one.
Points to remember:
A cursor is not a PL/SQL variable, and hence cannot be part of an
expression.
Parameters can be passed to a cursor,
cursor but it cannot be used to return
data from the cursor.
The parameter which is passed to the cursor is scoped within that
cursor.

•58
59
Example for Cursors
The example in the slide show how to create a cursor and
associate with SELECT statement. This allows us to access individual records
returned by the SELECT statement and process them.We can also achieve the
same thing using sub queries as shown in the below example:

60
DECLARE
Bonus_amt REAL;
BEGIN
FOR employee_record IN (SELECT empno, sal, comm FROM
employee) LOOP
Bonus_amt := (employee_record.sal * 0.05) +
((employee
p y _record.comm * 0.25););
INSERT INTO bonuses VALUES
(employee_record.empno, bonus_amt);
END LOOP;
COMMIT;
END;

61
Explicit Cursor Attributes
The Explicit Cursor attributes are used to get the current status of
cursors. The cursor attributes are,
%FOUND
Returns TRUE if the last FETCH retrieved a row, FALSE if not
%NOTFOUND
If the last FETCH operation did not retrieved a row, then it
returns
t TRUE
%ROWCOUNT
Returns the running count of number of rows fetched
%ISOPEN
If the cursor is open, then returns TRUE
If the cursor if closed, then returns FALSE

•62
Example:

BEGIN
OPEN Employee_cur;
LOOP
FETCH Employee_cur
E l INTO Emploee_record;
E l d
EXIT WHEN Employee_cur%ROWCOUNT > 10 OR
Employee_cur%NOTFOUND:
END LOOP;
CLOSE Employee_cur;

In the example above the loop is terminated when 10 records are fetched from
the cursor or it reaches to the end of cursor.

63
FOR Loops in Cursor
When a FOR loop is used with the cursor, the cursor is
automatically opened when the loop gets initiated. The loop iterates for every
record returned by the SELECT statement associated with the cursor and does
an implicit FETCH operation. Once all the row are fetched as per the query, then
the cursor is automatically closed and the loop terminates. The other situations
when a cursor gets closed automatically are
-An Exception is raised inside the loop
-An EXIT or GOTO statement is used to leave the loop

64
Using the For Update of With Cursor
Whenever a SELECT statement is executed to query database for
a set of records, it doesn’t take an exclusive lock. But sometimes, we may want
to take an exclusive lock on a set of records, even before we change them. FOR
UPDATE clause provided by Oracle is used to do this.Example:
DECLARE CURSOR temp_cur IS
SELECT product_name, manufacturer FROM Products
WHERE manufacturere = ‘ABC’
ABC
FOR UPDATE;
Points to remember:
You can qualify the for update of clause with a column name to indicate
explicitly which column is to be updated.
Locking does not restrict you to update other columns which are not
part of the SELECT statement.

65
Once the cursor is opened, locks are put on the specified result set. Using a
commit or rollback releases all locks on a table. So place the commit or rollback
after some processing logic or after all operations with the cursor are completed.

The WHERE CURRENT OF clause is used to make changes to the most

recently fetched row of data. This clause can be used with both the update and
delete statements.

66
Passing Arguments to a Cursor
You can declare a cursor to receive arguments. The arguments
passed to cursor can then be used as part of the select statement, to evaluate
the cursor each time the code is executed with different set of values, as cursors,
come into existence only when the cursor is opened.

67
68
Exception Handling
An exception is raised, whenever there is an erroneous situations
during the execution of the PL/SQL program. The exception is declared as an
identifier in the PL/SQL program. The program terminates when an exception is
raised. But the exception which is raised can be handled by set of statements
within the PL/SQL program. This set of statements is called the Exception
handler. The exception handler performs the final set of actions to be performed,
before the program terminates. But if the exception is not handled, then it gets
propagated to the calling environment
environment.
Methods of raising an Exception
a) An exception is raised automatically whenever an Oracle error
occurs. For example, if a SELECT statement is unable to retrieve
any records, then an Oracle error ORA-01403 occurs. When this
error occurs, PL/SQL raises an exception called NO_DATA_FOUND
exception.
b) A
An exception
ti can also
l bbe raised
i d explicitly
li itl b
by iissuing
i RAISE
command within the PL/SQL program. This can be a predefined
exception or an user-defined exception

69
Trapping an Exception
An exception is normally raised in the executable section of the
PL/SQL block and this is handled by the corresponding exception handler in the
exception section of the block. If the exception is handled successfully, then the
PL/SQL block terminates with success. But if an exception handler for a
particular exception is not present, then the PL/SQL block terminates with failure
and the exception gets propagated to the calling environment.

70
Types of Exceptions
There are three types of exceptions:
1) Predefined Oracle Server Error
These are predefined in Oracle server and are raised implicitly.
2) Non-predefined Oracle Server Error
These are Oracle server error which are also raised implicitly,
but these are not named by the Oracle server. They just
h
have error code,
d which
hi h can b
be assigned
i d with
ith an exception
ti
name using the PRAGMA INIT declarative.
3) User defined Error
These are defined by the developer and raised explicitly in the
PL/SQL block

71
Trapping the Exceptions
An exception is raised from the executable section of the PL/SQL
block. This exception can be trapped and handled by an exception handler
within the exception section of the PL/SQL block. An exception handler is written
using a WHEN clause followed by a sequence of statements to be executed to
handle the exception raised.Those exceptions which are raised in the block but
do not have a exception handler defined to handle them, can be handled using
the clause WHEN OTHERS. This traps any exception that is not handled and
hence this should be the last exception handler defined in the exception section
section.

72
Guidelines for Trapping Exceptions
a) Define exception handlers for each possible exception that may be
raised with the PL/SQL code. All the Exception handlers must be
defined within the exception block which starts with a keyword
EXCEPTION
b) PL/SQL processes only one exception handler before leaving the
block, whenever there is an exception
c)) There can be only
y one OTHERS clause and place
p this after all the
exception handling clauses
d) WHEN OTHERS is an optional clause
e) Exceptions cannot appear in the SQL statements or assignment
statements

73
Trapping predefined Oracle server errors
The predefined Oracle server errors can be trapped using an
exception handler referencing the standard name of the error. All the predefined
exceptions are declared in the STANDARD package. The most common
predefined exceptions which occur very frequently are NO_DATA_FOUND and
TOO_MANY_ROWS exceptions. Hence it is a best practice to handle these
exceptions in all the PL/SQL blocks.The normal execution of the PL/SQL code is
stopped when an exception is raised. When the exception is trapped by the
corresponding exception handler
handler, the control is transferred to the exception
handling section and the corrective action defined inside the exception handling
section is executed. But after execution of the exception handler, the control
cannot not go back to the executable section to resume the processing where it
left off. The block will be terminated after successful execution of the exception
handler.

74
75
Trapping Non-predefined Oracle server errors
The Non-predefined Oracle server errors have only an error code
to identify. They do not have a exception name to identify and handle. Hence to
assign a name to the non-predefined oracle server error, a compiler directive
called PRAGMA EXCEPTION_INIT is used, which instructs the compiler to
associate the give exception name to the error code. Once the association is
done, an exception handler for that exception can be written to handle the non-
predefined exception.

76
Steps to handle the non-predefined Oracle server error
1) Declare an exception
The exception is declared in the declarative section of the
PL/SQL block. The syntax for declaring the exception is:
<exception_name> EXCEPTION;
2) Associate with the Oracle server error
The declared exception is associated with a Oracle server error
using
i ththe PRAGMA EXCEPTION_INIT
EXCEPTION INIT directive.
di ti The
Th syntax
t
for doing this is:
PRAGMA EXCEPTION_INIT(<exception_name>,
<error_number>);
3) Handle the raised exception
The non-predefined oracle server exception is raised implicitly
and can be handled in the exception section using the
following syntax:
WHEN <exception_name> THEN
<statements >

77
Functions for Trapping Exceptions
The error code and the associated message can be identified
using the following two functions:
a) SQLCODE
The numeric value for the error code is returned
b) SQLERRM
The message associated with the error code is returned

78
Example:
DECLARE
error_number NUMBER;
error_message VARCHAR2(50);
BEGIN
...
EXCEPTION
...
WHEN OTHERS THEN
error_number := SQLCODE;
error_message := SQLERRM;
INSERT INTO log_errors VALUES (error_number, error_message);
END;
As shown above, the error code and error message have to be first stored
in a local variable and then only can be inserted into table. They cannot
be directly invoked in the VALUES clause to insert the error code and
error message.

79
Trapping User-defined Exceptions
User-defined exceptions are declared in the declare section of the
PL/SQL block and has to be raised explicitly using the RAISE statement. Once
the exception is raised, it can be handled in the exception section of the block
using the WHEN clause.

80
Steps to handle the User-defined Exception
1) Declare an exception
The exception is declared in the declarative section of the
PL/SQL block. The syntax for declaring the exception is:

<exception_name> EXCEPTION;

2) Raise
R i ththe exception
ti
The user-defined exception is raised using the RAISE statement
with the following syntax:

RAISE <exception_name>

81
 1) Handle the raised exception
The non-predefined oracle server exception is raised implicitly
and can be handled in the exception section using the
following syntax:
WHEN <exception_name> THEN
<statements >

The RAISE statement can also be used within the exception

section inside the exception handler to raise the same exception back to the
calling environment.

82
Propagating Exceptions
The exceptions, if not handled, can be propagated to the
enclosing block or to the calling environment. When an exception occurs inside
a sub block, then it is handled by an exception handler within the sub block,
terminating the sub block normally. Once the sub block gets terminated, the
control is resumed in enclosing block immediately after the sub block END
statement. But if there is no suitable exception handler found in the sub block for
the raised exception, then the exception gets propagated to the successive
enclosing blocks until the exception gets handled
handled. If none of the enclosing
blocks handle the exception, then finally the host environment will handle it but
may not in a desired manner.

Advantages of propagating exceptions

a) Enclose specific exception handlers for the exceptions in their own
block
b) More general exception handling can be done in the enclosing block

83
RAISE_APPLICATION_ERROR Procedure
This procedure is used to enable Oracle server to raise a pre-
defined exception with a non-standard error code and error message. This
avoids returning unhandled exceptions from the PL/SQL blocks. The syntax for
using this procedure is:

RAISE_APPLICATION_ERROR(<error_number>, <message>[,
{ | } )
<{TRUE|FALSE}>);

error_number
- It is a number specified by the user. The range of
numbers used for this is -20000 to -20999
message
- It is a used specified message.

84
TRUE|FALSE
- It is optional
- If TRUE, error is placed in the stack of
previous errors
- If FALSE, error is overwrites all previous
errors

RAISE_APPLICATION_ERROR procedure can be used in both the

executable section as well as the exception section of the PL/SQL block.
The error returned using this procedure is consistent with the Oracle
server error.

85
86
In PL/SQL, there are two types of subprograms called procedures and functions.
Subprograms allow you to decompose a program into logical units that provide
specific services or perform specific operations.
Procedures and Functions can be thought of as named anonymous blocks that
accept values, return values and be called from other blocks, procedures or
functions.
The basic difference between a procedure and a function is that a procedure is
used to accept and return parameters and perform a action whereas a function is
i t d d tto acceptt parameters,
intended t compute
t a value
l and d return
t the
th value
l to
t the
th
caller.
Procedures and Functions have a declarative part, an executable part and an
optional exception handling part.

87
How to use Procedures and Functions
Procedures and Functions can be created using any Oracle tool that supports
PL/SQL.
Stored procedures and functions provide
Higher Productivity
Better Performance
Memory savings
Application integrity
Tighter security

88
Subprograms
In the above slide it explains how you can reuse subprograms instead of writing same PL/SQL
statement repeatedly in a PL/SQL block.

When you have repetitive PL/SQL statements in a Program, you can replace them by writing the
p
repeated code in a sub-program
p g and call the sub-program
p g in p
place of the repeated
p code

89
Definition of a Procedure
A procedure is a named PL/SQL block that can accept parameters or arguments
and can be invoked from SQL prompt or PL/SQL block.
A procedure contains a header, a declaration section, an executable section, and
an optional exception-handling section.

A procedure can be compiled and stored in the database as a schema object.

The main advantages of creating a procedures is reusability and maintainability
of code. We can create the procedure once and the same can be used in any
number of applications any number of times. If the requirements change, only the
procedure needs to be updated.

90
Developing Procedures
Step 1.Enter the code to create a procedure (CREATE PROCEDURE statement) in a system editor or
word processor and save it as a SQL script file (.sql extension).
Step 2. Compile the code: Using SQL*Plus, load and run the SQL script file. The source code is
compiled into P code and the procedure is created.
A script file with the CREATE PROCEDURE (or CREATE OR REPLACE PROCEDURE) statement
enables you to change the statement if there are any compilation or run-time errors, or to make
subsequent changes to the statement. You cannot successfully invoke a procedure that contains any
compilation or run-time errors. In SQL*Plus, use SHOW ERRORS to see any compilation errors.
Running the CREATE PROCEDURE statement stores the source code in the data dictionary even if the
procedure contains compilation errors. Fix the errors in the code using the editor and recompile the
code.
Step 3. Execute the procedure to perform the desired action. After the source code is compiled and the
procedure is successfully created, the procedure can be executed any number of times using the
EXECUTE command from SQL*Plus. The PL/SQL compiler generates the pseudocode or P code,
based on the parsed code. The PL/SQL engine executes this when the procedure is invoked.

91
Syntax to create a procedure is given in the PPT, where
Procedure name: is the name used to identify the procedure
Local declarations : Optionally declare your local variables, constants, cursors
etc.
EXCEPTION : optional error handling section
Parameter : has the following syntax
<var_name>[IN| OUT | INOUT] data type [{:= | DEFAULT} value]
where, var_name is the unique parameter name. Each parameter is assigned an
optional mode, IN forces the parameter to be considered for input only.
OUT forces a parameter to be considered for output only,
INOUT allows a parameter to be considered both for input and output.
Default mode is IN.
Data type is a valid PL/SQL data type.
The parameters can be assigned a DEFAULT value , thus you can have an
option to omit the parameter during the call also.

92
93
94
You can call a stored procedure from any of the following constructs:
Anonymous Block, Procedure or Function
Stored procedure or function
Database Trigger
Pre Compiler Application
OCI application
Oracle tool(oracle forms)

95
Stored Functions
A function is a named PL/SQL block that can accept parameters and be invoked.
Generally speaking, you use a function to compute a value. Functions and procedures are
structured alike. A function must return a value to the calling environment, whereas a
procedure returns zero or more values to its calling environment. Like a procedure, a
function has a header
header, a declarative part
part, an executable part
part, and an optional exception-
exception
handling part. A function must have a RETURN clause in the header and at least one
RETURN statement in the executable section.
Functions can be stored in the database as a schema object for repeated execution. A
function stored in the database is referred to as a stored function. Functions can also be
created at client side applications. This lesson discusses creating stored functions. Refer
to appendix “Creating Program Units by Using Procedure Builder” for creating client-side
applications.

96
Functions promote reusability and maintainability. When validated they
can be used in any number off applications. Iff the processing
requirements change, only the function needs to be updated.
Function is called as part of a SQL expression or as part of a PL/SQL
expression. In a SQL expression, a function must obey specific rules to
control side effects. In a PL/SQL expression, the function identifier acts
like a variable whose value depends on the parameters passed to it.

97
A function is a subprogram like a procedure that is used to return one and only
one value
value.
A function can be called from an anonymous block, procedure and other
functions
The Syntax for creating a function is provided in the PPT, where
Function name : is the name used to identify the function
RETURN data type : used to declare the data type of the return value
Local Declarations : to optionally declare your local variables, constants, cursors
etc.
t
EXCEPTION: optional error handling section
Parameter : has the following syntax
<var_name>[IN| OUT | INOUT] data type [{:= | DEFAULT} value]
where,
var_name is the unique parameter name. Each parameter is assigned an
optional
p
mode,

98
IN forces the parameter to be considered for input only.
OUT forces a parameter to be considered for output only and INOUT
allows a parameter to be considered both for input and output
Default mode is IN.
Data type : is a valid PL/SQL data type.
The parameters can be assigned a DEFAULT value , thus you can have
an option to omit the parameter during the call also.

99
Developing Functions
Step 1.Enter the code to create a function (CREATE FUNCTION statement) in a system editor or word
processor and save it as a SQL script file (.sql extension).
Step 2 Compile the code: Using SQL*Plus, load and run the SQL script file. The source code is compiled
into P code and the FUNCTION is created.
A script file with the CREATE FUNCTION (or CREATE OR REPLACE FUNCTION) statement enables
you to change the statement if there are any compilation or run-time errors, or to make subsequent
changes to the statement. You cannot successfully invoke a FUNCTION that contains any compilation or
run-time errors. In SQL*Plus, use SHOW ERRORS to see any compilation errors. Running the CREATE
FUNCTION statement stores the source code in the data dictionary even if the FUNCTION contains
compilation errors. Fix the errors in the code using the editor and recompile the code.
Step 3.Execute the FUNCTION to perform the desired action. After the source code is compiled and the
FUNCTION is successfully created, the FUNCTION can be executed any number of times using the
EXECUTE command from SQL*Plus. The PL/SQL compiler generates the pseudocode or P code,
based on the parsed code. The PL/SQL engine executes this when the FUNCTION is invoked.

100
101
102
103
Invoking User-Defined Functions from SQL Expressions
SQL expressions can reference PL/SQL user-defined functions. Anywhere a built-in SQL
function can be placed, a user-defined function can be placed as well.
Advantages
Permits calculations that are too complex, awkward, or unavailable with SQL
Increases data independence by processing complex data analysis within the Oracle server,
rather than by retrieving the data into an application
Increases efficiency of queries by performing functions in the query rather than in the
application
Manipulates new types of data (for example, latitude and longitude) by encoding character
strings and using functions to operate on the strings

104
Example
•The slide shows how to create a function tax that is invoked from a SELECT statement.
The function accepts a NUMBER parameter and returns the tax after multiplying the
parameter value with 0.08.

105
106
To be callable from SQL statements, a user-defined
Stored function must follow the below points:-

Function called from a SELECT statement or a parallelized INSERT, UPDATE, or DELETE

statement, the function cannot modify any database tables.

Function called from an INSERT, UPDATE, or DELETE statement, the function cannot query or
modify any database tables modified by that statement.
Note: Only stored functions are callable from SQL statements. Stored procedures cannot be
called.
Note: Functions that are callable from SQL expressions cannot contain OUT and IN OUT
parameters. Other functions can contain parameters with these modes, but it is not
recommended.

107
Function 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.

108
Restrictions on Calling Functions from SQL: Example
The function contains one insert statement to inserts a new record into the SALES table.
Also the function is invoked from the UPDATE statement that updates the product price of
product id of 102.
The UPDATE statement returns an error saying that the table is mutating.

109
110
Creating Procedures with Parameters
When you create the procedure, the formal parameter defines the value used in the
executable section of the PL/SQL block, whereas the actual parameter is referenced when
invoking the procedure. The parameter mode IN is the default parameter mode. That is, if
mode is not specified for a parameter, the parameter is considered to be an IN parameter.
The parameter modes OUT and IN OUT must be explicitly specified for parameters
requiring them.
A formal parameter of IN mode cannot be assigned a value. That is, an IN parameter
cannot be modified in the body of the procedure.
An OUT or IN OUT parameter must be assigned a value before returning to the calling
environment. IN parameters can be assigned a default value in the parameter list. OUT
and IN OUT parameters cannot be assigned default values.
By default,
default the IN parameter is passed by reference and the OUT and IN OUT parameters
are passed by value. To improve performance with OUT and IN OUT parameters, the
compiler hint NOCOPY can be used to request to pass by reference.

111
IN Parameters: Example
The example in the slide shows a procedure with one IN parameter.
Executing this procedure in iSQL*Plus creates the increase_price procedure. While invoking
increase_price, it accepts one parameter for the PRODUCT ID and updates the SALES table's
PRODUCT PRICE with a increase of 2 percent. To invoke a procedure in SQL*Plus, we use the
EXECUTE command
command.
SQL>EXECUTE increase_price(1001);
IN parameters are passed as constants from the calling environment into the procedure. Attempts
to change the value of an IN parameter result in an error.

112
OUT Parameters: Example
The example in the slide shows,
A procedure with one IN and two OUT parameters to retrieve information about product.
The procedure accepts a value for product id 1001 and retrieves the prodcuct name, product price
from the SALES table into the two output parameters and return values to the calling environment.
The code to create the display_product procedure is shown in the next slide.

113
114
To view the Value of OUT Parameters from SQL*Plus
1. Run the script file to compile generate the source code.
2. use the VARIABLE command to create host variables in SQL*Plus.
3. Invoke the hike_price procedure, and supply these host variables as the OUT parameters.
4. To view the values passed from the procedure to the calling environment, use the PRINT
command.
Example in the slide shows the value of the prod_name variable passed back to the calling
environment The other variables can be viewed
environment. viewed, either individually
individually, or with a single PRINT command
command.
PRINT prod_name prod_price
Do not specify a size for a host variable of data type NUMBER when using the VARIABLE command.
A host variable of data type CHAR or VARCHAR2 defaults to a length of one, unless a value is
supplied in parentheses.
PRINT and VARIABLE are iSQL*Plus commands.

115
Note:
-use colon
l ((:)) tto reference
f the
th host
h t variables
i bl iin th
the EXECUTE command.
d
-Passing a constant or expression as an actual parameter to the OUT
variable causes compilation errors. For example:
EXECUTE hike_price(1001, :prod_name, hike+1000, :g_price)
causes a compilation error.

116
Create the procedure modify_PROD_Price and call the procedure from an anonymous block.

117
Parameter Passing Methods
For a procedure that contains multiple parameters, you can use a number of methods to specify the values of
the parameters.

Method Description
Positional Lists values in the order in which the parameters are declared
Named association Lists values in arbitrary order by associating each one with its parameter
name, using special syntax (=>)
Combination Lists the first values positionally, and the remainder using the special
syntax of the named method

118
In mixed notation the first parameter uses positional notation, and the second
parameter uses named notation. Positional notation must precede named notation.
The reverse is not allowed. For example, the following procedure call is illegal:
Sales_Report (product_no => product, amt); -- illegal

119
Example of Default Values for Parameters
You can initialize IN parameters to default values. That way, you can pass different
numbers of actual parameters to a subprogram, accepting or overriding the default values
as you please. Moreover, you can add new formal parameters without having to change
every call to the subprogram.
Execute
E ec te the statement in the slide to create the ADD_PRODUCT
ADD PRODUCT proced
procedure.
re Note the use
se
of the DEFAULT clause in the declaration of the formal parameter. You can assign default
values only to parameters of the IN mode. OUT and IN OUT parameters are not permitted
to have default values.
If an actual parameter is not passed, the default value of its corresponding formal
parameter is used. Consider the calls to the above procedure that are depicted in the next
page.

120
Example of Default Values for Parameters (continued)
The anonymous block above shows the different ways the ADD_PRODUCT procedure can be
invoked, and the output of each way the procedure is invoked.
Usually, you can use positional notation to override the default values of formal parameters.
However, you cannot skip a formal parameter by leaving out its actual parameter.
Note: All the positional parameters should precede the named parameters in a subprogram call.
Otherwise, you will receive an error message, as shown in the following example:
EXECUTE add_product(product_name=>'new product', 'new productno')

121
Removing Functions
When a stored function/procedure are no longer required, you can drop it a SQL
statement in SQL*Plus to drop it. To remove a stored function, Use the SQL command
DROP FUNCTION FUNCTION_NAME
DROP PROCEDURE PROCEDURE_NAME
CREATE OR REPLACE Versus DROP and CREATE
The REPLACE clause in the CREATE OR REPLACE syntax is equivalent to dropping a
function and re-creating it. When you use the CREATE OR REPLACE syntax, the
privileges granted on this object to other users remain the same. When you DROP a
function and then create it again, all the privileges granted on this function are
automatically revoked.
Removing
e o g Procedures
ocedu es
When a stored procedure is no longer required, you can use a SQL statement to drop it.
To remove a server-side procedure by using SQL*Plus, execute the SQL command DROP
PROCEDURE.Issuing rollback does not have an effect after executing a data definition
language (DDL) command such as DROP PROCEDURE, which commits any pending
transactions.

122
Procedures and Functions can be created using any Oracle tool that supports
PL/SQL.
/SQ
Procedures and Functions can be declared as part of any anonymous block,
procedure, function or package.
A procedure or function must be declared at the end of a declarative section after
all other objects are declared.
Procedures and Functions that are declared locally in other blocks, procedures
or functions can be accessible only
y from that p
program.
g
However Procedures and Functions can be made globally accessible once they
are recorded as part of the database.
Advantages
Procedures and Functions like in any modular programming language provide
modularity, extensibility, reusability, maintainability and abstraction.

123
124
125
Example
The example in the slide displays the names of all the procedures and functions that
you have created.

126
By default a stored procedure or method executes with the privileges of its
owner In Oracle 9i it is possible to execute the programs with privileges of the
owner.
caller. An Invoker’s rights program inherits the privileges and name resolution
context of the user calling the program. It enhances the code reuse and
centralizes the data administration.
The programs can be made to inherit Invoker’s rights by adding the AUTHID
clause in the create statements of a function, procedure, package and type. We
have to specify AUTHID as CURRENT_USER.
Ex:
CREATE OR REPLACE PROCEDURE delemp (tempno in
VARCHAR2)
AUTHID CURRENT_USER
IS
BEGIN
DELETE FROM employee WHERE empno=tempno
END;

127
In the example above, oracle will refer the employee table in the user’s
schema,
h who
h iinvokes
k thi
this procedure.
d If AUTHID clause
l is
i nott specified,
ifi d
Employee table from the definer’s schema is referred.
Name resolution happens in the following way
Names used in queries, DML statements, Dynamic SQL statements are
resolved in the users schema.
All other statements such as calls to packages, functions and procedures
are resolved in the definer’s schema.

128
A package is like a source module in C language. A package may collect a set of
related procedures and functions
f that serve as a subsystem to enforce
f specific
f
business rules. A package typically consists of set of standardized data types,
exceptions, variables, cursors, procedures or functions. Once created, a package
acts as a library, where its contents can be shared among different applications.

129
A package is made up two parts, package specification and package body.

Constructs which are public are declared in the package specification and
defined in the package body. Constructs which are private declared and defined
exclusively within the package body.

130
Developing a Package
There are three steps to developing a package,
To create the package specification, write the code within CREATE PACKAGE and run the
SQL script file. The source code is compiled into P code and is stored within the data
dictionary.
To create the package body write the code within the CREATE PACKAGE BODY and run the
SQL script file. The source code is compiled into P code and is stored within the data
dictionary.
Write and PL/SQL block to invoke public construct

A package specification can exist without a package body, but a package body cannot exist
without a package specification.

131
How to Create a Package Specification and Package Body
To create packages, you declare all public constructs within the package specification and define
within package Body, You can also declare and define private constructs within package Body
Specify the REPLACE option when the package specification already exists.
Initialize a variable with a constant value otherwise, the variable is initialized implicitly to NULL.

Parameter Description
package_name Name the package
public type and item Declare variables, constants, cursors, exceptions, or types
declarations
subprogram specifications Declare the PL/SQL subprograms

132
133
134
To refer any of the member of the package we need to use:-

Package_name.Member_name

135
Removing a Package
When a package is no longer required, Drop it by using the drop command.
You can drop the whole package or only the package body and retain the package
specification.

136
Guidelines for Writing Packages
Keep your packages as general as possible so that they can be reused in future applications.
Avoid writing packages that duplicate features provided by the Oracle.
Package specifications reflect the design of your application, so define them before defining the
package bodies.The package specification should contain only the types, items, and
subprograms that must be visible to users of the package.
package That way other developers cannot
misuse the package by basing code on irrelevant details.
To reduce the need for recompiling when code is changed, place as few items as possible in a
package spec. Changes to a package body do not require Oracle to recompile dependent
procedures. However, changes to a package spec require Oracle to recompile every stored
subprogram that references the package.

137
Advantages of Using Packages
Packages provides an alternative to create procedures and functions as stand-alone schema
objects, and they have various advantages.
Modularity
Packages let you encapsulate logically related types, items, and subprograms in a named
PL/SQL module.
module Each package is easy to understand
understand, and the interfaces between packages are
simple, clear, and well defined. This aids application development
Easier Application Design
When designing an application, all you need initially is the interface information in the package
specs. You can code and compile a spec without its body. Then, stored subprograms that reference
the package can be compiled as well. You need not define the package bodies fully until you are
ready to complete the application.

138
 Information Hiding
With packages, you can specify which types, items, and subprograms are public
(visible and accessible) or private (hidden and inaccessible). For example, if a
package contains four subprograms, three might be public and one private. The
package hides the implementation of the private subprogram so that only the
package (not your application) is affected if the implementation changes. This
simplifies maintenance and enhancement. Also, by hiding implementation details
from users, you protect the integrity of the package.

139
Advantages of Using Packages (continued)
Added Functionality : Packaged public variables and cursors persist for the duration of a session.
So, they can be shared by all subprograms that execute in the environment. Also, they allow you
to maintain data across transactions without having to store it in the database.
Better Performance : When you call a packaged subprogram for the first time, the whole package
is loaded into memory
memory. So,
So later calls to related subprograms in the package require no disk I/O
I/O.
Also, packages stop cascading dependencies and thereby avoid unnecessary recompiling. For
example, if you change the implementation of a packaged function, Oracle need not recompile
the calling subprograms because they do not depend on the package body.
Overloading : With packages you can overload procedures and functions, You can create multiple
subprograms with the same name in the same package, which invokes depends on passing
different number and type of parameters

140
Overloading
PL/SQL allows two or more packaged subprograms to have the same name.
This option is useful when you want a subprogram to accept similar sets of parameters that have
different datatypes.

Note: Only
O local or packaged subprograms can be overloaded. You cannot overload stand-alone
subprograms.

141
Overloading: Example
The slide shows the package specification of a package with overloaded procedures and
function .
The package contains two overloaded function named countemp and two overloaded
procedure named incrementsal

The first declaration of function takes one parameters of type varchar2 whereas the second
declaration takes one parameter of type number.
The first declaration of procedure takes two parameters of type number whereas the second
declaration takes three parameters of type number.

142
Using Forward Declarations
PL/SQL does not allow forward references. You must declare an identifier before using it.
Therefore, a subprogram must be declared before calling it.
In the example given in the slide, the procedure product_price cannot be referenced because it
has not yet been declared. You can solve the illegal reference problem by reversing the order of
the two procedures.
procedures However
However, this easy solution does not always work
work. Suppose the
procedures call each other or you absolutely want to define them in alphabetical order.
.

143
PL/SQL enables for a special subprogram declaration called as forward
declaration. It consists off the subprogram specification
f terminated by a
semicolon. You can use forward declarations to do the following:
Define subprograms in logical or alphabetical order
Define mutually recursive subprograms
Group subprograms in a package
Mutually recursive programs are programs that call each other directly or
indirectly
indirectly.
Note: If you receive a compilation error that product_price is undefined, it is
only a problem if product_price is a private packaged procedure. If
product_price is declared in the package specification, the reference to the
public procedure is resolved by the compiler

144
Using Forward Declarations (continued)
The formal parameter list must appear in both the forward declaration and the subprogram
body.
The subprogram body can appear anywhere after the forward declaration, but both must
appear in the same program unit.
Forward Declarations and Packages
Forward declarations typically let you group related subprograms in a package. The
subprogram specifications go in the package specification, and the subprogram bodies go in
the package body, where they are invisible to the applications. In this way, packages enable
you to hide implementation details.

145
REF CURSOR
Cursor Variables : Cursor variables are like C or Pascal pointers, which hold the
memory location (address) of some item instead of the item itself. So, declaring a
cursor variable creates a pointer, not an item. In PL/SQL, a pointer has datatype
REF X, where REF is short for REFERENCE and X stands for a class of objects.
Therefore, a cursor variable has datatype REF CURSOR. To execute a multi-row
query, Oracle opens an unnamed work area that stores processing information.
To access the information, you can use an explicit cursor, which names the work
area Or
area. Or, you can use a cursor variable
variable, which points to the work area
area. Whereas
a cursor always refers to the same query work area, a cursor variable can refer
to different work areas. So, cursors and cursor variables are not interoperable;
that is, you cannot use one where the other is expected.
Why Use Cursor Variables?
Mainly, you use cursor variables to pass query result sets between PL/SQL
stored subprograms and various clients. Neither PL/SQL nor any of its clients
owns a result set; they simply share a pointer to the query work area in which the
result set is stored
A query work area remains accessible as long as any cursor variable

146
REF CURSOR Types
To create cursor variables, you take two steps. First, you define a REF CURSOR
type, then declare cursor variables of that type. You can define REF CURSOR
types in any PL/SQL block, subprogram, or package .
Typically, you open a cursor variable by passing it to a stored procedure that
declares a cursor variable as one of its formal parameters.
When you declare a cursor variable as the formal parameter of a subprogram
that opens the cursor variable, you must specify the IN OUT mode. That way, the
p g
subprogram can p
pass an open
p cursor back to the caller.
Alternatively, you can use a standalone procedure to open the cursor variable.
Simply define the REF CURSOR type in a separate package, then reference that
type in the standalone procedure.

147
Fetching from a Cursor Variable
The FETCH statement retrieves rows from the result set of a multi-row query.
In the following example, you fetch rows one at a time from the cursor variable
SALES_CV into the user-defined record SALES_rec:
LOOP
/* Fetch from cursor variable. */
FETCH emp p_cv INTO SALES_rec;;
EXIT WHEN SALES_CV%NOTFOUND; -- exit when last row is fetched
-- process data record
END LOOP;
CLOSE SALES_CV;

148
A database trigger is a PL/SQL block that can be associated with a specific
database table.
Triggers are programs written in Pl/SQL that execute automatically whenever a
table or view is modified or when some user action or database system actions
occur.
A Trigger defines an action the database should take when some database –
related event occurs.

The purpose is to perform a specific service when a specified operation occurs

on a table.
You create a database trigger by specifying a database table and by specifying
that before or after a database operation( INSERT, UPDATE, DELETE) on the
table, a procedure is to be invoked. When the specified operation occurs on the
table, ORACLE automatically ‘fires’ the database trigger.
Applications where Database Triggers are useful

149
To verify data integrity on insertion or update
Customizing database
Implement delete cascades
Log events transparently
To enforce referential integrity
To enforce complex business rules
T provide
To id sophisticated
hi ti t d auditing
diti
Initiate business processes
Derive column values automatically
To enforce complex security authorizations
Maintain replicated data
To gather statistics in table access
To prevent invalid transactions

150
151
152
153
Create triggers:
When you need to perform related actions
When you wanted to perform centralize global operations

Do not create triggers:

Where functionality is already existing since duplication will increase
You can create stored procedures and invoke them in a trigger, if your PL/SQL code is very lengthy.
The excessive use of triggers increase more complexity

154
The trigger in the PPT converts all student names into upper case whenever a
user inserts a row into the STUDENT
S table or updates this column
Using OLD and NEW Qualifiers
Within a ROW trigger, reference the value of a column before and
after the data change by prefixing it with the OLD and NEW qualifier.
The OLD and NEW qualifiers are available only in ROW level
triggers.
:OLD Qualifier will result in NULL value for Insert statement and
:NEW qualifier will lead to NULL for DELETE statement.
Prefix these qualifiers with a colon (:) in every SQL and PL/SQL
statement.
There is no colon (:) prefix if the qualifiers are referenced in the
WHEN restricting condition.
Note: Row level triggers can decrease the performance if you do a lot of
updates
pdates on larger tables
tables.

155
The trigger is written to update a PRODUCT_COUNT , whenever a row is
inserted, updated or deleted from
f SALES
S S table.

156
157
Creating Row or Statement Triggers

Create a statement trigger or a row trigger based on the requirement that

the trigger must fire once for each row affected by the triggering
statement, or just once for the triggering statement, regardless of the
number of rows affected.
When the triggering data manipulation statement affects a single row,
gg and the row trigger
both the statement trigger gg fire exactly
y once.

158
Creating DML Statement Triggers
You can create a BEFORE statement trigger in order to prevent the triggering operation
from succeeding if a certain condition is violated.
For example, create a trigger to restrict inserts into the SALES table to certain business
hours, Monday through Friday.
If a user attempts to insert a row into the SALES table on Saturday, the user sees the
message, the trigger fails, and the triggering statement is rolled back. Remember that the
RAISE_APPLICATION_ERROR is a server-side built-in procedure that returns an error to
the user and causes the PL/SQL block to fail.
When a database trigger fails, the triggering statement is automatically rolled back by the
Oracle
server.

159
160
161
Instead of triggers are written only on views to achieve join view updatability.

INSTEAD OF is a valid clause only for views. You cannot specify an INSTEAD
OF trigger on a table.
If a view has INSTEAD OF triggers, any views created on it must have INSTEAD
OF triggers, even if the views are inherently updatable.
When defining INSTEAD OF triggers for LOB columns, you can read both the
:OLD and the :NEW value,
value but you cannot write either the :OLD or the :NEW
values.
A trigger is written in the PPT to update the DEPT table when ever a user tries to
update using a view

162
163
Example
Use the USER_TRIGGERS data dictionary view to display information about the
specified trigger.

164
 Triggers are fully compiled when the CREATE TRIGGER command is issued and the P
code is stored in the data dictionary.
If errors occur during the compilation of a trigger, the trigger is still created.

Database Trigger Stored Procedure

Invoked implicitly Invoked explicitly
COMMIT, ROLLBACK, and COMMIT, ROLLBACK, and SAVEPOINT
SAVEPOINT statements are not allowed statements are permitted within the procedure
within the trigger body. It is possible to body.
commit or rollback indirectly by calling a
procedure, but it is not recommended
because of side effects to transactions.
transactions
166
167
Declaring only when required
Some of the declarations may need a costly process to assign a
value for initialization. Hence it is recommended that always defer the
declarations of variables till it is required. It is also advised to search for
unwanted variables and remove them from the program.

168
169
Conditional Statement
If the conditional statement contains the AND operator, then the
first operand of AND is always executed and second operand of AND is
executed only when the first operand evaluates to true. Hence it is desirable to
place expressions which consume less time, first and the functions taking more
time, second. This will execute the function only when the first operand which is
expression, evaluates to true.

170
171
Avoid implicit data type conversions
PL/SQL implicitly converts structurally different data types in an
assignment statement. For example, assigning a PLS_INTEGER variable to a
NUMBER variable results in an implicit conversion because their internal
representations are different. Hence it is always recommended to assign values
to variables whose data types are compatible. This will improve performance.
In the example shown in the slide, the integer literal 15 is
represented internally as a signed 4-byte quantity, so PL/SQL must convert it to
an Oracle
O l number b b before
f th
the addition.
dditi H
However, th
the flfloating-point
ti i t literal
lit l 1515.0
0 iis
represented as a 22-byte Oracle number, so no conversion is necessary.

172
Choose optimized data type
Always choose an optimized data type.
For example,
the NUMBER data type which is 22 byte is good at
supporting portability but may degrade the performance. Hence whenever a
integer variable is to be declared, use PLS_INTEGER as it requires less storage
that INTEGER or NUMBER data types. Moreover, PLS_INTEGER used
machine arithmetic, whereas, INTEGER and NUMBER uses library y arithmetic
which consumes more time.

173
Avoid unnecessary NOT NULL constraints
Using NOT NULL constraint degrades the performance. Hence
minimize the use of NOT NULL constraint.
In the example shown in the slide, since m is constrained by NOT
NULL, the value of the expression a + b is assigned to a temporary variable,
which is then tested for nullity. If the variable is not null, its value is assigned to
m. Otherwise, an exception is raised. However, if m were not constrained, the
value would be assigned to m directly. Hence check for NULL programmatically
and
d th
then ttake
k necessary actionti to t improve
i performance.
f

174
175
176
177
178