ORACL可用SYS_REFCURSOR 返回结果集.

将以下转换为SQLSERVER 版本 CREATE OR REPLACE FUNCTION IMES.FN_GET_TRANSLATE_MSG ( TINPUT_CHAR VARCHAR2, --ErrorCodeMSG TINPUT_VALUE VARCHAR2, --产生Error的关键值 SN或者CartonNO，可以为空 TLine VARCHAR2, --产生Error的线别，可以为空 TSTATION_NAME VARCHAR2, --产生Error的站点，可以为空 TPROGRAM VARCHAR2, --产生Error的Pro或者程式，可以为空 TTIP VARCHAR2, --产生Error的电脑IP，可以为空 TEMP_NO VARCHAR2, --产生Error的人员工号，可以为空 TREMARK VARCHAR2 --产生Error的其他备注，可以为空 ) RETURN VARCHAR2 IS V_ReturnString VARCHAR2 (3000) := ''; CURSOR Params IS SELECT REGEXP_SUBSTR (TINPUT_CHAR, '[^,]+', 1, 1) t1, REGEXP_SUBSTR (TINPUT_CHAR, '[^,]+', 1, 2) t2, REGEXP_SUBSTR (TINPUT_CHAR, '[^,]+', 1, 3) t3, REGEXP_SUBSTR (TINPUT_CHAR, '[^,]+', 1, 4) t4, REGEXP_SUBSTR (TINPUT_CHAR, '[^,]+', 1, 5) t5, REGEXP_SUBSTR (TINPUT_CHAR, '[^,]+', 1, 6) t6, REGEXP_SUBSTR (TINPUT_CHAR, '[^,]+', 1, 7) t7, REGEXP_SUBSTR (TINPUT_CHAR, '[^,]+', 1, 8) t8, REGEXP_SUBSTR (TINPUT_CHAR, '[^,]+', 1, 9) t9, REGEXP_SUBSTR (TINPUT_CHAR, '[^,]+', 1, 10) t10, REGEXP_SUBSTR (TINPUT_CHAR, '[^,]+', 1, 11) t11 FROM DUAL; Param Params%ROWTYPE; V_COUNT NUMBER; V_MESSAGE_CODE IMES.M_ERROR_MSG.MESSAGE_CODE%TYPE; V_MSG_DESC1 IMES.M_ERROR_MSG.MESSAGE_DESC1%TYPE; V_NEXT_ACTION IMES.M_ERROR_MSG.NEXT_ACTION%TYPE; V_NEED_PROCESS IMES.M_ERROR_MSG.NEED_PROCESS%TYPE; V_NEED_ALERT IMES.M_ERROR_MSG.NEXT_ACTION%TYPE; V_NEED_LOG IMES.M_ERROR_MSG.NEED_LOG%TYPE; V_ALERT_MAIL IMES.M_ERROR_MSG.ALERT_MAIL%TYPE; LASTID NUMBER; V_GUID VARCHAR2 (60); CURSOR MsgDesc1Chars IS SELECT REGEXP_SUBSTR (V_MSG_DESC1, '[^$]+', 1, LEVEL, 'i') AS STR FROM DUAL CONNECT BY LEVEL <= 10; MsgDesc1Char MsgDesc1Chars%ROWTYPE; BEGIN /* Create by linlong 2021-11-3 传入信息，并根据设定翻译成输出信息的FUNCTION 传入信息样式：ErrorCode,Para1,Para2； ErrorCodeID,参数1，参数2，参数3...最多10个参数 */ OPEN Params; LOOP FETCH Params INTO Param; EXIT WHEN Params%NOTFOUND; --dbms_output.put_line('id is:' || Param.t1 ||' and param1 is:' || Param.t2); SELECT COUNT (1) INTO V_COUNT FROM IMES.M_ERROR_MSG WHERE MESSAGE_CODE = Param.t1 AND enabled = 'Y'; IF V_COUNT = 0 THEN EXIT; END IF; --根据ErrorCode获取ErrorCodeMSG SELECT MESSAGE_CODE, NVL (message_desc1, '.'), NVL (next_action, '.'), need_process, need_alert, ALERT_MAIL, NEED_LOG INTO V_MESSAGE_CODE, V_MSG_DESC1, V_NEXT_ACTION, V_NEED_PROCESS, V_NEED_ALERT, V_ALERT_MAIL, V_NEED_LOG FROM IMES.M_ERROR_MSG WHERE MESSAGE_CODE = Param.t1 AND enabled = 'Y'; --拼接message_desc1 OPEN MsgDesc1Chars; LOOP FETCH MsgDesc1Chars INTO MsgDesc1Char; EXIT WHEN MsgDesc1Chars%NOTFOUND; EXIT WHEN MsgDesc1Char.Str IS NULL; V_ReturnString := V_ReturnString || MsgDesc1Char.STR || (CASE WHEN MsgDesc1Chars%ROWCOUNT = 1 THEN Param.t2 WHEN MsgDesc1Chars%ROWCOUNT = 2 THEN Param.t3 WHEN MsgDesc1Chars%ROWCOUNT = 3 THEN Param.t4 WHEN MsgDesc1Chars%ROWCOUNT = 4 THEN Param.t5 WHEN MsgDesc1Chars%ROWCOUNT = 5 THEN Param.t6 WHEN MsgDesc1Chars%ROWCOUNT = 6 THEN Param.t7 WHEN MsgDesc1Chars%ROWCOUNT = 7 THEN Param.t8 WHEN MsgDesc1Chars%ROWCOUNT = 8 THEN Param.t9 WHEN MsgDesc1Chars%ROWCOUNT = 9 THEN Param.t10 WHEN MsgDesc1Chars%ROWCOUNT = 10 THEN Param.t11 END); END LOOP; CLOSE MsgDesc1Chars; --拼接next_action IF V_NEXT_ACTION = '.' OR V_NEXT_ACTION IS NULL THEN V_ReturnString := V_ReturnString; ELSE V_ReturnString := V_ReturnString || ' Next Action:' || V_NEXT_ACTION; END IF; END LOOP; CLOSE Params; IF V_ReturnString IS NULL THEN V_ReturnString := TINPUT_CHAR; END IF; RETURN V_MESSAGE_CODE || ' : ' || V_ReturnString; END; /