[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_DESTNAME                                 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编程中很方便的处理更长更大的变量了.