What is the cursor?

A cursor literally means a moving cursor.

Description in database language: cursor is a positional entity mapped on a row of data in the result set. With cursor users, they can access any row of data in the result set. After placing the cursor on a row, they can operate on the row data, such as extracting the data of the current row and so on.

Before Oracle9i, only one row of data was extracted at a time using FETCH statements; starting with Oracle9i, FETCH was used. The BULK COLLECT INTO statement can extract multiple rows of data at a time. The grammar is as follows:
(1) FETCH cursor_name INTO variable1,variable2,…；
This method must use circular statements to process all data in the result set.
(2) FETCH cursor_name BULK COLLECT INTO collect1,collect2,…[LIMIT rows]
[LIMIT rows] can be used to limit the number of rows extracted per cursor.

Classification of cursors: explicit cursors and implicit cursors

(1) Display the use of cursors:

1. Declare cursors

CURSOR mycur(vartype number) is

select emp_no,emp_zc from cus_emp_basic

where com_no = vartype;

2. Open the cursor

Openmycur (000627) Note: 000627: Parameters

3. Read data

fetch mycur into varno,varprice;

4. Close the cursor

close mycur;

Properties of cursors

Oracle cursor has four attributes:% ISOPEN,% FOUND,% NOTFOUND,% ROWCOUNT

% ISOPEN judges whether the cursor is opened. If open% ISOPEN equals true, otherwise it equals false.

% FOUND% NOTFOUND determines whether the row in which the cursor is located is valid. If it is valid, then% FOUNDD equals true, otherwise it equals false.

% ROWCOUNT returns the number of rows of records read by the cursor at the current location.

Example:

set serveroutput on;
declare
varno varchar2(20);
varprice varchar2(20);
CURSOR mycur(vartype number) is
select emp_no,emp_zc from cus_emp_basic
where com_no = vartype;
begin
if mycur%isopen = false then
open mycur(000627);
end if;
fetch mycur into varno,varprice;
while mycur%found
loop
dbms_output.put_line(varno||','||varprice);
if mycur%rowcount=2 then
exit;
end if;
fetch mycur into varno,varprice;
end loop;
close mycur;
end;

(2) Defining record variables based on cursors
Using% ROWTYPE attribute is not tight. Record variables can be defined based on tables and views. Record variables can also be defined based on cursors. When a record variable is defined based on a cursor, the record member name is actually the column name and the column alias of the SELECT statement. In order to simplify the data processing of display cursors, it is recommended that developers use record variables to store cursor data.
For example:

DECLARE
   CURSOR emp_cursor IS SELECT ename,sal FROM emp；
   emp_reocrd emp_cursor%ROWTYPE；
BEGIN
   OPEN emp_cursor；
   LOOP
      FETCH emp_cursor INTO emp_record；
      EXIT WHEN emp_record%NOTFOUND；
      dbms_ouput.put_line('Employee name:'||emp_record.ename||',Employee wages:'||emp_record.sal)；
   END LOOP；
   CLOSE emp_cursor；
END；

(3) The use of implicit cursors:

If select statement is used in pl/sql program to operate, pl/sql will implicitly handle the definition of cursor, which is called implicit cursor. This cursor does not need to be declared, opened, and closed.

Example:

Create or replace procedure cx_xm

(in_xh in char,out_num out char)

As

Begin

       Select xm into out_xm from xs where xh=in_xh;   /*Implicit cursors must use in*/

    Dbms_output.put_line(out_xm);

End

The following points should be noted when using implicit cursors:

A. Each implicit cursor must have an in;

B. As with the display cursor, variables with keyword into receive data with the same data type as the list.

C. Implicit cursors can only return migration data at a time.

Typical cursor for loop

A quick way to use cursor for loop and display cursor. It uses for loop to read row data in result set in turn. When for loop begins, the cursor opens automatically (without opening). The system reads the current row data of cursor automatically (without fetch) once every cycle. When the cursor exits for loop, the cursor is automatically closed (without close) and the cursor for loop is used. You can't use open statement, fetch statement and close statement, otherwise you will have errors.

set serveroutput on;

declare

cursor mycur(vartype number)is

select emp_no,emp_zc from cus_emp_basic

where com_no=vartype;

begin

for person in mycur(000627) loop

dbms_output.put_line('Employee number:'||person.emp_no||',address:'||person.emp_zc);

end loop;

end;

Use queries in cursor FOR loops

A query can be defined in the cursor FOR loop. Because there is no explicit declaration, the cursor has no name, and the record name is defined by the cursor query.

DECALRE
　v_tot_salary EMP.SALARY%TYPE;
BEGIN
　FOR r_dept IN (SELECT deptno,dname FROM dept ORDER BY deptno) LOOP
　　DBMS_OUTPUT.PUT_LINE('Department:'|| r_dept.deptno||'-'||r_dept.dname);
　　v_tot_salary:=0;
　　FOR r_emp IN (SELECT ename,salary
　　　FROM emp
　　　WHERE deptno=p_dept
　　　ORDER BY ename) LOOP
　　DBMS_OUTPUT.PUT_LINE('Name:'|| v_ename||' salary:'||v_salary);
　　v_tot_salary:=v_tot_salary+v_salary;
　　END LOOP;
　　DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept:'|| v_tot_salary);
　END LOOP;
END; 

Subqueries in cursors

The grammar is as follows:

CURSOR C1 IS SELECT * FROM emp

WHERE deptno NOT IN (SELECT deptno

FROM dept

WHERE dname!='ACCOUNTING'); 

It can be seen that there is no difference from sub-queries in SQL.

Updates and deletions in cursors

UPDATE and DELETE statements can still be used to update or delete data rows in PL/SQL. Explicit cursors are used only when multiple rows of data are required. PL/SQL provides a way to delete or update records using only cursors.

WHERE CURRENT OF substrings in UPDATE or DELETE statements deal specifically with the most recent data extracted from tables to perform UPDATE or DELETE operations. To use this method, the FOR UPDATE substring must be used when declaring cursors. When a conversation opens a cursor using the FOR UPDATE substring, all data rows returned to the set will be locked exclusively at the row level (ROW-LEVEL). Other objects can only query these data rows, not UPDATE, DELETE or SELECT. For UPDATE operation.

Grammar:

FOR UPDATE [OF [schema.]table.column[,[schema.]table.column]..

[nowait]

In multi-table queries, use the OF clause to lock specific tables. If the OF clause is ignored, all selected data rows in the tables will be locked. If these data rows have been locked by other sessions, ORACLE will normally wait until the data rows are unlocked.

The grammar of using WHERE CURRENT OF substrings in UPDATE and DELETE is as follows:

WHERE{CURRENT OF cursor_name|search_condition}

Example:

DELCARE
CURSOR c1 IS SELECT empno,salary
FROM emp
WHERE comm IS NULL
FOR UPDATE OF comm;
v_comm NUMBER(10,2);
BEGIN
FOR r1 IN c1 LOOP
IF r1.salary<500 THEN
v_comm:=r1.salary*0.25;
　　ELSEIF r1.salary<1000 THEN
v_comm:=r1.salary*0.20;
　　ELSEIF r1.salary<3000 THEN
v_comm:=r1.salary*0.15;
ELSE
v_comm:=r1.salary*0.12;
END IF;
UPDATE emp;
SET comm=v_comm
WHERE CURRENT OF c1l;
END LOOP;
END

Cursor variable (REF CURSOR cursor):

Strong type (restriction) (Strong REF CURSOR), specifying return type

Weak REF CURSOR, which does not specify the return type, can obtain any resu l t set.

Similar to cursors, cursor variables point to the current row of the result set of a multiline query. However, cursors and cursor variables are different, just like the relationship between constants and variables. Cursors are static and cursor variables are dynamic because they are not bound to specific queries.

(1) Usage 1:

declare
 type cur_tab is ref cursor;
 sqlcur cur_tab;
 v_T3100101 char(2);
 v_T3100102 char(10);
begin
 open sqlcur for select T3100101,T3100102 from T31001;
 loop
 fetch sqlcur into v_T3100101,v_T3100102;
 exit when sqlcur%notfound;
 dbms_output.put_line(v_T3100101||v_T3100102);
 end loop;
 close sqlcur;
end;

(2) Usage 2: Used by stored procedures to return result sets

create or replace package selecttable
is
type cur_T31001 is ref cursor return T31001%rowtype;　　--Notice, there's no one here. begin
end selecttable;



create or replace procedure T31001_select
(
cur out selecttable.cur_T31001       --Parameter types are defined as previously defined T31001
)
is
begin
　　open cur for
　　select * from T31001;
end T31001_select;

By extracting data from the cursor workspace, data in the database can be manipulated, including modification and deletion operations.

To manipulate the database through cursors, the FOR UPDATE OF clause must be added when defining cursors.

Moreover, when UPDATE or DELETE is used, the WHERE CURRENT OF clause must be added, and the cursor row is updated or deleted.

A FOR UPDATE clause will give the row an exclusive row-level lock.

The difference between cursor and ref cursor

At the bottom of the technology, they are the same. Ordinary plsql cursor is "static" when defined. Ref cursors can be opened dynamically.

For example, the following example:

Declare
type rc is ref cursor;
cursor c is select * from dual;
l_cursor rc;
begin
if ( to_char(sysdate,'dd') = 30 ) then
       open l_cursor for 'select * from emp';
elsif ( to_char(sysdate,'dd') = 29 ) then
       open l_cursor for select * from dept;
else
       open l_cursor for select * from dual;
end if;
open c;
end;

/

L RC opens dynamically according to logic; cursor c is defined so that it cannot be modified.

L ref cursor can be returned to the client, cursor is not.

L cursor can be global, ref cursor must be defined in a process or function.

L ref cursor can be passed between subroutines, cursor can't.

Static sql defined in L cursor is more efficient than ref cursor, so ref cursor is usually used to return result sets to the client.

This article is reproduced from http://blog.csdn.net/junmail/article/details/1480395.