/**
 * @ Author: Marshall Telaumbanua
 * @ Create Time: 2023-06-07 19:52:33
 * @ Usage: CALL MWCONFIG.MERGE_DTREE(GROUP, PATH, VALUE, MODULE)
 */

CREATE OR REPLACE PROCEDURE MERGE_DTREE (
	IN GROUP VARCHAR(256),
	IN PATH VARCHAR(1024),
	IN VALUE VARCHAR(512),
	IN MODULE VARCHAR(256),
	IN MODIFIED_BY VARCHAR(64) DEFAULT CURRENT USER,
	IN TIMESTAMP TIMESTAMP DEFAULT CURRENT TIMESTAMP
)
BEGIN
	DECLARE SQL_STMT VARCHAR(4000);

	SET SQL_STMT = 'MERGE INTO MWCONFIG.DTREE AS T ' ||
		'USING (VALUES (''' || GROUP || ''', ''' || PATH || ''', ''' || VALUE || ''', ''' || MODULE || ''')) ' ||
		'AS S (GROUP, PATH, VALUE, MODULE) ' ||
		'ON (T.GROUP = S.GROUP AND T.PATH = S.PATH) ' ||
		'WHEN MATCHED THEN ' ||
		'UPDATE SET T.VALUE = S.VALUE, T.MODULE = S.MODULE, T.MODIFIED_BY = CURRENT_USER, T.TIMESTAMP = CURRENT_TIMESTAMP ' ||
		'WHEN NOT MATCHED THEN ' ||
		'INSERT (GROUP, PATH, VALUE, MODULE, MODIFIED_BY, TIMESTAMP) ' ||
		'VALUES (S.GROUP, S.PATH, S.VALUE, S.MODULE, CURRENT_USER, CURRENT_TIMESTAMP)';

	PREPARE STMT FROM SQL_STMT;
	EXECUTE STMT;
END
@