Use of Oracle Cursors

There are two types of cursors: Display cursors and implicit cursors

Properties of an implicit cursor return value type comments

Sql%rowcount integer represents the number of data rows successfully executed by the DML statement

Sql%found Boolean value of True for INSERT, delete, update, or single row query operation succeeded

Sql%notfound Boolean is opposite to Sql%found return value

Sql%isopen Boolean DML is true during execution and is false after the end

--Performing an update operation with an implicit cursor begin the update  emp Set sal = Deptno = ten;  If Sql%found then    dbms_output.put_line (' update Success ');    commit;   else     dbms_output.put_line (' Update false ');   End If;end;

Definition and operation of cursors

--DECLARE CURSOR DECLARE variable 1 data type 1, variable 2 data type 2;cursor cursor name (parameter 1 data type 1, Parameter 2 data type 2,...) is SELECT statement;--opens cursor open cursor name (parameter 1 data type 1, Parameter 2 data type 2,...) --Fetch the data fetch cursor name into variable 1, variable 2, or FETCH cursor name INFO record variable;--closing cursor close cursor name

　　

　

--Use the cursor to get the name of the deptno=10 and Saldeclarev_ename varchar2 (a); v_sal varchar2 (+); cursor emp_cursor isselect ename,sal from emp where Deptno = 10;begin  open emp_cursor;  FETCH emp_cursor into v_ename,v_sal;  Dbms_output.put_line (v_ename| | ' ' | | V_sal);  Close Emp_cursor;end;ordeclarecursor emp_cursor isselect ename,sal from emp where deptno = 10;emp_record Emp_cursor%rowty Pe;begin  open emp_cursor;  FETCH emp_cursor into Emp_record;  Dbms_output.put_line (emp_record.ename| | ' ' | | Emp_record.sal);  Close emp_cursor;end;

　　

--Use the cursor to get the first 3 data of deptno=10 Declarev_ename varchar2 (a); v_sal varchar2 (+); cursor emp_cursor isselect ename,sal from emp where Deptno = 10;begin  open emp_cursor;       For I in 1..3 LOOP         FETCH emp_cursor into v_ename,v_sal;         Dbms_output.put_line (v_ename| | ' ' | | V_sal);        END LOOP;  Close emp_cursor;end;--gets all the data for deptno=10 with a cursor, which implies the definition of the record variable, the opening of the cursor, the extraction and closing process declarecursor emp_cursor Isselect ename,sal from EMP where deptno = 10;begin for Emp_record in Emp_cursor LOOP   dbms_output.put_line (emp_record.ename| | ' ' | | emp_record.sal| | ' ' | | Emp_cursor%rowcount);   END loop;end;--also has an easier way of begin for  EMP in (select Ename,sal from emp where deptno = ten) LOOP    dbms_output.put_line ( emp.ename| | ' ' | | Emp.sal);   End Loop;end;

　　

　

--Using the cursor to get the data in the table, the use of the loop, need to exit the condition Declarev_ename varchar2 (); v_sal varchar2 (+); cursor emp_cursor isselect ename,sal from EMP; Begin  Open emp_cursor;    LOOP      FETCH emp_cursor into v_ename,v_sal;      EXIT when Emp_cursor%notfound;  --When FETCH is not executed successfully, exit loop      Dbms_output.put_line (' 123: ' | | v_ename| | ' ' | | V_sal);    END LOOP;  Close emp_cursor;end;

　　

--Cursors with Parameters Declarev_ename varchar2 (+); v_sal varchar2 (in); cursor emp_cursor (p_deptno varchar2,p_sal varchar2) isselect Ename,sal from emp where deptno = P_deptno and sal = P_sal;emp_record emp_cursor%rowtype;begin  open emp_cursor (' 20 ', ' 8 ");    LOOP      FETCH emp_cursor into v_ename,v_sal;      EXIT when Emp_cursor%notfound;      Dbms_output.put_line (v_ename| | V_sal);    END LOOP;  Close Emp_cursor;end;ordeclarev_name varchar2 (+) v_sal varchar2 (V_deptno varchar2); cursor Emp_cursor Isselect ename,sal from emp where deptno = V_deptno and sal = V_sal;begin  v_deptno:= ';  v_sal:= ' 6000 ';  Open emp_cursor;  LOOP    FETCH emp_cursor into v_name,v_sal;    EXIT when Emp_cursor%notfound;    Dbms_output.put_line (v_name| | V_sal);  END LOOP; END;

　　

--Cursors with exception handling, after the executable part, multiple branches guided by the When statement Declarev_ename VARCHAR2 ((); V_sal varchar2 (in); Cursor Emp_cursor (P_deptno Varchar2,p_sal varchar2) Isselect ename,sal from emp where deptno = P_deptno and sal = P_sal;emp_record Emp_cursor%rowtype ; Begin  Open emp_cursor (' + ', ' 8000 ');    LOOP      FETCH emp_cursor into v_ename,v_sal;      EXIT when Emp_cursor%notfound;      Dbms_output.put_line (v_ename| | V_sal);    END LOOP;  Close emp_cursor;  EXCEPTION when    invalid_cursor then      dbms_output.put_line (' Attempt to use a cursor that is not open '); end;

　　

　　

Use of Oracle Cursors