CREATE OR REPLACE FUNCTION fun_mysql ( p_str IN VARCHAR2,p_delimiter IN VARCHAR2 ) RETURN VARCHAR2 IS j INT DEFAULT 0; i INT DEFAULT 1; len INT DEFAULT 0; len1 INT DEFAULT 0; strname VARCHAR2(4000); strval VARCHAR2(4000); BEGIN len:=length(p_str); len1:=length(p_delimiter); <<lable>> WHILE j<len LOOP j:=instr(p_delimiter,p_str,i); IF j=0 THEN j:=len; SELECT NAME INTO strval FROM cms_tlrctl WHERE tlrno=substr(p_str,i); strname:=nvl(strname,'')||','||strval; IF i>=len THEN EXIT lable; END IF; ELSE SELECT NAME INTO strval FROM cms_tlrctl WHERE tlrno=substr(p_str, i, j - i); strname := nvl(strname,'')||','||strval; i:= j + len1; END if; END LOOP lable; RETURN strname; END;
CREATE OR REPLACE FUNCTION fun_mysql
(
p_str IN VARCHAR2,p_delimiter IN VARCHAR2
)
RETURN VARCHAR2
IS
j INT DEFAULT 0;
i INT DEFAULT 1;
len INT DEFAULT 0;
len1 INT DEFAULT 0;
strname VARCHAR2(4000);
strval VARCHAR2(4000);
BEGIN
len:=length(p_str);
len1:=length(p_delimiter);
<<lable>>
WHILE j<len
LOOP
j:=instr(p_delimiter,p_str,i);
IF j=0 THEN
j:=len;
SELECT NAME INTO strval FROM cms_tlrctl WHERE tlrno=substr(p_str,i);
strname:=nvl(strname,'')||','||strval;
IF i>=len THEN
EXIT lable;
END IF;
ELSE
SELECT NAME INTO strval FROM cms_tlrctl WHERE tlrno=substr(p_str, i, j - i);
strname := nvl(strname,'')||','||strval;
i:= j + len1;
END if;
END LOOP lable;
RETURN strname;
END;