[PL/SQL]测试存储过程施行超长SQL(使用CLOB变量)
在存储过程执行动态SQL一般有两种方法:
1、EXECUTE IMMEDIATE sql语句.
11g支持 EXECUTE IMMEDIATE CLOB变量.
2、使用DBMS_SQL包
11g的DBMS_SQL.PARSE也已经支持CLOB变量
存储过程的参数VARCHAR2虽然已经支持32672的长度,但想传入更长的变量时候，我们要么使用LONG、要么使用LOB、要么使用多个VARCHAR2参数(需要在存储过程里拼接后再执行).
1、使用LONG类型传递
由于oracle已经明文建议不要再使用LONG,所以建议不要使用此类型做存储过程参数,实际上oracle很多函数也不支持LONG。
2、使用LOB对象类型
oracle对LOB类型大力推荐,也推出了DBMS_LOB包来辅助LOB对象的各种处理,所以我采用了CLOB类型做测试,结果证明CLOB完全可以处理超级大的SQL.
3、使用多个VARCHAR2参数
目前很多是采用这种方式,在存储里面进行拼接,不过虽然PL/SQL的varchar2变量可以到32762的长度,不过还是没有CLOB长.
下面开始准备使用CLOB变量做存储过程参数进行测试:
在这之前介绍一下SQL_TRACE，11g中sql跟踪的默认目录可以通过命令show parameter USER_DUMP_DEST查看
12:05:38 SYS@orcl> show parameter USER_DUMP_DEST
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string f:\app\administrator\diag\rdbm
s\orcl\orcl\trace
使用命令 alter session set tracefile_identifier=’测试跟踪存储过程' 更改跟踪文件的名称,方便识别
使用sys.dbms_system.set_sql_trace_in_session(...)来进行会话中的SQL跟踪,它有3个参数(SID,SERIAL#,SQL_TRACE)，所以需要查询到当前会话的SID及SERIAL#值.我这里使用下面的SQL查询到这2个值
select distinct b.sid, b.SERIAL#
from v$mystat a, v$session b
where a.sid = b.sid;
至此我将在存储过程中进行SQL的跟踪.
存储过程如下:
create or replace procedure p_TestClob
(
parray in CLOB
,POUT out SYS_REFCURSOR
) as
-- 测试存储过程参数为CLOB的情况
v_sql CLOB;
v_sid number;
v_SERIAL number;
TYPE F IS TABLE OF clob INDEX BY BINARY_INTEGER;--定义CLOB对象数组
V_P F;
V_SEP VARCHAR2(2) := '^';
rf sys_refcursor;
V_CURSOR NUMBER DEFAULT DBMS_SQL.OPEN_CURSOR;
V_RES NUMBER;
begin
dbms_lob.createtemporary(v_sql, true); --初始化CLOB
--sql跟踪
execute immediate 'alter session set tracefile_identifier=''测试跟踪存储过程' ||
fn_getname || ''' ';
select distinct b.sid, b.SERIAL#
into v_sid, v_SERIAL
from v$mystat a, v$session b
where a.sid = b.sid;
sys.dbms_system.set_sql_trace_in_session(v_sid, v_SERIAL, true);
--分离字段
SELECT * BULK COLLECT INTO V_P FROM TABLE(SPLITCLOB(PARRAY, V_SEP));
v_sql := 'SELECT ''' || v_p(1);
dbms_output.put_line('字段1长度:' || dbms_lob.getlength(v_p(1)));
if v_p.count > 1
then
for x in 2 .. v_p.count - 1
loop
DBMS_LOB.append(V_SQL, ''' as t' || to_char(x - 1) || ', ''');
DBMS_LOB.append(V_SQL, V_P(x));
end loop;
end if;
dbms_output.put_line('总长度为:' || dbms_lob.getlength(v_sql));
DBMS_LOB.append(V_SQL, ''' AS TT FROM DUAL ');
DBMS_SQL.PARSE(V_CURSOR, V_SQL, DBMS_SQL.NATIVE);--解析SQL
V_RES := DBMS_SQL.EXECUTE(V_CURSOR);--执行SQL
POUT := DBMS_SQL.TO_REFCURSOR(V_CURSOR);--转换为REF游标
dbms_output.put_line('成功了!!!');
sys.dbms_system.set_SQL_TRACE_in_session(v_sid, v_SERIAL, false);--关闭SQL跟踪
exception
when others then
dbms_output.put_line('失败了!!!');
sys.dbms_system.set_SQL_TRACE_in_session(v_sid, v_SERIAL, false);--关闭SQL跟踪
end p_TestClob;
SQL拼接完成后,可以使用DBMS_SQL包处理,也可以直接OPEN 游标 FOR V_SQL来返回执行后的结果集.
其中SPLITCLOB函数和fn_getname函数见本博客
http://blog.csdn.net/edcvf3/article/details/8050978一文
测试存储过程为:
declare
v_r sys_refcursor;
v_i integer;
v_cb clob := empty_clob();
begin
dbms_lob.createtemporary(v_cb, true);--初始化V_CB
for j in 1 .. 10000
loop
for i in 1 .. 100
loop
dbms_lob.append(v_cb, '999985' || i);
--v_cb := v_cb || 'TEST_CLOB' || i;
end loop;
v_cb := v_cb || '^';--^为字段分隔符
for i in 1 .. 100
loop
dbms_lob.append(v_cb, '00234567' || i);
end loop;
end loop;
v_i := dbms_lob.getlength(v_cb);
--debug
dbms_output.put_line('长度:' || v_i);
p_TestClob(v_cb, v_r);
end;
只要改变for后面的循环次数即可生成超级大的SQL语句.
我不断增大循环次数,当增加到如上10000*200次循环的时候,
执行结果为:
长度:17850000
字段1长度:792
总长度为:17987894
成功了!!!
花费时间:928.422 seconds
继续增大,仍然可以,只是时间需要的更长了.
呵呵,VARCHAR2变量没这么强大吧
可以发现测试的sql是这样的：SELECT ‘字段1’ as t1,‘字段2’ as t2,‘字段3’ as t3...‘最后字段' as tt from dual
当单个字段(如字段1)的长度超过4000的时候,会出现错误,猜想是因为在SQL里VARCHAR2只能支持4000字符长度,测试的结果如下:
长度:7568
字段1长度:4003
总长度为:4011
失败了!!!
查看SQL_TRACE文件发现如下错误:
PARSE ERROR #4:len=4011 dep=1 uid=84 oct=3 lid=84 tim=32007611307 err=1704
可以确定是在DBMS_SQL.PARSE解析SQL语句的时候出错的.
让我们调小一点，再测试发现
长度:7565
字段1长度:4000
总长度为:4008
成功了!!!
可见单个字段只能到4000的长度.
--------------------------------------------
至此可以体现CLOB变量的强大.加上ORACLE提供的DBMS_LOB包,我们就可以在PL/SQL编程中很方便的处理更长更大的变量了.