求一个oracle函数,具体要求如下:
输入参数1:varchar2,以分号分隔多个内容,如“11;22;3323;abc;ddd”
输入参数2:integer数值,返回值的位置输出参数:varchar2;输出参数是输入参数1中,第《输入参数2》个用";"分隔的值;
例如:
输入参数('11;22;3323;abc;ddd',1),返回 11
输入参数('11;22;3323;abc;ddd',4),返回 abc
输入参数1:varchar2,以分号分隔多个内容,如“11;22;3323;abc;ddd”
输入参数2:integer数值,返回值的位置输出参数:varchar2;输出参数是输入参数1中,第《输入参数2》个用";"分隔的值;
例如:
输入参数('11;22;3323;abc;ddd',1),返回 11
输入参数('11;22;3323;abc;ddd',4),返回 abc
select substr(str,instr(str,';',1,i-1)+2,instr(str,';',1,i)-2) from dual
SQL> edi
已写入 file afiedt.buf 1 create or replace function fun_zf(str varchar2,i number) return varchar2
2 as
3 id varchar2(100);
4 str2 varchar2(100);
5 begin
6 id:=';'||str||';';
7 select
8 substr(id,instr(id,';',1,i)+1,instr(id,';',1,i+1)-instr(id,';',1,i)-1) into str2
9 from dual;
10 return str2;
11* end;
SQL> /函数已创建。SQL> select fun_zf('11;22;3323;abc;ddd',1) from dual
2 /FUN_ZF('11;22;3323;ABC;DDD',1)
--------------------------------------------------------------------------------
11SQL> select fun_zf('11;22;3323;abc;ddd',4) from dual
2 /FUN_ZF('11;22;3323;ABC;DDD',4)
--------------------------------------------------------------------------------
abc
CREATE OR REPLACE FUNCTION getstr(str VARCHAR2, n PLS_INTEGER, delim CHAR) RETURN VARCHAR2 IS
tempstr VARCHAR2(2000);
--str :原始字符串
--n :取第几个字符串
--delim:分隔符
--调用方式: getstr('11;22;3323;abc;ddd',3,';')
BEGIN
tempstr := delim || str || delim;
RETURN substr(tempstr,
instr(tempstr, delim, 1, n) + 1,
instr(tempstr, delim, 1, n + 1) - instr(tempstr, delim, 1, n) - 1);
END;
/
r,returnStr out varchar2)
2 as
3 begin
4 if appearCount=1 then
5 select substr(inputStr,0,instr(inputstr,';',1,1)-1) into ret
nStr from dual;
6 else
7 dbms_output.put_line(instr(inputStr,';',1,appearCount-1));
8 dbms_output.put_line(instr(inputStr,';',1,appearCount));
9 select substr(inputStr,instr(inputStr,';',1,appearCount-1)+1
nstr(inputStr,';',1,appearCount)-instr(inputStr,';',1,appearCount-1)-1) into r
urnStr from dual;
10 end if;
11 end;
12 /过程已创建。SQL> declare
2 rstr varchar2(20);
3 begin
4 checkStr('11;22;3323;abc;ddd',4,rstr);
5 dbms_output.put_line(rstr);
6 end;
7 /
11
15
abcPL/SQL 过程已成功完成。SQL> declare
2 rstr varchar2(20);
3 begin
4 checkStr('11;22;3323;abc;ddd',3,rstr);
5 dbms_output.put_line(rstr);
6 end;
7 /
6
11
3323PL/SQL 过程已成功完成。SQL>