存储过程和函数

最新推荐文章于 2024-04-12 02:06:15 发布

yu_sh

最新推荐文章于 2024-04-12 02:06:15 发布

阅读量750

点赞数 1

分类专栏： MySQL 文章标签：数据库 mysql

本文链接：https://blog.csdn.net/A_easy_learner/article/details/116857236

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

1. 创建存储过程和函数

存储程序可以分为存储过程和函数，在MySQL中创建存储过程和函数使用的语句分别是CREATE PROCEDURE 和CREATE FUNCTION。使用CALL 调用存储过程，只能输出变量返回值。函数可以从语句外调用（引用函数名），也能返回标量值，存储过程也可以调用其他存储过程。

1. 创建存储过程

创建存储过程，需要使用CREATE PROCEDURE 语句。
语法格式：CREATE PROCEDURE sp_name ( [proc_parameter] ) [characteristics ...] routine_body

CREATE PROCEDURE 是用来创建存储函数的关键字；
sp_name 是存储过程的名称；

proc_parameter 是指定存储过程的参数列表，具体形式为：[IN | OUT | INOUT] param_name type
IN 表示输入参数，OUT 表示输出参数，INOUT 即可以表示输入参数，也能表示输出参数；
param_name 表示参数名称；
type 是参数的类型，可以是MySQL的任意类型。

characteristics 指定存储过程的特性，有以下取值
1. LANGUAGE SQL：说明routine_body部分是由SQL语句组成的，当前系统支持的语言为SQL。SQL是LANGUAGE特性的唯一值。
2. [NOT] DETERMINISTIC：指明存储过程执行的结果是否正确；
   DETERMINISTIC：表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出；
   NOT DETERMINISTIC：表示结果是不确定的，相同的输入可能得到不同的输出；
   如果没有指定任意一个值，默认为NOT DETERMINISTIC。
3. { CONTAINS SQL | NO SQL| READS SQL DATA | MODIFIES SQL DATA}：指明子程序使用SQL语句的限制。
   CONTAINS SQL：表明子程序包含SQL语句，但是不包含读写数据的语句；
   NO SQL：表明子程序不包含SQL语句；
   READS SQL DATA：说明子程序包含读数据的语句；
   MODIFIES SQL DATA：表明子程序包含写数据的语句；
   默认情况下，系统会指定为CONTAINS SQL。
4. SQL SECURITY ( DEFINER INVOKER )：指明谁有权限来执行。
   DEINER：表示只有定义者才能执行；
   INVOKER：表示拥有权限的调用者可以执行；
   默认情沉下，系统指定为DEFINER.
5. COMMENT ‘sring’：注释信息，可以用来描述存储过程成函数，

routine_body 是SQL代码的内容，可以用BEGIN  ...  END 来表示SQL代码的开始和结束。

DELIMITER ;; 语句的作用是将MySQL的结束语设置为 ';;' 。因为MySQL默认语句的结束符号为分号 ';'。为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER 改变存储过程的结束符，之后在恢复原来的符号。

//以下就创建几个存储过程作为例子
mysql> create table fruits
   -> (
   -> name VARCHAR(255) NOT NULL,
   -> price INT NOT NULL,
   -> primary key(name)
   -> );
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO fruits
   -> VALUES ('a',10),
   -> ('b',20),
   -> ('c',15),
   -> ('d',25);
Query OK, 4 rows affected (0.06 sec)

mysql> DELIMITER ;;
mysql> CREATE PROCEDURE AvgFruitPrice ()
   -> BEGIN
   -> SELECT AVG(price) AS avgprice FROM fruits;
   -> END;;
Query OK, 0 rows affected (0.09 sec)
mysql> DELIMITER ;


mysql> DELIMITER //
mysql> CREATE PROCEDURE CountProc(OUT paraml INT)
   -> BEGIN
   -> SELECT COUNT(*) INTO paraml FROM FROM fruits;
   -> END;
   -> //

mysql> CREATE PROCEDURE CountProc(OUT paraml INT)
   -> BEGIN
   -> SELECT COUNT(*) INTO paraml FROM fruits;
   -> END;
   -> //
Query OK, 0 rows affected (0.09 sec)
mysql> DELIMITER ;

2. 创建存储函数

创建存储函数，需要用CREATE FUNCTION 语句

语法格式：CREATE FUNCTION func_name ( [func_parameter] ) 
		 RETRUNS type
		 [characteristic ...] routine_body

CREATE FUNCTION：是用来创建存储函数的关键字；
func_name：是存储函数的名字；
func_parameter：是存储过程的参数列表，语法格式为：[IN | OUT | INOUT] param_name type , 与上面那个相同。
RETURNS type：语句表示函数要返回数据的类型；
characteristic 指定存储函数的特性，取值与上面那个相同。

//例子
mysql> DELIMITER //
mysql> CREATE FUNCTION NameByZip ()
    -> RETURNS CHAR(255)
    -> RETURN (SELECT name FROM fruits WHERE price = 20);
    -> //
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

//出错，已解决，添加一个characteristic特性DETERMINISTIC
//详情：https://blog.csdn.net/chinafire525/article/details/93028759

mysql> CREATE FUNCTION NameByZip ()
    -> RETURNS CHAR(255)
    -> DETERMINISTIC
    -> RETURN (SELECT name FROM fruits WHERE price = 20);
    -> //
Query OK, 0 rows affected (0.05 sec)

指定参数为IN。OUT和INOUT只对PROCEDURE是合法的。FUNCTION中总是默认为IN参数。
RETURNS子句只能对FUNCTION做指定，对函数而言这是强制的，它用来指定函数返回的类型，而且函数体必须包含一个RETURN value语句，而且RETURN value 的类型必须与RETURNS 子句相同，否则返回值将被强制转为恰当的类型。

mysql> DELIMITER ;

3. 变量的使用

使用set或select直接赋值，变量名以 @ 开头.
例如:set @var=1;
可以在一个会话的任何地方声明，作用域是整个会话，称为会话变量。
以 DECLARE 关键字声明的变量，只能在存储过程中使用，称为存储过程变量，例如：
DECLARE var1 INT DEFAULT 0;
主要用在存储过程中，或者是给存储传参数中。
两者的区别是：
在调用存储过程时，以DECLARE声明的变量都会被初始化为 NULL。而会话变量（即@开头的变量）则不会被再初始化，在一个会话内，只须初始化一次，之后在会话内都是对上一次计算的结果，就相当于在是这个会话内的全局变量。当退出会话（客户端）时，该客户端连接的所有变量将自动释放。

定义变量的语法格式：DECLARE var_name[,varname]... data_type [DEFAULT value];
为变量赋值的语法格式：SET var_name expr [, var_name = expr] ... ; 
还可以通过SELECT...INTO为一个或多个变量赋值，他将选定的列直接存储到对应位置的变量。
语法格式：SELECT col_name[,...] INTO var_name[,...] table_expr;
col_name：表示字段名称；var_name：表示定义的变量名称；table_expr：表示查询的条件表达式，包括表名称和WHERE子句。

由于DECLARE只能在储存过程或函数中使用，所以我们就只给出变量的使用方法，就不写出全部细节了。

DECLARE var1, var2, var3 INT;
SET var1 = 10, var2 = 20;
SET var3 = var1 + var2;
//正如上面的语法格式，DECLARE和SET的定义格式那般，它们可以同时定义相同类型的多个变量，而SET可以赋不同类型的值给不同类型的变量。

//通过SELECT...INTO语句查询指定记录并将之赋予变量
DECLARE fruitname CHAR(50);
DECLARE fruitprice DECIMAL(8,2);

SELECT name, price INTO fruitname, fruitprice FROM fruits WHERE price = 20;

4. 定义条件和处理程序

特定的条件需要特殊处理，当然我们可以先定义可能出现的错误，然后指定相应的程序去处理这类问题，类似于JAVA的异常，都是先定义可能出现的异常，然后再制定处理方法。

定义条件

定义条件使用DECLARE语句
语法格式：DECLARE condition_name CONDITION FOR [condition_type] 
		 [condition_type]:
		 SQLSTATE [value] sqlstate_value | mysql_error_code;

其中，condition_name：表示条件的名称；
condition_type：表示条件的类型；
sqlstate_value和mysql_error_code都可以表示MySQL的错误：
sqlstate_value为长度为5的字符串类型错误代码；
mysql_error_code为数值类型错误代码。
例如在ERROR 1142 (45000)中,sqlstate_value的值是42000，mysql_error_code的值为1142。

这个语句指定一个需要特殊处理的条件，将一个名字和一个指定的错误条件关联起来。这个名字随后被用在定义处理程序的DECLARE HANDLER 语句中。

//两种定义条件的方法
1. 使用sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE ‘42000’
2. 使用mysql_error_code
DEXLARE command_not_allowed CONDITION FOR 1148

定义处理程序

语法格式：DECLARE hadnler_type HANDLER FOR condition_value[,...] sp_statement
		 hadnler_type:
		 	CONTINUE | EXIT | UNDO
		 
		 condition_value:
		 	SQLSTATE [VALUE] sqlstate_value 
		 | condition_name
		 | SQLWARNING
		 | NOT FOUND
		 | SQLEXCEPETION
		 | mysql_error_code
		
其中，handler_type：为错误处理的方式，参数有3个值：CONTINUE、EXIT和 UNDO（UNDO目前MySQL不支持）。
CONTINUE：表示遇到错误不处理；EXIT：表示遇到错误马上退出；UNDO：表示遇到错误后撤回之前的操作。

condition_value：表示错误的类型，可以有以下取值：

 - SQLSTATE [VALUE]：sqlstate_value 包含5个字符的字符串错误值；
 - condition_name：表示DECLARE CONDITION定义的错误条件名称；
 - SQLWARNING：匹配所有以01开头的SQLSTATE错误代码；
 - NOT FOUND：匹配所有以02开头的SQLSTATE错误代码；
 - SQLEXCEPETION：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码；
 - mysql_error_code：匹配数值类型的错误代码。

sp_statement：参数为程序语句段，表示在遇到定义的错误时需要执行的存储过程或函数。


//定义程序的几种方式
方法一：捕获sqlstate_value
DECLARE CONTINUE HANDLER FORSQLSTATE '42S02' SET @info= 'NO_SUCH_TABLE';
该方法时捕获sqlstate_value值。如果遇到了“42S02”,执行 CONTINUE 操作，并输出“NO_SUCH_TABLE”信息。

方法二：先定义条件，然后调用
DECCLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info=' NO_SUCH_TABLE';
该方法是先定义条件，在调用条件。这里先定义no_such_table条件，遇到1146错误就执行 CONTINUE 操作。

方法三：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';
该方法捕获所有以01开头的sqlstate_value值，然后执行 EXIT 操作，并且输出“ERROR”信息。

方法四：使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info= ' NO_SUCH_TABLE ';
该方法捕获所有以02开头的sqlstate_value值，然后执行 EXIT 操作，并且输出“NO_SUCH_TABLE”信息。

方法五：使用SQLEXCEPTION
DECLARE EXIT  HANDLER FOR SQLEXCEPTION SET @info='ERROR';
该方法捕获没有被 SQLWARNING 或 NOT FOUND捕获的sqlstate_value值，然后执行 EXIT 操作，并且输出“ERROR”信息。

方法六：捕获mysql_error_code
DECLARE CONTINUE HANDLER FOR 1146 SET @info=' NO_SUCH_TABLE';
该方法捕获mysql_error_code值，若mysql_error_code的值为1146，执行 CONTINUE 操作，并且输出“NO_SUCH_TABLE”信息。

以上就是定义条件和处理程序的方法，接下来我们举个具体的例子，而不是定义的语法

mysql> DELIMITER //
mysql> CREATE TABLE cha_10.t (s1 int,primary key(s1));
Query OK, 0 rows affected (0.21 sec)

//这里定义了处理程序，当捕获到sqlstate_value值为“23000”时，执行 CONTINUE 操作并设置@x2 = 1；
mysql> CREATE PROCEDURE handlerdemo ()
    -> BEGIN
    -> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
    -> SET @x = 1;
    -> INSERT INTO cha_10.t VALUES (1);
    -> SET @x = 2;
    -> INSERT INTO cha_10.t VALUES (1);
    -> SET @x = 3;
    -> END;
    -> //
Query OK, 0 rows affected (0.11 sec)

mysql> DELIMITER ;
mysql> CALL handlerdemo();
Query OK, 0 rows affected (0.03 sec)

//由上面我们可以知道t表的s1是主键，因此不能有相同的值，因此在储存过程里第二次插入值“1”时是出错了（INSERT INTO cha_10.t VALUES (1)）
//但是由于前面定义了条件，出错了就执行CONTINUE，因此x的值为3；若是没有定义条件，x的值应该为2；
mysql> SELECT @x;
+------+
| @x   |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

//我们先定义一个变量@x2 = 2；在执行handlerdemo，在查看@x2的值，发现@x2 = 1，我们就可以知道条件满足了，执行了至少一次，改变了@x2的值。
mysql> SET @x2 = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x2;
+------+
| @x2  |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

mysql> CALL handlerdemo();
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x2;
+------+
| @x2  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

5. 光标的使用

查询语句可能返回多条，如果数据量非常大，需要在存储过程和存储函数中使用光标来逐条读取查询结构集中的记录。应用程序可以根据需要滚动或浏览其中的数据。
光标必须在声明处理程序之前被声明，并且变量和条件还必须在声明光标或处理程序之前被声明，最重要的是光标只能在存储过程和函数中使用。

声明光标

语法格式：DECLARE cursor_name CURSOR FOR select_statement
cursor_name：是光标名称
select_statement：是SELECT语句的内容，返回一个用于创建光标的结果集。

例子：
DECLARE cursor_fruit CURSOR FOR SELECT name， price FROM fruits;

打开光标

语法结构：OPEN cursor_name

例子：
OPEN cursor_fruit;

使用光标

语法格式：FETCH cursor_name INTO var_name [, var_name] ... {参数名称}
cursor_name：是光标的名称
var_name：表示要插入信息的参数，并且必须在声明光标前定义好。

例子：使用名称为cursor_fruit中用SELECT语句查询出来的信息存入fruit_name和fruit_price中。
fruit_name和fruit_price必须在前面已经定义好。

FETCH cursor_fruit INTO fruit_name, fruit_price;

关闭光标

语法格式：CLOSE cursor_name{光标名称};
这个语句关闭先前打开的光标。
如果未被明确地关闭，光标在他被声明的复合语句的末尾关闭。

例子：
CLOSE cursor_fruit;

6. 流程控制的使用

MySQL中用来构造控制流程的语句有IF语句、CASE语句、LOOP语句、LEAVE语句、ITERARE语句、REPEAT语句和WHILE语句。
每个流程中可能包含一个单独语句，或者使用BEGIN … END构造的复合语句，构造可以嵌套。

IF语句
IF语句包含多个条件判断，根据判断的结果为TRUE或FALSE执行相应的语句

语法格式：IF expr_condition THEN statement_list
		 [ELSEIF expr_condition THEN statement_list] ...
		 [ELSE statement_list]
END IF

与其他语言相同，IF的结构如下：
IF expr_condition
	then statement_list;
ELSE IF expr_condition
	then statement_list;
...
ELSE
	statement_list;
END IF;

expr_condition是判断的条件，满足则执行相应的statement_list。

例子：
IF val IS NULL
	THEN SELECT 'val is NULL';	//输出'val is NULL'
ELSE
	SELECT 'val is not NULL';	//输出'val is not NULL'
END IF;

CASE语句
CASE语句是另一种进行条件判断的语句，有两种格式。

第一种，语法定义：CASE case_expr
	             	WHEN when_value THEN statement_list
	             	[WHEN when_value THEN statement_list] ...
	             	[ELSE statement_list]
	             END CASE

case_expr：表示条件判断的表达式，决定了那个WHEN字句会被执行；
when_value：表示表达式可能的值，如果某个when_value表达式与case_expr表达式结构相同，则执行相应的statement_list语句；
statement_list：表示不同的when_value的值执行不同的语句。

例子：
val = 1 时，输出字符串“val is 1”,...
CASE val
	WHEN 1 THEN SELECT 'val is 1';
	WHEN 2 THEN SELECT 'val is 2';
	ELSE SELECT 'val is not 1 or 2';
END CASE;


第二种，语法格式：CASE
				 	WHEN expr_condition THEN statement_list
				 	[WHEN expr_condition THEN statement_list] ... 
				 	[ELSE statement_list]
				 END CASE;
其中，expr_condition：表示条件判断语句；
statement_list：参数表示不同条件的执行语句。
在该语句中，WHEN语句将被逐个执行，知道某个expr_condition表达式为真，则执行相应的statement_list，
否则就执行ELSE，即最后一个statement_list语句。


例子：
CASE 
	WHEN val is NULL THEN SELECT 'val is NULL';
	WHEN val < 0 THEN SELECT 'val is less then 0';
	WHEN val > 0 THEN SLELCT 'val is greater then 0';
	ELSE SELECT 'val is 0';
END CASE;

LOOP语句
LOOP循环语句用来重复执行某些语句，它不用进行判断，LOOP只是创建一个循环的过程，LOOP内的语句将一直重复执行知道跳出循环为止（使用LEAVE子句）。

语句格式：[loop_label:]LOOP
		 	statement_list
		 END LOOP [loop_label]
其中loop_label：表示LOOP语句的标注的名称，该参数可以忽略；
statement_list：表示要执行循环的语句。


例子：
//当id >= 10时，退出循环（leave add_loop）
DECLARE id INT DEFAULT 0;
add_loop: LOOP
SET id = id + 1;
	IF id >= 10 THEN LEAVE add_loop;
	END IF;
END LOOP add_loop;

LEAVE语句
LEAVE语句用来推出任何被标注的流程控制构造

语法结构：LEAVE label
其中label：表示循环的标志，LEAVE和BEGIN ... END 或循环一起使用。

例子：
add_num: LOOP
	SET @count = @count + 1;
	IF @count = 50 THEN LEAVE add_num;
END LOOP add_num;

ITERATE 语句
ITERATE 是迭代的意思，它将执行顺序转到语句段开头。
它只可以出现在循环语句内，ITERATE的意思为再次循环。

语法格式；ITERATE label
label：表示要进行迭代循环的标志，必须跟在循环标志之前。

例子：
//当p1 < 10 时，每次都迭代回my_loop循环；
//当10 <= p1 <=20，那么便输出'p1 is between 10 and 20'；
//当p1 > 20 那么就执行LEAVE my_loop 循环，推出my_loop循环。
mysql> CREATE PROCEDURE doiterate()
    -> BEGIN
    ->     DECLARE p1 INT DEFAULT 0;
    ->     my_loop: LOOP
    ->         SET p1 = p1 + 1;
    ->         IF p1 < 10
    ->             THEN ITERATE my_loop;
    ->         ELSEIF p1 > 20
    ->             THEN ITERATE my_loop;
    ->         END IF;
    ->         SELECT 'p1 is between 10 and 20';
    ->     END LOOP my_loop;
    -> END;
    -> //
Query OK, 0 rows affected (0.06 sec)

REPEAT 语句
REPEAT语句创建一个带条件判断的循环过程，每次执行之后都会进行一次判断，若表达式为真，则退出循环；否则重复执行循环中的语句，类似于do … while 循环语句，先执行后判断

语句格式：[repeat_label:] REPEAT
		  	statement_list
		  UNTIL expr_condition
		  END REPEAT [repeat_label]
repeat_label：是REPEAT语句的标注名称，该参数可以省略;
REPEAT语句内的语句或语句群被重复，直到expr_condition为真。


例子：
//当id >= 10时循环退出。
DECLARE id INT DEFAULT 0;
REPEAT 
	SET id = id + 1;
	UNTIL id >= 10
END REPEAT;

WHILE语句
语句WHILE创建一个带条件判断的循环过程，但是与REPEAT语句不同的是它是先判断后执行，类似于WHILE语句。

语法格式：[while_label:] WHILE expr_condition DO
		 	statement_list
		 END WHILE [while_label]
while_label：为WHILE语句的标注名称；
expr_condition：未进行判断的表达式，如果为真，这执行循环内的语句，直至expr_condition为假，退出循环。


例子：
DECLARE i INT DEFAULT 0;
WHILE i < 10 DO
	SET i = i + 1;
END WHILE;

2.调用存储过程和函数

创建好了存储过程和函数，那麽我们就可以使用CALL来调用用它们了，如果要调用的存储过程或函数识别的数据库的，我们就要在前面加上它所属的数据，如CALL suoyin.procedure。

1. 调用存储过程

语法结构：CALL sp_name([parameter[,...]]);
CALL语句调用一个先前CREATE PROCEDURE创建的存储过程，
其中sp_name：为存储过程的名称；
parameter[,...]：表示存储过程的参数，可以没有，也可以有多个。


例子：
mysql> delimiter //
mysql> CREATE PROCEDURE CountProc1(IN sid INT, OUT num INT)
    -> BEGIN
    ->     SELECT COUNT(*) INTO num FROM fruits WHERE price = sid;
    -> END;
    -> //
Query OK, 0 rows affected (0.10 sec)

mysql> CALL CountProc1;

mysql> CALL CountProc1(20, @num);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @num;
+------+
| @num |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

2. 调用存储函数

调用存储函数与之前学过的函数的调用方法一样的，使用SELECT语句，因为他们本质都是函数，一个是内部函数，一个是自己定义的函数。

注意：如果在创建存储函数中报错”you might want to use the less safe log_bin_trust_function_creators variable“，需要执行以下代码：

mysql> SET GLOBAL log_bin_trust_function_creators = 1;

语法格式：
例子：SELECT function_name([parameter[,...]])
function_name：为存储函数的名称
parameter[,...]：表示存储函数的参数，可以没有，也可以有多个。

例子：
mysql> delimiter //
mysql> CREATE FUNCTION CountProc2 (sid INT)
    -> RETURNS INT
    -> BEGIN
    ->     RETURN (SELECT COUNT(*) FROM fruits WHERE price = sid);
    -> END;
    -> //
Query OK, 0 rows affected (0.06 sec)

mysql> delimiter ;
mysql> SELECT CountProc2(20);
+----------------+
| CountProc2(20) |
+----------------+
|              1 |
+----------------+
1 row in set (0.01 sec)

3. 查看存储过程和函数

MySQL 存储了存储过程和函数的状态信息，用户可以使用SHOW STATUS 语句或SHOW CREATE语句查看，也可以直接从系统的information_schema数据库中查询。

使用SHOW STATUS语句查看存储过程和函数的状态

语法格式：SHOW [PROCEDURE | FUNCTION] STATUS [LIKE 'pattern']
这个语句是一个MySQL的扩展，返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。
如果没有指定样式，那么根据使用的语句，所有存储程序和存储函数的信息都会被列出。
其中，PROCEDURE 和 FUNCTION 分别表示查看存储过程和函数；
LIKE 语句表示匹配存储过程或函数的名称。

//"SHOW PROCEDURE STATUS LIKE 'C%'\G"：获取数据库中所有名称以'C'开头的存储过程的信息。
mysql> SHOW PROCEDURE STATUS LIKE 'C%'\G
*************************** 1. row ***************************
                  Db: cha_10
                Name: CountProc
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2021-05-15 17:36:26
             Created: 2021-05-15 17:36:26
       Security_type: DEFINER
             Comment:
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
*************************** 2. row ***************************
                  Db: cha_10
                Name: CountProc1
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2021-05-16 19:17:06
             Created: 2021-05-16 19:17:06
       Security_type: DEFINER
             Comment:
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci

使用SHOW CREATE 语句查看存储过程和函数的定义
除了SHOW STATUS之外，SHOW CREATE也可以查看存储过程函数的状态。

语法格式：SHOW CREATE [PROCEDURE | FUNCTION] sp_name
这个语句是一个MySQL的扩展。类似于SHOW CREATE TABLE，他返回一个可用来重新创建已命名子程序的确切字符串。
PROCEDURE和FUNCTION分别表示查看存储过程函数；
sp_name参数表示匹配存储过程或函数的名称

//例子：
mysql> SHOW CREATE FUNCTION cha_10.CountProc2 \G
*************************** 1. row ***************************
            Function: CountProc2
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `CountProc2`(sid INT) RETURNS int(11)
BEGIN
    RETURN (SELECT COUNT(*) FROM fruits WHERE price = sid);
END
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

从information_schema.Routines表中查看存储过程函数的信息
MySQL中存储过程和函数的信息都存储在information_schema数据库下的Routines表中，可以通过查询该表记录来查询存储过程和函数的信息。

语法格式：SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME = ' sp_name ' AND ROUTINE_TYPE = 'FUNCTION | PROCEDURE' AND ...;
其中ROUTINE_NAME：字段中存储的是存储过程和函数的名称；
sp_name：表示存储过程函数的名称。
在查询时一定要使用ROUTINE_NAME，否则他会查询所有存储过程和函数，如果有名称相同的，就使用ROUTINE_TYPE确定类型等等条件。


mysql> SELECT * FROM information_schema.Routines
    -> WHERE ROUTINE_NAME='CountProc2' AND ROUTINE_TYPE='FUNCTION' \G
*************************** 1. row ***************************
           SPECIFIC_NAME: CountProc2
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: cha_10
            ROUTINE_NAME: CountProc2
            ROUTINE_TYPE: FUNCTION
               DATA_TYPE: int
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 10
           NUMERIC_SCALE: 0
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: int(11)
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: BEGIN
    RETURN (SELECT COUNT(*) FROM fruits WHERE price = sid);
END
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: SQL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2021-05-16 19:22:32
            LAST_ALTERED: 2021-05-16 19:22:32
                SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
         ROUTINE_COMMENT:
                 DEFINER: root@localhost
    CHARACTER_SET_CLIENT: utf8mb4
    COLLATION_CONNECTION: utf8mb4_0900_ai_ci
      DATABASE_COLLATION: utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

修改存储过程和函数

使用ALTER 语句可以修改存储过程或函数的特性。

语法格式：ALTER [PROCEDURE | FUNCTION] sp_name [characteristic ... ]
其中，sp_name：表示存储过程或函数的名称；
ALTER [PROCEDURE | FUNCTION]两种类型的格式相同，且得出的结果也相同，参数的取值也可以是一样的。

characteristic：至此那个存储函数的特性，有如下取值：

 - CONTAINS SQL：表明子程序包含SQL语句，但是不包含读写数据的语句；
 - NO SQL：表明子程序不包含SQL语句；
 - READS SQL DATA：说明子程序包含读数据的语句；
 - MODIFIES SQL DATA：表明子程序包含写数据的语句；
 - SQL SECURITY ( DEFINER INVOKER )：指明谁有权限来执行，默认情沉下，系统指定为DEFINER.
 - DEINER：表示只有定义者才能执行；
 - INVOKER：表示拥有权限的调用者可以执行；
 - COMMENT ‘sring’：注释信息，可以用来描述存储过程成函数，


mysql> SHOW CREATE PROCEDURE CountProc \G
*************************** 1. row ***************************
           Procedure: CountProc
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `CountProc`(OUT paraml INT)
BEGIN
SELECT COUNT(*) INTO paraml FROM fruits;
END
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> ALTER PROCEDURE CountProc
    -> MODIFIES SQL DATA
    -> SQL SECURITY INVOKER;
Query OK, 0 rows affected (0.05 sec)

//多了 MODIFIES SQL DATA 和 SQL SECURITY INVOKER
mysql> SHOW CREATE PROCEDURE CountProc \G
*************************** 1. row ***************************
           Procedure: CountProc
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `CountProc`(OUT paraml INT)
    MODIFIES SQL DATA
    SQL SECURITY INVOKER
BEGIN
SELECT COUNT(*) INTO paraml FROM fruits;
END
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

5. 删除存储过程和函数

语法格式：DROP [PROCEDURE | FUNCTION] [IF EXISTS] sp_name
sp_name：是要移除的存储过程或函数的名称；
IF EXISTS 子句是一个MySQL的扩展。如果程序或函数不存储，它可以防止发生错误，产生一个用SHOW WARNINGS查看警告。


//例子：
mysql> DROP PROCEDURE CountProc;
Query OK, 0 rows affected (0.04 sec)
mysql> DROP FUNCTION CountProc2;
Query OK, 0 rows affected (0.01 sec)

6. 全局变量的持久化

在MySQL数据库中，全局变量可以通过SET GLOBAL 语句来设置，但是GLOBAL变量只能在这次服务其运行中使用，当重启MySQL服务器时他就不能使用了，想要再使用必须要再次设置。

GLOBAL变量

mysql> SET GLOBAL name = value;

PERSIST变量
SET PERSIST命令，当MySQL接收到这个命令后，会将该命令下的配置保存到数据目录下的mysqld-auto.cnf文件中，下次启动时会读取该文件，用其中的配置覆盖默认的配置文件。

mysql> SET PERSIST name = value;

7. 常见的问题

疑问1: MySQL存储过程和函数有什么区别?
在本质上它们都是存储程序。函数只能通过return语句返回单个值或者表对象;而存储过程不允许执行returm,但是可以通过out 参数返回多个值。函数限制比较多，不能用临时表，只能用表变量，还有一些函数都不可用等;存储过程的限制相对比较少。函数可以嵌入SQL语句中使用，可以在SELECT语句中作为查询语句的一个部分调用:存过程一般作为一个独立的部分来执行。
疑问2:存储过程中的代码可以改变吗?
目前，MySQL还不提供对已存在的存储过程代码的修改，如果必须要修改存储过程，必须使用DROP语句删除之后再重新编写代码，或者创建一个新的存储过程。
疑问3:在存储过程中可以调用其他存储过程吗?
存储过程包含用户定义的SQL语句集合，可以使用CALL语句调用存储过程。当然，在存储过程中也可以使用CALL语句调用其他存储过程，但是不能使用DROP语句删除其他存储过程
疑问4:存储过程的参数不要与数据表中的字段名相同。
在定义存储过程参数列表时，应注意把参数名与数据库表中的字段名区别开来，否则将出现无法预期的结果。
疑问5:存储过程的参数可以使用中文吗?
一般情况下，可能会出现存储这程中传入中文参数的情况，例如某个存储过程根据用户配字查找该用户的信息，传入的参数值可能是中文。这时需要在定义存储过程的时侯在后面加上chaterer set gbk,不然调用存储过程使用中文参数会出错，比如定义userInfo存储过程，代码如下：

CREATE PROCEDURE userInfo(IN u_name name VARCHAR (50) character set gbk, OUT u_age INT)

yu_sh

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
1
评论
存储过程和函数

1. 创建存储过程和函数存储程序可以分为存储过程和函数，在MySQL中创建存储过程和函数使用的语句分别是CREATE PROCEDURE 和CREATE FUNCTION。使用CALL 调用存储过程，只能输出变量返回值。函数可以从语句外调用（引用函数名），也能返回标量值，存储过程也可以调用其他存储过程。1. 创建存储过程创建存储过程，需要使用CREATE PROCEDURE 语句。语法格式：CREATE PROCEDURE sp_name ( [proc_parameter] ) [characte
复制链接

扫一扫