sql server转oracle存储过程改造中游标替代方案

前言

在sql server转oracle存储过程改造中， 由于返回数据集操作比较频繁，针对效率及改造工作量寻求一较好的方式

目前做法

代码片段：

  OPEN countrycursor FOR

      SELECT country_code    country_code,

             country_id      country_id,

             country_name    country_name,

             lang_en         lang_name,

             country_name_sc country_name_sc,

             lang_sc         lang_name_sc

        FROM countries

       ORDER BY country_id ASC;

缺点

1，  游标取出是运行程序真正得到游标进行数据遍历才进行的，可能造成占用数据库时间过长。

2，  若open游标是从一些事务临时表进行取数时，而存储过程在结束有commit操作时，则游标数据丢失。

3，  若程序中要根据此游标数据条数进行判断操作时，则必须再对此SQL进行统计操作，无法用游标做结果判断。

例如：

select count(1) into temp from dc_client_config;

if temp=0 then

err:=1;  --information not ready

else

open client_cursor for select client_id, client_name,

    ip_addr1, ip_addr2, ip_addr3, ip_addr4,

    block_connection, heartbeat_interval, reconnect_interval,

    timeout_recv, xml_convert_Big5, group_id

from dc_client_config where enable_status = 1

order by client_id;

err:=0;

end if;

 

替代方案

一,结构化数组

说明：定义好与返回相同列的二维数组

大致代码：

type rec_msn_set is record

(

  command_id NUMBER(5),

  xmlcode_id NUMBER(5),

  sports_id  NUMBER(5),

  pool_id    NUMBER(5),

  xml_name   VARCHAR2(50),

  force_flag NUMBER(10)

)

;

 TYPE tab_msn_setArr IS TABLE OF rec_msn_set;

 la_msn_arr tab_msn_setArr;

SELECT       command_id ,

                     xmlcode_id ,

                     sports_id ,

                     pool_id ,

                     xml_name ,

                     force_flag    BULK COLLECT  into la_msn_arr

                FROM event_trigger ;

--此处是可以根据SQL%ROWCOUNT进行判断的，open游标是不能这样判断，因为当时是没有执行的

if SQL%ROWCOUNT=0 then

err:=1;  --information not ready

else

err:=0;

end if;

劣势:

要定义太多类型，且与select查询一致，且公用调用返回类型时，不能通用化

优势：

程序执行占用数据库连接时间相对过短。

可对结果集进行操作或判断。

二,非结构化数组

说明：所以返回类型都为一个通用二维数组，

大致代码：

CREATE OR REPLACE TYPE ttt IS TABLE OF VARCHAR2(100);

CREATE OR REPLACE TYPE type_table IS TABLE OF ttt;

la_msn_arr type_table;

 

SELECT             command_id ,

                     xmlcode_id ,

                     sports_id ,

                     pool_id ,

                     xml_name ,

                     force_flag    BULK COLLECT  into la_msn_arr

                FROM event_trigger ;

 

劣势:

所有类型基于为varchar2(100)，比较死板，且长度要定义查询出来的最大长度

优势：

程序执行占用数据库连接时间相对过短。

额外编码量小，公共调用通用化。

可对结果集进行操作或判断。

 

选择

基于一些考虑，目前查询的内容基于为字符型（且数字也可隐式转为字符）.因此感觉方案二较优，倾向于采用此方案。

代码实现

样例：

Pl/sql部分：

CREATE OR REPLACE TYPE tp_record IS TABLE OF VARCHAR2(100) ;

CREATE OR REPLACE TYPE tp_table IS TABLE OF tp_record;

CREATE OR REPLACE PROCEDURE p_test_001(lt_table1 OUT tp_table,

                                       lt_table2 OUT tp_table)

 

 AS

 

BEGIN

  SELECT  tp_record (command_id, xmlcode_id, sports_id, pool_id, xml_name, force_flag) BULK COLLECT  --一次性数据装载于数组

    INTO lt_table1

    FROM event_trigger

   WHERE msg_code = 200

   ORDER BY order_no, sports_id ASC, pool_id ASC;

 

END;

/

JAVA调用部分：

package com.ihs;

 

import java.sql.Connection;

import java.sql.SQLException;

import oracle.jdbc.driver.OracleCallableStatement;

import oracle.jdbc.driver.OracleTypes;

import oracle.sql.ARRAY;

import com.piliskys.dataStore.ConnectionUtil;

public class TestArrDb {

 

    /**

     * @param args

     * @throws SQLException

     */

    public static void main(String[] args) throws SQLException {

       // TODO Auto-generated method stub

       //此处得到一个数据库连接，

     Connection conn= ConnectionUtil.currentConnection("ihs_vm134");

        

        OracleCallableStatement stmt =(OracleCallableStatement) conn.prepareCall("begin p_test_001(?,?); end;"); 

    //注册两输出参数

        stmt.registerOutParameter( 1, OracleTypes.ARRAY,"TP_TABLE" );

        stmt.registerOutParameter( 2, OracleTypes.ARRAY,"TP_TABLE" );

        stmt.execute();  

        ARRAY simpleArray = stmt.getARRAY(1);

        ARRAY simpleArray2 = stmt.getARRAY(2);

       //第二个输出没有赋值，内容是为空的

          System.out.println(  "simpleArray2="+simpleArray2);

       //转换为java数组，应先判断是否为空操作，，，此处略

        Object[ ] values = (Object[])simpleArray.getArray();

       //输出数组内容                  for( int i = 0; i < values.length; i++ )

                   {

                     Object[] ff= (Object[] ) ((ARRAY)values[i]).getArray();

                       for( int j = 0; j < ff.length; j++ )

                    System.out.println( "row[" + i +","+j+ "] = '" + ff[j] +"'" );

                   }

        

    }

 

}

 

后记：

   内存跟长度定义无关，

   当采用java连接池操作时，数组内存不易释放，而游标是取完自己内存加收的

后来再进行一些测试发现：

针对我上次说的游标三个不好之处

1，  游标取出是运行程序真正得到游标进行数据遍历才进行的，可能造成占用数据库时间过长。

这个我测试，用java程序运行存储过程及得到游标数据时间，比运行存储过程得到数组时间还短, 而且游标的方式是一用完内存就释放的，

这一点，游标应该更有优势。

2，  若open游标是从一些事务临时表进行取数时，而存储过程在结束有commit操作时，则游标数据丢失。

       对于这个，如果要采用游标方式，则临时表改为会话级，使用时先在truncate此临时表，当然，这样退出的时候，数据还保存在临时表中，会占用临时表空间，如数据量不大应该没有问题，另，如用事务临时表，遇到此情况也可由应用程序来commit，

3，  若程序中要根据此游标数据条数进行判断操作时，则必须再对此SQL进行统计操作，无法用游标做结果判断。

        这个选择游标是必要的过程。

 

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

 总体感觉，现在看来，使用游标的坏处好像也没有什么！:)点击打开链接