可以为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 ;
