PL SQL Cursors

NON-PREDEFINED/USER DEFINED EXCEPTION:

PL/SQL provides a limited set of predefined exceptions for the internal oracle system errors, but it provides a mechanism to declare user defined exceptions to handle the other unnamed oracle system errors and exception variables can  be defined and linked to the error using the PRAGMA EXCEPTION_INIT statement in a block’s declarative section.

Syntax:   PRAGMA EXCEPTION_INIT(exception_name,error_number)

PRAGMA is the keyword that signifies that the statement is complier directive, which is processed at compile time and not at run time. Rather, it directed the pl/sql compiler to associate an exception name with an Oracle system error number.

Steps to trapping a non predefined Oracle Server Exception:

1.  Declare the name for the exception within the declarative section.

Syntax: exception_name EXCEPTION;

Here, exception is the name of the exception.

2.  Associate the declared exception with the standard Oracle Server error number using the PRAGMA EXCEPTION_INTI statement

Syntax: PRAGMA EXCEPTION_INIT(exception,error_number);

Here, exception is the previously declared exception.

Error_number is a standard Oracle Server error number.

3. Use the RAISE statement to raise the exception explicitly within the executable section.

Syntax: RAISE exception name;

here, exception is the previously declared exception.

4. Reference the declared exception within the corresponding exception handling routine.

DECLARING USER-DEFINED EXCEPTIONS: Exceptions can be declared only in the declarative part of a PL/SQL block, subprogram, or Package. You declare an exception by introducing its name, followed by the keyword EXCEPTION>

Syntax:<exception_name> EXCEPTION;

In the following example, you declare an exception named past_due:

DECLARE
past_due EXCEPTION;

Exception and variable declarations are similar. But remember, an exception is an error condition, not a data item. Unlike variables, exceptions cannot appear in assignment statements or SQL statements. However, the same scope rules apply to variables and exceptions.

RAISING USER_DEFINED EXCEPTIONS: User-defined exceptions must be explicitly raised when associated error condition is detected. During program execution checking can be done error conditions specified as EXCEPTION in the DECLARE section of PL/SQL program. If an error condition is encountered, calling the RAISE statement explicitly raises the defined exception. RAISE statement for a given exception can be placed anywhere within the scope of that exception.

Declare

-------

-------

My exception EXCEPTION;

Begin

-------

-------

If(condition)

------

Else

Raise my_exception;

Endif;

Exception

When my_exception then

-------

End;

1)  WAP to handle value larger then specified for column with the help of user define exception.

2)  WAP to handle drop invalid column name user defined Exception.

3)  WAP to DELETE department from department table if there is no employee exist for department.

4)  WAP to handle exception with help of OTHERS.

5)  WAP to handle exception with help of OTHERS.

6)  WAP to handle MULTIPLE exceptions within same program.

7)   WAP to handle multiple exceptions with or Statement.

Assignment: WAP for raise exception to debit the amount 2000. If the remaining balance is less than 500 then print insufficient balance.

CURSOR MANAGEMENT

A cursor is a special construct used in procedural SQL to hold the data rows returned by an SQL query. All of the SQL statements we have used inside a PL/SQL block have returned a single value. If the SQL statement returns more than one value, Oracle returns the predefined exception TOO_MANY_ROWS. If we want to use SQL statement that returns more than one value inside our PL/SQL code, we need to use a cursor. We can think of a cursor as a reserved area of memory in which the output of the query is stored, like an array holding columns & rows. Cursors are held in reserved memory are in the DBMS server, not in the client computer.

TYPES OF CURSORS

Cursors are classified depending on the circumstances under which they are opened. Two of the most common types used in oracle are-

Ø  Implicit Cursors

Ø  Explicit Cursors

Ø  Implicit cursor: An implicit cursor is automatically created in procedural SQL  when the SQL  statement return only one value.

Cursor Attributes

NAME	DESCRIPTION
%found %notfound %rowcount %isopen	Returns TRUE if record was fetched successfully, FALSE otherwise. Returns TRUE if record was not fetched successfully, FALSE otherwise. Returns number of records fetched from cursor at that point in time. Returns TRUE if cursor is open, FALSE otherwise.

·         SQL%FOUND: It is used to determine if any rows were retrieved. This attribute will return TRUE if an INSERT, UPDATE, DELETE statement affects one or more rows or a SELECT INTO statement  returns one or more rows, otherwise it returns FALSE.

·         SQL%NOTFOUND- This attribute is opposite to the SQL%FOUND. It returns TRUE if no rows are found otherwise it return FALSE

·         SQL%ROWCOUNT- This attribute is used to determine the number of rows affected by INSERT, UPDATE or DELETE or SELECT INTO statement.

·         SQL%ISOPEN- This attribute always returns FALSE because implicit cursors are opened and closed implicitly before you can reference SQL%ISOPEN to check their status.

1)      WAP to delete the record if it is find otherwise print record is not found using %found cursor.

2)      WAP to update salary if employee is find otherwise print employee not found with the help of %notfound cursor.

3)      WAP to enter the dept no, delete the records and count how many records are deleted with the help of %rowcount cursor.

4)      WAP to enter the dept no, update the salary of that department and print how many employees are affected with the help of %rowcount cursor.

Ø  Explicit Cursor: An Explicit cursor is created to hold the output of an SQL statement that may return two or more rows.