PLSQL的遞歸
?
??? 今天想寫一個PLSQL的函數，需要實現遞歸，以前一直都沒有在PLSQL里用過遞歸，上網查了一下發現PLSQL確實可以實現遞歸的，貼個網上的例子：
?
?
SQL> create or replace function get_char_one_by_one(v_str varchar2)
? 2??? return varchar2 is
? 3? begin
? 4??? if v_str is not null and length(v_str) > 1 then
? 5????? return substr(v_str, 1, 1) || ' ' || get_char_one_by_one(substr(v_str, 2));
? 6??? else
? 7????? return v_str;
? 8??? end if;
? 9? end;
10? /
?
Function created
?
SQL> select get_char_one_by_one('I am good boy') from dual;
?
GET_CHAR_ONE_BY_ONE('IAMGOODBO
-------------------------------------------
I?? a m?? g o o d?? b o y
?
?
??? 自己隨便寫一個練練手，列出每個以','分開的字符部分：
?
SQL> create or replace function get_part(v_char in varchar2) return varchar2 is
? 2??? v_temp??????? integer;
? 3??? v_insert_char varchar2(1000);
? 4? begin
? 5??? v_temp??????? := instr(v_char, ',', 1);
? 6??? v_insert_char := substr(v_char, 1, v_temp - 1);
? 7??? if v_temp > 0 then
? 8????? dbms_output.put_line(v_insert_char);
? 9????? return get_part(substr(v_char, v_temp + 1));
10??? else
11????? dbms_output.put_line(v_char);
12????? return null;
13??? end if;
14? end;
15? /
?
Function created
?
SQL> select get_part('42342fesh,43rfddfg,t35greg4,f43g54') from dual;
?
GET_PART('42342FESH,43RFDDFG,T
-----------------------------------------------
?
42342fesh
43rfddfg
t35greg4
f43g54