Java之MySQL 存储过程【整理】

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

MySQL存储过程创建的格式：
create procedure 存储过程名字 ( [in|out|inout] 参数 datatype  )
begin
   MySQL 语句;
end;

MySQL 存储过程参数如果不显式指定“in”、“out”、“inout”，则默认为“in”。
习惯上，对于是“in” 的参数，我们都不会显式指定。

1. MySQL 存储过程名字后面的“()”是必须的，即使没有一个参数，也需要“()”
2. MySQL 存储过程参数，不能在参数名称前加“@”，
3. MySQL 存储过程的参数不能指定默认值。
4. MySQL 存储过程不需要在 procedure body 前面加 “as”。（ SQL Server 存储过程必须加 “as” 关键字）
5. 如果 MySQL 存储过程中包含多条 MySQL 语句，则需要 begin end 关键字。
6. MySQL 存储过程中的每条语句的末尾，都要加上分号 “;”
   declare c int;
   if a is null then
      set a = 0;
   end if;
7. MySQL存储过程中的注释。单行注释：--    多行注释：/*   */
8. 不能在 MySQL 存储过程中使用 “return” 关键字。
   set c = a + b;
   select c as sum;
   /* return c;- 不能在 MySQL 存储过程中使用。return 只能出现在函数中。/
9. 调用 MySQL 存储过程时候，需要在过程名字后面加“()”，即使没有一个参数，也需要“()”
   call pr_no_param();
10. 因为 MySQL 存储过程参数没有默认值，所以在调用 MySQL 存储过程时候，不能省略参数。可以用 null 来替代。
   call pr_add(10, null);

变量 
使用DECLARE来声明，DEFAULT赋默认值，SET赋值 
DECLARE variable_name [,variable_name...] datatype [DEFAULT value];
DECLARE dt datetime DEFAULT '1999-12-31 23:59:59';

SET 变量名 = 表达式值 [,variable_name = expression ...]
DECLARE counter INT DEFAULT 0;   
SET counter = counter+1;  

用户变量名一般以@开头

参数 
IN为默认类型，值必须在调用时指定，值不能返回（值传递） 
OUT值可以返回（指针传递） 
INOUT值必须在调用时指定，值可以返回 
CREATE PROCEDURE test(a INT, OUT b FLOAT, INOUT c INT)  

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 ;  

LOOP、END LOOP 
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   

WHILE DO、END WHILE 
CREATE PROCEDURE simple_while(OUT counter INT)   
BEGIN   
    SET counter = 0;   
    WHILE counter != 10 DO   
        SET counter = counter+1;   
    END WHILE;   
END 

REPEAT、UNTILL 
CREATE PROCEDURE simple_repeat(OUT counter INT)   
BEGIN   
    SET counter = 0;   
    REPEAT   
        SET counter = counter+1;   
    UNTIL counter = 10 END REPEAT;   
END

case when end case语句
CREATE PROCEDURE proc3 (in parameter int)  
begin 
declare var int;  
set var=parameter+1;  
case var  
when 0 then   
insert into t values(17);  
when 1 then   
insert into t values(18);  
else   
insert into t values(19);  
end case;  
end;  

LABLES 标号：
标号可以用在begin repeat while 或者loop 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。

INTO用于存储单行记录的查询结果 
DECLARE total_sales NUMERIC(8, 2);   
SELECT SUM(sale_value) INTO total_sales FROM sales WHERE customer_id=in_customer_id;  

CURSOR用于处理多行记录的查询结果 
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

UPDATE、INSERT、DELETE、CREATE TABLE等非查询语句也可以嵌入存储过程里 
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   

使用CALL调用存储程序 
DROP PROCEDURE IF EXISTS call_example   
CREATE PROCEDURE call_example(employee_id INT, employee_type VARCHAR(20))   
 
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

查看某个数据库下面的存储过程
select name from mysql.proc where db=’数据库名’;
查看存储过程的详细
SHOW CREATE PROCEDURE 数据库.存储过程名;
MySQL存储过程的修改
ALTER PROCEDURE、
MySQL存储过程的删除
删除一个存储过程比较简单，和删除表一样：
DROP PROCEDURE

MySQL存储过程的基本函数
(1).字符串类
CHARSET(str) //返回字串字符集
CONCAT (string2 [,... ]) //连接字串
INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0
LCASE (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开头,直到字串长度为length
LTRIM (string2 ) //去除前端空格
REPEAT (string2 ,count ) //重复count次
REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str
RPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为length
RTRIM (string2 ) //去除后端空格
STRCMP (string1 ,string2 ) //逐字符比较两字串大小,
SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符,
注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1 

TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
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)返回19
LEAST (number , number2 [,..]) //求最小值
MOD (numerator ,denominator ) //求余
POWER (number ,power ) //求指数
RAND([seed]) //随机数
ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]

(3).日期时间类
ADDTIME (date2 ,time_interval ) //将time_interval加到date2
CONVERT_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格式显示datetime
DATE_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) //分返回符号,正负或0
SQRT(number2) //开平方

触发器是一种特殊类型的存储过程，它不同于我们前面介绍过的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。当对某一表进行诸如Update、 Insert、 Delete 这些操作时，SQL Server 就会自动执行触发器所定义的SQL 语句，从而确保对数据的处理必须符合由这些SQL 语句所定义的规则。
触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。

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  

为了响应数据库更新,触发器可以调用一个或多个存储过程，甚至可以通过外部过程的调用而在DBMS(数据库管理系统)本身之外进行操作。
由此可见，触发器可以解决高级形式的业务规则或复杂行为限制以及实现定制记录等一些方面的问题。例如，触发器能够找出某一表在数据修改前后状态发生的差异，并根据这种差异执行一定的处理。此外一个表的同一类型(Insert、 Update、 Delete)的多个触发器能够对同一种数据操作采取多种不同的处理。
总体而言，触发器性能通常比较低。当运行触发器时，系统处理的大部分时间花费在参照其它表的这一处理上，因为这些表既不在内存中也不在数据库设备上，而删除表和插入表总是位于内存中。可见触发器所参照的其它表的位置决定了操作要花费的时间长短。