Show cursor FETCH data

Source: Internet
Author: User
Tags rollback
-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
Cursor Cur_emp is select * from Scott.emp; --Define a cursor
Row_emp Cur_emp%rowtype; --Define a record based on the cursor
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

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

---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
Cursor Emp_cur is
Select Empno, ename,sal from Scott.emp where job= ' manager ' for update;
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

--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;
Open emp_cur;
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
--Raise 1000 for each department manager
Update scott.emp set sal=sal+1000 where current of emp_cur;
End If;
End Loop;
Close emp_cur;

----Cursor FOR loop
--for record in Cursor name
--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

Cursor Emp_cur is
Select Empno,ename,sal from scott.emp where job= ' MANAGER ' for update;
For Emp_row in Emp_cur
Update scott.emp set sal=sal-1000 where current of emp_cur;
End Loop;
--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:
Type Emp_table_type is table of Scott.emp%rowtype index by Binary_integer;
Emp_table Emp_table_type;

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

---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:
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;
Open cur_emp;
Fetch CUR_EMP bulk collect into row_emp;
For I in 1..row_emp.count
Dbms_output.put_line (Row_emp (i) empno| | ' --'|| Row_emp (i). ename| | ' --'|| Row_emp (i). Job);
End Loop;
Close cur_emp;

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.