oracle存储过程杂记

一、游标

　　游标是SQL的一个内存工作区，它的作用是将数据库中的数据从磁盘中取出放到临时工作区，在临时工作区进行数据处理，然后再将数据返回给其他处理程序或者回写到数据库，这样可以避免频繁访问磁盘，优化程序的效率。游标有两种类型：隐式游标和显示游标。如果需要返回多行数据，就要由程序员自己定义一个显示游标。在java+ibatis中，经常会需要操作数据库后返回一个java bean对象，游标的作用就这在这里，建立一个存储过程，ibatis的ParameterMap中声明一个返回参数，jdbcType为ORACLECURSOR，形参为存储过程中的sys_refcursor，家里电脑跑不起oracle，所以网上找一个实例：

 <resultMap class="hashmap" id="backmap">
       <result property="userid" column="USERID"/>
       <result property="username" column="USERNAME"/>
       <result property="userpwd" column="USERPWD"/>
       <result property="groupname" column="GROUPNAME"/>
    </resultMap>
    <parameterMap class="hashmap" id="pro_cursor_map">
       <parameter property="backcursor" javaType="java.sql.ResultSet"
                  jdbcType="ORACLECURSOR" mode="OUT" resultMap="backmap"/>
    </parameterMap>
    <procedure id="pro_cursor" parameterMap="pro_cursor_map">
       {call user_account_proc1(?)}
    </procedure>

　　1、隐式游标

　　数据库的DML（Data Manipulation Language）操作和单行select语句会使用隐式游标：INSERT, UPDATE, DELETE和单行select语句SELECT ... INTO ...。隐式游标没有明确的open, declare, close, fetch等操作，在oracle的pl/sql中，隐式游标的名字为“SQL”，所以我们可以这样访问隐式游标的属性：
%ROWCOUNT :返回到目前为止，已经从游标中取出的记录数量，也就是DML语句成功执行的数据行数；
　　%FOUND ：如果成功取到数据返回true，否则返回false;
　　%NOTFOUND :%ISOPEN ：如果游标打开返回true，否则返回false;
　　%BULK_ROWCOUNT ：返回forall语句修改的记录数量;
　　%BULK_EXCEPTIONS ：返回forall语句修改记录时的异常信息.
注意，通过SQL游标总是返回上一个操作的游标属性。

　　2、显示游标

　　显示游标相对于隐式游标而言，游标从declare, open, fetch, close是一个完整的生命流程：

　　一个这样的游标可以被多次open进行使用，显式游标是静态游标，它的作用域是全局的，但也必须明白，静态游标也只有pl/sql代码才可以使用她。显示游标声明：

CURSOR cursor_name [ ( [ parameter_1 [, parameter_2 ...] ) ]
      [ RETURN return_specification ]
IS sql_select_statements
[FOR UPDATE [OF [column_list]];　

声明游标之后，打开游标，打开游标之后实际上并没有取回相关数据，这是在下一步fectch。

OPEN cursor_name [ ( argument_1 [, argument_2 ...] ) ]; 

fetch：

FETCH cursor_name INTO record or variables

然后关闭游标：

CLOSE cur_chief;

来看看一个完整的游标操作实例（set serveroutput on 这一句需要在SQLPLUS 下或者 PL/SQL DEV的command窗口下运行）：

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
  -- declare a cursor
  CURSOR cur_chief IS
      SELECT first_name,
             last_name,
             department_name
      FROM employees e
      INNER JOIN departments d ON d.manager_id = e.employee_id;
 
  r_chief cur_chief%ROWTYPE;
BEGIN
  OPEN cur_chief;
  LOOP
    -- fetch information from cursor into record
    FETCH cur_chief INTO r_chief;
 
    EXIT WHEN cur_chief%NOTFOUND;
 
    -- print department - chief
    DBMS_OUTPUT.PUT_LINE(r_chief.department_name || ' - ' ||
                         r_chief.first_name || ',' ||
                         r_chief.last_name);
  END LOOP;
  -- close cursor cur_chief
  CLOSE cur_chief;
END;
/

　　3、ref cursor

　　ref cursor属于动态游标，直到运行时才知道这条查询。ref游标可以动态打开，或者由一组SQL静态语句打开。比如：

Declare 
      type rc is ref cursor; 
      cursor c is select * from dual; 
      l_cursor rc; 
    begin 
      if (to_char(sysdate,'dd') = 30) then 
          -- ref cursor with dynamic sql 
          open l_cursor for 'select * from emp'; 
      elsif (to_char(sysdate,'dd') = 29) then 
          -- ref cursor with static sql 
          open l_cursor for select * from dept; 
      else 
           -- with ref cursor with static sql 
           open l_cursor for select * from dual; 
      end if; 
      -- the "normal" static cursor 
      open c; 
    end; 
    / 

普通游标和ref游标的区别有：

1）PL/SQL静态光标不能返回到客户端，只有PL/SQL才能利用它。ref光标能够被返回到客户端，这就是从Oracle的存储过程返回结果集的方式。
2）PL/SQL静态光标可以是全局的，而ref光标则不是。 也就是说，不能在包说明或包体中的过程或函数之外定义ref光标。 只能在定义ref光标的过程中处理它，或返回到客户端应用程序。
3）ref光标可以从子例程传递到子例程，而光标则不能。 为了共享静态光标，必须在包说明或包体中把它定义为全局光标。 因为使用全局变量通常不是一种很好的编码习惯，因此可以用ref光标来共享PL/SQL中的光标，无需混合使用全局变量。
最后，使用静态光标--通过静态SQL（但不用ref光标）--比使用ref光标效率高，而ref光标的使用仅限于以下几种情况：
把结果集返回给客户端；
在多个子例程之间共享光标（实际上与上面提到的一点非常类似）；
没有其他有效的方法来达到你的目标时，则使用ref光标，正如必须用动态SQL时那样；
简言之，首先考虑使用静态SQL，只有绝对必须使用ref光标时才使用ref光标，也有人建议尽量使用隐式游标，避免编写附加的游标控制代码（声明，打开，获取，关闭），也不需要声明变量来保存从游标中获取的数据。

　　4、sys_refcursor

　　sys_refcursor是oracle9i以后系统定义的一个refcursor，主要作用是用于存储过程返回结果集。用oracle存储过程的引用游标sys_refcursor 返回数据时，要注意if条件，如果将游标操作放在if语句块中，当不满足if的条件时，游标就不会打开，这时返回到java中就会异常，cursor is closed。所以

.... -- 部分代码

  v_sql := v_sql || ' and ii.payrequestid=:v_var';

.... -- 部分代码

open v_ourcur for v_sql using v_order; 

一般都放在最外层。注意上例中游标动态注入变量值，:=var的用法，以及用到了using——>动态注入变量值。

二、动态SQL

　　有时候会需要在存储过程中根据输入参数构造不同的SQL，包括SQL语句、字段值等等，这时候动态SQL就显得很有用了。如果拼接字段，直接用“||”字符串连接，如果需要往一个sql语句字符串注入字段值，则需要用到using 动态注入。

v_sql_order := 'select ' || v_order_id || ' from ' || v_tname_wap || ' right join dual on '|| v_order_str ||'=:v_var'; 
execute immediate v_sql_order into v_order using v_requestid; 

注意，在执行v_sql_order动态sql，得到结果可能为空，这是如果直接赋值给v_order，则会报异常，所以我这里用到了右连接right join dual，还要注意每段字符串之间要多留空格，不然拼接出来的v_sql_order很有可能就是这样：

select order_idfrom  table_wapright join dual on order_id=1;

而正确情况应该是这样：

select order_id from  table_wap right join dual on order_id=1; 

这是血的教训～~