Set three functions

First function field translation function

create or replace function FUNC_LICENCE_TYPE_DIC(field_val in varchar2,id in varchar2)
return varchar2
is
  v_val varchar2(50);
  v_mult_val varchar2(100);
  res_back varchar2(100);
  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.dictitemname into v_val from BDC_DICTITEM a where a.dictitemcode = c_row.column_value and a.dictid = id;
    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_LICENCE_TYPE_DIC;

Second function

Field separator function

CREATE OR REPLACE FUNCTION SPLIT_FOR_ARRAY(P_STR       IN VARCHAR2,
                                 P_DELIMITER IN VARCHAR2 DEFAULT (',') --Separator, default comma
                                 )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;

Third

Definition type

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

Example

Translate different dictionary items of the same field according to different categories

    select mm.*,FUNC_LICENCE_TYPE_DIC(mm.matain_reason,reasontype) matain_reason from (select t.voltage_type,
    t.matain_reason,
    case
    when t.voltage_type ='1'
    then '52'
    when t.voltage_type ='2'
    then '53'
    when t.voltage_type ='3'
    then '54'
    end reasontype from recodr_maintain_voltage t) mm

Description: if the type is 1, translate the dictionary item whose corresponding dictionary code is 52, and so on. Sometimes when designing a table, you will set similar content of multiple pages to the same field, but the corresponding dictionary items of this field are different for different pages, so you need to translate this field according to different categories.