=== 过程如下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;
