Show cursor FETCH data

-In Oracle, when we execute a SELECT statement from Pl/sql, the Oracle RDBMS (relational database management system) assigns a private SQL zone to the SGA shared pool for that statement.
--At the same time, put the query result set into the system global area SGA to improve the performance of accessing and modifying result sets.
--The private SQL zone contains information about the statement and the result set. Use cursors as pointers in Oracle to reference the private workspace.
-With cursors, the PL/SQL program can control the changes in the middle of the private workspace and the statement processing process.


Cursors in--pl/sql are divided into implicit cursors (Oracle handles related operations automatically when a select into which returns a single row is executed)
--and display cursors (manual handling of related operations, get multiple lines, reusability strong) two types.


--A typical query procedure is as follows:
--parsing: Make sure the SQL statement is valid, and then decide to execute the plan
-Bind: Associates a variable in a pl/sql program with a bound variable.
--Open: Open cursor
--Execute: Run the SQL statement in the SQL engine.
--Extract: Gets the next row from the result set of the cursor. After each fetch, the pl/sql moves the pointer forward in the result set. When a cursor is used, the fetch command does nothing and does not raise an error if the row is not fetched.
--CLOSE: Closes the cursor, freeing all memory occupied by the cursor.


--The state of the cursor (for example, whether it is open, how many rows of data is fetched, and so on) can be obtained using the properties of the cursor.




-----A complete example of using a display cursor
Declare
Cursor Cur_emp is select * from Scott.emp; --Define a cursor
Row_emp Cur_emp%rowtype; --Define a record based on the cursor
Begin
Open cur_emp; --Open cursor
Fetch cur_emp into row_emp;--fetch one row from the cursor add to the record


While Cur_emp%found--returns True if the record is successfully fetched


Loop
Dbms_output.put_line (Row_emp.empno | | '------'|| Row_emp.ename);
Fetch cur_emp into row_emp;--fetch one row from the cursor add to the record
End Loop;

Close cur_emp;---closing cursors
End


---provides a FOR UPDATE clause in a SELECT statement, the other person can only read the records before we execute a commit or rollback, and not modify them.
---Below are two examples of using a FOR UPDATE clause in a cursor
--1
Cursor Emp_cur is
Select Empno, ename,sal from Scott.emp where job= ' manager ' for update;
--2
Cursor Emp_cur is
Select Empno, ename, Sal from emp where job = ' manager ' for update of Sal;
--of is followed by a column name in the Query field list.

--If a FOR UPDATE clause is used in a SELECT statement for a multiple-table query, the rows in the table are locked only when a table's columns are referenced in the FOR UPDATE clause in China
-for example, in the following column, the FOR UPDATE clause does not lock any row in the table dept;
-This is because the FOR UPDATE clause refers only to the column Sal in the table EMP, and the column in table dept is not referenced.
SELECT * FROM Scott.dept
Cursor Emp_cur
Select D.dname,e.ename,e.sal from Scott.dept D, scott.emp e where e.job= ' manager ' and D.deptno =e.deptno for update of E.SA L



----A list of the FOR update clauses does not restrict us from changing only the columns listed.
----The lock or put it on top of all affected rows.
----of the list just makes it clearer what we need to change.
----does not have a keyword with, the database locks the affected rows of all the tables listed in the FROM clause.



Add a nowait keyword after-----for UPDATE clause to tell Oracle that if the table is locked by another user, there is no need to wait ...
-----Once a cursor with a for UPDATE is opened, all rows in the cursor result set are locked until the current session submits a commit statement to save the changes.
-----or use the rollback statement to cancel the modification.
When the-----commits or rolls back, the lock on the line is freed. Therefore, after a commit or rollback, we cannot fetch the execution of the forupdate cursor.
-----If we're using SELECT. When a record is fetched in a cursor defined for update, a commit or rollback must be executed, and an exit code interrupt in the loop or conditional logic continues to fetch the record from the cursor.

----The WHERE CURRENT OF clause references a cursor


---Below is a complete example that uses a display cursor to change data to achieve a 1000 pay rise for all department managers in the employee;

Select *from scott.emp

Declare
--Defines a cursor emp_cur, whose result set is all department managers in the employee, and the cursor is opened to lock the records
Cursor Emp_cur is
Select Empno,ename,sal from scott.emp where job= ' MANAGER ' for update;
Emp_row Emp_cur%rowtype;
Begin
Open emp_cur;
Loop
Fetch emp_cur into Emp_row;
If Emp_cur%notfound--if there are no records in the cursor, or the last record, exit the loop
Then
Exit
Else
--Raise 1000 for each department manager
Update scott.emp set sal=sal+1000 where current of emp_cur;
End If;
End Loop;
Commit
Close emp_cur;
End



----Cursor FOR loop
--for record in Cursor name
--loop
--EXECUTE statement
--end Loop;
--The record is not required to display the definition, and it is implicitly defined with the%rowtype attribute according to the specified cursor name. (The cursor for loop is only used when each record in the cursor needs to be processed)
Select *from scott.emp


Declare
Cursor Emp_cur is
Select Empno,ename,sal from scott.emp where job= ' MANAGER ' for update;
Begin
For Emp_row in Emp_cur
Loop
Update scott.emp set sal=sal-1000 where current of emp_cur;
End Loop;
Commit
End
--we can see that the cursor for loop does a good job of simplifying the development of cursors, we no longer need to declare records, we no longer need open,fetch and close statements,
--no longer need to use the%found property to detect whether or not the last record, all of which Oracle has implicitly done for us.




---。。 Bulk Collect
--You can use bulk in the Select Into\fetch into\returning into clause collect
---Bulk collect some of the rules that must be remembered.


--Now we use SELECT ... Bulk collect into rewrite the above explicit cursor example, the code is as follows:
Declare
Type Emp_table_type is table of Scott.emp%rowtype index by Binary_integer;
Emp_table Emp_table_type;
Begin


SELECT * Bulk collect into emp_table from scott.emp;
For I in 1..emp_table.count
Loop
Dbms_output.put_line (Emp_table (i). Empno | | ' --'|| Emp_table (i). ename);
End Loop;
End


---Of course can also be in the fetch of an explicit cursor ... Into to apply the bulk collect clause. The sample code is as follows:
Declare
Cursor Cur_emp is select *from scott.emp;
Type Row_emp_type is table of Cur_emp%rowtype index by Binary_integer;
Row_emp Row_emp_type;
Begin
Open cur_emp;
Fetch CUR_EMP bulk collect into row_emp;
For I in 1..row_emp.count
Loop
Dbms_output.put_line (Row_emp (i) empno| | ' --'|| Row_emp (i). ename| | ' --'|| Row_emp (i). Job);
End Loop;
Close cur_emp;
End