Cursor


create table test_for_cursor (
 i number(3),
 c varchar(50)
);

insert into test_for_cursor values (1, 'one');
insert into test_for_cursor values (10, 'ten');
insert into test_for_cursor values (5, 'five');
insert into test_for_cursor values (99, 'ninety-nine');
insert into test_for_cursor values (42, 'forty-two');

commit;
declare
 cursor cur_test is 
 select i, c from test_for_cursor for update;
 
 i number(3);
 c varchar(50);
begin
 open cur_test;
loop
 fetch cur_test into i, c;
 
 exit when cur_test%notfound;
if i>12 then
 update test_for_cursor set i=i*2, c=upper(c) where current of cur_test;
 end if;
 end loop;
end;
/
drop table test_for_cursor;
----Static cursors in PL/SQL
create table test_for_cursor (
 a varchar2(10),
 b number
);
insert into test_for_cursor values ('one', 1);
insert into test_for_cursor values ('two', 2);
insert into test_for_cursor values ('three', 3);
insert into test_for_cursor values ('four', 4);
insert into test_for_cursor values ('five', 5);
commit;
declare
 cursor cur_t is select * from test_for_cursor;
 
 rec_t test_for_cursor%rowtype;
begin
 open cur_t;
 loop
 fetch cur_t into rec_t;
 exit when cur_t%notfound;
 dbms_output.put_line('a: ' || rec_t.a || ', b: ' || rec_t.b);
 end loop;
end;
/
drop table test_for_cursor;
----- Dynamic cursors in PL/SQL
create or replace package dynamic_cursor is
type t_crs is ref cursor;
procedure dyn_sel (
 tab_name in varchar2,
 field_name in varchar2,
 val in varchar2,
 crs in out t_crs);
procedure openCursor;
end dynamic_cursor; 
/
create or replace package body dynamic_cursor as
 procedure dyn_sel (
 tab_name in varchar2,
 field_name in varchar2,
 val in varchar2,
 crs in out t_crs)
is
 stmt varchar2(100);
 begin
 stmt := 'select * from ' || tab_name || ' where ' || field_name || ' = :1 ';
open crs for stmt using val;
 end dyn_sel;
procedure openCursor is
 tc t_crs;
 f1 varchar2(50);
 f2 varchar2(50);
 begin
 dyn_sel('test_for_cursor','a','two',tc);
 loop
 fetch tc into f1,f2;
 exit when tc%notfound;
 dbms_output.put_line(f2);
 end loop;
 end openCursor;
 
end dynamic_cursor; 
/
begin
 dynamic_cursor.openCursor;
end;
/
----Cursors with parameters
create table tbl (
 i number,
 c varchar2(10) 
);
insert into tbl values(1,'foo');
insert into tbl values(4,'foo');
insert into tbl values(2,'bar');
insert into tbl values(3,'foo');
insert into tbl values(5,'bar');
create or replace function max_of_tbl(val in varchar2)
 return number
as
 cursor cur(v varchar2) is
 select max(i) from tbl 
 where c = v;
ret number;
begin
 open cur(val);
fetch cur into ret;
 
 close cur;
return ret;
end;
/
select max_of_tbl('foo') from dual;
select max_of_tbl('bar') from dual;
----PL/SQL: Cursor for Update Example
create table f (a number, b varchar2(10));
insert into f values (5,'five');
insert into f values (6,'six');
insert into f values (7,'seven');
insert into f values (8,'eight');
insert into f values (9,'nine');
commit;
create or replace procedure wco as
 cursor c_f is 
 select a,b from f where length(b) = 5 for update;
 v_a f.a%type;
 v_b f.b%type;
begin
 open c_f;
 loop
 fetch c_f into v_a, v_b;
 exit when c_f%notfound;
 update f set a=v_a*v_a where current of c_f;
 end loop;
close c_f;
end;
/
exec wco;
select * from f;
drop table f;
drop procedure wco;

-----Joining multiple tables
create table numbers_en (
 id_num number primary key,
 txt_num varchar2(10)
);
insert into numbers_en values (1, 'one' );
insert into numbers_en values (2, 'two' );
insert into numbers_en values (3, 'three');
insert into numbers_en values (4, 'four' );
insert into numbers_en values (5, 'five' );
insert into numbers_en values (6, 'six' );

create table lang (
 id_lang char(2) primary key,
 txt_lang varchar2(10)
);

insert into lang values ('de', 'german');
insert into lang values ('fr', 'french');
insert into lang values ('it', 'italian');

create table translations (
 id_num references numbers_en,
 id_lang references lang,
 txt_trans varchar2(10) not null
);

insert into translations values (1, 'de', 'eins' );
insert into translations values (1, 'fr', 'un' );
insert into translations values (2, 'it', 'duo' );
insert into translations values (3, 'de', 'drei' );
insert into translations values (3, 'it', 'tre' );
insert into translations values (4, 'it', 'quattro');
insert into translations values (6, 'de', 'sechs' );
insert into translations values (6, 'fr', 'six' );

declare
cursor cur is 
 select id_num,
 txt_num,
 id_lang,
 txt_lang,
 txt_trans
 from numbers_en join translations using(id_num)
 left join lang using(id_lang)
 for update of translations.txt_trans;
rec cur%rowtype;
begin
for rec in cur loop
dbms_output.put (
 to_char (rec.id_num , '999') || ' - ' || 
 rpad (rec.txt_num , 10 ) || ' - ' || 
 rpad(nvl(rec.txt_trans, ' '), 10 ) || ' - ' || 
 rec.id_lang || ' - ' || 
 rpad (rec.txt_lang , 10 )
 );
if mod(rec.id_num,2) = 0 then
 update translations set txt_trans = upper(txt_trans) 
 where current of cur;
 dbms_output.put_line(' updated');
 else
 dbms_output.new_line;
 end if;
end loop;
end;
/
 
----Ref cursors [PL/SQL] 
 create table rc_tbl (a number, b varchar2(10));
insert into rc_tbl values (1, 'one');
insert into rc_tbl values (2, 'two');
insert into rc_tbl values (3, 'three');
create or replace procedure rc_proc(rc in out sys_refcursor) as
begin
 open rc for select * from rc_tbl;
end;
/
variable rc_var refcursor
begin rc_proc(:rc_var); end;
/
print rc_var
drop table rc_tbl;
drop procedure rc_proc;
 
----Cursor for nested cursors 
 create or replace function func_sys_refcursor (
 rc in sys_refcursor
) return number as
v_a number;
 v_b varchar2(10);
v_ret number := 0;
begin
loop
 fetch rc into v_a, v_b;
 exit when rc%notfound;
v_ret := length(v_b) * v_a + v_ret;
 end loop;
return v_ret;
end;
/
select
 func_sys_refcursor(
 cursor (
 select * from table_ref_cursor
 )
 )
from dual;
 
---- ref cursor from record type [PL/SQL] 
 create or replace package drop_me_ref_cursor as
 type rec_t is record (a number, b varchar2(10));
type ref_cur_t is ref cursor return rec_t;
procedure do (c in ref_cur_t);
end drop_me_ref_cursor;
/
create or replace package body drop_me_ref_cursor as
procedure do(c in ref_cur_t) is
 r rec_t;
 begin
fetch c into r;
loop
 exit when c%notfound;
fetch c into r;
 end loop;
end do;
end drop_me_ref_cursor;
/
----PL/SQL: Cursor for Update Example 
 
create table f (a number, b varchar2(10));
insert into f values (5,'five');
insert into f values (6,'six');
insert into f values (7,'seven');
insert into f values (8,'eight');
insert into f values (9,'nine');
commit;
create or replace procedure wco as
 cursor c_f is 
 select a,b from f where length(b) = 5 for update;
 v_a f.a%type;
 v_b f.b%type;
begin
 open c_f;
 loop
 fetch c_f into v_a, v_b;
 exit when c_f%notfound;
 update f set a=v_a*v_a where current of c_f;
 end loop;
close c_f;
end;
/
exec wco;
select * from f;
drop table f;
drop procedure wco;
---Joining multiple tables
create table numbers_en (
 id_num number primary key,
 txt_num varchar2(10)
);
insert into numbers_en values (1, 'one' );
insert into numbers_en values (2, 'two' );
insert into numbers_en values (3, 'three');
insert into numbers_en values (4, 'four' );
insert into numbers_en values (5, 'five' );
insert into numbers_en values (6, 'six' );
create table lang (
 id_lang char(2) primary key,
 txt_lang varchar2(10)
);
insert into lang values ('de', 'german');
insert into lang values ('fr', 'french');
insert into lang values ('it', 'italian');
create table translations (
 id_num references numbers_en,
 id_lang references lang,
 txt_trans varchar2(10) not null
);
insert into translations values (1, 'de', 'eins' );
insert into translations values (1, 'fr', 'un' );
insert into translations values (2, 'it', 'duo' );
insert into translations values (3, 'de', 'drei' );
insert into translations values (3, 'it', 'tre' );
insert into translations values (4, 'it', 'quattro');
insert into translations values (6, 'de', 'sechs' );
insert into translations values (6, 'fr', 'six' );
declare
cursor cur is 
 select id_num,
 txt_num,
 id_lang,
 txt_lang,
 txt_trans
 from numbers_en join translations using(id_num)
 left join lang using(id_lang)
 for update of translations.txt_trans;

 rec cur%rowtype;
begin
for rec in cur loop
dbms_output.put (
 to_char (rec.id_num , '999') || ' - ' || 
 rpad (rec.txt_num , 10 ) || ' - ' || 
 rpad(nvl(rec.txt_trans, ' '), 10 ) || ' - ' || 
 rec.id_lang || ' - ' || 
 rpad (rec.txt_lang , 10 )
 );
if mod(rec.id_num,2) = 0 then
 update translations set txt_trans = upper(txt_trans) 
 where current of cur;
 dbms_output.put_line(' updated');
 else
 dbms_output.new_line;
 end if;
end loop;
end;
/
 declare
CURSOR c1
 IS
 SELECT /*+ parallel(t 64) */ *
 FROM storage_medium t
 where sm_id>0;
 --and rownum<100;
 --for update of BUSINESS_UNIT_ID;
 k1 c1%rowtype ; 
 say number:=0; 
begin
open c1;
loop
 fetch c1 into k1;
exit when c1%notfound;
update storage_medium a set a.BUSINESS_UNIT_ID=1
where sm_id=k1.sm_id; 
say:=say+1;
if nvl(say,0)=10000 then
commit;
say:=0;
----exit;
end if; 
end loop;
commit;
close c1;
end;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: