前言
在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进行统计操作,无法用游标做结果判断。
这个选择游标是必要的过程。
------------------------------------------------
总体感觉,现在看来,使用游标的坏处好像也没有什么!:)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/134308/viewspace-761609/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/134308/viewspace-761609/