13 Cursors Exception

cursors ■objectives : –explain what is a cursor –list the types of cursors –list & explain implicit cursor & its attributes –state & explain syntax for declaring, opening and fetching data

from explicit cursors –list & explain explicit cursor attributes –explain a cursor for loop –construct a pl/sql programs using cursors ■what

is a cursor ?

–a

cursor is a pl/sql construct that lets you individually manipulate each row in a set of rows returned by a query

–a

cursor allows row by row processing of a data in a table

cursor management in pl/sql ■when

sql commands are executed, oracle uses work areas ■this storage area is called the implicit cursor ■pl/sql allows users to name the private work areas and access the stored information ■cursor attributes are predefined variables that are helpful in finding out the details about the cursor

cursors ■there

are two types of cursors

–implicit cursors ■an implicit cursor is created implicitly by pl/sql –explicit cursors ■an explicit cursor is a user defined cursor

implicit cursor ■created

by oracle automatically with default name

sql ■you cannot process data by using an implicit cursor ■you can use certain attributes to access information about the most recently executed statement ■attributes are prefixed by keyword sql ■implicit cursor attributes are : –sql%notfound –sql%found –sql%rowcount –sql%isopen

implicit cursor attribute

description

sql%notfound result of the dml statement (boolean ) - true if not successful - false if successful sql%found

sql%rowcount ( number )

result of the dml statement (boolean ) - true is successful (i.e. dml returned at least one row) - false if not successful contains number of rows affected by last sql statement

sql%isopen

■cursor

false always because oracle ( boolean ) automatically closes implicit cursor after executing its sql statement

methods

–open –fetch

and –close methods cannot be used with implicit cursor sql

implicit cursor

■example declare v_rowsaffected number (6); begin update emp set comm = 500 where job = ‘salesman’; v_rowsaffected := sql%rowcount; insert into updated_records values (‘emp’, v_rowsaffected, ‘salesman’, sysdate); end;

explicit cursor ■an

explicit cursor is a user defined cursor ■pl/sql allows you to process the rows returned by a query by using an explicit cursor ■you can process data in an explicit cursor ■defined in the declaration section of the pl/sql block ■manipulation is done through open, fetch and close statements ■processing information can be accessed using its attributes ■steps in using explicit cursor are : –declaring

a cursor –opening a cursor –fetching rows from an opened cursor –closing a cursor

declaring a cursor ■a

cursor is defined in the declaration section of the pl/sql block ■declaring a cursor is defining its active set of rows ■this active set of rows can be used to view or modify data

■syntax

■

:

cursor <cursor_name> [(<parameter_list>)] is <select ………………statement >; example 1 : declare cursor emp_cursor is select ename, job, sal from emp; begin : end;

declaring a parameterised cursor ■an

explicit cursor can take parameters ■using parameter you can decide active set at runtime ■example

1:

declare cursor emp_cursor (p_job char ) is select ename, sal, job from emp where job =p_job ; begin : end;

opening a cursor ■a

cursor is to be opened before you can use it to read or modify data ■when you open a cursor –its

associated query is evaluated –the active set of rows become available

■syntax

: open <cursor_name> [(argument list)]; ■a cursor cannot be opened if –cursor

has not been declared yet –it is already open

fetching data from a cursor ■when

you open a cursor, the cursor points before the first row in the active set ■each fetch operation retrieves a row from the active set ■first fetch command moves pointer to the first row and then retrieves that row ■successive fetch commands fetch successive rows ■syntax

: fetch <cursor_name> into variable_name1, variable_name2 ……..; ■variables after into clause in the fetch command and column list in the select statement should have matching data types ■example : declare v_empno emp.empno%type; v_ename emp.ename%type; v_sal emp.sal%type; cursor emp_cursor is select empno, ename, sal from emp; begin open emp_cursor; -- fetches first row fetch emp_cursor into v_empno, v_ename, v_sal; -- fetches second row fetch emp_cursor into v_empno, v_ename, v_sal; : close emp_cursor; end;

cursor processing using a loop: ■you

can fetch rows and process them without use of multiple statements

■example

:

open emp_cursor; loop fetch emp_cursor into v_empno, v_ename, v_sal; exit when emp_cursor%notfound;

-- process row here end loop;

closing a cursor : ■you

can use close statement to close the cursor ■the close statement –disables

the cursor –relieves resources

■syntax

: close <cursor_name>; ■you can reopen the cursor again, if required ■if you perform any operation on a closed cursor, the pre-defined exception invalid_cursor is raised

explicit cursor attributes ■each

cursor has four attributes ■attributes are appended to the cursor name attribute description %notfound evaluates to true, if the fetch no more rows are left %found evaluates to true, if the last fetch succeeded attribute description %rowcount returns the number of rows till now %isopen

■example

evaluates to true, if the cursor is open

1:

loop fetch emp_cursor into v_empno, v_ename, v_sal;

failed i.e.

fetched

exit when emp_cursor%notfound; …………….. end loop; ■example

2:

loop fetch emp_cursor into v_empno, v_ename, v_sal; if emp_cursor%found then -- do some processing else exit; end if; end loop; ■example

3:

: fetch emp_cursor into v_empno, v_ename, v_sal; if emp_cursor%rowcount > 10 then -- do some processing end if; : ■example

4:

if emp_cursor%isopen then -- cursor is open -- do some processing else open emp_cursor; -- open cursor if not already open end if; ■example

5:

open emp_cursor; loop fetch emp_cursor into v_empno, v_ename, v_sal; exit when emp_cursor%notfound; end loop; v_counter := emp_cursor%rowcount; if emp_cursor%rowcount > 50 then v_rowsover50 := true; end if;

cursor for loops ■looping

constructs require explicit cursor processing ■a cursor for…loop can simplify the code

■a

cursor for…loop implicitly –declares

a record structure which stores fetched value –opens cursor implicitly –fetches one row at a time –closes the cursor

■syntax

: for in <cursor_name> loop <statements> end loop;

declare v_qohand itemmast.qoh%type; /* select those records which are not updated */ cursor update_items is select itno, trantype, qty from itemtran where upper (updt) = ‘n’; begin for item in update_items loop select qoh into v_qohand from itemmast where itno = item.itno; if upper(item.trantype) = ‘r’ then v_qohand := v_qohand + item.qty; else v_qohand := v_qohand - item.qty; end if; update where update where end loop; commit; end;

itemmast set qoh = v_qohand itno = item.itno; itemtran set updt = ‘y’ itno = item.itno;

declaring a cursor in cursor for loop ■you

can declare a cursor in a cursor for loop itself ■cursor name in the for statement is replaced with the select statement query

■example

-

declare v_result number; begin for each_record in (select num1, num2, num3 from numtable) loop v_result := (each_record.num1*45 / each_record.num2) + each_record.num3; insert into output_table values (v_result); end loop; end; declare v_empno emp.empno%type; v_ename emp.ename%type; v_sal emp.sal%type; begin for cur_rec in (select empno,ename,sal from emp where deptno = 10) loop dbms_output.put_line(cur_rec.empno||' having name as'||cur_rec.ename|| ' has sal of rs: '|| cur_rec.sal); end loop; end;

parameterized cursors declare vename emp.ename%type; vsal emp.sal%type; cursor cur_emp(p_deptno number) is select ename,sal from emp where deptno = p_deptno; begin open cur_emp(&deptno); fetch cur_emp into vename,vsal; loop exit when cur_emp%notfound; dbms_output.put_line('employee is '||vename||' has sal '||vsal); fetch cur_emp into vename,vsal; end loop; close cur_emp; end;

exception handling ■objectives : –identify the need of exception handling –list types of exceptions

–list the pre-defined exceptions –state & explain how to declare a user-defined exception –state & explain how to raise exception –state & explain how to write an exception handler –list error reporting functions –construct pl/sql programs with exception handling

exception handling in pl/sql ■in

pl/sql, a warning or an error condition is called an exception ■when an error occurs –an

exception is raised –normal execution stops and the control transfers to the exception handling section

■exception

can either be internally defined by runtime systems or can be user-defined –internal

exceptions are raised automatically –user-defined exceptions must be raised explicitly –you can also raise internal exceptions explicitly

predefined exceptions ■predefined

exceptions are error conditions that are defined by oracle ■predefined exceptions can not be altered ■predefined exceptions are raised automatically whenever a pl/sql program violates an oracle rule ■if an action is to be taken for an exception, when raised, you require a handler for it ■exception handler is written in the exception handling section of a pl/sql block ■some

of the predefined exceptions are

exception name cursor_already_open

description raised when you try to open an already open cursor

dup_val_on_index invalid_cursor

raised when you try to insert some duplicate values in a table column with unique or primary key constraint raised when you do an illegal cursor operation

invalid_number

raised when there is a failure of conversion of char type string to a number in a sql statement

login_denied

raised when you try to log on to oracle with an invalid username or password

no_data_found

raised when select ...into statement returns no rows

too_many_rows

raised when select ... into statement returns more than one row

program_error

raised when pl/sql runs out of memory or memory is corrupted

not_loged_on

raised when you execute a pl/sql program referring to a database without actually logging on to the database

user-defined exceptions ■a

user-defined exception can be defined in the declarative section of pl/sql block

■syntax

:

<exception_name> exception; ■example : declare : insufficient_balance exception; : begin : exception : end;

raising exceptions ■once

you have declared an exception, you can raise the exception in the program ■the keyword raise is used to raise an exception ■syntax

: raise <exception_name>;

■predefined

exception can also be raised using raise

keyword ■example

:

declare insufficient_balance exception; begin : if credit_limit < sales_amount then raise insufficient_balance; end if; : end;

execution of exceptions ■once –the

an exception is raised

control is transferred to the exception handling section of a pl/sql block –an associates exception handler, if found, is executed –if an associated exception handler is not found then

control propagates to the outer block –if exception handler is not found pl/sql reports error ■example

1:

declare begin

insufficient_balance exception;

: if credit_limit < sales_amount then raise insufficient_balance; end if; : exception when insufficient_balance then insert into temp (char_store) values (`balance is insufficient’); end;

declare v_cust_id customer.custid%type; v_credit_limit customer.creditlimit%type; -- user-defined exception insufficient_balance exception; begin select custid, creditlimit into v_cust_id, v_credit_limit from customer where custname = `seed’; if v_credit_limit < v_sales_amount then raise insufficient_balance; end if; : exception when no_data_found then -- predefined exception insert into temp(char_store) values (`customer not present’); when insufficient_balance then -- user-defined exception insert into temp(char_store) values (`balance is insufficient’); end;

■example

3:

declare -- user-defined exception out_of_stock exception; v_qty_on_hand number(5); begin … if v_qty_on_hand < 1 then raise out_of_stock;

end if; exception when out_of_stock then insert into temp (char_store) values (`item out of stock’); … end;

example : scope rule for exceptions declare my_exception exception; begin --some code goes here declare -- start of a new sub block insufficient_balance exception; my_exception exception; begin … if … then raise my_exception; end if; … if … then raise insufficient_balance;

exception end;

end if; … if … then raise no_data_found; ... end if; … when insufficient_balance then … -- end of a new sub block

exception when no_data_found then … when my_exception then … end;

handling unnamed exceptions ■you

may handle the unnamed exception by using an others exception handler ■the others handler catches all the exceptions that the block does not name explicitly ■the others exception handler must be defined after all the exception handlers ■example

begin …

:

exception when no_data_found then -- some code here when others then rollback; end;

error reporting functions ■when

an error condition is encountered, you might need to know its error number and the message ■error reporting functions can be used to get this information ■these functions are –sqlcode –sqlerrm

sqlcode function ■sqlcode

function returns the error number associated with most recently raised exception ■sqlcode function is used within an exception handler ■if you use outside it returns value zero

sqlerrm function ■sqlerrm

function returns the error message associated with an error number ■sqlerrm function is used within an exception handler ■if

you use outside it returns the message : ora-0000: normal, successful completion

■example

:

declare error_msg error_num begin …

varchar (50); number;

exception … when others then error_num := sqlcode; error_msg := sqlerrm (error_num); insert into errors values (error_msg); end;

example 1 ■create

a pl/sql block which accepts a customer number from the user and checks whether there are rows matching the customer number in sales table

■accordingly

pl / sql inserts a row in an operations table based on the following conditions –if no rows are found insert a row in operations with a remark “no rows found” –if more than one rows are found insert a row in operations with a remark “multiple rows found” –if one row is found insert a row in operations with a remark “one row found” –for other columns, insert appropriate values. user pseudo column can be used for username column

declare p_custid sales.custid%type; begin p_custid := &customer_number select custid into p_custid from sales where custid = p_custid; insert into operations values( p_custid , ‘one order for the customer ’); exception when no_data_found then insert into operations values ( p_custid , ‘no order for the customer ’); when too_many_rows then insert into operations values (p_custid , ‘multiple orders for the customer ’); end;