使用存储过程的优点有:
(1)存储过程在服务器端运行,执行速度快。
(2)存储过程执行一次后,其执行规划就驻留在高速缓冲存储器,在以后的操作中,只需从高速缓冲存储器中调用已编译好的二进制代码执行,提高了系统性能。
(3)确保数据库的安全。使用存储过程可以完成所有数据库操作,并可通过编程方式控制上述操作对数据库信息访问的权限。
创建存储过程可以使用CREATE PROCEDURE语句。要在MySQL 5.1中创建存储过程,必须具有CREATE ROUTINE权限。要想查看数据库中有哪些存储过程,可以使用SHOW PROCEDURE STATUS命令。要查看某个存储过程的具体信息,可使用SHOW CREATE PROCEDURE sp_name命令,其中sp_name是存储过程的名称。
CREATE PROCEDURE的语法格式:
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
其中,proc_parameter的参数如下:
[ IN | OUT | INOUT ] param_name type
characteristic特征如下:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
说明:
● sp_name:存储过程的名称,默认在当前数据库中创建。需要在特定数据库中创建存储过程时,则要在名称前面加上数据库的名称,格式为:db_name.sp_name。值得注意的是,这个名称应当尽量避免取与MySQL的内置函数相同的名称,否则会发生错误。
● proc_parameter:存储过程的参数,param_name为参数名,type为参数的类型,当有多个参数的时候中间用逗号隔开。存储过程可以有0个、1个或多个参数。MySQL存储过程支持三种类型的参数:输入参数、输出参数和输入/输出参数,关键字分别是IN、OUT和INOUT。输入参数使数据可以传递给一个存储过程。当需要返回一个答案或结果的时候,存储过程使用输出参数。输入/输出参数既可以充当输入参数也可以充当输出参数。存储过程也可以不加参数,但是名称后面的括号是不可省略的。
注意:参数的名字不要等于列的名字,否则虽然不会返回出错消息,但是存储过程中的SQL语句会将参数名看做列名,从而引发不可预知的结果。
characteristic:存储过程的某些特征设定,下面一一介绍。
LANGUAGE SQL:表明编写这个存储过程的语言为SQL语言,目前来讲,MySQL存储过程还不能用外部编程语言来编写,也就是说,这个选项可以不指定。将来将会对其扩展,最有可能第一个被支持的语言是PHP。
DETERMINISTIC:设置为DETERMINISTIC表示存储过程对同样的输入参数产生相同的结果,设置为NOT DETERMINISTIC则表示会产生不确定的结果。默认为NOT DETERMINISTIC。
CONTAINS SQL:表示存储过程不包含读或写数据的语句。NO SQL表示存储过程不包含SQL语句。READS SQL DATA表示存储过程包含读数据的语句,但不包含写数据的语句。MODIFIES SQL DATA表示存储过程包含写数据的语句。如果这些特征没有明确给定,默认的是CONTAINS SQL。
SQL SECURITY:SQL SECURITY特征可以用来指定存储过程使用创建该存储过程的用户(DEFINER)的许可来执行,还是使用调用者(INVOKER)的许可来执行。默认值是DEFINER。
COMMENT 'string':对存储过程的描述,string为描述内容。这个信息可以用SHOW CREATE PROCEDURE语句来显示。
● routine_body:这是存储过程的主体部分,也叫做存储过程体。里面包含了在过程调用的时候必须执行的语句,这个部分总是以BEGIN开始,以END结束。当然,当存储过程体中只有一个SQL语句时可以省略BEGIN-END标志。
在开始创建存储过程之前,先介绍一个很实用的命令,即DELIMITER命令。在MySQL中,服务器处理语句的时候是以分号为结束标志的。但是在创建存储过程的时候,存储过程体中可能包含多个SQL语句,每个SQL语句都是以分号为结尾的,这时服务器处理程序的时候遇到第一个分号就会认为程序结束,这肯定是不行的。所以这里使用DELIMITER命令将MySQL语句的结束标志修改为其他符号。
DELIMITER语法格式为:
DELIMITER $$
说明:$$是用户定义的结束符,通常这个符号可以是一些特殊的符号,如两个“#”,两个“¥”等。当使用DELIMITER命令时,应该避免使用反斜杠(“\”)字符,因为那是MySQL的转义字符。
1. 局部变量
在存储过程中可以声明局部变量,它们可以用来存储临时结果。要声明局部变量必须使用DECLARE语句。在声明局部变量的同时也可以对其赋一个初始值。
DECLARE语法格式如下:
DECLARE var_name[,...] type [DEFAULT value]
说明:var_name为变量名;type为变量类型;DEFAULT子句给变量指定一个默认值,如果不指定默认为NULL的话。
【例7.3】 声明一个整型变量和两个字符变量。
DECLARE num INT(4);
DECLARE str1, str2 VARCHAR(6);
说明:局部变量只能在BEGIN…END语句块中声明。
局部变量必须在存储过程的开头就声明,声明完后,可以在声明它的BEGIN…END语句块中使用该变量,其他语句块中不可以使用它。
前面已经学习过用户变量,在存储过程中也可以声明用户变量,不过千万不要将这两个混淆。局部变量和用户变量的区别在于:局部变量前面没有使用@符号,局部变量在其所在的BEGIN…END语句块处理完后就消失了,而用户变量存在于整个会话当中。
2. 使用SET语句赋值
要给局部变量赋值可以使用SET语句,SET语句也是SQL本身的一部分。
语法格式为:
SET var_name = expr [, var_name = expr] ...
【例7.4】 在存储过程中给局部变量赋值。
SET num=1, str1= 'hello';
说明:与声明用户变量时不同,这里的变量名前面没有@符号。声明用户变量的方法已经介绍过,这里不再举例。注意,例中的这条语句无法单独执行,只能在存储过程和存储函数中使用。
3. SELECT...INTO语句
使用这个SELECT…INTO语法可以把选定的列值直接存储到变量中。因此,返回的结果只能有一行。
语法格式为:
SELECT col_name[,...] INTO var_name[,...] table_expr
说明:col_name是列名,var_name是要赋值的变量名。table_expr是SELECT语句中的FROM子句及后面的部分,这里不再叙述。
【例7.5】 在存储过程体中将XS表中的学号为081101的学生姓名和专业名的值分别赋给变量name和project。
SELECT 姓名,专业名 INTO name, project
FROM XS;
WHERE 学号= '081101';
4. 流程控制语句
在MySQL中,常见的过程式SQL语句可以用在一个存储过程体中。例如:IF语句、CASE语句、LOOP语句、WHILE语句、ITERATE语句和LEAVE语句。
(1)IF语句
IF-THEN-ELSE语句可根据不同的条件执行不同的操作。
语法格式为:
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list ] ...
[ELSE statement_list]
END IF
说明:search_condition是判断的条件,statement_list中包含一个或多个SQL语句。当search_condition的条件为真时,就执行相应的SQL语句。
IF语句不同于系统的内置函数IF()函数,IF()函数只能判断两种情况,所以请不要混淆。
【例7.6】 创建XSCJ数据库的存储过程,判断两个输入的参数哪一个更大。
DELIMITER $$
CREATE PROCEDURE XSCJ.COMPAR
(IN K1 INTEGER, IN K2 INTEGER, OUT K3 CHAR(6) )
BEGIN
IF K1>K2 THEN
SET K3= '大于';
ELSEIF K1=K2 THEN
SET K3= '等于';
ELSE
SET K3= '小于';
END IF;
END$$
DELIMITER ;
说明:存储过程中K1和K2是输入参数,K3是输出参数。
(2)CASE语句
CASE语句在4.2.1节介绍选择列的时候已经涉及。这里介绍CASE语句在存储过程中的用法,与之前略有不同。
语法格式为:
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
或者:
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE
说明:一个CASE语句经常可以充当一个IF-THEN-ELSE语句。
第一种格式中case_value是要被判断的值或表达式,接下来是一系列的WHEN-THEN块,每一块的when_value参数指定要与case_value比较的值,如果为真,就执行statement_list中的SQL语句。如果前面的每一个块都不匹配就会执行ELSE块指定的语句。CASE语句最后以END CASE结束。
第二种格式中CASE关键字后面没有参数,在WHEN-THEN块中,search_condition指定了一个比较表达式,表达式为真时执行THEN后面的语句。与第一种格式相比,这种格式能够实现更为复杂的条件判断,使用起来更方便。
【例7.7】 创建一个存储过程,针对参数的不同,返回不同的结果。
DELIMITER $$
CREATE PROCEDURE XSCJ.RESULT
(IN str VARCHAR(4), OUT sex VARCHAR(4) )
BEGIN
CASE str
WHEN 'M' THEN SET sex='男';
WHEN 'F' THEN SET sex='女';
ELSE SET sex='无';
END CASE;
END$$
DELIMITER ;
【例7.8】 用第二种格式的CASE语句创建以上存储过程。程序片段如下:
CASE
WHEN str='M' THEN SET sex='男';
WHEN str='F' THEN SET sex='女';
ELSE SET sex='无';
END CASE;
(3)循环语句
MySQL支持3条用来创建循环的语句:WHILE、REPEAT和LOOP语句。在存储过程中可以定义0个、1个或多个循环语句。
● WHILE语句语法格式为:
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
说明:语句首先判断search_condition是否为真,不为真则执行statement_list中的语句,然后再次进行判断,为真则继续循环,不为真则结束循环。begin_label和end_label是WHILE语句的标注。除非begin_label存在,否则end_label不能被给出,并且如果两者都出现,它们的名字必须是相同的。
【例7.9】 创建一个带WHILE循环的存储过程。
DELIMITER $$
CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 5;
WHILE v1 > 0 DO
SET v1 = v11;
END WHILE;
END$$
DELIMITER ;
【例7.10】 用REPEAT语句创建一个如例7.9的存储过程。程序片段如下:
REPEAT
v1=v11;
UNTIL v1<1;
END REPEAT;
说明:REPEAT语句和WHILE语句的区别在于:REPEAT语句先执行语句,后进行判断;而WHILE语句是先判断,条件为真时才执行语句。
分享到:
相关推荐
使用野火STM32-F103 指南者 开发板 EERPOM存储小数
存储管理的主要功能之一是合理地分配空间。请求页式管理是一种常用的虚拟存储管理技术。本设计的目的是通过请求页式存储管理中页面置换算法模拟设计,了解虚拟存储技术的特点,掌握请求页式存储管理的页面置换算法。...
vmware添加iscsi共享存储设备, vmware添加iscsi共享储存
191-存储AT24C02(51单片机C语言实例Proteus仿真和代码)191-存储AT24C02(51单片机C语言实例Proteus仿真和代码)191-存储AT24C02(51单片机C语言实例Proteus仿真和代码)191-存储AT24C02(51单片机C语言实例Proteus仿真和...
Cloudreve云盘系统源码-支持本地储存和对象储存,界面美观 云盘系统安装教程 测试环境:PHP7.1 + MYSQL5.6 + Apache 上传源码到根目录 安装完毕 记住系统默认的账号密码 温馨提示:如果默认的伪静态不行就替换...
大基金将成立中国储存芯片联盟长江存储/晋华/睿力或加入.pdf
仿真连续分配存储管理系统,至少包括以下功能:并发分配与回收、查询、拼接等功能。 在动态分区管理系统中,主要的操作是分配内存和回收内存。 分配内存:系统利用某种分配算法,从空闲分区链(表)中找到所需大小...
S7-300CPU存储卡介绍与储存卡的使用
分布式存储系统是一个有效的解决有状态工作负载高可用问题的方案。Ceph 就是一个分布式存储系统,近年来其影响主键扩大。Rook 是一个编排器,能够支持包括 Ceph 在内的多种存储方案。Rook 简化了 Ceph 在 Kubernetes...
阅读实验教材《SQL Server 2012数据库管理与开发》第137页到155页的内容,掌握存储过程的概念、了解存储过程的类别(系统存储过程,用户自定义存储过程,扩展存储过程);掌握存储过程的建立;掌握存储过程的两种...
存储过程的理念、存储过程的基本语法、常用发放的实现及简易的存储分页
windows server 2012配置存储池和存储空间
因此保证每次找到的总是空闲分区中最小适应的,但这样会在储存器中留下许多难以利用的小的空闲区。最坏适应分配算法是要扫描整个空闲分区表或链表,总是挑选最大的一个空闲分区割给作业使用。进入系统时我们需要内存...
华中科技大学-计算机组成原理-educoder Logisim-储存系统设计(HUST) 答案代码 1.汉字字库存储芯片扩展实验 2.MIPS寄存器文件设计 3.MIPS RAM设计 4.全相联cache设计 5.直接相联cache设计 6.4路组相连cache设计 ...
线性表的顺序存储,此程序主要实现线性表的顺序存储,有C++语言实现,还是比较轻易看得懂的!
储存卡快速拷贝工具是一款能够将大文件快速的从本地磁盘复制到其他存储卡
软件名:USB安全存储专家(USSE)2005(Build 308)破解版 简介: USB安全存储专家(以下简称USSE)是一套计算机USB端口屏蔽、USB端口控制、实时监视、实时监控于一体的计算机网络安全控制系统。它通过USB存储协议...
1. 存储过程只在创造时进行编译即可,以后每次执行存储过程都不需再重新编译,而我们通常使用的SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。 2. 经常会遇到复杂的业务逻辑和对数据库的...
该技术文档包括基本的命令行划分配置流程,简单明了,可以通过实践了解存储阵列与服务器连接的组织结构,进一步扩展到其他存储产品的逻辑关联。
在MySQL 入门教程中,我们能够看到很多关于如何创建储存过程 和如何利用 IN 和 OUT 参数调用存储过程的示例。这些示例都很简单,能够很好的帮助你理解 MySQL 中创建带参数存储过程的语法。这些示例已在 MySQL 5.5 中...