----隐式游标
declare
begin
update emp set sal=sal+10 where empNO=73619;
if SQL%found then
dbms_output.put_line('表被更新');
end if;
if SQL%isopen then
dbms_output.put_line('test');
else
dbms_output.put_line('隐式游标始终为false');
end if;
dbms_output.put_line(SQL%Rowcount);
end;
----SQL%notfound no_data_found 异常
declare
myrow emp%rowtype;
empDept emp.deptno%type;
begin
empDept := '&部门编号';
select * into myrow from emp where emp.deptno=empDept;
exception
when no_data_found then
dbms_output.put_line('没有找到数据');
when too_many_rows then
dbms_output.put_line('返回行过多');
when others then
dbms_output.put_line('未知错误');
end;
----显式游标
declare
myrow emp%rowtype;
empDept emp.deptno%type;
cursor mycur is select * from emp where deptno=20;
begin
open mycur;
loop
fetch mycur into myrow;
exit when mycur%notfound;
dbms_output.put_line('员工姓名'||myrow.eName||'员工薪水'|| to_char(myrow.sal+nvl(myrow.comm,0)));
end loop;
close mycur;
exception
when no_data_found then
dbms_output.put_line('没有找到数据');
when too_many_rows then
dbms_output.put_line('返回行过多');
when others then
dbms_output.put_line('未知错误');
end;
----带参数显式游标
declare
myrow emp%rowtype;
empDept emp.deptno%type;
cursor mycur(deptId emp.deptno%type) is select * from emp where deptno=deptId;
begin
empDept := '&部门编号';
dbms_output.put_line('部门编号'||empDept );
open mycur(empDept);
loop
fetch mycur into myrow;
exit when mycur%notfound;
dbms_output.put_line('员工姓名'||myrow.eName||' 员工薪水'|| to_char(myrow.sal+nvl(myrow.comm,0)));
end loop;
close mycur;
exception
when no_data_found then
dbms_output.put_line('没有找到数据');
when too_many_rows then
dbms_output.put_line('返回行过多');
when others then
dbms_output.put_line('未知错误');
end;
----通过显示游标更改数据
declare
myrow emp%rowtype;
empDept emp.deptno%type;
cursor mycur is select * from emp where deptno=20 for update;
begin
open mycur;
loop
fetch mycur into myrow;
exit when mycur%notfound;
delete from emp where current of mycur;
end loop;
close mycur;
exception
when no_data_found then
dbms_output.put_line('没有找到数据');
when too_many_rows then
dbms_output.put_line('返回行过多');
when others then
dbms_output.put_line('未知错误');
end;
----循环游标 自动打开,自动提取, 自动关闭
declare
cursor mycur is select emp.empno,emp.ename from emp ;
begin
for abc in mycur
loop
dbms_output.put_line(abc.empno||' '||abc.ename);
end loop;
end;
----参数 循环游标
declare
deptID emp.deptno%type;
cursor mycur(mypara emp.deptno%type) is select emp.empno,emp.ename,emp.deptno from emp where emp.deptno=mypara;
begin
deptID := '&部门ID';
for abc in mycur(deptID)
loop
dbms_output.put_line(abc.deptno||' '||abc.empno||' '||abc.ename);
end loop;
end;
----ref cursor 强类型 (return 类型)
----弱类型 无返回类型
declare
type myCur_type is ref cursor;
mycur myCur_type;
EmpInfo emp%rowtype;
deptInfo dept%rowtype;
choice varchar2(100);
deptId emp.deptno%type;
begin
choice :=substr('&输入要查询的简写字母"员工:e,部门:d"',1,1);
deptID :='&部门部号';
dbms_output.put_line(choice);
if (choice ='e') then
open mycur for 'select * from emp where deptno=:1' using deptId;
elsif (choice ='d') then
open mycur for select * from dept;
end if;
case choice
when 'e' then
fetch mycur into EmpInfo;
dbms_output.put_line(EmpInfo.deptNO);
loop
exit when mycur%notfound;
dbms_output.put_line(EmpInfo.empno||' '|| EmpInfo.eName);
fetch mycur into EmpInfo;
end loop;
when 'd' then
loop
fetch mycur into deptInfo;
exit when mycur%notfound;
dbms_output.put_line(deptInfo.deptno||' '||deptInfo.dname);
end loop;
end case;
close mycur;
end;