MYSQL存储过程总结.doc

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+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、ELSE、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. SET 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: 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 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+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_sales 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 done 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. 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, date_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_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.DROP 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_minion_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. DECLARE 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. 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_salary(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_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，存储方法的参数列表只允许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. RETURNS 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 | 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. (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 discount_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 sales 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.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 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 COMPLETION 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常见的工作是创建表、插入数据、删除数据、清空表、删除表。为了避免命名规范带来的不便，最好让事件名称具有描述整个事件的能力。建议命名规则如下为：动作名称_（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时间戳，用来完成重复的计划任务。在两种计划任务中，时间戳可以是任意的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 COMPLETION参数表示当这个事件不会再发生的时候，即当单次计划任务执行完毕后或当重复性的计划任务执行到了ENDS阶段。而PRESERVE的作用是使事件在执行完毕后不会被Drop掉，建议使用该参数，以便于查看EVENT具体信息。标注5：ENABLE | DISABLE参数Enable和Disable表示设定事件的状态。Enable表示系统将执行这个事件。Disable表示系统不执行该事件。可以用如下命令关闭或开启事件：ALTER EVENT event_name ENABLE/DISABLE标注6：COMMENT comment 注释会出现在元数据中，它存储在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语句是有限制的，对它的限制跟函数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 (没有被执行)Then:建立一个新的线程传递事件的SQL语句给新的线程(该线程在执行完毕后会自动关闭)4.4 修改事件使用ALTER EVENT 来修改事件，具体的ALTER语法如下，与创建事件的语法类似：ALTER EVENTevent_nameON SCHEDULE scheduleRENAME TO new_event_nameON COMPLETION NOT PRESERVEENABLE | DISABLECOMMENT commentDO sql_statement 4.5 删除事件EVENT使用DROP EVENT语句来删除已经创建的事件，语法如下：DROP EVENTIF EXISTSevent_name 但当一个事件正在运行中时，删除该事件不会导致事件停止，事件会执行到完毕为止。使用DROP USER和DROP DATABASE 语句同时会将包含其中的事件删除。存储过程中的注释/* 多行注释*/- 单行注释分隔符这里需要注意的是DELIMITER /和DELIMITER ;两句，DELIMITER是分割符的意思，因为MySQL默认以;为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将;当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原MySQL存储过程管理MySQL存储过程的查询我们像知道一个数据库下面有那些表，我们一般采用show tables;进行查看。那么我们要查看某个数据库下面的存储过程，是否也可以采用呢？答案是，我们可以查看某个数据库下面的存储过程，但是是令一钟方式。我们可以用select name from mysql.proc where db=数据库名;或者select routine_name from information_schema.routines where routine_schema=数据库名;或者show procedure status where db=数据库名;进行查询。如果我们想知道，某个存储过程的详细，那我们又该怎么做呢？是不是也可以像操作表一样用describe 表名进行查看呢？答案是：我们可以查看存储过程的详细，但是需要用另一种方法：SHOW CREATE PROCEDURE 数据库.存储过程名;就可以查看当前存储过程的详细。 MySQL存储过程的修改ALTER PROCEDURE更改用CREATE PROCEDURE 建立的预先指定的存储过程，其不会影响相关存储过程或存储功能。 MySQL存储过程的删除删除一个存储过程比较简单，和删除表一样：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 (file_name ) /从文件读取内容LOCATE (substring , 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 (string2 ) /去除后端空格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 select 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_number ) /十进制转二进制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)默认变为整形值 mysql select round(1.23); +-+ | round(1.23) | +-+ | 1 | +-+ 1 row in set (0.00 sec) mysql select round(1.56); +-+ | round(1.56) | +-+ | 2 | +-+ 1 row in set (0.00 sec) (2)可以设定小数位数，返回浮点型数据 mysql select round(1.567,2); +-+ | round(1.567,2) | +-+ | 1.57 | +-+ 1 row in set (0.00 sec) SIGN (number2 ) /(3).日期时间类ADDTIME (date2 ,time_interval ) /将time_interval加到date2CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) /转换时区CURRENT_DATE ( ) /当前日期CURRENT_TIME ( ) /当前时间CURRENT_TIMESTAMP ( ) /当前时间戳DATE (datetime ) /返回datetime的日期部分DATE_ADD (date2 , INTERVAL d_value d_type ) /在date2中加上日期或时间DATE_FORMAT (datetime ,FormatCodes ) /使用formatcodes格式显示datetimeDATE_SUB (date2 , INTERVAL d_value d_type ) /在date2上减去一个时间DATEDIFF (date1 ,date2 ) /两个日期差DAY (date ) /返回日期的天DAYNAME (date ) /英文星期DAYOFWEEK (date ) /星期(1-7) ,1为星期天DAYOFYEAR (date ) /一年中的第几天EXTRACT (interval_name FROM date ) /从date中提取日期的指定部分MAKEDATE (year ,day ) /给出年及年中的第几天,生成日期串MAKETIME (hour ,minute ,second ) /生成时间串MONTHNAME (date ) /英文月份名NOW ( ) /当前时间SEC_TO_TIME (seconds ) /秒数转成时间STR_TO_DATE (string ,format ) /字串转成时间,以format格式显示TIMEDIFF (datetime1 ,datetime2 ) /两个时间差TIME_TO_SEC (time ) /时间转秒数WEEK (date_time ,start_of_week ) /第几周YEAR (datetime ) /年份DAYOFMONTH(datetime) /月的第几天HOUR(datetime) /小时LAST_DAY(date) /date的月的最后日期MICROSECOND(datetime) /微秒MONTH(datetime) /月MINUTE(datetime) /分返回符号,正负或0SQRT(number2) /开平方