Oracle 實現多語言(即根據使用者登入的環境自適應本地語言)

  CREATE OR REPLACE PACKAGE BODY OADBA.db_global
IS
   --設定引數值
   PROCEDURE set_value (parameter VARCHAR2, val VARCHAR2)
   IS
   BEGIN
      DBMS_SESSION.set_context ('db_context' , parameter , val );
   END set_value ;

   --取得引數值
   FUNCTION get_value (parameter VARCHAR2)
      RETURN VARCHAR2
   IS
      v_value   VARCHAR2 (200);
   BEGIN
      SELECT SYS_CONTEXT ('db_context' , parameter ) INTO v_value FROM DUAL;

      RETURN v_value ;
   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN NULL;
   END get_value ;

   /*
   把以strb作為分隔符號分解stra,並返回陣列。
   */
   FUNCTION split (v_stra IN VARCHAR2, v_strb IN VARCHAR2 )
      RETURN v_str_array
   IS
      i          INT := 1;
      j          INT := 0;
      v_len      INT := 0;
      v_len1     INT := 0;
      str        VARCHAR2 (4000);
      my_array   v_str_array ;
   BEGIN
      v_len := LENGTH (v_stra);
      v_len1 := LENGTH (v_strb);

      WHILE j <= v_len
      LOOP
         j := INSTR (v_stra , v_strb , i );

         IF j = 0
         THEN
            j := v_len + 1;
            str := SUBSTR (v_stra , i );
            --str_split.EXTEND;
            --my_table.extend;
            my_array (my_array .COUNT) := str ;

            IF i >= v_len
            THEN
               EXIT;
            END IF;
         ELSE
            str := SUBSTR (v_stra , i , j - i );
            i := j + v_len1 ;
            --str_split.EXTEND;
            my_array (my_array .COUNT) := str ;
         END IF;
      END LOOP;

      RETURN my_array ;
   END;

   /*
   依傳入Msg_ID返回Message
   v_msg_parameter為傳入引數，格式如：LOT_NO=SK123-001,MTR_NO=NX0012309
   多個引數以逗?分開
   */
   FUNCTION get_messages (v_msg_id VARCHAR2, v_msg_parameter VARCHAR2 DEFAULT NULL)
      RETURN VARCHAR2
   IS
      v_msg      VARCHAR2 (1000);
      v_array1   v_str_array ;
      v_array2   v_str_array ;
      v_str      VARCHAR2 (500);
   BEGIN
      SELECT fd040_desc
        INTO v_msg
        FROM fd040_v
       WHERE fd040_msg_id = v_msg_id ;

      IF v_msg_parameter IS NOT NULL
      THEN
         v_array1 := split (v_msg_parameter , ',');

         FOR i IN 0 .. (v_array1 .COUNT - 1)
         LOOP
            v_str := v_array1 (i );

            IF v_str IS NOT NULL
            THEN
               v_array2 := split (v_str , '=');

               IF v_array2 (0) IS NOT NULL AND v_array2.COUNT > 1
               THEN
                  v_msg := REPLACE (v_msg , '{' || v_array2 (0) || '}' , v_array2 (1));
               END IF;
            END IF;
         END LOOP;
      END IF;

      RETURN v_msg;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         RETURN db_global .GET_MESSAGE ('FDM00000', 'MsgID=' || v_msg_id);
      WHEN OTHERS
      THEN
         RETURN 'MsgID:' || v_msg_id || ' error! ' || SQLERRM;
   END get_messages ;

   /*
   依傳入Msg_ID返回Message
   v_msg_parameter為傳入引數，格式如：LOT_NO=SK123-001,MTR_NO=NX0012309
   多個引數以逗?分開
   */
   FUNCTION GET_MESSAGE (v_msg_id VARCHAR2, v_msg_parameter VARCHAR2 DEFAULT NULL)
      RETURN VARCHAR2
   IS
      v_msg      VARCHAR2 (1000);
      v_array1   v_str_array ;
      v_array2   v_str_array ;
      v_str      VARCHAR2 (500);
   BEGIN
      SELECT fd040_desc
        INTO v_msg
        FROM fd040_v
       WHERE fd040_msg_id = v_msg_id ;

      IF v_msg_parameter IS NOT NULL
      THEN
         v_array1 := split (v_msg_parameter , ',');

         FOR i IN 0 .. (v_array1 .COUNT - 1)
         LOOP
            v_str := v_array1 (i );

            IF v_str IS NOT NULL
            THEN
               v_array2 := split (v_str , '=');

               IF v_array2 (0) IS NOT NULL AND v_array2.COUNT > 1
               THEN
                  v_msg := REPLACE (v_msg , '{' || v_array2 (0) || '}' , v_array2 (1));
               END IF;
            END IF;
         END LOOP;
      END IF;

      v_msg := v_msg_id || '：' || v_msg;
      RETURN v_msg;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         RETURN db_global .GET_MESSAGE ('FDM00000', 'MsgID=' || v_msg_id);
      WHEN OTHERS
      THEN
         RETURN 'MsgID: ' || v_msg_id || ' error! ' || SQLERRM;
   END GET_MESSAGE ;

   --行動生成MessageID
   FUNCTION get_message_id (v_system_id VARCHAR2)
      RETURN VARCHAR2
   IS
      v_seq          NUMBER;
      v_message_id   VARCHAR2 (8);
   BEGIN
      IF v_system_id IS NOT NULL
      THEN
         BEGIN
                SELECT NVL (fd041_seq , 0) + 1
                  INTO v_seq
                  FROM fd041
                 WHERE fd041_system_id = v_system_id
            FOR UPDATE NOWAIT;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               v_seq := 1;
         END;

         v_message_id := v_system_id || 'M' || LTRIM (TO_CHAR (v_seq , '00000'));

         UPDATE fd041
            SET fd041_seq = v_seq
          WHERE fd041_system_id = v_system_id ;

         IF SQL% NOTFOUND
         THEN
            INSERT INTO fd041 (fd041_system_id , fd041_seq )
                 VALUES (v_system_id , v_seq );
         END IF;

         COMMIT;

         RETURN v_message_id ;
      ELSE
         RETURN NULL;
      END IF;
   END get_message_id ;

   PROCEDURE p_test
   IS
      v_array1   v_str_array ;
   BEGIN
      v_array1 := db_global .split ('a=', '=' );
      DBMS_OUTPUT.put_line (v_array1.COUNT);
   END;
END db_global;

CREATE OR REPLACE FORCE VIEW OADBA.SD079_V
(
SD079_CUST_NO,
SD079_SHP_MTR,
SD079_SHP_NAME,
SD079_LOC_NAME,
SD079_NLS_NAME,
SD079_UNIT,
SD079_LOC_UNIT,
SD079_NLS_UNIT,
SD079_TYPE,
SD079_CFM_USER,
SD079_CFM_DATE,
SD079_SYS_USER,
SD079_SYS_DATE
)
AS
SELECT SD079_CUST_NO ,
SD079_SHP_MTR ,
SD079_SHP_NAME ,
SD079_LOC_NAME ,
NVL (
DECODE (db_global.get_value ( 'USER_LANG'),
db_global.get_value ('LOC_LANG' ), SD079_LOC_NAME ,
'EN', SD079_SHP_NAME ,
SD079_SHP_NAME ),
SD079_SHP_NAME )
SD079_NLS_NAME ,
SD079_UNIT ,
SD079_LOC_UNIT ,
NVL (
DECODE (db_global.get_value ( 'USER_LANG'),
db_global.get_value ('LOC_LANG' ), SD079_LOC_UNIT ,
'EN', SD079_UNIT,
SD079_UNIT ),
SD079_UNIT )
SD079_NLS_UNIT ,
SD079_TYPE ,
SD079_CFM_USER ,
SD079_CFM_DATE ,
SD079_SYS_USER ,
SD079_SYS_DATE
FROM sd079;