Friday, 30 September 2011

implicit cursor and using of sql%rowcount

declare
no NUMBER;
begin
delete from studs;
no:=sql%rowcount;
end;

answers:
It returns no of row in the studs table

Thursday, 29 September 2011

procedure by using in parameter

CREATE OR REPLACE PROCEDURE SP_STUDENTS_UPDATE_BYPK
       (
          p_STUDENT_ID  IN stude1.STUDENT_ID%TYPE   , 
          p_ACTIVE_FLG  IN stude1.ACTIVE_FLG%TYPE                  
       )
AS
BEGIN

      UPDATE stude1
      SET
           ACTIVE_FLG     = p_ACTIVE_FLG                        
      WHERE  STUDENT_ID  = p_STUDENT_ID  ;

      COMMIT ;

EXCEPTION
        WHEN OTHERS THEN
             RAISE_APPLICATION_ERROR (-20001,
              p_STUDENT_ID    || ':$:' ||
              p_ACTIVE_FLG    || ':$:' ||
              SQLERRM, TRUE) ;

END SP_STUDENTS_UPDATE_BYPK ;


EXECUTE SP_STUDENTS_UPDATE_BYPK(1,1);