oracle游标

 

--第三节课内容
        Select 列名列表
        Into 变量名列表|记录类型的变量
        From ...
       --记录类型变量分量的数量和数据类型要和select子句中列的数量、数据类型一一对应
        Select ename,sal
        Into emp_record--相当与emp_record.ename,emp_record.sal
        From emp
        Where empno=7788;
       
练习：使用记录类型变量，输出20部门工资最低点的员工的姓名和工资
----------------------------------------------------------------------------------------------
Declare
 Type Emp_Record_Type Is Record(
  Ename Emp.Ename%Type,
  Sal   Emp.Sal%Type);
 Emp_Record Emp_Record_Type;
Begin
 Select e.Ename, e.Sal
 Into Emp_Record
 From Emp e, (Select Min(Sal) Minsal, Deptno From Emp Group By Deptno) a
 Where a.Deptno = 20 And e.Deptno = a.Deptno And e.Sal = a.Minsal;

 Dbms_Output.Put_Line(Emp_Record.Ename);
 Dbms_Output.Put_Line(Emp_Record.Sal);
End;
/
--SQL> /
--SMITH
--1000
--PL/SQL procedure successfully completed
---------------------------------------------------------------------------------------------

   4.简便的声明记录类型变量的方法(%Rowtype)
     记录类型变量名 前缀%Rowtype;
     --前缀可以是表名、视图名或者游标名
     --以这种方式声明的记录类型变量，其分量的定义和表或视图中列的定义一致。
     --不用事先声明类型
练习：使用记录类型变量，输出20部门工资最低点的员工的姓名和工资
----------------------------------------------------------------------------------------------
Declare
 Emp_Record Emp%Rowtype;
Begin
 Select e.*
 Into Emp_Record
 From Emp e, (Select Min(Sal) Minsal, Deptno From Emp Group By Deptno) a
 Where a.Deptno = 20 And e.Deptno = a.Deptno And e.Sal = a.Minsal;

 Dbms_Output.Put_Line(Emp_Record.Ename);
 Dbms_Output.Put_Line(Emp_Record.Sal);
 
 
  --扩展知识
  --可以使用在insert 语句的values子句中
  Insert Into emp Values emp_record;
 
  --也可以使用在update语句的set子句中
  Update emp Set Row=emp_record Where empno=9999;--把所有的字段都更新成emp表中对应的值，Row为Oracle9i以后提供的
 
End;
/
--SQL> /
--SMITH
--1000
--PL/SQL procedure successfully completed
---------------------------------------------------------------------------------------------

三、PL/SQL表(数组,散列表(Map))
    --PL/SQL表没有长度的限制
    --PL/SQL表能够动态增长
    --PL/SQL表由键值和元素两部分构成，通常用整数做键值；
    --键值没有固定的起始，也不要求连续，而且还可以是负数
    1.声明表类型
      Type 表类型名 Is Table Of 元素的数据类型 Index By 键值的数据类型
      --通常
      Type 表类型名 Is Table Of 元素的数据类型 Index By Binary_Integer;
     
      例如：
      Type ename_table_type Is Table Of Varchar2(30) Index By Binary_Integer;
    
    2.声明表变量
      变量名 表类型名;
     
      例如：
        ename_table ename_table_type;
       
    3.如何引用表变量
      引用表中的元素
          表变量名(Index);--引用表中键值为index的元素
                          --保证键值对应的元素真正的存在，否则会抛出异常
                         
      例如：
          ename_table(1);
          ename_table(10);--没有找到对应的元素，会抛出异常
          ename_table(11):='KING';
         
练习：创建PL/SQL表
-------------------------------------------------------------------------------
Declare
 Type Ename_Table_Type Is Table Of Varchar2(10) Index By Binary_Integer;
 Ename_Table Ename_Table_Type;
Begin
 Ename_Table(1) := 'SMITH';
 Ename_Table(99) := 'SCOTT';
 Ename_Table(-100) := 'FORD';
 
  --遍历表
  --判断表中是否有数据
  --ename_table.First第一个元素(键值最小的)
  --ename_table.Last最后一个元素(键值最大的)
  --判断元素是否存在
  If ename_table.Count>0 Then
     For i In ename_table.First..ename_table.Last Loop
         If ename_table.Exists(i) Then
            Dbms_Output.Put_Line(ename_table(i));
         End If;
     End Loop;
  End If;
End;
/
--SQL>
--FORD
--SMITH
--SCOTT
--PL/SQL procedure successfully completed
-------------------------------------------------------------------------------

第四章 游标
一、游标的概念
    Cursor:实际上就是内存中的一块区域，这块内存区存储是SQL语句执行后的结果集，游标相当于一个指针，
           指向这个区域中的第一条记录。
           游标主要是解决select语句返回结果集是0条或多条的情况。
          
二、游标的分类
   1.隐士游标：游标的声明、打开以及管理都是由Oracle自动完成的
     隐士游标的属性
       %Rowcount:返回最近一条sql语句执行以后所影响的记录条数
       %Found   :游标指向的记录是否存在，存在返回 True ,否则返回 False
       %Notfound:与%Found 相反
       %Isopen  :返回游标是否打开,打开为 True ,否则为 False
     对于隐士游标，属性的前缀是 Sql
    
练习：根据用户输入的部门编号，删除该部门的所有员工并输出删除的条数
Create Table emp_1 As Select * From emp;
-----------------------------------------------------------------------------------
Begin
     Delete From emp_1 Where deptno=&p_deptno;
     dbms_output.put_line('删除了' || Sql%Rowcount || '条记录');
End;
/
--SQL>
--删除了5条记录
--PL/SQL procedure successfully completed
-----------------------------------------------------------------------------------

   2.显示游标:游标的声明、打开、关闭以及管理都是由用户来完成的游标。
     游标主要是解决select语句返回结果集是0条或多条的情况。
     a.如何声明游标
       Cursor 游标名 Is 查询语句;
       --声明游标的select语句中一定不能出现into语句
     b.游标的操作方法
       i.打开游标
         Open 游标名;
         --在使用游标前一定要先打开
         --实际上就是执行了游标声明中的select语句;
         --结果集被放到内存的区域中；
         --游标指向了该区域中的第一条记录
        
        ii.提取数据
           Fetch 游标名 Into 变量列表|记录类型变量;
           --从内存的结果集中提取游标指向的当前数据
           --数据提取以后，游标下移;
           --数据提取以后，该数据从结果集中移除了；
           --游标只能下移、不能上移
        
        iii.关闭游标
           Close 游标名;
           --释放了内存区域
          
      c.游标操作的标准流程(看课件)
      d.显示游标的属性
         %Rowcount:迄今为止，已经从由表中提取的记录条数；
         %Found   :用fetch语句提取的数据是否为空；
         %Notfound:与%Found 相反；
         %Isopen  :游标是否打开
        属性的前缀是游标的名字。
       
练习：使用游标列出emp表中平均工资在1000以上的部门的编号，部门名称，平均工资以及人数
-------------------------------------------------------------------------------------------
Declare
 Cursor emp_cursor Is
  --Select Distinct d.Deptno, d.Dname, a.Avgsal, b.Countnum
  --From Emp e, Dept d, (Select Avg(Sal) Avgsal, Deptno From Emp Group By Deptno) a,
  --   (Select Count(*) Countnum, Deptno From Emp Group By Deptno) b
  --Where a.Avgsal > 1000 And e.Deptno = a.Deptno And d.Deptno = a.Deptno And b.Deptno = a.Deptno;
   
    Select d.deptno,d.dname,Avg(e.sal),Count(e.empno)
    From emp e,dept d
    Where e.deptno=d.deptno
    Group By d.deptno,d.dname
    Having Avg(sal)>1000;
   
    v_deptno dept.deptno%Type;
    v_dname dept.dname%Type;
    v_avgsal Number(7,2);
    v_count Number;
   
Begin
  Open emp_cursor;
       Loop
           Fetch emp_cursor Into v_deptno,v_dname,v_avgsal,v_count;
           Exit When emp_cursor%Notfound;
           Dbms_Output.Put_Line(v_deptno || ' ' || v_dname || ' ' || v_avgsal || ' ' || v_count);
       End Loop;
  Close emp_cursor; 
End;
/
--SQL>
--10 ACCOUNTING 1986 5
--20 RESEARCH 1956.5 5
--30 SALES 1566.67 6
--PL/SQL procedure successfully completed

-------------------------------------------------------------------------------------------