oracle字典 实现查找和转化的两个function

字典表结构如下：

CREATE TABLE DATA_DICTIONARY
   (	"DTD_ID" NUMBER, 
	"DTD_CODE" VARCHAR2(10), 
	"DTD_COLUMN_NAME" VARCHAR2(100), 
	"DTD_VALUE" VARCHAR2(100)
   ) 

--====================================================================
    -- NAME: FUN_GET_CODEDESC
    -- DESC: 字典转化，返回code（如'0102030405')+column_name 

    -- 对应的value（如：'001|010|110'）
     -- IN  : 
    -- OUT : 
    -- HISTORY:16-JUL-2008  user   CREATED
    -- NOTE:
    --====================================================================
    FUNCTION FUN_GET_CODEDESC(P_CODE IN VARCHAR2, P_COLUMN_NAME IN VARCHAR2)
      RETURN VARCHAR2 IS
      X_TEMP_CODE    VARCHAR2(2);
      X_TEMP_VALUE   VARCHAR2(100);
      X_RETURN_VALUE VARCHAR2(1000) := '|';
      X_LEN          NUMBER := 0;
      I              INT := 1;
    BEGIN
      X_LEN := LENGTH(P_CODE);
      -- 如果CODE是2位的，根据两个参数返回对应的VALUE
      WHILE I <= X_LEN LOOP
        X_TEMP_CODE := SUBSTR(P_CODE, I, 2);
        BEGIN
          SELECT DD.DTD_VALUE
            INTO X_TEMP_VALUE
            FROM DATA_DICTIONARY DD
           WHERE DD.DTD_CODE = X_TEMP_CODE
             AND DD.DTD_COLUMN_NAME = P_COLUMN_NAME;
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            GOTO HERE;
        END;
        X_RETURN_VALUE := X_RETURN_VALUE || X_TEMP_VALUE || &apos;|&apos;;
        <
 
 
  
  >
        I := I + 2;
      END LOOP;
      X_RETURN_VALUE := SUBSTR(X_RETURN_VALUE, 2, LENGTH(X_RETURN_VALUE) - 2);

      RETURN X_RETURN_VALUE;
    EXCEPTION
      WHEN OTHERS THEN
        RETURN NULL;
    END;

    
  
  --====================================================================
    -- NAME: FUN_GET_CODEDESC
    -- DESC: 字典转化，返回value（如：&apos;001|010|110&apos;）+column_name对应的
     -- code（如：‘010203’）
    -- IN  : 
    -- OUT : 
    -- HISTORY:16-JUL-2008   user   CREATED
    -- NOTE:
    --====================================================================
    FUNCTION FUN_GET_VALUECODE(P_VALUE IN VARCHAR2, P_COLUMN_NAME IN VARCHAR2)
      RETURN VARCHAR2 
      IS
      TYPE TBBL_ARRAY IS TABLE OF USER_TABLES.TABLE_NAME%TYPE INDEX BY BINARY_INTEGER;
      TYPE CUR_CURSOR IS REF CURSOR;

      X_ARRAY TBBL_ARRAY;
      X_CUR   CUR_CURSOR;
      X_NUM   NUMBER := 1;

      X_TEMP_VALUE_SQL VARCHAR2(1000);

      X_TEMP_CODE  VARCHAR2(1000);
    BEGIN
      X_TEMP_VALUE_SQL := &apos;SELECT DD.DTD_CODE  FROM DATA_DICTIONARY DD
                 WHERE DD.DTD_COLUMN_NAME = &apos;&apos;&apos; || P_COLUMN_NAME || &apos;&apos;&apos; AND 
                 DD.DTD_VALUE IN  
                 (&apos;&apos;&apos; || REPLACE(P_VALUE, &apos;|&apos;, &apos;&apos;&apos;,&apos;&apos;&apos;) || &apos;&apos;&apos;)&apos;;

      OPEN X_CUR FOR X_TEMP_VALUE_SQL;
      LOOP
        FETCH X_CUR
          INTO X_ARRAY(X_NUM);
        EXIT WHEN X_CUR%NOTFOUND;
        X_TEMP_CODE := X_TEMP_CODE || X_ARRAY(X_NUM);
        X_NUM := X_NUM + 1;
      END LOOP;
      CLOSE X_CUR;
      
      RETURN X_TEMP_CODE;
    EXCEPTION
      WHEN OTHERS THEN
        RETURN NULL;
    END;
 
 

有什么不好的地方请大家指教！！！