什么是存储过程
简单的说,就是一组SQL语句集,功能强大,可以实现一些比较复杂的逻辑功能,类似于JAVA语言中的方法;
有哪些特性
有输入输出参数,可以声明变量,有if/else, case,while等控制语句,通过编写存储过程,可以实现复杂的逻辑功能;
函数的普遍特性:模块化,封装,代码复用;
速度快,只有首次执行需经过编译和优化步骤,后续被调用可以直接执行,省去以上步骤;
我第一个存储过程
MyFirstProcedure.sql 简单的加法
DROP PROCEDURE IF EXISTS `proc_adder`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_adder`(IN a int, IN b int, OUT sum int)
BEGIN
#Routine body goes here...
DECLARE c int;
if a is null then set a = 0;
end if;
if b is null then set b = 0;
end if;
set sum = a + b;
END
;;
DELIMITER ;
set @a=2;
set @b=5;
call proc_adder(@a,@b,@s);--调用存储过程
select @s as sum;
其中需说明的是
DELIMITER ;;:之前说过了,把默认的输入的结束符;替换成;;。
DEFINER:创建者;
运行结果
创建 MySQL 存储过程的简单语法
create procedure 存储过程名字()
(
[in|out|inout] 参数 datatype
)
begin
MySQL 语句;
end;
IN为默认类型,值必须在调用时指定,值不能返回(值传递) ;
OUT值可以返回(指针传递) ;
INOUT值必须在调用时指定,值可以返回 ;
使用DECLARE来声明,DEFAULT赋默认值,SET赋值 ,例
1.DECLARE counter INT DEFAULT 0; /*默认为0*/
2.SET counter = counter+1; /*- 自增+1*/
注意
1.MySQL 存储过程注释格式为/*......*/,不能是//;
2.MySQL 存储过程中的每条语句的末尾,都要加上分号 “;” ;
3. 不能在 MySQL 存储过程中使用 “return” 关键字, “return” 只能出现在函数中;
4.因为 MySQL 存储过程参数没有默认值,所以在调用 MySQL 存储过程时候,不能省略参数。可以用 null 来替代。例:call pr_add(10, null);
5.MySQL5开始支持存储过程功能。
更多例子
1.discounted_price.sql
DELIMITER $$
DROP PROCEDURE IF EXISTS discounted_price$$
CREATE PROCEDURE discounted_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 ;
call discounted_price(300.02,@r);
select @r as result;
运行结果
注意:ELSEIF 不能写成ELSE IF
2.simple_loop.sql
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 ;
call simple_loop(@r);
select @r as result;
运行结果:
3.proc_case.sql
DROP PROCEDURE IF EXISTS `proc_case`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_case`(IN type int)
BEGIN
#Routine body goes here...
DECLARE c varchar(500);
CASE type
WHEN 0 THEN
set c = 'param is 0';
WHEN 1 THEN
set c = 'param is 1';
ELSE
set c = 'param is others, not 0 or 1';
END CASE;
select c;
END
;;
DELIMITER ;
set @type=1;
call proc_case(@type);
运行结果:
4.simple_while.sql
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 ;
call simple_while(@r);
select @r as result;
运行结果
5.simple_repeat.sql
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 ;
call simple_repeat(@r);
select @r as result;
运行结果:
6.simple_cursor_into.sql
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 ;
其中
1. 异常处理。如果用cursor获取SELECT语句返回的所有结果集时应该定义NOT FOUND error handler来防止存储程序提前终结 。如果SQL语句可能返回constraint violation等错误时应该创建一个handler来防止程序终结 ;
2. INTO用于存储单行记录的查询结果 ;
3.CURSOR用于处理多行记录的查询结果 ;
7. 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 ;
8. 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调用存储程序
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;
查看已有的存储过程
1.
select `name` from MySQL.proc where db = 'your_db_name' and `type` = 'PROCEDURE';
2.
show procedure status;
查看存储过程创建代码
show create procedure proc_name;
执行脚本文件的命令
source d:/a/b.sql;
不是
source d:\a\b.sql;
参考文献
1.Mysql中的存储过程[http://www.cnblogs.com/chenpi/p/5136483.html]
2.mysql存储过程 基本语法[http://www.cnblogs.com/lxs1314/p/5945428.html]
3.mysql查看所有存储过程[http://blog.csdn.net/god123209/article/details/7577138]