语句如下,其中rs.FHISTABLENAME是外围循环得出的一个动态数据表,在这个表中要查出一些字段,有些字段存在,有些字段不存在。我要求不存在的字段也把列出来,只不过内容是0.
问题出现在第二段sql语句中的某语句有问题所以调试错误,无法执行成功:
' ,case get_field_IsExist('''||rs.FHISTABLENAME||''','''||tmpFld||''') when 0 then 0 else '||tmpFld||' end FtestFld'||
在这句中,我要判断rs.FHISTABLENAME这个动态表里是否存在tmpFld变量所指的字段,如果不存在,则显示0,如果存在,则显示tmpFld变量字段里的内容。存不存在该字段,都要把这个字段显示出来。
在调试中发现当tmpFld值的字段(FSAl209)存在于rs.FHISTABLENAME这个动态表时,则执行没有问题;但如果不存在这个字段(FSAl209)则执行就会出错,会提示说字段名不存在。难不成case when语句每个分支都判断不成?所以希望各位给点意见,谢谢!
create or replace function get_field_IsExist(v_tableName in varchar2,v_colName in varchar2) return number
--获取字段是否存在
is cnt number:=0; --判断字段是否存在,存在则cnt值大于0,否则为0.begin
select count(*) into cnt from user_tab_cols where table_name=upper(v_tableName) and column_name=Upper(v_colName);
return cnt;
exception when others then
raise_application_error('-20001','Error:get_field_IsExist!');
end;
--在存储过程中调用之
tmpFld:='FSAl209';
sql_str:='select cmpTempTB.FPERSONID,person.FNumber,person.FName_L2 FempName,org.Fnumber FempOrgNum,org.fdisplayname_l2 FempOrgName,Position.Fnumber FPstNum,Position.FName_L2 FPstName,'||
' CmpPeriod.FNumber FcmpPrdNum,subStr(CmpPeriod.FNumber,0,4) FcmpPrdYear,subStr(CmpPeriod.FNumber,5,6) FcmpPrdMonth,empClassify.Fnumber FempClsNum,empClassify.Fname_L2 FempClsName,'||
' PositionType.FNumber FPstTypeNum,PositionType.FName_L2 FPstTypeName'||
' ,case get_field_IsExist('''||rs.FHISTABLENAME||''','''||tmpFld||''') when 0 then 0 else '||tmpFld||' end FtestFld'||
' from '||rs.FHISTABLENAME||' cmpTempTB'||
' left join T_BD_person person on cmpTempTB.FPERSONID=person.Fid'||
' left join T_ORG_BaseUnit org On cmpTempTB.FADMINORGUNITID=org.FID'||
' left join T_ORG_Position Position On cmpTempTB.FPOSITIONID=Position.FID '||
' left join T_HR_CMPPERIOD CmpPeriod on cmpTempTB.FPeriod=CmpPeriod.FID'||
' left join T_HR_EmployeeClassify empClassify on person.FEmployeeClassifyID=empClassify.Fid'||
' left join T_BD_PositionType PositionType on Position.FPositionTypeID=PositionType.FID'||
' where to_number(subStr(CmpPeriod.FNumber,0,4))='||baseYear||' or to_number(subStr(CmpPeriod.FNumber,0,4))='||cmpYear;
问题出现在第二段sql语句中的某语句有问题所以调试错误,无法执行成功:
' ,case get_field_IsExist('''||rs.FHISTABLENAME||''','''||tmpFld||''') when 0 then 0 else '||tmpFld||' end FtestFld'||
在这句中,我要判断rs.FHISTABLENAME这个动态表里是否存在tmpFld变量所指的字段,如果不存在,则显示0,如果存在,则显示tmpFld变量字段里的内容。存不存在该字段,都要把这个字段显示出来。
在调试中发现当tmpFld值的字段(FSAl209)存在于rs.FHISTABLENAME这个动态表时,则执行没有问题;但如果不存在这个字段(FSAl209)则执行就会出错,会提示说字段名不存在。难不成case when语句每个分支都判断不成?所以希望各位给点意见,谢谢!
create or replace function get_field_IsExist(v_tableName in varchar2,v_colName in varchar2) return number
--获取字段是否存在
is cnt number:=0; --判断字段是否存在,存在则cnt值大于0,否则为0.begin
select count(*) into cnt from user_tab_cols where table_name=upper(v_tableName) and column_name=Upper(v_colName);
return cnt;
exception when others then
raise_application_error('-20001','Error:get_field_IsExist!');
end;
--在存储过程中调用之
tmpFld:='FSAl209';
sql_str:='select cmpTempTB.FPERSONID,person.FNumber,person.FName_L2 FempName,org.Fnumber FempOrgNum,org.fdisplayname_l2 FempOrgName,Position.Fnumber FPstNum,Position.FName_L2 FPstName,'||
' CmpPeriod.FNumber FcmpPrdNum,subStr(CmpPeriod.FNumber,0,4) FcmpPrdYear,subStr(CmpPeriod.FNumber,5,6) FcmpPrdMonth,empClassify.Fnumber FempClsNum,empClassify.Fname_L2 FempClsName,'||
' PositionType.FNumber FPstTypeNum,PositionType.FName_L2 FPstTypeName'||
' ,case get_field_IsExist('''||rs.FHISTABLENAME||''','''||tmpFld||''') when 0 then 0 else '||tmpFld||' end FtestFld'||
' from '||rs.FHISTABLENAME||' cmpTempTB'||
' left join T_BD_person person on cmpTempTB.FPERSONID=person.Fid'||
' left join T_ORG_BaseUnit org On cmpTempTB.FADMINORGUNITID=org.FID'||
' left join T_ORG_Position Position On cmpTempTB.FPOSITIONID=Position.FID '||
' left join T_HR_CMPPERIOD CmpPeriod on cmpTempTB.FPeriod=CmpPeriod.FID'||
' left join T_HR_EmployeeClassify empClassify on person.FEmployeeClassifyID=empClassify.Fid'||
' left join T_BD_PositionType PositionType on Position.FPositionTypeID=PositionType.FID'||
' where to_number(subStr(CmpPeriod.FNumber,0,4))='||baseYear||' or to_number(subStr(CmpPeriod.FNumber,0,4))='||cmpYear;
baseYear varchar2(4) := '2011';
cmpYear varchar2(4) := '2010';
fHisTableName varchar2(20) := '';
--变量
cnt int;
countValue varchar2(10) := ''; tmpFld varchar2(10) := '';
sql_str varchar2(10000) := '';begin
--查询字段是否存在
--tmpFld := 'FSAl209';
/*
此处存在一个问题 FSAl209字段如果类型为字符型 类型不符
--测试
select case 1
when 0 then
0
else
'abc'
end FtestFld
from dual;
--提示错误:数据类型不一致
*/
--我用的是自己的数据库中的表进行测试的
fHisTableName := 'hi_org_info';
tmpFld := 'orgCode';
sql_str := 'select count(1) from user_tab_cols where table_name = upper(''' ||
fHisTableName || ''') and column_name = Upper(''' ||
tmpFld || ''')';
execute immediate sql_str
into cnt; if (cnt = 0) then
countValue := '0';
else
countValue := tmpFld;
end if;
dbms_output.put_line('表中字段是否存在的查询结果:' || countValue);
--拼串
sql_str := 'select cmpTempTB.FPERSONID,person.FNumber,person.FName_L2 FempName,org.Fnumber FempOrgNum,org.fdisplayname_l2 FempOrgName,Position.Fnumber FPstNum,Position.FName_L2 FPstName,' ||
' CmpPeriod.FNumber FcmpPrdNum,subStr(CmpPeriod.FNumber,0,4) FcmpPrdYear,subStr(CmpPeriod.FNumber,5,6) FcmpPrdMonth,empClassify.Fnumber FempClsNum,empClassify.Fname_L2 FempClsName,' ||
' PositionType.FNumber FPstTypeNum,PositionType.FName_L2 FPstTypeName' || ' ,' ||
countValue || ' as FtestFld' || ' from ' || fHisTableName ||
' cmpTempTB' ||
' left join T_BD_person person on cmpTempTB.FPERSONID=person.Fid' ||
' left join T_ORG_BaseUnit org On cmpTempTB.FADMINORGUNITID=org.FID' ||
' left join T_ORG_Position Position On cmpTempTB.FPOSITIONID=Position.FID ' ||
' left join T_HR_CMPPERIOD CmpPeriod on cmpTempTB.FPeriod=CmpPeriod.FID' ||
' left join T_HR_EmployeeClassify empClassify on person.FEmployeeClassifyID=empClassify.Fid' ||
' left join T_BD_PositionType PositionType on Position.FPositionTypeID=PositionType.FID' ||
' where to_number(subStr(CmpPeriod.FNumber,0,4))=' || baseYear ||
' or to_number(subStr(CmpPeriod.FNumber,0,4))=' || cmpYear;
dbms_output.put_line(sql_str);
end;
改为
when 0 then ''0'' else '''||tmpFld||''' end FtestFld'||