-- 创建带OUT参数的存储过程
DELIMITER //
CREATE PROCEDURE get_employee_count(OUT emp_count INT)
BEGIN
    SELECT COUNT(*) INTO emp_count FROM employees;
END //
DELIMITER ;

-- 调用存储过程并获取OUT参数值
CALL get_employee_count(@count);
SELECT @count AS employee_count;

-- 创建返回结果集的存储过程
DELIMITER //
CREATE PROCEDURE get_employees_by_dept(IN dept_id INT)
BEGIN
    SELECT * FROM employees WHERE department_id = dept_id;
END //
DELIMITER ;

-- 调用存储过程获取结果集
CALL get_employees_by_dept(10);

-- 创建存储函数
DELIMITER //
CREATE FUNCTION calculate_bonus(emp_id INT) 
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE bonus DECIMAL(10,2);
    SELECT salary * 0.1 INTO bonus FROM employees WHERE employee_id = emp_id;
    RETURN bonus;
END //
DELIMITER ;

-- 调用存储函数
SELECT calculate_bonus(1001);

-- 确保正确声明OUT参数
CREATE PROCEDURE proc_name(OUT param_name param_type)

-- 调用时使用变量接收
CALL proc_name(@var);
SELECT @var;

-- 添加错误处理
BEGIN
    DECLARE result INT DEFAULT NULL;
    SELECT id INTO result FROM table WHERE condition;
    IF result IS NULL THEN
        SET result = 0; -- 默认值
    END IF;
    RETURN result;
END