学习笔记_oracle——游标与动态SQL

游标
 查询结果及记录不足，很难用变量表示
 游标可以按行访问结果集
 基本原理
  DML的结果放在缓冲区
  游标是指向这个缓冲区的指针
 分类
  静态游标
  定义是在编译时绑定了一个select语句
   隐式游标 oracle自动生成的游标
   显示游标 用户手动申明和操作
  动态游标
  在运行时游标绑定查询语句
   强类型 只支持与之类型匹配的查询语句
   弱类型 只支持任何查询语句

 显式游标
  使用步骤
  1、声明游标
   在声明(DECLAR)中
   CURSOR 游标名 IS <SELECT语句> ;
  2、打开游标
   OPEN <游标名>;
  3、提取游标
   FETCH <游标名> INTO <变量列表>
   --变量类型、顺序、数量相同，每次调用移动一次
  4、关闭游标
   CLOSE <游标名>
  游标的属性(用游标名%属性名调用)
   %ISOPEN  判断是否打开
   %FOUND  是否有指向效行
   %NOTFOUND 是否有指向效行
   %ROWCONUT 游标提取过的行数
  使用循环游标简化游标的读取
   FOR <类型> IN <游标名> LOOP
    --数据库操作
   END LOOP
   其中<类型>是用户的自定义类型，
   系统自动生成，与查询结果中一致
 
 隐式游标
  在PL/SQL中使用SELECT操作即是隐式游标
  特点 1、不用声明
   2、不用打开和关闭
   3、必须使用INTO字句，结果只能一条
  属性的使用，在数属性前加上SQL
   其中 %ROWCONUT、%FOUND常用来判断是否为空
    在commit之前使用
   例如 update .....
    IF SQL%ROWCOUNT = 1 THEN
     --表已更新
    ELSE
     --表未更新
    END IF;

 动态游标
  REF动态游标，用于处理多行的查询结果集，在
  打开游标是才确定类型
  特点： 1、动态游标可以与不同的语句关联
   2、打开游标是绑定、是REF类型
   3、常用在存储过程中
  使用过程
   定义游标
    TYPE <类型名> IS REF COUSER
    RETURN <返回类型名>
   声明游标
    <游标名> <类型名>
   打开动态游标
    OPEN <游标名> FOR <查询语句>
  //=================实例=================
  DECLARE
   TYPE refcur IS REF COUSOR
   RETURN employees%ROWTYPE
   v_emp employees%ROWTYPE
  BEGIN
   OPEN refcur FOR
   --SQL语句
   LOOP
    FETCH refcur INTO v_emp;
    EXIT WHEN refcur%NOTFOUND;
    --执行语句
   END LOOP
   CLOSE refcur
  END

  强类型和弱类型的区别
   强类型：带有RETURN语句
    在OPEN时必须是指定的类型的select语句
   弱类型：不带有RETURN语句
    在OPEN时可以是任意的select语句

动态SQL
 静态SQL
  1、编译时确定，一般只能使用DML和事务控制语句
  2、DDL，回话控制语句不能直接使用
 动态SQL
  1、不编译，执行时动态确定
  2、根据用户输入参数等确定SQL
  3、解决PL/SQL中不支持DDL的问题
 语法
  EXECUTE IMMEDIATE '语句'

  EXECUTE IMMEDIATE '语句'
  [INTO <变量序列>] --用于接收SQL的查询结果
  [USING <参数序列>] --用于动态接收参数
  //============实例================
  EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)'
 
  DECLARE
   sql_stmt VARCHAR2(200)
   emp_id NUMBER(10) := XX;
   emp_rec enployees%ROWTYPE;
  BEGIN
   sql_stmt := 'select * from employees where id = :id'
   EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
  END;
  [注]只能执行返回结果为一行或者没有的语句
  当返回多行时需要使用REF游标
  OPEN <游标> FOR <SQL语句>
  [USING <参数列表>]
SQL行列转换
 静态语句
  SELECT t_name 姓名，
   sum(CASE t_sourse WHEN '语文' THEN t_score ELSE 0 END) 语文,
   sum(CASE t_sourse WHEN '数学' THEN t_score ELSE 0 END) 数学,
   sum(CASE t_sourse WHEN '物理' THEN t_score ELSE 0 END) 物理,
  FORM td
   GROUP BY t_name;

 动态语句
  DECLARE
   t_name VARCHAR(10);
   t_coures1 NUMBER;
   t_coures2 NUMBER;
   t_coures3 NUMBER;   
   TYPR c_type IS REF CURSOR;
   cur c_type;
  BEGIN
   OPEN cur FOR
   'sum (case t_course when ''' || ' 语文' || ''' then t_score else 0 end ) ,
    sum (case t_course when ''' || ' 数学' || ''' then t_score else 0 end ) ,
    sum (case t_course when ''' || ' 物理' || ''' then t_score else 0 end )
    FROM td GROUP BY t_name'
  LOOP
   FETCH cur INTO t_name,t_course1,t_course2,t_course3
   EXIT WHEN cur%NOTFOUND;
  END LOOP;
  CLOSE cur;
  END;