oracle字典 实现查找和转化的两个function
来源：互联网 发布：简单编程 编辑：程序博客网 时间：2024/06/06 06:55
字典表结构如下：
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;
有什么不好的地方请大家指教！！！
