oracle对符号号隔开的多个字段翻译

翻译函数 1

create or replace function FUNC_MANNAME(field_val in varchar2)
    return varchar2
    is
      v_val varchar2(4000);
      v_mult_val varchar2(4000);
      res_back varchar2(4000);
      cursor c_result is
      select t.column_value from table (SPLIT_FOR_ARRAY(field_val,',')) t;
      c_row c_result%rowtype;
    begin
      for c_row in c_result loop
         select a.user_NAME into v_val from ct_user a where a.user_id = c_row.column_value;
        v_mult_val := v_mult_val|| ','||v_val;
      end loop;
      select substr(v_mult_val,2) into res_back from dual;
      return res_back;
      EXCEPTION
      WHEN OTHERS THEN
        RETURN '';
    end FUNC_MANNAME;

或者翻译函数2（对字段处理后再翻译）

create or replace function FUNC_JOIN_ORG(ORG_ID in varchar2)
return varchar2
is
  v_val varchar2(4000);
	v_mult_val varchar2(4000);
	c_result array_container := array_container();
	res_back varchar2(4000);
	c_val varchar2(4000);
begin
	if instr(ORG_ID, ',') > 0 then
		 select SPLIT_FOR_ARRAY(ORG_ID,',') into c_result from dual;
		for c_row in 1..c_result.count loop
			 SELECT wm_concat(party_org_id) into v_val FROM pb_party_org where  state = '1' START WITH party_org_id = ''||c_result(c_row)||''  CONNECT BY nocycle PRIOR party_org_id = last_org;
			 if instr(v_mult_val,v_val) > 0 then 
				 v_mult_val := v_mult_val;
			 else 
			    v_mult_val := v_mult_val|| ','||v_val;
				end if;	
				
		end loop;
		select substr(v_mult_val,2) into res_back from dual;
	else
		SELECT wm_concat(party_org_id) into v_mult_val FROM pb_party_org where  state = '1'
                      START WITH party_org_id = ''||ORG_ID||''
                     CONNECT BY nocycle PRIOR party_org_id = last_org;
		res_back := v_mult_val;
	end if;
	    select FUNC_SPLIT(res_back) into c_val from dual;
		return c_val;
  EXCEPTION
  WHEN OTHERS THEN
    RETURN '';
end FUNC_JOIN_ORG;

工具函数

CREATE OR REPLACE FUNCTION SPLIT_FOR_ARRAY(P_STR       IN VARCHAR2,
                                 P_DELIMITER IN VARCHAR2 DEFAULT (',') --分隔符，默认逗号
                                 )RETURN  array_container  IS
  J        INT := 0;
  I        INT := 1;
  LEN      INT := 0;
  LEN1     INT := 0;
  STR      VARCHAR2(4000);
  MY_SPLIT array_container := array_container();
BEGIN
  LEN  := LENGTH(P_STR);
  LEN1 := LENGTH(P_DELIMITER);

  WHILE J < LEN LOOP
    J := INSTR(P_STR, P_DELIMITER, I);

    IF J = 0 THEN
      J   := LEN;
      STR := SUBSTR(P_STR, I);
      MY_SPLIT.EXTEND;
      MY_SPLIT(MY_SPLIT.COUNT) := STR;

      IF I >= LEN THEN
        EXIT;
      END IF;
    ELSE
      STR := SUBSTR(P_STR, I, J - I);
      I   := J + LEN1;
      MY_SPLIT.EXTEND;
      MY_SPLIT(MY_SPLIT.COUNT) := STR;
    END IF;
  END LOOP;

  RETURN MY_SPLIT;
END SPLIT_FOR_ARRAY;

所需类型

CREATE OR REPLACE TYPE "ARRAY_CONTAINER"  AS TABLE OF varchar2(100)

业务翻译字段

 FUNC_USERNAME(c.join_user_id)    AS psn