自定义PL/SQL函数: 如果你需要频繁地进行字符串拆分操作,或者希望有一个更通用的解决方案,可以编写一个自定义的PL/SQL函数或过程来处理这个任务。 sql CREATE OR REPLACE FUNCTION split_string(p_string IN VARCHAR2, p_delimiter IN VARCHAR2) RETURN SYS.ODCIVARCHAR2LIST PIPELINED IS l_idx PLS_INTEGER; l...
SQL> create or replace package base_type_library_pkg is type ba_type is record(col_membervarchar2(200)) ; type ba_tab_type is table of ba_type; end base_type_library_pkg; / Package created ② 创建函数实现包 create orreplace package get_split_table_pkg is -- Author : Jason Shang -...
CREATE OR REPLACE FUNCTION SPLIT_STRING (STR VARCHAR2, DELIMITER VARCHAR2) RETURN SYS_REFCURSOR IS TYPE string_table_type IS TABLE OF VARCHAR2 (4000) INDEX BY PLS_INTEGER; result_set SYS_REFCURSOR; string_list string_table_type; delimiter_len NUMBER := LENGTH (DELIMITER); last_delim NUMBER...
PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start)); v_start := v_index + 1; END IF; END LOOP; RETURN; END splitstr; 第二种:sql解决 select dbms_lob.substr(regexp_substr('abc,def,ggg,1,2,3,4,5', '[^,]+', 1, x.n)) as player_guid from dual a, (select rownum...
FUNCTION splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2) RETURN str_split PIPELINED AS v_length NUMBER := LENGTH(p_string); v_start NUMBER := 1; v_index NUMBER; BEGIN WHILE(v_start <= v_length) LOOP v_index := INSTR(p_string, p_delimiter, v_start); ...
(1)定义split_type类型: CREATEORREPLACE TYPE split_typeISTABLEOFVARCHAR2 (4000) (2)定义split函数: CREATEORREPLACEFUNCTIONsplit ( p_strINVARCHAR2, p_delimiterINVARCHAR2default(',')--分隔符,默认逗号 ) RETURNsplit_type IS jINT:= 0;
NOTE : This post is about splitting a single comma delimited string. If you have to apply it on a table with multiple rows having comma delimited strings, then look at Split comma delimited strings in a table using Oracle SQL This is one of the most comm
如果你需要频繁进行这种拆分操作,可以考虑创建一个 PL/SQL 存储过程。 代码语言:javascript 复制 CREATE OR REPLACE FUNCTION split_string ( p_string IN VARCHAR2, p_delimiter IN VARCHAR2 := ',', p_max_parts IN NUMBER := 100 ) RETURN SYS.ODCIVARCHAR2LIST PIPELINED IS l_string VARCHAR2(32767) ...
记录一下CREATE OR REPLACE TYPE str_split IS TABLE OF VARCHAR2 (4000); CREATE OR REPLACE FUNCTION splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2) RETURN str_split PIPELINED AS v_length oracle 原创 天天代码码天天 2023-05-11 10:50:35 ...
本函数可以将“目标字符串”以“指定字符串”进行拆分,并通过表结构返回结果。代码如下:CREATEORREPLACETYPE str_splitISTABLEOFVARCHAR2(4000);CREATEORREPLACEFUNCTIONsplitstr(p_stringINVARCHAR2, p_delimiterINVARCHAR2)RETURNstr_split PIPELINEDASv_lengthNUMBER:=LENGTH(p_string); ...