存储过程：定义枚举数值，判断是否存在字段名然后遍历相加，返回。

Question

存储过程有1个参数，表示表名，字符型：p_TableName。
实现要求：
过程里需要定义一个枚举类型的变量v_fileName存储字段名，这些字段内容都是数值型：如FSAL001、FSAL003、FSAL100、FSAL008、FSAL098 
result:=0; 存储枚举变量v_fileName各字段名的总值
for i=1 to size(V_fileName)
  if V_FileName(i)是入参表名p_TableName的字段  then
     result=result+sum(V_fileName(i))
  end if
end for
return result;问题：
1）在pl/sql中如何定义枚举变量，我可以在该变量中定义我需要相加的字段名
2）如何判断相应的字段名是否在某个表中存在？
请指教大家，谢谢！

Answer 1

1、可以使用记录或对象类型
2、select count(*) from user_tab_cols t where t.TABLE_NAME='大写表名' AND t.COLUMN_NAME='大写字段名';

Answer 2

回tangren，怎么用记录类型或对象类型存储？存储后怎么调用？
可以按我的需求写一个大概出来吗？

Answer 3

create or replace procedure p_sum(
                                  c_TableName varchar2,--表名参数 记得大写
                                  res out number)
as
str varchar2(10);
col varchar2(10);
num number;
s1 number;
begin
res :=0;
str:='FSAL';
for i in 1..98 loop
col:=str||i;
select count(*) into num from user_tab_cols where table_name=c_TableName and column_name=col;
if num>0 then
execute immediate 'select sum('||col||') from '||c_TableName into s1;
res:=res+s1;
end if;
end loop;
dbms_output.put_line(res);
end;

Answer 4

create or replace procedure p_sum(
                              p_TableName varchar2,--表名参数 记得大写
                                  res out number)
is
  type v_fileName_type is table of VARCHAR2(20) ;--枚举类型我用一个嵌套表来存储;
  v_fileName  v_fileName_type;
  sql_str     varchar2(4000);
  result      number :=0;  --返回的结果
  v_res       number :=0;  
  cnt         number :=0;  --判断V_FileName(i)是否是入参表名p_TableName的字段
begin
     --初始化嵌套表
     v_fileName:=v_fileName_type('FSAL001','FSAL003','FSAL100','FSAL008','FSAL098');
     for i in 1..v_fileName.count loop
           select count(*) into cnt from user_tab_cols where table_name=Upper(p_TableName) and column_name=Upper(v_fileName(i));
           if cnt>0 then
              sql_str:='select sum('||v_fileName(i)||') from '||p_TableName ;
              execute immediate sql_str into result;
              v_res:=v_res+result;
           end if;
     end loop;
     res:=v_res;  --返回的结果
exception
    when others then
        raise_application_error(-20102,'** p_sum error **' ||sqlerrm);
end;

Answer 5

--测试：我将 v_fileName中值改成 ('sal','FSAL003','FSAL100','FSAL008','FSAL098')
DECLARE
res NUMBER;
BEGIN
     p_sum('emp',res);
     dbms_output.put_line(res);
END;--结果：
    PL/SQL block, executed in 0 sec.
    51725                           
    Total execution time 0.016 sec. 

Answer 6

--008 改下create or replace procedure p_sum(
                                  c_TableName varchar2,--表名参数 记得大写
                                       res out number)
as
str varchar2(10);
col varchar2(10);
num number;
s1 number;
begin
res :=0;
str:='FSAL';
for i in 1..98 loop
select str||lapd(i,3,'0') into col from dual;---这里改下
select count(*) into num from user_tab_cols where table_name=c_TableName and column_name=col;
if num>0 then
execute immediate 'select sum('||col||') from '||c_TableName into s1;
res:=res+s1;
end if;
end loop;
dbms_output.put_line(res);
end;

Answer 7

谢谢paddy,我在点疑问：
1）入参的p_Tablename ：表名参数一定要大写吗？？我这个p_TableName入参传进来的值也是一个动态的表的。
2）for i in 1..v_fileName.count loop
1..v_filename.count一定要有两个点吗？i还用定义吗？

Answer 8

回wkc168,我那些字段名FSALXX并不固定是FSAL开头的，名称并不固定，这里只是测试举例哦，所以不能按你的方式来这样遍历的。

Answer 9

--既然名称是不定的那就用数组来create or replace procedure p_sum(
                                  c_TableName varchar2,--表名参数 记得大写
                                       res out number)
as
type str_type is table of varchar2(100);
str str_type:=str_type('FSAL001','FSAL003','FSAL100','FSAL008','FSAL098',.其他值...);
num number;
s1 number;
begin
res :=0;
for i in 1..str.count loop
select count(*) into num from user_tab_cols where table_name=c_TableName and column_name=str(i);
if num>0 then
execute immediate 'select sum('||str(i)||') from '||c_TableName into s1;
res:=res+s1;
end if;
end loop;
dbms_output.put_line(res);
end;

Answer 10

他的里面有upper  就不需要  我的没写就的大写 就需要  个中道理你自己去试试
select count(*) into num from user_tab_cols where table_name=c_TableName and column_name=str(i); 就明白

Answer 11

结贴，我以paddy做的为例已经调试成功。还是谢谢wkc168，10楼的代码也符合我的需求。同给分吧`~加油！