| |
Oracle cursor into explicit games, game implicit explicit cursor is a user define their own operation for processing the results of the query SELECT query returns multiple rows; implicit cursor is automatically operated, for processing DML statements and returns a single row of data SELECT query.
In this article, we focus on explicit cursor.
First, the use of the cursor step
Step four steps: Define a cursor, the cursor is opened to retrieve the cursor, close the cursor.
1, the definition of the cursor
Cursor must be declared in the statement PL / SQL block section.
CURSOR cursor_name IS select_statement;
When you define a cursor does not generate seletct_statement (SELECT queries) data, when the cursor is opened, it really generates the data.
2, open the cursor
OPEN cursor_name;
Only after the cursor is opened, only the data from the database query cache buffer memory allocation.
3, to retrieve the cursor
FETCH curso_name INTO variable_list;
Retrieving the cursor on the premise that, you must open the cursor, open cursor only, and will have a data buffer.
Cursor to retrieve data, only down, not be rolled back, for example: after retrieving the second data retrieval want to first data, you must close the cursor, open the cursor again, to retrieve the first data.
4, close the cursor
After processing the data retrieved a cursor, the cursor must be closed in order to free up system resources it takes up, as occupied by the internal buffer
CLOSE curso_name;
Second, using the example of the cursor
declare
- Declare cursor
cursor c_dept is
select * from dept t order by t.deptno;
v_dept c_dept% rowtype;
begin
--open cursor c_dept
open c_dept;
- Search cursor c_dept
loop
fetch c_dept into v_dept;
dbms_output.put_line
( 'Deptno:' || v_dept.deptno || ', dname:' || v_dept.dname || ', loc:' || v_dept.loc);
exit when c_dept% notfound;
end loop;
--close cursor
close c_dept;
end; |
|
|
|