|
|
Posted on 3/19/2015 10:53:06 AM
|
|
|
select * from stuinfo;
--Implicit cursor
begin
dbms_output.put_line('Before-query:'|| sql%rowcount);
update stuinfo set stusex = 'female';
dbms_output.put_line('After query:'|| sql%rowcount);
end;
--explicit cursor
--1. Common
declare
cursor mycur1 is select * from stuinfo; --define cursors
--v_id stuinfo.stuid%type;
--v_name stuinfo.stuname%type;
--v_sex stuinfo.stusex%type;
--v_bir stuinfo.stubir%type;
v_row stuinfo%rowtype;
begin
open mycur1; --Open cursor
loop
--fetch mycur1 into v_id,v_name,v_sex,v_bir; --Move the cursor down to put the read value into the variable
fetch mycur1 into v_row; --Read cursor
exit when mycur1%notfound;
--dbms_output.put_line('id：'|| v_id||', name: '|| v_name||', gender: '|| v_sex||' Birthday: '|| v_bir); --Output result
dbms_output.put_line('id：'|| v_row.stuid||', name: '|| v_row.stuname||', gender: '|| v_row.stusex||' Birthday: '|| v_row.stubir);
end loop;
close mycur1; --Turn off the cursor
end;
--2. Versifier with parameters
declare
cursor mycur1(v_sex stuinfo.stusex%type) is select * from stuinfo where stusex = v_sex; --define cursors
v_row stuinfo%rowtype;
begin
open mycur1('&gender'); --Open cursor
loop
fetch mycur1 into v_row; --Read cursor
exit when mycur1%notfound;
dbms_output.put_line('id：'|| v_row.stuid||', name: '|| v_row.stuname||', gender: '|| v_row.stusex||' Birthday: '|| v_row.stubir);
end loop;
close mycur1; --Turn off the cursor
end;
--3. Circular cursor
declare
cursor mycur1 is select * from stuinfo; --define cursors
begin
for v_row in mycur1
loop
dbms_output.put_line('id：'|| v_row.stuid||', name: '|| v_row.stuname||', gender: '|| v_row.stusex||' Birthday: '|| v_row.stubir);
end loop;
end;
--4. Update the data with the cursor
declare
cursor mycur1 is select * from stuinfo for update; --define cursors
begin
for v_row in mycur1
loop
if v_row.stuid = '003' then
delete from stuinfo where current of mycur1;
else
update stuinfo set stusex = 'male' where current of mycur1;
end if;
end loop;
end;
select * from stuinfo;
update stuinfo set stusex = 'female' where stuid = '002';
--reference cursor
--1. Weak type
declare
type curname is ref cursor; --define a cursor type (curname is the type name)
mycur1 curname; --
v_sex stuinfo.stusex%type;
v_row stuinfo%rowtype;
v_row2 subinfo%rowtype;
begin
v_sex := '& gender';
if v_sex = 'male' then
open mycur1 for select * from stuinfo where stusex = 'male';
loop
fetch mycur1 into v_row; --Read cursor
exit when mycur1%notfound;
dbms_output.put_line('id：'|| v_row.stuid||', name: '|| v_row.stuname||', gender: '|| v_row.stusex||' Birthday: '|| v_row.stubir);
end loop;
else
open mycur1 for select * from subinfo;
loop
fetch mycur1 into v_row2; --Read cursor
exit when mycur1%notfound;
end loop;
end if;
close mycur1; --Turn off the cursor
end;
select * from subinfo;
--Strong type
declare
type curname is ref cursor return stuinfo%rowtype;
mycur1 curname;
v_sex stuinfo.stusex%type;
v_row stuinfo%rowtype;
begin
v_sex := '& gender';
if v_sex = 'male' then
open mycur1 for select * from stuinfo where stusex = 'male';
else
open mycur1 for select * from stuinfo where stusex = 'female';
end if;
loop
fetch mycur1 into v_row; --Read cursor
exit when mycur1%notfound;
dbms_output.put_line('id：'|| v_row.stuid||', name: '|| v_row.stuname||', gender: '|| v_row.stusex||' Birthday: '|| v_row.stubir);
end loop;
close mycur1; --Turn off the cursor
end;
|
Previous:"Wealth and Chess Game" - revealing the inside story of love, workplace, and postgraduate entrance examinationNext:php5.2.17 needs to change the cgi.force_redirect value 1 to 0 when installing
|
Posted on 3/19/2015 11:21:08 AM
|
