参考:
MySQL 存储过程 | 菜鸟教程
https://www.runoob.com/w3cnote/mysql-stored-procedure.html
SQL 存储过程 procedure 讲解+代码实例_sql procedure-CSDN博客
https://blog.csdn.net/baishuiniyaonulia/article/details/105378977
SQL 语法错误1064 (42000)|极客教程
https://geek-docs.com/sql/sql-ask-answer/96_sql_error_1064_42000_you_have_an_error_in_your_sql_syntax.html
本地环境: win10,mysql Ver 8.0.33 for Win64 on x86_64 (MySQL Community Server - GPL)
基础
MySQL 5.0开始支持存储过程(Stored Procedure)。它相当于一组完成特定功能的SQL语句,经过创建和编译后保存在数据库中,用户只要指定名称和参数即可执行。
优点
- 相当于封装代码,能隐藏复杂的商业逻辑
- 可以接受和返回参数
- 可重用性好
- 执行时比直接执行一系列SQL语句效率高,因为它的执行计划已经在编译时生成了,不必再重复优化
缺点
- 往往定制化于特定的数据库上,如果更换系统,可能需要重写
- 性能受限于所属数据库
语法
-
声明语句(就是create…)的结束符支持自定义。比如下面将结束符设置为
$$
,那么写完END
之后就要紧跟这个表示结束delimiter $$
-
注释:
--
是单行注释,c风格的是多行注释 -
变量赋值
SET @p_in=1
-
调用:
call 存储过程名称(参数)
-
创建procedure的完整语法:
-
routine_body里包含dml(数据操作语句)、ddl(数据定义语句)、if-then-else、while-do、declare等(复杂的流程控制语句以后用到再写,本文不展开)
- BEGIN和END是可以嵌套的,里面嵌套的BEGIN-END块和里面的每条语句,必须以分号结束,但是最外层的END要以delimiter结束(这个是可以自定义的)
- begin_label 和 end_label 是一一对应的,使用label可以增强可读性,而且某些语句如 leave 和 iterate 会用到 label 。对应是指:
label1: BEGIN label2: BEGIN label3: BEGIN statements; END label3; END label2; END label1
-
proc_parameter 里 IN 表示是输入参数,就是向procedure里传值的,可以是literal也可以是变量;OUT 表示输出参数,是procedure向外传的,可以返回多个,但是传出的只能是变量;INOUT表示传入兼传出,只能是变量 (最好别用)。同时,参数名字不要等于列名,否则参数名会被当成列名处理
mysql> set @p_in=1; mysql> call in_param(@p_in);
-
变量定义:局部变量定义一定要放在body开始。datatype是sql里合法的
DECLARE l_int int unsigned default 4000000; DECLARE l_numeric number(8,2) DEFAULT 9.95; DECLARE l_date date DEFAULT '1999-12-31'; DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';
-
用户变量:是在MySQL客户端使用的变量
mysql> select 'hello' into @x; Query OK, 1 row affected (0.00 sec) mysql> select @x; +-------+ | @x | +-------+ | hello | +-------+ 1 row in set (0.00 sec)
加上@
才能区分变量名和列名,这个要注意。- 在存储过程中可以直接使用用户变量:
mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World'); mysql > SET @greeting='Hello'; mysql > CALL GreetWorld( );
- 在某个procedure中可以使用另一个procedure中的变量:
mysql> CREATE PROCEDURE p1() SET @last_procedure='p1'; mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_procedure); mysql> CALL p1( ); mysql> CALL p2( );
- 在存储过程中可以直接使用用户变量:
-
删除procedure:跟删除表一样,用drop
-
修改:alter
-
查询procedure:
show procedure status where db='xxx';
-
查询procedure详细内容:
show create procedure 数据库名.过程名;
实例
创建一个procedure
首先指定数据库,修改end_label为$$
,然后写创建语句:这里包含两个字符串类型的参数 n 和 utype。创建结束后恢复 end_label 为;
。如果不恢复的话普通sql语句都难以结束。但如果不改这个end_label,可能会在END处报1064错误。
mysql> use vector;
Database changed
mysql> delimiter $$
mysql> create procedure SearchBU(in n varchar(200), in utype varchar(100))
-> BEGIN
-> select name, ustart, uend, direction, unit_type, seq
-> from basic_unit
-> where
-> name like concat('%', n, '%')
-> and
-> unit_type like concat('%', utype, '%');
-> END$$
Query OK, 0 rows affected (0.04 sec)
mysql> delimiter ;
调用
mysql> call SearchBU("AmpR promoter","promoter");
+---------------+--------+------+-----------+-----------+-----------------------------------------------------------------------------------------------------------+
| name | ustart | uend | direction | unit_type | seq |
+---------------+--------+------+-----------+-----------+-----------------------------------------------------------------------------------------------------------+
| AmpR promoter | 1 | 105 | f | promoter | CGCGGAACCCCTATTTGTTTATTTTTCTAAATACATTCAAATATGTATCCGCTCATGAGACAATAACCCTGATAAATGCTTCAATAATATTGAAAAAGGAAGAGT |
+---------------+--------+------+-----------+-----------+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)