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

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

楼上正解
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

按楼上的方法改完后修改的那行报错：
### 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

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

使用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

我改为：
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

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

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 thenelsif date1 is null and date2  is not null then
else中间是一些类似上面的判断。。省略下
end if;
  delete from temp_rebate_anlyse;
  commit;end rebate_anlyse;

看不出问题来
按照你的格式写了一段，执行正常
SQL> create or replace procedure p_test1(date1 in varchar2,date2 in varchar2, v_cursor out CLOB)
  2  as
  3  strs varchar2(100);
  4  v_dummy varchar2(10);
  5  begin
  6    dbms_lob.createtemporary(v_cursor,true);
  7    declare
  8    cursor myCusor is select dummy from dual;
  9    begin
10      open myCusor;
11      loop
12        fetch myCusor into v_dummy;
13        EXIT WHEN myCusor%NOTFOUND;
14        strs:=v_dummy||':'||date1||':'||date2||';';
15        DBMS_LOB.Append(v_cursor,strs);
16      end loop;
17      close myCusor;
18    end;
19  dbms_output.put_line(v_cursor);
20  end;
21  /

Procedure created

SQL> set serverout on
SQL> declare
  2  v_cur clob;
  3  begin
  4  p_test1('2010-1-1','2010-12-31',v_cur);
  5  end;
  6  /

X:2010-1-1:2010-12-31;

PL/SQL procedure successfully completed

SQL> 如果存储过程能够编译成功，可以使用debug来定位
我个人更习惯将create procedure ... as ..
改写成declare  begin end 的格式来调试，方便查找错误的出处

SQL> set serverout on
SQL> declare v_cur clob;
  2  begin rebate_anlyse('20140808','20140811',v_cur); end;
  3  /
13305517589:1368:3:338,2:0:1368:0,0:合肥市;13223423234:0:1:0,1:0:0:0,0:合肥市;18
130053601:194:2:194,2:0:194:0,0:合肥市;18105692924:198:2:198,2:0:198:0,0:合肥市;
13512412:0:1:0,1:0:0:0,0:合肥市;18956177636:9750:4:9150,2:5000:4750:5000,1:芜湖
市;18955891515:2600:4:74,1:0:2600:0,0:阜阳市;15345586712:4596:2:1596,1:0:4596:0,
0:阜阳市;13335517409:864:2:864,2:0:864:0,0:合肥市;PL/SQL procedure successfully completed.SQL> declare v_cur clob;
  2  begin
  3  rebate_anlyse('20140803','20140803',v_cur);
  4  end;
  5  /
declare v_cur clob;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "AHEBPP.REBATE_ANLYSE", line 112
ORA-06512: at line 3
SQL> ^[[A^[[A^CSQL> declare v_cur clob;
  2  begin
  3  rebate_anlyse('20140803','20140803',v_cur);
  4  end;
  5  /PL/SQL procedure successfully completed.帮我再看下，第一次执行08~11号数据十来条，加上输出语句dbms_output.put_line(v_cursor);可以输出；
第二次执行8月03号一天数据有1300条大概，加上输出语句dbms_output.put_line(v_cursor);报错如上；
第三次执行3号一天数据1300条，去掉输出语句dbms_output.put_line(v_cursor); 不报错了。
这样是不是说明输出语句支持的字符数跟varchar2一样的，不能超过32767，而我我1楼写
的调用代码，用mybatis调用的时候报错是不是说明是外部程序调用的错误，而不是ORACLE的错误？：
jdbcType=VARCHAR,javaType=java.lang.String
是不是有什么问题？
大哥，有木有什么指点呢？我这点oracle知识是已经殚精竭虑了。。

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

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

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

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

提供下插入临时表的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;

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

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

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;

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

调试易

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

解决方案 »