=== 过程如下

CREATE OR REPLACE procedure sp_upt_t30check(in_sWhere In Varchar2,in_sValue In Varchar2,out_rep Out SYS_REFCURSOR) Is

  l_sWhere Varchar2(2000);

  l_sValue Varchar2(2000);

  ls_gcode Varchar2(2000);

  ls_gcode_chk Varchar2(2000);

  v_gcode_code Varchar2(2000);

  v_gcode_chk Varchar2(2000);

  l_sql Varchar2(2000);

  l_sql_temp Varchar2(2000);

  l_conf_type Varchar2(20);

  lt_where ENJOY_OTHERSREP.TT_WHERE;

  i Integer :=1;

  j Integer;

  C_rep   SYS_REFCURSOR;

begin

--   SET NOCOUNT ON;

  l_sWhere := in_sWhere;

  l_sValue := in_sValue;

  -- 循环依次取where条件字段和条件值，存到自增长表：lt_where

  While instr(l_sWhere,';') > 0 Loop

     lt_where(i).F_COL := substr(l_sWhere,1,instr(l_sWhere,';')-1);

     lt_where(i).F_VAL := substr(l_sValue,1,instr(l_sValue,';')-1);

     If lt_where(i).F_COL = 'C_GCODE' Then

        ls_gcode := lt_where(i).F_VAL;

     Elsif lt_where(i).F_COL = 'C_CONF_TYPE' Then

        l_conf_type := lt_where(i).F_VAL;

     Elsif lt_where(i).F_COL = 'C_IST30' Then

        ls_gcode_chk := lt_where(i).F_VAL;

     End If;

     i := i + 1;

     -- 去掉取过的字段

     l_sWhere := substr(l_sWhere,instr(l_sWhere,';')+1);

     l_sValue := substr(l_sValue,instr(l_sValue,';')+1);

  End Loop;

  lt_where(i).F_COL := l_sWhere;

  lt_where(i).F_VAL := l_sValue;

  l_sql := 'update md_t30_list set C_IST30 = ''#ls_gcode_chk#'' where c_conf_type = '''||l_conf_type||'''';

  FOR j In 1 .. i Loop

     If lt_where(j).F_COL = 'C_STORE_AREA' And l_conf_type = '02' --区域

        Or lt_where(j).F_COL = 'C_STORE_ID' And l_conf_type In ('03','08','09','10','11') --门店

        Or lt_where(j).F_COL = 'C_SADNO' And l_conf_type In ('04','08')  --S部门

        Or lt_where(j).F_COL = 'C_SADNO' And l_conf_type In ('05','09')  --部门

        Or lt_where(j).F_COL = 'C_SADNO' And l_conf_type In ('06','10')  --部类

        Or lt_where(j).F_COL = 'C_SADNO' And l_conf_type In ('07','11') Then  --课类

        l_sWhere := l_sWhere||';'||lt_where(j).F_COL;

        l_sValue := l_sValue||';'||lt_where(j).F_VAL;

        l_sql := l_sql||' and '||lt_where(j).F_COL||' = '''||lt_where(j).F_VAL||'''';

     End If;

  End Loop;

  l_sql := l_sql ||' and c_gcode = ''#ls_gcode_code#''';

  While instr(ls_gcode,',') > 0 Loop

     v_gcode_code := substr(ls_gcode,1,instr(ls_gcode,';')-1);

     v_gcode_chk := substr(ls_gcode_chk,1,instr(ls_gcode_chk,';')-1);

     l_sql_temp := Replace(l_sql,'#ls_gcode_chk#',v_gcode_chk);

     l_sql_temp := Replace(l_sql_temp,'#ls_gcode_code#',v_gcode_code);

     Execute Immediate l_sql_temp;

     -- 去掉取过的字段

     ls_gcode := substr(ls_gcode,instr(ls_gcode,',')+1);

     ls_gcode_chk := substr(ls_gcode_chk,instr(ls_gcode_chk,',')+1);

  End Loop;

  l_sql_temp := Replace(l_sql,'#ls_gcode_chk#',ls_gcode_chk);

  l_sql_temp := Replace(l_sql_temp,'#ls_gcode_code#',ls_gcode);

  Execute Immediate l_sql_temp;

  OPEN C_rep FOR 'select 1 rtn from dual';

  out_rep := C_rep;

  Commit;

Exception When Others Then

  OPEN C_rep FOR 'select -1 rtn from dual';

  out_rep := C_rep;

end sp_upt_t30check;