postgresql动态游标 plsql游标

Oracle之PL/SQL学习笔记之游标(五)

处理多行记录的事务经常使用游标来实现

1. 游标的概念

        为了处理SQL语句，Oracle必须分配一片叫上下文(Context area)的区域来处理所必须的信息，其中包括要处理的行的数目，一个指向语句被分析以后的表示形式和指针以及查询的活动集(active set)。

        游标是一个指向上下文的句柄(handle)或指针。通过游标，PL/SQL可以控制上下文区和处理语句时上下文区会发生些什么事情。

       对于不同的SQL语句，游标的使用情况不同

SQL语句	游标
非查询语句	隐式的
结果是单行的查询语句	隐式的或显示的
结果是多行的查询语句	显示的

1.1 处理显示游标

 显示游标处理需要四个PL/SQL步骤:

• 
  
• 
  
• 
  
• 
  
• 定义游标: 就是定义一个游标名，以及与其相对应的SELECT语句。
  格式:  cursor cursor_name[(parameter[,parameter]...)] is select_statement;
  游标参数只能为输入参数，其格式为
  parameter_name in datatype[{:=|DEFAULT} expression]
  注意：

            在指定数据类型时，不能使用长度约束。如NUMBER(4)、CHAR(10)等都是错误的。

            定义游标时，不能有into自居。

• 打开游标： 就是执行游标所对应的SELECT 语句，将其查询结果放入工作区，并且指针指向工作区的首部，表示游标结果集合。如果游标查询语句中带有FOR UPDATE选项，open语句还将锁定数据库表中游标结果集合对应的数据行。
   OPEN cursor_name[([parameter=>]value[,[parameter=>]value]...)]
  在向游标传递参数时，可以使用与函数参数相同的传值方法，即位置表示法和名称表示法。PL/SQL程序不能用OPEN语句重复打开一个游标。
• 提取游标数据： 就是检索结果集中的数据行，放入指定的输出变量中。
  格式： 
           FETCH cursor_name into {variable_list|record_variable};
  对该记录进行处理；
  继续处理，直到活动集合中没有记录；
• 关闭游标： 当提取和处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占用的系统资源，并使该游标的工作区变成无效，不能再使用FETCH语句取其中数据。关闭后的游标可以使用OPEN语句重新打开。
  格式:
      close cursor;
  实例:

declare
  v_sal emp.sal%type;
  v_empno emp.empno%type;
  cursor emp_cursor is select sal,empno from emp where deptno=30;
begin
  open emp_cursor;--打开游标
  fetch emp_cursor into v_sal,v_empno;--提取游标
  while emp_cursor%found loop
     dbms_output.put_line('雇员编号:'||v_empno||',salary:'||v_sal);
     fetch emp_cursor into v_sal,v_empno;--提取游标
  end loop;
  close emp_cursor;--关闭游标
end;
declare
  v_sal emp.sal%type;
  v_empno emp.empno%type;
  cursor emp_cursor is select sal,empno from emp where deptno=30;
begin
  open emp_cursor;--打开游标
  fetch emp_cursor into v_sal,v_empno;--提取游标
  while emp_cursor%found loop
     dbms_output.put_line('雇员编号:'||v_empno||',salary:'||v_sal);
     fetch emp_cursor into v_sal,v_empno;--提取游标
  end loop;
  close emp_cursor;--关闭游标
end;

头脑风暴: 

    假如我们的游标查询很多个字段的值，那么fetch emp_cursor into v_1,v_2,....是不是很麻烦呢？想象我们的复合数据类型。

显示游标的属性

游标属性	值类型	说明
%FOUND	布尔型	当最近一次读取记录时成功返回，则值为true
%NOTFOUND	布尔型	与%FOUND相反
%ISOPEN	布尔型	当游标已打开时返回true
%ROWCOUNT	数字型	返回已从游标中读取的记录数

1.2 处理隐式游标

             隐式游标和显示游标有所差异，它虽然没有显示游标一样的可操作性，但是在实际的工作当中也经常用到。

1.2.1 隐式游标的特点

           每当允许SELECT或DML语句时，PL/SQL会打开一个隐式的游标。隐式游标不受用户的控制，这一点和显示游标有明显的不同。下面列出隐式游标和显示游标的不同处。

•  隐式游标有PL/SQL自动管理；
• 隐式游标的默认名称是SQL;
• SELECT 或DML操作产生隐式游标；
• 隐式游标的属性值始终是最新执行的SQL语句的；
  实例:
  

--隐式游标begin
---------------
--业务说明： 跟新指定员工编号员工的工资+11;如果存在该员工更新数据
-- 如果不存在，打印查无此人。
begin
  update emp set sal=sal+11 where empno=7788;
  if sql%notfound then 
    dbms_output.put_line('查无此人');
  end if;
---------------
--隐式游标end
--隐式游标begin
---------------
--业务说明： 跟新指定员工编号员工的工资+11;如果存在该员工更新数据
-- 如果不存在，打印查无此人。
begin
  update emp set sal=sal+11 where empno=7788;
  if sql%notfound then 
    dbms_output.put_line('查无此人');
  end if;
---------------
--隐式游标end

   隐式游标的属性

属性名	值类型	说明
%ISOPEN	true/false	该属性返回false，由Oracle自己控制
%FOUND	true/false	此属性反应DML操作是否影响到了数据，SELECT INTO语句返回数据为true
%NOTFOUND	true/false	与%FOUND相反
%ROWCOUNT	number	该属性返回的是DML操作影响的行数。

2.  显示游标与循环控制

  案例:把雇员工资低于3000的调整到3000(loop)

-----------
--业务说明: 把雇员工资低于3000的调整到3000
-----------
declare
   v_sal emp.sal%type;--雇员工资，用于判断工资是否大于3000
   v_empno emp.empno%type;--雇员编号，用于更新工资
   cursor emp_cursor is select sal,empno from emp;--定义游标
begin
  --打开游标
  open emp_cursor;
  loop
    --获取数据
    fetch emp_cursor into v_sal,v_empno;
    exit when emp_cursor%notfound;
    if v_sal<3000 then 
      update emp set sal=3000 where empno=v_empno;
      dbms_output.put_line('雇员编号为:'||v_empno||'执行了更新操作');
      commit;
     end if;
  end loop;
  close emp_cursor;--关闭游标
end;
-----------
--业务说明: 把雇员工资低于3000的调整到3000
-----------
declare
   v_sal emp.sal%type;--雇员工资，用于判断工资是否大于3000
   v_empno emp.empno%type;--雇员编号，用于更新工资
   cursor emp_cursor is select sal,empno from emp;--定义游标
begin
  --打开游标
  open emp_cursor;
  loop
    --获取数据
    fetch emp_cursor into v_sal,v_empno;
    exit when emp_cursor%notfound;
    if v_sal<3000 then 
      update emp set sal=3000 where empno=v_empno;
      dbms_output.put_line('雇员编号为:'||v_empno||'执行了更新操作');
      commit;
     end if;
  end loop;
  close emp_cursor;--关闭游标
end;

   我们可以对以上案例进行修改，一般情况下使用cursor时是与for循环配置实用的:(for)

declare
   type emp_sal_empbno_record is record(
        sal emp.sal%type,--雇员工资，这儿需要注意，多个字段，需要用,隔开
        empno emp.empno%type--雇员编号
   );
   cursor emp_cursor is select sal,empno from emp;--定义游标
begin
  --for循环自动打开游标，获取每行的数据，关闭游标
  for emp_sal_empbno_record in emp_cursor loop
    if emp_sal_empbno_record.sal<3000 then 
      update emp set sal=3000 where empno=emp_sal_empbno_record.empno;
      dbms_output.put_line('雇员编号为:'||emp_sal_empbno_record.empno||'执行了更新操作');
      commit;
     end if;
  end loop;
end;
declare
   type emp_sal_empbno_record is record(
        sal emp.sal%type,--雇员工资，这儿需要注意，多个字段，需要用,隔开
        empno emp.empno%type--雇员编号
   );
   cursor emp_cursor is select sal,empno from emp;--定义游标
begin
  --for循环自动打开游标，获取每行的数据，关闭游标
  for emp_sal_empbno_record in emp_cursor loop
    if emp_sal_empbno_record.sal<3000 then 
      update emp set sal=3000 where empno=emp_sal_empbno_record.empno;
      dbms_output.put_line('雇员编号为:'||emp_sal_empbno_record.empno||'执行了更新操作');
      commit;
     end if;
  end loop;
end;

    分析:   从上面的代码中可以看到，for循环中，没有open cursor，fetch cursor，close cursor   但是这些都确切的发生了。

    for循环帮我们在其内部处理了这些操作，简化了用户的操作，一般情况下for循环+cursor 堪称完美。(特殊情况除外)，

   这儿还有一个需要注意，emp_sal_empbno_record 是记录类型，不是记录类型的变量。

   案例: 工资在0-3000 的加薪5%， 3000-4000 加薪3%，4000-5000 加薪2% 5000-n 加薪1%(while)

declare 
  type emp_record is record(
       sal emp.sal%type,
       empno emp.empno%type
  );--定义一个记录类型
  
  v_emp_record emp_record;--定义记录类型的变量
  v_temp number(4,2);--加薪比例
  cursor emp_cursor is select sal,empno from emp;--定义游标
  
begin
  open emp_cursor;--打开游标
  fetch emp_cursor into v_emp_record;--提取数据
  while emp_cursor%found loop
    if v_emp_record.sal<3000 then
      v_temp:=0.05;--需要注意赋值操作使用:=
    elsif v_emp_record.sal<4000 then
      v_temp:=0.03;
    elsif v_emp_record.sal<5000 then 
      v_temp:=0.02;
    else 
      v_temp:=0.01;
    end if; --记住 if语句结束后一定要有end if
    update emp set sal=sal*(1+v_temp) where empno=v_emp_record.empno;
    commit;   
    --提取数据,必须在while循环内部提取数据，不然就是死循环了
    fetch emp_cursor into v_emp_record;
  end loop;
end;
declare 
  type emp_record is record(
       sal emp.sal%type,
       empno emp.empno%type
  );--定义一个记录类型
  
  v_emp_record emp_record;--定义记录类型的变量
  v_temp number(4,2);--加薪比例
  cursor emp_cursor is select sal,empno from emp;--定义游标
  
begin
  open emp_cursor;--打开游标
  fetch emp_cursor into v_emp_record;--提取数据
  while emp_cursor%found loop
    if v_emp_record.sal<3000 then
      v_temp:=0.05;--需要注意赋值操作使用:=
    elsif v_emp_record.sal<4000 then
      v_temp:=0.03;
    elsif v_emp_record.sal<5000 then 
      v_temp:=0.02;
    else 
      v_temp:=0.01;
    end if; --记住 if语句结束后一定要有end if
    update emp set sal=sal*(1+v_temp) where empno=v_emp_record.empno;
    commit;   
    --提取数据,必须在while循环内部提取数据，不然就是死循环了
    fetch emp_cursor into v_emp_record;
  end loop;
end;

    注意： 两个案例，对应三种不同的循环处理游标，大家做下对比。

3 使用cursor for loop(简化迭代结果集)

      对上面的案例进行改进:

declare 
  v_temp number(4,2);--加薪比例
  cursor emp_cursor is select sal,empno from emp;--定义游标
begin
  --从游标中取数据，自动打开，关闭,判读是否还有数据
  for v_emp in emp_cursor
    loop
      if v_emp.sal<3000 then 
        v_temp:=0.005;
      elsif v_emp.sal<4000 then
        v_temp:=0.03;
      elsif v_emp.sal<5000 then
        v_temp:=0.02;
      else 
        v_temp:=0.01;
      end if;
      update emp set sal=sal*(1+v_temp) where empno=v_emp.empno;
      commit;
    end loop;
end;
declare 
  v_temp number(4,2);--加薪比例
  cursor emp_cursor is select sal,empno from emp;--定义游标
begin
  --从游标中取数据，自动打开，关闭,判读是否还有数据
  for v_emp in emp_cursor
    loop
      if v_emp.sal<3000 then 
        v_temp:=0.005;
      elsif v_emp.sal<4000 then
        v_temp:=0.03;
      elsif v_emp.sal<5000 then
        v_temp:=0.02;
      else 
        v_temp:=0.01;
      end if;
      update emp set sal=sal*(1+v_temp) where empno=v_emp.empno;
      commit;
    end loop;
end;

4 使用BULK COLLECT 和FOR 语句的游标

      游标中通常使用fetch.... into ... 语句取数据，这种方式是单条数据提取，在数据量很大的情况下执行效率不是很理想。而FETCH ... BULK COLLECT INTO 语句可以批量提取数据，

在数据量大的情况下它的执行效率比单条提取数据高。

declare 
  cursor emp_cursor is select * from emp;
  type emp_tab is table of emp%rowtype;
  v_emp_tab emp_tab;
begin 
  open emp_cursor;--打开游标
  loop
    fetch emp_cursor bulk collect into v_emp_tab limit 5;--使用limit显示一次取得记录数
    for i in 1 .. v_emp_tab.count loop
        dbms_output.put_line('雇员编号:'||v_emp_tab(i).empno||',雇员姓名:'||v_emp_tab(i).ename);
    end loop;
    exit when emp_cursor%notfound;
  end loop;
end;
declare 
  cursor emp_cursor is select * from emp;
  type emp_tab is table of emp%rowtype;
  v_emp_tab emp_tab;
begin 
  open emp_cursor;--打开游标
  loop
    fetch emp_cursor bulk collect into v_emp_tab limit 5;--使用limit显示一次取得记录数
    for i in 1 .. v_emp_tab.count loop
        dbms_output.put_line('雇员编号:'||v_emp_tab(i).empno||',雇员姓名:'||v_emp_tab(i).ename);
    end loop;
    exit when emp_cursor%notfound;
  end loop;
end;

   fetch emp_cursor bulk collect into v_emp_tab limit 5; 一次取5条记录到集合中，减少了取的次数。

5.  带参数的游标

---------带参数的游标begin-------------
--业务说明: 查询出某部门中雇员名中包含C的员工的姓名
declare 
    c_ename emp.ename%type:='%C%';
    v_ename emp.ename%type;--雇员姓名
    cursor emp_cursor(
           name varchar2,--雇员名,模糊查询
           dtno number--部门编号
           ) is select ename from emp where ename like name and deptno=dtno;
begin
  open emp_cursor(c_ename,10);
  loop 
    fetch emp_cursor into v_ename;
    exit when emp_cursor%NOTFOUND;
    dbms_output.put_line(v_ename||'的名字包含C');
  end loop;
  close emp_cursor;
end;
---------带参数的游标end---------------
---------带参数的游标begin-------------
--业务说明: 查询出某部门中雇员名中包含C的员工的姓名
declare 
    c_ename emp.ename%type:='%C%';
    v_ename emp.ename%type;--雇员姓名
    cursor emp_cursor(
           name varchar2,--雇员名,模糊查询
           dtno number--部门编号
           ) is select ename from emp where ename like name and deptno=dtno;
begin
  open emp_cursor(c_ename,10);
  loop 
    fetch emp_cursor into v_ename;
    exit when emp_cursor%NOTFOUND;
    dbms_output.put_line(v_ename||'的名字包含C');
  end loop;
  close emp_cursor;
end;
---------带参数的游标end---------------

  注意: 申明游标变量是，参数的类型，不能使用长度限制:

         错误的写法: varchar2(50),number(7,2)