|
select * from stuinfo;
--隐式游标
begin
dbms_output.put_line('查询前:'||sql%rowcount);
update stuinfo set stusex = '女';
dbms_output.put_line('查询后:'||sql%rowcount);
end;
--显式游标
--1.普通的
declare
cursor mycur1 is select * from stuinfo;--定义游标
--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;--打开游标
loop
--fetch mycur1 into v_id,v_name,v_sex,v_bir;--向下移动游标,把读取出的值放入到变量里
fetch mycur1 into v_row;--读取游标
exit when mycur1%notfound;
--dbms_output.put_line('id:'||v_id||',姓名:'||v_name||',性别:'||v_sex||'生日:'||v_bir);--输出结果
dbms_output.put_line('id:'||v_row.stuid||',姓名:'||v_row.stuname||',性别:'||v_row.stusex||'生日:'||v_row.stubir);
end loop;
close mycur1;--关闭游标
end;
--2.带参数的游标
declare
cursor mycur1(v_sex stuinfo.stusex%type) is select * from stuinfo where stusex = v_sex;--定义游标
v_row stuinfo%rowtype;
begin
open mycur1('&性别');--打开游标
loop
fetch mycur1 into v_row;--读取游标
exit when mycur1%notfound;
dbms_output.put_line('id:'||v_row.stuid||',姓名:'||v_row.stuname||',性别:'||v_row.stusex||'生日:'||v_row.stubir);
end loop;
close mycur1;--关闭游标
end;
--3.循环游标
declare
cursor mycur1 is select * from stuinfo;--定义游标
begin
for v_row in mycur1
loop
dbms_output.put_line('id:'||v_row.stuid||',姓名:'||v_row.stuname||',性别:'||v_row.stusex||'生日:'||v_row.stubir);
end loop;
end;
--4.用游标更新数据
declare
cursor mycur1 is select * from stuinfo for update;--定义游标
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 = '男' where current of mycur1;
end if;
end loop;
end;
select * from stuinfo;
update stuinfo set stusex = '女' where stuid = '002';
--引用游标
--1.弱类型
declare
type curname is ref cursor;--定义一个游标类型(curname是类型名称)
mycur1 curname;--
v_sex stuinfo.stusex%type;
v_row stuinfo%rowtype;
v_row2 subinfo%rowtype;
begin
v_sex := '&性别';
if v_sex = '男' then
open mycur1 for select * from stuinfo where stusex = '男';
loop
fetch mycur1 into v_row;--读取游标
exit when mycur1%notfound;
dbms_output.put_line('id:'||v_row.stuid||',姓名:'||v_row.stuname||',性别:'||v_row.stusex||'生日:'||v_row.stubir);
end loop;
else
open mycur1 for select * from subinfo;
loop
fetch mycur1 into v_row2;--读取游标
exit when mycur1%notfound;
end loop;
end if;
close mycur1;--关闭游标
end;
select * from subinfo;
--强类型
declare
type curname is ref cursor return stuinfo%rowtype;
mycur1 curname;
v_sex stuinfo.stusex%type;
v_row stuinfo%rowtype;
begin
v_sex := '&性别';
if v_sex = '男' then
open mycur1 for select * from stuinfo where stusex = '男';
else
open mycur1 for select * from stuinfo where stusex = '女';
end if;
loop
fetch mycur1 into v_row;--读取游标
exit when mycur1%notfound;
dbms_output.put_line('id:'||v_row.stuid||',姓名:'||v_row.stuname||',性别:'||v_row.stusex||'生日:'||v_row.stubir);
end loop;
close mycur1;--关闭游标
end;
|
上一篇:《财色棋局》 --揭密恋爱,职场,考研内幕下一篇:php5.2.17在安装的时候需要把cgi.force_redirect值1改成0
|