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