Oracle PL/SQL (Fundamentals)

• PL/SQL
• Advantages of PL/SQL
• PL/SQL Block Structure
• Fundamentals of PLSQL
• Variables and Constants
• Bind Variables
• Built in Functions
• Conditional and Iterative Control
• Sql within PL/SQL
• Composite Datatypes (PL/SQL Records and PLSQL Tables)
• Cursors
• Exceptions



• PL/SQL is the procedural extension to the non-procedural SQL
• Combines data manipulation of SQL and procedural power of
standard procedural languages.
• Supports sub-programming features such as Procedures and
Functions.


The Advantages of PL/SQL

• Support for SQL
• Block Structure
• Control Structures
• Better Performance
• Higher Productivity


PL/SQL Block Structure


Anonymous Blocks

Anonymous block is block without a name. These blocks are
declared at the point in an application where they are to be run,
and passed to the PL/SQL engine for execution at run-time.

The structure anonymous block is as follows:
DECLARE

BEGIN

EXCEPTION

END;



Fundamentals of PL/SQL


• Character Set
• Reserved Words
• Lexical Units
• Delimiters
• Identifiers
• Literals


Variables and Constants

• Variables are used to store the result of a query or calculation.
• Variables must be declared before use.
• DEFAULT reserve word is used to initialize variables and
constants.
• Variables can also be declared using the row attributes of a
table %ROWTYPE and %TYPE.


Declaring Variables and Constants

• Variables are declared in the DECLARE section of the PL/SQL
block.
• Declaration involves the name of the variable followed by its
data type.
• All statements must end with a semicolon.
• Initial values can also be assigned to a variable at the time of
declaration.
• To assign a value to a variable, the assignment operator := is used.
• They can also specify initial value and specify NOT NULL
constraints.



Using DEFAULT

The reserved word DEFAULT can be used instead of the assignment operator to initialize variables and constants. For e.g.., the declarations
DEPTNO1 NUMBER (4):=40;
PIN_CODE1 CONSTANT NUMBER (6):= 110005;
can be rewritten as follows:
DEPTNO1 NUMBER (4) DEFAULT 40;
PIN_CODE1 CONSTANT NUMBER (6) DEFAULT 110005;


Using %TYPE

To avoid type and size conflict between a variable and the column of a
table, the attribute %TYPE is used. Advantage of this method of defining a variable is that, whenever the type and/or size of a column in the table is changed, it is automatically reflected in the variable declaration.
TMP_NAME EMP.ENAME%TYPE;
Here, the variable TMP_NAME will be of the same size and type as that of the same ename column of emp table.


Using %ROWTYPE

The %ROWTYPE attribute provides a record type that represents a row in a table. The record can store an entire row of data selected
from the table. In case, variables for the entire row of a table need to declared, then instead of declaring them individually, the attribute %ROWTYPE is used:
EMP_ROW_VAR1 EMP%ROWTYPE;
Here, the variable EMP_ROW_VAR1 will be a composite variable, consisting of the column names of the table as its members. To refer to a specific Variable, say sal, the following syntax will be used:
EMP_ROW_VAR1.SAL:= 5500;



Scope and Visibility of a Variable

• The scope of a variable is the portion of the program in which
the variable can be accessed.
• The visibility of a variable is the portion of the program where the
variable can be accessed without having to qualify the reference.



Bind Variables

• A bind variable is a variable that you declare in a host environment.
• Bind variables can be used to pass run-time values, either number or
character, into or out of one or more PL/SQL programs.
• The PL/SQL programs use bind variables as they would use any
other variable.
Creating Bind Variables
• VARIABLE return_code NUMBER
• VARIABLE return_msg VARCHAR2(33)
• VARIABLE RESULT NUMBER


Displaying Bind Variables

To display the current value of bind variables in the SQL*Plus environment, use the PRINT command.
An example of using a host variable in a PL/SQL block:
BEGIN
SELECT (SAL*12) + NVL (COMM,0) INTO :RESULT
FROM emp WHERE empno =7369;
END;
/
PRINT RESULT
To reference a bind variable in PL/SQL, you must prefix its name with a colon(:) .



Built-in-Functions

PL/SQL provides many powerful functions to enable easy data
manipulation.
• The built-in-functions fall into the following categories:
• Error reporting Functions.
• Single-row number Functions.
• Single-row character Functions.
• Datatype conversion Functions.
• Date Functions.



Conditional and Iterative Control

The conditional control available with PL/SQL are

• IF THEN-ELSE Statement
The types of loops available with PL/SQL are:

• LOOP-END LOOP
• FOR-LOOP
• WHILE-LOOP


IF-THEN-ELSE statement

The IF clause can be used for the conditional processing of statements.
If the condition specified after the IF clause evaluates to true, the
statements following the THEN clause are executed until one of the
following is encountered: ELSIF,ELSE or END IF.
The syntax for an IF-THEN-ELSE statement is
IF THEN

[ELSIF THEN
]
ELSE

END IF;



ELSE Clause

The ELSE clauses is optional. It should always be attached to IF clause.
Example
IF trans_type=‘CR’ THEN
UPDATE accounts SET bal=bal+credit WHERE………..
ELSE
UPDATE accounts SET bal=bal+credit WHERE………..
END IF;



ELSIF Clause

The ELSIF clause is also optional as ELSE clause. If the first condition
evaluates to FALSE the ELSIF tests another condition. An if statement
can have any number of ELSIF clauses.
Example:
IF sales>5000 THEN
bonus:=1500;
ELSIF sales<3500 THEN
bonus:=500;
ELSE
bonus:=1000;
END IF;




Simple Loop (LOOP-ENDLOOP)

The syntax is:
LOOP

END LOOP;
Each time the flow of execution reaches the END LOOP statement,
control is returned to the corresponding LOOP statement above it.
This LOOP is endless without EXIT statement.



The EXIT Statement

To control the termination of above mentioned loop EXIT statement is
used. EXIT statement allows control to be passed to the next statement
beyond END LOOP, thus ending the loop immediately.
Example:
LOOP
ctr:=ctr+1;
IF ctr =10 THEN
EXIT;
END IF;
END LOOP;




EXIT WHEN Statement

EXIT-WHEN statement allows a loop to complete conditionally. When
the EXIT statement is encountered, the condition in the WHEN clause
is evaluated. If the condition evaluates to TRUE, the loop completes and
the control passes to the next statement after the loop.
The EXIT-WHEN statement replaces a simple If statement.
The syntax is:
EXIT [loop-label] [WHEN condition];
Example:
LOOP
ctr:=ctr+1;
EXIT WHEN ctr =10;
END LOOP;




FOR LOOP

FOR loops iterate over a specified range of integers. The range is part
of an iteration scheme, which is enclosed by the keywords FOR and
LOOP.
The syntax is
FOR IN .. LOOP

END LOOP;
is the name of variable whose value will be
incremented/decremented automatically on each iteration of the loop.
The index variable has the following properties:
• It is of datatype NUMBER and need not be declared
• It’s Scope is only within the FOR loop.
• Within the FOR loop, the index variable can be referenced, but not
modified.
and are integer expressions which determine the range
of values for the control variable.
By default, the control variable begins with the value of and is
Incremented by +1 on each iteration until is reached. The loop
is terminated at the end of this iteration.
Example:
FOR ctr IN 1..20
LOOP
INSERT INTO temp values (ctr);
……….
……….
END LOOP:
NOTE: If ctr is required beyond the end of the loop, then it must be
copied to a declared variable before the loop ends.




WHILE-LOOP

The WHILE loop tests the condition provided, and if evaluates to true,
then the statements within the LOOP and END LOOP are executed. The
loop continues as long as the condition is true.
The syntax is
WHILE LOOP

END LOOP;
Example
WHILE total<=2000 LOOP
………………..
SELECT sal into salary FROM emp WHERE
total := total+salary;
END LOOP;




SQL Within PL/SQL

• DML in PL/SQL
• The INTO Clause.
Points to remember while using SQL commands within PL/SQL
• SELECT statements which do not return a single row will cause an
exception to be raised.
• DML commands can process multiple rows.
DML in PL/SQL
The allowable DML statements are SELECT, INSERT, UPDATE and
DELETE.
The INTO Clause
The INTO clause is used with SELECT, to store values from the table
into variables.




Writing PL/SQL Code

PL/SQL Code is written using any text editor. The PL/SQL program is
compiled and executed using the command @.

• Inserting Comments in PL/SQL Program
Comments can be placed in PL/SQL with a double minus(-) preceding
the comment or within/*….*/.
• dbms_output.put_line( )
The procedure dbms_output.put_line will produce the output on the
screen. It accepts only one argument.
Hence, the different variables are concatenated with double pipe()
symbol.
To enable the server output, the SET SERVER OUTPUT ON command
must be given at the SQL*Plus prompt, prior to the execution of the
dbms_output.put_line function.


Example: A.

Write a PL/SQL code to update salary of employees number is 7499 to 5000 if salary is less than 5000.
DECLARE
x NUMBER;
BEGIN
SELECT sal INTO x FROM emp WHERE empno =7499;

IF x<5000 THEN

UPDATE emp SET sal=5000 WHERE empno=7499;
END IF;
END;
/



Example: B.

Write a PL/SQL code to insert all the details of employee no 7499 to a new table emp1 which has same structure as emp table.
DECLARE
v_newrec emp%ROWTYPE;
BEGIN
SELECT * into v_newrec FROM emp WHERE empno=7499;
INSERT into emp1
VALUES (v_newrec.empno, v_newrec.ename, v_newrec.job,
v_newrec.mgr, v_newrec.hiredate, v_newrec.sal, v_newrec.comm,
v_newrec.deptno);
END;
/




Composite Datatypes

PL/SQL Records
• PL/SQL records provides a way to deal with separate but related
variables as a unit.
• PL/SQL record is a variable that may contain a collection of separate
values, each individually addressable.
• The record type has to be defined before its record can be declared.
• In case one of the record component is a record, then it is called a
nested record.
The syntax for creating a record is
TYPE IS RECORD
({field-typevariable%TYPEtable.column%TYPE
table%ROWTYPE},{field-typevariable%TYPE
table.column%TYPEtable%ROWTYPE} ……);



PL/SQL Tables

• PL/SQL tables are modeled as database tables, but are actually not
• Primary keys can be associated with them to have array-like access to
rows
• The size can be dynamically increased by adding more rows when
required. However no rows can be deleted
• PL/SQL tables can have one column and a primary key, neither of
which can be named
• Column can belong to any scalar type, but the primary key must
belong to BINARY_INTEGER
TYPEIS TABLE OF
[NOT NULL]
INDEX BY BINARY_INTEGER;


Example: PL/SQL Records
The following PL/SQL program displays the total salary which
includes commission of empno 7369. It should also display
employees name, his department details and his old and new salary.

Declare
TYPE Deptrec is record
(dno dept.deptno%TYPE,
vdname dept.dname %TYPE,
vloc dept.loc%TYPE,
name emp.ename%TYPE,
vsal emp.sal%TYPE,
vcom emp.comm%TYPE,
newsal emp.sal%TYPE);
dept_det deptrec;

BEGIN

SELECT ename, sal, comm, dept.deptno, dname, loc into dept_det.name,
dept_det.vsal, dept_det.vcom, dept_det.dno, dept_det.vdname, dept_det.
vloc FROM emp, dept
WHERE emp.deptno=dept.deptno
and empno=7369;
dept_det.newsal:=dept_det.vsal+NVL(dept_det.vcom,0);
DBMS_OUTPUT.PUT_LINE
(dept_det.dnodept_det.vdnamedept_det.vloc
dept_det.namedept_det.vsaldept_det.vcomdept_det.newsal);
END;
/



Examples: PL/SQL Tables
To load the employees names and salaries into PL/SQL tables and then
display the contents of the table.

DECLARE
TYPE EMPNAMETYPE IS TABLE OF EMP.ENAME%TYPE NOT NULL
INDEX BY BINARY_INTEGER;
TYPE EMPSALTYPE IS TABLE OF EMP.SAL%TYPE
INDEX BY BINARY_INTEGER;
ENAMELIST EMPNAMETYPE;
SALARYLIST EMPSALTYPE;
SUBSCRIPT BINARY_INTEGER:=1;
CTR NUMBER:=1;
BEGIN
FOR EMPREC IN (SELECT ENAME, SAL FROM EMP) LOOP
ENAMELIST (SUBSCRIPT):=EMPREC.ENAME;
SALARYLIST(SUBSCRIPT):=EMPREC.SAL;
SUBSCRIPT:=SUBSCRIPT+1;
END LOOP;

WHILE ctr LOOP
DBMS_OUTPUT.PUT_LINE (ENAMELIST(ctr));
DBMS_OUTPUT.PUT_LINE (SALARYLIST(ctr));
CTR:=CTR+1;
END LOOP;
END;
/




Cursors

Cursors: Oracle Uses work area called Private SQL areas to execute SQL Statements and store information. A Cursor is a PL/SQL construct that allows you to name these work areas, and to access their stored information
Types of Cursor
• Implicit Cursors
• Explicit Cursors


Implicit Cursor: Implicit Cursor are declared by PL/SQL implicitly for all DML Statements and for single row queries
Example: Select Statement issued directly within the BEGIN .. END part of a block opens up an implicit cursor.

Explicit Cursors : Declared and named by the programmer
Explicit Cursors allow multiple rows to be processed from the query.


Implicit Cursor

DECLARE
v_x_sal number;
BEGIN
SELECT sal INTO v_x_sal FROM emp where empno=7499;
DBMS_OUTPUT.PUT_LINE(v_x_sal);
END;
/


Implicit Cursor has four attributes
• SQL%NOTFOUND
• SQL%FOUND
• SQL%ISOPEN
• SQL%ROWCOUNT



Explicit Cursors


Active Set: The Set of rows returned by a multiple row query
Its Size is the number of rows that meets your search criteria
Explicit cursor points to the current row in the active set. This allows your program to process the rows one at a time.

Explicit Cursors

Declaring a Cursor
• Cursor Name
• Structure of the Query
Syntax: CURSOR IS
: It includes most of the usual clauses,
but INTO Clause is not allowed

Example:
DECLARE

CURSOR c1 is SELECT ename, deptno FROM emp
WHERE sal>2100;
………………………………
BEGIN
………………………………
END;


Opening a Cursor

Here Query execution is done. After Opening the Cursor the rows returned by the query are available for fetching.
Syntax: Open ;
This statement is used within the executable section of the block.
It also establishes an active set of the rows

Example:
OPEN c1;
The cursor will now point to the first row in the active set.



Retrieving Individual rows

After the cursor is opened the current row is loaded
Into variables. The current row is the row at which
The cursor is currently pointing
The retrieval of data into PL/SQL variable or host
Variable is done through FETCH statement
Syntax:
FETCH INTO ;
• For each column value returned by the query
Associated with the cursor, there must be a
Corresponding variable in the INTO list.
• ALSO their Datatypes must be compatible


Example of Using Fetch Command
DECLARE
v_sal number;
CURSOR c1 is SELECT sal FROM emp WHERE job='CLERK';
BEGIN
OPEN C1;
LOOP
FETCH C1 into v_sal;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (v_sal);
END LOOP;
CLOSE C1;
END;
/



CLOSING A CURSOR
It explicitly closes the cursor, allowing it to be
Reopened , if required.
Syntax:
CLOSE
Example: CLOSE c1
Example of Using CLOSE
DECLARE
v_name emp.ename%TYPE;
CURSOR c1 is select ename from emp;
BEGIN
OPEN c1;
LOOP
FETCH c1 into v_name;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_name);
END LOOP;
CLOSE C1;
END;
/


Explicit Cursor Attributes


Attribute - Type - Description
%ISOPEN - Boolean - Evaluates to TRUE if the cursor is open
%NOTFOUND - Boolean - Evaluates to TRUE if the most recent fetch does not return a row
%FOUND - Boolean - Evaluates to TRUE if the most recent fetch returns a row
%ROWCOUNT - NUMBER - Evaluates to the total number of rows returned so far


Cursor and Records

Process the rows of the active set by fetching values into a PL/SQL Record
DECLARE
CURSOR emp_cursor is Select empno,ename FROM emp;
Emp_record emp_cursor%rowtype;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_record;
EXIT WHEN emp_cursor%notfound;
DBMS_OUTPUT.PUT_LINE(emp_record.ename);
END LOOP;
CLOSE emp_cursor;
END;
/


Cursor FOR Loops

Syntax:
FOR record_name IN cursor_name LOOP
statement1;
statement2;
. . .
END LOOP;
•The cursor FOR loop is a shortcut to process
Explicit cursors
•Implicit Open,fetch,exit and close occurs
•The record is implicitly declared


Example: Cursor FOR Loop
DECLARE
CURSOR emp_cursor IS
SELECT ename,deptno FROM Emp;
BEGIN
FOR emp_record IN emp_cursor
LOOP
IF emp_record.deptno = 20 THEN
DBMS_OUTPUT.PUT_LINE ('Employee ' emp_record.ename
'works in the Research Dept.');
END IF;
END LOOP;
END;
/



Cursor For Loops Using Sub queries


• No need to declare the cursor
Example:
Begin
FOR emp_record in (SELECT ename, deptno from emp)
LOOP
IF emp_record.deptno =20 THEN
DBMS_OUTPUT.PUT_LINE ('Employee '
emp_record.ename ' works in the Research Dept. ');
END IF;
END LOOP;
END;
/



Difference between Implicit and Explicit Cursor

Implicit Cursors : Used for all DML Statements and
Single row queries.
Explicit Cursors : Used for queries of zero, one or
More rows.



Cursor with Parameters

Syntax:
CURSOR cursor_name(parameter_name datatype, )
IS
Select_statement;
• Pass parameter values to a cursor when the cursor is opened and the query is executed
• Open an explicit cursor several times with different active set each time
Open cursor_name(parameter_value , ……);


Example Cursor with Parameters
DECLARE
CURSOR c1(p1 number) is
Select empno,ename,sal,deptno from emp where deptno=p1;
Crec c1%rowtype;
Vdeptno number:=20;
BEGIN
OPEN c1(10);
LOOP
FETCH c1 into crec;
IF c1%FOUND THEN
IF crec.sal >5000 THEN
Dbms_output.put_line(crec.empno' 'crec.sal ' ' crec.deptno);
End if;
ELSE
Exit;
END IF;
END LOOP;
CLOSE C1;
OPEN C1(Vdeptno);
LOOP
FETCH c1 into crec;
IF c1%FOUND THEN
IF crec.sal >5000 then
Insert into emp1(empno,ename,sal,deptno) values (crec.empno,crec.ename,crec.sal,crec.deptno);
END IF;
ELSE
Exit;
END IF;
END LOOP;
CLOSE C1;
END;
/



The For Update Clause

Syntax:
SELECT …..
FROM ……
FOR UPDATE [OF column_reference][NOWAIT];
• For Update Clause can be used within the cursor query. This means that rows returned by the query are locked exclusively when the OPEN statement is processed. Since locks are released at the end of the transaction. Commit command should not be given across fetches from an explicit cursor if FOR UPDATE is used.
• The For Update clause is the last clause in a select statement, even after the ORDER BY.
• Lock only those records which are satisfied by condition.. • NOWAIT : Returns an Oracle error if the rows are locked by another session.



The WHERE CURRENT of clause

• This allows you to apply update and deletes to the row
Currently being addressed , without the need to explicitly reference the ROWID.
• Must Include the FOR UPDATE clause in the cursor query
So that the rows are locked on Open


Example : For Update and WHERE CURRENT of clause
DECLARE
Cursor c1 is SELECT empno, ename, dept.deptno,sal
FROM dept,emp where emp.deptno=dept.deptno
AND emp.deptno=20 FOR UPDATE OF sal NOWAIT;
BEGIN
FOR emp_record in c1
LOOP
IF emp_record.sal <5000 then
Update emp set sal= emp_record.sal*1.10
Where Current of c1;
END IF;
END LOOP;
END;
/
The slide example loops through each employee in Department 20 and checks whether the salary is Less than 5000. If the salary is less than 5000, the Salary is raised by 10%. The Where Current of Clause in the Update Statement refers to the currently fetched record.



Exceptions Handling in PL/SQL

• In PL/SQL, a warning or error condition is called an exception
• When an error occurs, an exception is raised, i.e. the normal execution
stops and the control transfers to the exception handling part of the
PL/SQL block.
• Exceptions can either be of two types:
1. Predefined Exceptions
2. User Defined Exceptions
Exceptions are identifiers in PL/SQL which may be ‘raised’ during the
execution of a block to terminate its main body of actions. A block will
always terminate when an exception is raised, but you may specify an
‘Exception Handler’ to perform final actions before the block terminates.



Predefined Exceptions

• Predefined exceptions are internally defined by runtime system
• A predefined exceptions are raised implicitly
Predefined exceptions are raised whenever PL/SQL program violates an
ORACLE rule .
Every ORACLE error has a number, but exceptions must be handled by
name.



Exception Handlers

If any type of exception is raised, control is passed to the EXCEPTION
section of the block in which the exception occurred. If the exception is
not handled there, or of there is no EXCEPTION section at all, then the
Block terminates with an unhandled exception, which may effect the
enclosing environment. The same exception cannot be declared more
than once in the same PL/SQL block.
The syntax of defining exception handler is
WHEN THEN ;
Where ‘actions’ may be one or more PL/SQL or SQL statements, each
Terminated by semi-colons.



Example Pre Defined Exceptions
DECLARE
vename emp.ename%TYPE;
vjob emp.job%TYPE;
BEGIN
SELECT ename, job INTO vename, vjob FROM emp WHERE hiredate
BETWEEN '01-JAN-97' AND '31-DEC-97';
DBMS_OUTPUT.PUT_LINE (vename' 'vjob);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line ('No Employees hired in 97');
WHEN TOO_MANY_ROWS THEN
dbms_output.put_line ('More than one Manager has joined in 97');
END;
/



Some of the predefined exceptions are:
• CURSOR_ALREADY_OPEN
• DUP_VAL_ON_INDEX
• INVALID_CURSOR
• TOO_MANY_ERROR
• INVALID_NUMBER
• NO_DATA_FOUND
• ZERO_DIVIDE


When Others Exception

• Although the Exception section in the previous example would trap the two exception, specified other types of exception are ignored
• Rather than defining a separate handler for every exception type, When others exception handler is defined which handles all errors not already handled in the block
• When others exception handler should always be the last exception handler in the block.



When Other Exception Handler

DECLARE

BEGIN

EXCEPTION
When no_data_found then
Dbms_output.put_line ('No employee hired in 97');
When too_many_rows then
Dbms_output.put_line ('More than one manager has
joined in 97');
When others then
Dbms_output.put_line ('Error during execution of the block');
END;
/


When an exception has occurred, one wants to evaluate the
associated error
Pl/sql provides two functions for this purpose

SQLCODE: It returns the error number associated with the
exception that has occurred.
SQLERRM: It returns character data. It returns the complete
error message associated with the exception including the
error number.


Example : SQLCODE And SQLERRM
DECLARE
Error_message varchar2(100);
Error_code Number;
BEGIN

EXCEPTION
When others then
Error_message:=substr(sqlerrm,1,100);
Error_code:=sqlcode;
Dbms_output.put_line (Error_message' ' Error_code);
END;
/


User Defined Exception

• User defined exceptions are declared and defined by the user.
• User defined exceptions must be raised explicitly using the Raise
Statement.
Plsql allows to define user defined exception. Unlike predefined
Exception, user defined exceptions must be declared and must be
raised explicitly by Raise statements.
Declaring an Exception
Exceptions can be declared only in the declarative part of PL/SQL
Block, subprograms, or package


Syntax of Declaring exception

Identifier EXCEPTION;
Note: Exception and variable declaration are similar. But an
Exception is an error condition, not an object. Unlike variables,
Exceptions cannot appear in assignment statements or SQL
Statements


Example:
The following pieces of code illustrate the declaration and use
of user defined exceptions.
DECLARE
------
OUT_OF_STOCK EXCEPTION;----User defined exception
QTY_ONHAND NUMBER(5);
BEGIN
------
IF QTY_ONHAND < 1 THEN
RAISE OUT_OF_STOCK; ----Raise the User Defined Exception
END IF;
-----
----
EXCEPTION
WHEN OUT_OF_STOCK THEN----Handle the User Defined Exception
/* Write the Exception Handling codes Here */
END;



How Exceptions are Raised
• Implicitly in case of Pre defined Exceptions
• Explicitly using a Raise command in
Case of User Defined Exception.
Notes:
• Exception cannot be declared twice in the same block . However,
Same exception can be declared in two different blocks
• Exceptions declared in a Block are considered local to that block
and global to all its sub block. Because a block can reference only
Local or global exceptions , enclosing blocks cannot reference
Exception declared in a sub block.

How Exceptions are Raised

• Implicitly in case of Pre defined Exceptions
• Explicitly using a Raise command in
Case of User Defined Exception.
Notes:
• Exception cannot be declared twice in the same block . However,
Same exception can be declared in two different blocks
• Exceptions declared in a Block are considered local to that block
and global to all its sub block. Because a block can reference only
Local or global exceptions , enclosing blocks cannot reference
Exception declared in a sub block.


Example A: Raising and Handling User Defined Exceptions
Declare
My_exception Exception;
-----
Begin
----------
Begin
---------------
----------------
Raise My_exception;
Exception
When My_Exception Then
---------
---------
End;
--
End;



Example B: Raising and Handling User Defined Exceptions


Declare
My_Exception1 Exception;
My_Exception2 Exception;
BEGIN
-----
BEGIN
----
IF THEN
RAISE MY_EXCEPTION2;
EXCEPTION
WHEN MY_EXCEPTION1 THEN
------
------
END;
-----
Exception
When MY_EXCEPTION2 THEN
----
---
END;
---