oracle 转字典写法,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 || '|';

<

>

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(如：'001|010|110')+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 := 'SELECT DD.DTD_CODE FROM DATA_DICTIONARY DD

WHERE DD.DTD_COLUMN_NAME = ''' || P_COLUMN_NAME || ''' AND

DD.DTD_VALUE IN

(''' || REPLACE(P_VALUE, '|', ''',''') || ''')';

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;

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