山寨机中的战斗机！	程序优化工程师到底对IT界有没有贡献

oracle存储过程中的动态sql

楼主SkyOfCloud（天边的云）2005-09-20 11:25:54 在 Oracle / 开发提问

请教大家一个问题：
在pl/sql中，oracle9i：
在存储过程中：

下边这个sql是好的，能看到strSql的值
strSql := ' select '''||FEquTypeName||''' DeviceName,q1.rongliang1 rongliang1,q1.shuliang1 shuliang1,round((q1.shuliang1/' ||iAllCount|| ')*100,2) per1,q1.len1 len1 from ( '|| ' select CAPABILITY rongliang1,count(CABLE_ID) shuliang1,sum(CABLE_LENGTH) len1 from T_CABLE_INF group by CAPABILITY'||' ) q1 ';
如果改成这样：
tmpSql1 := ' select CAPABILITY rongliang1,count(CABLE_ID) shuliang1,sum(CABLE_LENGTH) len1 from T_CABLE_INF group by CAPABILITY';
strSql := ' select '''||FEquTypeName||''' DeviceName,q1.rongliang1 rongliang1,q1.shuliang1 shuliang1,round((q1.shuliang1/' ||iAllCount|| ')*100,2) per1,q1.len1 len1 from ( '|| tmpSql1 ||' ) q1 ';
就是错的，看不到strSql的值，因为一运行到这一步就错了，错误提示信息：数字或值错误。

问题点数：100、回复次数：13Top

1 楼SkyOfCloud（天边的云）回复于 2005-09-20 11:26:32 得分 0

求求各位大虾帮帮忙，很急的。Top

2 楼zergtao（taotao）回复于 2005-09-20 11:38:12 得分 5

上下两个字符串print出来看一下有没有什么不同的地方。Top

3 楼SkyOfCloud（天边的云）回复于 2005-09-20 11:39:34 得分 0

下边的字符串不能print出来，因为运行到拼接的时候就出错Top

4 楼bobfang（匆匆过客）回复于 2005-09-20 11:51:06 得分 10

是否其他问题引起的，按你写的再加上变量的定义，测试没有错。

SQL> declare
2 tmpSql1 varchar2(500);
3 strSql varchar2(1000);
4 FEquTypeName varchar2(10):=NULL;
5 iAllCount number:=NULL;
6 begin
7 tmpSql1 := ' select CAPABILITY rongliang1,count(CABLE_ID) shuliang1,sum(CABLE_LENGTH) len1 from T_CABLE_INF group by CAPABILITY';
8 strSql := ' select '''||FEquTypeName||''' DeviceName,q1.rongliang1 rongliang1,q1.shuliang1 shuliang1,round((q1.shuliang1/' ||iAllCount|| ')*100,2) per1,q1.len1 len1 from ( '|| tmpSql1 ||' ) q1 ';
9 end;
10 /

PL/SQL 过程已成功完成。Top

5 楼SkyOfCloud（天边的云）回复于 2005-09-20 12:07:37 得分 0

编译是没有错，但是调试有错Top

6 楼SkyOfCloud（天边的云）回复于 2005-09-20 12:08:49 得分 0

单步调式存储过程的时候，在这一句出错Top

7 楼SkyOfCloud（天边的云）回复于 2005-09-20 13:02:22 得分 0

怎么大虾都不见了？Top

8 楼waterfirer（水清）回复于 2005-09-20 13:37:50 得分 5

你把存储过程贴出来吧，光看这个拼串没有问题，可能是别的地方的问题Top

9 楼SkyOfCloud（天边的云）回复于 2005-09-20 13:40:16 得分 0

我单步跟踪了，别的地方都是正确的Top

10 楼SkyOfCloud（天边的云）回复于 2005-09-20 13:45:27 得分 0

存储过程：

create or replace procedure P_CAPABILITY_STAT
(

FType g_Ddf_Info.DDF_ID%type, --设备类型编号
FName G_DDF_INFO.DDF_NAME%type, --设备名称
FSelType g_Ddf_Info.DDF_ID%type, --查询类型
FUnitLevel g_Ddf_Info.DDF_ID%type, --机构级别
FEquTypeName G_DDF_INFO.DDF_NAME%type, --设备类型名称
FUnitID G_DDF_INFO.DDF_NAME%type, --机构ID
p_Cur out route_output.T_Cur--返回结果集
)

is

strSql varchar2(1000);
tmpSql1 varchar2(1000);
tmpSql2 varchar2(500);
tmpSql3 varchar2(500);
tmpStr varchar2(500);
IALLCOUNT number;--总数量
iAllLen number;--总长度
v_tableName varchar(20);
v_count_field varchar(20);
v_len_field varchar(20);
v_Name varchar(20);
v_field1 varchar2(20);
v_field2 varchar(20);
cursor1 integer;
rows_processed integer;
begin

tmpSql1 := ' select TREENODEID from T_TREENODEINFO where nodestyle=0 and ' ;
if FUnitLevel = 0 then
--省公司
begin

tmpSql1 := ' in ( select UNIT_ID from T_UNIT_INF where UNIT_LEVEL=4 and UNIT_ID in ( ' || tmpSql1 --4
||' PRENODEID in ( '|| tmpSql1 --3
||' PRENODEID in ( ' || tmpSql1 --2
|| ' PRENODEID '; --1

tmpSql3 := ')))) ' ;
end;
elsif FUnitLevel = 1 then --地市局

tmpSql1 := ' in ( select UNIT_ID from T_UNIT_INF where UNIT_LEVEL=4 and UNIT_ID '
||' in ( ' || tmpSql1 --3
||' PRENODEID in ( ' || tmpSql1 || ' PRENODEID ';--2
tmpSql3 := '))) ' ;

elsif FUnitLevel = 2 then --分局

tmpSql1 := ' in ( select UNIT_ID from T_UNIT_INF where UNIT_LEVEL=4 and UNIT_ID '
||' in ( ' || tmpSql1 --3
||' PRENODEID ';
tmpSql3 := ')) ' ;

elsif FUnitLevel = 3 then--支局

tmpSql1 := ' in ( select UNIT_ID from T_UNIT_INF where UNIT_LEVEL=4 and UNIT_ID '
||' in ( ' || tmpSql1 --3
||' PRENODEID ';--2
tmpSql3 := ') ' ;

end if;

if FType = 0 then

v_tableName := 'T_CABLE_INF';
v_count_field := 'CABLE_ID';
v_len_field := 'CABLE_LENGTH';
v_Name := ' CABLE_Name ';
v_field1 := ',q1.len1 len1';
v_field2 := ','||to_char(iAllLen)||' allLen';

end if;

tmpSql1 := ' from '||v_tableName||' where NODE_ID '||tmpSql1||'='||FUnitID||tmpSql3;

if FName is not null then
tmpStr := '%'||FName||'%';
tmpSql2 := ' and ' || v_Name ;
if FSelType = 0 then
tmpSql2 := tmpSql2 || ' like '''|| tmpStr||'''';

elsif FSelType = 1 then
tmpSql2 := tmpSql2 || ' = '''||FName||'''';
end if;
end if;

tmpSql1 := tmpSql1||tmpSql2;
--总数量
tmpSql2 := 'select count(CABLE_ID) '||tmpSql1;
cursor1 := dbms_sql.open_cursor; --创建游标
dbms_sql.parse(cursor1,tmpSql2, dbms_sql.native);
dbms_sql.define_column(cursor1, 1, iAllCount);
rows_processed := dbms_sql.execute(cursor1);
loop
if dbms_sql.fetch_rows(cursor1) > 0 then
begin
dbms_sql.column_value(cursor1, 1, iAllCount);
end;
else
exit;
end if;
end loop;
dbms_sql.close_cursor(cursor1);

--总长度
tmpSql2 := 'select sum(CABLE_LENGTH) '||tmpSql1;

cursor1 := dbms_sql.open_cursor; --创建游标
dbms_sql.parse(cursor1,tmpSql2, dbms_sql.native);
dbms_sql.define_column(cursor1, 1, iAllLen);
rows_processed := dbms_sql.execute(cursor1);
loop
if dbms_sql.fetch_rows(cursor1) > 0 then
begin
dbms_sql.column_value(cursor1, 1, iAllLen);
end;
else
exit;
end if;
end loop;
dbms_sql.close_cursor(cursor1);

tmpSql1 := tmpSql1 || ' group by CAPABILITY ';
tmpSql2 := 'select CAPABILITY rongliang1,count(CABLE_ID) shuliang1,sum(CABLE_LENGTH) len1 '|| tmpSql1;
strSql := ' select '''||FEquTypeName||''' DeviceName,q1.rongliang1 rongliang1,q1.shuliang1 shuliang1,round((q1.shuliang1/' ||iAllCount|| ')*100,2) per1,q1.len1 len1 from ( ';
strSql := strSql ||tmpSql2;
strSql := strSql ||' ) q1 ';
--OPEN p_cur FOR strSql ;
end P_CAPABILITY_STAT;

而且这个看着是没有问题，一调试问题就出来了Top

11 楼waterfirer（水清）回复于 2005-09-20 13:53:10 得分 50

是不是strSql长度超了？Top

12 楼nicolas1999king（恋伶）回复于 2005-09-20 14:13:49 得分 30

不够长Top

13 楼SkyOfCloud（天边的云）回复于 2005-09-20 15:40:44 得分 0

对了，楼上两个说的正确。终于解决了，谢谢大家！！Top