MYSQL存储过程总结

1、1，前提 需要MySQL 5 2，Hello World MySQL存储过程之Hello World 案例DELIMITER $ 2. 3.DROP PROCEDURE IF EXISTS HelloWorld$ 4.CREATE PROCEDURE HelloWorld() 5.BEGIN 6. SELECT "Hello World!" 7.END$ 8. 9.DELIMITER ; 3，变量 使用DECLARE来声明，DEFAULT赋默认值，SET赋值 案例1.DECLARE counter INT DEFAULT 0; 2.SET counter = counter

2、+1; SELECT INTO查询结果赋值 案例1.DECLARE total_sales NUMERIC(8, 2); 2.SELECT SUM(sale_value) INTO total_sales FROM sales WHERE customer_id=in_customer_id; 4，参数 IN为默认类型，值必须在调用时指定，值不能返回（值传递） OUT值可以返回（指针传递） INOUT值必须在调用时指定，值可以返回 案例1.CREATE PROCEDURE test(a INT, OUT b FLOAT, INOUT c INT) 5，条件判断 IF THEN、ELSEIF、E

3、LSE、END IF 案例1.DELIMITER $ 2. 3.DROP PROCEDURE IF EXISTS discounted_price$ 4.CREATE PROCEDURE discunted_price(normal_price NUMERIC(8, 2), OUT discount_price NUMERIC(8, 2) 5.BEGIN 6. IF (normal_price > 500) THEN 7. SET discount_price = normal_price * .8; 8. ELSEIF (normal_price > 100) THEN 9. S

4、ET discount_price = normal_price * .9; 10. ELSE 11. SET discount_price = normal_price; 12. END IF; 13.END$ 14. 15.DELIMITER ; 6，循环 LOOP、END LOOP 案例1.DELIMITER $ 2. 3.DROP PROCEDURE IF EXISTS simple_loop$ 4. 5.CREATE PROCEDURE simple_loop(OUT counter INT) 6.BEGIN 7. SET counter = 0; 8. my_simple_loop

5、: LOOP 9. SET counter = counter+1; 10. IF counter = 10 THEN 11. LEAVE my_simple_loop; 12. END IF; 13. END LOOP my_simple_loop; 14.END$ 15. 16.DELIMITER ; WHILE DO、END WHILE 案例1.DELIMITER $ 2. 3.DROP PROCEDURE IF EXISTS simple_while$ 4. 5.CREATE PROCEDURE simple_while(OUT counter INT) 6.BEGIN 7. SET

6、counter = 0; 8. WHILE counter != 10 DO 9. SET counter = counter+1; 10. END WHILE; 11.END$ 12. 13.DELIMITER ; REPEAT、UNTILL 案例1.DELIMITER $ 2. 3.DROP PROCEDURE IF EXISTS simple_repeat$ 4. 5.CREATE PROCEDURE simple_repeat(OUT counter INT) 6.BEGIN 7. SET counter = 0; 8. REPEAT 9. SET counter = counter+

7、1; 10. UNTIL counter = 10 END REPEAT; 11.END$ 12. 13.DELIMITER ; 7，异常处理 如果用cursor获取SELECT语句返回的所有结果集时应该定义NOT FOUND error handler来防止存储程序提前终结 如果SQL语句可能返回constraint violation等错误时应该创建一个handler来防止程序终结 8，数据库交互 INTO用于存储单行记录的查询结果 案例1.DECLARE total_sales NUMERIC(8, 2); 2.SELECT SUM(sale_value) INTO total_sale

8、s FROM sales WHERE customer_id=in_customer_id; CURSOR用于处理多行记录的查询结果 案例1.DELIMITER $ 2. 3.DROP PROCEDURE IF EXITS cursor_example$ 4.CREATE PROCEDURE cursor_example() 5. READS SQL DATA 6.BEGIN 7. DECLARE l_employee_id INT; 8. DECLARE l_salary NUMERIC(8,2); 9. DECLARE l_department_id INT; 10. DECLARE do

9、ne INT DEFAULT 0; 11. DECLARE cur1 CURSOR FOR SELECT employee_id, salary, department_id FROM employees; 12. DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; 13. 14. OPEN cur1; 15. emp_loop: LOOP 16. FETCH cur1 INTO l_employee_id, l_salary, l_department_id; 17. IF done=1 THEN 18. LEAVE emp_loop; 19

10、. END IF; 20. END LOOP emp_loop; 21. CLOSE cur1; 22.END$ 23.DELIMITER ; unbounded SELECT语句用于存储过程返回结果集 案例1.DELIMITER $ 2.DROP PROCEDURE IF EXISTS sp_emps_in_dept$ 3.CREATE PROCEDURE sp_emps_in_dept(in_employee_id INT) 4.BEGIN 5. SELECT employee_id, surname, firstname, address1, address2, zipcode, dat

11、e_of_birth FROM employees WHERE department_id=in_employee_id; 6.END$ 7. 8.DELIMITER ; UPDATE、INSERT、DELETE、CREATE TABLE等非查询语句也可以嵌入存储过程里 案例1.DELIMITER $ 2. 3.DROP PROCEDURE IF EXITS sp_update_salary$ 4.CREATE PROCEDURE sp_update_salary(in_employee_id INT, in_new_salary NUMERIC(8,2) 5.BEGIN 6. IF in_n

12、ew_salary < 5000 OR in_new_salary > 500000 THEN 7. SELECT "Illegal salary: salary must be between $5000 and $500, 000" 8. ELSE 9. UPDATE employees SET salary=in_new_salary WHERE employee_id=in_employee_id; 10. END IF: 11.END$ 12. 13.DELIMITER ; 9，使用CALL调用存储程序 案例1.DELIMITER $ 2. 3.DRO

13、P PROCEDURE IF EXISTS call_example$ 4.CREATE PROCEDURE call_example(employee_id INT, employee_type VARCHAR(20) 5. NO SQL 6.BEGIN 7. DECLARE l_bonus_amount NUMERIC(8,2); 8. 9. IF employee_type='MANAGER' THEN 10. CALL calc_manager_bonus(employee_id, l_bonus_amount); 11. ELSE 12. CALL calc_mini

14、on_bonus(employee_id, l_bonus_amount); 13. END IF; 14. CALL grant_bonus(employee_id, l_bonus_amount); 15.END$ 16.DELIMITER ; 10，一个复杂的例子 案例1.CREATE PROCEDURE putting_it_all_together(in_department_id INT) 2. MODIFIES SQL DATA 3.BEGIN 4. DECLARE l_employee_id INT; 5. DECLARE l_salary NUMERIC(8,2); 6. D

15、ECLARE l_department_id INT; 7. DECLARE l_new_salary NUMERIC(8,2); 8. DECLARE done INT DEFAULT 0; 9. 10. DECLARE cur1 CURSOR FOR 11. SELECT employee_id, salary, department_id 12. FROM employees 13. WHERE department_id=in_department_id; 14. 15. DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; 16. 17

16、. CREATE TEMPORARY TABLE IF NOT EXISTS emp_raises 18. (employee_id INT, department_id INT, new_salary NUMERIC(8,2); 19. 20. OPEN cur1; 21. emp_loop: LOOP 22. FETCH cur1 INTO l_employee_id, l_salary, l_department_id; 23. IF done=1 THEN /* No more rows */ 24. LEAVE emp_loop; 25. END IF; 26. CALL new_s

17、alary(1_employee_id, l_new_salary); /* Get new salary */ 27. IF (l_new_salary <> l_salary) THEN /* Salary changed */ 28. UPDATE employees 29. SET salary=l_new_salary 30. WHERE employee_id=l_employee_id; 31. /* Keep track of changed salaries */ 32. INSERT INTO emp_raises(employee_id, department

18、_id, new_salary) 33. VALUES (l_employee_id, l_department_id, l_new_salary); 34. END IF: 35. END LOOP emp_loop; 36. CLOSE cur1; 37. /* Print out the changed salaries */ 38. SELECT employee_id, department_id, new_salary from emp_raises 39. ORDER BY employee_id; 40.END; 11，存储方法 存储方法与存储过程的区别 1，存储方法的参数列表

19、只允许IN类型的参数，而且没必要也不允许指定IN关键字 2，存储方法返回一个单一的值，值的类型在存储方法的头部定义 3，存储方法可以在SQL语句内部调用 4，存储方法不能返回结果集 语法： 案例1.CREATE 2. DEFINER = user | CURRENT_USER 3. PROCEDURE sp_name (proc_parameter,.) 4. characteristic . routine_body 5. 6.CREATE 7. DEFINER = user | CURRENT_USER 8. FUNCTION sp_name (func_parameter,.) 9. R

20、ETURNS type 10. characteristic . routine_body 11. 12.proc_parameter: 13. IN | OUT | INOUT param_name type 14. 15.func_parameter: 16. param_name type 17. 18.type: 19. Any valid MySQL data type 20. 21.characteristic: 22. LANGUAGE SQL 23. | NOT DETERMINISTIC 24. | CONTAINS SQL | NO SQL | READS SQL DATA

21、 | MODIFIES SQL DATA 25. | SQL SECURITY DEFINER | INVOKER 26. | COMMENT 'string' 27. 28.routine_body: 29. Valid SQL procedure statement 各参数说明见CREATE PROCEDURE and CREATE FUNCTION Syntax 例子： 案例1.DELIMITER $ 2. 3.DROP FUNCTION IF EXISTS f_discount_price$ 4.CREATE FUNCTION f_discount_price 5. (

22、normal_price NUMERIC(8,2) 6. RETURNS NUMERIC(8,2) 7. DETERMINISTIC 8.BEGIN 9. DECLARE discount_price NUMERIC(8,2); 10. 11. IF (normal_price > 500) THEN 12. SET discount_price = normal_price * .8; 13. ELSEIF (normal_price >100) THEN 14. SET discount_price = normal_price * .9; 15. ELSE 16. SET d

23、iscount_price = normal_price; 17. END IF; 18. 19. RETURN(discount_price); 20.END$ 21. 22.DELIMITER ; 12，触发器 触发器在INSERT、UPDATE或DELETE等DML语句修改数据库表时触发 触发器的典型应用场景是重要的业务逻辑、提高性能、监控表的修改等 触发器可以在DML语句执行前或后触发 案例1.DELIMITER $ 2. 3.DROP TRIGGER sales_trigger$ 4.CREATE TRIGGER sales_trigger 5. BEFORE INSERT ON s

24、ales 6. FOR EACH ROW 7.BEGIN 8. IF NEW.sale_value > 500 THEN 9. SET NEW.free_shipping = 'Y' 10. ELSE 11. SET NEW.free_shipping = 'N' 12. END IF; 13. 14. IF NEW.sale_value > 1000 THEN 15. SET NEW.discount = NEW.sale_value * .15; 16. ELSE 17. SET NEW.discount = 0; 18. END IF; 19.

25、END$ 20. 21.DELIMITER ; 13，定时任务- 查看是否开启定时器show variables like '%sche%' SHOW VARIABLES LIKE 'event_scheduler'SELECT event_scheduler;开启event_scheduler，也可以设置为on set global event_scheduler =1; alter event evt_test ON COMPLETION PRESERVE DISABLE;alter event evt_test ON COMPLETION PRESERVE

26、 ENABLE;CCREATE DEFINER=rootlocalhost EVENT evt_test ON SCHEDULE EVERY 100 SECOND STARTS '2014-10-25 00:00:00' ON COMPLETION PRESERVE ENABLE DO insert into tt(gmt_create) values (now()4.2 创建事件CREATE EVENT 的语法如下：CREATE EVENTIF NOT EXISTS -*标注1event_name -*标注2ON SCHEDULE schedule -*标注3ON COMPL

27、ETION NOT PRESERVE -*标注4ENABLE | DISABLE -*标注5COMMENT 'comment' -*标注6DO sql_statement -*标注7说明： 标注1：IF NOT EXISTS 使用IF NOT EXISTS，只有在同名event不存在时才创建，否则忽略。建议不使用以保证event创建成功。标注2：event_name 名称最大长度可以是64个字节。名字必须是当前Dateabase中唯一的，同一个数据库不能有同名的event。使用event常见的工作是创建表、插入数据、删除数据、清空表、删除表。为了避免命名规范带来的不便，最好让事

28、件名称具有描述整个事件的能力。建议命名规则如下为：动作名称_（INTO/FROM_）表名_TIME，例如：1. 每月创建（清空/删除）fans表： create(truncate/drop)_table_fans_month；2. 每天从fans表插入（删除）数据：insert(delete)_into(from)_fans_day；标注3：ON SCHEDULE ON SCHEDULE 计划任务，有两种设定计划任务的方式： 1. AT 时间戳，用来完成单次的计划任务。2. EVERY 时间（单位）的数量时间单位STARTS 时间戳 ENDS时间戳，用来完成重复的计划任务。在两种计划任务中，时

29、间戳可以是任意的TIMESTAMP 和DATETIME 数据类型，时间戳需要大于当前时间。在重复的计划任务中，时间（单位）的数量可以是任意非空（Not Null）的整数式，时间单位是关键词：YEAR，MONTH，DAY，HOUR，MINUTE 或者SECOND。提示: 其他的时间单位也是合法的如：QUARTER, WEEK, YEAR_MONTH,DAY_HOUR,DAY_MINUTE,DAY_SECOND,HOUR_MINUTE,HOUR_SECOND, MINUTE_SECOND，不建议使用这些不标准的时间单位。标注4： ON COMPLETION NOT PRESERVE ON COMP

30、LETION参数表示"当这个事件不会再发生的时候"，即当单次计划任务执行完毕后或当重复性的计划任务执行到了ENDS阶段。而PRESERVE的作用是使事件在执行完毕后不会被Drop掉，建议使用该参数，以便于查看EVENT具体信息。标注5：ENABLE | DISABLE参数Enable和Disable表示设定事件的状态。Enable表示系统将执行这个事件。Disable表示系统不执行该事件。可以用如下命令关闭或开启事件：ALTER EVENT event_name ENABLE/DISABLE标注6：COMMENT 'comment' 注释会出现在元数据中，它

31、存储在information_schema表的COMMENT列，最大长度为64个字节。'comment'表示将注释内容放在单引号之间，建议使用注释以表达更全面的信息。标注 7: DO sql_statement DO sql_statement字段表示该event需要执行的SQL语句或存储过程。这里的SQL语句可以是复合语句，例如：BEGINCREATE TABLE test1;/创建表（需要测试一下）DROP TABLE test2;/删除表CALL proc_test1();/调用存储过程END使用BEGIN和END标识符将复合SQL语句按照执行顺序放在之间。当然SQL语句

32、是有限制的，对它的限制跟函数Function和触发器Trigger 中对SQL语句的限制是一样的，如果你在函数Function 和触发器Trigger 中不能使用某些SQL，同样的在EVENT中也不能使用。明确的来说有下面几个：LOCK TABLESUNLOCK TABLESCREATE EVENTALTER EVENTLOAD DATA4.3 执行逻辑For (已建立事件each event that has been created)If (事件的状态非DISABLE)And (当前时间在ENDS时间之前)And (当前时间在STARTS时间之后)And (在上次执行后经过的时间)And

33、(没有被执行)Then:建立一个新的线程传递事件的SQL语句给新的线程(该线程在执行完毕后会自动关闭)4.4 修改事件使用ALTER EVENT 来修改事件，具体的ALTER语法如下，与创建事件的语法类似：ALTER EVENTevent_nameON SCHEDULE scheduleRENAME TO new_event_nameON COMPLETION NOT PRESERVEENABLE | DISABLECOMMENT 'comment'DO sql_statement 4.5 删除事件EVENT使用DROP EVENT语句来删除已经创建的事件，语法如下：DROP

34、EVENTIF EXISTSevent_name 但当一个事件正在运行中时，删除该事件不会导致事件停止，事件会执行到完毕为止。使用DROP USER和DROP DATABASE 语句同时会将包含其中的事件删除。存储过程中的注释/* 多行注释*/- 单行注释分隔符这里需要注意的是DELIMITER /和DELIMITER ;两句，DELIMITER是分割符的意思，因为MySQL默认以""为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将"

35、;"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原MySQL存储过程管理MySQL存储过程的查询我们像知道一个数据库下面有那些表，我们一般采用show tables;进行查看。那么我们要查看某个数据库下面的存储过程，是否也可以采用呢？答案是，我们可以查看某个数据库下面的存储过程，但是是令一钟方式。我们可以用select name from c where db='数据库名'或者select routine_name from information_schema.routines where routine_schema='

36、数据库名'或者show procedure status where db='数据库名'进行查询。如果我们想知道，某个存储过程的详细，那我们又该怎么做呢？是不是也可以像操作表一样用describe 表名进行查看呢？答案是：我们可以查看存储过程的详细，但是需要用另一种方法：SHOW CREATE PROCEDURE 数据库.存储过程名;就可以查看当前存储过程的详细。 MySQL存储过程的修改ALTER PROCEDURE更改用CREATE PROCEDURE 建立的预先指定的存储过程，其不会影响相关存储过程或存储功能。 MySQL存储过程的删除删除一个存储过程比较简单，和

37、删除表一样：DROP PROCEDURE从MySQL的表格中删除一个或多个存储过程。MySQL存储过程的基本函数(1).字符串类CHARSET(str) /返回字串字符集CONCAT (string2 ,. ) /连接字串INSTR (string ,substring ) /返回substring首次在string中出现的位置,不存在返回0LCASE (string2 ) /转换成小写LEFT (string2 ,length ) /从string2中的左边起取length个字符LENGTH (string ) /string长度LOAD_FILE ( ) /从文件读取内容LOCATE (s

38、ubstring , string ,start_position ) 同INSTR,但可指定开始位置LPAD (string2 ,length ,pad ) /重复用pad加在string开头,直到字串长度为lengthLTRIM (string2 ) /去除前端空格REPEAT (string2 ,count ) /重复count次REPLACE (str ,search_str ,replace_str ) /在str中用replace_str替换search_strRPAD (string2 ,length ,pad) /在str后用pad补充,直到长度为lengthRTRIM (st

39、ring2 ) /去除后端空格STRCMP (string1 ,string2 ) /逐字符比较两字串大小,SUBSTRING (str , position ,length ) /从str的position开始,取length个字符,注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1 mysql> select substring('abcd',0,2); +-+ | substring('abcd',0,2) | +-+ | | +-+ 1 row in set (0.00 sec) mysql> sele

40、ct substring('abcd',1,2); +-+ | substring('abcd',1,2) | +-+ | ab | +-+ 1 row in set (0.02 sec) TRIM(BOTH|LEADING|TRAILING padding FROMstring2) /去除指定位置的指定字符UCASE (string2 ) /转换成大写RIGHT(string2,length) /取string2最后length个字符SPACE(count) /生成count个空格(2).数学类ABS (number2 ) /绝对值BIN (decimal_n

41、umber ) /十进制转二进制CEILING (number2 ) /向上取整CONV(number2,from_base,to_base) /进制转换FLOOR (number2 ) /向下取整FORMAT (number,decimal_places ) /保留小数位数HEX (DecimalNumber ) /转十六进制注：HEX()中可传入字符串，则返回其ASC-11码，如HEX('DEF')返回4142143也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19LEAST (number , number2 ,.) /求最小值MOD (numerator ,denominator ) /求余POWER (number ,power ) /求指数RAND(seed) /随机数ROUND (number ,decimals ) /四舍五入,decimals为小数位数注：返回类型并非均为整数，如：(1)默认变