Introduction and Use of Cursor in Oracle

Keywords: SQL Database Attribute

I. Concepts
A cursor is a memory workspace of SQL, defined by the system or user as a variable. The function of cursors is to temporarily store blocks of data extracted from databases. In some cases, data needs to be transferred from tables stored on disk to computer memory for processing, and finally the processing results are displayed or written back to the database. In this way, the speed of data processing will be improved, otherwise frequent disk data exchange will reduce efficiency.  
Types
Cursor types include three types: implicit Cursor, explicit Cursor and Ref Cursor (dynamic Cursor).  
1. Implicit Cursor:
For Select... INTO... Statement, only one data can be retrieved from the database at a time. For this type of DML Sql statement, it is implicit Cursor. For example: Select/Update/Insert/Delete operation.  
2) Function: Implicit Cusor attributes can be used to understand the operation status and results, so as to achieve process control. Cursor's attributes include:
The SQL%ROWCOUNT integer represents the number of rows of data successfully executed by the DML statement
The SQL%FOUND Boolean value TRUE represents the success of insertion, deletion, update or single-line query operations.
The Boolean type of SQL%NOTFOUND is contrary to the return value of the attribute of SQL%FOUND.
SQL%ISOPEN Boolean DML is true during execution and false after execution.
3) Implicit Cursor is a system that automatically opens and closes Cursor.
Here is a Sample:

  1. Set Serveroutput on;  
  2.   
  3. begin  
  4.     update t_contract_master set liability_state = 1 where policy_code = '123456789';  
  5.       
  6.     if SQL%Found then  
  7.        dbms_output.put_line('the Policy is updated successfully.');  
  8.        commit;  
  9.     else  
  10.       dbms_output.put_line('the policy is updated failed.');  
  11.     end if;  
  12.   
  13. end;  
  14.   
  15. /  
 

2. Explicit Cursor:
(1) For extracting multi-row data from the database, explicit Cursor is needed. The properties of explicit cursor include:
Property of cursor, return value type, meaning
% ROWCOUNT Integer Gets the Number of Data Rows Returned by FETCH Statement
% The recent FOUND Boolean FETCH statement that returns a row of data is true or false.
% NOTFOUND Boolean and% FOUND attribute return values are opposite.
% ISOPEN Boolean cursor is true when it has been opened, otherwise it is false.

(2) The application of explicit cursors is divided into four steps:
Define cursor - Cursor [Cursor Name] IS;
Open the cursor - Open [Cursor Name];
Operational data - Fetch [Cursor name]
Close the cursor --- Close [Cursor Name]. This Step must not be omitted.  
(3) The following are three common explicit Cursor uses.

  1. Set serveroutput on;  
  2.   
  3. declare   
  4.     ---define Cursor  
  5.     Cursor cur_policy is  
  6.      select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account  
  7.      from t_contract_master cm  
  8.      where cm.liability_state = 2  
  9.      and cm.policy_type = 1  
  10.      and cm.policy_cate in ('2','3','4')  
  11.      and rownum < 5  
  12.      order by cm.policy_code desc;  
  13.     curPolicyInfo cur_policy%rowtype;---Define cursor variables  
  14. Begin  
  15.    open cur_policy; ---open cursor  
  16.    Loop   
  17.      --deal with extraction data from DB  
  18.      Fetch cur_policy into curPolicyInfo;  
  19.      Exit when cur_policy%notfound;  
  20.            
  21.      Dbms_Output.put_line(curPolicyInfo.policy_code);  
  22.    end loop;  
  23.    Exception   
  24.      when others then  
  25.          close cur_policy;  
  26.          Dbms_Output.put_line(Sqlerrm);  
  27.            
  28.    if cur_policy%isopen then    
  29.     --close cursor   
  30.       close cur_policy;  
  31.    end if;  
  32. end;  
  33.   
  34. /  
  35. ------------------------  
  36.   
  37. Set serveroutput on;  
  38.   
  39. declare   
  40.     Cursor cur_policy is  
  41.      select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account  
  42.      from t_contract_master cm  
  43.      where cm.liability_state = 2  
  44.      and cm.policy_type = 1  
  45.      and cm.policy_cate in ('2','3','4')  
  46.      and rownum < 5  
  47.      order by cm.policy_code desc;  
  48.      v_policyCode t_contract_master.policy_code%type;  
  49.      v_applicantId t_contract_master.applicant_id%type;  
  50.      v_periodPrem t_contract_master.period_prem%type;  
  51.      v_bankCode t_contract_master.bank_code%type;  
  52.      v_bankAccount t_contract_master.bank_account%type;  
  53. Begin  
  54.    open cur_policy;  
  55.    Loop   
  56.      Fetch cur_policy into v_policyCode,  
  57.                            v_applicantId,  
  58.                            v_periodPrem,  
  59.                            v_bankCode,  
  60.                            v_bankAccount;  
  61.      Exit when cur_policy%notfound;  
  62.            
  63.      Dbms_Output.put_line(v_policyCode);  
  64.    end loop;  
  65.    Exception   
  66.      when others then  
  67.          close cur_policy;  
  68.          Dbms_Output.put_line(Sqlerrm);  
  69.            
  70.    if cur_policy%isopen then     
  71.       close cur_policy;  
  72.    end if;  
  73. end;  
  74. /  
  75. --------------------------------  
  76. Set serveroutput on;  
  77.   
  78. declare   
  79.     Cursor cur_policy is  
  80.      select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account  
  81.      from t_contract_master cm  
  82.      where cm.liability_state = 2  
  83.      and cm.policy_type = 1  
  84.      and cm.policy_cate in ('2','3','4')  
  85.      and rownum < 5  
  86.      order by cm.policy_code desc;  
  87. Begin  
  88.    For rec_Policy in cur_policy loop  
  89.        Dbms_Output.put_line(rec_policy.policy_code);  
  90.    end loop;  
  91.    Exception   
  92.      when others then  
  93.          Dbms_Output.put_line(Sqlerrm);  
  94.            
  95. end;  
  96.   
  97. /  

 

3. Ref Cursor (Dynamic Cursor):
1) The difference between implicit Cursor and explicit Cursor is that Ref Cursor can obtain data result sets by passing parameters during operation. The other two Cursors, which are static, determine the data result set during compilation.  
2) Ref cursor use:
 Type [Cursor type name] is ref cursor 
Define dynamic Sql statement
 Open cursor 
Operational data - Fetch [Cursor name]
 Close Cursor 
Here is a Sample:

  1. Set serveroutput on;  
  2.   
  3. Declare  
  4.     ---define cursor type name  
  5.     type cur_type is ref cursor;  
  6.     cur_policy cur_type;  
  7.     sqlStr varchar2(500);  
  8.     rec_policy t_contract_master%rowtype;  
  9. begin  
  10.    ---define dynamic Sql  
  11.    sqlStr := 'select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account from t_contract_master cm  
  12.      where cm.liability_state = 2   
  13.      and cm.policy_type = 1   
  14.      and cm.policy_cate in (2,3,4)   
  15.      and rownum < 5   
  16.      order by cm.policy_code desc ';  
  17. ---Open Cursor  
  18.   open cur_policy for sqlStr;  
  19.   loop  
  20.        fetch cur_policy into rec_policy.policy_code, rec_policy.applicant_id, rec_policy.period_prem,rec_policy.bank_code,rec_policy.bank_account;  
  21.        exit when cur_policy%notfound;  
  22.          
  23.        Dbms_Output.put_line('Policy_code:'||rec_policy.policy_code);  
  24.     
  25.   end loop;  
  26. close cur_policy;      
  27.   
  28. end;  
  29. /  

4. Common Exception s

  1. 1. Error, name, code, meaning
  2. 2. CURSOR_ALREADY_OPEN ORA_06511 Attempts to Open the Opened Cursor
  3. 3. INVALID_CURSOR ORA_01001 Attempt to use an open cursor
  4. 4. DUP_VAL_ON_INDEX ORA_00001 Store duplicate values in columns with unique index constraints
  5. 5. ZERO_DIVIDE ORA_01476. A division error with a divisor of zero occurs.
  6. 6. INVALID_NUMBER ORA_01722 Attempts to Numerically Convert Invalid Characters
  7. 7. ROWTYPE_MISMATCH ORA_06504. Types of main variables and cursors are incompatible.
  8. 8. VALUE_ERROR ORA_06502 Conversion, Truncation or Arithmetic Error
  9. 9. TOO_MANY_ROWS ORA_01422 SELECT... INTO... Statement returns more than one row of data.
  10. 10. NO_DATA_FOUND ORA_01403 SELECT... INTO... Statement with no data returned
  11. 11. TIMEOUT_ON_RESOURCE ORA_00051. A timeout error occurred while waiting for resources.
  12. 12. TRANSACTION_BACKED_OUT ORA_00060 Delivery failed due to deadlock
  13. 13. STORAGE_ERROR ORA_06500 Memory error occurred
  14. 14. PL/SQL internal error occurred in PROGRAM_ERROR ORA_06501
  15. 15. NOT_LOGGED_ON ORA_01012 Attempt to operate an unconnected database
  16. 16. LOGIN_DENIED ORA_01017 provides invalid username or password at connection time

 

Attachment: Using cursors

  1. 15-1:Receiving cursor data using scalar variables  
  2.     DECLARE  
  3.       CURSOR emp_cursor IS  
  4.         SELECT ename,job,sal FROM emp WHERE deptno=&dno;  
  5.       v_ename emp.ename%TYPE;  
  6.       v_sal emp.sal%TYPE;  
  7.       v_job emp.job%TYPE;  
  8.     BEGIN  
  9.       OPEN emp_cursor;  
  10.       LOOP  
  11.         FETCH emp_cursor INTO v_ename,v_job,v_sal;  
  12.         EXIT WHEN emp_cursor%NOTFOUND;  
  13.         dbms_output.put_line('Full name:'||v_ename||'，Post:'||v_job||'，Wages:'||v_sal);  
  14.       END LOOP;  
  15.       CLOSE emp_cursor;  
  16.     END;  
  17.     /  
  18. 15-2:Use pl/sql Recording variable receives cursor data  
  19.     DECLARE  
  20.       CURSOR emp_cursor IS  
  21.         SELECT ename,sal FROM emp ORDER BY sal DESC;  
  22.       emp_record emp_cursor%ROWTYPE;  
  23.     BEGIN  
  24.       OPEN emp_cursor;  
  25.       LOOP  
  26.         FETCH emp_cursor INTO emp_record;  
  27.         EXIT WHEN emp_cursor%NOTFOUND OR emp_cursor%ROWCOUNT>&n;  
  28.         dbms_output.put_line('Full name:'||emp_record.ename||  
  29.          '，Wages:'||emp_record.sal);  
  30.       END LOOP;  
  31.       CLOSE emp_cursor;  
  32.     END;  
  33.     /  
  34. 15-3:Use pl/sql Collection variable receives cursor data  
  35.     DECLARE  
  36.       CURSOR emp_cursor IS SELECT ename,sal FROM emp  
  37.         WHERE lower(job)=lower('&job');  
  38.       TYPE emp_table_type IS TABLE OF emp_cursor%ROWTYPE  
  39.         INDEX BY BINARY_INTEGER;  
  40.       emp_table emp_table_type;  
  41.       i INT;  
  42.     BEGIN  
  43.       OPEN emp_cursor;  
  44.       LOOP  
  45.         i:=emp_cursor%ROWCOUNT+1;  
  46.         FETCH emp_cursor INTO emp_table(i);  
  47.         EXIT WHEN emp_cursor%NOTFOUND;  
  48.         dbms_output.put_line('Full name:'||emp_table(i).ename||  
  49.           '，Wages:'||emp_table(i).sal);  
  50.       END LOOP;  
  51.       CLOSE emp_cursor;  
  52.     END;  
  53.     /  
  54. 15-4:CursorforReference to defined cursors in loops  
  55.     DECLARE  
  56.       CURSOR emp_cursor IS SELECT ename,hiredate FROM emp  
  57.         ORDER BY hiredate DESC;  
  58.     BEGIN  
  59.       FOR emp_record IN emp_cursor LOOP  
  60.         dbms_output.put_line('Full name:'||emp_record.ename  
  61.           ||',Date of work:'||emp_record.hiredate);  
  62.         EXIT WHEN emp_cursor%ROWCOUNT=&n;  
  63.       END LOOP;  
  64.     END;  
  65.     /  
  66. 15-5:CursorforDirect reference subqueries in loops  
  67.     BEGIN  
  68.       FOR emp_record IN (SELECT ename,hiredate,rownum FROM emp  
  69.         ORDER BY hiredate) LOOP  
  70.         dbms_output.put_line('Full name:'||emp_record.ename  
  71.          ||',Date of work:'||emp_record.hiredate);  
  72.         EXIT WHEN emp_record.rownum=&n;  
  73.       END LOOP;  
  74.     END;  
  75.     /  
  76. 15-6:Parameter cursor  
  77.     DECLARE  
  78.       CURSOR emp_cursor(dno NUMBER) IS  
  79.         SELECT ename,job FROM emp WHERE deptno=dno;  
  80.     BEGIN  
  81.       FOR emp_record IN emp_cursor(&dno) LOOP  
  82.         dbms_output.put_line('Full name:'||emp_record.ename  
  83.           ||',Post:'||emp_record.job);  
  84.       END LOOP;  
  85.     END;  
  86.     /  
  87. 15-7:Update cursor rows  
  88.     DECLARE  
  89.       CURSOR emp_cursor IS  
  90.         SELECT ename,sal,deptno FROM emp FOR UPDATE;  
  91.       dno INT:=&no;  
  92.     BEGIN  
  93.       FOR emp_record IN emp_cursor LOOP  
  94.         IF emp_record.deptno=dno THEN  
  95.            dbms_output.put_line('Full name:'||emp_record.ename  
  96.             ||',Original wages:'||emp_record.sal);  
  97.            UPDATE emp SET sal=sal*1.1 WHERE CURRENT OF emp_cursor;  
  98.         END IF;  
  99.       END LOOP;  
  100.     END;  
  101.     /  
  102. 15-8:Delete cursor rows  
  103.     DECLARE  
  104.       CURSOR emp_cursor IS  
  105.         SELECT ename FROM emp FOR UPDATE;  
  106.       name VARCHAR2(10):=lower('&name');  
  107.     BEGIN  
  108.       FOR emp_record IN emp_cursor LOOP  
  109.         IF lower(emp_record.ename)=name THEN  
  110.            DELETE FROM emp WHERE CURRENT OF emp_cursor;  
  111.         ELSE  
  112.            dbms_output.put_line('Full name:'||emp_record.ename);  
  113.         END IF;  
  114.       END LOOP;  
  115.     END;  
  116.     /  
  117. 15-9:Use of Clauses add row-sharing locks on a particular table．  
  118.     DECLARE  
  119.       CURSOR emp_cursor IS  
  120.         SELECT a.dname,b.ename FROM dept a JOIN emp b  
  121.         ON a.deptno=b.deptno  
  122.         FOR UPDATE OF b.deptno;  
  123.       name VARCHAR2(10):=LOWER('&name');  
  124.     BEGIN  
  125.       FOR emp_record IN emp_cursor LOOP  
  126.         IF LOWER(emp_record.dname)=name THEN  
  127.           dbms_output.put_line('Full name:'||emp_record.ename);  
  128.           DELETE FROM emp WHERE CURRENT OF emp_cursor;  
  129.         END IF;  
  130.       END LOOP;  
  131.     END;  
  132.     /  
  133. 15-10:Use cursor variables with no return type  
  134.     DECLARE  
  135.       TYPE ref_cursor_type IS REF CURSOR;  
  136.       ref_cursor ref_cursor_type;  
  137.       v1 NUMBER(6);  
  138.       v2 VARCHAR2(10);  
  139.     BEGIN  
  140.       OPEN ref_cursor FOR  
  141.         SELECT &col1 col1,&col2 col2 FROM &table WHERE &cond;  
  142.       LOOP  
  143.         FETCH ref_cursor INTO v1,v2;  
  144.         EXIT WHEN ref_cursor%NOTFOUND;  
  145.         dbms_output.put_line('col1='||v1||',col2='||v2);  
  146.       END LOOP;  
  147.       CLOSE ref_cursor;  
  148.     END;  
  149.     /  
  150. 15-11:Use cursor variables with return types  
  151.     DECLARE  
  152.       TYPE emp_cursor_type IS REF CURSOR RETURN emp%ROWTYPE;  
  153.       emp_cursor emp_cursor_type;  
  154.       emp_record emp%ROWTYPE;  
  155.     BEGIN  
  156.       OPEN emp_cursor FOR SELECT * FROM emp  
  157.         WHERE deptno=&dno;  
  158.       LOOP  
  159.         FETCH emp_cursor INTO emp_record;  
  160.         EXIT WHEN emp_cursor%NOTFOUND;  
  161.         dbms_output.put_line('Full name:'||emp_record.ename||  
  162.           ',wages:'||emp_record.sal);  
  163.       END LOOP;  
  164.       CLOSE emp_cursor;  
  165.     END;  
  166.     /  
  167. 15-12:Use fetch...bulk collect Extract all data  
  168.     DECLARE  
  169.       CURSOR emp_cursor IS  
  170.         SELECT * FROM emp WHERE LOWER(job)=LOWER('&job');  
  171.       TYPE emp_table_type IS TABLE OF emp%ROWTYPE;  
  172.       emp_table emp_table_type;  
  173.     BEGIN  
  174.       OPEN emp_cursor;  
  175.       FETCH emp_cursor BULK COLLECT INTO emp_table;  
  176.       CLOSE emp_cursor;  
  177.       FOR i IN 1.. emp_table.COUNT LOOP  
  178.         dbms_output.put_line('Full name:'||emp_table(i).ename  
  179.           ||',wages:'||emp_table(i).sal);  
  180.       END LOOP;  
  181.     END;  
  182.     /  
  183. 15-13:Use limit Clause restriction on the number of rows extracted  
  184.     DECLARE  
  185.       CURSOR emp_cursor IS SELECT * FROM emp;  
  186.       TYPE emp_array_type IS VARRAY(5) OF emp%ROWTYPE;  
  187.       emp_array emp_array_type;  
  188.     BEGIN  
  189.       OPEN emp_cursor;  
  190.       LOOP  
  191.         FETCH emp_cursor BULK COLLECT INTO emp_array LIMIT &rows;  
  192.         FOR i IN 1..emp_array.COUNT LOOP  
  193.           dbms_output.put_line('Full name:'||emp_array(i).ename  
  194.             ||',wages:'||emp_array(i).sal);  
  195.         END LOOP;  
  196.         EXIT WHEN emp_cursor%NOTFOUND;  
  197.       END LOOP;  
  198.       CLOSE emp_cursor;  
  199.     END;  
  200.     /  
  201. 15-14:Use cursor Expression  
  202.     DECLARE  
  203.       CURSOR dept_cursor(no NUMBER) IS  
  204.          SELECT a.dname,CURSOR(SELECT * FROM emp  
  205.          WHERE deptno=a.deptno)  
  206.          FROM dept a WHERE a.deptno=no;  
  207.       TYPE ref_cursor_type IS REF CURSOR;  
  208.       emp_cursor ref_cursor_type;  
  209.       emp_record emp%ROWTYPE;  
  210.       v_dname dept.dname%TYPE;  
  211.     BEGIN  
  212.       OPEN dept_cursor(&dno);  
  213.       LOOP  
  214.          FETCH dept_cursor INTO v_dname,emp_cursor;  
  215.          EXIT WHEN dept_cursor%NOTFOUND;  
  216.          dbms_output.put_line('Department name:'||v_dname);  
  217.          LOOP  
  218.            FETCH emp_cursor INTO emp_record;  
  219.            EXIT WHEN emp_cursor%NOTFOUND;  
  220.            dbms_output.put_line('----Employee name:'||emp_record.ename  
  221.             ||',post:'||emp_record.job);  
  222.          END LOOP;  
  223.       END LOOP;  
  224.       CLOSE dept_cursor;  
  225.     END;  
  226.     /  

Posted by lillyapps on Sat, 18 May 2019 01:36:31 -0700

Hot Keywords

©2024 Programmer Group