Add the split function to oracle

Add the split function in oracle. CREATE table TYPE www.2cto.com create or replace type "TYPE_SPLIT" as table of varchar2 (4000); 2. create or replace function split (p_list VARCHAR2, p_sep VARCHAR2: = ',') RETURN type_split pipelined is l_idx PLS_INTEGER; v_list VARCHAR2 (4000): = p_list; begin if p_list is null or p_list = ''then return; end if; LOOP l_idx: = instr (v_list, p_sep); IF l_idx> 0 then pipe row (substr (v _ List, 1, l_idx-1); v_list: = substr (v_list, l_idx + length (p_sep); else pipe row (v_list); EXIT; end if; end loop; RETURN; END split; 3. we often use in for queries such as www.2cto.com SELECT t. * FROM stu t WHERE t. useId IN ('12, 1000 '); however, when the number of queried results exceeds, an error is returned. So we can use the preceding split to query SELECT t. * FROM stu t, (SELECT column_value cv from table (CAST (split ('12, 66 ') AS type_split) a WHERE t. userId =. cv, the first is to split the string '12, 66 'into four rows, split ('12, 66') and then, is to convert the split value to the split type type_split we define; cast (split ('12, 66 ') as type_split) This step does not seem to be needed, in the table, convert to table. The default name of the split field is column_value;