mysql定义处理程序
可以为sql执行过程中发生的某种类型的错误定义特殊的处理程序，定义处理程序时，使用declare语句的语法如下：
declare 处理方式 handler for 错误类型 处理语句
处理方式 ：处理有3个取值：continue 、exit 、undo
continue ：表示遇到错误不处理，继续执行
exit：表示遇到错误马上退出。
undo：表示遇到错误后撤回之前的操作，mysql中暂时不支持这样的操作
错误类型（即条件）可以有如下取值
sqlstate （SQLSTATE）‘字符串错误码’：表示长度为5的sqlstate_value 类型的错误代码；
MySQL_error_code:匹配数值类型错误代码；
错误名称：表示DECLARE... CONDITION定义的错误条件名称。
SLQWARNIG：匹配所有以01开头的SQLSTATE错误代码
NOT FOUND：匹配所有以02开头的SQLSTATE错误代码
SQLEXCEPTION：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQKSTATE错误代码；
CREATE TABLE admin( id INT PRIMARY KEY AUTO_INCREMENT, user_name VARCHAR(15) NOT NULL, pwd VARCHAR(25) NOT NULL ); SELECT * FROM admin; DESC admin; delimiter $ CREATE PROCEDURE insert_user(IN user VARCHAR(15),IN pwd VARCHAR(25)) BEGIN INSERT INTO admin(user_name,pwd) VALUES (user_name,pwd); END $ delimiter $ CALL insert_user('tom','123') CREATE TABLE beauty( id INT PRIMARY KEY auto_increment, `name` VARCHAR(15) NOT NULL, phone VARCHAR(15) not NULL, birth date ); INSERT INTO beauty (`name`,phone,birth) VALUES ('朱茵','123435323','1982-02-12'), ('刘诗诗','1435435323','1583-02-12'), ('邓紫棋','1238735323','1381-02-12'), ('刘亦菲','1642215323','2132-02-12'); SELECT * FROM beauty delimiter $ CREATE PROCEDURE get_phone(IN id INT,OUT `name` VARCHAR(15),OUT phone VARCHAR(25)) BEGIN SELECT b.`name` ,b.phone INTO `name`,phone FROM beauty b WHERE b.id = id; END$ delimiter ; CALL get_phone(2,@name,@phone); SELECT @name,@phone; SET @name = #用户变量 局部变量 #会话用户变量，使用@开头，作用域当前会话 #局部变量：只能使用在存储过程和存储函数中的 CREATE PROCEDURE test_var1() BEGIN DECLARE a INT DEFAULT 0; DECLARE b INT DEFAULT 0; DECLARE `empname` VARCHAR(500); #赋值 SET a = 1; SET b := 2; SELECT `name` INTO `empname` FROM t_base WHERE id = 1; #使用 SELECT a,b,`empname`; END DROP PROCEDURE test_var1 CALL test_var1() CREATE PROCEDURE test_pro() BEGIN DECLARE emp_name VARCHAR(25); DECLARE sal DOUBLE(10,2) DEFAULT 0.0; #赋值 SELECT `des`,id INTO emp_name,sal FROM t_base WHERE id = 1; #使用 SELECT emp_name,sal; END CALL test_pro() CREATE PROCEDURE updateDataNoCondition() BEGIN #声明处理程序 #处理方式一 DECLARE CONTINUE HANDLER FOR 1048 SET @prc_value = -1; #处理方式二 #DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @prc_value = -1; SET @x =1; 写一个更新语句 SET @x =2; 写一个更新语句二 SET @x =3; 写一个更新语句三 END； 然后再调用存储过程 查看变量， #流程控制 CREATE PROCEDURE test_if() BEGIN DECLARE last_name VARCHAR(10); if last_name is null then SELECT 'last_name_is null'; end if; END; CALL test_if delimiter $ CREATE PROCEDURE test() BEGIN #声明变量 DECLARE emp_sal DOUBLE; #记录员工工资 DECLARE bounds DOUBLE; #记录奖金率 #赋值 SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id; SELECT commission_pct INTO bounds from employees WHERE employee_id = emp_id; #判断 IF emp_sal <9000 THEN UPDATE emplpoyee SET salary = 9000 WHERE employee_id = emp_id; ELSEIF emp_sal<10000 AND bounds IS NULL ELSE UPDATE employees SET commssoion_pct = 0.01 WHERE employee_id = emp_id; ELSE UPDATE employees SET salary = salary+100 WHERE employee_id = emp_id; END IF; END $ delimiter ; #循环结构之loop delimiter $ CREATE PROCEDURE test_loop() BEGIN DECLARE num INT DEFAULT 1; loop_lable:LOOP #重新赋值 SET num = num + 1; IF num >= 10 THEN LEAVE loop_lable; END IF; END LOOP loop_lable; #查看num SELECT num; END $ delimiter ; CALL test_loop() delimiter $ CREATE PROCEDURE update_salary_loop(OUT num INT) BEGIN #声明变量 DECLARE avg_sal DOUBLE; #记录员工的平均工资 DECLARE loop_count INT DEFAULT 0; #记录循环的次数 #获取员工的平均工资 SELECT AVG(salary) INTO avg_sal FROM employee; loop_lab:LOOP #结束循环的条件 IF avg_sal >= 1200 THEN LEAVE loop_lab; END IF; #如果低于12000，循环更新员工工资 UPDATE employee SET salary = salary * 1.1; #更新avg_sal变量的值 UPDATE AVG(salary) INTO avg_sal FROM employee; #记录循环次数 SET loop_count = loop_count +1; END LOOP lopp_lab; SET num = loop_count; END $ delimiter ; #初始化条件 #循环条件 #循环体 #迭代条件
delimiter $
CREATE PROCEDURE test_while()
BEGIN
#初始化条件
DECLARE num INT DEFAULT 1;
#循环条件
WHILE num <= 10 DO
#循环体（略）
#迭代条件
SET num = num+1;
END WHILE;
SELECT num;
END $
delimiter ;
#调用存储过程
CALL test_while
#删除存储过程
DROP PROCEDURE test_while
delimiter $
CREATE PROCEDURE test_while(OUT num INT)
BEGIN
#声明变量
DECLARE avg_sal DOUBLE;
DECLARE while_count INT DEFAULT 0;
#赋值
SELECT AVG(salary) INTO avg_sal FROM employees ;
WHILE avg_sal > 5000 DO
#循环体
UPDATE employee SET salary = salary * 0.9 ;
SET while_count = while_count + 1;
#再赋值
SELECT AVG(salary) INTO avg_sal = FROM employees;
END WHILE;
#给num赋值
SET num = while_count;
END $
delimiter ;
delimiter $
CREATE PROCEDURE test_repeat()
BEGIN
#声明变量
DECLARE num INT DEFAULT 1;
REPEAT
SET num = num + 1;
UNTIL num >= 10 #不满足继续循环，满足跳出
END REPEAT;
#查看
SELECT num;
END $
delimiter ;
#调用存储过程
CALL test_repeat
delimiter $
CREATE PROCEDURE update_salary_repeat(OUT num INT)
BEGIN
#声明变量
DECLARE agv_sal DOUBLE; #记录平均工资
DECLARE repeat_count INT DEFAULT 1; #记录循环次数
#赋值
SELECT AVG(salary) INTO avg_sal FROM employees;
REPLACE
UPDATE employees SET salary = salary *1.15;
SET repleat_count = repleat_count + 1 ;
SELECT AVG(salary) INTO avg_sal FROM employees;
UNTIL avg_sal >= 13000 #这里不加分号
END REPLACE;
END $
delimiter ;
CALL update_salary_repeat(@num)
SELECT @num;
delimiter $
CREATE PROCEDURE test(OUT num INT)
BEGIN
DECLARE avg_sal DOUBLE; #记录平均工资
DECLARE while_count DEFAULT 0; #记录循环次数;
SELECT AVG(salary) INTO avg_sal FROM employees # 初始化条件
while_label:WHILE TRUE DO
#循环条件
IF avg_sal <= 10000 THEN
LEAVE while_label;
END IF;
UPDATE employees SET salary = salary * 0.9;
SET while_count = while_count + 1;
#迭代条件
SELECT AVG(salary) INTO avg_sal FROM employees;
END WHILE;
#赋值
SET num = while_count;
END $
delimiter ;
delimiter $
CREATE PROCEDURE test_iterate()
BEGIN
DECLARE num INT DEFAULT 0 ;
loop_label:LOOP
#赋值
SET num = num + 1;
IF num< 10
THEN ITERATE loop_label;
ELSEIF num > 15
THEN LEAVE loop_label;
END IF;
SELECT '文本输出' ;
END LOOP ;
END $
delimiter ;
浙公网安备 33010602011771号