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.
Contains the number of rows to be processed. A pointer to the representation of the statement that was parsed and the active set of the query (active set).

A cursor is a handle (handle) or pointer to a context.

with cursors, PL/SQL can control the context area and what happens in the context when the statement is processed

② Explicit cursor Handling

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

Define a cursor: Defines a cursor name, and the corresponding SELECT statement.

Format:
CURSOR cursor_name[(parameter[, parameter] ...) is select_statement;
Cursor parameters can only be input parameters 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: Is the SELECT statement corresponding to the cursor being run. Put its query results in the workspace. And the pointer points to the header of the workspace, identifying the cursor result collection.

Suppose you have a for UPDATE option in a CURSOR query statement. The OPEN statement also locks the corresponding data rows of the database table in the index result collection.
Format:
OPEN cursor_name[([parameter =>] value[, [parameter =>] value] ...);
You can use the same value-passing method as the function parameter when passing a parameter to a cursor. That is, location notation and name notation. The PL/SQL program cannot open a cursor repeatedly with an open statement.

Extract cursor data: Is the data row in the result collection. Into the specified output variable.
Format:
FETCH cursor_name into {variable_list | record_variable};
The record is processed.
Continue processing. Until there are no records in the active 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 invalidates the working area of the cursor,
You can no longer use FETCH statements to fetch data. A closed cursor can be opened again using the Open statement.

Format:
CLOSE cursor_name;
NOTE: A defined cursor cannot have an into clause.

2. Cursor Properties
%found Boolean property. The value is true when the record is successfully returned at the time of a recent read.
%notfound boolean attribute, opposite to%found;
%isopen Boolean property. Returns True when the cursor is open;
%rowcount numeric attributes. 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. Self-actively runs the cursor's open, FETCH, close statements, and the function of the loop statement, and when entering the loop, the cursor for Loop statement itself actively 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 extracts the next line of data for processing by itself, and ends the loop after extracting all the data rows from the result set, and closes the cursor on its own initiative.
Format:
For index_variable in cursor_name[value[, value] ...] LOOP
--Cursor Data processing code
END LOOP;
Of
Index_variable an indexed variable that is implicitly declared for a cursor for loop statement, which is a record variable. The structure of the structure is the same 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 name of each element in the index_variable is the same as the column name in the cursor query statement selection list.
Assuming that a computed column exists in the selection list of a cursor query statement, the ability to access the column data through the index variable in the cursor for Loop statement is required after the specified alias for those computed columns.

Note: Do not manually manipulate the cursor in the program. Do not define a record 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 changes and deletions, the Oracle system has its own initiative to set cursors for these operations and create their workspaces.
These cursors created by the system are called implicit cursors. The name of an implicit cursor is SQL, which is defined by the Oracle system.

For the operation of an implicit cursor,
The definition, opening, value, and closing operations are done by the Oracle system on its own initiative. No user processing is required. The user can only pass the associated properties of an implicit cursor. To complete the corresponding operation.
In the client area of an implicit cursor, the data that is stored is the data that is included in the most recent processing of an SQL statement that is independent of the user's own defined display cursors.

The format call is: sql%
Implicitly-cursor Properties
Sql%found Boolean property, True if the record was successfully returned at the time of a recent read;
Sql%notfound Boolean property, as opposed 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 the implicit cursor as soon as it finishes running.

④ about the difference between No_data_found and%notfound
SELECT ... The INTO statement triggers No_data_found. (EXCEPTION when No_data_found ... ）
Fires%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 Churn and delete operations
Cursor churn and deletion refers to altering or deleting the data rows specified in the table under cursor positioning.
Then. Requires that the FOR UPDATE option must be used in a cursor query statement. To lock the cursor result collection in all columns and part of the corresponding data row in the table when the cursor is opened.
In order to be processed (query) the row is not modified by another user. ORACLE provides a for UPDATE clause to lock the selected row.
This requirement forces Oracle to lock the rows of the cursor result collection. Ability to prevent 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]
Suppose there is another session that has a lock on the rows in the active set. Then the select for update operation waits until the other sessions release the locks before continuing with their operations.
In this case, when the NOWAIT clause is added, if the rows are really locked by a session, then open returns immediately and gives:
Ora-0054:resource busy and acquire with nowait specified.
Suppose 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.
Alter or delete data rows from the corresponding database table in 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: infer if there is a 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 cursor
 close  salary_cursor;
End;

2. Requirements: Print out the wages of all employees of 80 departments: 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
--Infer employee's salary, run 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
--Infer employee's salary, run 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 a number of 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
--Infer employee's salary, run 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), if the employee is not found, print the "No This person" message

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