/**
 * @ Author: Marshall Telaumbanua
 * @ Create Time: 2023-06-07 19:52:33
 * @ Usage: CALL MWCONFIG.MERGE_MAPPING_GROUP_LINE(MAPPING_ID, MAPPING_GROUP_ID, NAME, TEXT, SEQ)
 */

CREATE OR REPLACE PROCEDURE MERGE_MAPPING_GROUP_LINE (
	IN MAPPING_ID VARCHAR(256),
	IN MAPPING_GROUP_ID BIGINT,
	IN NAME VARCHAR(256),
	IN TEXT CLOB,
	IN SEQ INTEGER
)
BEGIN
	DECLARE SQL_STMT VARCHAR(10000);

	SET SQL_STMT = 'MERGE INTO MWCONFIG.MAPPING_GROUP_LINE AS T ' ||
		'USING (VALUES (''' || MAPPING_ID || ''', ' || MAPPING_GROUP_ID || ', ''' || NAME || ''', ''' || TEXT || ''', ' ||
		CASE
			WHEN SEQ IS NULL THEN 'NULL'
			ELSE CAST(SEQ AS VARCHAR)
		END || ')) AS S (MAPPING_ID, MAPPING_GROUP_ID, NAME, TEXT, SEQ) ' ||
		'ON (T.MAPPING_ID = S.MAPPING_ID AND T.MAPPING_GROUP_ID = S.MAPPING_GROUP_ID AND T.NAME = S.NAME) ' ||
		'WHEN MATCHED THEN ' ||
		'UPDATE SET ' ||
		'T.TEXT = S.TEXT, ' ||
		'T.SEQ = S.SEQ ' ||
		'WHEN NOT MATCHED THEN ' ||
		'INSERT (MAPPING_ID, MAPPING_GROUP_ID, NAME, TEXT, SEQ) ' ||
		'VALUES (S.MAPPING_ID, S.MAPPING_GROUP_ID, S.NAME, S.TEXT, S.SEQ)';

	PREPARE STMT FROM SQL_STMT;
	EXECUTE STMT;
END
@