mysql 存储过程学习

存储过程框架

DEMILITER $$ -- 重定义符

DROP PROCEDURE IF EXISTS store_procedure$$ -- 如果存在此名的存储过程，先删除

CREATE PROCEDURE store_procedure() -- 创建名为store_procedure的存储过程

BEGIN -- 开始

-- 内容

END$$ -- 结束

DEMILITER ; -- 恢复;为分隔符

在使用复合语句时，必须考虑和解决这样一个问题：复合语句块里的语句必须以分号（;）彼此隔开，但因为分号同时也是mysql程序默认使用的语句分隔符，所以在使用mysql程序定义存储程序时会发生冲突。解决这个问题的办法是使用DELIMITER命令把mysql程序的语句分隔符重定义为另一个字符或字符串。

注释：

两个连字符加一个空格的单行注释，如：-- 注释

类似C语言的多行注释，如：/*注释

*/

变量：

变量声明：

DECLARE 变量名；

示例：DECLARE my_integer INT;

变量声明同时初始化：

DECLARE 变量名 DEFAULT 变量初始化值；

示例：DECLARE my_integer INT DEFAULT 0;

变量赋值：

SET 变量名=值；

示例：SET my_integer=1;

常用的MySQL数据类型 

用户变量（全局变量）：

可以跨存储过程使用；

DECALRE @g_variable INT DEFAULT 0;

参数：

参数类型分为输入（IN，默认）、输出（OUT）、输入输出（INOUT）三种类型；

存储函数只能用输入（IN）模式，但可以有一个返回值，对于需要返回多个值的功能，只能采用存储过程；

示例：存储过程计算平方根

DEMILITER $$

DROP PROCEDURE sqrt_store_procedure

CREATE PROCEDURE sqrt_store_procedure(IN in_num INT, OUT out_num FLOAT)

BEGIN

SET out_num=SQRT(in_num);

END$$

DEMILITER ;

操作符：

数学操作符

比较操作符

逻辑操作符

AND操作符比较两个逻辑表达式，并且只在两个表达式都为真是才返回TRUE。

OR操作符比对两个逻辑表达式，并且只要其中的一个表达式为真即返回TRUE。

XOR操作符只有在两个值不完全为真时才返回TRUE。

位操作符

条件执行：

IF、CASE；

示例：IF

DECLARE count INT DEFAULT 0;

IF （price > 100) THEN

count = 0;

ELSEIF (prict >= 30 AND prict <= 100) THEN

count=1;

ELSE

count=2;

END IF;

示例：CASE

DECLARE count INT DEFAULT 0;

CASE enum_type

WHEN ‘A’ THEN

count=1;

WHEN ‘B’ THEN

count=2;

ELSE -- 未满足’A’、’B’的情况下，执行ELSE流程

count=3;

END CASE;

在没有ELSE的情况下，如果enum_type没有’A’、’B’的情况，会抛出一个异常：ERROR 1339 (20000): Case not found for CASE statement 

CASE语句中的WHEN表达式也可以是条件，如：

CASE price

WHEN (price > 200) THEN

count=1;

WHEN ((price > 100) AND (price <= 200)) THEN

count=2;

ELSE

count=3;

END CASE;

循环：

简单循环LOOP、END LOOP；

当条件为真时，继续执行的循环WHILE、END WHILE；

循环直至条件为真：REPEAT、UNTIL；

三种循环都可以用LEAVE子句来终止循环；

示例：LOOP

DECLARE count INT DEFAULT 0;

my_loop: LOOP

SET count=count+1;

IF (count=10)

LEAVE my_loop;

END IF;

SELECT CONCAT(count, “ is select value”);

END LOOP my_loop;

SELECT “I can count to 10”;

ITERATE语句用来重新从循环头部开始执行，而不执行任何在循环中遗留下来的语句。

DECLARE count INT DEFAULT 0;

my_loop: LOOP

SET count=count+1;

IF (count=10)

LEAVE my_loop;

ELSEIF (MOD(count,2)=0)

ITERATE my_loop;

END IF;

SELECT CONCAT(count, “ is select value”);

END LOOP my_loop;

SELECT “I can count to 10 odd num”;

REPEAT……UNTIL循环，REPEAT循环的循环体总是能确保至少运行一次。

DECLARE count INT DEFAULT 0;

my_loop: REPEAT

SET count=count+1;

IF (MOD(count,2)<>0)

SELECT CONCAT(count, “ is select value”);

END IF;

UNTIL count=10

END LOOP my_loop;

SELECT “I can count to 10 odd num”;

WHILE循环，WHILE循环只有在条件为真是才执行循环。

DECLARE count INT DEFAULT 0;

my_loop: WHILE （couunt<10) DO

SET count=count+1;

IF (MOD(count,2)<>0)

SELECT CONCAT(count, “ is select value”);

END IF;

END WHILE my_loop;

SELECT “I can count to 10 odd num”;

MYSql函数：

字符串函数 

这些函数主要对字符串变量执行操作，比方说：你可以连接字符串，在字符串中查找字符，得到子串和其他常规操作。

数学函数

这些函数主要对数字进行操作，比方说：你可以进行乘方（平方），三角函数（sin，cos），随机数函数和对数函数等。 

日期和时间函数 

折现函数主要的操作对象是日期和时间，比方说：你可以得到当前时间，从一个日期上加上或减去一个时间间隔，找出两个日期间的差异，获取某个确定的时间点（比如：得到一天中某时间的小时数）。

其它函数

这些函数包括了所有不容易被分入上面类别中函数。他们包括类型转换函数，流程控制函数（比如：CASE），信息反馈函数（比如服务器版本）和加密函数。

经常被使用的MySQL函数

和数据库交互：

将一个SQL语句所返回的单个记录放入本地变量中；

创建一个“游标”来迭代SQL语句所返回的结果集；

执行一个SQL语句，将执行后的结果集返回给它的调用程序；

内嵌一个不返回结果集的SQL语句，如INSERT，UPDATA，DELETE等；

将一个SQL语句所返回的单个记录放入本地变量中：SELECT INTO

示例：

DECLARE num INT;

SELECT NUM(price)

INTO num

FROM tabprice;

SELECT CONCAT(“Total Price is”, num);

游标

游标的声明必须在我们所有的变量声明之后。在的变量之前定义游标会产生一个1337错误。游标总是和SELECT语句配合使用。

DECLARE cur CURSOR FOR SELECT * FROM customers;

创建一个“游标”来迭代SQL语句所返回的结果集：CURSOR

查询多条记录数据；

示例：CURSOR

DECLARE done DEFAULT -1;

DECLARE nprice DEFAULT 0;

DECLARE cur1 CURSOR FOR

SELECT price FROM tabprice;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; -- 如果没有记录，错误处理no data to fetch条件，几乎所有的游标都要用到

SET done=0;

OPEN cur1;

my_loop: LOOP

FETCH cur1 INTO nprice; -- 循环从游标中取值

IF （done=1)

LEAVE my_loop;

END IF;

END LOOP my_loop;

CLOSE cur1;

DEALLOCATE cur1; -- 释放游标

不使用no data to fetch的另一种解决方案@@FETCH_STATUS

@@fetch_status是MSSQL的一个全局变量

其值有以下三种，分别表示三种不同含义：【返回类型integer】

0 FETCH 语句成功

-1 FETCH 语句失败或此行不在结果集中

-2 被提取的行不存在

@@fetch_status值的改变是通过fetch next from实现的

“FETCH NEXT FROM Cursor”

DECLARE Employee_Cursor CURSOR FOR

SELECT LastName, FirstName FROM Northwind.dbo.Employees

OPEN Employee_Cursor

FETCH NEXT FROM Employee_Cursor

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM Employee_Cursor

END

CLOSE Employee_Cursor

DEALLOCATE Employee_Cursor

No data to fetch条件捕获在嵌套游标循环中使用比较复杂，有两种方案

1.在内循环结束时重置条件为0

DECLARE done DEFAULT -1;

DECLARE nprice DEFAULT 0;

DECLARE cur1 CURSOR FOR

SELECT price FROM tabprice;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; -- 如果没有记录，错误处理no data to fetch条件，几乎所有的游标都要用到

SET done=0;

OPEN cur1;

my_loop: LOOP

FETCH cur1 INTO nprice; -- 循环从游标中取值

DECLARE cur2 CURSOR FOR SELECT * FROM cumtomers;

OPEN cur2;

my_loop2: LOOP

FETCH cur2 INTO custom;

IF (done=1)

LEAVE my_loop2;

END IF;

END LOOP my_loop2;

CLOSE cur2;

DEALLOCATE cur2; -- 释放游标

SET done=0; -- 重置事件值

IF （done=1)

LEAVE my_loop;

END IF;

END LOOP my_loop;

CLOSE cur1;

DEALLOCATE cur1; -- 释放游标

2.内循环封闭为一个块

DECLARE done DEFAULT -1;

DECLARE nprice DEFAULT 0;

DECLARE cur1 CURSOR FOR

SELECT price FROM tabprice;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; -- 如果没有记录，错误处理no data to fetch条件，几乎所有的游标都要用到

SET done=0;

OPEN cur1;

my_loop: LOOP

FETCH cur1 INTO nprice; -- 循环从游标中取值

cur2begin: BEGIN

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2=1;

DECLARE cur2 CURSOR FOR SELECT * FROM cumtomers;

OPEN cur2;

my_loop2: LOOP

FETCH cur2 INTO custom;

IF (done2=1)

LEAVE my_loop2;

END IF;

END LOOP my_loop2;

CLOSE cur2;

DEALLOCATE cur2; -- 释放游标

END cur2begin;

IF （done=1)

LEAVE my_loop;

END IF;

END LOOP my_loop;

CLOSE cur1;

DEALLOCATE cur1; -- 释放游标

在存储过程中调用另一个存储过程：

CALL 存储过程名；

存储函数：

函数的参数列表中模式只能为IN。OUT和INOUT参数不被允许。制定IN关键字是被允许也是缺省的；

函数必须返回一个值，它的类型被定义于函数的头部；

函数能被SQL语句所调用；

函数可能不返回任何结果集；

在程序的真正目的是比对值和需要返回值时或者你希望在SQL语句中创建用户自定义函数的时候更多的考虑使用存储函数，而不是存储过程；

框架：

DEMILITER $$ -- 重定义符

DROP FUNCTION IF EXISTS store_function$$ -- 如果存在此名的存储函数，先删除

CREATE FUNCTION store_function() RETURNS INT DETERMINITISTIC

 -- 创建名为store_procedure的存储函数

-- 

BEGIN -- 开始

-- 内容

RETURN(返回值);

END$$ -- 结束

DEMILITER ; -- 恢复;为分隔符