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数据类型 数据类型 解释 相应值的示例 INT, INTEGER 32位整数。取值范围为-21亿到+21亿，如果是非符号数，值可以达到42亿，但这样做就不能包括负数 123，345 -2,000,000,000 BIGINT 64位整数。取值范围为-9万亿到+9万亿或者非负的0到18万亿 9,000,000,000,000,000,000 -9,000,000,000,000,000,000 FLOAT 32位浮点数。取值范围为1.7e38 to 1.7e38或者非负的0到3.4e38 0.00000000000002 17897.890790 -345.8908770 1.7e21 DOUBLE 64位浮点数。取值范围接近无限（1.7e308） 1.765e203 -1.765e100 DECIMAL（precision,scale） NUMERIC（precision,scale） 定点数。存储情况取决于precision，能保存可能出现的数字范围。 NUMERIC通常用来保存重要的十进制数，例如现金 78979.00 -87.50 9.95 DATE 日期类型，没有详述时间 ‘1999-12-31‘ DATETIME 日期和时间，时间精确到秒 ‘1999-12-31 23:59:59‘ CHAR(length) 定长字符串。值会被空白填充至制定长度，最大长度为255字节 ‘hello world ‘ VARCHAR(length) 最大长度为64K的可变字符串 ‘Hello world‘ BLOB, TEXT 最大64K长度，BLOB用来保存2进制数据，TEXT用来保存文本数据 任何能想象的内容 LONGBLOB, LONGTEXT BLOB和TEXT的加长版本，存储能力达4GB 任何能想象的内容，但比BLOB和TEXT能存放更大的长度 用户变量（全局变量）： 可以跨存储过程使用； 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 ; 操作符： 数学操作符 操作符 描述 用例 + 加 SET var1=2+2; à 4 - 减 SET var2=3-2; à 1 * 乘 SET var3=3*2; à 6 / 除 SET var4=10/3; à 3.3333 DIV 整除 SET var5=10 DIV à3; 3 % 模 SET var6=10%3; à 1 比较操作符 操作符 描述 示例 示例结果 > 是否大于 1>2 False < 是否小于 2<1 False <= 是否小于等于 2<=2 True >= 是否大于等于 3>=2 True BETWEEN 是否位于两个值之间 5 BETWEEN 1 AND 10 True NOT BETWEEN 是否不位于两个值之间 5 NOT BETWEEN 1 AND 10 False IN 值位于列表中 5 IN (1,2,3,4) False NOT IN 值不位于列表中 5 NOT IN (1,2,3,4) True = 等于 2=3 False <>, != 不等于 2<>3 False <=> Null安全等于（如果两个值均为Null返回TRUE） NULL<=>NULL True LIKE 匹配简单模式 "Guy Harrison" LIKE "Guy%" True REGEXP 匹配扩展正则表达式 "Guy Harrison" REGEXP "[Gg]reg" False IS NULL 值为空 0 IS NULL False IS NOT NULL 值不为空 0 IS NOT NULL True 逻辑操作符 AND操作符比较两个逻辑表达式，并且只在两个表达式都为真是才返回TRUE。 AND TRUE FALSE NULL TRUE TRUE FALSE NULL FALSE FALSE FALSE NULL NULL NULL NULL NULL OR操作符比对两个逻辑表达式，并且只要其中的一个表达式为真即返回TRUE。 OR TRUE FALSE NULL TRUE TRUE TRUE TRUE FALSE TRUE FALSE NULL NULL TRUE NULL NULL XOR操作符只有在两个值不完全为真时才返回TRUE。 XOR TRUE FALSE NULL TRUE FALSE TRUE NULL FALSE TRUE FALSE NULL NULL NULL NULL NULL 位操作符 操作符 用途 | OR & AND << Shift bits to left >> Shift bits to right ~ NOT or invert bits 条件执行： 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函数 函数 描述 ABS(number) 返回提供数字的绝对值。比方说， ABS(-2.3)=2.3. CEILING(number) 返回下一个最大整数，比方说， CEILING(2.3)=3. CONCAT(string1[,string2,string3,...]) 返回所有提供字符串的连接形式的值 CURDATE 返回当前时间（不带时间） DATE_ADD(date,INTERVAL amount_type) 给提供的时间值加上一个时间间隔并返回一个新时间。正确的形式有SECOND, MINUTE, HOUR, DAY, MONTH和YEAR DATE_SUB(date,INTERVAL interval_type) 从提供的时间值上减去一个时间间隔并返回一个新的时间。正确的形式有SECOND, MINUTE, HOUR, DAY, MONTH和YEAR FORMAT(number,decimals) 返回一个指定精确度的数值，并给与以1000为单位的分割（通常使用“，”） GREATEST(num1,num2[,num3, ... ]) 返回所有提供参数中的最大数 IF(test, value1,value2) 测试一个逻辑条件，如果为真则返回value1，如果为假返回value2 IFNULL(value,value2) 返回第一个值，除非第一个值为空；这样的话返回第二个值 INSERT(string,position,length,new) 把一个字符串插入到另一个字符串中 INSTR(string,substring) 返回一个字符串中子串的位置 ISNULL(expression) 如果参数为空则返回1，否则返回0 LEAST(num1,num2[,num3, ... ]) 返回参数列表中的最小值 LEFT(string,length) 返回字符串最左边的部分 LENGTH(string) 返回字符串中的字节数。CHAR_LENGTH可以被用来返回字符数（这会在你使用多字节字符集是产生差异） LOCATE(substring,string[,number]) 返回字符串中子串的位置，可选的第三个参数为在父字符串中开始搜索的位置 LOWER(string) 返回给定字符串的小写形式 LPAD(string,length,padding) 返回字符串 str, 其左边由字符串padding 填补到length 字符长度，第三个参数为填充字符 LTRIM(string) 删除所有字符串中的前缀空格 MOD(num1,num2) 返回第一个数除于第二个数后的模（余数部分） NOW 返回当前日期和时间 POWER(num1,num2) 返回num1的num2次方 RAND([seed]) 返回一个随机数。seed可被用于随机数生成器的种子数 REPEAT(string,number) 返回一个重复number次string的字符串 REPLACE(string,old,new) 用new替换所有出现old的地方 ROUND(number[,decimal]) 舍去给定数值的指定精度的位数 RPAD(string,length,padding) 返回字符串 str, 其右边由字符串padding 填补到length 字符长度，第三个参数为填充字符 RTRIM(string) 删除字符串尾部的空格 SIGN(number) 如果number小于0则返回-1，如果大于0则返回1，如果为0则返回0 SQRT(number) 返回number的平方根 STRCMP(string1,string2) 如果两个值相同则返回0，若根据当前分类次序，第一个参数小于第二个，则返回 -1，其它情况返回 1 。 SUBSTRING(string,position,length) 从字符串指定位置开始返回length个字符 UPPER(string) 将指定字符串转换为大写 VERSION 返回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的存储函数 -- MySQL相较于存储过程，对于存储函数有更严格的规则。函数必须声明不修改SQL（使用NO SQL或者 READS SQL DATA子句）或者声明为DETERMINISTIC（如果服务器被允许开启二进制日志）。这个限制是为了防止当函数返回不确定值 时，数据库同步复制的不一致性，我们的样例例程使用了“deterministic”，这样我们就能确保在提供了相同的参数的情况下返回相同的值 BEGIN -- 开始 -- 内容 RETURN(返回值); END$$ -- 结束 DEMILITER ; -- 恢复;为分隔符