MySQL: شرح مفصل للإجراءات المخزنة والوظائف في MySQL

CREATE PROCEDURE sp_dept_salary_stats()
BEGIN
    SELECT department, SUM(salary) AS total_salary, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department;
END;

-- Call
CALL sp_dept_salary_stats();

-- Create a Stored Procedure
DELIMITER //
CREATE PROCEDURE sp_get_user(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

-- Call
CALL sp_get_user(1);

DELIMITER //
CREATE PROCEDURE sp_count_users(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM users;
END //
DELIMITER ;

-- Call
CALL sp_count_users(@total);
SELECT @total;

DELIMITER //
CREATE PROCEDURE sp_double(INOUT num INT)
BEGIN
    SET num = num * 2;
END //
DELIMITER ;

-- Call
SET @num = 5;
CALL sp_double(@num);
SELECT @num;  -- 10

DELIMITER //
CREATE PROCEDURE sp_variable_demo()
BEGIN
    DECLARE total INT DEFAULT 0;
    DECLARE avg_salary DECIMAL(10,2);
    DECLARE user_name VARCHAR(50);
    
    -- Assignment
    SELECT COUNT(*) INTO total FROM employees;
    SELECT AVG(salary) INTO avg_salary FROM employees;
    
    -- Using Variables
    SELECT total, avg_salary;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE sp_check_salary(IN emp_id INT)
BEGIN
    DECLARE emp_salary DECIMAL(10,2);
    
    SELECT salary INTO emp_salary FROM employees WHERE id = emp_id;
    
    IF emp_salary > 10000 THEN
        SELECT 'High salary' AS level;
    ELSEIF emp_salary > 5000 THEN
        SELECT 'Medium salary' AS level;
    ELSE
        SELECT 'Low salary' AS level;
    END IF;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE sp_order_status(IN order_id INT)
BEGIN
    DECLARE order_status VARCHAR(20);
    
    SELECT status INTO order_status FROM orders WHERE id = order_id;
    
    CASE order_status
        WHEN 'pending' THEN SELECT 'Order is pending' AS message;
        WHEN 'paid' THEN SELECT 'Order is paid' AS message;
        WHEN 'shipped' THEN SELECT 'Order is shipped' AS message;
        ELSE SELECT 'Unknown status' AS message;
    END CASE;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE sp_sum_1_to_n(IN n INT, OUT total INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    SET total = 0;
    
    WHILE i <= n DO
        SET total = total + i;
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

-- Call
CALL sp_sum_1_to_n(100, @total);
SELECT @total;  -- 5050

DELIMITER //
CREATE PROCEDURE sp_list_users()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE user_name VARCHAR(50);
    DECLARE user_email VARCHAR(100);
    
    -- Declare cursor
    DECLARE cur_users CURSOR FOR 
        SELECT username, email FROM users;
    
    -- End of cursor handler
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- Open cursor
    OPEN cur_users;
    
    -- Iterate
    read_loop: LOOP
        FETCH cur_users INTO user_name, user_email;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SELECT user_name, user_email;
    END LOOP;
    
    -- Close cursor
    CLOSE cur_users;
END //
DELIMITER ;

DELIMITER //
CREATE FUNCTION fn_calc_bonus(salary DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE bonus DECIMAL(10,2);
    
    IF salary > 10000 THEN
        SET bonus = salary * 0.2;
    ELSEIF salary > 5000 THEN
        SET bonus = salary * 0.1;
    ELSE
        SET bonus = salary * 0.05;
    END IF;
    
    RETURN bonus;
END //
DELIMITER ;

-- Using Functions
SELECT name, salary, fn_calc_bonus(salary) AS bonus FROM employees;

-- View All Stored Procedures
SHOW PROCEDURE STATUS WHERE Db = 'mydb';

-- View the CREATE statement
SHOW CREATE PROCEDURE sp_get_user;

-- Delete a Stored Procedure
DROP PROCEDURE IF EXISTS sp_get_user;

-- Delete Function
DROP FUNCTION IF EXISTS fn_calc_bonus;