/**
 * @ Author: Marshall Telaumbanua
 * @ Create Time: 2023-06-07 19:52:33
 * @ Usage: CALL MWCONFIG.MERGE_HOST(ID, HOST, USERNAME, PASSWORD, CONTEXT)
 */

CREATE OR REPLACE PROCEDURE MERGE_HOST (
	IN ID VARCHAR(256),
	IN HOST VARCHAR(256),
	IN USERNAME VARCHAR(64),
	IN PASSWORD VARCHAR(256),
	IN CONTEXT VARCHAR(1024),
	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.HOST AS T ' ||
		'USING (VALUES (''' || ID || ''', ''' || HOST || ''', ''' || USERNAME || ''', ''' || PASSWORD || ''', ''' || CONTEXT || ''', ''' || MODIFIED_BY || ''', ''' || TIMESTAMP || ''')) ' ||
		'AS S (ID, HOST, USERNAME, PASSWORD, CONTEXT, MODIFIED_BY, TIMESTAMP) ' ||
		'ON (T.ID = S.ID) ' ||
		'WHEN MATCHED THEN ' ||
		'UPDATE SET T.HOST = S.HOST, T.USERNAME = S.USERNAME, T.PASSWORD = S.PASSWORD, T.CONTEXT = S.CONTEXT, T.MODIFIED_BY = S.MODIFIED_BY, T.TIMESTAMP = S.TIMESTAMP ' ||
		'WHEN NOT MATCHED THEN ' ||
		'INSERT (ID, HOST, USERNAME, PASSWORD, CONTEXT, MODIFIED_BY, TIMESTAMP) ' ||
		'VALUES (S.ID, S.HOST, S.USERNAME, S.PASSWORD, S.CONTEXT, S.MODIFIED_BY, S.TIMESTAMP)';

	PREPARE STMT FROM SQL_STMT;
	EXECUTE STMT;
END
@