MySQL存储过程有点类似于简单的编程,即在数据库中执行简单的代码逻辑。
存储过程的创建
- 创建必须先指定截断符,指定截断符是为了让mysql数据库暂时不以
;
结束。一般可以将截断符设置为//
或&&
,这都是自己定义的 。 - 为防止数据库之前就存在同名的存储过程,首先要进行存储过程的删除。
- 按下面的标准语句进行存储过程的创建。
- 使用截断符,表明存储过程创建完毕。
- 恢复数据库默认截断符
;
,执行delimiter ;
即可。
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
characteristic:
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
routine_body:
Valid SQL routine statement
[begin_label:] BEGIN
[statement_list]
……
END [end_label]
实例
- 首先我们先创建一张教师表并插入少量测试数据
CREATE TABLE `tb_teacher` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`num` int(10) NOT NULL,
`name` varchar(20) NOT NULL,
`sex` varchar(4) NOT NULL,
`birthday` datetime DEFAULT NULL,
`address` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `num` (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
+----+------+--------+-----+---------------------+--------------+
| id | num | name | sex | birthday | address |
+----+------+--------+-----+---------------------+--------------+
| 1 | 1001 | Spark | m | 1984-11-08 00:00:00 | 上海长宁 |
| 2 | 1002 | Hadoop | w | 1994-11-28 00:00:00 | 北京昌平 |
| 3 | 1003 | Hive | m | 1978-12-08 00:00:00 | 北京海淀 |
| 4 | 1004 | HBase | w | 1990-01-08 00:00:00 | 上海静安 |
+----+------+--------+-----+---------------------+--------------+
- 创建存储过程
MariaDB [db_test]> drop procedure if exists get_teacher;
Query OK, 0 rows affected (0.00 sec)
MariaDB [db_test]> delimiter //
MariaDB [db_test]> create procedure get_teacher
-> (in teacher_id int, out res varchar(255))
-> begin
-> select concat(name,' ',sex,' ',birthday,' ',address)
-> into res from tb_teacher
-> where id = teacher_id;
-> end //
Query OK, 0 rows affected (0.01 sec)
MariaDB [db_test]> delimiter ;
测试存储过程
MariaDB [db_test]> call get_teacher(1,@res);
Query OK, 1 row affected (0.00 sec)
MariaDB [db_test]> call get_teacher(2,@res2);
Query OK, 1 row affected (0.00 sec)
MariaDB [db_test]> select @res as teacher1,@res2 as teacher2;
+------------------------------------------+-------------------------------------------+
| teacher1 | teacher2 |
+------------------------------------------+-------------------------------------------+
| Spark m 1984-11-08 00:00:00 上海长宁 | Hadoop w 1994-11-28 00:00:00 北京昌平 |
+------------------------------------------+-------------------------------------------+
1 row in set (0.00 sec)
这就是简单的存储过程的使用了!如果感兴趣,可以再评论区留言,一起进步!