游标管理

                                                       游标管理
/*
游标在oracle编程中很重要，我写了些代码，便于大家对游标有个直接的感受，我这里简单给大家演示下游标运行的结果情况！由于时间关系我这里只是演示下运行情况，就不在每个代码都自己写！
大家在看这个演示时可以参考我sqlserver 2000的演示情况！

游标分为：静态游标（隐式游标和显示游标）和REF游标。
*/

 

--环境表
create table toys
(toyid varchar2(10),
toyname varchar2(15),
toyprice number
);

INSERT INTO toys VALUES('P001', 'Doll', 100);
INSERT INTO toys VALUES('P002','Batman',125);
INSERT INTO toys VALUES('P003','Spiderman',110);
INSERT INTO toys VALUES('P004','He-man',200);
COMMIT;

 

------------------------------------------第一部分：静态游标------------------------------------------------------------------------------
---------------------------------------------知识点1：隐式游标的使用

--案例00：隐式游标的简单使用
create table test
(sid int not null,
sno int not null)

begin
insert into test values (1001,2001);
dbms_output.put_line(' 隐式游标影响的行数：   '||sql%rowcount);
end;

--案例01：隐式游标的简单使用(多行数据使用)

begin
insert into test values (1003,2003);
insert into test values (1004,2004);
dbms_output.put_line(' 隐式游标影响的行数：   '||sql%rowcount);
end;

--案例02：同时测试几个隐式游标属性

/*别的几个属性结果是布尔运算，所以需要判断结果真假*/

begin
  insert into test values (1008,2008);  /*这里的数据不能和表中数据重复，否则报错*/
  dbms_output.put_line(' 隐式游标影响的行数：   '||sql%rowcount);
  if sql%notfound then   --sql%notfound为布尔运算只能判断真或假
      dbms_output.put_line('notfound为真！'); 
  else
       dbms_output.put_line('notfound为假！');
  end if;
  if sql%found then   --sql%found为布尔运算只能判断真或假
       dbms_output.put_line('found为真！'); 
 else
      dbms_output.put_line('found为假！');
  end if;
end;

--案例03：%isopen属性

begin
  insert into test values (1008,2008);  /*这里的数据不能和表中数据重复，否则报错*/
  dbms_output.put_line(' 隐式游标影响的行数：   '||sql%rowcount);
  if sql%notfound then   --sql%notfound为布尔运算只能判断真或假
      dbms_output.put_line('notfound为真！'); 
  else
       dbms_output.put_line('notfound为假！');
  end if;
  if sql%found then   --sql%found为布尔运算只能判断真或假
       dbms_output.put_line('found为真！'); 
 else
      dbms_output.put_line('found为假！');
  end if;
  if sql%isopen then   --sql%isopen为布尔运算只能判断真或假
       dbms_output.put_line('isopen为真！'); 
 else
      dbms_output.put_line('isopen为假！');
  end if;
end;

--案例04：使用update查看隐式游标属性

 

begin
 update test set sid=2018;  --这里的数据不能和表中数据重复，否则报错
  dbms_output.put_line(' 隐式游标影响的行数：   '||sql%rowcount);
  if sql%notfound then   --sql%notfound为布尔运算只能判断真或假
      dbms_output.put_line('notfound为真！');
  else
       dbms_output.put_line('notfound为假！');
  end if;
  if sql%found then   --sql%found为布尔运算只能判断真或假
       dbms_output.put_line('found为真！');
 else
      dbms_output.put_line('found为假！');
  end if;
  if sql%isopen then   --sql%isopen为布尔运算只能判断真或假
       dbms_output.put_line('isopen为真！');
 else
      dbms_output.put_line('isopen为假！');
  end if;
end;

--案例05：删除表中数据再更新表查看隐式游标属性

delete from test;

begin
 update test set sid=2008;  --这里的数据不能和表中数据重复，否则报错
  dbms_output.put_line(' 隐式游标影响的行数：   '||sql%rowcount);
  if sql%notfound then   --sql%notfound为布尔运算只能判断真或假
      dbms_output.put_line('notfound为真！');
  else
       dbms_output.put_line('notfound为假！');
  end if;
  if sql%found then   --sql%found为布尔运算只能判断真或假
       dbms_output.put_line('found为真！');
 else
      dbms_output.put_line('found为假！');
  end if;
  if sql%isopen then   --sql%isopen为布尔运算只能判断真或假
       dbms_output.put_line('isopen为真！');
 else
      dbms_output.put_line('isopen为假！');
  end if;
end;

 

-------------------------------------知识点2：显示游标的使用

--案例00：显示游标的简单使用
declare
cursor empcur is select * from emp; --定义游标
emprow emp%rowtype;  /*该变量与表emp中的所有列属性相同，可以保存多行数据*/
begin
 open empcur;  --打开游标
 fetch empcur into emprow;  --从游标中取值
 dbms_output.put_line('员工编号:' ||emprow.empno || '员工姓名:' ||emprow.ename);
--这里使用 “变量名.列名”的形式
 close empcur;  --关闭游标
 end;

--案例01：显示游标循环提取值

declare
cursor empcur is select * from emp;
emprow emp%rowtype;  /*该变量与表emp中的所有列属性相同，可以保存多行数据*/
begin
 open empcur;
  loop           --循环从游标中提取值到变量中
 fetch empcur into emprow;
 dbms_output.put_line('员工编号:' ||emprow.empno || '员工姓名:' ||emprow.ename);
--这里使用 “变量名.列名”的形式
exit when empcur%notfound;  /*显示游标在使用游标属性时需要使用游标名作为前缀。当没有值的时候将不在循环取值！*/
end loop;
close empcur;
 end;

--案例02：显示游标循环提取值并打印出游标属性％rowcount

declare
cursor empcur is select * from emp;
emprow emp%rowtype;  /*该变量与表emp中的所有列属性相同，可以保存多行数据*/
begin
 open empcur;
  loop
 fetch empcur into emprow;
 dbms_output.put_line('员工编号:' ||emprow.empno || '员工姓名:' ||emprow.ename);
--这里使用 “变量名.列名”的形式
 dbms_output.put_line('游标影响的行数为：' ||empcur%rowcount);
--这里格式为：游标名游标属性名
exit when empcur%notfound;  /*显示游标在使用游标属性时需要使用游标名作为前缀。*/
end loop;
close empcur;
 end;

 

--示例03：显示游标的使用方法

declare
jt emp.ename%type;   /*变量使用来保存游标中的数据,只能保存一行数据*/
cursor k01 is
select ename from emp where empno <7500;
begin
open k01;
loop
fetch k01 into jt;
exit when k01%notfound;    /*显示游标使用游标属性时需要添加游标名作为前缀。*/
dbms_output.put_line(k01%rowcount || jt);
/*显示的是游标属性的影响数目和变量值。由于变量保存的是单列值所以不需要变“量名.列名”的格式*/
end loop;
close k01;
end;

--案例04：使用while循环处理游标中提取的值

declare
cursor empcur is select * from emp;
emprow emp%rowtype;  /*该变量与表emp中的所有列属性相同，可以保存多行数据*/
begin
 open empcur;
while  empcur%found loop   /*当empcur%notfound条件满足即为true时开始循环*/
 fetch empcur into emprow;
 dbms_output.put_line('员工编号:' ||emprow.empno || '员工姓名:' ||emprow.ename);
--这里使用 “变量名.列名”的形式
 dbms_output.put_line('游标影响的行数为：' ||empcur%rowcount);
--这里格式为：游标名游标属性名
end loop;
close empcur;
 end;

/*不显示结果的原因在于第一次判断时是空的，当第一个fetch时empcur%found才会true，所以需要改造下该语句*/

declare
cursor empcur is select * from emp;
emprow emp%rowtype;  /*该变量与表emp中的所有列属性相同，可以保存多行数据*/
begin
 open empcur;
 fetch empcur into emprow; --判断条件执行时事先让游标取值一次
 while  empcur%found loop   /*当empcur%notfound条件满足即为true时开始循环*/
 fetch empcur into emprow;
 dbms_output.put_line('员工编号:' ||emprow.empno || '员工姓名:' ||emprow.ename);
--这里使用 “变量名.列名”的形式
 dbms_output.put_line('游标影响的行数为：' ||empcur%rowcount);
--这里格式为：游标名游标属性名
end loop;
close empcur;
 end;

--如果进入死循环则将代码修改为：

declare
cursor empcur is select * from emp;
emprow emp%rowtype;  --该变量与表emp中的所有列属性相同，可以保存多行数据
begin
 open empcur;
 fetch empcur into emprow; --判断条件执行时事先让游标取值一次
 while  empcur%found loop   --当empcur%notfound条件满足即为true时开始循环
 fetch empcur into emprow;
 dbms_output.put_line('员工编号:' ||emprow.empno || '员工姓名:' ||emprow.ename);
--这里使用 “变量名.列名”的形式
 dbms_output.put_line('游标影响的行数为：' ||empcur%rowcount);
--这里格式为：游标名游标属性名
fetch empcur into emprow;  --让游标再次提取值，否则empcur%found永远为真则可能进入死循环。
end loop;
close empcur;
 end;

 

 

--示例05：使用显示游标更新
declare
new_sal  number;
cursor k01 is
select sal from emp where sal <6000 for update of sal;
begin
open k01;
loop
fetch k01 into new_sal;
exit when k01%notfound;
update emp set sal=1.1*new_sal where current of k01;
end loop;
close k01;
commit;
end;

 

--示例06：使用标量变量接受游标数据
declare
cursor emp_cursor is
select ename, job,sal from emp where deptno=&dno;
v_ename emp.ename%type;
v_sal emp.sal%type;
v_job emp.job%type;
begin
open emp_cursor;
loop
fetch emp_cursor into v_ename, v_job, v_sal;
exit when emp_cursor%notfound;
dbms_output.put_line('姓名:'||v_ename||',岗位:'||v_job||',工资:'||v_sal);
end loop;
close emp_cursor;
end;

 

--案例07：使用PL/SQL记录变量接受显示游标数据

declare
cursor emp_cursor is
select ename,sal from emp order by sal desc;
emp_record emp_cursor%rowtype;  /* 使用%rowtype属性定义记录变量，因为游标中有多列值取出*/
begin
open emp_cursor;
loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound or emp_cursor%rowcount>&n;
dbms_output.put_line('姓名 :'||emp_record.ename||', 工资 :' ||emp_record.sal);  --“变量名.列名”格式
end loop;
close emp_cursor;
end;

--案例08：使用PL/SQL集合变量即表接受显示游标数据

declare
cursor emp_cursor is select ename, sal from emp
where lower(job)=lower('&job');
type emp_table_type is table of emp_cursor%rowtype
index by binary_integer;
emp_table emp_table_type;
i int;
begin
open emp_cursor;
loop
i:=emp_cursor%rowcount+1;
fetch emp_cursor into emp_table(i);
exit when emp_cursor%notfound;
dbms_output.put_line(' 姓名:'||emp_table(i).ename||', 工资:'||emp_table(i).sal);
end loop;
close emp_cursor;
end;

 

 
--案例09：游标for循环01
declare
 cursor empcur is select * from emp ;
begin
 for emprow in empcur loop
--将游标empcur中的值赋给变量emprow,变量emprow系统会自动定义
 dbms_output.put_line('员工工号:'||emprow.empno||'员工姓名 :'||emprow.ename);
 dbms_output.put_line('提取的行数:'||empcur%rowcount);
end loop;
end;

 

 

 

--案例10：游标for循环02

/*不需要处理游标使用过程中的打开、取值和结果，会自动完成*/

DECLARE
   CURSOR mytoy_cur IS
     SELECT toyid, toyname, toyprice
     FROM toys;
BEGIN
 FOR toy_rec IN mytoy_cur LOOP
    DBMS_OUTPUT.PUT_LINE ('玩具编号：'||toy_rec.toyid||' '
    ||'玩具名称：' ||toy_rec.toyname||' '
    ||'玩具单价：'  ||toy_rec.toyprice);
  END LOOP;
END;

 

 

 

--案例11：游标for循环(满足条件退出循环)
declare
cursor emp_cursor is select ename, hiredate from emp
order by hiredate desc;
begin
for emp_record in emp_cursor loop
dbms_output.put_line(': '||emp_record.ename ||',   :'||emp_record.hiredate );
exit when emp_cursor%rowcount=&n;
end loop;
end;

 

 

--案例12：游标for中使用子查询

begin
for emp_record in (select ename, hiredate, rownum from emp order by hiredate) loop
dbms_output.put_line(': '||emp_record.ename ||',   :'||emp_record.hiredate );
exit when emp_record.rownum=&n;
end loop;
end;

 

 

--示例13：带参数的显示游标
SET SERVEROUTPUT ON
DECLARE
  dept_code emp.deptno%TYPE; --变量用来保存数据
  emp_code  emp.empno%TYPE;
  emp_name  emp.ename%TYPE;
  CURSOR emp_cur(deptparam NUMBER) IS  --使用游标参数
    SELECT empno, ename FROM emp
    WHERE deptno=deptparam;
BEGIN
  dept_code := &部门编号;
  OPEN emp_cur(dept_code);
  LOOP
    FETCH emp_cur INTO emp_code, emp_name;
    EXIT WHEN emp_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(emp_code||' '||emp_name);
  END LOOP;
  CLOSE emp_cur;
END;

--案例14：使用参数游标

declare
cursor emp_cursor (dno number) is
select ename, job from emp where deptno=dno;
begin
for emp_record in emp_cursor(&dno)  loop
dbms_output.put_line('姓名:'||emp_record.ename||',岗位:'||emp_record.job);
end loop;
end;

 

 

 

 

 

-------------------------------------------------------------------------------------------------第二部分：游标变量-------------------------------

--示例1：REF游标(同表查询)
declare
    type reffempcur is ref cursor;
    empcur reffempcur; --申明REF游标变量
    flag int:=0;
   emprow emp%rowtype;
begin
      flag:=&flag;
     if flag=0 then 
             open empcur for select * from emp
              where sal>500 and sal <1000;
    elsif    flag=1  then
       open empcur for select * from emp
            where sal>=1000;
    else
         open empcur for select * from emp ;
    end if;
    loop
        fetch empcur into emprow;
        dbms_output.put_line(' 员工号：' ||emprow.empno);
        exit when empcur%notfound;
   end loop;
end;

-示例2：REF游标(不同表查询)
--第一步：新建表
SQL> desc test;
Name Type    Nullable Default Comments
---- ------- -------- ------- --------
SID  INTEGER                          
SNO  INTEGER                          

SQL> desc apple;
Name  Type         Nullable Default Comments
----- ------------ -------- ------- --------
TNO   INTEGER      Y                        
TNAME VARCHAR2(10) Y                        

 

--第二步：新建游标

declare
    type reffempcur is ref cursor;
    empcur reffempcur; --申明REF游标变量
    flag int:=0;
    testrow test%rowtype;
    applerow apple%rowtype;
begin
      flag:=&flag;
     if flag=1 then
       open empcur for select * from test;
         loop
        fetch empcur into testrow;
        exit when empcur%notfound;
        dbms_output.put_line(' 学号：' ||testrow.sid);
        end loop;
    else 
         open empcur for select * from apple;
          loop
         fetch empcur into applerow;
        exit when empcur%notfound;
        dbms_output.put_line(' 老师工号：' ||applerow.tno);
         end loop;
    end if;
end;

--案例3：
set serverouput on
accept tab prompt '你想查看什么信息？员工信息(E)或部门信息(D):';
declare
  type refcur_t is ref cursor;  --这里需要申明游标类型
  refcur refcur_t;
  p_id number;
  p_name varchar2(100);
  selection varchar2(1) := upper(substr('&tab ',1,1));
begin
  if selection = 'E' THEN
    open refcur for
      select empno id, ename name
      from emp;
    dbms_output.put_line('===员工信息===');
  elsif selection = 'D' THEN
    open refcur for
      select deptno id, dname name
      from dept;
    dbms_output.put_line('===部门信息===');
  else
  dbms_output.put_line('请输入员工信息(E) 或 部门信息(D)');
    return;
  end if;
  fetch refcur into p_id, p_name;
  while refcur%found loop
    dbms_output.put_line('#' || p_id || ':' || p_name);
    fetch refcur into p_id, p_name;
  end loop ;
  close refcur;
end;