MySQL存储过程

一、MySQL存储过程

MySQL 存储过程是一段预编译的 SQL 代码，可以被多次调用，不同于简单的 SQL 命令，使用存储过程可以大大提高数据库的性能和效率。

1.1 步骤

下面是 MySQL 存储过程的使用步骤：

1.1.1 创建存储过程

在 MySQL 中可以使用 CREATE PROCEDURE 语句创建存储过程，定义存储过程的参数、SQL 语句和返回值。

示例代码：

CREATE PROCEDURE proc_name (IN arg1 VARCHAR(50), OUT arg2 INT)
BEGIN
  -- SQL CODE
END

其中，proc_name 为存储过程的名称，arg1 和 arg2 分别是输入参数和输出参数，在 BEGIN 和 END 之间编写 SQL 代码。

1.1.2 执行存储过程

使用 CALL 语句调用存储过程，并提供输入参数的值，可以获取存储过程的返回值。示例代码：

CALL proc_name(input_value, @output_value);
SELECT @output_value;

其中，input_value 是输入参数的值，@output_value为存储过程的输出参数。

1.1.3 修改存储过程

使用 ALTER PROCEDURE 语句修改存储过程的定义和内容。示例代码：

ALTER PROCEDURE proc_name (IN arg1 VARCHAR(50), OUT arg2 INT)
BEGIN
  -- UPDATED SQL CODE
END

其中，proc_name为需要修改的存储过程的名称。

1.1.4 删除存储过程

使用 DROP PROCEDURE 语句删除存储过程。示例代码：

DROP PROCEDURE proc_name;

其中，proc_name 为需要删除的存储过程的名称。

以上就是 MySQL 存储过程的基本使用步骤，可以根据实际需求和情况灵活运用。

1.2 抽奖案例

1.2.1 创建员工表

-- 创建员工表
CREATE TABLE employee
    ( emp_id    INTEGER NOT NULL PRIMARY KEY
    , emp_name  VARCHAR(50) NOT NULL
    , sex       VARCHAR(10) NOT NULL
    , dept_id   INTEGER NOT NULL
    , manager   INTEGER
    , hire_date DATE NOT NULL
    , job_id    INTEGER NOT NULL
    , salary    NUMERIC(8,2) NOT NULL
    , bonus     NUMERIC(8,2)
    , email     VARCHAR(100) NOT NULL
    , CONSTRAINT ck_emp_sex CHECK (sex IN ('男', '女'))
    , CONSTRAINT ck_emp_salary CHECK (salary > 0)
    , CONSTRAINT uk_emp_email UNIQUE (email)
    , CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES department(dept_id)
    , CONSTRAINT fk_emp_job FOREIGN KEY (job_id) REFERENCES job(job_id)
    , CONSTRAINT fk_emp_manager FOREIGN KEY (manager) REFERENCES employee(emp_id)
    ) ;

-- 创建索引
CREATE INDEX idx_emp_name ON employee(emp_name);
CREATE INDEX idx_emp_dept ON employee(dept_id);
CREATE INDEX idx_emp_job ON employee(job_id);
CREATE INDEX idx_emp_manager ON employee(manager);

1.2.2 插入模拟数据

INSERT INTO employee VALUES (1, '刘备', '男', 1, NULL, DATE('2000-01-01'), 1, 30000, 10000, 'liubei@shuguo.com');
INSERT INTO employee VALUES (2, '关羽', '男', 1, 1, DATE('2000-01-01'), 2, 26000, 10000, 'guanyu@shuguo.com');
INSERT INTO employee VALUES (3, '张飞', '男', 1, 1, DATE('2000-01-01'), 2, 24000, 10000, 'zhangfei@shuguo.com');
INSERT INTO employee VALUES (4, '诸葛亮', '男', 2, 1, DATE('2006-03-15'), 3, 24000, 8000, 'zhugeliang@shuguo.com');
INSERT INTO employee VALUES (5, '黄忠', '男', 2, 4, DATE('2008-10-25'), 4, 8000, NULL, 'huangzhong@shuguo.com');
INSERT INTO employee VALUES (6, '魏延', '男', 2, 4, DATE('2007-04-01'), 4, 7500, NULL, 'weiyan@shuguo.com');
INSERT INTO employee VALUES (7, '孙尚香', '女', 3, 1, DATE('2002-08-08'), 5, 12000, 5000, 'sunshangxiang@shuguo.com');
INSERT INTO employee VALUES (8, '孙丫鬟', '女', 3, 7, DATE('2002-08-08'), 6, 6000, NULL, 'sunyahuan@shuguo.com');
INSERT INTO employee VALUES (9, '赵云', '男', 4, 1, DATE('2005-12-19'), 7, 15000, 6000, 'zhaoyun@shuguo.com');
INSERT INTO employee VALUES (10, '廖化', '男', 4, 9, DATE('2009-02-17'), 8, 6500, NULL, 'liaohua@shuguo.com');
INSERT INTO employee VALUES (11, '关平', '男', 4, 9, DATE('2011-07-24'), 8, 6800, NULL, 'guanping@shuguo.com');
INSERT INTO employee VALUES (12, '赵氏', '女', 4, 9, DATE('2011-11-10'), 8, 6600, NULL, 'zhaoshi@shuguo.com');
INSERT INTO employee VALUES (13, '关兴', '男', 4, 9, DATE('2011-07-30'), 8, 7000, NULL, 'guanxing@shuguo.com');
INSERT INTO employee VALUES (14, '张苞', '男', 4, 9, DATE('2012-05-31'), 8, 6500, NULL, 'zhangbao@shuguo.com');
INSERT INTO employee VALUES (15, '赵统', '男', 4, 9, DATE('2012-05-03'), 8, 6000, NULL, 'zhaotong@shuguo.com');
INSERT INTO employee VALUES (16, '周仓', '男', 4, 9, DATE('2010-02-20'), 8, 8000, NULL, 'zhoucang@shuguo.com');
INSERT INTO employee VALUES (17, '马岱', '男', 4, 9, DATE('2014-09-16'), 8, 5800, NULL, 'madai@shuguo.com');
INSERT INTO employee VALUES (18, '法正', '男', 5, 2, DATE('2017-04-09'), 9, 10000, 5000, 'fazheng@shuguo.com');
INSERT INTO employee VALUES (19, '庞统', '男', 5, 18, DATE('2017-06-06'), 10, 4100, 2000, 'pangtong@shuguo.com');
INSERT INTO employee VALUES (20, '蒋琬', '男', 5, 18, DATE('2018-01-28'), 10, 4000, 1500, 'jiangwan@shuguo.com');
INSERT INTO employee VALUES (21, '黄权', '男', 5, 18, DATE('2018-03-14'), 10, 4200, NULL, 'huangquan@shuguo.com');
INSERT INTO employee VALUES (22, '糜竺', '男', 5, 18, DATE('2018-03-27'), 10, 4300, NULL, 'mizhu@shuguo.com');
INSERT INTO employee VALUES (23, '邓芝', '男', 5, 18, DATE('2018-11-11'), 10, 4000, NULL, 'dengzhi@shuguo.com');
INSERT INTO employee VALUES (24, '简雍', '男', 5, 18, DATE('2019-05-11'), 10, 4800, NULL, 'jianyong@shuguo.com');
INSERT INTO employee VALUES (25, '孙乾', '男', 5, 18, DATE('2018-10-09'), 10, 4700, NULL, 'sunqian@shuguo.com');

1.2.3 随机获取一条数据

SELECT name FROM employee ORDER BY RAND() LIMIT 1

1.2.4 创建中奖员工表

CREATE TABLE luck_emp(
  emp_id int primary key,
  emp_name varchar(50) NOT NULL, 
  prize varchar(10) NOT NULL);

中奖的员工信息存储到该表中。

1.2.5 使用存储函数实现抽奖

创建存储过程：

DELIMITER $$
CREATE PROCEDURE luck_draw(
  IN p_prize varchar(10),
  IN p_num   int)
BEGIN
  INSERT INTO luck_emp(emp_id, emp_name, prize)
  SELECT emp_id, emp_name, p_prize
  FROM employee
  WHERE emp_id NOT IN (SELECT emp_id FROM luck_emp)
  ORDER BY rand()
  LIMIT p_num;
  
  SELECT * FROM luck_emp;
END$$
-- 还原sql结束语句
DELIMITER ;

执行存储过程：

-- 抽1个一等奖
CALL luck_draw('一等奖', 1)

-- 抽2个二等奖
CALL luck_draw('二等奖', 2)

-- 抽3个三等奖
CALL luck_draw('三等奖', 3)

结果：

emp_id	emp_name	p_prize
23	邓芝	一等奖
14	张苞	二等奖
3	张飞	二等奖
7	孙尚香	三等奖
2	关羽	三等奖
9	赵云	三等奖