如何优化动态sql?各位高手请指点！！！

Question

头都大了，也没想出办法，请各位高手多多指点啊！代码如下：
例如程序如下：
create or replace procedure  get_result(
                                                cm_id            in         number,
                                                cm_name      in         varchar2(16),
                                                cm_age         in         smallint,
                                                cm_room_num  in      smallint,
                                                cm_money     in         number,
                                                tmp_cursor        out       tmptype.t_cursor)
      return  number as
                           tmp_cm_name              varchar2(16);
                           tmp_cm_id                     number;
                           tmp_cm_age                  smallint;
                           tmp_cm_room_num        smallint;
                           tmp_cm_money              number;
                           tmp_sql                          char(4000);
                           tmp_string                      varchar2(255);
       begin
                           tmp_cm_name         :=    ' ';             
                           tmp_cm_id               :=  0;       
                           tmp_cm_age            := 0;
                           tmp_cm_room_num  := 0;
                           tmp_cm_money        := 0;
                           tmp_sql                     := ' ';
                           tmp_string                 := ' ';
                          
                       tmp_string := cm_id || cm_name || cm_age || cm_room_num
                                            || cm_money;
                       if  tmp_string <> ' '  then
                           tmp_sql := tmp_sql || 'select  count(*) ' || tmp_string ;
                       else  
                           tmp_sql := tmp_sql || ' select  count(*)';
                       end if;
                      
                        tmp_sql := tmp_sql || 'sun(cm_money) ' ;
                        tmp_sql := tmp_sql || ' from money_table a, member_info b
                                                       where  a.member_id = b.member_id ';                        if cm_id <> 0 then
                           tmp_sql := tmp_sql || ' and a.member_id = cm_id ';
                        end if;
                        if cm_age <> 0 then
                           tmp_sql := tmp_sql || 'and a.member_age = cm_age ';
                         end if;
                        if cm_room_num <> 0 then
                           tmp_sql := tmp_sql || ' and a.member_room_num =  
                                                             cm_room_num';
                         end if;
                         if trim(tmp_string) is not null then
                            tmp_sql:= tmp_sql|| '  group by  ' || tmp_string|| '  
                                                             order  by   ' || tmp_string;
                         end if;         open tmp_cursor  for  tmp_sql;
                           return(0);
                           return(SQLCODE);
         end get_result;

Answer 1

try:
...
begin
                           tmp_cm_name         :=    ' ';             
                           tmp_cm_id               :=  0;       
                           tmp_cm_age            := 0;
                           tmp_cm_room_num  := 0;
                           tmp_cm_money        := 0;
                           tmp_sql                     := ' ';
                           tmp_string                 := ' ';
  tmp_string := cm_id || cm_name || cm_age || cm_room_num|| cm_money;
tmp_sql:='select  count(*) '||decode(tmp_string,'','',tmp_string)||'sun(cm_money) ' ||
' from money_table a, member_info b where  a.member_id = b.member_id '||
decode(cm_id,0,'',' and a.member_id = cm_id ')||
decode(cm_age,0,'','and a.member_age = cm_age ')||
decode(cm_room_num ,0,'','and a.member_room_num = cm_room_num ')||
decode(trim(tmp_string),null,'','  group by  ' || tmp_string|| 
' order  by  ' || tmp_string;                        open tmp_cursor  for  tmp_sql;
                           return(0);
                           return(SQLCODE);
end get_result;

Answer 2

我这个小脚本想实现的功能是根据输入不同的条件，汇总出工资表，如果输入条件如职员id、职员年龄age、职员室号room_num不为空的话，那么就根据相应条件查询汇总，如果为空则全选，两个表money_table、member_info的主键都是member_id。这样使用动态sql好像效率蛮低的，我又想不出其他办法，楼上大哥的办法我也试了一下，但是感觉不到差别啊。