mysql 存储过程学习

MYSQL的日期和时间函数
我们先来看几个时间函数快速入门一下，如果想详细了解建议看mysql文档：
1、SELECT CURDATE(); -- 当前日期：2017-02-15
2、SELECT CURTIME(); -- 当前时间：09:40:36
3、SELECT CURRENT_TIMESTAMP(); -- 当前时间：2017-02-15 09:40:36
4、SEELCT DATE_ADD(CURDATE(), interval -1 day); -- 昨天
5、SELECT DATE_ADD(CURDATE(), INTERVAL -WEEKDAY(CURDATE()) DAY);-- 本周一
6、SELECT DATE_SUB(SUBDATE(CURDATE(),WEEKDAY(CURDATE())),INTERVAL 7 DAY);-- 上周一
7、SELECT DATE_FORMAT(CURDATE(), '%Y-%m-01'); -- 本月1号

8、SELECT DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 MONTH); -- 上月1号

9、SELECT CURDATE()+0; -- 改变时间返回形式：20170215
10、SELECT EXTRACT(YEAR_MONTH FROM CURDATE()); -- 从日期中提取其部分 201702
11、SELECT LAST_DAY(CURDATE()); -- 返回该月最后一天对应的值 2017-02-28

## 创建存储过程

DEMILITER $$ -- 重定义符
USE article_publish$$
DROP PROCEDURE IF EXISTS SELECT_a_user$$; -- 如果存在此名的存储过程，先删除
CREATE PROCEDURE SELECT_a_user; -- 创建名为store_procedure的存储过程
BEGIN -- 开始
-- 内容
-- 声明变量
DECLARE my_integer INT DEFAULT 0;
-- 变量赋值
SET my_interger = 1;

-- 全局变量 可以跨存储过程使用； DECALRE @g_variable INT DEFAULT 0;


-- 操作符 1、数学操作符 2、比较操作符 3、逻辑操作符 4、位操作符 

-- 条件执行 IF CASE
DECLARE count INT DEFAULT 0;

IF (price > 100) THEN count = 0;
ELSEIF (price > 30 AND price < 100) count = 1;
ELSE count = 2;
END IF;


-- 下面使用CASE
CASE enum_type
WHEN 'A' THEN count = 1;
WHEN 'B' THEN count = 2;
ELSE count = 3;
END CASE;


-- 在没有ELSE的情况下，如果enum_type没有’A’、’B’的情况，会抛出一个异常：ERROR 1339 (20000): Case not found for CASE statement 
-- CASE语句中的WHEN表达式也可以是条件 WHEN (price > 100)


/**
循环：
简单循环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, '已被选中');
END LOOP my_loop;

SELECT "没能循环到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, '已被选中');
END LOOP my_loop;

SELECT "10以内的偶数不循环";

-- REPEAT……UNTIL循环，REPEAT循环的循环体总是能确保至少运行一次。
DECLARE count int DEFAULT 0 ;
my_loop: REPEAT

SET count = count + 1;
IF (MOD(count, 2) != 0)
SELECT CONCAT(count, '已被选中');
END IF;
UNTIL count = 10;
END REPEAT my_loop;

SELECT "10以内的偶数不循环";

-- WHILE循环，WHILE循环只有在条件为真时才执行循环。
DECLARE count int DEFAULT 0 ;
my_loop: WHILE (count < 10) DO
SET count = count + 1;
IF (MOD(count, 2) != 0)
SELECT CONCAT(count, '已被选中');
END IF;
END WHILE my_loop;

SELECT "10以内的偶数不循环";

/**
MYSql函数：
字符串函数 
这些函数主要对字符串变量执行操作，比方说：你可以连接字符串，在字符串中查找字符，得到子串和其他常规操作。 
数学函数
这些函数主要对数字进行操作，比方说：你可以进行乘方（平方），三角函数（sin，cos），随机数函数和对数函数等。 
日期和时间函数
折现函数主要的操作对象是日期和时间，比方说：你可以得到当前时间，从一个日期上加上或减去一个时间间隔，找出两个日期间的差异，获取某个确定的时间点（比如：得到一天中某时间的小时数）。 
其它函数
这些函数包括了所有不容易被分入上面类别中函数。他们包括类型转换函数，流程控制函数（比如：CASE），信息反馈函数（比如服务器版本）和加密函数。
**/
/**
和数据库交互：
将一个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
**/


-- 查询多条记录
DECLARE done DEFAULT -1;
DECLARE nprice DEFAULT 0;
DECLARE cur1 CURSOR FOR

SELECT price FROM c_user;

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”
**/
-- 1、定义游标 2、打开游标 3、读取游标 4、关闭游标 5、释放游标
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 存储过程名；
END$$ -- 结束
DEMILITER ; -- 恢复;为分隔符




/**
存储函数：
函数的参数列表中模式只能为IN。OUT和INOUT参数不被允许。制定IN关键字是被允许也是缺省的；
函数必须返回一个值，它的类型被定义于函数的头部；
函数能被SQL语句所调用；
函数可能不返回任何结果集；
在程序的真正目的是比对值和需要返回值时或者你希望在SQL语句中创建用户自定义函数的时候更多的考虑使用存储函数，而不是存储过程；
**/

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


CREATE FUNCTION store_function() RETURNS INT DETERMINITISTIC
-- 创建名为store_procedure的存储函数 
-- MySQL相较于存储过程，对于存储函数有更严格的规则。函数必须声明不修改SQL（使用NO SQL或者READS SQL DATA子句）或者声明为DETERMINISTIC（如果服务器被允许开启二进制日志）。这个限制是为了防止当函数返回不确定值时，数据库同步复制的不一致性，我们的样例例程使用了“deterministic”，这样我们就能确保在提供了相同的参数的情况下返回相同的值 
BEGIN -- 开始
-- 内容
RETURN(返回值);
END$$ -- 结束
DEMILITER ; -- 恢复;为分隔符