الإجراءات المخزنة (Stored Procedures)

DELIMITER //

CREATE PROCEDURE procedure_name()
BEGIN
    -- أوامر SQL هنا
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE get_all_employees()
BEGIN
    SELECT 
        employee_id,
        first_name,
        last_name,
        email,
        department
    FROM employees
    ORDER BY last_name;
END //

DELIMITER ;

-- استدعاء الإجراء
CALL get_all_employees();

DELIMITER //

CREATE PROCEDURE get_employees_by_department(IN dept_name VARCHAR(50))
BEGIN
    SELECT 
        employee_id,
        first_name,
        last_name,
        email,
        salary
    FROM employees
    WHERE department = dept_name
    ORDER BY last_name;
END //

DELIMITER ;

-- استدعاء الإجراء مع تمرير قيمة
CALL get_employees_by_department('IT');
CALL get_employees_by_department('Sales');

DELIMITER //

CREATE PROCEDURE count_employees_in_department(
    IN dept_name VARCHAR(50),
    OUT emp_count INT
)
BEGIN
    SELECT COUNT(*) INTO emp_count
    FROM employees
    WHERE department = dept_name;
END //

DELIMITER ;

-- استدعاء الإجراء
CALL count_employees_in_department('IT', @total);

-- عرض النتيجة
SELECT @total AS total_employees;

DELIMITER //

CREATE PROCEDURE increase_salary(INOUT salary DECIMAL(10, 2), IN percentage DECIMAL(5, 2))
BEGIN
    SET salary = salary + (salary * percentage / 100);
END //

DELIMITER ;

-- استخدام الإجراء
SET @current_salary = 5000;
CALL increase_salary(@current_salary, 10);
SELECT @current_salary; -- النتيجة: 5500

DELIMITER //

CREATE PROCEDURE get_department_stats(
    IN dept_name VARCHAR(50),
    OUT total_employees INT,
    OUT avg_salary DECIMAL(10, 2),
    OUT max_salary DECIMAL(10, 2)
)
BEGIN
    SELECT 
        COUNT(*),
        AVG(salary),
        MAX(salary)
    INTO total_employees, avg_salary, max_salary
    FROM employees
    WHERE department = dept_name;
END //

DELIMITER ;

-- استدعاء الإجراء
CALL get_department_stats('IT', @total, @avg, @max);

-- عرض النتائج
SELECT @total AS total, @avg AS average, @max AS maximum;

DELIMITER //

CREATE PROCEDURE calculate_bonus(IN emp_id INT, OUT bonus DECIMAL(10, 2))
BEGIN
    DECLARE emp_salary DECIMAL(10, 2);
    DECLARE years_of_service INT;
    DECLARE bonus_percentage DECIMAL(5, 2);
    
    -- الحصول على راتب الموظف وسنوات الخدمة
    SELECT 
        salary,
        YEAR(CURRENT_DATE) - YEAR(hire_date)
    INTO emp_salary, years_of_service
    FROM employees
    WHERE employee_id = emp_id;
    
    -- حساب نسبة المكافأة بناءً على سنوات الخدمة
    IF years_of_service < 2 THEN
        SET bonus_percentage = 5;
    ELSEIF years_of_service < 5 THEN
        SET bonus_percentage = 10;
    ELSEIF years_of_service < 10 THEN
        SET bonus_percentage = 15;
    ELSE
        SET bonus_percentage = 20;
    END IF;
    
    -- حساب المكافأة
    SET bonus = emp_salary * bonus_percentage / 100;
END //

DELIMITER ;

-- استخدام الإجراء
CALL calculate_bonus(123, @employee_bonus);
SELECT @employee_bonus;

DELIMITER //

CREATE PROCEDURE categorize_employee(IN emp_id INT, OUT category VARCHAR(50))
BEGIN
    DECLARE emp_salary DECIMAL(10, 2);
    
    SELECT salary INTO emp_salary
    FROM employees
    WHERE employee_id = emp_id;
    
    IF emp_salary < 3000 THEN
        SET category = 'Junior';
    ELSEIF emp_salary < 6000 THEN
        SET category = 'Mid-Level';
    ELSEIF emp_salary < 10000 THEN
        SET category = 'Senior';
    ELSE
        SET category = 'Executive';
    END IF;
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE get_discount_rate(IN customer_type VARCHAR(20), OUT discount DECIMAL(5, 2))
BEGIN
    SET discount = CASE customer_type
        WHEN 'VIP' THEN 20.00
        WHEN 'Premium' THEN 15.00
        WHEN 'Regular' THEN 10.00
        WHEN 'New' THEN 5.00
        ELSE 0.00
    END;
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE generate_numbers(IN max_num INT)
BEGIN
    DECLARE counter INT DEFAULT 1;
    
    DROP TABLE IF EXISTS temp_numbers;
    CREATE TEMPORARY TABLE temp_numbers (num INT);
    
    WHILE counter <= max_num DO
        INSERT INTO temp_numbers VALUES (counter);
        SET counter = counter + 1;
    END WHILE;
    
    SELECT * FROM temp_numbers;
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE calculate_factorial(IN num INT, OUT result BIGINT)
BEGIN
    DECLARE counter INT DEFAULT 1;
    SET result = 1;
    
    REPEAT
        SET result = result * counter;
        SET counter = counter + 1;
    UNTIL counter > num
    END REPEAT;
END //

DELIMITER ;

-- حساب 5! (5 factorial)
CALL calculate_factorial(5, @fact);
SELECT @fact; -- النتيجة: 120

DELIMITER //

CREATE PROCEDURE sum_to_n(IN n INT, OUT total INT)
BEGIN
    DECLARE counter INT DEFAULT 1;
    SET total = 0;
    
    my_loop: LOOP
        SET total = total + counter;
        SET counter = counter + 1;
        
        IF counter > n THEN
            LEAVE my_loop;
        END IF;
    END LOOP;
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE safe_insert_employee(
    IN emp_name VARCHAR(100),
    IN emp_email VARCHAR(100),
    OUT success BOOLEAN,
    OUT error_message VARCHAR(255)
)
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        SET success = FALSE;
        SET error_message = 'خطأ في إدراج الموظف';
    END;
    
    DECLARE CONTINUE HANDLER FOR 1062  -- Duplicate entry error
    BEGIN
        SET success = FALSE;
        SET error_message = 'البريد الإلكتروني موجود مسبقاً';
    END;
    
    SET success = TRUE;
    SET error_message = '';
    
    INSERT INTO employees (first_name, email)
    VALUES (emp_name, emp_email);
    
    IF success THEN
        SET error_message = 'تم إدراج الموظف بنجاح';
    END IF;
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE create_order(
    IN p_customer_id INT,
    IN p_product_id INT,
    IN p_quantity INT,
    OUT p_order_id INT,
    OUT p_success BOOLEAN,
    OUT p_message VARCHAR(255)
)
BEGIN
    DECLARE v_product_price DECIMAL(10, 2);
    DECLARE v_stock_quantity INT;
    DECLARE v_total_amount DECIMAL(10, 2);
    
    -- معالج للأخطاء
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_success = FALSE;
        SET p_message = 'حدث خطأ أثناء إنشاء الطلب';
        SET p_order_id = 0;
    END;
    
    -- بدء المعاملة
    START TRANSACTION;
    
    -- التحقق من وجود المنتج والكمية المتاحة
    SELECT price, stock_quantity 
    INTO v_product_price, v_stock_quantity
    FROM products
    WHERE product_id = p_product_id;
    
    -- التحقق من توفر الكمية
    IF v_stock_quantity < p_quantity THEN
        SET p_success = FALSE;
        SET p_message = 'الكمية المطلوبة غير متوفرة في المخزون';
        SET p_order_id = 0;
        ROLLBACK;
    ELSE
        -- حساب الإجمالي
        SET v_total_amount = v_product_price * p_quantity;
        
        -- إدراج الطلب
        INSERT INTO orders (customer_id, order_date, status, total_amount)
        VALUES (p_customer_id, NOW(), 'pending', v_total_amount);
        
        SET p_order_id = LAST_INSERT_ID();
        
        -- إدراج تفاصيل الطلب
        INSERT INTO order_items (order_id, product_id, quantity, unit_price)
        VALUES (p_order_id, p_product_id, p_quantity, v_product_price);
        
        -- تحديث المخزون
        UPDATE products
        SET stock_quantity = stock_quantity - p_quantity
        WHERE product_id = p_product_id;
        
        -- تأكيد المعاملة
        COMMIT;
        
        SET p_success = TRUE;
        SET p_message = 'تم إنشاء الطلب بنجاح';
    END IF;
END //

DELIMITER ;

-- استخدام الإجراء
CALL create_order(1, 101, 2, @order_id, @success, @message);
SELECT @order_id, @success, @message;

-- عرض جميع الإجراءات في قاعدة البيانات الحالية
SHOW PROCEDURE STATUS WHERE Db = DATABASE();

-- عرض تعريف إجراء معين
SHOW CREATE PROCEDURE get_all_employees;

-- من جداول النظام
SELECT 
    ROUTINE_NAME,
    ROUTINE_TYPE,
    CREATED,
    LAST_ALTERED
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = DATABASE()
    AND ROUTINE_TYPE = 'PROCEDURE';

-- حذف إجراء
DROP PROCEDURE get_all_employees;

-- حذف آمن
DROP PROCEDURE IF EXISTS get_all_employees;