MySQL存储过程
来源:互联网 发布:php引号转义 编辑:程序博客网 时间:2024/06/04 22:44
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;
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 ;
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 ;
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 ;
9,使用CALL调用存储程序
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;
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 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 ;
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 ;
0 0
- 【mysql】mysql存储过程
- MySQL存储过程详解 mysql 存储过程
- MySQL存储过程详解 mysql 存储过程
- MySQL存储过程详解 mysql 存储过程
- MySQL存储过程详解 mysql 存储过程
- MySQL存储过程详解 mysql 存储过程
- MySQL存储过程详解 mysql 存储过程
- MySQL存储过程详解 mysql 存储过程
- MySQL存储过程详解 mysql 存储过程
- MySQL存储过程详解 mysql 存储过程
- MySQL存储过程详解 mysql 存储过程
- MySQL存储过程详解 mysql 存储过程
- MySQL存储过程详解 mysql 存储过程
- MySQL存储过程详解 mysql 存储过程
- MySQL存储过程详解 mysql 存储过程
- MySQL存储过程详解 mysql 存储过程
- MySQL存储过程详解 mysql 存储过程
- MySQL存储过程详解 mysql 存储过程
- thingking in java test2.11练习(8)
- 杭电1113
- nyist 12 喷水装置(二)
- android图片缓存
- 2016微软探星夏令营在线技术笔试题解(2)
- MySQL存储过程
- 流媒体服务器原理和架构解析
- Linux系统奇怪的磁盘占用解决记录
- 微信公众平台在验证接口配置信息时无法通过
- @Value取不到值
- Linux KVM IO设备虚拟化的三种方式
- 【HDU5729 2016 Multi-University Training Contest 1G】【状压DP 容斥】Rigid Frameworks 刚性图 nm二分图的联通方案数
- 修改虚机管理员密码-Windows篇
- ces PS1SHELL