Proc Cursors and exceptions

Steps to use for cursors:        1.   Declaration vernier                     cursor   cursor name   is   cursor execution Statement        2.   Open cursors                  open   cursor name        3.   Extract data, save in specified variable                 FETCH     cursor name  into   variable name &NB Sp      4.   Close cursors            close  -    cursor name to see how a certain cursor is defined     &NBSP ;     CURSOR   mycursor  -is                  select   *    from   tablename    where  ;         or defining parameters with cursors           cursor    mycursor (tid   number) & nbsp is                select   *   from     tablename    where  id=tid;       If the statement is not certain when the cursor is defined, it can also be in the open cursor is          type   mycursor   is   ref   cursor;        v_cursor    mycursor;       Open    v_cursor    for     ' SQL statement ' or string variable         &N Bsp     This allows dynamic execution of statements    cursor parameters will be passed in at open time   Example:         DECLARE

V_rec Temptab%rowtype;
Cursor MyCursor (CID number)
Is
SELECT * FROM Temptab
where id<=cid;
Begin
Open MyCursor (2);
While Mycursor%isopen loop isopen to determine if the cursor is open
Fetch mycursor into V_rec;
Ifmycursor%notfound then NotFound determine if there is data in the cursor
Exit
End If;
Dbms_output.put_line (v_rec.id| | ' '|| V_rec.name);
End Loop;
Close MyCursor; Close cursor
End

Abnormal:

Exception type name

Too_many_rows a variable but returns more than one data

Others all other exceptions

The error number of the binding exception

pragma exception_init (exception name, binding error number); When such an exception occurs after binding, the error number is bound.

Raise exception name manually throw exception

To see an example of an unusual use

Declare
V_rec Temptab%rowtype;
ABC exception; Custom exceptions
Pragmaexception_init (abc,-0001); is an exception binding error number
Begin
SELECT * into V_rec from Temptabwhere;
If 2>1 Then
Raise ABC; Throwing Exceptions manually
End If;
INSERT into Temptab Valuesv_rec;
exception
When Too_many_rows then an exception and then excepion the tag after the capture
Dbms_output.put_line (' too_many_rowsexception ');
When ABC then
Dbms_output.put_line (' abcexception ');
When others then
Dbms_output.put_line (' othersexception ');
End;--others to put in the last