CREATE OR REPLACE PROCEDURE P_Utl_Split
(
? iCity???????? VARCHAR2,?????? -- 选择的地市,格式：1|深圳~2|广东~3|广州
? oCityIds?? OUT? VARCHAR2????? -- 地市id, 格式:(1,2)
)
AS
? eInPut????????? EXCEPTION;?????? --输入查询参数异常
? ERRCODE???????? NUMBER;????????? --错误码
? ERRMSG????????? VARCHAR2(200);?? --错误信息
? -- 定义游标，查询所有城市信息
? CURSOR C_City IS
? SELECT ID,NAME FROM city;
? v_CityId???????? NUMBER(4);????????? -- 临时保存地市ID
? v_CityName?????? VARCHAR2(100);????? -- 临时保存地市名称
? v_tmp_CityID???? VARCHAR2(100);????? -- 中间变量，保存拼接的地市ID
? v_City? VARCHAR2(2000);
? v_pos1? INT;
? v_pos2? INT;
? v_time? INT;
? v_char? VARCHAR2(100);
? v_loc1? INT;
? v_loc2? INT;
BEGIN
? -- 空处理
? IF (iCity IS NULL) THEN
??? RAISE eInPut;
? END IF;

? -- 选择了所有
? IF iCity = '0|所有' THEN
??? OPEN C_City;
??? LOOP
????? FETCH C_City INTO
????? v_CityId,v_CityName;
????? EXIT WHEN C_City%NOTFOUND;
????? v_tmp_CityID:=v_tmp_CityID||v_CityId||','; -- 拼接地市ID：1,2,
??? END LOOP;
??? v_tmp_CityID:=substr(v_tmp_CityID,1,length(v_tmp_CityID)-1); -- 截掉最后一个逗号
??? oCityIds:='('||v_tmp_CityID||')'; -- 拼装最后结果
? -- 需要拆分
? ELSE
??? -- 初始化变量
??? v_City := iCity;
??? v_City := '~' || v_City || '~';
??? v_time := 1;
??? v_pos1 := 1;
??? v_pos2 := 1;

??? WHILE v_pos2 <> 0? LOOP--拆分组
????? v_pos1 := v_pos2;
????? v_pos2 := INSTR(v_City, '~', v_time + 1, 1);

????? IF v_pos2 - v_pos1 - 1 > 0 THEN
??????? v_char := SUBSTR(v_City, v_pos1+1, v_pos2-v_pos1-1);
??????? v_loc1 := INSTR(v_char,'|',1);
??????? v_loc2 := INSTR(v_char || '|','|',v_loc1+1);

??????? v_CityId:=SUBSTR(v_char,1,v_loc1 - 1);
??????? v_CityName:=SUBSTR(v_char,v_loc1+1,v_loc2-v_loc1 -1);
??????? v_tmp_CityID:=v_tmp_CityID||v_CityId||','; -- 拼接地市ID：1,2,
????? END IF;
????? v_time := v_pos2 + 1;
??? END LOOP;
??? v_tmp_CityID:=substr(v_tmp_CityID,1,length(v_tmp_CityID)-1); -- 截掉最后一个逗号
??? oCityIds:='('||v_tmp_CityID||')'; -- 拼装最后结果
? END IF;

EXCEPTION
? WHEN eInPut THEN
??? INSERT INTO t_ErrLog(LogDate,LogID,ExceptionObjName,ErrorCode,ErrorMsg)
???????????????????????? VALUES(SYSDATE,5,'P_Utl_Split',006,'参数输入错误');
?commit;
? WHEN OTHERS THEN
??? ROLLBACK;
??? ERRCODE := SQLCODE;
??? ERRMSG := SUBSTR(SQLERRM,1,200);
??? INSERT INTO t_ErrLog(LogDate,LogID,ExceptionObjName,ErrorCode,ErrorMsg)
???????????????????????? VALUES(SYSDATE,2,'P_Utl_Split',ERRCODE,ERRMSG);
??? COMMIT;
END P_Utl_Split;