oracle在存储过程中动态生成sql语句长度过长

xtbzqw 2010-08-12 05:09:24

请问各位数据库高手，我在写一个oracle存储过程的时候，有这样的一个需求，用户输入起始日期和结束日期，然后统计出各个公司每个月的销售情况，我是用存储过程写的，根据条件拼成一条sql语句，然后查询返回一个游标结果集，如果输入的起始日期和结束日期差距很大的话，那么动态拼成的sql语句会很长，以致于超过varchar2的最长长度而报错，在网上找了一些资料，说可以用dbms_sql.varchar2s可以解决，但是由于自己对oracle数据库不是很熟悉，弄了半天也没有解决，还请各位帮忙指点
怎么样通过dbms_sql.varchar2s返回一个查询结果游标记录集

...全文

1467 23 打赏收藏转发到动态举报

写回复

用AI写文章

23 条回复

切换为时间正序

请发表友善的回复…

发表回复

碧水幽幽泉 2010-09-13

打赏
举报

嗯，group by 按月份分组就成了！

xtbzqw 2010-09-13

打赏
举报

问题已经解决了，换了一种方式做统计，sql语句中可以根据月份来做group by

macower 2010-09-12

打赏
举报

另外可以用 PARTITION BY month解决你到网上搜索下就有这个属于高级查询

你原来的SQL 有明显的问题
这个不能那样单纯的进行SQL 拼接还是用Oracle的高级查询比较好

macower 2010-09-12

打赏
举报

用游标循环就OK了

kingkingzhu 2010-08-24

打赏
举报

传说中有个 dbms_sql 随你sql多长

murton 2010-08-24

打赏
举报

你动态生成SQL可以分成几部分。定义几个VARCHAR2(4000)的变量，例如：
v_select_clause varchar2(4000);
v_where_clause varchar2(4000); --- 一个不够的话，可以多定义几个，每个存储一部分的where子句
v_orderby_clause varchar2(4000);
然后就往里面塞SQL语句的各个部分
最后要打开游标时，就可以这样：



open your_cursor for v_select_clause || v_where_clause || v_orderby_clause;

xieyu_zy 2010-08-24

打赏
举报

太神奇了，你的WHERE条件有多长呀？还是前面的SQL长，如果前面的SQL很长，要检查一下是不是有性能问题，可否换一下，如果没有换的必要，觉得也没有性能问题，那么你就把前面的很多SQL换成视图吧，那样的你SQL就简单多了。

Edwin12345 2010-08-19

打赏
举报

dbms_sql中有两种方法传入动态sql，一种是以varchar2（或long)传入的，长度受数据类型的限制，另外一种是以varchar2类型的table掺入sql语句，这个长度不受数据类型的限制，只受oracle服务器的限制。
具体用法打开dbms_sql包就可以看到

cnwz 2010-08-13

打赏
举报

楼主这个直接修改可以吗？
1、用BETWEEN代替 <= AND >= ，应该可以节省一定的长度
因为BETWEEN YYYYMMDD1 AND YYYYMMDD2是指 >=1的0时0分0秒，<= 2的0时0分0秒
也可以改为： >= YYYYMMDD1 AND < YYYYMMDD2 + 1，去掉时间部分
2、日期直接用 YYYYMMDDHH24MISS ，把那些连接符号去掉，应该也可以省一些

这个只是在长度超出不是特别多的情况下看看

minitoy 2010-08-13

打赏
举报

又试了下4000+长度的sql，也可行。10楼高。

minitoy 2010-08-13

打赏
举报

还真成

SQL> 

SQL> CREATE OR REPLACE PROCEDURE proc_ttt_1

  2  AS

  3  v_sql LONG;

  4  BEGIN

  5    v_sql:='select 1 from dual';

  6    EXECUTE IMMEDIATE v_sql;

  7  END;

  8  /

 

Procedure created

 

SQL> exec proc_ttt_1;

 

PL/SQL procedure successfully completed

 

SQL>

minitoy 2010-08-13

打赏
举报

e，还可以这样啊。我试了下execute immediate clob变量，被oracle b4了。

wbin_ndsc 2010-08-13

打赏
举报

把sql句的变量定义成long的试试啊，我也是听说的，呵呵

minitoy 2010-08-12

打赏
举报

没试出来好办法。将个思路。你sql之所以长是因为日期格式造成的，避免拼格式出现可以有效剪短sql长度。
创建一个临时表
CREATE global TEMPORARY TABLE test1
(id NUMBER(10),
month varchar(8),
seq number(10),
mnt number(10));
在过程里执行动态sql ；
insert into test1
select zs_group_id,to_char(zs_bull_start_time,'yyyymm'),months_between(trunc(t.month,'mm'),to_date(入参开始时间,'yyyymm')),count(*)
FROM p_wanhua_zs_code
GROUP BY zs_group_id,to_char(zs_bull_start_time,'yyyymm');
然后拼结果语句
SELECT ID,SUM(deocde(seq,0,mnt,0))......
FROM test1
GROUP BY ID;
OPEN v_cur for v_sql;

xtbzqw 2010-08-12