declare V_STR VARCHAR2(4000):=26,28,29,85;
V_SQL VARCHAR2(4000);
V_DMSM VARCHAR2(4000);
V_NUM_I NUMBER:=0;
TYPE CUR_TYPE IS REF CURSOR;
CUR CUR_TYPE;
REC UD_USER%ROWTYPE;
BEGIN
WHILE TRUE LOOP
V_NUM_I :=INSTR(V_STR,',');
IF (V_NUM_I<=0 ) OR V_NUM_I IS NULL THEN
IF V_STR IS NOT NULL THEN
V_SQL:=V_SQL||','''||V_STR||'''';
END IF;
EXIT;
ELSIF V_NUM_I=1 THEN
V_NUM_I :=1;
ELSE
V_SQL:=V_SQL||','''||SUBSTR(V_STR,1,V_NUM_I-1)||'''';
IF V_NUM_I=LENGTH(V_STR) THEN
EXIT;
END IF;
END IF;
V_STR :=SUBSTR(V_STR,V_NUM_I+1);
END LOOP;
IF V_SQL IS NOT NULL THEN
V_SQL:=SUBSTR(V_SQL,2);
OPEN CUR FOR 'SELECT A.XM FROM UD_USER A WHERE A.YHZH IN ('||V_SQL||')';
LOOP
FETCH CUR INTO REC.XM;
EXIT WHEN CUR%NOTFOUND;
V_DMSM:=V_DMSM||','||REC.XM;
END LOOP;
END IF;
dbms_output.put_line(V_DMSM);
end;
V_SQL VARCHAR2(4000);
V_DMSM VARCHAR2(4000);
V_NUM_I NUMBER:=0;
TYPE CUR_TYPE IS REF CURSOR;
CUR CUR_TYPE;
REC UD_USER%ROWTYPE;
BEGIN
WHILE TRUE LOOP
V_NUM_I :=INSTR(V_STR,',');
IF (V_NUM_I<=0 ) OR V_NUM_I IS NULL THEN
IF V_STR IS NOT NULL THEN
V_SQL:=V_SQL||','''||V_STR||'''';
END IF;
EXIT;
ELSIF V_NUM_I=1 THEN
V_NUM_I :=1;
ELSE
V_SQL:=V_SQL||','''||SUBSTR(V_STR,1,V_NUM_I-1)||'''';
IF V_NUM_I=LENGTH(V_STR) THEN
EXIT;
END IF;
END IF;
V_STR :=SUBSTR(V_STR,V_NUM_I+1);
END LOOP;
IF V_SQL IS NOT NULL THEN
V_SQL:=SUBSTR(V_SQL,2);
OPEN CUR FOR 'SELECT A.XM FROM UD_USER A WHERE A.YHZH IN ('||V_SQL||')';
LOOP
FETCH CUR INTO REC.XM;
EXIT WHEN CUR%NOTFOUND;
V_DMSM:=V_DMSM||','||REC.XM;
END LOOP;
END IF;
dbms_output.put_line(V_DMSM);
end;