32.1 存储过程变量
DECLARE variable_name datatype(size) [DEFAULT default_value];DECLARE salary DECIMAL(8, 2) DEFAULT 0.0;DECLARE x, y INTEGER DEFAULT 0;SET variable_name = value;SELECT expression1, ...INTO variable1, ...FROM ...;SELECT expression1, ...FROM ...INTO variable1, ...;DELIMITER $$CREATE PROCEDURE TestProc()BEGIN
DECLARE sal decimal(8, 2) DEFAULT 0.0;
DECLARE x, y integer DEFAULT 0;
SELECT salary FROM employee WHERE emp_name = '张飞'
INTO sal;
SET x = 6, y = 9;
SELECT sal, x, y;
END$$DELIMITER ;CALL TestProc();sal |x|y|--------|-|-|24000.00|6|9|DROP PROCEDURE TestProc;DELIMITER $$CREATE PROCEDURE TestProc()BEGIN
DECLARE x integer DEFAULT 0;
SET x = 6;
SELECT 'outer', x;
BEGIN
DECLARE x integer DEFAULT 0;
SET x = 7;
SELECT 'inner', x;
END;
END$$DELIMITER ;CALL TestProc();outer|x|-----|-|outer|6|inner|x|-----|-|inner|7|32.2 条件控制语句
32.2.1 IF 语句
IF search_condition THEN statement_list [ELSEIF other_condition THEN other_statement_list]
...
[ELSE else_statement_list]END IF;DROP PROCEDURE TestProc;DELIMITER $$CREATE PROCEDURE TestProc()BEGIN
DECLARE x, y integer DEFAULT 0;
SET x = 5, y = 10;
IF x = 5 THEN
SELECT 'x = 5';
END IF;
IF y < 10 THEN
SELECT 'y < 10';
ELSEIF y > 10 THEN
SELECT 'y > 10';
ELSE
SELECT 'y = 10';
END IF;
END$$DELIMITER ;CALL TestProc();x = 5|-----|x = 5|y = 10|------|y = 10|32.2.2 CASE 语句
CASE case_value WHEN when_value1 THEN statement_list1 [WHEN when_value2 THEN statement_list2]
...
[ELSE else_statement_list]END CASE;CASE
WHEN search_condition1 THEN statement_list1 [WHEN search_condition2 THEN statement_list2]
...
[ELSE else_statement_list]END CASE;DROP PROCEDURE TestProc;DELIMITER $$CREATE PROCEDURE TestProc()BEGIN
DECLARE x integer DEFAULT 0;
SET x = 5;
CASE
WHEN x < 0 THEN
SELECT 'x < 0';
WHEN x = 0 THEN
SELECT 'x = 0';
ELSE
SELECT 'x > 0';
END CASE;
END$$DELIMITER ;CALL TestProc();x > 0|-----|x > 0|32.3 循环控制语句
32.3.1 LOOP 语句
[begin_label:] LOOP
statement_listEND LOOP [end_label]DELIMITER $$CREATE PROCEDURE TestLoop()BEGIN
DECLARE x,sumx integer DEFAULT 0;
label1: LOOP
SET x = x + 1;
IF x > 10 THEN
LEAVE label1;
END IF;
IF x mod 2 THEN
ITERATE label1;
ELSE
SET sumx = sumx + x;
END IF;
END LOOP label1;
SELECT sumx;
END$$DELIMITER ;CALL TestLoop();sumx|----|
30|32.3.2 REPAET 语句
[begin_label:] REPEAT
statement_list
UNTIL search_conditionEND REPEAT [end_label];DELIMITER $$CREATE PROCEDURE TestRepeat()BEGIN
DECLARE x,sumx integer DEFAULT 0;
label1: REPEAT
SET x = x + 1;
IF x mod 2 THEN
ITERATE label1;
ELSE
SET sumx = sumx + x;
END IF;
UNTIL x = 10 END REPEAT label1;
SELECT sumx;
END$$DELIMITER ;32.3.3 WHILE 语句
[begin_label:] WHILE search_condition DO
statement_listEND WHILE [end_label];DELIMITER $$CREATE PROCEDURE TestWhile()BEGIN
DECLARE x,sumx integer DEFAULT 0;
label1: WHILE x < 10 DO
SET x = x + 1;
IF x mod 2 THEN
ITERATE label1;
ELSE
SET sumx = sumx + x;
END IF;
END WHILE label1;
SELECT sumx;
END$$DELIMITER ;32.4 错误处理
32.4.1 定义错误条件
DECLARE condition_name CONDITION
FOR { mysql_error_code | SQLSTATE [VALUE] sqlstate_value }32.4.2 定义错误处理器
DECLARE { CONTINUE | EXIT } HANDLER
FOR condition_value [, condition_value] ...
statement;condition_value: {
mysql_error_code | SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION
}DELIMITER $$CREATE PROCEDURE TestError()BEGIN
DECLARE empid int DEFAULT 1;
DECLARE duplicate_employee CONDITION FOR 1062;
DECLARE EXIT HANDLER
FOR duplicate_employee BEGIN
ROLLBACK;
SELECT CONCAT('重复的员工信息！', 'emp_id:', empid) Message;
END;
INSERT INTO employee
VALUES (empid, '刘备', '男', 1, NULL, DATE('2000-01-01'), 1, 30000, 10000, 'liubei@shuguo.com');END$$DELIMITER ;CALL TestError();Message |---------------------|重复的员工信息！emp_id:1|DECLARE EXIT HANDLER
FOR duplicate_employee BEGIN
UPDATE employee SET emp_name = '刘备'
...
WHERE emp_id = empid;
END;32.4.3 获取诊断信息
mysql> DROP TABLE no_such_table;ERROR 1051 (42S02): Unknown table 'hrdb.no_such_table'mysql> GET DIAGNOSTICS CONDITION 1
-> @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;Query OK, 0 rows affected (0.00 sec)mysql> SELECT @p1, @p2;+-------+------------------------------------+| @p1 | @p2 |+-------+------------------------------------+| 42S02 | Unknown table 'hrdb.no_such_table' |+-------+------------------------------------+1 row in set (0.00 sec)GET [CURRENT | STACKED] DIAGNOSTICS {
target = NUMBER | target = ROW_COUNT,
...}
GET [CURRENT | STACKED] DIAGNOSTICS
CONDITION condition_number {
target = condition_information_item_name,
...}
condition_information_item_name: {
CLASS_ORIGIN | SUBCLASS_ORIGIN | RETURNED_SQLSTATE | MESSAGE_TEXT | MYSQL_ERRNO | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CATALOG_NAME | SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | CURSOR_NAME
}语句信息，包括错误条件的编号（NUMBER）和影响的行数（ROW_COUNT），对应上面的第一种语法。
条件信息，例如错误码（MYSQL_ERRNO）和错误信息（MESSAGE_TEXT）等，对应上面的第二种语法。如果一个语句触发了多个错误条件，每个错误条件都提供了一个条件区域，编号从 1 到 NUMBER。
CREATE TABLE t(id int NOT NULL);DELIMITER $$CREATE PROCEDURE do_insert(value INT)BEGIN
-- 声明保存诊断信息的变量
DECLARE cno INT;
DECLARE code CHAR(5) DEFAULT '00000';
DECLARE msg TEXT;
DECLARE nrows INT;
DECLARE result TEXT;
-- 声明异常错误处理器
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
GET DIAGNOSTICS cno = NUMBER;
GET DIAGNOSTICS CONDITION cno
code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;
END;
-- 执行插入语句
INSERT INTO t(id) VALUES(value);
-- 检查插入是否成功
IF code = '00000' THEN
GET DIAGNOSTICS nrows = ROW_COUNT;
SET result = CONCAT('insert succeeded, row count = ',nrows);
ELSE
SET result = CONCAT('insert failed, error = ',code,', message = ',msg);
END IF;
-- 返回错误信息
SELECT result;END$$DELIMITER ;CALL do_insert(1);result |-------------------------------|insert succeeded, row count = 1|CALL do_insert(NULL);result |------------------------------------------------------------------|insert failed, error = 23000, message = Column 'id' cannot be null|32.4.4 抛出错误条件
SIGNAL { SQLSTATE [VALUE] sqlstate_value | condition_name } [SET condition_information_item_name = simple_value, ...]condition_information_item_name: {
CLASS_ORIGIN | SUBCLASS_ORIGIN | MESSAGE_TEXT | MYSQL_ERRNO | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CATALOG_NAME | SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | CURSOR_NAME
}DELIMITER $$CREATE PROCEDURE p(divisor INT)BEGIN
IF divisor = 0 THEN
SIGNAL SQLSTATE '22012'
SET MESSAGE_TEXT = 'devided by zero!';
END IF;END;DELIMITER ;CALL p(0);ERROR 1644 (22012): devided by zero!RESIGNAL 语句只能用于错误处理器的处理语句中，可以修改某些错误信息后再次抛出；
RESIGNAL 语句的属性可以忽略，意味着将接收到的错误不经修改再次抛出。
32.5 游标的使用
Asensitive 灵敏性，服务器可能直接遍历基础表的数据，也可能复制一份额外的临时数据；
只读性（Read only），不能通过游标修改基础表中的数据；
不可滚动性（Nonscrollable），只能按照查询结果的顺序访问数据，不能反向遍历，也不能跳跃访问数据行。
通过
DECLARE语句声明游标；使用
OPEN语句打开游标；循环使用
FETCH语句获取游标中的数据行；使用
CLOSE语句关闭游标并释放资源。
DELIMITER $$CREATE PROCEDURE TestCurosr()BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE id int;
DECLARE name varchar(50);
DECLARE namelist varchar(500) default '';
DECLARE cur1 CURSOR FOR
SELECT emp_id, emp_name FROM employee WHERE sex = '女';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
getEmp: LOOP
FETCH cur1 INTO id, name;
IF done THEN
LEAVE getEmp;
ELSE
SET namelist = concat( name, ';', namelist);
END IF;
END LOOP;
CLOSE cur1;
SELECT namelist;END$$DELIMITER ;CALL TestCurosr();namelist |------------------|赵氏;孙丫鬟;孙尚香;|32.5.1 声明游标
DECLARE cursor_name CURSOR FOR select_statement; DECLARE cur1 CURSOR FOR
SELECT emp_id, emp_name FROM employee WHERE sex = '女';32.5.2 打开游标
OPEN cur1;32.5.3 遍历游标
FETCH cur1 INTO id, name;32.5.4 关闭游标
CLOSE cur1;32.6 访问权限控制
原文作者：SQL编程思想；如有侵权，请联系管理员删除。
请登录后发表评论
登录
全部评论
