SHARE
TWEET

Untitled

a guest Aug 13th, 2017 60 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT*FROM Split('A,B,C,D',',')
  2.    
  3. |Id | Data
  4.  --   ----
  5. |1  | A  |
  6. |2  | B  |
  7. |3  | C  |
  8. |4  | D  |
  9.    
  10. SELECT LEVEL AS id, REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, LEVEL) AS data
  11.    FROM dual
  12. CONNECT BY REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, LEVEL) IS NOT NULL;
  13.    
  14. CREATE OR REPLACE TYPE T_TABLE IS OBJECT
  15. (
  16.     Field1 int
  17.     , Field2 VARCHAR(25)
  18. );
  19. CREATE TYPE T_TABLE_COLL IS TABLE OF T_TABLE;
  20. /
  21.    
  22. CREATE OR REPLACE TYPE T_TABLE IS OBJECT
  23. (
  24.     Field1 int
  25.     , Field2 VARCHAR(25)
  26. );
  27. CREATE TYPE T_TABLE_COLL IS TABLE OF T_TABLE;
  28. /
  29.    
  30. CREATE OR REPLACE TYPE T_TABLE IS OBJECT
  31. (
  32.     Field1 int
  33.     , Field2 VARCHAR(25)
  34. );
  35. CREATE TYPE T_TABLE_COLL IS TABLE OF T_TABLE;
  36. /
  37.    
  38. CREATE OR REPLACE TYPE T_TABLE IS OBJECT
  39. (
  40.     Field1 int
  41.     , Field2 VARCHAR(25)
  42. );
  43. CREATE TYPE T_TABLE_COLL IS TABLE OF T_TABLE;
  44. /
  45.    
  46. CREATE OR REPLACE TYPE T_TABLE IS OBJECT
  47. (
  48.     Field1 int
  49.     , Field2 VARCHAR(25)
  50. );
  51. CREATE TYPE T_TABLE_COLL IS TABLE OF T_TABLE;
  52. /
  53.    
  54. CREATE OR REPLACE TYPE T_TABLE IS OBJECT
  55. (
  56.     Field1 int
  57.     , Field2 VARCHAR(25)
  58. );
  59. CREATE TYPE T_TABLE_COLL IS TABLE OF T_TABLE;
  60. /
  61.    
  62. CREATE OR REPLACE FUNCTION TEST_RETURN_TABLE
  63. RETURN T_TABLE_COLL
  64.     IS
  65.       l_res_coll T_TABLE_COLL;
  66.       l_index number;
  67.     BEGIN
  68.       l_res_coll := T_TABLE_COLL();
  69.       FOR i IN (
  70.         WITH TAB AS
  71.           (SELECT '1001' ID, 'A,B,C,D,E,F' STR FROM DUAL
  72.           UNION
  73.           SELECT '1002' ID, 'D,E,F' STR FROM DUAL
  74.           UNION
  75.           SELECT '1003' ID, 'C,E,G' STR FROM DUAL
  76.           )
  77.         SELECT id,
  78.           SUBSTR(STR, instr(STR, ',', 1, lvl) + 1, instr(STR, ',', 1, lvl + 1) - instr(STR, ',', 1, lvl) - 1) name
  79.         FROM
  80.           ( SELECT ',' || STR || ',' AS STR, id FROM TAB
  81.           ),
  82.           ( SELECT level AS lvl FROM dual CONNECT BY level <= 100
  83.           )
  84.         WHERE lvl <= LENGTH(STR) - LENGTH(REPLACE(STR, ',')) - 1
  85.         ORDER BY ID, NAME)
  86.       LOOP
  87.         IF i.ID = 1001 THEN
  88.           l_res_coll.extend;
  89.           l_index := l_res_coll.count;
  90.           l_res_coll(l_index):= T_TABLE(i.ID, i.name);
  91.         END IF;
  92.       END LOOP;
  93.       RETURN l_res_coll;
  94.     END;
  95.     /
  96.    
  97. select * from table(TEST_RETURN_TABLE());
  98.    
  99. SQL> select * from table(TEST_RETURN_TABLE());
  100.  
  101.     FIELD1 FIELD2
  102. ---------- -------------------------
  103.       1001 A
  104.       1001 B
  105.       1001 C
  106.       1001 D
  107.       1001 E
  108.       1001 F
  109.  
  110. 6 rows selected.
  111.    
  112. CREATE OR REPLACE FUNCTION split_String(
  113.   i_str    IN  VARCHAR2,
  114.   i_delim  IN  VARCHAR2 DEFAULT ','
  115. ) RETURN SYS.ODCIVARCHAR2LIST DETERMINISTIC
  116. AS
  117.   p_result       SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
  118.   p_start        NUMBER(5) := 1;
  119.   p_end          NUMBER(5);
  120.   c_len CONSTANT NUMBER(5) := LENGTH( i_str );
  121.   c_ld  CONSTANT NUMBER(5) := LENGTH( i_delim );
  122. BEGIN
  123.   IF c_len > 0 THEN
  124.     p_end := INSTR( i_str, i_delim, p_start );
  125.     WHILE p_end > 0 LOOP
  126.       p_result.EXTEND;
  127.       p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start );
  128.       p_start := p_end + c_ld;
  129.       p_end := INSTR( i_str, i_delim, p_start );
  130.     END LOOP;
  131.     IF p_start <= c_len + 1 THEN
  132.       p_result.EXTEND;
  133.       p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );
  134.     END IF;
  135.   END IF;
  136.   RETURN p_result;
  137. END;
  138. /
  139.    
  140. SELECT ROWNUM AS ID,
  141.        COLUMN_VALUE AS Data
  142. FROM   TABLE( split_String( 'A,B,C,D' ) );
  143.    
  144. ID DATA
  145. -- ----
  146.  1 A
  147.  2 B
  148.  3 C
  149.  4 D
  150.    
  151. CREATE OR REPLACE FUNCTION Split (p_str varchar2) return sys_refcursor is
  152. v_res sys_refcursor;
  153.  
  154. begin
  155.   open v_res for
  156.   WITH TAB AS
  157.   (SELECT p_str STR FROM DUAL)
  158.   select substr(STR, instr(STR, ',', 1, lvl) + 1, instr(STR, ',', 1, lvl + 1) - instr(STR, ',', 1, lvl) - 1) name
  159.   from
  160.     ( select ',' || STR || ',' as STR from TAB ),
  161.     ( select level as lvl from dual connect by level <= 100 )
  162.     where lvl <= length(STR) - length(replace(STR, ',')) - 1;
  163.  
  164.      return v_res;
  165.    end;
  166.    
  167. CREATE OR REPLACE FUNCTION Split (p_str varchar2) return sms.t_empty_type is
  168. v_emptype t_empty_type := t_empty_type();
  169. v_cnt     number := 0;
  170. v_res sys_refcursor;
  171. v_value nvarchar2(128);
  172. begin
  173.   open v_res for
  174.   WITH TAB AS
  175.   (SELECT p_str STR FROM DUAL)
  176.   select substr(STR, instr(STR, ',', 1, lvl) + 1, instr(STR, ',', 1, lvl +     1) - instr(STR, ',', 1, lvl) - 1) name
  177.   from
  178.     ( select ',' || STR || ',' as STR from TAB ),
  179.     ( select level as lvl from dual connect by level <= 100 )
  180.     where lvl <= length(STR) - length(replace(STR, ',')) - 1;
  181.  
  182.  
  183.   loop
  184.      fetch v_res into v_value;
  185.       exit when v_res%NOTFOUND;
  186.       v_emptype.extend;
  187.       v_cnt := v_cnt + 1;
  188.      v_emptype(v_cnt) := empty_type(v_value);
  189.     end loop;
  190.     close v_res;
  191.  
  192.     return v_emptype;
  193. end;
  194.    
  195. SELECT * FROM (TABLE(split('a,b,c,d,g')))
  196.    
  197. create or replace FUNCTION PK_GET_NTH_PART(MYSTRING VARCHAR2,SEPARATOR_OF_SUBSTR VARCHAR2,NTH_PART NUMBER)
  198. RETURN VARCHAR2
  199. IS
  200. NTH_SUBSTR VARCHAR2(500);
  201. POS1 NUMBER(4);
  202. POS2 NUMBER(4);
  203. BEGIN
  204. IF NTH_PART=1 THEN
  205. SELECT REGEXP_INSTR(MYSTRING,SEPARATOR_OF_SUBSTR, 1, 1)  INTO POS1 FROM DUAL;
  206. SELECT SUBSTR(MYSTRING,0,POS1-1) INTO NTH_SUBSTR FROM DUAL;
  207. ELSE
  208. SELECT REGEXP_INSTR(MYSTRING,SEPARATOR_OF_SUBSTR, 1, NTH_PART-1) INTO  POS1 FROM DUAL;
  209. SELECT REGEXP_INSTR(MYSTRING,SEPARATOR_OF_SUBSTR, 1, NTH_PART)  INTO POS2 FROM DUAL;
  210. SELECT SUBSTR(MYSTRING,POS1+1,(POS2-POS1-1)) INTO NTH_SUBSTR FROM DUAL;
  211. END IF;
  212. RETURN NTH_SUBSTR;
  213. END;
  214.    
  215. SELECT REGEXP_COUNT(MYSTRING, '~', 1, 'i') INTO NO_OF_RECORDS FROM DUAL;
  216. WHILE NO_OF_RECORDS>0
  217. LOOP
  218.     PK_RECORD    :=PK_GET_NTH_PART(MYSTRING,'~',NO_OF_RECORDS);
  219.     -- do some thing
  220.     NO_OF_RECORDS  :=NO_OF_RECORDS-1;
  221. END LOOP;
  222.    
  223. select
  224.     split.field(column_name,1,',','"') name1,
  225.     split.field(column_name,2,',','"') name2
  226. from table_name
RAW Paste Data
Top