变量、流程控制、游标
0. 游标
#0.1 定义 #虽然可以通过筛选条件WHERE 和 HAVING, 或者是限定返回记录的关键字LIMIT 返回一条记录，但是， #确无法再结果集中像指针一样，向前定位一条记录，向后定位一条记录，或者是：随意定位到某一条 #记录，并对记录的数据进行处理。 #游标提供了一种灵活的操作方式，让我们能够对结果集中的每条记录进行定位，并对指向的记录中的数据进行操作 #的数据结构。 #游标让SQL这种面向集合的语音有了面向过程开发的能力。 #在SQL中，游标是一种临时的数据库对象，可以指向存储在数据库表两种的数据行指针。这里游标 #充当了指针的作用，我们可以通过操作游标来对数据进行操作。
#优缺点：
#为逐条读取结果集中的数据，提供了完美的解决方案。
#游标可以在存储过程中使用，效率高，程序也更加简洁。
#性能收到影响，在使用游标的过程中，会对数据行进行“加锁”，这样在业务并发量大的时候，
#不仅会影响业务之间的效率，还会消耗系统资源，造成内存不足。这是因为游标是在内存中进行的处理。
#养成用完之后关闭游标的习惯，进而提高系统的运行效率。
#0.2 4步骤 #声明游标(注意：写在其他变量声明的下面) #MySql SqlServer DB2 MariaDB DECLARE cursor_desc CURSOR FOR SELECT id,salary FROM employee; #Oracle PostgreSQL DECLARE cursor_desc CURSOR IS id,salary FROM employee; #打开游标(结果集送到游标的工作区，为后面游标的“逐条读取”结果集中的记录做准备) OPEN cursor_desc; #使用游标(var_id var_salary 声明的变量的名称) FETCH cursor_desc INTO var_id, var_salary; #关闭游标 CLOSE 游标名称;
#0.3 举例说明 DELIMITER // CREATE PROCEDURE pro_test_cursor(IN max_salary DOUBLE, OUT total_count INT) BEGIN DECLARE sum_salary DOUBLE DEFAULT 0.0; DECLARE emp_salary DOUBLE; DECLARE emp_count INT DEFAULT 0; #① 声明游标 DECLARE emp_cursor CURSOR FOR SELECT salary FROM employee ORDER BY salary DESC; #② 打开游标 OPEN emp_cursor; REPEAT #③ 使用游标 FETCH emp_cursor INTO emp_salary; SET sum_salary = sum_salary + emp_salary; SET emp_count = emp_count + 1; UNTIL sum_salary >= max_salary END REPEAT; SET total_count = emp_count; #④ 关闭游标 CLOSE emp_cursor; END // DELIMITER ; #drop PROCEDURE pro_test_cursor; CALL pro_test_cursor(30000, @total_count); SELECT @total_count;
1. 变量
#介绍：
#在MySQL数据库的存储过程和函数中，
#可以使用变量来存储查询或计算的中间结果数据，
#或者输出最终的结果数据。
#变量分为：系统变量和用户自定义变量
#1.1 系统变量 #系统变量由系统定义，不是用户定义，属于 服务器 层面。启动MySQL服务，生成MySQL服务实例期间，MySQL将为 #MySOL服务器内存中的系统变量赋值，这些系统变量定义了当前MySOL服务实例的属性、特征。这些系统变量的 #值要么是 编译MySQL时参数 的默认值，要么是 配置文件 (例如myini等)中的参数值。大家可以过网址 #https://dev.mysql.com/doc/refman/8.0/en/server-system-variables,html 查看MvSOL文档的系统变量 #分类：全局（关键字global）系统变量（另外一个名字：全局变量） + # 会话（关键字session）系统变量（另外一个名字：local变量）， #如果没有关键字，默认为：会话级别。 #有的系统变量，既是全局变量，也是会话变量 #1.1.1 查看 #查看全部和部分变量： SHOW GLOBAL VARIABLES; SHOW GLOBAL VARIABLES LIKE '%port%'; SHOW SESSION VARIABLES; SHOW SESSION VARIABLES LIKE '%port%'; #或 SHOW VARIABLES; SHOW VARIABLES LIKE '%port%'; #查看指定系统变量 #作为 MySOL 编码规范，MySQL 中的系统变量以 两个"@”开头，其中“@@global”仅用于标记全局系统变 #，“@@session”仅用于标记会话系统变量。“@@”首先标记会话系统变量，如果会话系统变量不存在，则标记全 #局系统变量。 #全局变量 SELECT @@global.max_connections; #会话变量 SELECT @@session.pseudo_thread_id; #既是全局又是会话变量 SELECT @@global.character_set_client; #或者 SELECT @@session.character_set_client; #1.1.2 修改 #方法1 修改My.ini 文件里的系统变量，但必须重启服务才起作用（不可取） #方法2 在MySQL服务运行期间，使用“SET”命令修改系统变量，不需要重启服务（可取） #修改全局系统变量，仅仅对当前的数据库服务实例有效，一旦重启MySQL服务，就失效了 SET @@global.max_connections = 152; #或 SET GLOBAL max_connections = 153;
MySQL8.0 新增 全局变量的持久化（即便重启MySQL服务了，也还起作用）
SET PERSIST GLOBAL max_connections = 1000; #会话系统变量，仅仅针对当前会话有效（可以理解：链接数据库等于一个会话） SET @@session.pseudo_thread_id = ""; #或 SET SESSION pseudo_thread_id = "";
#1.2 用户自定义变量 #用户自己定义的，MySQL中用户变量以一个“@”开头。“@”符号只针对 会话用户变量而已就可以了 #根据作用范围不同，分为：会话用户变量 和 局部变量 #会话用户变量，作用和会话变量一样，只针对当前会话有效 #局部变量，只在BEGIN和END语句块中有效。局部变量只能在存储过程/函数中使用 #1.2.1 会话用户变量 #举例1 SET @employee_salary = 100; SET @employee_salary2 := 99; SET @sum := @employee_salary + @employee_salary2; SELECT @sum; #举例2 SELECT @count := COUNT(1) FROM employee; SELECT @count; SELECT AVG(salary) INTO @avg_salary FROM employee; SELECT @avg_salary; #1.2.2 局部变量（必须在存储过程/函数中使用） #可以使用DECLARE语句定义一个局部变量 #仅仅在定义它的BEGIN...END中有效 #只能放在BEGIN...END中，而且必须是第一行 #举例1 DELIMITER // CREATE PROCEDURE pro_select_salary() BEGIN #声明并赋默认值 DECLARE emp_name VARCHAR(25); DECLARE emp_name2 VARCHAR(25); DECLARE emp_salary DOUBLE(10, 2) DEFAULT 0; #赋值 SET emp_name = 'tom'; SET emp_name2 := 'frank'; SELECT salary INTO emp_salary FROM employee WHERE id = 100; SELECT emp_name, emp_name2, emp_salary; END // DELIMITER; #调用 CALL pro_select_salary();
2. 定义条件和处理程序（=找错并解决）
#定义条件
#是事先定义程序执行过程中可能遇到的问题
#处理程序
#定义了在遇到问题时应当采取的处理方式，
#并且保证存储过程或函数在遇到警告或错误时能继续执行。
#这样可以增强存储程序处理问题的能力，避免程序异常停止运行。
#2.1 定义条件 #格式： #2.2 处理程序 #格式：DECLARE 处理方式 HANDLER FOR 错误类型 处理语句 #处理方式 #① CONTINUE：表示遇到错误不处理，继续执行 #② EXIT：表示遇到错误马上退出 #③ UNDO：表示遇到错误后撤回之前的操作。MySQL中暂时不支持这样的操作 #错误类型 #① SQLSTATE '字符串错误码'：表示长度为5的sqlstate_value类型的错误代码 #② MySQL_error_code: 匹配数值类型的错误代码 #③ 错误名称 表示DECLARE...CONDITION定义的错误条件名称 #④ SQLWARNING：匹配所有以 01开头的SQLSTATE错误代码 #⑤ NOT FOUND：匹配所有以 02开头的额SQLSTATE错误代码 #⑥ SQLEXCEPTION：匹配所有没有被SQLWARNING 或NOT FOUND捕获的SQLSTATE错误代码 #处理语句 #如果出现上述条件之一，则采用对应的处理方式，并执行指定的处理语句。语句可以像“SET 变量 = 值” #这样的简单语句，也可以使用BEGIN...END编写的复核语句 #举例 DECLARE CONTINUE HANDLER FOR SQLSTATE '34Q32' SET @info = '错误1'; DECLARE EXIT HANDLER FOR 1048 SET @info = '不能为空'; DECLARE NO_SUCH_TABLE CONDITION FOR 1046; DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = '不能为空'; #2.3 举例说明如何使用：定义条件和处理程序 #改造前 DELIMITER // CREATE PROCEDURE pro_query_salary() BEGIN SET @value = 1; UPDATE employee SET salary = NULL WHERE id = 100; SET @value = 2; UPDATE employee SET salary = 9999 WHERE id = 100; SET @value = 3; SELECT @value; END // DELIMITER; #drop procedure pro_query_salary; CALL pro_query_salary(); #改造后 DELIMITER // CREATE PROCEDURE pro_query_salary() BEGIN #声明处理程序 DECLARE CONTINUE HANDLER FOR 1048 SET @proce_value = -1; SET @value = 1; UPDATE employee SET salary = NULL WHERE id = 100; SET @value = 2; UPDATE employee SET salary = 9999 WHERE id = 100; SET @value = 3; END // DELIMITER; CALL pro_query_salary(); SELECT @value, @proce_value;
2.5 LEAVE 和 ITERATE
#2.5.1 关键字LEAVE（=break） #LEAVE语句，可以用在循环语句内，或者以BEGIN和END 包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。 #如果你有面向过程的编程语言的使用经验，可以把LEAVE理解为break #基本格式如下： # LEAVE 标记名
① 举例1 DELIMITER // CREATE PROCEDURE pro_test_leave(IN num INT) leave_label:BEGIN IF num = 0 THEN LEAVE leave_label; ELSEIF num = 1 THEN SELECT AVG(salary) FROM employee; ELSEIF num = 2 THEN SELECT MAX(salary) FROM employee; ELSE SELECT MIN(salary) FROM employee; END IF; SELECT COUNT(1) FROM employee; END // DELIMITER; #drop procedure pro_test_leave; CALL pro_test_leave(2);
② 举例2
DELIMITER //
CREATE PROCEDURE pro_test_leave2(OUT num INT)
BEGIN
DECLARE avg_salary DOUBLE;
DECLARE leave_count INT DEFAULT 0;
SELECT AVG(salary) INTO avg_salary FROM employee;
leave_label:WHILE TRUE DO
IF avg_salary <= 5000
THEN LEAVE leave_label;
END IF;
UPDATE employee SET salary = salary * 0.95;
SET leave_count = leave_count + 1;
SELECT AVG(salary) INTO avg_salary FROM employee;
END WHILE;
SET num = leave_count;
END //
DELIMITER;
#drop procedure pro_test_leave2;
CALL pro_test_leave2(@num); SELECT @num;
③ 举例2
等同于break的return用法
DELIMITER $$
CREATE FUNCTION `fun_get_outbound_toloc_lsh`(obnextid VARCHAR(50), skuid VARCHAR(25))
RETURNS VARCHAR(36)
BEGIN
DECLARE toloc VARCHAR(25) DEFAULT 'Pickto';
SELECT
IFNULL(op.TO_LOC, '') INTO toloc
FROM obn_pick op
LEFT JOIN outbound o ON o.OBN_ID = op.OBN_ID
LEFT JOIN loc l ON l.WH_ID =o.WH_ID AND l.LOC = IFNULL(op.TO_LOC, '')
WHERE o.OBN_EXT_ID = obnextid AND IFNULL(op.TO_LOC, '') <> '' AND IFNULL(op.TO_LOC, '') <> 'Pickto' AND l.LOC_TYPE = 'Pickto' AND op.SKU_ID = skuid
ORDER BY op.UPDATE_DATE DESC
LIMIT 1;
IF IFNULL(toloc, '') <> '' THEN
RETURN toloc; -- 如果值不为空，返回toloc
END IF;
SELECT
IFNULL(od.UDF13, '') INTO toloc
FROM outbound_dt od
LEFT JOIN outbound o ON o.OBN_ID = od.OBN_ID
LEFT JOIN loc l ON l.WH_ID =o.WH_ID AND l.LOC = IFNULL(od.UDF13, '')
WHERE o.OBN_EXT_ID LIKE CONCAT(LEFT(obnextid, 9), '%') AND IFNULL(od.UDF13, '') <> '' AND IFNULL(od.UDF13, '') = 'Pickto' AND l.LOC_TYPE <> 'Pickto'
ORDER BY od.UPDATE_DATE DESC
LIMIT 1;
IF IFNULL(toloc, '') <> '' THEN
RETURN toloc; -- 如果值不为空，返回toloc
END IF;
RETURN toloc;
END$$
DELIMITER ;
#2.5.2 关键字ITERATE(=continue) #只能用在循环语句内，表示重新开始循环，理解为continue #语句格式如下： #ITERATE label(标记名) DELIMITER // CREATE PROCEDURE pro_test_iterate() BEGIN DECLARE num INT DEFAULT 0; iterate_label:LOOP SET num = num + 1; IF num < 10 THEN ITERATE iterate_label; ELSEIF num > 15 THEN LEAVE iterate_label; END IF; SELECT CONCAT('当前执行次数', num); END LOOP; END // DELIMITER; #drop procedure pro_test_iterate; CALL pro_test_iterate();
3. 流程控制
#3.1 介绍 #顺序结构 #分支结构 #条件判断语句：IF、CASE #循环结构 #循环语句：LOOP、WHILE、REPEAT #跳转语句：ITERATE、LEAVE
#3.2 分支结构IF DELIMITER // CREATE PROCEDURE pro_test_if() BEGIN DECLARE emp_name VARCHAR(25) DEFAULT 'frankwang'; IF emp_name IS NULL THEN SELECT 'emp_name is null'; ELSEIF emp_name = 'tomzhang' THEN SELECT 'emp_name is tomzhang'; ELSEIF emp_name ='frankwang' THEN SELECT 'emp_name is frankwang'; ELSE SELECT 'emp_name is other'; END IF; END // DELIMITER ; #DROP PROCEDURE pro_test_if; CALL pro_test_if();
#3.3 分支结构CASE delimiter // create procedure pro_test_case() begin /* 方法1 DECLARE salary INT DEFAULT 0; CASE salary WHEN 1 THEN SELECT 'salary is 1'; WHEN 2 THEN SELECT 'salary is 2'; ELSE SELECT 'salary is 0'; END CASE; */ #方法2 declare salary int default 900; case when salary >= 1000 then select '优秀'; when salary >= 500 and salary <1000 then select '良品'; when salary < 500 then select '不错'; else select '及格'; end case; end // delimiter; #drop procedure pro_test_case; call pro_test_case();
#循环结构四要素 #1. 初始换条件 #2. 循环条件 #3. 循环体 #4. 迭代条件
#3.4 循环结构LOOP DELIMITER // CREATE PROCEDURE pro_test_loop() BEGIN DECLARE num INT DEFAULT 0; loop_label:LOOP SET num = num + 1; IF num > 100 THEN LEAVE loop_label; END IF; END LOOP loop_label; SELECT num; END // DELIMITER ; #drop procedure pro_test_loop; CALL pro_test_loop(); DELIMITER // CREATE PROCEDURE pro_test_loop2(OUT num INT) BEGIN DECLARE avg_salary DOUBLE; DECLARE loop_count INT DEFAULT 0; SELECT AVG(salary) INTO avg_salary FROM employee; loop_label:LOOP IF avg_salary > 15000 THEN LEAVE loop_label; END IF; UPDATE employee SET salary=salary + 500; #更新一下平均数，不然就是死循环了 SELECT AVG(salary) INTO avg_salary FROM employee; SET loop_count = loop_count + 1; END LOOP loop_label; SET num = loop_count; END // DELIMITER; #drop procedure pro_test_loop2; CALL pro_test_loop2(@num); SELECT @num;
#3.5 循环结构WHILE DELIMITER // CREATE PROCEDURE pro_test_while() BEGIN #初始换条件 DECLARE num INT DEFAULT 0; #循环条件 WHILE num < 10 DO #循环体（略） #迭代条件 SET num = num + 1; END WHILE; SELECT num; END // DELIMITER; #drop procedure pro_test_while; CALL pro_test_while(); DELIMITER // CREATE PROCEDURE pro_test_while2(OUT num INT) BEGIN DECLARE avg_salary DOUBLE; DECLARE while_count INT DEFAULT 0; SELECT AVG(salary) INTO avg_salary FROM employee; WHILE avg_salary > 5000 DO UPDATE employee SET salary = salary * 0.9; SET while_count = while_count + 1; SELECT AVG(salary) INTO avg_salary FROM employee; END WHILE; SET num = while_count; END // DELIMITER; #drop procedure pro_test_while2; CALL pro_test_while2(@num); SELECT @num;
#3.6 循环结构REPEAT #区别先前：先执行一次循环体，再判断循环条件 DELIMITER // CREATE PROCEDURE pro_test_repeat() BEGIN DECLARE num INT DEFAULT 0; REPEAT SET num = num + 1; #1. 注意：10后面没有分号的 #2. num > 10 是退出循环，和先前2个循环 num > 10(进入循环) 是不一样的 UNTIL num > 10 END REPEAT; SELECT num; END // DELIMITER; #drop procedure pro_test_repeat(); CALL pro_test_repeat(); DELIMITER // CREATE PROCEDURE pro_test_repeat(OUT num INT) BEGIN DECLARE avg_salary DOUBLE; DECLARE repeat_count INT DEFAULT 0; SELECT AVG(salary) INTO avg_salary FROM employee; REPEAT UPDATE employee SET salary = salary * 1.3; SET repeat_count = repeat_count + 1; SELECT AVG(salary) INTO avg_salary FROM employee; #1. 注意：13000后面没有分号的 #2. avg_salary > 13000 是退出循环，和先前2个循环 avg_salary > ****(进入循环) 是不一样的 UNTIL avg_salary > 13000 END REPEAT; SET num = repeat_count;w END // DELIMITER; #drop procedure pro_test_repeat; CALL pro_test_repeat(@num); SELECT @num;
浙公网安备 33010602011771号