Oracle Database: PL/SQL cursor overview, oraclepl

Oracle Database: PL/SQL cursor overview, oraclepl
I. cursor Concept

Literally, it refers to a swimming cursor, which is a handle or pointer pointing to the context area.

When performing the CRUD operation in the PL/SQL block, ORACLE allocates a context zone for it in the memory. The database language used to describe the cursor is the location entity mapped to a row of data in the context result set.

You can use a cursor to access any row of data in the result set. After the cursor points to a row, you can operate on the row of data. Cursors provide an independent method for processing each row of data in a multi-row data query result set. They are a common programming method for designing embedded SQL statements.

In each user session, multiple cursors can be opened at the same time. The maximum number of cursors is defined by the OPEN_CURSORS parameter in the database initialization parameter file.

Cursors can be divided into explicit cursors and implicit cursors.

Ii. Explicit cursor

There are four steps to use an explicit cursor:

Declare/define a cursor
Open cursor
Read data
Close cursor

2.1 declare/define a cursor

Syntax:

CURSOR cursor_name
    [(parameter_dec [, parameter_dec]…)]
      [RETURN datatype]
        IS
          select_statement;
Examples:

DECLARE
  CURSOR c1 RETURN departments% ROWTYPE;-declare C1 cursor

  CURSOR c2 IS-declare C2 cursor and define
    SELECT employee_id, job_id, salary FROM employees
    WHERE salary> 2000;

  CURSOR c1 RETURN departments% ROWTYPE IS-define C1 cursor
    SELECT * FROM departments
    WHERE department_id = 110;

  CURSOR c3 RETURN locations% ROWTYPE;-declare C3 cursor

  CURSOR c3 IS-define C3 cursor
    SELECT * FROM locations
    WHERE country_id = 'JP';

  CURSOR c4 (sal number) IS-declare and define C4 cursor
    SELECT employee_id, job_id, salary FROM employees
    WHERE salary> sal;
BEGIN
  NULL;
END;
Description:

When specifying parameter data types, length constraints cannot be used. For example, the parameters of a C4 cursor cannot be written as number (10,4).

[RETURN datatype] is optional and represents the data returned by the cursor. If you choose, you should strictly match the selection list in select_statement in order and data type. Generally it is record data type (RECORD) or data with "% ROWTYPE".

2.2 Open the cursor
Execute the SELECT statement corresponding to the cursor, put its query result into the work area, and the pointer points to the head of the work area to identify the cursor result set.

grammar:

OPEN cursor_name [(cursor_parameter [[,] actual_cursor_parameter] ...)]
Examples:

OPEN c4 (1300);
2.3 Reading data
Retrieve the data rows in the result set and put them into the specified output variable.

grammar:

FETCH {cursor | cursor_variable |: host_cursor_variable}
  {into_clause | bulk_collect_into_clause [LIMIT numeric_expression]};
When the FETCH statement is executed, one data row is returned at a time, and then the cursor is automatically moved to the next data row. When the last row of data is retrieved, if the FETCH statement is executed again, the operation fails and the cursor attribute% NOTFOUND is set to TRUE. So after each execution of the FETCH statement, check the cursor attribute% NOTFOUND to determine whether the FETCH statement was successfully executed and return a data row to determine whether the corresponding variable has been assigned a value.

Examples:

fetch c4 into eid, jid, sal;
2.4 Close the cursor
After processing the cursor result set data, the cursor should be closed in time to release the system resources occupied by the cursor.

After closing the cursor, you can no longer use the FETCH statement to get the data. The closed cursor can be reopened using the OPEN statement.

grammar:

CLOSE cursor_name;
Complete example 1:

DECLARE
   -Define cursor
   CURSOR c_cursor IS
   SELECT first_name || last_name, Salary FROM EMPLOYEES WHERE rownum <11;

   -Declare variables
   v_ename EMPLOYEES.first_name% TYPE;
   v_sal EMPLOYEES.Salary% TYPE;
BEGIN
  -Open cursor
  OPEN c_cursor;
  -- retrieve data
  FETCH c_cursor INTO v_ename, v_sal;
  -- Data processing
  WHILE c_cursor% FOUND LOOP
     DBMS_OUTPUT.PUT_LINE (v_ename || '---' || to_char (v_sal));
     FETCH c_cursor INTO v_ename, v_sal;
  END LOOP;
  -Close cursor
  CLOSE c_cursor;
END;
Complete example 2:

DECLARE
   -Define RECORD record type
   TYPE emp_record_type IS RECORD (
        f_name employees.first_name% TYPE,
        h_date employees.hire_date% TYPE);
   -Declare record variables
   v_emp_record EMP_RECORD_TYPE;
   -Define the cursor, with parameters and return value
   CURSOR c3 (dept_id NUMBER, j_id VARCHAR2)
      RETURN EMP_RECORD_TYPE
   IS
      SELECT first_name, hire_date FROM employees
      WHERE department_id = dept_id AND job_id = j_id;

BEGIN
   -Open the cursor and pass the parameter value
   OPEN c3 (j_id => 'AD_VP', dept_id => 90);
   LOOP
      FETCH c3 INTO v_emp_record;-get data
      IF c3% FOUND THEN
         DBMS_OUTPUT.PUT_LINE (v_emp_record.f_name || 'employment date is || v_emp_record.h_date);
      ELSE
         DBMS_OUTPUT.PUT_LINE ('The result set has been processed');
         EXIT;-exit the loop after processing
      END IF;
   END LOOP;
   CLOSE c3; --Close the cursor
END;
Three: explicit cursor attributes
The state of the cursor (such as whether it is open, how many rows of data are acquired, etc.) can be obtained using the cursor properties.

The cursor attribute is added after the cursor name in the form of "% attribute name". The explicit cursor attributes are:

Property name Description
% FOUND returns TRUE if the record is successfully obtained, otherwise returns FALSE
% NOTFOUND returns TRUE if the record acquisition fails, otherwise returns FALSE
% ROWCOUNT returns the number of records that have been fetched from the cursor
% ISOPEN returns TRUE if the cursor is open, otherwise returns FALSE
Examples:

DECLARE
   v_empno EMPLOYEES.EMPLOYEE_ID% TYPE;
   v_sal EMPLOYEES.Salary% TYPE;
   -Define cursor
   CURSOR c_cursor IS SELECT EMPLOYEE_ID, Salary FROM EMPLOYEES;
BEGIN
   -Open cursor
   OPEN c_cursor;
   LOOP
      -- retrieve data
      FETCH c_cursor INTO v_empno, v_sal;
      EXIT WHEN c_cursor% NOTFOUND;-If the record is not read, then exit the loop
      IF v_sal <= 1200 THEN
            UPDATE EMPLOYEES SET Salary = Salary + 50 WHERE EMPLOYEE_ID = v_empno;
            DBMS_OUTPUT.PUT_LINE ('Code is' || v_empno || 'Salary updated!');
      END IF;
      DBMS_OUTPUT.PUT_LINE ('Number of records:' || c_cursor% ROWCOUNT);
   END LOOP;
   -Close cursor
   CLOSE c_cursor;
END;
Four: define the record variable based on the cursor
Using the% ROWTYPE attribute can not only define record variables based on tables and views, but also record variables based on cursors. When a record variable is defined based on a cursor, the record member name is actually the column name and column alias of the SELECT statement.

In order to simplify the data processing of explicit cursors, it is recommended to use cursor-based record variables to store cursor data. Defining record variables based on cursors is more convenient than declaring record type variables, and is not prone to errors.

Examples:

DECLARE
   -Define cursor
   CURSOR emp_cursor IS SELECT ename, sal FROM emp;
   emp_reocrd emp_cursor% ROWTYPE;-cursor variable
BEGIN
   -Open cursor
   OPEN emp_cursor;
   LOOP
      -Get records
      FETCH emp_cursor INTO emp_record;
      EXIT WHEN emp_record% NOTFOUND;
      dbms_ouput.put_line ('Employee name:' || emp_record.ename || ', employee salary:' || emp_record.sal);
   END LOOP;
   -Close cursor
   CLOSE emp_cursor;
END;
Five: implicit cursor
If a SELECT statement is used in the PL / SQL block to operate, PL / SQL will implicitly process the cursor definition, and for non-query statements, such as modify and delete operations, the ORACLE system automatically sets the cursor for these operations and creates it Workspace. Cursors created implicitly by the system are called implicit cursors, and the name of the implicit cursor is SQL.

For implicit cursor operations, such as definition, open, value, and close operations, are automatically completed by the ORACLE system, without user processing. The user can only complete the corresponding operation through the relevant attributes of the implicit cursor. In the workspace of the implicit cursor, the data stored is the data contained in a newly processed SQL statement that has nothing to do with the user-defined display cursor.

Implicit cursor properties:

Property name Description
SQL% FOUND returns TRUE if the record is successfully obtained, otherwise returns FALSE
SQL% NOTFOUND returns TRUE if the record acquisition fails, otherwise returns FALSE
SQL% ROWCOUNT returns the number of records that have been fetched from the cursor
SQL% ISOPEN returns TRUE if the cursor is open, otherwise returns FALSE
Implicit cursors do not have to be explicitly defined in INSERT, UPDATE, DELETE, SELECT statements.

Examples:

DECLARE
   v_rows NUMBER;

cord emp% ROWTYPE;
        v_name dept.dname% TYPE;
        v_dno emp.deptno% TYPE;
BEGIN
      v_dno: = & no;
      OPEN dept_emp_cursor (v_dno);
      loop
           FETCH dept_emp_cursor INTO v_name, emp_cursor;
           EXIT WHEN dept_emp_cursor% NOTFOUND;
           dbms_output.put_line ('Department name:' || v_name);
           LOOP
               FETCH emp_cursor INTO emp_record;
               EXIT WHEN emp_cursor% NOTFOUND;
               dbms_output.put_line ('employee name:' || emp_record.ename || ', salary:' || emp_record.sal);
           END LOOP;
      end loop;
      CLOSE dept_emp_cursor;
END;

BEGIN
   -Update table data
   UPDATE employees SET salary = 5000 WHERE department_id = 90 AND job_id = 'AD_VP';
   -Get the number of affected rows
   v_rows: = SQL% ROWCOUNT;
   DBMS_OUTPUT.PUT_LINE ('updated' || v_rows || 'employee's salary');
END;
Six: cursor FOR loop
Cursor FOR loop and a quick way to display the cursor, it uses the FOR loop to read the row data in the result set in turn, when the FOR loop begins, the cursor is automatically opened (no OPEN is required), the system automatically reads once every loop Take the data of the current row of the cursor (without FETCH). When exiting the FOR loop, the cursor is automatically closed (without using CLOSE). When using the cursor FOR loop, you cannot use the OPEN statement, FETCH statement, and CLOSE statement, otherwise an error will occur.

grammar:

FOR index_variable IN cursor_name [(value [, value]…)] LOOP
    -Cursor processing statements
END LOOP;
Examples:

DECLARE
    CURSOR emp_cur (vartype number) IS
        SELECT emp_no, emp_zc FROM cus_emp_basic WHERE com_no = vartype;
BEGIN
    FOR person IN emp_cur (123) LOOP
        DBMS_OUTPUT.PUT_LINE ('number:' || person.emp_no || ', address:' || person.emp_zc);
END LOOP;
END;
Seven: Use the display cursor to modify the data
UPDATE and DELETE statements can still be used to update or delete data rows in PL / SQL. Explicit cursors are only used when you need to obtain multiple rows of data. PL / SQL provides a method to delete or update records using only cursors.

The WHERE CURRENT OF clause in an UPDATE or DELETE statement specifically deals with the most recent data fetched from a table to perform an UPDATE or DELETE operation. To use this method, you must use the FOR UPDATE clause when declaring the cursor. When you use the FOR UPDATE clause to open a cursor, all rows in the returned set will be in row-level (ROW-LEVEL) exclusive lock, other objects Can query these data rows, can not perform UPDATE, DELETE or SELECT ... FOR UPDATE operation.

grammar:

FOR UPDATE [OF [schema.] Table.column [, [schema.] Table.column] ..
[NOWAIT]
In a multi-table query, the OF clause is used to lock a specific table. If the OF clause is omitted, all selected data rows in the table will be locked. If these data rows have been locked by other sessions, ORACLE will normally wait until the data rows are unlocked. When the NOWAIT clause is added, if these lines are really locked by another session, OPEN returns immediately and gives:

ORA-00054: resource busy and acquire with nowait specified.
The syntax for using the WHERE CURRENT OF substring in UPDATE and DELETE is as follows:

WHERE {CURRENT OF cursor_name | search_condition}
Examples:

DELCARE
    CURSOR c1 IS
        SELECT empno, salary FROM emp
        WHERE comm IS NULL
        FOR UPDATE OF comm;
    v_comm NUMBER (10,2);
BEGIN
    FOR r1 IN c1 LOOP
        IF r1.salary <500 THEN
            v_comm: = r1.salary * 0.25;
ELSEIF r1.salary <1000 THEN
            v_comm: = r1.salary * 0.20;
ELSEIF r1.salary <3000 THEN
            v_comm: = r1.salary * 0.15;
        ELSE
            v_comm: = r1.salary * 0.12;
        END IF;
        UPDATE emp SET comm = v_comm WHERE CURRENT OF c1;
    END LOOP;
END
Eight: cursor variable
Similar to cursors, cursor variables point to the current row of the result set of a multi-row query. However, cursors and cursor variables are different, just like the relationship between constants and variables. The cursor is static, and the cursor variable is dynamic because it is not tied to a specific query.

8.1 Declare cursor variables
grammar:

TYPE ref_type_name IS REF CURSOR
    [RETURN return_type];
Description:

There are two types of cursor variable types: strong type definition and weak type definition. The strong type definition must specify the return value type of the cursor variable, while the weak type definition does not specify the return value type.

return_type is the return value type of the cursor variable, it must be a record variable.

Examples:

-Define a REF CURSOU type
TYPE ref_cursor_type IS REF CURSOR;
-Declare a cursor variable
cv_ref REF_CURSOR_TYPE;
8.2 Use of cursor variables
Like cursors, cursor variable operations also include three steps: open, extract, and close.

8.2.1 Open cursor variable
grammar:

OPEN {cursor_variable_name |: host_cursor_variable_name}
FOR select_statement;
Description:

host_cursor_variable_name is the cursor variable declared in the PL / SQL host environment (such as OCI: ORACLE Call Interface, Pro * c program, etc.)

The OPEN ... FOR statement can reopen the cursor variable before closing the current cursor variable without causing a CURSOR_ALREAD_OPEN exception error. When the cursor variable is newly opened, the memory processing area of the previous query will be released.

8.2.2 Extracting data
grammar:

FETCH {cursor_variable_name |: host_cursor_variable_name}
INTO {variable [, variable]… | record_variable};
Description:

Put the extracted data into common variables and record variables for storage.

8.2.3 Close the cursor
grammar:

CLOSE {cursor_variable_name |: host_cursor_variable_name}
Description:

If the application attempts to close an unopened cursor variable, it will cause an INVALID_CURSOR exception error.

Example 1:

DECLARE
TYPE ref_type_table IS REF CURSOR;
v_cursor ref_type_table;
emp_record emp% rowtype;
BEGIN
     OPEN v_cursor FOR select * from emp where deptno = & no;
     LOOP
         FETCH v_cursor INTO emp_record;
         EXIT WHEN v_cursor% NOTFOUND;
         dbms_output.put_line ('Employee number:' || emp_record.ename || 'Department number:' || emp_record.deptno);
     END LOOP;
     CLOSE v_cursor;
END;
Example 2:

DECLARE
    emp_record emp% rowtype;
    TYPE ref_type_table IS REF CURSOR RETURN emp% rowtype;
    v_cursor ref_type_table;
BEGIN
     OPEN v_cursor FOR select * from emp where deptno = & no;
     LOOP
         FETCH v_cursor INTO emp_record;
         EXIT WHEN v_cursor% NOTFOUND;
         dbms_output.put_line ('Employee number:' || emp_record.ename || 'Department number:' || emp_record.deptno);
     END LOOP;
     CLOSE v_cursor;
END;

DECLARE
Type emp_record_type IS RECORD (
       ename emp.ename% TYPE,
       salary emp.sal% TYPE,
       deptno emp.deptno% TYPE);
emp_record emp_record_type;

TYPE ref_type_table IS REF CURSOR RETURN emp_record_type;
v_cursor ref_type_table;
BEGIN
     OPEN v_cursor FOR select ename, sal, deptno from emp where deptno = & no;
     LOOP
         FETCH v_cursor INTO emp_record;
         EXIT WHEN v_cursor% NOTFOUND;
         dbms_output.put_line ('employee number:' || emp_record.ename || ', department number:' || emp_record.deptno || ', salary:' || emp_record.salary);
     END LOOP;
     CLOSE v_cursor;
END;
Nine: use cursor to get in batch
grammar:

FETCH ... BULK COLLECT INTO ... [LIMIT row_number];
Description:

Using BULK COLLECT, we can use one back and forth to the database to return multiple rows of data. BULK COLLECT reduces the number of context switches between PL / SQL and SQL engines, thus accelerating the speed of data acquisition.

Examples:

DECLARE
     CURSOR emp_cursor (v_deptno number) IS SELECT * FROM EMP WHERE deptno = v_deptno;
     TYPE type_emp_table IS TABLE OF emp% ROWTYPE INDEX BY BINARY_INTEGER;
     emp_table type_emp_table;
     v_dno emp.deptno% TYPE;
BEGIN
     v_dno: = & no;
     OPEN emp_cursor (v_dno);
     FETCH emp_cursor BULK COLLECT INTO emp_table;
     CLOSE emp_cursor;
     FOR i IN 1..emp_table.COUNT LOOP
         dbms_output.put_line ('employee number:' || emp_table (i) .ename || 'salary:' || emp_table (i) .sal);
     END LOOP;
     CLOSE emp_cursor;
END;
Ten: cursor expression
Cursor expressions are used to return nested cursors. grammar:

CURSOR (sub_query)
Examples:

DECLARE
       CURSOR dept_emp_cursor (v_deptno number) IS
   SELECT dname, cursor (SELECT * FROM emp e WHERE e.deptno = d.deptno)
   FROM dept d WHERE deptno = v_deptno;
       TYPE emp_cursor_type IS REF CURSOR;
       emp_cursor emp_cursor_type;
       emp_re