达梦数据库函数模板

达梦数据库函数语法和Oracle数据库函数语法相似

CREATE OR REPLACE FUNCTION HRCORE.ADD_DICTIONARY(TYPECODE VARCHAR(100),PID VARCHAR(40),DIC_VALUE VARCHAR2(255)) RETURN NUMBER
IS  pragma autonomous_transaction; 
VARNAME NUMBER := 0;
DICTIONARY_KEY_A NUMBER := 0;
DICTIONARY_KEY_B NUMBER := 0;
DIC_TYPE_NAME_A VARCHAR := '';
DIC_TYPE_NAME_B VARCHAR := '';
DIC_ID VARCHAR(72) := '';
BEGIN
    /*执行体*/
SELECT MAX(TO_NUMBER(D.DICTIONARY_KEY)) INTO DICTIONARY_KEY_A FROM HRCORE.HR_COMMON_DICTIONARY D WHERE D.TYPE_CODE = TYPECODE GROUP BY D.TYPE_NAME;
SELECT MAX(TO_NUMBER(D.DICTIONARY_KEY)) INTO DICTIONARY_KEY_B FROM HRCORE.HR_STAFF_DICTIONARY_CASCADE D WHERE D.TYPE_CODE = TYPECODE GROUP BY D.TYPE_NAME;
SELECT D.TYPE_NAME INTO DIC_TYPE_NAME_A FROM HRCORE.HR_COMMON_DICTIONARY D WHERE D.TYPE_CODE = TYPECODE GROUP BY D.TYPE_NAME;
SELECT D.TYPE_NAME INTO DIC_TYPE_NAME_B FROM HRCORE.HR_STAFF_DICTIONARY_CASCADE D WHERE D.TYPE_CODE = TYPECODE GROUP BY D.TYPE_NAME;

SELECT SUBSTR(SYS_GUID(),1) INTO DIC_ID FROM DUAL;
IF DICTIONARY_KEY_A <= DICTIONARY_KEY_B
THEN DICTIONARY_KEY_A := DICTIONARY_KEY_B;
END IF;
DICTIONARY_KEY_A := (DICTIONARY_KEY_A + 1);
PRINT('DICTIONARY_KEY_A = '||DICTIONARY_KEY_A);
PRINT('DICTIONARY_KEY_B = '||DICTIONARY_KEY_B);
VARNAME := DICTIONARY_KEY_A;
INSERT INTO HRCORE.HR_COMMON_DICTIONARY (ID,CREATE_TIME,UPDATE_TIME,TYPE_CODE,TYPE_NAME,
DICTIONARY_KEY,DICTIONARY_VALUE,PARENT_ID,SORT_NO,DELETE_FLAG
) VALUES (
DIC_ID,SYSDATE,SYSDATE,TYPECODE,DIC_TYPE_NAME_A,DICTIONARY_KEY_A,DIC_VALUE,PID,0,1
);
COMMIT;
INSERT INTO HRCORE.HR_STAFF_DICTIONARY_CASCADE (ID,CREATE_TIME,UPDATE_TIME,TYPE_CODE,TYPE_NAME,
DICTIONARY_KEY,DICTIONARY_VALUE,PARENT_ID,SORT_NO,DELETE_FLAG
) VALUES (
DIC_ID,SYSDATE,SYSDATE,TYPECODE,DIC_TYPE_NAME_B,DICTIONARY_KEY_A,DIC_VALUE,PID,0,1
);
COMMIT;

RETURN VARNAME;
END ADD_DICTIONARY;