实现单个字段01;02;03;转视图(列值拼接为同一行)

时间:2022-07-08 15:13:44

 SELECT (SELECT WMSYS.WM_CONCAT(V.CODE_NAME)
 SELECT * FROM TABLE(SPLIT_STR('01;02', ';')) T, VW_EQUIP_CATEG V
  WHERE T.COLUMN_VALUE = V.CODE)

 

 

 

CREATE OR REPLACE FUNCTION split_str(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);

          IF  v_index = 0        

    THEN            

      PIPE ROW(SUBSTR(p_string, v_start));            

      v_start := v_length + 1;        

    ELSE            

       PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));            

       v_start := v_index + 1;        

    END IF;    

  END LOOP;

    RETURN;

 

    RETURN;

END split_str;