--隐式游标
set serveroutput on;
begin
if sql%isopen then dbms_output.put_line('before:已打开');
else dbms_output.put_line('before:未打开');
end if;
update emp set sal=222222222 where empno=&npo;
if sql%isopen then dbms_output.put_line('after:已打开');
else dbms_output.put_line('after:未打开');
end if;
if sql%notfound then
dbms_output.put_line('没有书籍更新');
else
commit;
dbms_output.put_line('更新数据成功'||sql%rowcount);
end if;
exception
when others then
rollback;
dbms_output.put_line('更新失败');
end;
set serveroutput on;
begin
if sql%isopen then dbms_output.put_line('before:已打开');
else dbms_output.put_line('before:未打开');
end if;
update emp set sal=222222222 where empno=&npo;
if sql%isopen then dbms_output.put_line('after:已打开');
else dbms_output.put_line('after:未打开');
end if;
if sql%notfound then
dbms_output.put_line('没有书籍更新');
else
commit;
dbms_output.put_line('更新数据成功'||sql%rowcount);
end if;
exception
when others then
rollback;
dbms_output.put_line('更新失败');
end;
--显示游标 loop循环
set serveroutput on;
declare
--创建游标
cursor cs_emp is select empno,ename,job,sal from emp;
em emp.empno%type;
en emp.ename%type;
jo emp.job%type;
sa emp.sal%type;
begin
--打开游标
if not cs_emp%isopen then
dbms_output.put_line('游标未打开');
open cs_emp;
dbms_output.put_line('游标已打开');
end if;
loop
--操作游标
fetch cs_emp into em,en,jo,sa;
dbms_output.put_line(em||':'||en||':'||jo||':'||sa);
exit when cs_emp%notfound;
end loop;
--关闭游标
close cs_emp;
end;
--显示游标 while循环
set serveroutput on;
declare
--创建游标
cursor cs_emp is select empno,ename,job,sal from emp;
em emp.empno%type;
en emp.ename%type;
jo emp.job%type;
sa emp.sal%type;
begin
--打开游标
if not cs_emp%isopen then
dbms_output.put_line('游标未打开');
open cs_emp;
dbms_output.put_line('游标已打开');
end if;
fetch cs_emp into em,en,jo,sa;
dbms_output.put_line(em||':'||en||':'||jo||':'||sa);
while cs_emp%found
loop
--操作游标
fetch cs_emp into em,en,jo,sa;
dbms_output.put_line(em||':'||en||':'||jo||':'||sa);
end loop;
--关闭游标
close cs_emp;
end;
set serveroutput on;
declare
--e emp%rowtype;
cursor cs_emp is select * from emp;
begin
for e in cs_emp loop
dbms_output.put_line(e.empno||':'||e.ename||':'||e.job);
end loop;
if cs_emp%isopen then
close cs_emp;
end if;
end;
---动态游标,引用游标
/*
1、定义动态游标数据类型 type类型名称 is ref cursor
2、声明此类型的游标变量 变量名 类型名称;
3、打开游标并制定所引用的结果集: open游标变量名 for select语句
4、操作游标 fetch 游标名 into 变量名
5、关闭
*/
set serveroutput on;
declare
type my_cr is ref cursor;
cs_ref my_cr;
num int:=0;
eno emp.empno%type;
enm emp.ename%type;
begin
num:=&nm;
if num=1 then
open cs_ref for select empno,ename from emp;
dbms_output.put_line('编号-姓名');
loop
fetch cs_ref into eno,enm;
exit when cs_ref%notfound;
dbms_output.put_line(eno||'-'||enm);
end loop;
else
open cs_ref for select deptno,dname from dept;
dbms_output.put_line('编号-部门编号');
loop
fetch cs_ref into eno,enm;
exit when cs_ref%notfound;
dbms_output.put_line(eno||'-'||enm);
end loop;
end if;
--关闭游标
if cs_ref%isopen then
close cs_ref;
end if;
end;
/*--在程序包定义动态游标类型
create or replace package pkg_test is
type mycur is ref cursor;
end;
--从表中查询数据的存储过程proc_select_test
create or replace procedure proc_select_test(cur_return out pkg_test.mycur)
is
begin
open cur_return for select empno,ename from emp;
end;
*/
create or replace package pkg_test is
type mycur is ref cursor;
end;
--从表中查询数据的存储过程proc_select_test
create or replace procedure proc_select_test(cur_return out pkg_test.mycur)
is
begin
open cur_return for select empno,ename from emp;
end;
*/
--在程序包中定义动态游标类型
create or replace package pkg_test is
type mycur is ref cursor;
end;
--从表中查询数据的存储过程proc_select_test
create or replace procedure proc_select_test(cur_return out pkg_test.mycur)
is
begin
open cur_return for select empno,ename from emp;
end;
create or replace package pkg_test is
type mycur is ref cursor;
end;
--从表中查询数据的存储过程proc_select_test
create or replace procedure proc_select_test(cur_return out pkg_test.mycur)
is
begin
open cur_return for select empno,ename from emp;
end;