存储过程

# ----   无参存储过程  ------------
DELIMITER $$
CREATE PROCEDURE pro_insert_employees()
    BEGIN
        insert into employees (first_name,last_name) VALUES("1","1"),("2","2"),("3","3");
    END $$
DELIMITER ;    
    
CALL pro_insert_employees() ;

    

# ----  in 存储过程  ----------
DELIMITER $$
CREATE PROCEDURE pro_findEmployeesByid(IN id INT)
BEGIN
    SELECT * FROM employees WHERE employee_id=id;
END $$
DELIMITER ;

CALL pro_findEmployeesByid(101);


#  -----  OUT  存储过程  -----------
DELIMITER $$
CREATE PROCEDURE pro_countEmployees(OUT sumCount INT)
BEGIN
    SELECT COUNT(1) INTO sumCount
    FROM employees;
END $$
DELIMITER ;

CALL pro_countEmployees(@sumCount);

SELECT @sumCount;



#  -----   INOUT 存储过程   ----------------
DELIMITER $$
CREATE PROCEDURE pro_countByDepartmentId(IN departId INT ,OUT sumCount INT )
BEGIN
        SELECT COUNT(1) INTO sumCount
    FROM employees WHERE department_id=departId;
END $$
DELIMITER ;

CALL pro_countByDepartmentId(90,@sumCount);
SELECT @sumCount;