Oracle Database PL/SQL cursor learning Notes

1. 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.

2. 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;
Example:

DECLARE
CURSOR c1 RETURN parameters % ROWTYPE; -- declares C1 CURSOR

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

CURSOR c1 RETURN parameters % rowtype is -- defines C1 CURSOR
SELECT * FROM orders ments
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 -- declares C4 CURSOR and defines
SELECT employee_id, job_id, salary FROM employees
WHERE salary> sal;
BEGIN
NULL;
END;
Note:

When specifying the parameter data type, the length constraint cannot be used, for example, the C4 cursor parameter, and cannot be written to the number () structure.

[RETURN datatype] is optional, indicating the data returned by the cursor. If this option is selected, it should strictly match the selection list in select_statement in order and data type. Generally, it is a RECORD or data with "% ROWTYPE.

2.2 Open cursor

Execute the SELECT statement corresponding to the cursor, put the query result into the workspace, and point the pointer to the first of the workspace to identify the cursor result set.

Syntax:

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

OPEN c4 (1300 );
2.3 read data

The data rows in the search result set are placed in the specified output variable.

Syntax:

FETCH {cursor | cursor_variable |: host_cursor_variable}
{Cmd_clause | bulk_collect_cmd_clause [LIMIT numeric_expression]};
When a FETCH statement is executed, a data row is returned each time, and 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. Therefore, after each execution of the FETCH statement, check the cursor attribute % NOTFOUND to determine whether the FETCH statement is successfully executed and return a data row to determine whether a value is assigned to the corresponding variable.

Example:

Fetch c4 into eid, jid, sal;
2.4 close the cursor

After processing the cursor result set data, close the cursor in time to release the system resources occupied by the cursor.

After the cursor is closed, you cannot use the FETCH statement to obtain the data. The closed cursor can be reopened using the OPEN statement.

Syntax:

CLOSE cursor_name;
Complete example 1:

DECLARE
-- Define a 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 the cursor
OPEN c_cursor;
-- Get data
FETCH c_cursor INTO v_ename, v_sal;
-- Process data
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 the cursor
CLOSE c_cursor;
END;
Complete Example 2:

DECLARE
-- Define the 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;
-- Defines a cursor with parameters and returned values
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 | 'The employment date is' | v_emp_record.h_date );
ELSE
DBMS_OUTPUT.PUT_LINE ('finished fruit set ');
EXIT; -- EXIT the loop after processing
End if;
End loop;
CLOSE c3; -- CLOSE the cursor
END;
3. Explicit cursor attributes

You can use the cursor attribute to obtain the status of a cursor (such as whether to open or how many rows of data are obtained.

The cursor property is added after the cursor name in the form of "% Property Name. Explicit cursor attributes include:

Attribute name description
% FOUND Returns TRUE if the record is obtained successfully; otherwise, FALSE.
% NOTFOUND: if the record fails to be obtained, TRUE is returned; otherwise, FALSE is returned.
% ROWCOUNT returns the number of records that have been obtained from the cursor.
% ISOPEN if the cursor is open, TRUE is returned; otherwise, FALSE is returned.
Example:

DECLARE
V_empno EMPLOYEES. EMPLOYEE_ID % TYPE;
V_sal EMPLOYEES. Salary % TYPE;
-- Define a cursor
CURSOR c_cursor is select EMPLOYEE_ID, Salary from employees;
BEGIN
-- Open the cursor
OPEN c_cursor;
LOOP
-- Get data
FETCH c_cursor INTO v_empno, v_sal;
Exit when c_cursor % NOTFOUND; -- EXIT the loop if the record is not read.
IF v_sal <= 1200 THEN
Update employees set Salary = Salary + 50 WHERE EMPLOYEE_ID = v_empno;
DBMS_OUTPUT.PUT_LINE ('code: '| v_empno |' salary updated! ');
End if;
DBMS_OUTPUT.PUT_LINE ('number of records: '| c_cursor % ROWCOUNT );
End loop;
-- Close the cursor
CLOSE c_cursor;
END;
4. Define record variables based on cursors

The % ROWTYPE attribute can be used not only to define record variables based on tables and views, but also to define record variables based on cursors. When the record variable is defined based on the cursor, the record member name is actually the column name and column alias of the SELECT statement.

To simplify the processing of explicit cursors, we recommend that you use cursor-based record variables to store cursor data. Defining record variables based on cursors is easier than declaring record type variables and is not prone to errors.

Example:

DECLARE
-- Define a cursor
CURSOR emp_cursor is select ename, sal FROM emp;
Emp_reocrd emp_cursor % ROWTYPE; -- cursor variable
BEGIN
-- Open the cursor
OPEN emp_cursor;
LOOP
-- Retrieve 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 the cursor
CLOSE emp_cursor;
END;
5. Implicit cursor

If the SELECT statement is used in the PL/SQL block for operations, PL/SQL implicitly processes the cursor definition. For non-query statements, such as modification and deletion operations, the ORACLE system automatically sets the cursor for these operations and creates the workspace. The cursor implicitly created by the system is called an implicit cursor, and the name of the implicit cursor is SQL.

For implicit cursor operations, such as definition, opening, value setting, and closing operations, the ORACLE system automatically completes the operation without user processing. You can perform operations only through the properties of implicit cursors. In the workspace of an implicit cursor, the stored data is the data contained in an SQL statement that is not related to the custom display cursor.

Implicit cursor attributes:

Attribute name description
SQL % FOUND Returns TRUE if the record is obtained successfully; otherwise, FALSE
SQL % NOTFOUND if the record fails to be obtained, TRUE is returned; otherwise, FALSE is returned.
SQL % ROWCOUNT returns the number of records that have been obtained from the cursor
SQL % ISOPEN if the cursor is open, TRUE is returned; otherwise, FALSE is returned.
Implicit cursors do not need to be explicitly defined in INSERT, UPDATE, DELETE, and SELECT statements.

Example:

DECLARE
V_rows NUMBER;
BEGIN
-- Update table data
UPDATE employees SET salary = 5000 WHERE department_id = 90 AND job_id = 'ad _ VP ';
-- Obtain the number of affected rows
V_rows: = SQL % ROWCOUNT;
DBMS_OUTPUT.PUT_LINE ('updated '| v_rows | 'employees' salaries ');
END;
6. Cursor FOR loop

A quick usage of the cursor FOR loop and display cursor. It uses the FOR loop to read the row data in the result set in sequence. When the FOR loop starts, the cursor is automatically opened (no OPEN is required ), the system automatically reads the data of the current row of the cursor once every cycle (FETCH is not required). When you exit the FOR loop, the cursor is automatically closed (CLOSE is not required) when you use a cursor FOR loop, you cannot use OPEN statements, FETCH statements, or CLOSE statements. Otherwise, an error occurs.

Syntax:

FOR index_variable IN cursor_name [(value [, value]…)] LOOP
-- Cursor processing statement
End loop;
Example:

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 ('No.:' | person. emp_no | ', address:' | person. emp_zc );
End loop;
END;
7. Use a display cursor to modify data

You can still use the UPDATE and DELETE statements to UPDATE or DELETE data rows in PL/SQL. An explicit cursor is used only when multiple rows of data are required. PL/SQL allows you to delete or update records by using only the cursor.

The where current of clause in the UPDATE or DELETE statement is used to process the most recent data retrieved from the table for the UPDATE or DELETE operation. To use this method, you must use the for update clause when declaring a cursor. When you use the for update clause to open a cursor, all data rows in the returned set will be in ROW-LEVEL) exclusive locking. Other objects can only query these data rows and cannot perform UPDATE, DELETE, or SELECT... For update operation.

Syntax:

For update [OF [schema.] table. column [, [schema.] table. column]...
[NOWAIT]
In multi-table queries, the "OF" clause is used to lock a specific table. If the "OF" clause is ignored, all selected data rows in the table are locked. If these data rows have been locked by other sessions, ORACLE will normally wait until the data row is unlocked. When the NOWAIT clause is added, if these rows are actually locked by another session, OPEN returns immediately and gives:

ORA-00054: resource busy and acquire with nowait specified.
The syntax for using where current of substring in UPDATE and DELETE is as follows:

WHERE {current of cursor_name | search_condition}
Example:

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
8. Cursor variable

Similar to a cursor, a cursor variable points to the current row of the result set of a multi-row query. However, the cursor and the cursor variable are different, just like the relationship between constants and variables. The cursor is static and the cursor variable is dynamic because it is not bound with a specific query.

8.1 declare a cursor variable

Syntax:

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

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

Return_type is the type of the return value of the cursor variable. It must be a record variable.

Example:

-- 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 a cursor, the cursor variable operation also includes three steps: open, extract, and close.

8.2.1 open the cursor variable

Syntax:

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

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

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

8.2.2 data extraction

Syntax:

FETCH {cursor_variable_name |: host_cursor_variable_name}
INTO {variable [, variable]… | Record_variable };
Note:

Store the extracted data in common variables and record variables.

8.2.3 close the cursor

Syntax:

CLOSE {cursor_variable_name |: host_cursor_variable_name}
Note:

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

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 No.: '| emp_record.ename | 'Department No.:' | 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 No.: '| emp_record.ename | 'Department No.:' | 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 No.: '| emp_record.ename |', Department No.: '| emp_record.deptno |', salary: '| emp_record.salary );
End loop;
CLOSE v_cursor;
END;
9. Batch retrieve using cursors

Syntax:

FETCH... bulk collect into... [LIMIT row_number];
Note:

With bulk collect, we can return multiple rows of data back and forth to the database. Bulk collect reduces the number of context switches between PL/SQL and SQL engines, thus accelerating data acquisition.

Example:

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 ID: '| emp_table (I). ename | 'salary:' | emp_table (I). sal );
End loop;
CLOSE emp_cursor;
END;
10. Cursor expression

The cursor expression is used to return nested cursors. Syntax:

CURSOR (sub_query)
Example:

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_record 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;