Use of PL/SQL cursors



Use of Cursors

① Cursor Concept
In order to handle SQL statements, ORACLE must allocate a region called the context area to process the required information.
This includes the number of rows to be processed, a pointer to the representation of the statement being parsed, and the active set of the query.
A cursor is a handle (handle) or pointer to a context. with cursors, PL/SQL can control the context area and what happens to the context when the statement is processed

② Explicit cursor Handling

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

Define a cursor: You define a cursor name, and a SELECT statement that corresponds to it.
Format:
CURSOR cursor_name[(parameter[, parameter] ...) is select_statement;
The cursor parameter can only be an input parameter in the form of:
Parameter_name [in] datatype [{: = | DEFAULT} expression]
You cannot use a length constraint when specifying a data type. such as number (4), CHAR (10), etc. are all wrong.

Open cursor: Executes the SELECT statement corresponding to the cursor, puts its query results in the workspace, and the pointer points to the header of the workspace, identifying the cursor result collection.
If the cursor query statement has a FOR Update option, the OPEN statement also locks the data row for the database table's index result collection.
Format:
OPEN cursor_name[([parameter =>] value[, [parameter =>] value] ...);
When passing parameters to a cursor, you can use the same method of passing values as function parameters, that is, positional notation and name notation. The PL/SQL program cannot open a cursor repeatedly with an open statement.

Extract cursor data: The data row in the result collection is retrieved and placed in the specified output variable.
Format:
FETCH cursor_name into {variable_list | record_variable};
Processing of the record;
Continue processing until there are no records in the activity collection;

Close cursors: When the cursor result collection data is fetched and processed, the cursor should be closed in time to release the system resources that the cursor occupies and invalidate the cursor's workspace.
You can no longer use FETCH statements to fetch data from them. The closed cursor can be reopened using the Open statement.
Format:
CLOSE cursor_name;
NOTE: A defined cursor cannot have an into clause.

2. Cursor Properties
%found Boolean property, True if the record was last read successfully;
%notfound boolean attribute, opposite to%found;
%isopen Boolean property that returns True when the cursor is open;
%rowcount A numeric property that returns the number of records that have been read from the cursor.

3. For loop for cursors
The PL/SQL language provides a cursor for loop statement that automatically executes the function of the cursor's open, FETCH, close statement, and Loop statement, and when it enters the loop, the cursor for loop automatically opens the cursor and extracts the first row of cursor data. When the program finishes processing the current extracted data and enters the next loop, the cursor for Loop statement automatically extracts the next row of data for the program to process, ending the loop after extracting all the data rows in the result set, and automatically closing the cursor.
Format:
For index_variable in cursor_name[value[, value] ...] LOOP
--Cursor Data processing code
END LOOP;
which
Index_variable an indexed variable that is implicitly declared for a cursor for loop statement, which is a record variable with the same structure as the structure collection returned by the cursor query statement. You can read the extracted cursor data by referencing the index record variable element in the program.
The names of the elements in the index_variable are the same as the column names in the cursor query statement selection list.
If a computed column exists in the select list of a cursor query statement, you must specify the alias for those computed columns before the column data can be accessed through the index variable in the cursor for loop statement.
Note: Do not manually manipulate cursors in your program; do not define records in your program to control the For loop.

③ processing an implicit cursor
The explicit cursor is mainly used for the processing of the query statement, especially in the case that the query result is more than one record;
For non-query statements, such as modify and delete operations, the Oracle system automatically sets cursors for these operations and creates their workspaces.
These cursors created by the system are called implicit cursors, and the name of the implicit cursor is SQL, which is defined by the Oracle system. For the operation of an implicit cursor,
Defined, opened, valued, and closed operations are performed automatically by the Oracle system without the need for user processing. The user can only complete the corresponding operation through the related properties of the implicit cursor.
In the client area of an implicit cursor, the data that is stored is the data contained in a newly processed SQL statement that is independent of the user-defined display cursor.

The format call is: sql%
Implicitly-cursor Properties
Sql%found Boolean property, True if the record was last read successfully;
Sql%notfound boolean attribute, opposite to%found;
SQL%rowcount Numeric property that returns the number of records that have been read from the cursor;
SQL%isopen Boolean property, always evaluates to false. The SQL command closes an implicit cursor immediately after execution.

④ about the difference between No_data_found and%notfound
SELECT ... The INTO statement triggers no_data_found; (EXCEPTION when No_data_found then ...) ）
Triggers%notfound when a WHERE clause of an explicit cursor is not found;
Trigger Sql%notfound when the WHERE clause of the UPDATE or DELETE statement is not found;
In the extraction loop, use%notfound or%found to determine the exit condition of the loop, and do not use No_data_found.

⑤ cursor modification and deletion operations
Cursor modification and deletion refers to modifying or deleting a specified row of data in a table under cursor positioning.
In this case, the FOR Update option must be used in a cursor query statement to lock all columns and some columns of the cursor result collection in the table for the corresponding data row when the cursor is opened.
ORACLE provides a for UPDATE clause to lock the selected row for the row being processed (query) that is not being altered by another user.
This requirement forces Oracle to lock the rows of the cursor result collection, preventing other transactions from updating or deleting the same rows until your transaction commits or rolls back.
Grammar:
SELECT ... From ... For UPDATE [of column[, column] ...] [NOWAIT]
If another session has locked a row in the active set, the Select for update operation waits until the other session releases the locks before continuing with its operation.
In this case, when the NOWAIT clause is added, if the rows are really locked by another session, open immediately returns and gives:
Ora-0054:resource busy and acquire with nowait specified.
If you use the for UPDATE to declare a cursor, you can use the WHERE CURRENT OF cursor_name clause in the DELETE and UPDATE statements.
Modifies or deletes a data row in the database table corresponding to the current row of the cursor result collection

⑥ Example: Using Cursors

1. Requirements: Print out the wages of all employees of 80 departments: salary:xxx

Declare
--1. Defining cursors
Cursor Salary_cursor is a select salary from employees where department_id = 80;
V_salary Employees.salary%type;
Begin
--2. Open cursor
Open salary_cursor;

 --3. Extract cursor
 fetch salary_cursor into v_salary;
 
 --4. Loop the cursor: Determine if there is a 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 cursor
 close  salary_cursor;
End;

2. Requirements: Print out 80 departments of all employees wages: Xxx ' s salary is:xxx

Declare
Cursor Sal_cursor is a 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 out last_name, email, salary information (using cursors, record types) for manager_id 100 employees

Declare
--Declaring cursors
Cursor Emp_cursor is select last_name, e-mail, salary from employees where manager_id = 100;

--Declaring record types
Type Emp_record is record (
Name Employees.last_name%type,
Email Employees.email%type,
Salary Employees.salary%type
);

--Declaring a variable of record type
V_emp_record Emp_record;
Begin
--Open cursor
Open emp_cursor;

--Extracting cursors
Fetch emp_cursor into V_emp_record;

--Loop 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 cursor
Close emp_cursor;
End
(Law II: use for loop)
Declare
Cursor Emp_cursor is a 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. Use cursors to adjust the salary of employees in the company:

Salary Range Adjustment Base
0-5000 5%
5000-10000 3%
10000-15000 2%
15000-1%

Declare
--Defining cursors
Cursor Emp_sal_cursor is a select salary, employee_id from employees;

--Define cardinality variables
Temp Number (4, 2);

--Define the variable that holds the cursor value
V_sal Employees.salary%type;
V_ID Employees.employee_id%type;
Begin
--Open cursor
Open emp_sal_cursor;

--Extracting cursors
Fetch emp_sal_cursor into V_sal, v_id;

--handles the loop operation of cursors
While Emp_sal_cursor%found loop
--Judge employee's salary, perform update operation
--dbms_output.put_line (v_id | | ': ' | | V_sal);

If V_sal <=
Temp: = 0.05;
elsif v_sal<= 10000 Then
Temp: = 0.03;
elsif v_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 cursor
Close emp_sal_cursor;
End

Using the Decode function in SQL

Update employees Set salary = Salary * (1 + (decode (trunc), 0, 0.05,
1, 0.03,
2, 0.02,
0.01)))

5. Use the cursor for loop to complete 4.

Declare
--Defining cursors
Cursor Emp_sal_cursor is a select salary, employee_id ID from employees;

--Define cardinality variables
Temp Number (4, 2);
Begin
--handles the loop operation of cursors
For C in Emp_sal_cursor Loop
--Judge employee's salary, perform update operation
--dbms_output.put_line (v_id | | ': ' | | V_sal);

If C.salary <=
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*. Cursors with parameters

Declare
--Defining cursors
Cursor Emp_sal_cursor (dept_id number, sal number) is
Select Salary + sal, employee_id ID
From Employees
where department_id = dept_id and Salary > sal;

--Define cardinality variables
Temp Number (4, 2);
Begin
--handles the loop operation of cursors
For C in emp_sal_cursor (sal = 4000, dept_id =) loop
--Judge employee's salary, perform update operation
--dbms_output.put_line (C.id | | ': ' | | C.sal);

If C.sal <=
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 (up to 10) and print the "No this person" message if the employee is not found

Begin
Update employees Set salary = salary + where employee_id = 1005;

If Sql%notfound Then
Dbms_output.put_line (' Check none of this person! ');
End If;
End

Use of PL/SQL cursors