mysql 存储过程

最新推荐文章于 2021-02-27 11:20:59 发布

天才小小布

最新推荐文章于 2021-02-27 11:20:59 发布

阅读量551

点赞数

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

本文链接：https://blog.csdn.net/w410589502/article/details/56282892

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

一、存储过程

1 存储过程定义

存储过程（英文：Stored Procedure）是在大型数据库系统中，为了完成特定功能而编写的一组的SQL语句集。存储过程经编译存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

2 存储过程优缺点

2.1 优点

（1）减少了脚本的执行环节，缩短了获取数据的时间。存储过程只在创建的时进行编译，在调用使用的时候直接执行，不需再次编译；而一般SQL语句每次执行前都需要编译一次，故效率没有存储过程高；
（2）减少网络传输量，提高了传输速度。存储过程编译后存储在数据库服务器上，使用的时候只需要指定存储过程的名字并给出参数（如果该存储过程带有参数）就可以了；而一般SQL语句需要将所执行语句字符串传输到数据库服务器端，相比于存储过程而言向数据库服务端传送的字符串长度比较大；
（3）安全性比较高。为存储过程参数赋值只能使用问号传参的形式（这一点可以通过下面JDBC对MySQL数据库存储过程的调用例子体现出来），这样可以防止SQL注入式攻击；一般SQL语句也可以做到防止SQL注入式攻击，但是并不是必须的。可以将Grant、Deny以及Revoke权限应用于存储过程，即言可以设定只有某些用户才具有对指定存储过程的使用权；

2.2 缺点

（1）如果在一个程序系统中大量的使用存储过程，当程序交付使用的时候随着客户需求的增加会导致数据结构的变化，接着就是存储过程的修改，这样系统维护就会越来越难并且代价也会越来越大。

3 mysql存储过程基本语法

3.1 基本语法函数

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

[characteristic ...] routine_body

sp_name：存储过程的名称，默认在当前数据库中创建。这个名称应当尽量避免与MySQL的内置函数相同的名称。
proc_parameter：存储过程的参数列表。
格式：[IN|OUT|INOUT]param_name type
（1）param_name为参数名。
（2）type为参数的数据类型。多个参数彼此间用逗号分隔。输入参数、输出参数和输入/输出参数，分别用in/out/inout标识。参数的取名不要与数据表的列名相同。
Characteristic：存储过程的某些特征设定
（1）COMMENT’string’：用于对存储过程的描述，其中string为描述内容，comment为关键字。
（2）LANGUAGE SQL：指明编写这个存储过程的语言为SQL语言。这个选项可以不指定。
（3）DETERMINISTIC：表示存储过程对同样的输入参数产生相同的结果；NOT DETERMINISTIC，则表示会产生不确定的结果（默认）。
（4）contains sql | no sql | reads sql data | modifies sql data Contains sql：表示存储过程包含读或写数据的语句（默认）
No sql 表示不包含sql语句
Reads sql data 表示存储过程只包含读数据的语句
Modifies sql data 表示存储过程只包含写数据的语句
（5）sql security：这个特征用来指定存储过程使用创建该存储过程的用户(definer)的许可来执行，还是使用调用者(invoker)的许可来执行。默认是definer
Routine_body：存储过程的主体部分，包含了在过程调用的时候必须执行的sql语句。以begin开始，以end结束。如果存储过程体中只有一条sql语句,可以省略begin-end标志。

3.2 编写基本存储过程

3.2.1：基本语法

1：创建

CREATE PROCEDURE 存储过程名 (参数列表)
   BEGIN
         SQL语句代码块
   END

2：调用
调用存储过程的方法是：CALL加上过程名以及一个括号。

CALL 存储过程名();

哪怕是不用传递参数，存储过程名字后面的括号“（）”也是必须的。
3：删除

DROP PROCEDURE IF EXISTS 存储过程名;

:4：参数
MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：
CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名数据类形…])
IN 输入参数：表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
OUT 输出参数：该值可在存储过程内部被改变，并可返回
INOUT 输入输出参数：调用时指定，并且可被改变和返回

IN参数例子

CREATE PROCEDURE INT_TEST(IN X INT)
BEGIN
    SELECT X;
    SET X=100;
    SELECT X;
END

执行

set @para=4;
CALL INT_TEST(@para)

结果1：X值为4，结果2：X值为100
以上可以看出，X虽然在存储过程中被修改，但并不影响@para的值。

OUT参数例子

CREATE PROCEDURE OUT_TEST (OUT p_out int)  
BEGIN 
SELECT p_out;  
SET p_out=2;  
SELECT p_out;  
END;

执行

SET @p_out=1;  
CALL OUT_TEST(@p_out);

结果1：p_out值为null，结果2：p_out值为2

INOUT参数例子

CREATE PROCEDURE INOUT_TEST(INOUT p_inout int)   
BEGIN 
SELECT p_inout;  
SET p_inout=2;  
SELECT p_inout;   
END;

执行

SET @p_inout=1;  
CALL INOUT_TEST(@p_inout) ;

结果1：p_inout值为1，结果2：p_inout值为2

3.2.2：存储过程体

存储过程体中可以使用各种sql语句和过程式语句的组合，来封装数据库应用中复杂的业务逻辑和处理规则，以实现数据库应用的灵活编程。下面主要介绍几个用于构造存储过程体的常用语法元素。
1：局部变量
在存储过程体中可以声明局部变量，用来存储存储过程体中临时结果。

DECLARE var_name[,…] type [DEFAULT value]

var_name：指定局部变量的名称
type：用于声明局部变量的数据类型
DEFAULT子句：用于为局部变量指定一个默认值。若没有指定，默认为null
例如：

declare id int(10);

使用说明：
（1）局部变量只能在存储过程体的begin…end语句块中声明。
（2）局部变量必须在存储过程体的开头处声明。
（3）局部变量的作用范围仅限于声明它的begin..end语句块，其他语句块中的语句不可以使用它。
（4）局部变量不同于用户变量，两者区别：局部变量声明时，在其前面没有使用@符号，并且它只能在begin..end语句块中使用；而用户变量在声明时，会在其名称前面使用@符号，同时已声明的用户变量存在于整个会话之中。

2：set语句
使用set语句为局部变量赋值

Set var_name = expr;

例如

Set id=10;

3：select … into 语句
使用select … into语句对变量进行赋值，该语句在数据库中进行查询，并将得到的结果赋值给变量。

Select col_name[,…] into var_name[,…] table_expr

col_name：要从数据库中查询的列字段名
var_name：变量名，列字段名按照在列清单和变量清单中的位置对应，将查询得到的值赋给对应位置的变量
table_expr：select语句中的其余部分，包括可选的from子句和where子句

例如

select name,cellphone into p_name,p_cellphone from user where id = 1;

说明：存储过程体中的select…into语句返回的结果集只能有一行数据。
整体实例

create procedure getUserInfo  
()  
Begin 
declare p_name varchar(32);  
declare p_cellphone varchar(16);  
select name,cellphone into p_name,p_cellphone from user where id = 1;  
End

补充：将变量值返回给调用者
在存储过程中定义的变量，经过一系列的处理之后，结果值可能需要返回给存储过程调用者。那么如何返回呢？方便的做法是使用select语句将变量作为结果集返回，因此在上面一段代码的基础上加上一句：select p_name,p_cellphone;

create procedure getUserInfo  
()  
Begin 
declare p_name varchar(32);  
declare p_cellphone varchar(16);  
select name,cellphone into p_name,p_cellphone from user where id = 1;  
select p_name,p_cellphone;  
End

4：定义处理程序
是事先定义程序执行过程中可能遇到的问题。并且可以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题，并提出解决方法。

DECLARE handler_type HANDLER FOR condition_value[,…] sp_statement

handler_type：CONTINUE | EXIT | UNDO
Condition_value：Sqlwarning | not found | sqlexception

DECLARE p_name CURSOR for select name from user where id = 1;

（未完善，待补充。。。）

5：流程控制语句
（1）条件判断语句
if语句：

If search_condition then statement_list 
[else if search_condition then statement_list]… 
[else statement_list] 
End if

search_condition参数：条件判断语句
statement_list参数：不同条件的执行语句

case语句：
表达形式1：

Case case_value 
When when_value then statement_list 
[When when_value then statement_list]… 
[else statement_list] 
End case

表达式2：

Case
When search_condition then statement_list 
End case

（2）循环语句
while语句、repeat语句和loop语句。
while语句：

[begin_label:] 
while search_condition do 
Statement_list 
End while 
[end_label]

判断条件search_condition是否为真,若为真,则执行statement_list中的语句，然后再进行判断，如若仍然为真则继续循环，直至条件判断不为真时循环结束。

repeat语句语法格式：

[begin_label:] 
repeat 
Statement_list 
Until search_condition 
End repeat 
[end_label]

Repeat语句首先执行statement_list中的语句，然后判断条件search_condition是否为真，倘若为真，则结束循环，若不为真，继续循环。
Repeat先执行后判断，while先判断后执行。

3.2.3 函数

（未完待补充。。。）

这个总结的太累了，我先休息一下

参考文章：
MySql存储过程与函数详解
 MySQL存储过程中的语法学习

天才小小布

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 存储过程

一、存储过程1 存储过程定义存储过程（英文：Stored Procedure）是在大型数据库系统中，为了完成特定功能而编写的一组的SQL语句集。存储过程经编译存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。2 存储过程优缺点2.1 优点（1）减少了脚本的执行环节，缩短了获取数据的时间。存储过程只在创建的时进行编译，在调用使用的时候直接执行
复制链接

扫一扫