<>1 概述

存储过程就像是程序中的子程序,存储在数据库中。存储过程可以有名称、参数列表和 SQL 语句。使用 CALL 语句调用。

<>2 特点

* 运行速度快。利用缓存的优势等。
* 可移植的
好处:

* 简化应用开发人员的工作。
* 增强安全性。
* 减少数据在数据库和应用服务器之间的传输。
<>3 案例

实际运行创建一个存储存储过程
DELIMITER;; CREATE DEFINER=`root`@`localhost` PROCEDURE `selectitem`() BEGIN
#Routine body goes here... SELECT * from sys_role; END;; DELIMITER;
说明:
分为几部分: DELIMITER 设置结束符,设置;;的原因是避免使用;时会直接导致创建存储过程中出现;时会结束。 create 创建 DEFINER=`
root`@`localhost` 声明定义者 PROCEDURE 存储过程关键字 `selectitem`() 存储过程名称 -- 执行的逻辑体 BEGIN
#Routine body goes here... SELECT * from sys_role; END
调用存储过程,使用call命令
call selectitem`
<>4 特征子句(了解)

在创建一个存储过程时可以添加对这个过程的特征,称为特征子句。有以下特征

* COMMENT
它用于描述存储的例程

* LANGUAGE SQL
表明储存过程使用SQL编写

* NOT DETERMINISTIC
不确定性,相同的输入参数产生不同结果

* CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA
CONTAINS SQL:包含 SQL (默认值)

NO SQL:不包含 SQL 语句

READS SQL DATA:含读取数据的语句,不含写入数据的语句

MODIFIES SQL DATA:包含写入数据的语句

* SQL SECURITY { DEFINER | INVOKER }
指定安全上下文,默认值是 DEFINER,就是执行该储存过程的方式。

如果是DEFINER ,执行存储过程前验证definer对应的用户是否存在,是否拥有执行权限。

如果是INVOKER ,执行存储过程前验证调用该储存过程的用户是否有对应权限。

<>4.1举例
CREATE PROCEDURE p2 () LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY DEFINER
COMMENT '' SELECT * FROM student;
<>5 复合语句

复合语句是一个包含执行块,变量,条件等的处理程序。

5.6版本开始Mysql有以下复合语句:

*
BEGIN … END

*
Statement Label 陈述标签

*
DECLARE 声明

*
Variables in Stored Programs 存储程序中的变量

*
Flow Control Statements 流程控制声明

*
Cursors 游标

*
Condition Handling 状况处理

<>5.1 BEGIN … END

BEGIN … END是很常用的语句块,通常代表的是一个操作域。用于编写复合语句。
CREATE DEFINER=`root`@`localhost` PROCEDURE `userpre`() COMMENT '测试存储过程' BEGIN
#Routine body goes here... SELECT * from user; END
在一个BEGIN … END块内的局部变量与其他BEGIN … END块的局部变量不共享

<>5.2 DECLARE

DECLARE 能够用于在存储过程中声明变量。 需要在BEGIN… END 复合语句中使用。在使用前先声明。
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`() COMMENT '测试存储过程'
BEGIN -- 局部变量 DECLARE fr int; #Routine body goes here... SELECT * from user; END
局部变量,只在声明它们的 BEGIN END 块中有效。
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`() COMMENT '测试存储过程'
BEGIN DECLARE fr int; set fr = 6 + 2; SELECT fr; END

<>6 用户变量

除了有局部变量,还有用户变量,定义使用@变量名。一个客户端定义的变量不能被其它客户端看到或使用。当客户端退出时,该客户端连接的所有变量将自动释放。
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`() COMMENT '测试存储过程'
BEGIN set @a = 34; set @b = @a + 6; SELECT @b,@a; END

<>7 存储过程参数

使用存储过程时能够传入使用参数,进行逻辑的处理。

参数有三种:

* IN(默认)
表明该参数是一个输入参数,无需输出

* OUT
表明该参数是一个输出参数,执行完存储过程之后会返回该值

* INOUT
既是输入也是输出

案例IN:
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`(IN a INT) COMMENT
'测试存储过程' BEGIN SELECT a; END

案例OUT:
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`(OUT o INT) COMMENT
'测试存储过程' BEGIN set o = 2 + 1; END

案例INOUT
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`(INOUT o INT)
COMMENT '测试存储过程' BEGIN set o = o + 1; END

<>8 流控制语句

存储过程当然少不了语句的流程控制。在MySQL中 支持 IF、 CASE、 ITERATE、 LEAVE、 LOOP、 WHILE 和 REPEAT 结构。

<>8.1 分支语句

<>8.1.1 if

if语句学过编程语言都知道了是一种分支语句,但注意不同于SQL的if函数。

语法:
IF condition THEN statement(s) [ELSEIF condition THEN statement(s)] ... [ELSE
statement(s)] END IF
案例:
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`(IN tname VARCHAR(16
)) COMMENT '测试存储过程' BEGIN if tname = '1' then SELECT '输入1'; elseif tname = '2'
then SELECT '输入2'; else select '非1非2'; end if; END
输入 1 结果

<>8.1.2 case

case也是一个条件分支语句。语法有两种。
# 语法1 CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN
statement_list] ... [ELSE statement_list] END CASE # case_value 可以是一个值
when_value判断与case_value是否相等才去执行该分支 # 语法2 CASE WHEN search_condition THEN
statement_list[WHEN search_condition THEN statement_list] ... [ELSE
statement_list] END CASE # search_condition 表达式 比如 a >2
case来改造上面if的案例,语法一:
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`(IN tname VARCHAR(16
)) COMMENT '测试存储过程' BEGIN case tname when '1' then SELECT '输入1'; when '2' then
SELECT '输入2'; else select '非1非2'; end case; END
语法2:
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`(IN tname VARCHAR(16
)) COMMENT '测试存储过程' BEGIN case when tname = '1' then SELECT '输入1'; when tname =
'2' then SELECT '输入2'; else select '非1非2'; end case; END
<>8.2 循环

循环相关的语句关键字有ITERATE、 LEAVE、 LOOP、 WHILE、REPEAT 。

<>8.2.1 WHILE

语法:
[begin_label:] WHILE search_condition DO statement_list END WHILE [end_label]
当search_condition条件为真时,执行statement_list的代码块。

例子:

使用while循环叠加总数
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`(IN number INT)
COMMENT '测试存储过程' BEGIN declare i int DEFAULT 0; set @sum = 0; WHILE number > i
DO set i = i + 1; set @sum = @sum + i; END WHILE; SELECT @sum; END
参数5时,就是1+2+3+4+5,结果:

<>8.2.2 ITERATE

ITERATE是重新启动循化的意思,使用在循 LOOP、 REPEAT 和 WHILE语句内,用法LEAVE label。类比Java的continue。

label 是标签 可以用在表示一个循环体,下面有例子会用到label。

<>8.2.3 LEAVE

LEAVE 语句用于退出循环。类比Java的break。

在SQL中还有RETURN关键字,但只用于函数,存储过程是不能使用的。

<>8.2.4 LOOP

重复执行语句

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

结合user表,写一个loop的例子,原本的表内容如下,我们需要对age插入一个随机数。

CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`(IN number INT)
COMMENT '测试存储过程' BEGIN declare i int DEFAULT 1; set @sum = 0; loop_label:LOOP
update user set age = (RAND() * 10) where id = i; set i = i + 1; -- 定义退出的条件 if
number< i then leave loop_label; end if; end loop loop_label; END
输入5为参数,结果:

<>8.2.5 REPEAT

REPEAT 重复执行语句,与LOOP类似都是先执行后判断的循环语句。能够使用标签。

语法:
[begin_label:] REPEAT statement_list UNTIL search_condition END REPEAT [
end_label]
例子:

和while的例子一样求和
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`(IN number INT)
COMMENT '测试存储过程' BEGIN declare i int DEFAULT 1; set @sum = 0; REPEAT set @sum =
i+ @sum; set i = 1 + i; UNTIL i > number end REPEAT; SELECT @sum; END
参数传5,结果仍然是15。

<>9 修改存储过程

对储存过程修改可以使用ALTER语句,但无法修改储存过程的实现逻辑和参数类型等。

语法:
ALTER PROCEDURE proc_name [characteristic ...]characteristic: COMMENT 'string'
| LANGUAGE SQL | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
例子:

修改comment
mysql> alter procedure user_test_pre COMMENT '修改过程的注释'; Query OK, 0 rows
affected (0.00 sec) mysql>
查看结果:

<>10 删除存储过程

指定数据库中删除存储过程

语法:
DROP PROCEDURE [ IF EXISTS ] <过程名>
<>11 游标

数据库游标是一种控制结构,可以遍历数据库中的记录。游标可以同时操作整个结果集。

游标具有以下属性:

* Asensitive:
有两种游标:敏感游标和不敏感游标。敏感游标指向实际数据,不敏感游标使用数据的临时副本。如果不更新敏感游标所使用的数据,则更安全。 MySQL游标是敏感的。
* Read only: 只读,无法通过游标更新基础表中的数据
* Nonscrollable: 只能在一个方向上遍历,无法跳过行读取或以相反的顺序读取行。
如何使用游标,一共有4步:

* 声明游标 DECLARE cursor_name CURSOR FOR select_statement
* 打开游标 OPEN cursor_name
* 游标提取数据到变量中 FETCH [[NEXT] FROM] cursor_name INTO var_name [, var_name] ...
读取游标中的数据到变量。

* 关闭游标 CLOSE cursor_name
注意使用游标时,声明的顺序需要先声明变量、声明条件、然后声明游标、处理程序声明这个顺序。

例子:

得到所以的名称,并且拼接在一起查询出来。
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`() COMMENT '修改过程的注释'
BEGIN declare var_name,every_name VARCHAR(255) DEFAULT ""; declare b int DEFAULT
0; declare cursor_a CURSOR FOR (select name from user); declare continue handler
for not FOUND SET b = 1; open cursor_a; read_l:LOOP FETCH cursor_a into var_name
; -- 判断退出循环 if b=1 then LEAVE read_l; end if; set every_name = concat(every_name
,",",var_name); end LOOP read_l; CLOSE cursor_a; SELECT SUBSTR(every_name FROM 2
); END
处理程序声明:declare continue handler for not FOUND SET b =
1;语句由于游标一直向下读数据,我们需要一个条件知道它读完,使用NOT FOUND处理情况,当无数据设置b = 1。

注意:

* FETCH后游标才会向下走,所以统计时FETCH后就进行判断就能防止循环一次。
* 接收的变量名请不要和表的列名一致,不然可能获取不到值。
<>12 存储程序的访问控制

存储程序包括存储过程、函数、触发器。

在使用存储程序和视图时要确认有权限执行,所有存储的程序(过程、函数和触发器)和视图都可以具有一个 DEFINER 属性,能否执行在DEFINER
属性控制,如果有SQL SECURITY定义,优先使用SQL SECURITY中的定义。如果在存储的程序或视图定义中省略 DEFINER
属性,则默认帐户是创建对象的用户。

MySQL 使用以下规则来控制用户,能够在DEFINER 属性中指定哪些帐户:

* 只有在拥有 SUPER 特权的情况下,才可以指定您自己帐户以外的 DEFINER 值
* 如果没有 SUPER 特权,那么唯一合法的用户值是您自己的帐户,可以按字面意思指定,也可以使用 CURRENT_USER。不能将定义者设置为其他帐户
* 尽可能在对象定义中使用 SQL SECURITY INVOKER,以便只有具有与执行的操作相应的权限的用户才能使用。
* 如果在使用具有 SUPER 特权的帐户时创建定义上下文存储的程序或视图,请指定一个显式 DEFINER
属性,该属性指定一个帐户,该帐户只拥有执行操作所需的特权。只有在绝对必要时才指定高特权 DEFINER 帐户
* 管理员可以通过不授予用户 SUPER 特权来防止用户指定高特权 DEFINER 帐户
* 可以通过不授予未授权用户特殊权限来防止,其他用户调用未授权的数据
* 没有 EXECUTE 权限的用户不能引用存储过程或函数
* 视图不能由没有相应权限的用户调用执行
<>13 其他

<>1.查看存储过程

1.1 查看创建的存储过程
SHOW CREATE PROCEDURE xxx(过程名);
1.2 查看过程状态
show procedure status where db='数据库名';
<>2.一个储存过程完整的语法
CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name
([proc_parameter[,...]]) [characteristic ...] routine_body proc_parameter: [ IN
| OUT | INOUT ] param_name type type: Any valid MySQL data 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
<>3.传参执行sql特点,在存储过程执行sql,有些时候构建sql的参数并不会生效。比如:
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`(IN tname VARCHAR(16
)) COMMENT '测试存储过程' BEGIN -- 创建一个数据库 CREATE DATABASE IF NOT EXISTS tname; END
假设:执行过程传入ta并不会创建名称ta的库,而是创建tname数据库。

修改一下:
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`(IN tname VARCHAR(16
)) COMMENT '测试存储过程' BEGIN -- 创建一个数据库 set @cdb = concat("CREATE DATABASE IF NOT
EXISTS ", tname); -- 使用预编译的方式 PREPARE tempsqsl from @cdb; execute tempsqsl; END
这样就能解决问题。

技术
©2019-2020 Toolsou All rights reserved,
TypeScript:函数类型接口8道大厂指针笔试题让你秒杀指针!!!MySQL 日期时间加减mysql 查询条件之外的数据_mysql 查询符合条件的数据查linux的操作系统版本,如何查看Linux操作系统版本?将String类型转换成Map数据类型使用uuid做MySQL主键,被老板,爆怼一顿C语言中的字符串函数和字符函数linux服务器中毒排查--基础篇C# ASCII码字符转换