/**
 * @ Author: Marshall Telaumbanua
 * @ Create Time: 2023-06-07 19:52:33
 * @ Usage: CALL MWCONFIG.MERGE_SERVER_PORT(ADAPTOR_ID, PORT, SERVER, GROUP)
 */

CREATE OR REPLACE PROCEDURE MERGE_SERVER_PORT (
	IN ADAPTOR_ID VARCHAR(256),
	IN PORT INTEGER,
	IN SERVER VARCHAR(256),
	IN GROUP VARCHAR(256),
	IN MODIFIED_BY VARCHAR(64) DEFAULT CURRENT_USER,
	IN TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
LANGUAGE SQL
BEGIN
	DECLARE SQL_STMT VARCHAR(10000);

	SET SQL_STMT = 'MERGE INTO MWCONFIG.SERVER_PORT AS T ' ||
		'USING (VALUES (''' || ADAPTOR_ID || ''', ' || PORT || ', ''' || SERVER || ''', ''' || GROUP || ''')) AS S (ADAPTOR_ID, PORT, SERVER, "GROUP") ' ||
		'ON (T.ADAPTOR_ID = S.ADAPTOR_ID) ' ||
		'WHEN MATCHED THEN ' ||
		'    UPDATE SET ' ||
		'        T.PORT = S.PORT, ' ||
		'        T.SERVER = S.SERVER, ' ||
		'        T.GROUP = S.GROUP ' ||
		'WHEN NOT MATCHED THEN ' ||
		'    INSERT (ADAPTOR_ID, PORT, SERVER, "GROUP") ' ||
		'    VALUES (S.ADAPTOR_ID, S.PORT, S.SERVER, S.GROUP)';

	PREPARE STMT FROM SQL_STMT;
	EXECUTE STMT;
END
@