plsql游标的介绍

3. 游标的介绍
    游标:一个指向保存多行SQL查询结果集的工作区的句柄(指针)

3.1 显式游标

(1)显式游标的使用

案例1:定义变量的方式使用游标

declare
  cursor csr_org is select h.hrc_descr,o.org_short_name
                      from org_tab o,hrc_tab h
                     where o.hrc_code=h.hrc_code
                    order by 2;        --定义游标
  v_hrc_descr varchar2(20);             --定义变量
  v_org_short_name varchar2(30);
begin
  open csr_org;        --打开游标,游标指针指向游标第一行
  loop
    --fetch操作每次取游标的一行,取完之后移到游标的下一行
    fetch csr_org into v_hrc_descr,v_org_short_name;   
    exit when(csr_org%notfound);    --循环退出的条件
    dbms_output.put_line(rpad(v_hrc_descr,23,‘ ‘)||‘ ‘||rpad(v_org_short_name,30,‘ ‘));
  end loop;
  close csr_org;
exception when others then
  null;
end;

总结:
  A. 打开游标
  B. 将游标的结果集取到PLSQL记录或者变量,fetch csr_org into v_hrc_descr,v_org_short_name;fetch语句执行完之后,指针指向下一行
  C. 当游标找不到数据的时候退出循环
  D. 关闭游标

在sqlplus中也能执行,但是要注意:
(1) 要打开serveroutput
SQL> conn plsql/plsql
Connected.
SQL> set serveroutput on
(2)在整个程序的最后,要加上一个/,表示运行该程序
SQL> declare
  cursor csr_org is select h.hrc_descr,o.org_short_name
                      from org_tab o,hrc_tab h
                     where o.hrc_code=h.hrc_code
                    order by 2;
  v_hrc_descr varchar2(20);
  v_org_short_name varchar2(30);
begin
  open csr_org;
  loop
    fetch csr_org into v_hrc_descr,v_org_short_name;
    exit when(csr_org%notfound);
    dbms_output.put_line(rpad(v_hrc_descr,23,‘ ‘)||‘ ‘||rpad(v_org_short_name,30,‘ ‘));
  end loop;
  close csr_org;
exception when others then
  null;
 18  end;
 19  /


案例2:定义行变量(记录)的方式使用游标

declare
  cursor csr_org is select h.hrc_descr,o.org_short_name
                      from org_tab o,hrc_tab h
                     where o.hrc_code=h.hrc_code
                    order by 2;
  v_org_rec csr_org%rowtype;    --行变量的声明要来源于表或者有明确结构的游标
begin
  open csr_org;
  loop
    fetch csr_org into v_org_rec;
    exit when(csr_org%notfound);
    dbms_output.put_line(rpad(v_org_rec.hrc_descr,23,‘ ‘)||‘ ‘||rpad(v_org_rec.org_short_name,30,‘ ‘));
  end loop;                                   ||_____行变量有明确的游标结构,游标中有两个字段hrc_descr和org_short_name
  close csr_org;
exception when others then
  null;
end;

案例3:使用while..loop的方式改写案例2

declare
  cursor csr_org is select h.hrc_descr,o.org_short_name
                      from org_tab o,hrc_tab h
                     where o.hrc_code=h.hrc_code
                    order by 2;
  v_org_rec csr_org%rowtype;    --行变量的声明要来源于表或者有明确结构的游标
begin
  open csr_org;
  fetch csr_org into v_org_rec;
  while (csr_org%found) loop        --while循环的条件为真才会执行循环体的语句
    dbms_output.put_line(rpad(v_org_rec.hrc_descr,23,‘ ‘)||‘ ‘||rpad(v_org_rec.org_short_name,30,‘ ‘));
    fetch csr_org into v_org_rec;
  end loop;
  close csr_org;
exception when others then
  null;
end;

案例4:使用for..loop的方式改写案例2

declare
  cursor csr_org is select h.hrc_descr,o.org_short_name
                      from org_tab o,hrc_tab h
                     where o.hrc_code=h.hrc_code
                    order by 2;
begin
  for idx in csr_org loop
    dbms_output.put_line(rpad(idx.hrc_descr,23,‘ ‘)||‘ ‘||rpad(idx.org_short_name,30,‘ ‘));  
  end loop;
exception when others then
  null;
end;

for loop方式需要注意:
A. 不需要显式的打开或者关闭游标,将游标的名字放到loop中会隐式打开这个游标
B. 不需要判断退出或者循环的条件,自动判断
C. 不需要fetch语句让游标下移
D. 引用的时候,需要用游标句柄引用,句柄不需要声明

案例5:不声明游标的方式使用游标

begin
  for idx in (select h.hrc_descr,o.org_short_name
                      from org_tab o,hrc_tab h
                     where o.hrc_code=h.hrc_code
                    order by 2) loop
    dbms_output.put_line(rpad(idx.hrc_descr,23,‘ ‘)||‘ ‘||rpad(idx.org_short_name,30,‘ ‘));  
  end loop;
exception when others then
  null;
end;

练习1:

 在scott用户下,取到所有员工的个人信息(姓名、编号、薪资、雇佣日期、部门名称)输出,使用行变量的方式取值
 通过PLSQL匿名块的方式,使用三种循环方式来实现

declare
   cursor csr_emp is select a.ename,a.empno,a.sal,a.job,a.hiredate,b.dname
                       from emp a,dept b
                      where a.deptno=b.deptno;
begin
  for idx in csr_emp loop
    dbms_output.put_line(rpad(idx.ename,20,‘ ‘)||to_char(idx.empno)||to_char(idx.sal)||rpad(idx.job,15,‘ ‘)||to_char(idx.hiredate)||rpad(idx.dname,15,‘ ‘));
  end loop;
exception when others then
  null;
end;


PLSQL developer工具的使用技巧:

http://www.jb51.net/article/35224.htm

(2)显式游标的属性

属性                类型                      含义
----------------------------------------------------------------------------------------------------------
cursor_name%found            boolean    true:fetch取值成功  false:fetch取值失败,如果游标关闭,这个属性无值
cursor_name%notfound        boolean    true: fetch取值失败  false: fetch取值成功,如果游标关闭,这个属性无值
cursor_name%isopen        boolean    true: 游标已经打开   false: 游标是关闭的
cursor_name%rowcount        number    open游标之后,且fetch游标的第一行之后,close游标之前,游标共处理了多少行

案例6:定义游标之后,判断游标是否是打开还是关闭的

注意规则:
  A. 不能打开一个已经打开的游标
  B. 不能关闭一个已经关闭的游标

declare
  cursor csr_org is select h.hrc_descr,o.org_short_name
                      from org_tab o,hrc_tab h
                     where o.hrc_code=h.hrc_code
                    order by 2;
  v_org_rec csr_org%rowtype;    --行变量的声明要来源于表或者有明确结构的游标
begin
  if not csr_org%isopen then
     open csr_org;
  end if;
  loop
    fetch csr_org into v_org_rec;
    exit when(csr_org%notfound);
    dbms_output.put_line(rpad(v_org_rec.hrc_descr,23,‘ ‘)||‘ ‘||rpad(v_org_rec.org_short_name,30,‘ ‘));
  end loop;
  if csr_org%isopen then
     close csr_org;
  end if;
exception when others then
  null;
end;

isopen:

案例7:取到游标处理的行数

declare
  cursor csr_org is select h.hrc_descr,o.org_short_name
                      from org_tab o,hrc_tab h
                     where o.hrc_code=h.hrc_code
                    order by 2;
  v_org_rec csr_org%rowtype;    --行变量的声明要来源于表或者有明确结构的游标
  rownumber number;
begin
  if not csr_org%isopen then
     open csr_org;
  end if;
  loop
    fetch csr_org into v_org_rec;
    exit when(csr_org%notfound);
    dbms_output.put_line(rpad(v_org_rec.hrc_descr,23,‘ ‘)||‘ ‘||rpad(v_org_rec.org_short_name,30,‘ ‘));
  end loop;
  rownumber:=csr_org%rowcount;
  dbms_output.put_line(to_char(rownumber));
  if csr_org%isopen then
     close csr_org;
  end if;
exception when others then
  null;
end;

将获取的行数放在loop循环中

declare
  cursor csr_org is select h.hrc_descr,o.org_short_name
                      from org_tab o,hrc_tab h
                     where o.hrc_code=h.hrc_code
                    order by 2;
  v_org_rec csr_org%rowtype;    --行变量的声明要来源于表或者有明确结构的游标
  rownumber number;
begin
  if not csr_org%isopen then
     open csr_org;
  end if;
  loop
    fetch csr_org into v_org_rec;
    exit when(csr_org%notfound);
    dbms_output.put_line(rpad(v_org_rec.hrc_descr,23,‘ ‘)||‘ ‘||rpad(v_org_rec.org_short_name,30,‘ ‘));
    rownumber:=csr_org%rowcount;
    dbms_output.put_line(to_char(rownumber));
  end loop;
  if csr_org%isopen then
     close csr_org;
  end if;
exception when others then
  null;
end;

如果是在游标关闭之后,这个属性是获取不到值的

declare
  cursor csr_org is select h.hrc_descr,o.org_short_name
                      from org_tab o,hrc_tab h
                     where o.hrc_code=h.hrc_code
                    order by 2;
  v_org_rec csr_org%rowtype;    --行变量的声明要来源于表或者有明确结构的游标
  rownumber number;
begin
  if not csr_org%isopen then
     open csr_org;
  end if;
  loop
    fetch csr_org into v_org_rec;
    exit when(csr_org%notfound);
    dbms_output.put_line(rpad(v_org_rec.hrc_descr,23,‘ ‘)||‘ ‘||rpad(v_org_rec.org_short_name,30,‘ ‘));
  end loop;
  if csr_org%isopen then
     close csr_org;
  end if;
  dbms_output.put_line(‘--------------------------‘);
  rownumber:=csr_org%rowcount;
  dbms_output.put_line(to_char(rownumber));
exception when others then
  null;
end;

##########################################################################################

3.2 参数化游标

  定义:显式游标带参数,为特定的参数返回特定的结果集

  优势:
     A. 避免定义多个游标
     B. 避免对每一个游标作硬解析

(1) LOOP循环实现参数化游标

declare
  cursor csr_org(p_hrc_code number) is
          select h.hrc_code,o.org_short_name
            from org_tab o,hrc_tab h
           where o.hrc_code=h.hrc_code
             and o.hrc_code=p_hrc_code
         order by 2;    --定义了一个参数化的游标
  v_org_rec csr_org%rowtype;   --定义行变量
begin
  open csr_org(2);
  loop
    fetch csr_org into v_org_rec;
    exit when(csr_org%notfound);
    dbms_output.put_line(rpad(v_org_rec.hrc_code,23,‘ ‘)||‘ ‘||rpad(v_org_rec.org_short_name,30,‘ ‘));
  end loop;   
  close csr_org;
  open csr_org(1);
  loop
    fetch csr_org into v_org_rec;
    exit when(csr_org%notfound);
    dbms_output.put_line(rpad(v_org_rec.hrc_code,23,‘ ‘)||‘ ‘||rpad(v_org_rec.org_short_name,30,‘ ‘));
  end loop;   
  close csr_org;
exception when others then
  null;
end;

(2)FOR..LOOP循环参数化游标
declare
  cursor csr_org(p_hrc_code number) is
          select h.hrc_code,o.org_short_name
            from org_tab o,hrc_tab h
           where o.hrc_code=h.hrc_code
             and o.hrc_code=p_hrc_code
         order by 2;    --定义了一个参数化的游标
  v_org_rec csr_org%rowtype;   --定义行变量
begin
  for ind in csr_org(2) loop
    dbms_output.put_line(rpad(ind.hrc_code,23,‘ ‘)||‘  ‘||rpad(ind.org_short_name,30,‘ ‘));
  end loop;
  for ind in csr_org(1) loop
    dbms_output.put_line(rpad(ind.hrc_code,23,‘ ‘)||‘  ‘||rpad(ind.org_short_name,30,‘ ‘));
  end loop;
exception when others then
  null;
end;

(3)while..loop循环参数化游标

declare
  cursor csr_org(p_hrc_code number) is
          select h.hrc_code,o.org_short_name
            from org_tab o,hrc_tab h
           where o.hrc_code=h.hrc_code
             and o.hrc_code=p_hrc_code
         order by 2;    --定义了一个参数化的游标
  v_org_rec csr_org%rowtype;   --定义行变量
begin
  open csr_org(2);
  fetch csr_org into v_org_rec;
  while (csr_org%found) loop
    dbms_output.put_line(rpad(v_org_rec.hrc_code,23,‘ ‘)||‘ ‘||rpad(v_org_rec.org_short_name,30,‘ ‘));
    fetch csr_org into v_org_rec;
  end loop;
  close csr_org;
  open csr_org(1);
  fetch csr_org into v_org_rec;
  while (csr_org%found) loop
    dbms_output.put_line(rpad(v_org_rec.hrc_code,23,‘ ‘)||‘ ‘||rpad(v_org_rec.org_short_name,30,‘ ‘));
    fetch csr_org into v_org_rec;
  end loop;
  close csr_org;
exception when others then
  null;
end;

练习2:
A. 在scott用户下,取到所有员工的个人信息,包括姓名、编号、薪资、职位、雇佣日期、部门名称、输出
B. 通过PLSQL匿名块,按照三个部门分别输出三个部分,每个部门之间用‘---------------------------------’隔开
C. 要求open在程序中只能出现一次。用嵌套循环,用参数化游标

declare
  cursor csr_emp(p_deptno number) is
           select a.ename,a.empno,a.sal,a.job,a.hiredate,b.dname
             from emp a,dept b
            where a.deptno=b.deptno
              and a.deptno=p_deptno;
  v_csr_emp csr_emp%rowtype;
begin
  for idx in (select deptno from dept where deptno!=40) loop
    for ind in csr_emp(idx.deptno) loop
      dbms_output.put_line(rpad(ind.ename,20,‘ ‘)||to_char(ind.empno)||to_char(ind.sal)||rpad(ind.job,15,‘ ‘)||to_char(ind.hiredate)||rpad(ind.dname,15,‘ ‘));
    end loop;
  dbms_output.put_line(‘------------------------------‘);
  end loop;
exception when others then
  null;
end;


###########################################################################################

3.3 隐式游标

隐式游标:当执行DML语句的时候,PLSQL引擎会自动打开一个指向该工作区域的句柄,执行完毕之后

(1)隐式游标的属性

   属性名                类型                    含义
---------------------------------------------------------------------------------------------------------------
SQL%notfound              boolean        true:fetch值失败;    false:fetch值成功
SQL%found                boolean         true:fetch值成功;     false: fetch值失败
SQL%isopen                boolean        true:游标已经打开    false: 游标已经关闭
SQL%rowcount                number        DML之后,commit之前,游标共处理了多少行
---------------------------------------------------------------------------------------------------------------
隐式游标的属性必须在DML语句之后,commit之前,才能取到值

案例1:

begin
  delete from sec_hrc_org_tab where hrc_code = 1;
  if (sql%found) then
    dbms_output.put_line(to_char(sql%rowcount) ||
                         ‘ rows delete table for sec_hrc_org_tab!‘);
  end if;
  commit;
  insert into sec_hrc_org_tab
    select h.hrc_code,
           h.hrc_descr,
           o.org_id,
           o.org_short_name,
           o.org_long_name
      from org_tab o, hrc_tab h
     where o.hrc_code = h.hrc_code
       and o.hrc_code = 1;
  if (sql%found) then
    dbms_output.put_line(to_char(sql%rowcount) ||
                         ‘ rows insert table fro sec_hrc_org_tab!‘);
  end if;
  commit;
exception
  when others then
    null;
end;

输出:
3 rows delete table for sec_hrc_org_tab!
3 rows insert table fro sec_hrc_org_tab!

案例2:如果将属性的取值放到commit之后,属性将无值

begin
  delete from sec_hrc_org_tab where hrc_code = 1;
  commit;
  if (sql%found) then
    dbms_output.put_line(to_char(sql%rowcount) ||
                         ‘ rows delete table for sec_hrc_org_tab!‘);
  end if;
  insert into sec_hrc_org_tab
    select h.hrc_code,
           h.hrc_descr,
           o.org_id,
           o.org_short_name,
           o.org_long_name
      from org_tab o, hrc_tab h
     where o.hrc_code = h.hrc_code
       and o.hrc_code = 1;
  commit;
  if (sql%found) then
    dbms_output.put_line(to_char(sql%rowcount) ||
                         ‘ rows insert table fro sec_hrc_org_tab!‘);
  end if;
exception
  when others then
    null;
end;

--没有任何输出

(2) 使用隐式游标的属性,通过第三方监控表监控主程序
declare
  v_num_rows number;
  v_hrc_code number:=2;
begin
  delete from sec_hrc_org_tab where hrc_code=v_hrc_code;
  insert into sec_hrc_org_tab select h.hrc_code,h.hrc_descr,o.org_id,o.org_short_name,o.org_long_name
                                from org_tab o,hrc_tab h
                               where o.hrc_code=h.hrc_code
                                 and o.hrc_code=v_hrc_code;
  v_num_rows:=sql%rowcount;
  if(sql%found) then             --注意这种更新监控表的方式
     update sec_hrc_audit set num_rows=num_rows+v_num_rows where hrc_code=v_hrc_code;
     if(sql%notfound) then
       insert into sec_hrc_audit values(v_hrc_code,v_num_rows);
     end if;
  end if;
  commit;
exception when others then
  null;
end;

将程序改为统计每次执行插入的条数:

declare
  v_num_rows number;
  v_hrc_code number:=2;
begin
  delete from sec_hrc_org_tab where hrc_code=v_hrc_code;
  insert into sec_hrc_org_tab select h.hrc_code,h.hrc_descr,o.org_id,o.org_short_name,o.org_long_name
                                from org_tab o,hrc_tab h
                               where o.hrc_code=h.hrc_code
                                 and o.hrc_code=v_hrc_code;
  v_num_rows:=sql%rowcount;
  if(sql%found) then             --注意这种更新监控表的方式
     update sec_hrc_audit set num_rows=v_num_rows where hrc_code=v_hrc_code;
     if(sql%notfound) then
       insert into sec_hrc_audit values(v_hrc_code,v_num_rows);
     end if;
  end if;
  commit;
exception when others then
  null;
end;

练习3:
 取出一个结果集,包含scott用户下emp员工的编号、薪资、雇佣日期、部门名称
 将这个结果集保存在一个表emp_t(4个字段)中,按照部门为单位顺序处理,然后通过第三方表(emp_audit)监控,统计每个部门有多少员工,每个部门的总的薪水。
  emp_audit(deptno number,emp_count number,emp_salsum number);
  select sum(sal) into v_salsum from emp where deptno=10;
 
  A. 创建emp_t表
   SQL> create table emp_t(empno number,sal number,hiredate date,dname varchar2(10));

  B. 创建emp_audit表
   SQL> create table emp_audit(deptno number,emp_count number,emp_salsum number);

  C. 编写匿名块
 
declare
  v_num number;
  v_sal number;
begin
  for idx in (select deptno from dept) loop
    insert into emp_t select e.empno,e.sal,e.hiredate,d.dname
                        from emp e,dept d
                       where e.deptno=d.deptno
                         and e.deptno=idx.deptno;
    v_num:=sql%rowcount;
    select sum(sal) into v_sal from emp where deptno=idx.deptno;
    update emp_audit set emp_count=v_num,emp_salsum=nvl(v_sal,0) where deptno=idx.deptno;
    if(sql%notfound) then
      insert into emp_audit values(idx.deptno,v_num,nvl(v_sal,0));
    end if;
    commit;
  end loop;
exception when others then
  null;
end;


###########################################################################################

3.4 游标变量

  定义:PLSQL中一种变量,可以在运行的时候针对不同的查询使用这个变量
  优势:可以在程序之间传递结果集,在运行的时候一个游标变量可以关联多个查询。

(1) 基本使用方法
declare
  type rc is ref cursor;    --定义一个游标类型
  v_rc rc;                  --声明一个游标类型的游标变量、
  hrc_rec hrc_tab%rowtype;  --定义一个表的行变量,其结构与表的结构相同
begin
  open v_rc for select * from hrc_tab;
  loop
    fetch v_rc into hrc_rec;
    exit when(v_rc%notfound);
    dbms_output.put_line(to_char(hrc_rec.hrc_code)||‘ ‘||hrc_rec.hrc_descr);
  end loop;
  close v_rc;
exception when others then
  null;
end;

(2) 游标变量的赋值操作

declare
  type rc is ref cursor;    --定义一个游标类型
  v_rc1 rc;                  --声明一个游标类型的游标变量
  v_rc2 rc;                  --声明另外一个游标类型的游标变量
  hrc_rec hrc_tab%rowtype;  --定义一个表的行变量,其结构与表的结构相同
begin
  open v_rc1 for select * from hrc_tab;
  v_rc2:=v_rc1;
  loop
    fetch v_rc2 into hrc_rec;
    exit when(v_rc2%notfound);
    dbms_output.put_line(to_char(hrc_rec.hrc_code)||‘ ‘||hrc_rec.hrc_descr);
  end loop;
  close v_rc1;             --关闭掉v_rc1也就意味着关闭掉v_rc2,不用手动去关闭
  if (v_rc2%isopen) then
    close v_rc2;
  end if;
exception when others then
  null;
end;

SQL> show parameter open_cursor

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
open_cursors                 integer     300

一个会话能够打开的游标的最大数量,如果打开的游标超过这个设定的值,系统就会报错

确实需要打开,将参数调大
SQL> alter system set open_cursors=700 scope=both;

System altered.

SQL> show parameter open_cursors

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
open_cursors                 integer     700

注意:
 A. 赋值操作一定要在open之后,只有打开游标之后才能够进行赋值
 B. 不能给游标变量赋予NULL
 C. 不能够判断游标变量是否相等。(if v_rc1=v_rc2 then。。。错误的写法)
 D. 判断游标是否为空,只能够用游标的属性来判断(v_rc2%notfound),(if v_rc2 is null then 。。。错误的写法)

(3) 使用同一个游标变量打开多个查询

declare
  type rc is ref cursor;
  v_rc rc;
  hrc_rec hrc_tab%rowtype;
  v_hrc_descr varchar2(20);
  v_org_short_name varchar2(30);
begin
  open v_rc for select * from hrc_tab;                --打开第一个查询
  loop
    fetch v_rc into hrc_rec;
    exit when(v_rc%notfound);
    dbms_output.put_line(to_char(hrc_rec.hrc_code)||‘ ‘||hrc_rec.hrc_descr);
  end loop;
  close v_rc;
  dbms_output.put_line(‘------------------------------------‘);
 
  open v_rc for select h.hrc_descr,o.org_short_name
                  from org_tab o,hrc_tab h
                 where o.hrc_code=h.hrc_code;         --打开第二个查询
  loop
    fetch v_rc into v_hrc_descr,v_org_short_name;
    exit when(v_rc%notfound);
    dbms_output.put_line(v_hrc_descr||‘   ‘||v_org_short_name);
  end loop;
  close v_rc;
exception when others then
  null;
end;

(4)游标变量在底层的操作

A. 游标变量初始化
B. 将结果集赋给游标变量
C. 使用的时候分配内存
D. 在底层将游标打开
E. 将结果集取到PLSQL变量中
F. 关闭掉游标

练习4:
 定义一个游标变量类型,声明游标,分别打开两个查询并输出
 1.员工的姓名、薪资、雇佣日期、部门名
 2.员工的编号、薪水、薪水等级,关联salgrade表

declare
  type rc is ref cursor;
  v_rc rc;
  v_ename varchar2(20);
  v_sal number;
  v_hiredate date;
  v_change varchar2(20);
  v_dname varchar2(10);
  v_empno number;
  v_grade number;
begin
  open v_rc for select e.ename,e.sal,e.hiredate,d.dname
                  from emp e,dept d
                 where e.deptno=d.deptno;
  loop
    fetch v_rc into v_ename,v_sal,v_hiredate,v_dname;
    exit when(v_rc%notfound);
    v_change:=to_char(v_hiredate,‘yyyy-mm-dd‘);
    dbms_output.put_line(rpad(v_ename,23,‘ ‘)||‘  ‘||rpad(to_char(v_sal),15,‘ ‘)||‘ ‘||v_change||‘  ‘||v_dname);
  end loop;
  close v_rc;
  dbms_output.put_line(‘------------------------------------‘);
 
  open v_rc for select a.empno,a.sal,s.grade
                  from emp a,salgrade s
                 where a.sal between s.losal and s.hisal;
  loop
    fetch v_rc into v_empno,v_sal,v_grade;
    exit when(v_rc%notfound);
    dbms_output.put_line(to_char(v_empno)||‘   ‘||rpad(to_char(v_sal),10,‘ ‘)||‘  ‘||to_char(v_grade));
  end loop;
  close v_rc;
exception when others then
  null;
end;


###########################################################################################

3.5 游标表达式

定义:定义游标的时候嵌套游标使用的方式来表达一个结果集

declare
  type rc is ref cursor;
  hrc_rec rc;
  cursor csr_hrc_org is select h.hrc_descr,cursor(
                               select o.org_long_name
                                 from org_tab o
                                where h.hrc_code=o.hrc_code)
                          from hrc_tab h;
  v_hrc_descr varchar2(20);
  v_hrc_long_name varchar2(30);
begin
  open csr_hrc_org;
  loop
    fetch csr_hrc_org into v_hrc_descr,hrc_rec;    --将结果集赋给声明的游标变量
    exit when(csr_hrc_org%notfound);
    loop
      fetch hrc_rec into v_hrc_long_name;
      exit when(hrc_rec%notfound);
      dbms_output.put_line(v_hrc_descr||‘  ‘||v_hrc_long_name);
    end loop;
  end loop;
  close csr_hrc_org;
exception when others then
  null;
end;


练习5:将上面练习4改用为游标表达式的方式来实现

两个查询分别输出:

declare
  type rc is ref cursor;
  v_rc1 rc;
  v_rc2 rc;
  v_ename varchar2(20);
  v_sal number;
  v_hiredate date;
  v_change varchar2(20);
  v_dname varchar2(10);
  v_empno number;
  v_grade number;
begin
  open v_rc1 for select d.dname,cursor(
                       select e.ename,e.sal,e.hiredate
                         from emp e
                        where e.deptno=d.deptno)
                  from dept d;
                
  loop
    fetch v_rc1 into v_dname,v_rc2;
    exit when(v_rc1%notfound);
    loop
      fetch v_rc2 into v_ename,v_sal,v_hiredate;
      exit when(v_rc2%notfound);
      v_change:=to_char(v_hiredate,‘yyyy-mm-dd‘);
      dbms_output.put_line(rpad(v_ename,23,‘ ‘)||‘  ‘||rpad(to_char(v_sal),15,‘ ‘)||‘ ‘||v_change||‘  ‘||v_dname);
    end loop;
  end loop;
  close v_rc1;  
  dbms_output.put_line(‘------------------------------------‘);
  open v_rc1 for select s.grade,cursor(
                       select a.empno,a.sal
                         from emp a
                        where a.sal between s.losal and s.hisal)
                  from salgrade s;
                 
  loop
    fetch v_rc1 into v_grade,v_rc2;
    exit when(v_rc1%notfound);
    loop
      fetch v_rc2 into v_empno,v_sal;
      exit when(v_rc2%notfound);
      dbms_output.put_line(to_char(v_empno)||‘   ‘||rpad(to_char(v_sal),10,‘ ‘)||‘  ‘||to_char(v_grade));
    end loop;
  end loop;
  close v_rc1;
exception when others then
  null;
end;

直接输出:

declare
  type rc is ref cursor;
  v_rc1 rc;
  v_rc2 rc;
  v_ename varchar2(20);
  v_sal number;
  v_hiredate date;
  v_change varchar2(20);
  v_dname varchar2(10);
  v_empno number;
  v_grade number;
begin
  open v_rc1 for select e.ename,e.empno,e.sal,e.hiredate,d.dname,cursor(
                        select s.grade
                          from salgrade s
                         where e.sal between s.losal and s.hisal)
                   from emp e,dept d
                  where e.deptno=d.deptno;
                
  loop
    fetch v_rc1 into v_ename,v_empno,v_sal,v_hiredate,v_dname,v_rc2;
    exit when(v_rc1%notfound);
    v_change:=to_char(v_hiredate,‘yyyy-mm-dd‘);
    dbms_output.put_line(rpad(v_ename,23,‘ ‘)||‘  ‘||rpad(to_char(v_sal),15,‘ ‘)||‘ ‘||v_change||‘  ‘||v_dname);
    loop
      fetch v_rc2 into v_grade;
      exit when(v_rc2%notfound);
      dbms_output.put_line(rpad(to_char(v_empno),22,‘ ‘)||‘   ‘||rpad(to_char(v_sal),15,‘ ‘)||‘  ‘||to_char(v_grade));
    end loop;
    dbms_output.put_line(‘--------------------------‘);
  end loop;
  close v_rc1;  
exception when others then
  null;
end;

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。