携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第16天，点击查看活动详情

前言

上篇我们学习了MySQL中的存储函数与存储过程的修改删除与查看，以及优缺点。有兴趣的小伙伴可以阅读(# MySQL学习-存储过程之修改删除以及优缺点)。
下面针对MySQL中的存储过程与函数做一些练习。

创建数据库

创建数据库test_pro。

CREATE DATABASE test_pro;

USE test_pro;

创建表

创建表admin。增加用户名与密码字段。

CREATE TABLE admin(
id INT PRIMARY KEY AUTO_INCREMENT,
uesr_name VARCHAR(15) NOT NULL,
pwd VARCHAR(25) NOT NULL
);

练习一

创建存储过程insert_user()，实现传入用户名和密码，插入到admin表中。

分析： 需要传入参数，这里使用IN传参。

DELIMITER $
CREATE PROCEDURE insert_user(IN USER VARCHAR(15), IN pwd VARCHAR(25))
BEGIN
    INSERT INTO admin(uesr_name, pwd)
    VALUES (USER, pwd);
END $
DELIMITER ;

调用存储过程插入数据。

CALL insert_user('xiaoming', '123');

SELECT *
FROM admin

id	uesr_name	pwd
1	xiaoming	123

从结果中可以看出，插入数据成功。

练习二

创建存储过程get_phone()，实现传入学生编号，返回学生姓名和电话。

首先创建一张表students。并插入数据

CREATE TABLE students(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(15) NOT NULL,
phone VARCHAR(15) UNIQUE,
birth DATE
);

INSERT INTO students(name, phone, birth)
VALUES ('xiaoming', '123', 2000-01-02),
('xiaolan', '1234', 2001-05-02),
('xiaoqiang', '12345', 2000-09-02),

创建存储过程，使用IN， OUT。

DELIMITER $
CREATE PROCEDURE get_phone(IN id INT, IN name VARCHAR(15), OUT phone VARCHAR(15))
BEGIN
    SELECT s.name, s.phone INTO name, phone
    FROM students s
    WHERE s.id = id;
END $
DELIMITER ;

调用存储过程，传入参数。

CALL get_phone(1, @name, @phone);
SELECT @name, @phone

@name	@phone
xiaoming	123

练习三

创建存储过程date_diff(),实现传入日期，返回日期间隔大小。

DELIMITER $
CREATE PROCEDURE date_diff(IN birth1 DATE, IN birth2 DATE, OUT diff_date INT)
BEGIN
    SELECT DATEDIFF(birth1, birth2) INTO diff_date;
END $
DELIMITER ;

调用存储过程

SET @birth1 = '1992-09-08';
SET @birth2 = '1992-10-30';

CALL date_diff(@birth1, @birth2, @diff_date);
SELECT @diff_date;

@diff_date
-52

练习四

创建带INOUT模式参数的存储过程，传入a和b两个值，最终a和b都翻倍并返回。

DELIMITER $
CREATE PROCEDURE add_double(INOUT a INT, INOUT b INT)
BEGIN
    SET a = a * 2;
    SET b = b * 2;
END $
DELIMITER ;

调用存储过程。

SET @a = 3, @b = 5;
CALL add_double(@a, @b);
SELECT @a, @b;

@a	@b
6	10

练习五

删除练习三的存储过程。

DROP PROCEDURE IF_EXISTS date_diff;

练习六

查看练习四的存储过程信息。

SHOW TABLE PROCEDURE add_double;

或者

SHOW PROCEDURE STATUS LIKE 'add_%';

练习七

创建函数get_count(), 返回员工个数。

DELIMITER $
CREATE FUNCTION get_count()
RETURNS INT
BEGIN
    RETURN (
        SELECT COUNT(*)
        FROM employees
    );
END $
DELIMITER ;

调用函数。

SELECT get_count();

get_count()
3

练习八

创建函数ename_salary(),根据员工的姓名，返回他的工资。

DELIMITER $
CREATE FUNCTION ename_salary(emp_name VARCHAR(15))
RETURNS DOUBLE
BEGIN
    RETURN (
        SELECT salary
        FROM employees
        WHERE name = emp_name
    );
END $
DELIMITER ;

调用函数。

SELECT ename_salary('xiaoming');

ename_salary('xiaoming')
3400

今天先学习到这里，明天继续。