SQL 儲存程序（Stored Procedure）入門

SELECT customer_name FROM customers WHERE customer_id = 101;
SELECT order_total FROM orders WHERE customer_id = 101;
SELECT last_login FROM users WHERE customer_id = 101;

DELIMITER //
CREATE PROCEDURE GetCustomerDetails(IN customer_id INT)
BEGIN
    SELECT customer_name FROM customers WHERE customer_id = customer_id;
    SELECT order_total FROM orders WHERE customer_id = customer_id;
    SELECT last_login FROM users WHERE customer_id = customer_id;
END //
DELIMITER ;

CALL GetCustomerDetails(101);

SELECT customer_name FROM customers WHERE customer_id = 101;
-- ...
SELECT customer_name FROM customers WHERE customer_id = 202;
-- ...
SELECT customer_name FROM customers WHERE customer_id = 303;

CREATE PROCEDURE GetCustomerName(IN customer_id INT)
BEGIN
    SELECT customer_name FROM customers WHERE customer_id = customer_id;
END;

CALL GetCustomerName(101);

GRANT EXECUTE ON PROCEDURE GetCustomerDetails TO 'user1';
REVOKE SELECT, INSERT, UPDATE, DELETE ON customers FROM 'user1';

query = "SELECT * FROM users WHERE username = '" + user_input + "'";

CREATE PROCEDURE GetUser(IN user_name VARCHAR(50))
BEGIN
    SELECT * FROM users WHERE username = user_name;
END;

CALL GetUser('admin');

CREATE PROCEDURE TransferMoney(IN from_acc INT, IN to_acc INT, IN amount DECIMAL(10,2))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
    END;

    START TRANSACTION;
    UPDATE accounts SET balance = balance - amount WHERE account_id = from_acc;
    UPDATE accounts SET balance = balance + amount WHERE account_id = to_acc;
    COMMIT;
END;

DELIMITER //

CREATE PROCEDURE GetEmployeeCount()
BEGIN
    SELECT COUNT(*) AS TotalEmployees FROM employees;
END //

DELIMITER ;

CALL GetEmployeeCount();

DELIMITER //

CREATE PROCEDURE GetDepartmentEmployeeCount(IN department_id INT)
BEGIN
    SELECT COUNT(*) AS TotalEmployees
    FROM employees
    WHERE dept_id = department_id;
END //

DELIMITER ;

CALL GetDepartmentEmployeeCount(101);

DROP PROCEDURE IF EXISTS GetEmployeeCount;

DROP PROCEDURE GetEmployeeCount;

CREATE PROCEDURE GenerateSalesReport()
BEGIN
    SELECT department, SUM(sales_amount) AS TotalSales
    FROM sales
    GROUP BY department;
END;

CALL GenerateSalesReport();

CREATE PROCEDURE UpgradeVIP()
BEGIN
    UPDATE customers
    SET membership_level = 'VIP'
    WHERE total_spent > 10000;
END;

CALL UpgradeVIP();

DELIMITER //

CREATE PROCEDURE TransferMoney(IN from_acc INT, IN to_acc INT, IN amount DECIMAL(10,2))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK; -- 若發生錯誤，回滾交易
    END;

    START TRANSACTION;
    UPDATE accounts SET balance = balance - amount WHERE account_id = from_acc;
    UPDATE accounts SET balance = balance + amount WHERE account_id = to_acc;
    COMMIT;
END //

DELIMITER ;

CALL TransferMoney(101, 202, 500);

DELIMITER $$

CREATE PROCEDURE GetEmployee(IN emp_id INT)
BEGIN
    SELECT * FROM employees WHERE employee_id = emp_id;
END $$

DELIMITER ;

CREATE OR REPLACE FUNCTION GetEmployee(emp_id INT)
RETURNS TABLE(employee_id INT, name TEXT, salary DECIMAL) AS $$
BEGIN
    RETURN QUERY SELECT * FROM employees WHERE employee_id = emp_id;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE GetEmployee (emp_id IN NUMBER)
IS
    emp_name VARCHAR2(100);
BEGIN
    SELECT name INTO emp_name FROM employees WHERE employee_id = emp_id;
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_name);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employee found.');
END;
/

CREATE PROCEDURE GetEmployee
    @emp_id INT
AS
BEGIN
    SELECT * FROM employees WHERE employee_id = @emp_id;
END;

CREATE TABLE salary_cache (
    cache_id INT PRIMARY KEY AUTO_INCREMENT,
    total_salary DECIMAL(10,2),
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER $$

CREATE PROCEDURE GetCachedTotalSalary()
BEGIN
    DECLARE cached_salary DECIMAL(10,2);

    -- 檢查快取是否有數據
    SELECT total_salary INTO cached_salary FROM salary_cache ORDER BY last_updated DESC LIMIT 1;

    -- 若無快取，重新計算並存入
    IF cached_salary IS NULL THEN
        INSERT INTO salary_cache (total_salary)
        SELECT SUM(salary) FROM employees;
    END IF;

    -- 回傳快取的薪資總額
    SELECT total_salary FROM salary_cache ORDER BY last_updated DESC LIMIT 1;
END $$

DELIMITER ;

import redis
import mysql.connector

# 連接 Redis
cache = redis.Redis(host='localhost', port=6379, db=0)

# 檢查 Redis 快取
cached_result = cache.get('total_salary')

if cached_result:
    print("使用快取結果：", cached_result)
else:
    # 連接 MySQL 並執行儲存程序
    db = mysql.connector.connect(user='root', password='password', database='company')
    cursor = db.cursor()
    cursor.execute("CALL GetTotalSalary(@salary); SELECT @salary;")
    result = cursor.fetchall()[0][0]

    # 將結果存入 Redis
    cache.setex('total_salary', 3600, result)  # 設定 1 小時快取
    print("計算結果：", result)