请教大家一个问题:
在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的值,因为一运行到这一步就错了,错误提示信息:数字或值错误。
在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的值,因为一运行到这一步就错了,错误提示信息:数字或值错误。
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 过程已成功完成。
(
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;
而且这个看着是没有问题,一调试问题就出来了