Oracle临时表与游标

1.临时表

1.1 会话级临时表

会话级临时表是指临时表中的数据只在会话生命周期之中存在，当用户退出会话结束的时候，Oracle自动清除临时表中数据。

Create Global Temporary Table Table_Name
(
Col1 Type1,
Col2 Type2
...
)
On Commit Preserve Rows;

1.2 事务级临时表

事务级临时表是指临时表中的数据只在事务生命周期中存在。
当一个事务结束（commit or rollback），Oracle自动清除临时表中数据。

Create Global Temporary Table Table_Name
(
Col1 Type1,
Col2 Type2
...
)
On Commit delete Rows;

2.游标

2.1游标的概念

游标位于内存中的 “临时表”。
具体如下：游标是从数据表中提取出来的数据，以临时表的形式存放到内存中，在游标中有一个 数据指针， 在初始状态下指向的是首记录，利用 fetch 语句可以移动该指针，从而对游标中的数据进行各种操作，然后将操作结果写回到数据库中。
游标用来查询数据库，获取记录集合（结果集）的指针，可以让开发者 一次访问一行结果集， 在每条结果集上作操作。

2.2 游标概述图

2.3 游标分类

2.3.1 数据准备

create table mz_dept(
dept_code varchar2(10),
dept_name  varchar2(50)
)
insert all
       into mz_dept(dept_code,dept_name) values('0001','皮肤科')
       into mz_dept(dept_code,dept_name) values('0002','肿瘤科')
       select 1 from dual;
       commit;

2.3.2 静态游标

2.3.2.1 隐式游标

在 PL/SQL 中使用DML (insert,update,delete,merge)和 select into时，会自动创建隐式游标，隐式游标自动声明、打开和关闭（无法手动查看），其名为 SQL，通过检查隐式游标的属性可以获得 最近执行的 DML 和 select into 语句的信息,这个游标有3个常用的属性。

属性	返回值类型	说明
SQL%FOUND	布尔型	前一个 fetch 语句是否有值，true：有，false：没有
SQL%NOTFOUND	布尔型	与上述相反，常被用于退出循环，true：有，false：没有, null : 空。注意哦，只有为 true 时，才退出（当第一此 fetch 为 null 时，不会退出！）
SQL%ROWCOUNT	整型	当前成功执行的数据行数（非总记录数）
SQL%ISOPEN	布尔型	游标是否开启， true：开启，false：关闭

declare
   v_count number;
begin
   insert into mz_dept(dept_code, dept_name) values('0004', '口腔科');
   if sql%found then
      dbms_output.put_line('插入数据成功!');
   end if;
   
   update mz_dept set dept_name = '精神病科' where dept_code = '0004';
   if sql%rowcount > 0 then
      dbms_output.put_line('更新数据成功!');
   end if;
   
   delete from mz_dept where dept_code =  '0005';
   if sql%notfound then
      dbms_output.put_line('删除数据失败!');
   else
      dbms_output.put_line('删除数据成功!');
   end if;
   
   if sql%isopen then
      dbms_output.put_line('隐式游标打开，可手动查看');
   else 
      dbms_output.put_line('隐式游标打开，无法手动查看');
   end if;
    
exception
   when others then
      dbms_output.put_line(sqlcode || ' : ' || sqlerrm);
      dbms_output.put_line(dbms_utility.format_error_backtrace);
end;

2.3.2.2 显示游标

   cursor cur_stu(参数值 参数类型) is select * from stu t [where t.id = 参数值]; 
   eg：
    --有参数
    cursor dept_cusor(v_dept_code varchar2 := '0001') is select * from mz_dept where dept_code = v_dept_code ;
    --没有参数
     cursor dept_cusor is select * from mz_dept where t.dept_code = '0001' ;

用例：行级数据循环提取

--总共 4 个步骤，缺一不可：（参数可选）
--1.生命游标
--2.打开游标
--3.提取数据
--4.关闭游标
declare
   cursor cur_mz_dept(cur_dept_code varchar2) is select * from mz_dept where dept_code = cur_dept_code; -- 步骤1: 声明游标
   v_mz_dept  cur_mz_dept%rowtype; --游标变量，用来接收游标数据
begin
-- 步骤2: 打开游标
   if not cur_mz_dept%isopen then
      open cur_mz_dept('0001');--获取dept_code=0001的数据列表
   end if;
 
 -- 步骤3: 提取数据
   loop 
     fetch cur_mz_dept into v_mz_dept;
     exit when cur_mz_dept%notfound;
       dbms_output.put_line(v_mz_dept.dept_code ||' : '||v_mz_dept.dept_name);
   end loop;
   
-- 步骤4: 关闭游标
   if cur_mz_dept%isopen then
      close cur_mz_dept; 
   end if;

exception
   when others then
      dbms_output.put_line(sqlcode || ' : ' || sqlerrm);
      dbms_output.put_line(dbms_utility.format_error_backtrace);
end;

用例：表级数据一次性提取

--我们也可以一次性取出游标的所有数据，放入到table类型变量中：
declare
   -- 步骤1: 声明游标
   cursor cur_mz_dept is select * from mz_dept; 
   --声明table类型
   type ts_mz_dept is table of cur_mz_dept%rowtype;
   --声明table类型变量
   v_mz_dept ts_mz_dept;
   --v_mz_dept_rs  cur_mz_dept%rowtype; --游标变量，用来接收游标数据

begin
-- 步骤2: 打开游标
   if not cur_mz_dept%isopen then
      open cur_mz_dept;
   end if;
 
 -- 步骤3: 一次性取出游标中的所有数据 ，放入table类型变量中
   fetch cur_mz_dept bulk collect into v_mz_dept limit 500; 
   --limit 限制一次性提取数据的数量，避免数据量太大，内存卡爆，这里因为集合数据小于500，故此未做循环。

-- 步骤4: 关闭游标
   if cur_mz_dept%isopen then
      close cur_mz_dept; 
   end if;
   
   --循环输出table类型变量中的内容
  for i in v_mz_dept.first .. v_mz_dept.last
  loop
      dbms_output.put_line('编号:'||v_mz_dept(i).dept_code||',名称:'||v_mz_dept(i).dept_name);
  end loop;

exception
   when others then
      dbms_output.put_line(sqlcode || ' : ' || sqlerrm);
      dbms_output.put_line(dbms_utility.format_error_backtrace);
end;

游标for循环（隐式游标）
每次使用游标都需要打开和关闭游标，比较麻烦，我们可以使用游标for循环来简化游标的使用方式,此方法对于静态游标最好用，可以少写一些代码，推荐使用此种方法

declare
  v_num number := 10;
begin

  for thisrow in (select * from mz_dept where rownum < v_num) loop
      dbms_output.put_line('编号:'||thisrow.dept_code||',名称:'||thisrow.dept_name);
  end loop;

exception
   when others then
      dbms_output.put_line(sqlcode || ' : ' || sqlerrm);
      dbms_output.put_line(dbms_utility.format_error_backtrace);
end;

2.3.3 动态游标

动态游标分为强类型和弱类型。

2.3.3.1 强类型动态游标

强类型动态游标的语法：

1.for 后是 SQL语句（不能是字符串）,也就是说不能跟预编译的sql语句
2.cur… 必须和 return 的 类型完全一致
3.无法使用绑定变量，也就是说不能动态查询
PS：强类型动态游标限制多，不方便。

type cur_my is ref cursor return mz_dept%rowtype;
v_cur cur_my;

用例：

declare
   --v_sql varchar2(8000);
   --v_d1 varchar2(10) := '0001';   
   --v_dept_code mz_dept.dept_code%type;
   --v_dept_name mz_dept.dept_name%type;
   type cur_mz_dept is ref cursor return mz_dept%rowtype;
   v_cur_mz_dept cur_mz_dept;
   v_mz_dept mz_dept%rowtype;
begin
   --v_sql := 'select dept_code,dept_name from mz_dept where dept_code = : v_d1';
   open v_cur_mz_dept for select * from mz_dept; --using v_d1; 
   loop
      fetch v_cur_mz_dept
         into v_mz_dept;
      exit when v_cur_mz_dept%notfound;
      dbms_output.put_line('科室编号：' || v_mz_dept.dept_code || ' ' || '科室名称：' || v_mz_dept.dept_name);
   end loop;
   
   close v_cur_mz_dept;
exception
   when others then
      dbms_output.put_line(sqlcode || ' : ' || sqlerrm);
      dbms_output.put_line(dbms_utility.format_error_backtrace);
end;

2.3.3.2 弱类型动态游标

declare
   v_sql varchar2(8000);
   v_d1 varchar2(10) := '0001';   
   v_dept_code mz_dept.dept_code%type;
   v_dept_name mz_dept.dept_name%type;
   -- type cur_stu_type is ref cursor;
   -- cur_stu cur_stu_type;
   cur_mz_dept sys_refcursor;--与上面两行代码等效
begin
   v_sql := 'select dept_code,dept_name from mz_dept where dept_code = : v_d1';
   open cur_mz_dept for v_sql using v_d1; -- 绑定变量 : 大数据处理常用优化手段
   loop
      fetch cur_mz_dept
         into v_dept_code, v_dept_name;
      exit when cur_mz_dept%notfound;
      dbms_output.put_line('科室编号：' || v_dept_code || chr(13) || '科室名称：' || v_dept_name);
   end loop;
   
   close cur_mz_dept;
exception
   when others then
      dbms_output.put_line(sqlcode || ' : ' || sqlerrm);
      dbms_output.put_line(dbms_utility.format_error_backtrace);
end;

3.总结

PS:一般来说批量处理的速度要最好,隐式游标的次之,单条处理的最差，当然批处理的时候，若数据量很大，注意循环批处理，一次性别取太多的数据，防止电脑耗掉过多内存。

1、批量处理
open 游标；
loop
   fetch 游标 bulk collect into 集合变量（也就是 table 类型哦） limit 数值; -- 一般 500 左右
   exit when 条件 --（变量.count = 0，如果用 sql%notfound 不足 limit 的记录就不会被执行哦）
close 游标；

2、隐式游标
for x in (sql 语句) loop
... 逻辑处理
end loop;

3、单条处理
open  游标;
loop
   fetch 游标 into 变量；
   exit when 条件
end loop;
close 游标；