home

cursorforupdate pl/sql docs

 

exception handling - old packages from programing with pl/sql package book from 1996.... 
EXCEPTION     
    WHEN NO_DATA_FOUND     
    THEN        PLVexc.recNstop ('Company has not been located.');     
    WHEN OTHERS     
    THEN        PLVexc.recNgo;  
END;



Both of these exception handlers make use of the PLVexc package.
This package automatically records the current program, user, error number, 
and message. It relies on the PLVlog package so that the recording can take 
place to a database table or to a PL/SQL table. It performs rollbacks to   
the last savepoint if requested so that the current transaction is "erased," 
but the write to the log table is preserved.
PLVexec

Action

Constant

Handler Program

Description

Continue processing

 
c_go
 
go

Continue processing; do not record the error. This is the equivalent of WHEN OTHERS THEN NULL, which means "ignore this error."

Record and then continue

 
c_recNgo
 
recNgo

Record the error and then continue processing. This action would be appropriate when the exception affects the current block but is not severe enough to stop the entire session.

Halt processing

 
c_stop
 
stop

Stop processing; do not record the error. This action causes PLVexc to raise the process_halted exception. This action would be appropriate when the exception is so severe (either in terms of the database or the application) that it requires termination of the entire session.

Record and then halt processing

 
c_recNstop
 
recNstop

Record the error and then

stop processing. This action causes PLVexc to raise the process_halted exception, as with c_stop.
       

 

Here is the basic syntax of a cursor FOR loop: 

FOR record_index IN cursor_name  
LOOP     
   <executable statement(s)>  
END LOOP;

Old fashioned way:

DECLARE
    CURSOR occupancy_cur IS
        SELECT pet_id, room_number
        FROM occupancy WHERE occupied_dt = SYSDATE;
    occupancy_rec occupancy_cur%ROWTYPE;
 BEGIN
    OPEN occupancy_cur;
    LOOP
      FETCH occupancy_cur INTO occupancy_rec;
      EXIT WHEN occupancy_cur%NOTFOUND;
      update_bill
          (occupancy_rec.pet_id, occupancy_rec.room_number);
    END LOOP;
    CLOSE occupancy_cur;
 END;

Cursor FOR loop;

DECLARE
    CURSOR occupancy_cur IS        
SELECT pet_id, room_number          
FROM occupancy WHERE occupied_dt = SYSDATE;  
BEGIN     
FOR occupancy_rec IN occupancy_cur     
LOOP        update_bill (occupancy_rec.pet_id, occupancy_rec.room_number);    
 END LOOP;  
END;