3,491
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE T97
(
ID NUMBER(4),
MyName VARCHAR2(20)
);
INSERT INTO T97 VALUES(1, 'A');
INSERT INTO T97 VALUES(2, 'B');
INSERT INTO T97 VALUES(3, 'C');
INSERT INTO T97 VALUES(4, 'D');
INSERT INTO T97 VALUES(5, 'E');
INSERT INTO T97 VALUES(6, 'F');
CREATE OR REPLACE FUNCTION FunctionT97(strIDS VARCHAR2)
RETURN VARCHAR2
IS
-- 定义字符串数组类型
TYPE Varchar2Array IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
TmpeStr VARCHAR2(32);
Str VARCHAR2(4000);
j INTEGER;
-- 定义数组
Arr Varchar2Array;
-- 保存返回的最终结果
strResult VARCHAR2(200) := '';
vName VARCHAR2(20);
BEGIN
-- 将传递进来的用,分隔的字符串转换为数组
Str := strIDS;
j := 0;
IF Instr(strIDS, ',', 1, 1) = 0 THEN
Arr(j) := strIDS;
j := j + 1;
else
While Instr(str, ',', 1, 1) > 0 Loop
TmpeStr := Substr(str, 1, Instr(str, ',', 1, 1) - 1);
Arr(j) := TmpeStr;
str := SubStr(Str, Instr(str, ',', 1, 1) + 1, length(str));
j := j + 1;
end loop;
if not str is null then
--将最后一个保存
Arr(j) := str;
j := j + 1;
end if;
end if;
FOR k IN 0..Arr.count - 1 LOOP
SELECT MyName INTO vName FROM T97 WHERE ID = Arr(k);
strResult := strResult || vName || ', ';
END LOOP;
-- 处理掉最后一个,号
strResult := substr(strResult, 1, length(strResult) - 2);
RETURN strResult;
END FunctionT97;