[MySQL]MySQL编程——存储过程篇(baidu comate试用)

一、建立数据库shopdb(utf8字符集，utf8_general_ci排序规则)

二、DDL语句

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    createDate DATETIME NOT NULL,
    userName VARCHAR(255) NOT NULL,
    phone VARCHAR(15),
    age INT,
    sex ENUM('M', 'F', 'O'),
    introduce TEXT
);

三、DML

INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES ('2023-04-01 10:00:00', '张三', '13800138000', 20, 'M', '来自北京，性格开朗。');
INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES ('2023-04-02 11:20:00', '李四', '13900139000', 21, 'M', '爱好篮球，运动健将。');
INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES ('2023-04-03 09:30:00', '王五', '13700137000', 19, 'F', '擅长绘画，多次获奖。');
INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES ('2023-04-04 14:45:00', '赵六', '13600136000', 22, 'M', '热衷于社会公益活动。');
INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES ('2023-04-05 16:10:00', '孙七', '13500135000', 20, 'F', '文静内敛，喜欢阅读。');
INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES ('2023-04-06 12:30:00', '周八', '13400134000', 21, 'M', '热衷于编程，技术宅。');
INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES ('2023-04-07 08:00:00', '吴九', '13300133000', 19, 'F', '音乐才女，擅长多种乐器。');

四、存储过程DQL

--创建存储过程函数

-- 设置分隔符为 //，以便在存储过程中使用多个分号
DELIMITER //
-- 创建一个名为 GetIntroduceByUserName 的存储过程
-- 该存储过程接受一个名为 input_userName 的输入参数，类型为 VARCHAR(255)
CREATE PROCEDURE GetIntroduceByUserName(IN input_userName VARCHAR(255))
BEGIN
    -- 从 student 表中选择 introduce 列
    -- 这里的 WHERE 子句用于过滤结果，只返回 userName 列等于输入参数 input_userName 的记录
    SELECT introduce
    FROM student
    WHERE userName = input_userName;
END //
-- 将分隔符重新设置回默认的分号 ;
DELIMITER ;

--调用存储过程函数
call GetIntroduceByUserName;

PS:所示代码皆由 baidu comate 生成，用于学习参考。