MySQL Stored Procedure: ประกาศตัวแปรและคำนวณคณิตศาสตร์

สร้าง Stored Procedure

mysql> DELIMITER //
mysql> CREATE PROCEDURE declare_demo_sp()
BEGIN
    DECLARE Value1 INT;
    DECLARE Value2 INT;
    SET Value1 = 100;
    SET Value2 = 2000;
    SELECT Value1, Value2, Value1 * Value2 AS MultiplicationResult;
END //
mysql> DELIMITER ;

เรียกใช้ Procedure

mysql> CALL declare_demo_sp();

ผลลัพธ์:

+--------+--------+----------------------+
| Value1 | Value2 | MultiplicationResult |
+--------+--------+----------------------+
|    100 |   2000 |               200000 |
+--------+--------+----------------------+

องค์ประกอบ Stored Procedure

Variable Scope

• Local Variable (DECLARE): ใช้ได้เฉพาะใน BEGIN...END Block
• User Variable (@var): ใช้ได้ทั่ว Session
• System Variable (@@var): Global/Session Settings

ตัวอย่างเพิ่มเติม: Parameter และ Logic

CREATE PROCEDURE calc_bonus(IN salary DECIMAL(10,2), IN years INT, OUT bonus DECIMAL(10,2))
BEGIN
    DECLARE rate DECIMAL(3,2);
    
    IF years >= 10 THEN SET rate = 0.20;
    ELSEIF years >= 5 THEN SET rate = 0.15;
    ELSE SET rate = 0.10;
    END IF;
    
    SET bonus = salary * rate;
END //

Flow Control ใน Procedure

-- IF ELSEIF ELSE
IF condition THEN ... ELSEIF condition THEN ... ELSE ... END IF;

-- CASE
CASE value WHEN v1 THEN ... WHEN v2 THEN ... ELSE ... END CASE;

-- LOOP
loop_label: LOOP ... IF condition THEN LEAVE loop_label; END IF ... END LOOP;

-- WHILE
WHILE condition DO ... END WHILE;

-- REPEAT
REPEAT ... UNTIL condition END REPEAT;

จัดการ Error

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
    GET DIAGNOSTICS CONDITION 1 @msg = MESSAGE_TEXT;
    SELECT @msg AS Error;
END;