MySQL 第六章过程式数据库对象.1存储对象

6.1 存储过程

    在MySQL中，可以定义一段程序放在数据库中，它是最重要的数据库对象之一，可以由声明式SQL语句（create，update，select等）和过程式SQL语句（如if-then-else）组成，存储过程可以由触发器或者另一个存储过程来调用。

    存储过程的优点如下：

1. 存储过程在服务器端运行，执行速度快
2. 存储过程执行一次后，其执行规划就驻留在高速缓冲存储器，以后的操作中，只需要从高速缓冲存储器中调用已经编译好的二进制代码执行，提高了系统性能
3. 确保数据库的安全，使用存储过程可以完成所有数据库操作，并通过编程的方式控制对数据库信息的访问。

6.1.1 创建存储过程

    创建存储过程命令如下：

create procedure 存储过程名([参数,...])
[特征..] 存储过程体

1. 存储过程参数

    参数格式为：

[ in | out | inout ] 参数名 参数类型

• 系统默认在当前数据库中创建，需要在特定数据库中创建存储过程时，则要在名称前面加上数据库名称：数据库名称.存储过程名
• 当存储过程有多个参数时候中间用逗号隔开。MySQL存储过程支持三种类型的参数：输入参数（in）、输出参数（out）、输入/输出参数。输入参数使数据可以传递给一个存储过程，当需要返回一个答案或结果时，使用输出参数，输入/输出参数既可以充当输入参数，也可以充当输出参数。
• 存储过程可以有0个、1个或多个参数，存储过程即使不加参数，名称后面的括号 也是不可省略的
• 参数的名字不要采用列的名字，虽然不会报错，但是在存储过程中的sql语句会将参数名看作列名，从而引发不可预知的结果

2. 存储过程特征

    特征格式为：

language SQL
| [not] deterministic
| { contains sql | no sql | reads sql data | modifies sql data }
| sql security { definer | invoker }
| comment 'string'

• language sql 表名编写这个存储过程的语言为sql语言
• deterministic：设置为deterministic表示存储过程对同样的输入参数产生相同的结果，设置为not deterministic 则表示产生不确定的结果，默认为not deterministic
• contains sql：表示存储过程不包含读或写数据的语句； no sql：表示存储过程不包含sql语句
  reads sql data：表示存储过程包含读数据的语句，但不包含写数据的语句。 modifies sql data：表示存储过程包含写数据的语句
  如果这些特征没有明确给定，默认的是 contains sql。
• sql security：可以用来指定存储过程是使用创建该存储过程的用户（definer）的许可来执行，还是使用调用者（invoker）的许可来执行，默认值是definer
• comment ’string‘：对存储过程的描述（就是备注），string为描述内容，这个信息可以用show create procedure来显示

3. 存储过程体

    存储过程体包含了在过程调用的时候必须执行的语句，这个部分总是以begin开始，以end结束。当然在存储过程体中只有一个sql语句时可以省略begin-end标志。

    在MySQL中，服务器处理语句是以分号为结束标志，但是在创建存储过程中，可能包含多个sql语句，每个语句如果都使用分号结尾，则程序遇到第一个分号就会以为程序结束。所以使用’delimiter结束符号‘命令将mysql语句的结束标志修改为其他符号，最后再使用’opdelimiter;‘恢复以分号为结束标志。

例如：用存储过程实现删除一个特定学生的信息

delimiter $$
create procedure delete_student(in xh char(6))
begin
	delete from xs where 学号=xh;
end $$
delimiter ;

当调用这个存储过程时，MySQL会根据提供的参数xh的值，删除对应在xs表中的数据。

6.1.2 存储过程体

    在存储过程体中可以使用所有sql语句类型，包括所有的ddl、dml、dcl，当然，过程式语言也是允许的，其中也包括变量的定义和赋值

1. 局部变量

    在存储过程中可以声明局部变量，它们可以用来存储临时结果

    要声明局部变量必须使用declare语句，在声明局部变量的同时可以对其赋一个初始值，如果不指定默认为null。

    语法格式如下

declare 变量名....类型[default 默认值]

例如:声明一个整型变量和两个字符变量

declare num int(4);
declare str1, str2 varchar(6);

2. 使用set语句赋值

    要给局部变量赋值可以使用set语句

    语法格式为

set 变量名=expr [,变量名 = expr]....

例如：在存储过程中给局部变量赋值

set num=1 ,str1='hello';

3. select…into语句

    使用select…into语句可以把选定的列值直接存储到变量中去，因此返回的结果只能有一行，

    语法格式为

select 列名[,...] into 变量名[,....] table_expr