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

Question

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

Answer 1

@@
between and不就可以了，怎么会很长呢

Answer 2

create or replace procedure p_wh_group_month_add_count(
--统计各大类每月新增代码数量结果
in_begin_day in varchar2,
--起始日期(如：2010-08-08)
in_end_day in varchar2,
--结束日期(如：2010-08-08)
v_cur out sys_refcursor --返回游标
--返回各大类每月新增代码数量结果
) is
i NUMBER;v_poor_month NUMBER;
--起始日期和结束日期月份之差
v_begin_month_last_day varchar2(20);
--起始日期的这个月的最后一天
v_end_month_begin_day varchar2(20);
--结束日期的这个月的第一天
v_month_day varchar2(20);
v_temp_first_day DATE;v_sql varchar2(32767) ;
begin
v_temp_first_day:=to_date(in_begin_day,'yyyy-mm-dd');
v_begin_month_last_day:=to_char(last_day(v_temp_first_day),'yyyy-mm-dd');
v_end_month_begin_day:=substr(in_end_day,0,8)||'01';v_poor_month:=fun_month_num(in_begin_day,in_end_day);v_sql:='select t.zs_group_id';
i:=0;
while i<=v_poor_month loop
if i=0 then
v_sql:=v_sql||',count(case when t.zs_bull_start_time>=(to_date('''||in_begin_day||' 00:00:00'',''yyyy-mm-dd hh24:mi:ss'')) and t.zs_bull_start_time<=(to_date('''||v_begin_month_last_day||' 23:59:59'',''yyyy-mm-dd hh24:mi:ss'')) then t.zs_bull_id end) "'||i||'"' ;
end if;
if i>0 and i<v_poor_month then
v_temp_first_day:=add_months(v_temp_first_day,1);
v_month_day:=to_char(v_temp_first_day,'yyyy-mm');
v_sql:=v_sql||',count(case when to_char(t.zs_bull_start_time,''yyyy-mm'')='''||v_month_day||''' then t.zs_bull_id end) "'||i||'"' ;
end if;
if i=v_poor_month then
v_sql:=v_sql||',count(case when t.zs_bull_start_time>=(to_date('''||v_end_month_begin_day||' 00:00:00'',''yyyy-mm-dd hh24:mi:ss'')) and t.zs_bull_start_time<=(to_date('''||in_end_day||' 23:59:59'',''yyyy-mm-dd hh24:mi:ss'')) then t.zs_bull_id end) "'||i||'"' ;
end if;
i:=i+1;
end loop;
v_sql:=v_sql||' from p_wanhua_zs_code t group by t.zs_group_id ';
DBMS_OUTPUT.PUT_LINE(v_sql);
open v_cur for v_sql;
end;

Answer 3

这是我写的存储过程，你说between and怎么能够统计出各个月的记录啊

Answer 4

不能在前台做交叉么，后台只产生基础数据。
zs_group_id，月份，count(zs_bull_id)

Answer 5

没试出来好办法。将个思路。你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;

Answer 6

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

Answer 7

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

Answer 8

还真成
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> 

Answer 9

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

Answer 10

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

Answer 11

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

Answer 12

你动态生成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;

Answer 13

传说中有个 dbms_sql  随你sql多长

Answer 14

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

Answer 15

另外 可以用 PARTITION BY month解决  你到网上搜索下 就有 这个属于高级查询  你原来的SQL 有明显的问题  
这个不能那样单纯的进行SQL 拼接 还是用Oracle的高级查询比较好

Answer 16

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

Answer 17

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