Type Oracle database cursors

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

A cursor is a memory area of the SQL, a system or user defined as variables. Data block cursor function is used for the temporary storage of extracted from the database.
Oracle database Cursor type contains three kinds: static cursors: divided into explicit and implicit cursor (explicit) and (implicit) the cursor; REF cursor: is a reference type, similar to a pointer.

Test data

create table student(sno number primary key,sname varchar2(10));

declare i number:=1;
begin
while i<=50  
loop 
      insert into student(sno,sname) values (i,'name'||to_char(i));
      i:=i+1;
end loop;
end;

Implicit cursor attributes:
The data for SQL%ROWCOUNT integer representing the number of DML statement executed successfully.
TRUE stands for the insert, delete, update or single query operation success SQL%FOUND Boolean value.
SQL%NOTFOUND and the SQL%FOUND property returns a Boolean values.
True SQL%ISOPEN Boolean DML implementation process, after the end of false.

declare
begin
  update student set sname ='name'||to_char(sno*10) where sname='name80';
  if sql%found then
    dbms_output.put_line('name is updated');
  else
    dbms_output.put_line('There is no record');
  end if;
end;


declare    
begin      
  for names in (select * from student) loop    
    dbms_output.put_line(names.sname);    
  end loop;    
  exception when others then    
    dbms_output.put_line(sqlerrm);    
end;

Explicit cursor attributes:
%ROWCOUNT FETCH statement returns the number of rows of data.
%The FOUND FETCH statement recent returns one row is true, false otherwise.
%NOTFOUND and the%FOUND property returns a Boolean values.
%The ISOPEN Boolean cursor has been opened as true, false otherwise.

The use of explicit cursor is divided into four steps:
A cursor - Cursor [Cursor Name] IS;
B open the cursor - Open [Cursor Name];
C operation data - Fetch [Cursor name];
D close the cursor - Close [Cursor Name];

Typical explicit cursors:

declare cursor cur_rs is select * from student;
sinfo student%rowtype;
begin
  open cur_rs;
  loop
    fetch cur_rs into sinfo;
    exit when cur_rs%%notfound;
    dbms_output.put_line(sinfo.sname); 
  end loop;
  exception when others then    
    dbms_output.put_line(sqlerrm);
end;

Explicit cursor with parameter open:

declare cursor cur_rs(in_name varchar2) is select * from student where sname=in_name;
begin
  for sinfo in cur_rs('sname') loop
    dbms_output.put_line(sinfo.sname);
  end loop;
  exception when others then    
    dbms_output.put_line(sqlerrm);
end;

Use current of update or delete operation executes the statement:

declare    
cursor cur_rs is select * from student for update;    
begin
  for sinfo in cur_rs loop 
      update student set sname=sname||'xx' where current of cur_rs;   
  end loop;   
  commit;    
  exception when others then    
  dbms_output.put_line(sqlerrm);    
end;   

REF cursor, For dynamic SQL to determine the processing operation results, using REF CURSOR, can pass the result set in a program (a program to open a cursor variable, processing data in another program).

BULK can be realized by using REF CURSOR SQL, to improve the performance of SQL.
REF CURSOR two, Strong REF CURSOR and Weak REF CURSOR.
Strong REF CURSOR: the specified retrun type, type CURSOR variable must and return type.
Weak REF CURSOR: does not specify a return type, can match with any type of CURSOR variable.

Run time based on the dynamic SQL query results traversal:

create or replace package pkg_test01 as    
type student_refcursor_type is ref cursor return student%rowtype;    
procedure student_rs_loop(cur_rs IN student_refcursor_type);    
end pkg_test01; 

create or replace package body pkg_test01 as    
procedure student_rs_loop(cur_rs IN student_refcursor_type) is    
std student%rowtype;    
begin
  loop    
    fetch cur_rs into std;    
    exit when cur_rs%NOTFOUND;    
    dbms_output.put_line(std.sname);    
  end loop;    
end student_rs_loop;    
end pkg_test01;


declare stdRefCur pkg_test01.student_refcursor_type;    
begin
  for i in 10..50 loop    
    dbms_output.put_line('Student NO=' || i);    
    open stdRefCur for select * from student where sno=i;    
    pkg_test01.student_rs_loop(stdRefCur); 
end loop;
exception when others then dbms_output.put_line(sqlerrm);    
close stdRefCur;     
end; 

The use of FORALL and BULK in the COLLECT clause. Using BULK SQL can reduce the communication overhead between PLSQL Engine and SQL Engine, improve performance.
1 accelerated INSERT, UPDATE, DELETE statement execution, is to use FORALL statements to replace loop.
2 accelerated SELECT, using BULK COLLECT INTO instead of INTO.

create table student_tmp as select sno,sname from student where 0=1;

--Removal of the primary key constraint
alter table student drop constraint SYS_C0040802;  

--Executive two times into the
insert into student select * from student where sno=50;

 
declare cursor cur_std(stdid student.sno%type) is select sno,sname from student where sno=stdid;    
type student_table_type is table of cur_std%rowtype index by pls_integer;    
student_table student_table_type;    
 
begin     
open cur_std(50);    
fetch cur_std bulk collect into student_table;    
close cur_std;    
 
for i in 1..student_table.count loop    
 dbms_output.put_line(student_table(i).sno || ' ' || student_table(i).sname);    
end loop;    
 
forall i in student_table.first..student_table.last    
 insert into student_tmp values(student_table(i).sno, student_table(i).sname);    
commit;    
 
end;    

--Clean up the experiment environment
drop table student purge;
drop package pkg_test01;

Attached: dynamic performance tables V$OPEN_CURSOR.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Andrea at December 01, 2013 - 8:42 AM