1,前提
需要MySQL 5
2,Hello World
MySQL存储过程之Hello World
3,变量
使用 DECLARE来声明, DEFAULT赋默认值, SET赋值
4,参数
IN为默认类型,值必须在调用时指定,值不能返回(值传递)
OUT值可以返回(指针传递)
INOUT值必须在调用时指定,值可以返回
5,条件判断
IF THEN、 ELSEIF、 ELSE、 END IF
6,循环
LOOP、 END LOOP
WHILE DO、 END WHILE
REPEAT、 UNTILL
7,异常处理
如果用cursor获取SELECT语句返回的所有结果集时应该定义NOT FOUND error handler来防止存储程序提前终结
如果SQL语句可能返回constraint violation等错误时应该创建一个handler来防止程序终结
8,数据库交互
INTO用于存储单行记录的查询结果
CURSOR用于处理多行记录的查询结果
unbounded SELECT语句用于存储过程返回结果集
UPDATE、INSERT、DELETE、CREATE TABLE等 非查询语句也可以嵌入存储过程里
9,使用 CALL调用存储程序
10,一个复杂的例子
11,存储方法
存储方法与存储过程的区别
1,存储方法的参数列表只允许IN类型的参数,而且没必要也不允许指定IN关键字
2,存储方法返回一个单一的值,值的类型在存储方法的头部定义
3,存储方法可以在SQL语句内部调用
4,存储方法不能返回结果集
语法:
各参数说明见 CREATE PROCEDURE and CREATE FUNCTION Syntax
例子:
12,触发器
触发器在INSERT、UPDATE或DELETE等DML语句修改数据库表时触发
触发器的典型应用场景是重要的业务逻辑、提高性能、监控表的修改等
触发器可以在DML语句执行前或后触发
需要MySQL 5
2,Hello World
MySQL存储过程之Hello World
- DELIMITER $$
- DROP PROCEDURE IF EXISTS HelloWorld$$
- CREATE PROCEDURE HelloWorld()
- BEGIN
- SELECT "Hello World!";
- END$$
- DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXISTS HelloWorld$$
CREATE PROCEDURE HelloWorld()
BEGIN
SELECT "Hello World!";
END$$
DELIMITER ;
3,变量
使用 DECLARE来声明, DEFAULT赋默认值, SET赋值
- DECLARE counter INT DEFAULT 0;
- SET counter = counter+1;
DECLARE counter INT DEFAULT 0;
SET counter = counter+1;
4,参数
IN为默认类型,值必须在调用时指定,值不能返回(值传递)
OUT值可以返回(指针传递)
INOUT值必须在调用时指定,值可以返回
- CREATE PROCEDURE test(a INT, OUT b FLOAT, INOUT c INT)
CREATE PROCEDURE test(a INT, OUT b FLOAT, INOUT c INT)
5,条件判断
IF THEN、 ELSEIF、 ELSE、 END IF
- DELIMITER $$
- DROP PROCEDURE IF EXISTS discounted_price$$
- CREATE PROCEDURE discunted_price(normal_price NUMERIC(8, 2), OUT discount_price NUMERIC(8, 2))
- BEGIN
- IF (normal_price > 500) THEN
- SET discount_price = normal_price * .8;
- ELSEIF (normal_price > 100) THEN
- SET discount_price = normal_price * .9;
- ELSE
- SET discount_price = normal_price;
- END IF;
- END$$
- DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXISTS discounted_price$$
CREATE PROCEDURE discunted_price(normal_price NUMERIC(8, 2), OUT discount_price NUMERIC(8, 2))
BEGIN
IF (normal_price > 500) THEN
SET discount_price = normal_price * .8;
ELSEIF (normal_price > 100) THEN
SET discount_price = normal_price * .9;
ELSE
SET discount_price = normal_price;
END IF;
END$$
DELIMITER ;
6,循环
LOOP、 END LOOP
- DELIMITER $$
- DROP PROCEDURE IF EXISTS simple_loop$$
- CREATE PROCEDURE simple_loop(OUT counter INT)
- BEGIN
- SET counter = 0;
- my_simple_loop: LOOP
- SET counter = counter+1;
- IF counter = 10 THEN
- LEAVE my_simple_loop;
- END IF;
- END LOOP my_simple_loop;
- END$$
- DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXISTS simple_loop$$
CREATE PROCEDURE simple_loop(OUT counter INT)
BEGIN
SET counter = 0;
my_simple_loop: LOOP
SET counter = counter+1;
IF counter = 10 THEN
LEAVE my_simple_loop;
END IF;
END LOOP my_simple_loop;
END$$
DELIMITER ;
WHILE DO、 END WHILE
- DELIMITER $$
- DROP PROCEDURE IF EXISTS simple_while$$
- CREATE PROCEDURE simple_while(OUT counter INT)
- BEGIN
- SET counter = 0;
- WHILE counter != 10 DO
- SET counter = counter+1;
- END WHILE;
- END$$
- DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXISTS simple_while$$
CREATE PROCEDURE simple_while(OUT counter INT)
BEGIN
SET counter = 0;
WHILE counter != 10 DO
SET counter = counter+1;
END WHILE;
END$$
DELIMITER ;
REPEAT、 UNTILL
- DELIMITER $$
- DROP PROCEDURE IF EXISTS simple_repeat$$
- CREATE PROCEDURE simple_repeat(OUT counter INT)
- BEGIN
- SET counter = 0;
- REPEAT
- SET counter = counter+1;
- UNTIL counter = 10 END REPEAT;
- END$$
- DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXISTS simple_repeat$$
CREATE PROCEDURE simple_repeat(OUT counter INT)
BEGIN
SET counter = 0;
REPEAT
SET counter = counter+1;
UNTIL counter = 10 END REPEAT;
END$$
DELIMITER ;
7,异常处理
如果用cursor获取SELECT语句返回的所有结果集时应该定义NOT FOUND error handler来防止存储程序提前终结
如果SQL语句可能返回constraint violation等错误时应该创建一个handler来防止程序终结
8,数据库交互
INTO用于存储单行记录的查询结果
- DECLARE total_sales NUMERIC(8, 2);
- SELECT SUM(sale_value) INTO total_sales FROM sales WHERE customer_id=in_customer_id;
DECLARE total_sales NUMERIC(8, 2);
SELECT SUM(sale_value) INTO total_sales FROM sales WHERE customer_id=in_customer_id;
CURSOR用于处理多行记录的查询结果
- DELIMITER $$
- DROP PROCEDURE IF EXITS cursor_example$$
- CREATE PROCEDURE cursor_example()
- READS SQL DATA
- BEGIN
- DECLARE l_employee_id INT;
- DECLARE l_salary NUMERIC(8,2);
- DECLARE l_department_id INT;
- DECLARE done INT DEFAULT 0;
- DECLARE cur1 CURSOR FOR SELECT employee_id, salary, department_id FROM employees;
- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
- OPEN cur1;
- emp_loop: LOOP
- FETCH cur1 INTO l_employee_id, l_salary, l_department_id;
- IF done=1 THEN
- LEAVE emp_loop;
- END IF;
- END LOOP emp_loop;
- CLOSE cur1;
- END$$
- DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXITS cursor_example$$
CREATE PROCEDURE cursor_example()
READS SQL DATA
BEGIN
DECLARE l_employee_id INT;
DECLARE l_salary NUMERIC(8,2);
DECLARE l_department_id INT;
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT employee_id, salary, department_id FROM employees;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN cur1;
emp_loop: LOOP
FETCH cur1 INTO l_employee_id, l_salary, l_department_id;
IF done=1 THEN
LEAVE emp_loop;
END IF;
END LOOP emp_loop;
CLOSE cur1;
END$$
DELIMITER ;
unbounded SELECT语句用于存储过程返回结果集
- DELIMITER $$
- DROP PROCEDURE IF EXISTS sp_emps_in_dept$$
- CREATE PROCEDURE sp_emps_in_dept(in_employee_id INT)
- BEGIN
- SELECT employee_id, surname, firstname, address1, address2, zipcode, date_of_birth FROM employees WHERE department_id=in_employee_id;
- END$$
- DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXISTS sp_emps_in_dept$$
CREATE PROCEDURE sp_emps_in_dept(in_employee_id INT)
BEGIN
SELECT employee_id, surname, firstname, address1, address2, zipcode, date_of_birth FROM employees WHERE department_id=in_employee_id;
END$$
DELIMITER ;
UPDATE、INSERT、DELETE、CREATE TABLE等 非查询语句也可以嵌入存储过程里
- DELIMITER $$
- DROP PROCEDURE IF EXITS sp_update_salary$$
- CREATE PROCEDURE sp_update_salary(in_employee_id INT, in_new_salary NUMERIC(8,2))
- BEGIN
- IF in_new_salary < 5000 OR in_new_salary > 500000 THEN
- SELECT "Illegal salary: salary must be between $5000 and $500, 000";
- ELSE
- UPDATE employees SET salary=in_new_salary WHERE employee_id=in_employee_id;
- END IF:
- END$$
- DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXITS sp_update_salary$$
CREATE PROCEDURE sp_update_salary(in_employee_id INT, in_new_salary NUMERIC(8,2))
BEGIN
IF in_new_salary < 5000 OR in_new_salary > 500000 THEN
SELECT "Illegal salary: salary must be between $5000 and $500, 000";
ELSE
UPDATE employees SET salary=in_new_salary WHERE employee_id=in_employee_id;
END IF:
END$$
DELIMITER ;
9,使用 CALL调用存储程序
- DELIMITER $$
- DROP PROCEDURE IF EXISTS call_example$$
- CREATE PROCEDURE call_example(employee_id INT, employee_type VARCHAR(20))
- NO SQL
- BEGIN
- DECLARE l_bonus_amount NUMERIC(8,2);
- IF employee_type='MANAGER' THEN
- CALL calc_manager_bonus(employee_id, l_bonus_amount);
- ELSE
- CALL calc_minion_bonus(employee_id, l_bonus_amount);
- END IF;
- CALL grant_bonus(employee_id, l_bonus_amount);
- END$$
- DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXISTS call_example$$
CREATE PROCEDURE call_example(employee_id INT, employee_type VARCHAR(20))
NO SQL
BEGIN
DECLARE l_bonus_amount NUMERIC(8,2);
IF employee_type='MANAGER' THEN
CALL calc_manager_bonus(employee_id, l_bonus_amount);
ELSE
CALL calc_minion_bonus(employee_id, l_bonus_amount);
END IF;
CALL grant_bonus(employee_id, l_bonus_amount);
END$$
DELIMITER ;
10,一个复杂的例子
- CREATE PROCEDURE putting_it_all_together(in_department_id INT)
- MODIFIES SQL DATA
- BEGIN
- DECLARE l_employee_id INT;
- DECLARE l_salary NUMERIC(8,2);
- DECLARE l_department_id INT;
- DECLARE l_new_salary NUMERIC(8,2);
- DECLARE done INT DEFAULT 0;
- DECLARE cur1 CURSOR FOR
- SELECT employee_id, salary, department_id
- FROM employees
- WHERE department_id=in_department_id;
- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
- CREATE TEMPORARY TABLE IF NOT EXISTS emp_raises
- (employee_id INT, department_id INT, new_salary NUMERIC(8,2));
- OPEN cur1;
- emp_loop: LOOP
- FETCH cur1 INTO l_employee_id, l_salary, l_department_id;
- IF done=1 THEN /* No more rows */
- LEAVE emp_loop;
- END IF;
- CALL new_salary(1_employee_id, l_new_salary); /* Get new salary */
- IF (l_new_salary <> l_salary) THEN /* Salary changed */
- UPDATE employees
- SET salary=l_new_salary
- WHERE employee_id=l_employee_id;
- /* Keep track of changed salaries */
- INSERT INTO emp_raises(employee_id, department_id, new_salary)
- VALUES (l_employee_id, l_department_id, l_new_salary);
- END IF:
- END LOOP emp_loop;
- CLOSE cur1;
- /* Print out the changed salaries */
- SELECT employee_id, department_id, new_salary from emp_raises
- ORDER BY employee_id;
- END;
CREATE PROCEDURE putting_it_all_together(in_department_id INT)
MODIFIES SQL DATA
BEGIN
DECLARE l_employee_id INT;
DECLARE l_salary NUMERIC(8,2);
DECLARE l_department_id INT;
DECLARE l_new_salary NUMERIC(8,2);
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR
SELECT employee_id, salary, department_id
FROM employees
WHERE department_id=in_department_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
CREATE TEMPORARY TABLE IF NOT EXISTS emp_raises
(employee_id INT, department_id INT, new_salary NUMERIC(8,2));
OPEN cur1;
emp_loop: LOOP
FETCH cur1 INTO l_employee_id, l_salary, l_department_id;
IF done=1 THEN /* No more rows */
LEAVE emp_loop;
END IF;
CALL new_salary(1_employee_id, l_new_salary); /* Get new salary */
IF (l_new_salary <> l_salary) THEN /* Salary changed */
UPDATE employees
SET salary=l_new_salary
WHERE employee_id=l_employee_id;
/* Keep track of changed salaries */
INSERT INTO emp_raises(employee_id, department_id, new_salary)
VALUES (l_employee_id, l_department_id, l_new_salary);
END IF:
END LOOP emp_loop;
CLOSE cur1;
/* Print out the changed salaries */
SELECT employee_id, department_id, new_salary from emp_raises
ORDER BY employee_id;
END;
11,存储方法
存储方法与存储过程的区别
1,存储方法的参数列表只允许IN类型的参数,而且没必要也不允许指定IN关键字
2,存储方法返回一个单一的值,值的类型在存储方法的头部定义
3,存储方法可以在SQL语句内部调用
4,存储方法不能返回结果集
语法:
- CREATE
- [DEFINER = { user | CURRENT_USER }]
- PROCEDURE sp_name ([proc_parameter[,...]])
- [characteristic ...] routine_body
- CREATE
- [DEFINER = { user | CURRENT_USER }]
- FUNCTION sp_name ([func_parameter[,...]])
- RETURNS type
- [characteristic ...] routine_body
- proc_parameter:
- [ IN | OUT | INOUT ] param_name type
- func_parameter:
- param_name type
- type:
- Any valid MySQL data type
- characteristic:
- LANGUAGE SQL
- | [NOT] DETERMINISTIC
- | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
- | SQL SECURITY { DEFINER | INVOKER }
- | COMMENT 'string'
- routine_body:
- Valid SQL procedure statement
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
routine_body:
Valid SQL procedure statement
各参数说明见 CREATE PROCEDURE and CREATE FUNCTION Syntax
例子:
- DELIMITER $$
- DROP FUNCTION IF EXISTS f_discount_price$$
- CREATE FUNCTION f_discount_price
- (normal_price NUMERIC(8,2))
- RETURNS NUMERIC(8,2)
- DETERMINISTIC
- BEGIN
- DECLARE discount_price NUMERIC(8,2);
- IF (normal_price > 500) THEN
- SET discount_price = normal_price * .8;
- ELSEIF (normal_price >100) THEN
- SET discount_price = normal_price * .9;
- ELSE
- SET discount_price = normal_price;
- END IF;
- RETURN(discount_price);
- END$$
- DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS f_discount_price$$
CREATE FUNCTION f_discount_price
(normal_price NUMERIC(8,2))
RETURNS NUMERIC(8,2)
DETERMINISTIC
BEGIN
DECLARE discount_price NUMERIC(8,2);
IF (normal_price > 500) THEN
SET discount_price = normal_price * .8;
ELSEIF (normal_price >100) THEN
SET discount_price = normal_price * .9;
ELSE
SET discount_price = normal_price;
END IF;
RETURN(discount_price);
END$$
DELIMITER ;
12,触发器
触发器在INSERT、UPDATE或DELETE等DML语句修改数据库表时触发
触发器的典型应用场景是重要的业务逻辑、提高性能、监控表的修改等
触发器可以在DML语句执行前或后触发
- DELIMITER $$
- DROP TRIGGER sales_trigger$$
- CREATE TRIGGER sales_trigger
- BEFORE INSERT ON sales
- FOR EACH ROW
- BEGIN
- IF NEW.sale_value > 500 THEN
- SET NEW.free_shipping = 'Y';
- ELSE
- SET NEW.free_shipping = 'N';
- END IF;
- IF NEW.sale_value > 1000 THEN
- SET NEW.discount = NEW.sale_value * .15;
- ELSE
- SET NEW.discount = 0;
- END IF;
- END$$
- DELIMITER ;