首页 > 数据库 >MySQL 存储过程浅谈

MySQL 存储过程浅谈

时间：2022-12-04 22:34:55浏览次数：35  
标签：语句 存储 END 浅谈 SQL value param MySQL name

一、存储过程定义
​ 存储过程（Stored Procedure）：一组为了完成特定功能的SQL语句集，存储在数据库中，经过一次编译后不需要再次编译。

二、存储过程特点
1、可以完成复杂的判断和运算

2、执行速度快

3、可重复使用

4、减少网络之间的数据传输，节省开销

三、存储过程语法

1、创建

delimiter $$ --定义存储过程结束标记，可自定义
--创建 使用procedure
create procedure [procedure_name]([param_type][param_name][data_type],...)
begin
[存储体];--分号不可少
end $$
delimiter;

-- 创建存储过程示例
delimiter $$
create procedure getUserById(in userId int)
begin
select * from tb_user where user_id=userId;
end $$
delimiter;

delimiter：表示sql语句执行的结束

参数类型说明：param_type[in|out|inout]

IN：表示调用者需要对存储过程传入参数。
OUT：表示调用者到一个或多个返回值。
INOUT：表示调用者既要传入值，又要传出值。
2、删除

-- 删除procedure
drop procedure if exists [procedure_name];

-- 示例
drop procedure if exists getUserById;

3、调用

-- 无参
call procedure_name();
-- 有参
call procedure_name(param1,param2,...)

4、定义变量
变量声明语句必须放在存储体的开始部分

-- 定义变量 declare
-- 语法
-- declare [declare_name][type][default_value(可选)]
declare userId int default 66;
declare userId int;
-- 变量赋值
-- set declare_name=new_value;
set userId=77;

5、流程控制语句
1、条件控制语句
1、IF语句
语法

IF search_condition_1 THEN statement_list_1
[ELSEIF search_condition_2 THEN statement_list_2] ...
[ELSE statement_list_n]
END IF

实例

DROP PROCEDURE IF EXISTS get_max_num ;

DELIMITER $$
CREATE PROCEDURE get_max_num (IN a INT, IN b INT, OUT c) 
BEGIN
IF a>b 
THEN set c = a ;

ELSE set c = b;

END IF ;

END $$ 
DELIMITER ;

SELECT @a;
CALL get_max_num(3,4);
SELECT @a;

2、case语句
存储程序的CASE语句实现一个复杂的条件构造。如果search_condition 求值为真，相应的SQL被执行。如果没有搜索条件匹配，在ELSE子句里的语句被执行

语法

CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
Or:

CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE

实例

-- case 语句的使用
DROP PROCEDURE test_case;

DELIMITER $$
CREATE PROCEDURE test_case (IN param INT) 
BEGIN
DECLARE result VARCHAR (10) ;
CASE param 
WHEN param = 1 
THEN SET result = "星期一" ;
WHEN param = 2 
THEN SET result = "星期二" ;
WHEN param = 3 
THEN SET result = "星期三" ;
ELSE SET result = "error";
END CASE ;
SELECT result AS "星期" ;
END $$ 
DELIMITER ;
CALL test_case(11);

2、循环控制语句
1、while语句
WHILE语句内的语句或语句群被重复，直至search_condition 为真。
WHILE语句可以被标注。 除非begin_label也存在，end_label才能被用，如果两者都存在，它们必须是一样的
先判断，后执行

语法

[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]

实例

-- while语句
DROP PROCEDURE IF EXISTS test_while;
DELIMITER $$

CREATE PROCEDURE test_while (IN a INT)
BEGIN
DECLARE param INT DEFAULT 5 ;
SET param=a;
SELECT "yyyy";
WHILE
param > 0 DO 
SELECT 
param ;
SET param = param - 1 ;

END WHILE ;

END $$
DELIMITER ;

2、repeat
REPEAT语句内的语句或语句群被重复，直至search_condition 为真。
先执行，后判断
语法

[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]

实例
-- repeat语句
DROP PROCEDURE IF EXISTS test_repeat;
DELIMITER $$
CREATE PROCEDURE test_repeat(IN param INT)
BEGIN
SET @param=1;
REPEAT 
SET @param=@param+1;
SELECT @param;
UNTIL @param>param
END REPEAT;
END $$
DELIMITER;
CALL test_repeat(2)

3、loop语句
LOOP允许某特定语句或语句群的重复执行，实现一个简单的循环构造。在循环内的语句一直重复直循环被退出，退出通常伴随着一个LEAVE 语句。
LOOP语句可以被标注。除非begin_label存在，否则end_label不能被给出，并且如果两者都出现，它们必须是同样的。

语法
[begin_label:] LOOP
statement_list
END LOOP [end_label]

实例
-- loop语句
DROP PROCEDURE test_loop;
DELIMITER$$
CREATE PROCEDURE test_loop (IN param INT) 
BEGIN
f_loop :
LOOP
IF param < 1 
THEN LEAVE f_loop ;
ELSE SET param = param - 1 ;
SELECT 
param ;
END IF ;
END LOOP f_loop ;
END $$

DELIMITER;
CALL test_loop(4);

4、leave语句
这个语句被用来退出任何被标注的流程控制构造。它和BEGIN … END或循环一起被使用

5、iterate
ITERATE只可以出现在LOOP, REPEAT, 和WHILE语句内。ITERATE意思为：“再次循环。”

CREATE PROCEDURE doiterate(p1 INT)
BEGIN
label1: LOOP
SET p1 = p1 + 1;
IF p1 < 10 THEN ITERATE label1; END IF;
LEAVE label1;
END LOOP label1;
SET @x = p1;
END

 

CREATE PROCEDURE sp_name ([proc_parameter[,...]])

[characteristic ...] routine_body

其中，sp_name参数是存储过程的名称；proc_parameter表示存储过程的参数列表； characteristic参数指定存储过程的特性；routine_body参数是SQL代码的内容，可以用BEGIN…END来标志SQL代码的开始和结束。

[ IN | OUT | INOUT ] param_name type

其中，IN表示输入参数；OUT表示输出参数； INOUT表示既可以是输入，也可以是输出； param_name参数是存储过程的参数名称；type参数指定存储过程的参数类型，该类型可以是MySQL数据库的任意数据类型。

characteristic参数有多个取值。其取值说明如下：

LANGUAGE SQL：说明routine_body部分是由SQL语言的语句组成，这也是数据库系统默认的语言。

[NOT] DETERMINISTIC：指明存储过程的执行结果是否是确定的。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是非确定的，相同的输入可能得到不同的输出。默认情况下，结果是非确定的。

{ 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。

SQL SECURITY { DEFINER | INVOKER }：指明谁有权限来执行。DEFINER表示只有定义者自己才能够执行；INVOKER表示调用者可以执行。默认情况下，系统指定的权限是DEFINER。

COMMENT 'string'：注释信息。

技巧：创建存储过程时，系统默认指定CONTAINS SQL，表示存储过程中使用了SQL语句。但是，如果存储过程中没有使用SQL语句，最好设置为NO SQL。而且，存储过程中最好在COMMENT部分对存储过程进行简单的注释，以便以后在阅读存储过程的代码时更加方便。

四、举例说明

#1.下面创建一个名为num_from_employee的存储过程。

CREATE PROCEDURE num_from_employee (IN emp_id INT, OUT count_num INT )

READS SQL DATA

BEGIN

SELECT COUNT(*) INTO count_num

FROM employee

WHERE d_id=emp_id ;

END

上述代码中，存储过程名称为num_from_employee；输入变量为emp_id；输出变量为count_num。SELECT语句从employee表查询d_id值等于emp_id的记录，并用COUNT(*)计算d_id值相同的记录的条数，最后将计算结果存入count_num中。代码的执行结果如下：

mysql> DELIMITER &&

mysql> CREATE PROCEDURE num_from_employee

(IN emp_id INT, OUT count_num INT )

-> READS SQL DATA

-> BEGIN

-> SELECT COUNT(*) INTO count_num

-> FROM employee

-> WHERE d_id=emp_id ;

-> END &&

Query OK, 0 rows affected (0.09 sec)

mysql> DELIMITER ;

代码执行完毕后，没有报出任何出错信息就表示存储函数已经创建成功。以后就可以调用这个存储过程，数据库中会执行存储过程中的SQL语句。

说明：MySQL中默认的语句结束符为分号(;)。存储过程中的SQL语句需要分号来 结束。为了避免冲突，首先用"DELIMITER &&"将MySQL的结束符设置为&&。最后再用"DELIMITER ;"来将结束符恢复成分号。这与创建触发器时是一样的。

#2.下面创建一个名为name_from_employee的存储函数。代码如下：

CREATE FUNCTION name_from_employee (emp_id INT )

RETURNS VARCHAR(20)

BEGIN

RETURN (SELECT name

FROM employee

WHERE num=emp_id );

END

上述代码中，存储函数的名称为name_from_employee；该函数的参数为emp_id；返回值是VARCHAR类型。SELECT语句从employee表查询num值等于emp_id的记录，并将该记录的name字段的值返回。代码的执行结果如下：

mysql> DELIMITER &&

mysql> CREATE FUNCTION name_from_employee (emp_id INT )

-> RETURNS VARCHAR(20)

-> BEGIN

-> RETURN (SELECT name

-> FROM employee

-> WHERE num=emp_id );

-> END&&

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

结果显示，存储函数已经创建成功。该函数的使用和MySQL内部函数的使用方法一样。

 

#3 变量的使用

在存储过程和函数中，可以定义和使用变量。用户可以使用DECLARE关键字来定义变量。然后可以为变量赋值。这些变量的作用范围是BEGIN…END程序段中。本小节将讲解如何定义变量和为变量赋值。

1．定义变量

MySQL中可以使用DECLARE关键字来定义变量。定义变量的基本语法如下：

DECLARE var_name[,...] type [DEFAULT value]

其中， DECLARE关键字是用来声明变量的；var_name参数是变量的名称，这里可以同时定义多个变量；type参数用来指定变量的类型；DEFAULT value子句将变量默认值设置为value，没有使用DEFAULT子句时，默认值为NULL。

下面定义变量my_sql，数据类型为INT型，默认值为10。代码如下：

DECLARE my_sql INT DEFAULT 10 ;

2．为变量赋值

MySQL中可以使用SET关键字来为变量赋值。SET语句的基本语法如下：

SET var_name = expr [, var_name = expr] ...

其中，SET关键字是用来为变量赋值的；var_name参数是变量的名称；expr参数是赋值表达式。一个SET语句可以同时为多个变量赋值，各个变量的赋值语句之间用逗号隔开。

 

#4.下面为变量my_sql赋值为30。代码如下：

SET my_sql = 30 ;

MySQL中还可以使用SELECT…INTO语句为变量赋值。其基本语法如下：

SELECT col_name[,…] INTO var_name[,…]

FROM table_name WEHRE condition

其中，col_name参数表示查询的字段名称；var_name参数是变量的名称；table_name参数指表的名称；condition参数指查询条件。

 

#5.下面从employee表中查询id为2的记录，将该记录的d_id值赋给变量my_sql。代码如下：

SELECT d_id INTO my_sql

FROM employee WEHRE id=2 ;

5.1 定义条件和处理程序

定义条件和处理程序是事先定义程序执行过程中可能遇到的问题。并且可以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题，并提出解决办法。这样可以增强程序处理问题的能力，避免程序异常停止。MySQL中都是通过DECLARE关键字来定义条件和处理程序。本小节中将详细讲解如何定义条件和处理程序。

1．定义条件

MySQL中可以使用DECLARE关键字来定义条件。其基本语法如下：

DECLARE condition_name CONDITION FOR condition_value

condition_value:

SQLSTATE [VALUE] sqlstate_value | mysql_error_code

其中，condition_name参数表示条件的名称；condition_value参数表示条件的类型；sqlstate_value参数和mysql_error_code参数都可以表示MySQL的错误。例如ERROR 1146 (42S02)中，sqlstate_value值是42S02，mysql_error_code值是1146。

 

#6.下面定义"ERROR 1146 (42S02)"这个错误，名称为can_not_find。可以用两种不同的方法来定义，代码如下：

//方法一：使用sqlstate_value

DECLARE can_not_find CONDITION FOR SQLSTATE '42S02' ;

//方法二：使用mysql_error_code

DECLARE can_not_find CONDITION FOR 1146 ;

2．定义处理程序

MySQL中可以使用DECLARE关键字来定义处理程序。其基本语法如下：

DECLARE handler_type HANDLER FOR

condition_value[,...] sp_statement

handler_type:

CONTINUE | EXIT | UNDO

condition_value:

SQLSTATE [VALUE] sqlstate_value |

condition_name | SQLWARNING

| NOT FOUND | SQLEXCEPTION | mysql_error_code

其中，handler_type参数指明错误的处理方式，该参数有3个取值。这3个取值分别是CONTINUE、EXIT和UNDO。CONTINUE表示遇到错误不进行处理，继续向下执行；EXIT表示遇到错误后马上退出；UNDO表示遇到错误后撤回之前的操作，MySQL中暂时还不支持这种处理方式。

注意：通常情况下，执行过程中遇到错误应该立刻停止执行下面的语句，并且撤回前面的操作。但是，MySQL中现在还不能支持UNDO操作。因此，遇到错误时最好执行EXIT操作。如果事先能够预测错误类型，并且进行相应的处理，那么可以执行CONTINUE操作。

condition_value参数指明错误类型，该参数有6个取值。sqlstate_value和mysql_error_code与条件定义中的是同一个意思。condition_name是DECLARE定义的条件名称。SQLWARNING表示所有以01开头的sqlstate_value值。NOT FOUND表示所有以02开头的sqlstate_value值。SQLEXCEPTION表示所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值。sp_statement表示一些存储过程或函数的执行语句。

 

#7.下面是定义处理程序的几种方式。代码如下：

//方法一：捕获sqlstate_value

DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'

SET @info='CAN NOT FIND';

//方法二：捕获mysql_error_code

DECLARE CONTINUE HANDLER FOR 1146 SET @info='CAN NOT FIND';

//方法三：先定义条件，然后调用

DECLARE can_not_find CONDITION FOR 1146 ;

DECLARE CONTINUE HANDLER FOR can_not_find SET

@info='CAN NOT FIND';

//方法四：使用SQLWARNING

DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';

//方法五：使用NOT FOUND

DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND';

//方法六：使用SQLEXCEPTION

DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

上述代码是6种定义处理程序的方法。第一种方法是捕获sqlstate_value值。如果遇到sqlstate_value值为42S02，执行CONTINUE操作，并且输出"CAN NOT FIND"信息。第二种方法是捕获mysql_error_code值。如果遇到mysql_error_code值为1146，执行CONTINUE操作，并且输出"CAN NOT FIND"信息。第三种方法是先定义条件，然后再调用条件。这里先定义can_not_find条件，遇到1146错误就执行CONTINUE操作。第四种方法是使用SQLWARNING。SQLWARNING捕获所有以01开头的sqlstate_value值，然后执行EXIT操作，并且输出"ERROR"信息。第五种方法是使用NOT FOUND。NOT FOUND捕获所有以02开头的sqlstate_value值，然后执行EXIT操作，并且输出"CAN NOT FIND"信息。第六种方法是使用SQLEXCEPTION。SQLEXCEPTION捕获所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值，然后执行EXIT操作，并且输出"ERROR"信息。

参考链接：https://blog.csdn.net/weixin_32201711/article/details/113142123?spm=1001.2101.3001.6650.2&utm_medium=distribute.pc_relevant.none-task-blog-2~default~CTRLIST~Rate-2-113142123-blog-127013006.pc_relevant_default&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2~default~CTRLIST~Rate-2-113142123-blog-127013006.pc_relevant_default&utm_relevant_index=5

标签：语句,存储,END,浅谈,SQL,value,param,MySQL,name
From： https://www.cnblogs.com/linuxmysql/p/16951011.html

相关文章

  • MySQL 高级SQL语句
    一、高级SQL语句（进阶查询）先准备2个表一个location表：  一个store_info表：  1.1selectselect，显示表格中一个或数个字段的所有数据记录。    1.2dis......
  • Mysql主从复制，读写分离
    一、Mysql主从复制概述在企业中，业务数据量通常都比较大，单台Mysql在安全性，高可用性和高并发方面都无法满足实际需求，因此可以通过主从复制的方式来同步数据，再通过读写分离来提......
  • MySQL数据库 事务
    一、事务的概念事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么......
  • MySQL存储引擎
    1.MyISAM底层存储（非聚集索引方式）与InnoDB底层存储（聚集索引方式）1.1MyISAM底层存储（非聚集索引方式）Myisam创建表后生成的文件有三个：frm:创建表的语句MYD:表里面的数据文......
  • MySQL 日志管理
    一日志分类日志种类 作用错误日志 记录MySQL服务器启动、关闭及运行错误等信息事务日志 1、redolog重做日志2、undolog回滚日志查询日志 记录所有的sql慢查询日志 记......
  • MySQL数据库用户管理
    一、数据库用户管理1.1新建用户 CREATEUSER'用户名'@'来源地址'[IDENTIFIEDBY[PASSWORD]'密码'];复制代码'用户名'： 指定将创建的用户名。'来源地址'： 指定......
  • MYSQL数据库之索引
    一、索引的概念索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址（类似于c语言的链表通过指针指向数据记录的内存地址）。使用索引后可......
  • mysql test single table huge rows
    1.showcreatetabletb2;showcreatetabletb3;  2.desctb2;desctb3;  3.selectcount(Idx)fromtb2;selectcount(Idx)fromtb3; desctb2;desc......
  • Mysql的一些基操
    一、用户权限问题创建用户并授权mysql创建用户的方法分成三种：CREATEUSER的方法、GRANT的方法、INSERT USER表的方法。CREATEUSER'username'@'host'IDENTIFIEDBY......
  • 浅谈性能测试中混合场景比例控制方式
    控制比例的方式很多，下面来举例研究。 方式一：吞吐量控制器控制比例（同一个线程组中）      持续3秒  结论：可以控制比例 方式二：吞吐量控制器控......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99