如下:为了统计点数据,想省点事,用动态语句执行,但是生成的变量在pl/sql工具下总是提示
(long value) 问题,stirng类型的最大长度为32767,但是我的变量并没有这么大,
vInsSql 再最后调试时没有值,我剩下少点就可以,如何解决这个问题!
create or replace procedure test(
P_Ny in string,ASqlStr in string,IsDl in boolean default false,
aDwJb in string default '1') is
vErr string(2000);
vDlFbTable string(100);
vDelSql string(1000);
vInsSql varchar2(32767);
vInsSql1 string(32767);
begin
---------------------------------
--功能:实现岗位警力的统计
--支队统计条件为:substr(dwdm,1,2)分组,执勤队按dwdm分组,警区按 jqdm分组
--上面这点是经过验证的
--p_Ny 传入的往月参数,为空时为当月数据
--ASqlStr 传入的where 条件
--IsDl 是否需要关联道路附表
--aDwJb:统计类别 ='1' 支队 = '2' 执勤队 '3' 警区
--created by lxj 2005-08-11 0:36
---------------------------------
if IsDl then
vDlFbTable := ',dl_f';
else
vDlFbTable := '';
end if;
vDelSql :='DELETE FROM REP_GWJLACC';
if aDwJb = '1' then
vInsSql :='insert into REP_GWJLACC select a.qydm dw,' ||
'count(decode(a.gwxzdm,''21'',1,null)) llxgw,' ||
'sum(decode(a.gwxzdm,''21'',zqrs,0)) llxjl,' ||
',count(decode(a.gwxzdm,''23'',1,null)) qcxgw,' ||
'sum(decode(a.gwxzdm,''23'',zqrs,0)) qcxjl,' ||
'count(decode(a.gwxzdm,''24'',1,null)) qzxgw,' ||
'sum(decode(a.gwxzdm,''24'',zqrs,0)) qzxjl,' ||
'count(decode(a.gwxzdm,''25'',1,null)) jdzdxgw,' ||
'sum(decode(a.gwxzdm,''25'',zqrs,0)) jdzdxjl,' ||
'count(decode(a.gwxzdm,''21'',1,''22'',1,''23'',1,''24'',1,''25'',1,null)) xjgw,' ||
'sum(decode(a.gwxzdm,''21'',zqrs,''22'',zqrs,''23'',zqrs,''24'',zqrs,''25'',zqrs,0)) xjjl,' ||
'count(decode(a.gwxzdm,''31'',1,null)) hsggw,' ||
'sum(decode(a.gwxzdm,''31'',zqrs,0)) hsgjl,' ||
'count(decode(a.gwxzdm,''32'',1,null)) kkggw,' ||
'sum(decode(a.gwxzdm,''32'',zqrs,0)) kkgjl,' ||
'count(decode(a.gwxzdm,''33'',1,null)) sdggw,' ||
'sum(decode(a.gwxzdm,''33'',zqrs,0)) sdgjl,' ||
'count(decode(a.gwxzdm,''34'',1,null)) ycggw,' ||
'sum(decode(a.gwxzdm,''23'',zqrs,0)) ycgjl,' ||
'count(decode(a.gwxzdm,''31'',1,''32'',1,''33'',1,''34'',1,null)) xj2gw,' ||
'sum(decode(a.gwxzdm,''31'',zqrs,''32'',zqrs,''33'',zqrs,''34'',zqrs,0)) xj2jl,' ||
'count(decode(a.gwxzdm,''21'',1,''22'',1,''23'',1,''24'',1,''25'',1,''31'',1,''32'',1,''33'',1,''34'',1,null)) hjgw,' ||
'sum(decode(a.gwxzdm,''21'',zqrs,''22'',zqrs,''23'',zqrs,''24'',zqrs,''25'',zqrs,''31'',zqrs,''32'',zqrs,''33'',zqrs,''34'',zqrs,0)) hjjl,' ||
' ''1'' title,''bz'' bz ,''1'' id' ||
'from gw_t a ' || vDlFbTable || ' where 1=1 ' || ASqlStr ||
' group by a.qydm' ;
end if;
vInsSql1 := Vinssql;
EXECUTE IMMEDIATE vDelSql;
EXECUTE IMMEDIATE vInsSql;
commit;
exception
when others then
begin
vErr := sqlerrm;
--insert into LogQw values(sysdate,vErr);
--commit;
end;
rollback;
end test;
(long value) 问题,stirng类型的最大长度为32767,但是我的变量并没有这么大,
vInsSql 再最后调试时没有值,我剩下少点就可以,如何解决这个问题!
create or replace procedure test(
P_Ny in string,ASqlStr in string,IsDl in boolean default false,
aDwJb in string default '1') is
vErr string(2000);
vDlFbTable string(100);
vDelSql string(1000);
vInsSql varchar2(32767);
vInsSql1 string(32767);
begin
---------------------------------
--功能:实现岗位警力的统计
--支队统计条件为:substr(dwdm,1,2)分组,执勤队按dwdm分组,警区按 jqdm分组
--上面这点是经过验证的
--p_Ny 传入的往月参数,为空时为当月数据
--ASqlStr 传入的where 条件
--IsDl 是否需要关联道路附表
--aDwJb:统计类别 ='1' 支队 = '2' 执勤队 '3' 警区
--created by lxj 2005-08-11 0:36
---------------------------------
if IsDl then
vDlFbTable := ',dl_f';
else
vDlFbTable := '';
end if;
vDelSql :='DELETE FROM REP_GWJLACC';
if aDwJb = '1' then
vInsSql :='insert into REP_GWJLACC select a.qydm dw,' ||
'count(decode(a.gwxzdm,''21'',1,null)) llxgw,' ||
'sum(decode(a.gwxzdm,''21'',zqrs,0)) llxjl,' ||
',count(decode(a.gwxzdm,''23'',1,null)) qcxgw,' ||
'sum(decode(a.gwxzdm,''23'',zqrs,0)) qcxjl,' ||
'count(decode(a.gwxzdm,''24'',1,null)) qzxgw,' ||
'sum(decode(a.gwxzdm,''24'',zqrs,0)) qzxjl,' ||
'count(decode(a.gwxzdm,''25'',1,null)) jdzdxgw,' ||
'sum(decode(a.gwxzdm,''25'',zqrs,0)) jdzdxjl,' ||
'count(decode(a.gwxzdm,''21'',1,''22'',1,''23'',1,''24'',1,''25'',1,null)) xjgw,' ||
'sum(decode(a.gwxzdm,''21'',zqrs,''22'',zqrs,''23'',zqrs,''24'',zqrs,''25'',zqrs,0)) xjjl,' ||
'count(decode(a.gwxzdm,''31'',1,null)) hsggw,' ||
'sum(decode(a.gwxzdm,''31'',zqrs,0)) hsgjl,' ||
'count(decode(a.gwxzdm,''32'',1,null)) kkggw,' ||
'sum(decode(a.gwxzdm,''32'',zqrs,0)) kkgjl,' ||
'count(decode(a.gwxzdm,''33'',1,null)) sdggw,' ||
'sum(decode(a.gwxzdm,''33'',zqrs,0)) sdgjl,' ||
'count(decode(a.gwxzdm,''34'',1,null)) ycggw,' ||
'sum(decode(a.gwxzdm,''23'',zqrs,0)) ycgjl,' ||
'count(decode(a.gwxzdm,''31'',1,''32'',1,''33'',1,''34'',1,null)) xj2gw,' ||
'sum(decode(a.gwxzdm,''31'',zqrs,''32'',zqrs,''33'',zqrs,''34'',zqrs,0)) xj2jl,' ||
'count(decode(a.gwxzdm,''21'',1,''22'',1,''23'',1,''24'',1,''25'',1,''31'',1,''32'',1,''33'',1,''34'',1,null)) hjgw,' ||
'sum(decode(a.gwxzdm,''21'',zqrs,''22'',zqrs,''23'',zqrs,''24'',zqrs,''25'',zqrs,''31'',zqrs,''32'',zqrs,''33'',zqrs,''34'',zqrs,0)) hjjl,' ||
' ''1'' title,''bz'' bz ,''1'' id' ||
'from gw_t a ' || vDlFbTable || ' where 1=1 ' || ASqlStr ||
' group by a.qydm' ;
end if;
vInsSql1 := Vinssql;
EXECUTE IMMEDIATE vDelSql;
EXECUTE IMMEDIATE vInsSql;
commit;
exception
when others then
begin
vErr := sqlerrm;
--insert into LogQw values(sysdate,vErr);
--commit;
end;
rollback;
end test;
你好,我还是不大明白,以后怎么办,字符串是可以插入到临死表,也可以取出,取出后怎么办,能否给段小代码,不胜感激!