Oracle Learning Notes (vi) __oracle

46. Abnormal

(1)

    DECLARE  v_temp number (4);
   Begin  select empno into V_temp from emp where deptno = ten;
   Exception when  too_many_rows then
   dbms_output.put_line (' Too Many records ');
  When  others then
   dbms_output.put_line (' error ');
   End

(2)

  
    Declare             V_tempnumber (4);        begin             select empno into V_temp from EMP where empno
   = 2222;       Exception             No_data_found then   &NB Sp
                   Dbms_output.put_line (' No such data ');
        END; ----------------Error logging logs (with Table records: Saving system logs to a database for later viewing)-----------  Creating a sequence (for processing incremented IDs):    
     Create sequence seq_errorlog_id start with 1 increment by 1;       Create log table:       CREATE table errorlog       (      ID number primary key,       Errcode number,       errmsg Varcha R2 (1024)       Errdate date      );       Sample program:       declare             V_DEPTNO
   Dept.deptno%type: = 10;
               V_errcode number;
               v_errmsg VARCHAR2 (1024);
        begin             DELETE from dept where deptno = V_deptno;
           commit;       Exception             Others then     &NB Sp
                 rollback;
                             v_errcode:= SQLCODE;
                             v_errmsg:= SQLERRM;             INSERT into errorlog values (seq_Errorlog_id.nextval, V_errcode,v_errmsg, sysdate);
                             commit;       END;

The key cursor (cursor) and pointer concepts in the

pl/sql are almost

  
  Declare             CURSORC is select * from EMP;
   The statement here does not execute immediately, but when the following open C, it will really go to the database to fetch data             v_emp C%rowtype;
         begin             Open C;
                      fetch C into v_emp; Dbms_output.put_line (V_emp.ename);
   This will output only one piece of data 134 will use the method of looping output each record         close C;
        END; ----------------------use a Do While loop to traverse each data in a cursor---------------------      DECLARE   &NB Sp         cursor C is                     Sele
   ct* from EMP;
               v_emp C%rowtype;       begin             Open C;     &NBsp         loop                     FETCH C INT
  o v_emp;  (1)   exit when (C%notfound);  NotFound is the key word in Oracle to determine if there is a next data (2)   Dbms_output.put_line (v_emp.ename);
   (1) (2) The order can not be reversed, otherwise the final result will be printed more than once.
           end loop;
           close C;
        END; ------------------------using a while loop, traversing the cursor---------------------      DECLARE     &NBSP ;       Cursor C is                     select* fro
   M EMP;
               v_emp Emp%rowtype;
         begin             Open C;
               fetch C into v_emp;             while (C%found) loop                dbms_output.put_line (v_emp.ename);
                   fetch C into v_emp;
           end loop;
           close C;       End;s------------------------use a For loop to traverse the cursor (the most convenient and efficient method.)
   )-----------------      DECLARE             CURSORC is
                   SELECT * from EMP;       begin             for v_emp in C loop     &NBS P
                 dbms_output.put_line (v_emp.ename);
               Endloop;       END;

cursors with parameters (equivalent to functions)

  
   DECLARE  cursor C (v_deptno emp.deptno%type, V_job emp.job%type) is
   Select ename, sal from EMP where Deptno=v_deptno and job=v_job;
  begin  forv_temp in C (' clerk ') loop
   Dbms_output.put_line (v_temp.ename);  endloop
  ;
   End

Updatable Cursors

Declare CURSORC is select * from EMP2 for update;
                       Begin for V_temp in C loop if (v_temp.sal<) then
               Update EMP2 Set sal = Sal * 2 where CURRENT of C;
                elsif (v_temp.sal =5000) then Deletefrom EMP2 where current of C;
              End If;
              End Loop;
         commit; End

Store procedure stored procedure (with name of the program block)

  
     Createor Replace procedure p             is--except for these two sentences, the following statements are all the same           cursor C is                 &NB Sp
     SELECT * from EMP2 for update;       begin         for v_emp in C loop       &NBS P     if (v_emp.deptno=) then                     UPDA
   TEEMP2 Set sal = Sal +10 where current of C;             ElseIf (V_emp.deptno =20) then            
           UPDATEEMP2 Set sal = sal + where current OFC;             else                   &NBS P
   UPDATEEMP2 Set sal = sal + where current of C;      
         endif;           End Lo