MySQL存储过程实践

对employees数据库建立存储过程

创建不含有输入输出变量的存储过程

DELIMITER // -- 设定语句结束分隔符
DROP PROCEDURE IF EXISTS GetEmployees; -- 如果该存储过程已经存在，则删除
CREATE PROCEDURE GetEmployees ( ) -- 创建不含有输入输出变量的存储过程
BEGIN
    SELECT
        * 
    FROM
        employees 
    LIMIT 3;
    
END // 
DELIMITER ; -- 重新设定语句结束分隔符，每次查询结束后都要将分隔符还原为；

 

创建带有一个输出变量的存储过程

DELIMITER // -- 设定语句结束分隔符
DROP PROCEDURE IF EXISTS GetTotalNum; -- 如果该存储过程已经存在，则删除

CREATE PROCEDURE GetTotalNum (OUT TotalNum INT )  -- 创建带有输出变量的存储过程
BEGIN
    SELECT
        count( * ) INTO TotalNum 
    FROM
        dept_emp;
    
END // 
DELIMITER ;-- 重新设定语句结束分隔符，每次查询结束后都要将分隔符还原为；

 

创建带有多个输出变量的存储过程 

DELIMITER // -- 设定语句结束分隔符
DROP PROCEDURE IF EXISTS GetSalary; -- 如果该存储过程已经存在，则删除

CREATE PROCEDURE GetSalary ( OUT salaryhigh INT, OUT salarylow INT, OUT salaryavg INT )  -- 创建不含有输入输出变量的存储过程
BEGIN
    SELECT
        Max( salary ) INTO salaryhigh 
    FROM
        salaries;
        
    SELECT
        Min( salary ) INTO salarylow 
    FROM
        salaries;
        
    SELECT
        Avg( salary ) INTO salaryavg 
    FROM
        salaries;
    
END // 
DELIMITER; -- 重新设定语句结束分隔符，每次查询结束后都要将分隔符还原为；

 

创建带有输入输出变量的存储过程

DELIMITER // -- 设定语句结束分隔符
DROP PROCEDURE IF EXISTS GetNumEachDept; -- 如果该存储过程已经存在，则删除
CREATE PROCEDURE GetNumEachDept ( IN dept CHAR(10), OUT NumDept INT )  -- 创建带有输入输出变量的存储过程
BEGIN
    SELECT
        count( * ) INTO NumDept 
    FROM
        dept_emp 
    WHERE
        dept_no = dept;
    
END// 
DELIMITER; -- 重新设定语句结束分隔符，每次查询结束后都要将分隔符还原为；

 

调用存储过程

CALL GetEmployees();

CALL GetSalary (@SalaryHigh, @SalaryLow, @SalaryAvg);
SELECT @Salaryhigh, @Salarylow, @Salaryavg;

CALL GetNumEachDept ( 'd004', @NumDept );
SELECT @NumDept;

CALL GetTotalNum(@TotalNum);
SELECT @TotalNum;

 

运行结果：

emp_no   birth_date    first_name  last_name  gender   hire_date
10001    1953-09-02    Georgi      Facello      M      1986-06-26
10002    1964-06-02    Bezalel     Simmel       F      1985-11-21
10003    1959-12-03    Parto       Bamford      M      1986-08-28

@Salaryhigh  @Salarylow   @Salaryavg
158220        38623            63811

@NumDept
73485

@TotalNum
331603

CALL GetEmployees()
> OK
> 时间: 0s

CALL GetSalary (@SalaryHigh, @SalaryLow, @SalaryAvg)
> OK
> 时间: 2.467s

SELECT @Salaryhigh, @Salarylow, @Salaryavg
> OK
> 时间: 0s

CALL GetNumEachDept ( 'd004', @NumDept )
> OK
> 时间: 0.03s

SELECT @NumDept
> OK
> 时间: 0s

CALL GetTotalNum(@TotalNum)
> OK
> 时间: 0.07s

SELECT @TotalNum
> OK
> 时间: 0s