ตอนที่ 3 : การ Print และแสดงผลบน Stored Procedure (MySQL : Stored Procedure)

SELECT 'String or Variable'

DROP PROCEDURE IF EXISTS getCustomer;

DELIMITER //
CREATE PROCEDURE getCustomer()
BEGIN

	DECLARE x,y INT DEFAULT 0;
        
	SELECT COUNT(*) INTO x FROM customer;
	
	SELECT COUNT(*) INTO y FROM customer WHERE COUNTRY_CODE = 'US';
        
	SELECT concat('x =  ', x),concat('y =  ', y);

END //

DELIMITER ;

CALL getCustomer();

CREATE TABLE IF NOT EXISTS `stored_log` (
  `log_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `connection_id` int(11) NOT NULL,
  `message` varchar(500) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP PROCEDURE IF EXISTS writeLog;

DELIMITER //
CREATE PROCEDURE writeLog(IN sLogMsg VARCHAR(500))
BEGIN
	INSERT INTO stored_log (connection_id,message) VALUES (connection_id(),sLogMsg);
END //

DELIMITER ;

CALL writeLog('Step 1');

DROP PROCEDURE IF EXISTS getCustomer;

DELIMITER //
CREATE PROCEDURE getCustomer()
BEGIN

	DECLARE x,y INT DEFAULT 0;

	CALL writeLog('Step 1');
        
	SELECT COUNT(*) INTO x FROM customer;
        
	CALL writeLog(concat('Step 2 x=',x));
	
	SELECT COUNT(*) INTO y FROM customer WHERE COUNTRY_CODE = 'US';
        
	CALL writeLog(concat('Step 3 y=',y));

END //

DELIMITER ;

CALL getCustomer();