100分求！！ORACLE 存储过程返回结果集，拼接为字符串输出为clob

t13523417 2014-08-26 06:41:00

首先试了一种用系统游标的方式返回结果集，无奈90万的数据，里面加各种统计业务型sql，导出excle，直接sql运行消耗1分钟，
但放到存储过程中，以游标方式速度就非常慢
而后找问题，尝试以自定义游标的方式，速度依然很慢，说明不是这方面问题，
对oracle表示不太通，只能一个方法试试。
请继续往下看~~~~~
据说改为返回字符串的方式速度会很快，于是：
改为把查询出来的结果集在存储过程中拼接成字符串的方式，数据较少，速度看不出来，测试ok，但是问题来了。。。
返回输出参数varchar2只能是4000，所以报错：
PL/SQL: 数字或值错误 : 字符串缓冲区太小
对于我这个近百万的数据表来说肯定不够的，所以想到了输出参数为clob类型，但是数据量小的时候可以执行，比如几十条，但是数据量稍微大点就出错，比如1000多条就报错：Cause: java.sql.SQLException: ORA-06502: PL/SQL: 数字或值错误

[b]大肾们，可知道是个什么情况啊？
上代码：中间一坨子省略下：
create or replace procedure rebate_anlyse(date1 in varchar2,date2 in varchar2, v_cursor out CLOB)
as
v_mobiles varchar2(20);
v_ljamt varchar2(20);
strs varchar2(100);
begin
DECLARE CURSOR myCusor IS
SELECT mobiles, ljamt。。。。from。。where。。。;
begin
OPEN myCusor;
LOOP
fetch myCusor into v_mobiles, v_ljamt ;
EXIT WHEN myCusor%NOTFOUND;
strs:= v_mobiles||':'||v_ljamt||';'; ---结果集拼接成一个字符串
v_cursor:=v_cursor||strs; ----把字符串输出为clob类型
END LOOP;
CLOSE myCusor;
end;
end rebate_anlyse;
mybatis调用代码：
<select id ="execProcedure" parameterType="java.util.Map" statementType="CALLABLE" >
{call rebate_anlyse(#{windate_begin, jdbcType=VARCHAR, mode=IN},
#{windate_end, jdbcType=VARCHAR, mode=IN},
#{v_cursor, mode=OUT, jdbcType=VARCHAR,javaType=java.lang.String})}
</select >

现在问题是：导出时执行比如导8月1号到2号数据共几十条，可以到，几百条也不报错可以导出，一千多条就报错。。。。。。。求大婶们。。？？？？是clob的问题吗？clob 4G已经够大了啊，什么情况呢？？
或者谁有木有什么好的解决方案呢？

...全文

1964 24 打赏收藏转发到动态举报

写回复

用AI写文章

24 条回复

切换为时间正序

请发表友善的回复…

发表回复

t13523417 2014-09-10

打赏
举报

@bw555，@wildwave，感谢楼上俩位专家的耐心和热心解答，多谢了！按楼上办法单查询时间减少了一半，但主要问题出在，临时表没有加索引，原因是我百度时很多网友说道临时表不建议加索引，顾信以为真，所以，以后有什么问题还是要自己验证过才好。也或许跟其他网友的应用场景不一样吧。我这里加入临时表10数据20秒就ok了。100万的话1分半ok。再次感谢俩位专家斑猪！结贴！

t13523417 2014-09-09

打赏
举报

引用 15 楼 bw555 的回复:

试试jdbcType=Clob

兄台，能不能帮指点上面20楼我贴出来的sql的性能方面是否有什么问题。。

bw555 2014-09-09

打赏
举报


SELECT SUM(winamt)||','||count(*) into v_ljamt FROM temp_rebate_anlyse WHERE mobiles = v_mobiles;
SELECT city        into v_city FROM temp_rebate_anlyse WHERE rownum =1 AND mobiles = v_mobiles;
SELECT SUM(winamt)||','||count(*)  into v_nowamt FROM temp_rebate_anlyse WHERE windate >=date1 AND windate <=date2 AND mobiles = v_mobiles;
SELECT SUM(winamt)  into v_ljOilamt FROM temp_rebate_anlyse WHERE mobiles = v_mobiles AND isoil ='0';
SELECT SUM(winamt)||','||count(*) into v_nowOilamt FROM temp_rebate_anlyse WHERE isoil ='0' AND windate >=date1 AND windate <=date2 AND mobiles = v_mobiles;

以上语句可以利用decode或case when合并到一起执行，这样只需遍历一边就可以了未测试，参考

select SUM(winamt)||','||count(*),max(v_city),
sum(case when windate >=date1 AND windate <=date2 then winamt end)||','
  ||nvl(sum(case when windate >=date1 AND windate <=date2 then 1 end),0),
sum(decode(isoil,'0',winamt)),
sum(case when isoil ='0' AND windate >=date1 AND windate <=date2 then winamt end)||','
  ||nvl(sum(case when isoil ='0' AND windate >=date1 AND windate <=date2 then 1 end),0)
into v_ljamt,v_city,v_nowamt,v_ljOilamt,v_nowOilamt
where mobiles = v_mobiles;

t13523417 2014-09-05

打赏
举报

引用 18 楼 wildwave 的回复:

SELECT count(1) from。。where。。。; 游标里的这条语句，将查询结果列改成count(1)，执行看看要多久

斑猪兄。。试了下count(1) ，时间基本保持不变的。。还有我说明下，里面的sum和count拼接在一起的原因，我认为是这样是做了一次查询，单独分开的话就是俩个查询，经过实践，拼起来，速度确实比不拼快乐一大截呢，因为拼接起来的后面的where条件是一样的，不一样的就不能拼了。。业务上是统计每个手机号做的消费情况，总金额，加油金额，非加油金额，某段时间的加油金额，某段时间非加油金额，及其各自笔数。。是在想不到什么办法了。。再次感谢斑猪。。。

t13523417 2014-09-05

打赏
举报

引用 19 楼 wildwave 的回复:

提供下插入临时表的SQL语句以及上一条语句和游标用的SQL语句的执行计划，这里有点问题

您好：这是修改后的sql，用这个sql10万数据，耗时5分钟：

create or replace procedure rebate_anlyse(date1 in varchar2,date2 in varchar2, v_cursor out CLOB)
as
v_mobiles varchar2(20);
v_ljamt varchar2(20); 
v_nowamt varchar2(20);
v_ljOilamt varchar2(20); 
v_city varchar2(20);
v_nowOilamt varchar2(20); 
strs  varchar2(200);  
begin  
  dbms_lob.createtemporary(v_cursor,true); 
  /**********先放入临时表**********/ 
  /***************************再业务统计************************************/ 
    insert into temp_rebate_anlyse   
       SELECT * FROM (
          SELECT mobiles ,city ,winamt,windate,isoil FROM  t_act_wintxn_group t  
                   where windate>=date1 and windate<=date2
          union all
          SELECT product_no,create_user,nvl(win_money,0) win_money,win_date,'1' FROM  t_act_win_txn_his y
                   where recharge_result='01' and win_date>=date1 and win_date<=date2
          union all
          SELECT product_no , create_user,  nvl(win_money,0) win_money,win_date,'1' FROM  t_act_win_txn t
                   where recharge_result='01' and win_date>=date1 and win_date<=date2
    );
      
    DECLARE 
      CURSOR  myCusor IS select distinct(mobiles)   from temp_rebate_anlyse WHERE  windate >=date1 AND windate <=date2  ;  
      begin 
        OPEN myCusor;
        LOOP
            fetch myCusor into  v_mobiles; 
            EXIT WHEN myCusor%NOTFOUND;
              SELECT SUM(winamt)||','||count(*) into v_ljamt FROM temp_rebate_anlyse WHERE mobiles = v_mobiles;
              SELECT city        into v_city FROM temp_rebate_anlyse WHERE rownum =1 AND mobiles = v_mobiles;
              SELECT SUM(winamt)||','||count(*)  into v_nowamt FROM temp_rebate_anlyse WHERE windate >=date1 AND windate <=date2 AND mobiles = v_mobiles;
              SELECT SUM(winamt)  into v_ljOilamt FROM temp_rebate_anlyse WHERE mobiles = v_mobiles AND isoil ='0';
              SELECT SUM(winamt)||','||count(*) into v_nowOilamt FROM temp_rebate_anlyse WHERE isoil ='0' AND windate >=date1 AND windate <=date2 AND mobiles = v_mobiles;
              
              strs:= v_mobiles||':'||v_ljamt||':'||v_nowamt||':'||v_ljOilamt||':'||v_nowOilamt||':'||v_city||';';
              --v_cursor:=v_cursor||strs;   
              DBMS_LOB.Append(v_cursor,strs);--CLOB拼接方式，非竖线
        END LOOP; 
        CLOSE myCusor; 
      end; 

end rebate_anlyse;

================================================================ =============================================================== 这个是修改前的sql，10万数据，耗时6分钟。。 create or replace procedure rebate_anlyse(date1 in varchar2,date2 in varchar2, v_cursor out CLOB) as v_mobiles varchar2(20); v_ljamt varchar2(20); v_nowamt varchar2(20); v_ljOilamt varchar2(20); v_city varchar2(20); v_nowOilamt varchar2(20); strs varchar2(200); begin dbms_lob.createtemporary(v_cursor,true); /**********先放入临时表**********/ /***************************再业务统计************************************/ insert into temp_rebate_anlyse SELECT * FROM ( SELECT mobiles ,city ,winamt,windate,isoil FROM t_act_wintxn_group t where windate>=date1 and windate<=date2 union all SELECT product_no,create_user,nvl(win_money,0) win_money,win_date,'1' FROM t_act_win_txn_his y where recharge_result='01' and win_date>=date1 and win_date<=date2 union all SELECT product_no , create_user, nvl(win_money,0) win_money,win_date,'1' FROM t_act_win_txn t where recharge_result='01' and win_date>=date1 and win_date<=date2 ); DECLARE CURSOR myCusor IS SELECT mobiles, (SELECT SUM(winamt)||','||count(*) FROM temp_rebate_anlyse WHERE mobiles = t.mobiles ) ljamt, (SELECT city FROM temp_rebate_anlyse WHERE mobiles = t.mobiles AND rownum =1) city, (SELECT SUM(winamt)||','||COUNT(*) FROM temp_rebate_anlyse WHERE mobiles = t.mobiles AND windate >=date1 AND windate <=date2) nowamt, (SELECT SUM(winamt) FROM temp_rebate_anlyse WHERE mobiles = t.mobiles AND isoil ='0') ljOilamt, (SELECT SUM(winamt)||','||COUNT(*) FROM temp_rebate_anlyse WHERE mobiles = t.mobiles AND isoil ='0' AND windate >=date1 AND windate <=date2) nowOilamt FROM (select distinct(mobiles),windate from temp_rebate_anlyse WHERE windate >=date1 AND windate <=date2 ) t order by city; begin OPEN myCusor; LOOP fetch myCusor into v_mobiles, v_ljamt,v_nowamt,v_ljOilamt ,v_nowOilamt,v_city; EXIT WHEN myCusor%NOTFOUND; strs:= v_mobiles||':'||v_ljamt||':'||v_nowamt||':'||v_ljOilamt||':'||v_nowOilamt||':'||v_city||';'; --v_cursor:=v_cursor||strs; DBMS_LOB.Append(v_cursor,strs);--CLOB类型不能直接竖线拼接，clob有自己的字符串操作方法 END LOOP; CLOSE myCusor; --dbms_output.put_line(v_cursor); end; end rebate_anlyse;

小灰狼W 2014-09-05

打赏
举报

提供下插入临时表的SQL语句以及上一条语句和游标用的SQL语句的执行计划，这里有点问题

小灰狼W 2014-09-05

打赏
举报

SELECT count(1) from。。where。。。; 游标里的这条语句，将查询结果列改成count(1)，执行看看要多久

t13523417 2014-09-05

打赏
举报

引用 15 楼 bw555 的回复:

试试jdbcType=Clob

兄台的方法是对的。。感谢！最后还有个问题想跟您讨论下：就是上面的sql性能还是有问题，就是我这个逻辑，三张表总和有百万的数据量，我先放入临时表，再后面直接拿临时表数据操作，这样查询速度还是很慢，后来下面的查询改为了游标，循环遍历每个sum或count计算，再拼接成字符串输出clob，这样速度提高了一些，但离期望的速度还是很远。10万数据，先插临时表，后查询，再拼接，耗时6分钟。。后者，先插临时表，再游标遍历每条输出耗时5分钟。。请问有没有什么好的解决方案或其他查询方法？可以根本的去优化这个sql呢？

t13523417 2014-09-05

打赏
举报

引用 14 楼 wildwave 的回复:

REBATE_ANLYSE的第112行是一个dbms_output.put_line? 这个输出过程的参数类型是varchar2，不管你用什么变量来传入，都会转成varchar2，因此会报错只要出现ora- 错误提示，基本都存在数据库方面的问题至于存储过程在数据库这边执行都正常以后，外部程序怎么来取clob我不太清楚，这一点我不能帮你

斑猪，好厉害，已经好了，不过性能还是有问题，能不能再问你最后一个问题，就是我这个逻辑，三张表总和有百万的数据量，我先放入临时表，再后面直接拿临时表数据操作，这样查询速度还是很慢，后来下面的查询改为了游标，循环遍历每个sum或count计算，再拼接成字符串输出clob，这样速度提高了一些，但离期望的速度还是很远。10万数据，先插临时表，后查询，再拼接，耗时6分钟。。后者，先插临时表，再游标遍历每条输出耗时5分钟。。请问有没有什么好的解决方案或其他查询方法？可以根本的去优化这个sql呢？

t13523417 2014-08-27

打赏
举报

引用 10 楼 wildwave 的回复:

strs不需要定义成clob，按原来的varchar2就行了能不能贴下完整代码

完整代码如下： create or replace procedure rebate_anlyse(date1 in varchar2,date2 in varchar2, v_cursor out CLOB) as v_mobiles varchar2(20); v_ljamt varchar2(20); v_ljnum varchar2(20); v_nowamt varchar2(20); v_ljOilamt varchar2(20); v_ljNotOilamt varchar2(20); v_city varchar2(20); v_nowOilamt varchar2(20); strs varchar2(200); begin dbms_lob.createtemporary(v_cursor,true); /**********先放入临时表**********/ insert into temp_rebate_anlyse SELECT * FROM ( 。。。。三张表的union all有点长。。 ); /***************************业务统计************************************/ if date1 is not null and date2 is not null then DECLARE CURSOR myCusor IS SELECT mobiles, ljamt,ljnum,nowamt,ljOilamt,(ljamt-ljOilamt) ljNotOilamt,nowOilamt,city FROM( SELECT mobiles, (SELECT NVL(SUM(winamt),0) FROM temp_rebate_anlyse WHERE mobiles = t.mobiles ) ljamt, (SELECT COUNT(*) FROM temp_rebate_anlyse WHERE mobiles = t.mobiles ) ljnum, (SELECT city FROM temp_rebate_anlyse WHERE mobiles = t.mobiles AND rownum =1) city, (SELECT NVL(SUM(winamt),0)||','||COUNT(*) FROM temp_rebate_anlyse WHERE mobiles = t.mobiles AND windate >=date1 AND windate <=date2) nowamt, (SELECT NVL(SUM(winamt),0) FROM temp_rebate_anlyse WHERE mobiles = t.mobiles AND wintype like '%加油') ljOilamt, (SELECT NVL(SUM(winamt),0)||','||COUNT(*) FROM temp_rebate_anlyse WHERE mobiles = t.mobiles AND wintype like '%加油' AND windate >=date1 AND windate <=date2) nowOilamt FROM (select * from temp_rebate_anlyse ORDER BY windate desc ) t WHERE 1=1 AND windate >=date1 AND windate <=date2 GROUP BY mobiles ); begin OPEN myCusor; LOOP fetch myCusor into v_mobiles, v_ljamt,v_ljnum,v_nowamt,v_ljOilamt,v_ljNotOilamt,v_nowOilamt,v_city; EXIT WHEN myCusor%NOTFOUND; strs:= v_mobiles||':'||v_ljamt||':'||v_ljnum||':'||v_nowamt||':'||v_ljOilamt||':'||v_ljNotOilamt||':'||v_nowOilamt||':'||v_city||';'; --v_cursor:=v_cursor||strs; DBMS_LOB.Append(v_cursor,strs); END LOOP; CLOSE myCusor; end; elsif date2 is null and date1 is not null then elsif date1 is null and date2 is not null then else 中间是一些类似上面的判断。。省略下。。。 end if; delete from temp_rebate_anlyse; commit; end rebate_anlyse;

小灰狼W 2014-08-27

打赏
举报

strs不需要定义成clob，按原来的varchar2就行了能不能贴下完整代码

t13523417 2014-08-27

打赏
举报

引用 5 楼 bw555 的回复:

strs也需要定义为lob类型 Dbms_lob.append的两个参数都应该是lob类型

strs:= v_mobiles||':'||v_ljamt||':'||v_ljnum||':' 这个地方是不是也要改为append 啊？后面有多个字符串拼接，语法怎么写？

t13523417 2014-08-27

打赏
举报

引用 5 楼 bw555 的回复:

strs也需要定义为lob类型 Dbms_lob.append的两个参数都应该是lob类型

我改为： strs clob; begin dbms_lob.createtemporary(v_cursor,true); dbms_lob.createtemporary(strs,true); ；。。。。。。。。 DBMS_LOB.Append(v_cursor,strs); 报错“：### Error querying database. Cause: java.sql.SQLException: ORA-06502: PL/SQL: 数字或值错误 ORA-06512: 在 line 1 ### The error may involve com.huateng.mis.mapper.TActWintxnGroupMapper.execProcedure-Inline ### The error occurred while setting parameters ### Cause: java.sql.SQLException: ORA-06502: PL/SQL: 数字或值错误 ORA-06512: 在 line 1 ; bad SQL grammar []; nested exception is java.sql.SQLException: ORA-06502: PL/SQL: 数字或值错误 ORA-06512: 在 line 1

t13523417 2014-08-27

打赏
举报

引用 6 楼 wildwave 的回复:

使用DBMS_LOB.Append来操作时，需要先将该对象初始化在loop前，先执行 dbms_lob.createtemporary(v_cursor,true); 即可

append方法里面的俩个参数是不是必须都是clob类型的，我那个strs是字符串类型的，可以这样追加吗？改了。。还是报错的： ### Error querying database. Cause: java.sql.SQLException: ORA-06502: PL/SQL: 数字或值错误 ORA-06512: 在 line 1 ### The error may involve com.huateng.mis.mapper.TActWintxnGroupMapper.execProcedure-Inline ### The error occurred while setting parameters ### Cause: java.sql.SQLException: ORA-06502: PL/SQL: 数字或值错误 ORA-06512: 在 line 1 ; bad SQL grammar []; nested exception is java.sql.SQLException: ORA-06502: PL/SQL: 数字或值错误 ORA-06512: 在 line 1

小灰狼W 2014-08-27

打赏
举报

使用DBMS_LOB.Append来操作时，需要先将该对象初始化在loop前，先执行 dbms_lob.createtemporary(v_cursor,true); 即可

bw555 2014-08-27

打赏
举报

strs也需要定义为lob类型 Dbms_lob.append的两个参数都应该是lob类型

t13523417 2014-08-27

打赏
举报

引用 2 楼 bw555 的回复:

楼上正解 lob类型的操作都有相应的专业函数，不能简单当作字符串来进行处理的否则就会受到字符串长度的限制

按楼上的方法改完后修改的那行报错： ### Error querying database. Cause: java.sql.SQLException: ORA-06502: PL/SQL: 数字或值错误 : invalid LOB locator specified: ORA-22275 ORA-06512: 在 "SYS.DBMS_LOB", line 639 ORA-06512: 在 "AHEBPP.REBATE_ANLYSE", line 108 ORA-06512: 在 line 1

t13523417 2014-08-27

打赏
举报

引用 1 楼 wildwave 的回复:

其实我没搞清楚前面直接sql运行到游标到clob这个过程是怎么回事... 但你这里报错是因为这句： v_cursor:=v_cursor||strs; xxx||xxx这种是字符串的拼接方式，字符串在pl/sql代码中最大的长度为32767 应该改成 DBMS_LOB.Append(v_cursor,strs);

改完后修改的那行报错： ### Error querying database. Cause: java.sql.SQLException: ORA-06502: PL/SQL: 数字或值错误 : invalid LOB locator specified: ORA-22275 ORA-06512: 在 "SYS.DBMS_LOB", line 639 ORA-06512: 在 "AHEBPP.REBATE_ANLYSE", line 108 ORA-06512: 在 line 1

bw555 2014-08-27

打赏
举报

试试jdbcType=Clob

小灰狼W 2014-08-27

打赏
举报

REBATE_ANLYSE的第112行是一个dbms_output.put_line? 这个输出过程的参数类型是varchar2，不管你用什么变量来传入，都会转成varchar2，因此会报错只要出现ora- 错误提示，基本都存在数据库方面的问题至于存储过程在数据库这边执行都正常以后，外部程序怎么来取clob我不太清楚，这一点我不能帮你