PL/SQL cursor usage, plsql cursor usage

PL/SQL cursor usage, plsql cursor usage
Zookeeper

Use of cursors

① Cursor Concept
To process SQL statements, ORACLE must allocate a region called context area to process required information,
This includes the number of rows to be processed, a pointer to the expression after the statement is analyzed, and the queried active set ).
A cursor is a handle or pointer pointing to the context. Through the cursor, PL/SQL can control the context area and what will happen in the context area when processing statements.

② Explicit cursor Processing

1. Explicit cursor processing requires four PL/SQL steps:

Define a cursor: defines a cursor name and the SELECT statement corresponding to it.
Format:
CURSOR cursor_name [(parameter [, parameter]…)] IS select_statement;
The cursor parameter can only be an input parameter, in the following format:
Parameter_name [IN] datatype [{: = | DEFAULT} expression]
Length constraints are not allowed when you specify a data type. For example, NUMBER (4) and CHAR (10) are all incorrect.

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.
If the cursor query statement contains the for update option, the OPEN statement also locks the data rows corresponding to the cursor result set in the database table.
Format:
OPEN cursor_name [([parameter =>] value [, [parameter =>] value]…)];
When passing parameters to a cursor, you can use the same value passing method as the function parameter, that is, location representation and name representation. The PL/SQL program cannot OPEN a cursor again with an OPEN statement.

Extract cursor data: it is the data row in the search result set and placed in the specified output variable.
Format:
FETCH cursor_name INTO {variable_list | record_variable };
Process the record;
Continue to process until there are no records in the activity set;

Close cursor: After the cursor result set data is extracted and processed, the cursor should be closed in time to release the system resources occupied by the cursor and make the workspace of the cursor invalid,
You cannot use the FETCH statement to retrieve the data. The closed cursor can be reopened using the OPEN statement.
Format:
CLOSE cursor_name;
Note: The Defined cursor cannot have an INTO clause.

2. cursor attributes
% FOUND Boolean attribute. The value is TRUE if the last read record is returned successfully;
% NOTFOUND Boolean attribute, opposite to % FOUND;
% ISOPEN Boolean attribute. TRUE is returned when the cursor is opened;
% ROWCOUNT numeric attribute, returns the number of records read from the cursor.

3. FOR loop of the cursor
The PL/SQL language provides the OPEN, FETCH, CLOSE, and loop statements FOR the cursor to automatically execute. When the cursor enters the loop, the cursor FOR loop statement automatically opens the cursor, and extract the first row of cursor data. When the program enters the next cycle after processing the extracted data, the cursor FOR loop statement automatically extracts the next row of data FOR processing by the program, when all data rows in the result set are extracted, the cycle ends and the cursor is automatically closed.
Format:
FOR index_variable IN cursor_name [value [, value]…] LOOP
-- Cursor Data Processing code
End loop;
Where:
Index_variable is the index variable implicitly declared by the cursor FOR loop statement. This variable is the record variable, and its structure is the same as that of the structure set returned by the cursor query statement. In the program, you can reference the index record variable element to read the extracted cursor data,
The names of each element in index_variable are the same as the column names in the selection list of the cursor query statement.
If a computed Column exists in the selection list of a cursor query statement, you must specify an alias FOR these calculated columns before using the index variable in the cursor FOR loop statement to access the column data.
Note: Do not manually operate the cursor in the program; do not define records in the program to control the FOR loop.

③ Handle implicit cursors
Explicit cursors are mainly used to process query statements, especially when multiple records are queried;
For non-query statements, such as modification and deletion operations, the ORACLE system automatically sets a cursor for these operations and creates its workspace,
These System-implicitly created cursors are called implicit cursors. The implicit cursors are named SQL, which is defined by the ORACLE system. For implicit cursor operations,
For example, the definition, open, value, and close operations are all automatically completed by the ORACLE system, and no user needs to process them. 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.

Format call: SQL %
Implicit cursor property
SQL % FOUND: Boolean attribute. The value is TRUE if the last read record is returned successfully;
SQL % NOTFOUND Boolean attribute, opposite to % FOUND;
SQL % ROWCOUNT numeric attribute, returns the number of records read from the cursor;
SQL % ISOPEN Boolean attribute. The value is always FALSE. Close the implicit cursor immediately after the SQL command is executed.

④ Differences between NO_DATA_FOUND and % NOTFOUND
SELECT... The INTO statement triggers NO_DATA_FOUND; (EXCEPTION when NO_DATA_FOUND then ......)
% NOTFOUND is triggered when the WHERE clause of an explicit cursor is not found;
SQL % NOTFOUND is triggered when the WHERE clause of the UPDATE or DELETE statement is not found;
% NOTFOUND or % FOUND should be used in the extraction loop to determine the exit condition of the loop. Do not use NO_DATA_FOUND.

⑤ Cursor modification and deletion operations
The cursor modification and deletion operations refer to modifying or deleting the specified data rows in the table under the cursor positioning.
In this case, The for update option must be used in the cursor query statement to lock all the columns and some columns of the corresponding data row in the table when the cursor is opened.
To prevent rows being processed (queried) from being modified by another user, ORACLE provides a for update clause to lock the selected rows.
This requirement forces ORACLE to lock rows in the cursor result set to prevent other transactions from updating or deleting the same rows until your transaction is committed or rolled back.
Syntax:
SELECT... FROM... For update [OF column [, column]…] [NOWAIT]
If another session has applied a lock to the row in the active set, the select for update operation will not continue until other sessions release these locks,
In this case, when the NOWAIT clause is added, if these rows are actually locked by another session, OPEN returns immediately and gives:
ORA-0054: resource busy and acquire with nowait specified.
If you use for update to declare a cursor, you can use the where current of cursor_name clause in the DELETE and UPDATE statements,
Modify or delete the data rows in the database table corresponding to the current row of the cursor result set

Example 6: Use a cursor

1. Requirement: print the salary of all employees in department 80: salary: xxx
 
Declare
-- 1. Define a cursor
Cursor salary_cursor is select salary from employees where department_id = 80;
V_salary employees. salary % type;
Begin
-- 2. Open the cursor
Open salary_cursor;

-- 3. Extract the cursor
Fetch salary_cursor into v_salary;
 
-- 4. Perform cyclic operations on the cursor: Determine whether there is any next record in the cursor
While salary_cursor % found loop
Dbms_output.put_line ('salary: '| v_salary );
Fetch salary_cursor into v_salary;
End loop;
 
-- 5. Close the cursor
Close salary_cursor;
End;

2. Requirement: print the salary of all employees in department 80: Xxx's salary is: xxx
 
Declare
Cursor sal_cursor is select salary, last_name from employees where department_id = 80;
V_sal number (10 );
V_name varchar2 (20 );
Begin
Open sal_cursor;

Fetch sal_cursor into v_sal, v_name;

While sal_cursor % found loop
Dbms_output.put_line (v_name |'s salary is '| v_sal );
Fetch sal_cursor into v_sal, v_name;
End loop;

Close sal_cursor;

End;

3. Print the last_name, email, and salary information of the employee whose manager_id is 100 (using the cursor and record type)

Declare
-- Declare a cursor
Cursor emp_cursor is select last_name, email, salary from employees where manager_id = 100;

-- Declare record type
Type emp_record is record (
Name employees. last_name % type,
Email employees. email % type,
Salary employees. salary % type
);

-- Declare a record type variable
V_emp_record emp_record;
Begin
-- Open the cursor
Open emp_cursor;

-- Extract cursor
Fetch emp_cursor into v_emp_record;

-- Cyclically operate the cursor
While emp_cursor % found loop
Dbms_output.put_line (v_emp_record.name | ',' | v_emp_record.email | ',' | v_emp_record.salary );
Fetch emp_cursor into v_emp_record;
End loop;

-- Close the cursor
Close emp_cursor;
End;
(Method 2: Use the for loop)
Declare
Cursor emp_cursor is select last_name, email, salary from employees where manager_id = 100;
Begin
For v_emp_record in emp_cursor loop
Dbms_output.put_line (v_emp_record.last_name | ',' | v_emp_record.email | ',' | v_emp_record.salary );
End loop;
End;

4. Adjust the salary of employees in the company using the cursor:

Salary range adjustment Base
0-5000 5%
5000-10000 3%
10000-15000 2%
15000-1%

Declare
-- Define a cursor
Cursor emp_sal_cursor is select salary, employee_id from employees;

-- Define base variable
Temp number (4, 2 );

-- Defines the variable for storing the cursor Value
V_sal employees. salary % type;
V_id employees. employee_id % type;
Begin
-- Open the cursor
Open emp_sal_cursor;

-- Extract cursor
Fetch emp_sal_cursor into v_sal, v_id;

-- Process the cyclic Operation of the cursor
While emp_sal_cursor % found loop
-- Judge the employee's salary and perform the update operation
-- Dbms_output.put_line (v_id | ':' | v_sal );

If v_sal <= 5000 then
Temp: = 0.05;
Elsif Vanderbilt Sal <= 10000 then
Temp: = 0.03;
Elsif Vanderbilt Sal <= 15000 then
Temp: = 0.02;
Else
Temp: = 0.01;
End if;

-- Dbms_output.put_line (v_id | ':' | v_sal | ',' | temp );
Update employees set salary = salary * (1 + temp) where employee_id = v_id;

Fetch emp_sal_cursor into v_sal, v_id;
End loop;
-- Close the cursor
Close emp_sal_cursor;
End;

Use the decode function in SQL

Update employees set salary = salary * (1 + (decode (trunc (salary/5000), 0, 0.05,
1, 0.03,
2, 0.02,
0.01 )))

5. Use the cursor for loop to complete 4.

Declare
-- Define a cursor
Cursor emp_sal_cursor is select salary, employee_id id from employees;

-- Define base variable
Temp number (4, 2 );
Begin
-- Process the cyclic Operation of the cursor
For c in emp_sal_cursor loop
-- Judge the employee's salary and perform the update operation
-- Dbms_output.put_line (v_id | ':' | v_sal );

If c. salary <= 5000 then
Temp: = 0.05;
Elsif c. salary <= 10000 then
Temp: = 0.03;
Elsif c. salary <= 15000 then
Temp: = 0.02;
Else
Temp: = 0.01;
End if;

-- Dbms_output.put_line (v_id | ':' | v_sal | ',' | temp );
Update employees set salary = salary * (1 + temp) where employee_id = c. id;
End loop;
End;

6 *. A cursor with Parameters

Declare
-- Define a cursor
Cursor emp_sal_cursor (dept_id number, sal number) is
Select salary + 1000 sal, employee_id id
From employees
Where department_id = dept_id and salary> sal;

-- Define base variable
Temp number (4, 2 );
Begin
-- Process the cyclic Operation of the cursor
For c in emp_sal_cursor (sal => 4000, dept_id => 80) loop
-- Judge the employee's salary and perform the update operation
-- Dbms_output.put_line (c. id | ':' | c. sal );

If c. sal <= 5000 then
Temp: = 0.05;
Elsif c. sal <= 10000 then
Temp: = 0.03;
Elsif c. sal <= 15000 then
Temp: = 0.02;
Else
Temp: = 0.01;
End if;

Dbms_output.put_line (c. sal | ':' | c. id | ',' | temp );
-- Update employees set salary = salary * (1 + temp) where employee_id = c. id;
End loop;
End;

7. Implicit cursor: update the specified employee salary (salary increase 10). If the employee does not find the employee, print "check no employee ".

Begin
Update employees set salary = salary + 10 where Maid = 1005;

If SQL % notfound then
Dbms_output.put_line ('no such person found! ');
End if;
End;