MySQL創建儲存過程函數

DDL

CREATE TABLE `student` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '學號',`createDate` datetime DEFAULT NULL COMMENT '創建時間',`modifyDate` datetime DEFAULT NULL COMMENT '修改時間',`userName` varchar(30) NOT NULL COMMENT '學生名稱',`pwd` varchar(36) DEFAULT NULL COMMENT '密碼',`phone` varchar(11) DEFAULT NULL COMMENT '手機號',`age` tinyint(3) unsigned DEFAULT NULL COMMENT '年齡',`sex` char(2) DEFAULT '男' COMMENT '性別',`className` varchar(20) DEFAULT NULL,`addRess` varchar(255) DEFAULT NULL COMMENT '地址',`introduce` varchar(255) DEFAULT NULL COMMENT '簡介',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `course` (`id` int(11) NOT NULL AUTO_INCREMENT,`courseName` varchar(20) NOT NULL COMMENT '課程名稱',`department` varchar(30) NOT NULL,`lv` int(11) DEFAULT NULL COMMENT '年級',`number` int(11) DEFAULT NULL COMMENT '課程人數',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `score` (`id` int(11) NOT NULL AUTO_INCREMENT,`courseId` int(11) NOT NULL COMMENT '課程編號',`studentId` int(11) NOT NULL,`result` float(5,2) NOT NULL COMMENT '成績',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DML

INSERT INTO `student` VALUES ('1', '2023-09-16 09:22:22', '2024-04-15 10:12:12', '張三', '123456', '15612345678', '19', '男', '信息1班', '石家莊', '一首張三的歌送給大家');
INSERT INTO `student` VALUES ('2', '2023-09-17 09:22:22', '2024-04-15 08:11:12', '李四', '123156', '15612345178', '19', '女', '信息1班', '石家莊', '一曲美麗的舞蹈送給大家');
INSERT INTO `student` VALUES ('3', '2023-09-18 09:23:22', '2024-04-15 11:12:12', '王五', '123256', '15612345278', '20', '男', '信息2班', '沈陽', '大刀王五也是英雄');
INSERT INTO `student` VALUES ('4', '2023-09-19 09:24:22', '2024-04-15 12:13:12', '趙六', '123356', '15612345378', '18', '男', '信息1班', '甘肅', '六六大順');
INSERT INTO `student` VALUES ('5', '2023-09-10 09:25:22', '2024-04-15 13:14:12', '阮小七', '143456', '15612345678', '19', '男', '信息2班', '石家莊', '阮氏三雄，小七最霸氣');
INSERT INTO `student` VALUES ('6', '2023-09-21 09:26:22', '2024-04-15 14:15:12', '朱重八', '153456', '15612355678', '18', '男', '信息2班', '浙江', '開局一個碗。');
INSERT INTO `student` VALUES ('7', '2023-09-22 09:27:22', '2024-04-15 15:16:12', '蘇老九', '163456', '15612365678', '20', '男', '信息2班', '石家莊', '武狀元');
INSERT INTO `student` VALUES ('8', '2023-09-23 09:28:22', '2024-04-15 16:17:12', '王石', '123476', '15612345778', '22', '男', '信息1班', '徐蚌', '鉆石王老五');
INSERT INTO `student` VALUES ('9', '2023-09-24 19:29:22', '2024-04-15 17:18:12', '蕭十一', '128456', '15612385678', '21', '男', '信息1班', '石家莊', '帥氣逼人，英雄也。');
INSERT INTO `student` VALUES ('10', '2023-09-25 09:20:22', '2024-04-15 18:19:12', '宮十二', '129456', '15612395678', '22', '女', '信息1班', '杭州', '十二條舔狗和一位綠茶的故事');
INSERT INTO `course` VALUES ('1', '計算機基礎', '信息工程系', '1', '800');
INSERT INTO `course` VALUES ('2', 'MySQL應用基礎', '信息工程系', '2', '567');
INSERT INTO `course` VALUES ('3', 'Java基礎', '信息工程系', '1', '567');
INSERT INTO `course` VALUES ('4', '專業導論', '信息工程系', '1', '645');
INSERT INTO `course` VALUES ('5', 'Excel實戰訓練', '信息工程系', '1', '863');
INSERT INTO `course` VALUES ('6', '大學英語', '教務處', '1', '432');
INSERT INTO `course` VALUES ('7', '大學語文', '教務處', '1', '533');
INSERT INTO `course` VALUES ('8', '高等數學(一)', '教務處', '2', '456');
INSERT INTO `score` VALUES ('1', '1', '1', '89.00');
INSERT INTO `score` VALUES ('2', '1', '2', '89.50');
INSERT INTO `score` VALUES ('3', '1', '3', '98.00');
INSERT INTO `score` VALUES ('4', '1', '4', '82.00');
INSERT INTO `score` VALUES ('5', '1', '5', '59.00');
INSERT INTO `score` VALUES ('6', '1', '6', '22.00');
INSERT INTO `score` VALUES ('7', '1', '7', '68.00');
INSERT INTO `score` VALUES ('8', '1', '8', '7.00');
INSERT INTO `score` VALUES ('9', '1', '9', '91.00');
INSERT INTO `score` VALUES ('10', '1', '10', '69.00');
INSERT INTO `score` VALUES ('11', '2', '1', '99.00');
INSERT INTO `score` VALUES ('12', '2', '2', '69.50');
INSERT INTO `score` VALUES ('13', '2', '3', '58.00');
INSERT INTO `score` VALUES ('14', '2', '4', '72.00');
INSERT INTO `score` VALUES ('15', '2', '5', '89.00');
INSERT INTO `score` VALUES ('16', '2', '6', '82.00');
INSERT INTO `score` VALUES ('17', '2', '7', '58.00');
INSERT INTO `score` VALUES ('18', '2', '8', '77.00');
INSERT INTO `score` VALUES ('19', '2', '9', '17.00');
INSERT INTO `score` VALUES ('20', '2', '10', '79.00');
INSERT INTO `score` VALUES ('21', '3', '1', '55.00');
INSERT INTO `score` VALUES ('22', '3', '2', '77.50');
INSERT INTO `score` VALUES ('23', '3', '3', '88.00');
INSERT INTO `score` VALUES ('24', '3', '4', '12.00');
INSERT INTO `score` VALUES ('25', '3', '5', '88.00');
INSERT INTO `score` VALUES ('26', '3', '6', '71.00');
INSERT INTO `score` VALUES ('27', '3', '7', '36.00');
INSERT INTO `score` VALUES ('28', '3', '8', '94.00');
INSERT INTO `score` VALUES ('29', '3', '9', '66.00');
INSERT INTO `score` VALUES ('30', '3', '10', '34.00');
INSERT INTO `score` VALUES ('31', '4', '1', '55.00');
INSERT INTO `score` VALUES ('32', '4', '2', '87.50');
INSERT INTO `score` VALUES ('33', '4', '3', '88.00');
INSERT INTO `score` VALUES ('34', '4', '4', '82.00');
INSERT INTO `score` VALUES ('35', '4', '5', '88.00');
INSERT INTO `score` VALUES ('36', '4', '6', '31.00');
INSERT INTO `score` VALUES ('37', '4', '7', '86.00');
INSERT INTO `score` VALUES ('38', '4', '8', '94.00');
INSERT INTO `score` VALUES ('39', '4', '9', '86.00');
INSERT INTO `score` VALUES ('40', '4', '10', '34.00');
INSERT INTO `score` VALUES ('41', '5', '1', '95.00');
INSERT INTO `score` VALUES ('42', '5', '2', '27.50');
INSERT INTO `score` VALUES ('43', '5', '3', '88.00');
INSERT INTO `score` VALUES ('44', '5', '4', '82.60');
INSERT INTO `score` VALUES ('45', '5', '5', '88.00');
INSERT INTO `score` VALUES ('46', '5', '6', '31.50');
INSERT INTO `score` VALUES ('47', '5', '7', '86.50');
INSERT INTO `score` VALUES ('48', '5', '8', '94.70');
INSERT INTO `score` VALUES ('49', '5', '9', '86.00');
INSERT INTO `score` VALUES ('50', '5', '10', '34.00');
INSERT INTO `score` VALUES ('51', '6', '1', '75.00');
INSERT INTO `score` VALUES ('52', '6', '2', '77.50');
INSERT INTO `score` VALUES ('53', '6', '3', '88.00');
INSERT INTO `score` VALUES ('54', '6', '4', '72.60');
INSERT INTO `score` VALUES ('55', '6', '5', '88.00');
INSERT INTO `score` VALUES ('56', '6', '6', '71.50');
INSERT INTO `score` VALUES ('57', '6', '7', '76.50');
INSERT INTO `score` VALUES ('58', '6', '8', '94.70');
INSERT INTO `score` VALUES ('59', '6', '9', '76.00');
INSERT INTO `score` VALUES ('60', '6', '10', '74.00');
INSERT INTO `score` VALUES ('61', '7', '1', '75.00');
INSERT INTO `score` VALUES ('62', '7', '2', '67.50');
INSERT INTO `score` VALUES ('63', '7', '3', '68.00');
INSERT INTO `score` VALUES ('64', '7', '4', '72.60');
INSERT INTO `score` VALUES ('65', '7', '5', '88.00');
INSERT INTO `score` VALUES ('66', '7', '6', '61.50');
INSERT INTO `score` VALUES ('67', '7', '7', '76.50');
INSERT INTO `score` VALUES ('68', '7', '8', '64.70');
INSERT INTO `score` VALUES ('69', '7', '9', '76.00');
INSERT INTO `score` VALUES ('70', '7', '10', '64.00');
INSERT INTO `score` VALUES ('71', '8', '1', '95.00');
INSERT INTO `score` VALUES ('72', '8', '2', '97.00');
INSERT INTO `score` VALUES ('73', '8', '3', '98.00');
INSERT INTO `score` VALUES ('74', '8', '4', '92.00');
INSERT INTO `score` VALUES ('75', '8', '5', '98.00');
INSERT INTO `score` VALUES ('76', '8', '6', '91.00');
INSERT INTO `score` VALUES ('77', '8', '7', '96.00');
INSERT INTO `score` VALUES ('78', '8', '8', '94.00');
INSERT INTO `score` VALUES ('79', '8', '9', '96.00');
INSERT INTO `score` VALUES ('80', '8', '10', '94.00');

DQL

儲存過程使用大綱，有參數傳遞

#儲存過程使用大綱.有參數傳遞
delimiter $$
#聲明一個名稱為get_student_introduce
create procedure get_student_introduce(in p_name varchar(20))
#開始操作
begin
#撰寫真正在操作DML+DQL都行
select * from student;
set @userName='龍姑娘';
SELECT introduce '簡介' from student where userName=p_name;
end $$
delimiter;
#使用函數（調用儲存過程）
call get_student_introduce('王語嫣');

儲存過程使用大綱，無參數傳遞

#儲存過程使用大綱.無參數傳遞
delimiter $$
#聲明一個名稱為get_student_introduce
create procedure get_student_introduce()
#開始操作
begin
#撰寫真正在操作DML+DQL都行
select * from student;
set @userName='龍姑娘';
SELECT introduce '簡介' from student where userName=@userName;
end $$
delimiter;
#使用函數（調用儲存過程）
call get_student_introduce();