之路"/>
MYSQL数据库开发之路
目录
引言
创建存储过程和存储函数
创建存储过程
创建存储函数
变量的应用
局部变量
全局变量
为变量赋值
光标的运用
声明光标（declare cursor）
打开光标（open cursor）
使用光标（fetch cursor）
关闭光标（close cursor）
流程控制语句
if语句
case语句
while循环语句
引言
存储过程和函数：在数据库中定义一些SQL语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。
存储过程和函数的作用：可以避免开发人员重复的编写相同的SQL语句；由于存储过程和函数是在MySQL服务器中存储和执行的，因此可以减少客户端和服务端的数据传输。
本文将介绍存储过程和函数的含义、作用，以及创建、使用、查看、修改和删除存储过程和函数的方法。
创建存储过程和存储函数
通常在数据库系统中，为了保证数据的完整性、一致性，同时为了提高应用性能，大多数据库常采用存储过程和存储函数技术。
存储过程和存储函数是一组SQL语句的组合，被当作整体存入MySQL数据库服务器中。
用户定义的存储函数不能用于修改全局库状态，但该函数可以从查询中被唤醒调用，也可以像存储过程一样通过语句执行。
创建存储过程
在MySQL中，创建存储过程，语法格式如下：
create procedure sp_name ([ in | out | inout ] param_name type,......)
[characteristic ...... ]
begin
......
end;
参数说明：
sp_name:存储过程名称
in：输出参数
out：输出参数
inout：既可以输入也可以输出
param_name:存储过程参数名称
type：指定存储过程的参数类型，该类型可以是MySQL数据库的任意数据类型
characteristic：指定存储过程特性
begin：开始
end：结束
※注意
由于存储过程内部语句要以分号结束，所以在定义存储过程前，应将语句结束标志“；”更改为其他字符，并且应降低该字符在存储过程中出现的概率，更改结束标志可以用关键字“delimiter”定义。
mysql >delimiter//#创建一个名称未count_of_student的存储过程。首先，创建一个名为students的MySQL数据库，然后创建一个名未studentinfo的数据表
DELIMITER $$ #将语句结束标志设置为 ‘$$’USE `students`$$ DROP PROCEDURE IF EXISTS `count_of_student`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `count_of_student`(OUT count_num INT)READS SQL DATA
BEGINSELECT COUNT(*) INTO count_num FROM studentinfo;END$$DELIMITER ;#将语句结束标志设置为 ‘;’创建存储函数
创建存储函数与创建存储过程大体相同。其创建存储函数的基本形式如下：
create function sp_name (func_parameter[,......])
returns type
[characteristic......] routine_body
| 参数 | 说明 | 参数 | 说明 |
| sp_name | 存储函数的名称 | characteristic | 指定存储过程的特性 |
| fun_parameter | 存储函数的参数列表 | routine_body | SQL代码的内容 |
| returns type | 指定返回值的类型 |
func_parameter由多个参数组成，其中每个参数均由参数名称和参数类型组成，结构如下：
parameter_name type
paramter_name:是存储函数的函数名称，type参数用于指定存储函数的参数类型
DELIMITER $$USE `students`$$DROP FUNCTION IF EXISTS `name_of_student`$$CREATE DEFINER=`root`@`localhost` FUNCTION `name_of_student`(std_id INT) RETURNS VARCHAR(50) CHARSET utf8
BEGINRETURN (SELECT NAME FROM studentinfo WHERE sid=std_id);END$$DELIMITER ;变量的应用
在MySQL存储过程中的参数主要有局部参数和会话参数两种，这两种参数又可以被称为局部变量和会话变量。局部变量只能定义该局部变量的begin......end范围内有效，会话变量在整个存储过程范围内均有效。
局部变量
语法格式如下：
declare var_name[,......] type[default value]
局部变量以关键字declare声明，后跟变量名和变量类型：
declare a int
当然在声明局部变量时可以用关键字default为变量指定默认值：
declare a int default 10
#分别在内层和外层begin……end块中都定义同名的变量x，按照语句从上到下执行的顺序，
如果变量x在整个程序中都有效，则最终结果应该都为inner，
但真正的输出结果却不同，这说明在内部begin……end块中定义的变量只在该块内有效。
DELIMITER $$USE `students`$$DROP PROCEDURE IF EXISTS `p1`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
BEGINDECLARE X CHAR(10) DEFAULT 'outer';BEGINDECLARE X CHAR(10) DEFAULT 'inner';SELECT X;END;SELECT X;END$$DELIMITER ;call p1();全局变量
会话变量不必声明即可使用，会话变量在整个过程中有效，会话变量名以字符“@”作为起始字符。
#在该示例中，分别在内部和外部begin.....end块中都定义了同名的会话变量@t,
并且最终输出结果相同，说明会话变量的作用范围为整个程序DELIMITER $$USE `students`$$DROP PROCEDURE IF EXISTS `p2`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `p2`()
BEGINSET @t=1;BEGIN SET @t=2;SELECT @t;END;SELECT @t;END$$DELIMITER ;call p2();为变量赋值
使用set关键字为变量赋值。set语句的语法格式如下：
set var_name =expr[,var_name=expr].........
set关键字：用来为变量赋值
var_name：变量的名称
expr：赋值表达式
多个变量赋值时用“，”隔开
例如：
set mr_soft=10;
另外，MySQL中还可以应用另一种方式为变量赋值。其语法结构如下:
select col_name[,......] into var_name [,......] from table_name where condition;
col_name:表示查询的字段名称
var_name：变量的名称
table_name：指定数据表名称
condition：指定查询条件
例如：
#从studentinfo表中查询 name为'LeonSK'的记录 ，将该记录下的tel字段内容
赋值给变量customer_telselect tel into customer_tel from studentinfo where name='leonSK';※注意
上述赋值语句必须存在于创建的存储过程中，且需将赋值语句放在begin......end之间。若脱离此范围，该变量将不能被使用或被赋值。
光标的运用
通过数据库查询，其结果可能是多条记录。在存储过程和函数中使用光标可以实现逐条读取结果集中的记录。光标的使用包括：
声明光标（declare cursor）
打开光标（open cursor）
使用光标（fetch cursor）
关闭光标（close cursor）
声明光标（declare cursor）
在MySQL中，声明光标仍然使用declare关键字，语法如下：
declare cursor_name cursor for select_statement;
cursor_name：光标的名称，其使用与表名同样的规则
select_statement：是一个select语句，返回一行或多行数据。
例如：
#定义光标info_of_studentdeclare info_of_student cursor for select
sid,name,age,sex,age
from studentinfo
where sid=1;※注意
这个语句也可以在存储过程中定义多个光标，但是必须保证每个光标名称的唯一性，即每个光标需要有自己唯一的名称。
这里的select子句不能包含into 子句，并且光标只能在存储过程或存储函数中使用，不能单独执行。
打开光标（open cursor）
在声明光标之后，要从光标中提取数据，必须要先打开光标，语法格式如下
open cursor_name；
在程序中一个光标可以打开多次，因为由于用户打开光标后，其他用户或程序正在更新数据表，导致用户在每次打开光标后，显示的结果都不相同。
#打开贯标info_of_studentopen info_of_student使用光标（fetch cursor）
在光标顺利打开后，可以使用fetch....into 语句来读取数据。语法格式如下：
fetch cursor_name into var_name [,var_name]..........
cursor_name:代表已经打开光标的名称
var_name：表示将光标中select语句查询出来的参数存入该参数中。var_name是存放数据的变量名，必须在声明光标签定义好。fetch...into 语句与select....into语句具有相同的意义
#将光标info_of_student中select语句查询出来的信息存入temp_name和temp_tel中，
其中temp_name和temp_tel已经定义好
fetch info_of_student into temp_name,temp_tel;关闭光标（close cursor）
光标使用完毕后，要及时关闭，在MySQL中采用close关键字关闭光标，语法格式如下：
close cursor_name;
cursor_name：表示光标名称，例如：
#关闭info_of_student
close info_of_student;注意：
对于已经关闭的光标，在关闭后不能使用fetch来使用光标，光标在使用完后一定要关闭。
流程控制语句
在MySQL中，常见的过程式SQL语句可以在一个存储过程体中，其中包括if语句、case语句、loop语句、while语句、iterate语句和leave语句，他们可以进行流程控制。
if语句
if语句用来进行条件判断，根据不同的条件执行不同的操作。在执行时先判断if后的条件是否为真，若为真则执行then后面的语句，若为假则继续判断，语法格式如下：
if condition then
...
[elseif condition then]
.....
[else]
......
endif
USE `students`$$DROP PROCEDURE IF EXISTS `example_if`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `example_if`(IN X INT)
BEGINIF X=1 THENSELECT 1;ELSEIF X=2 THENSELECT 2;ELSESELECT 3;END IF;END$$DELIMITER ;CALL example_if(1);case语句
case语句为多分支语句结构，该语句首先从when后面的value中查找与case后面value相等的值，如果查找到则执行该分支的内容，若没找到，则执行else后面的内容，语法格式如下：
case value
when value then ......
[when value then......]
[else......]
end case;
其中，value：表示条件判断的变量，when.... then中value表示变量的取值
DELIMITER $$USE `students`$$DROP PROCEDURE IF EXISTS `example_case`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `example_case`(IN X INT)
BEGINCASE XWHEN 1 THEN SELECT 1;WHEN 2 THEN SELECT 2;ELSE SELECT 'else';END CASE;END$$DELIMITER ;CALL example_case(3);while循环语句
while循环语句执行时首先判断condition条件是否为真，如果为真则执行循环体，否则退出循环，语法格式如下：
while condition do
.....
end while;
#求1-100的和
DELIMITER $$USE `students`$$DROP PROCEDURE IF EXISTS `example_while`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `example_while`(OUT SUM INT)
BEGINDECLARE i INT DEFAULT 1;DECLARE s INT DEFAULT 0;WHILE i<=100 DOSET s=s+i;SET i=i+1;END WHILE;SET SUM =s;END$$DELIMITER ;CALL example_while(@s);
SELECT @s;
更多推荐
MYSQL数据库开发之路
发布评论