ORACLE字元拆分函數,返回結果集,oracle拆分字串
ORACLE不能像MSSQL那樣支援直接返回表類型,所以要先創建一種自定義類型。這裏用到的是巢狀表格(Nested Table)。
-- Nested TableCREATE OR REPLACE TYPE split_str IS TABLE OF VARCHAR(100);/-- FunctionCREATE OR REPLACE FUNCTION fn_Split( p_Str VARCHAR2, p_Delimiter VARCHAR2)RETURN split_str PIPELINEDAS v_Str VARCHAR(4000) := p_Str; v_Index NUMBER; v_SubLength NUMBER;BEGIN -- p_Delimiter is null IF p_Delimiter IS NULL THEN FOR x IN 1..LENGTH(v_Str) LOOP PIPE ROW(SUBSTR(V_Str,x,1)); END LOOP; RETURN; END IF; v_index := INSTR(v_Str,p_Delimiter); WHILE v_Index <> 0 LOOP PIPE ROW(SUBSTR(v_Str,1,v_Index-1)); v_SubLength := LENGTH(v_Str) - (v_index + LENGTH(p_Delimiter) - 1); v_Str := SUBSTR(v_Str,-v_SubLength,v_SubLength); v_index := INSTR(v_Str,p_Delimiter); END LOOP; PIPE ROW(v_Str); RETURN;END;/-- 調用(第二種方式更好)SELECT fn_split('This@@is@@it','@@') FROM dual;SELECT * FROM Table(fn_split('This@@is@@it','@@'));
From: 無明之師
將Oracle欄位值按特定字元拆分(多條記錄)?
需要寫一個函數,對特定字元分隔的字串進行拆分,然後就好做了
oracle 函數與預存程序返回結果集
(一)預存程序(例:a表(欄位有ano,aname,aage))
create or replace procedure test
as
begin
cursor cursor_test is select * from a;
row_test a%rowtype;
begin
for row_test in cursor_test loop
dbms_output.put_line(row_test.ano||row_test.aname||row_test.aage);
end loop;
end test;
調用方法:
(1)EXEC test;
(2)begin
test;
end;
/