Docstoc

cursors

Document Sample
cursors Powered By Docstoc
					Cursor attributes ----------------1) 2) 3) 4) %Found - If a row is returned in a cursor fetch then true %NotFound - If a row is returned in a cursor fetch then False %Rowcount - current no of Rows fetched from cursor %Isopen

Exceptions ---------Predefined Exceptions --------------------1) dup_val_on_index-duplicate value in a table on which index is created 2) Invalid_number-Char to a number assignment 3) no_data_found 4) Too_many_rows 5) Value_error-Eg. Char>5 assigned to a variable char(5) User defined Error ----------------declare depositid number :=999999; invalid_deposit exception;/** imp **/ Begin If depositid =999999 then Raise invalid_deposit;/** imp **/ end If; Exception when invalid_deposit then /** imp **/ dbms_output.put_line('invalid deposit'); end; Implicit cursor --------------All DMl &pl/sql select statements ,including queries which return one or more rows Explicit cursor --------------open c1 fetch c1 into v_a loop endloop; Cursor attributes ----------------isOpen,isfound,isnotfound,rowcount cursor and records

-----------------cursor c1 is select .............; c2 c1%rowtype ; begin fetch c1 into c2 v:=c2.empno; cursor for loop using subqueries -------------------------------for c1 in (select ename from emp) loop end loop; Note- In a parametric cursor no size is defined

Pragma Exception init ---------------------To attach oracle serevr error to some name -Direct instruction to compiler Declare Pragma_Exception_init(fk_constraint,-2292); Begin Exception When fk_constraint then End; Raise_application_error ----------------------Raise_application_error(no,message) - gives application error and the messages Raise ----Single raise statement should be in exception handling condition. This will abort the process and display the Ora error.


				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:19
posted:8/29/2009
language:English
pages:2