JDBC
JDBC调用MySQL存储过程
What 存储过程
SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。
存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式。
存储过程是数据库的一个重要的功能，MySQL 5.0以前并不支持存储过程，这使得MySQL在应用上大打折扣。
lMySQL5.0开始支持存储过程，这样即可以大大提高数据库的处理速度，同时也可以提高数据库编程的灵活性。
why 存储过程
有输入输出参数，可以声明变量，有if/else, case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能；
函数的普遍特性：模块化，分封装，代码复用；
速度快，只有首次执行需要经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤；
l增强SQL语言的功能和灵活性：存储过程可以用控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
l标准组件式编程：存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。
l较快的执行速度：如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。
l减少网络流量：针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织进存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少网络流量并降低了网络负载。
l
l作为一种安全机制来充分利用：通过对执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。
how 存储过程语法
语法
CREATEPROCEDURE 过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...] 过程体
DELIMITER
//
CREATE PROCEDURE myproc(OUT s int)
BEGIN
SELECT COUNT(*) INTO s FROM students;
END
//
DELIMITER ;
分隔符
MySQL默认以";"为分隔符，如果没有声明分割符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错，所以要事先用“DELIMITER //”声明当前段分隔符，让编译器把两个"//"之间的内容当做存储过程的代码，不会执行这些代码；“DELIMITER ;”的意为把分隔符还原。
参数
存储过程根据需要可能会有输入、 输出、输入输出参数，如果有多个参数用","分割开。MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT:
IN参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认
OUT:该值可在存储过程内部被改变，并可返回 INOUT:调用时指定，并且可被改变和返回 过程体
过程体的开始与结束使用BEGIN与END进行标识。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| 1823ja |
| b |
| bank |
| bank1 |
| blog |
| books |
| c |
| db2 |
| easybuy |
| lob |
| lob2 |
| mybatis |
| mysql |
| performance_schema |
| procedure1 |
| procedure2 |
| qjh |
| redis |
| redpackagemgr |
| smbms |
| student |
| test |
+--------------------+
23 rows in set (0.00 sec)
mysql> use procedure2
Database changed
mysql> clear
mysql> delimiter //
mysql> create procedure myproc2(OUT num int)
-> BEGIN
-> SELECT COUNT(1) INTO num FROM students;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
Microsoft Windows [版本 10.0.17134.1130]
(c) 2018 Microsoft Corporation。保留所有权利。
C:\Users\80468>mysql -u root -p\
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
C:\Users\80468>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.5.47 MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> Ctrl-C -- exit!
Bye
C:\Users\80468>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.5.47 MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> clear
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| 1823ja |
| b |
| bank |
| bank1 |
| blog |
| books |
| c |
| db2 |
| easybuy |
| lob |
| lob2 |
| mybatis |
| mysql |
| performance_schema |
| procedure1 |
| procedure2 |
| qjh |
| redis |
| redpackagemgr |
| smbms |
| student |
| test |
+--------------------+
23 rows in set (0.00 sec)
mysql> use procedure2;
Database changed
mysql> delimiter //
mysql> create procedure out_param(OUT p_out INT)
-> BEGIN
-> SELECT p_out;
-> set p_out=2;
-> select p_out;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> set @p_out=1;
-> delimiter ;
-> Ctrl-C -- exit!
Bye
C:\Users\80468>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.5.47 MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show procedures status;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'procedures status' at line 1
mysql> use procedure2;
Database changed
mysql> set @p_out=1;
Query OK, 0 rows affected (0.00 sec)
mysql> call out_param(@p_out);
+-------+
| p_out |
+-------+
| NULL |
+-------+
1 row in set (0.00 sec)
+-------+
| p_out |
+-------+
| 2 |
+-------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> select @p_out;
+--------+
| @p_out |
+--------+
| 2 |
+--------+
1 row in set (0.00 sec)
mysql> delimiter ;
mysql>
IN参数例子
DELIMITER // CREATE PROCEDURE in_param(IN p_in int) BEGIN SELECT p_in; SET p_in=2; SELECT p_in; END; // DELIMITER ;
#调用 SET @p_in=1; CALL in_param(@p_in);SELECT @p_in;
OUT参数例子#存储过程OUT参数 DELIMITER // CREATE PROCEDURE out_param(OUT p_out int) BEGIN SELECT p_out; SET p_out=2; SELECT p_out; END; // DELIMITER ; #调用 SET @p_out=1; CALL out_param(@p_out);SELECT @p_out;
INOUT参数例子#存储过程INOUT参数 DELIMITER // CREATE PROCEDURE inout_param(INOUT p_inout int) BEGIN SELECT p_inout; SET p_inout=2; SELECT p_inout; END; // DELIMITER ; #调用 SET @p_inout=1; CALL inout_param(@p_inout) ; SELECT @p_inout;
变量赋值
语法：SET 变量名 = 变量值 [,变量名= 变量值 ...]
用户变量
用户变量一般以@开头
注意：滥用用户变量会导致程序难以理解及管理
#在MySQL客户端使用用户变量 SELECT 'Hello World' into @x; SELECT @x;
SET @y='Goodbye Cruel World'; SELECT @y; SET @z=1+2+3; SELECT @z;
MySQL存储过程的删除
DROP PROCEDURE [过程1[,过程2…]]从MySQL的表格中删除一个或多个存储过程。
内部变量在其作用域范围内享有更高的优先权，当执行到end时，内部变量消失，
不再可见了，在存储过程外再也找不到这个内部变量，但是可以通过out参数
或者将其值指派给会话变量来保存其值。
变量作用域
DELIMITER //
CREATE PROCEDURE proc()
BEGIN
DECLARE x1 VARCHAR(5) DEFAULT 'outer';
BEGIN
DECLARE x1 VARCHAR(5) DEFAULT 'inner';
SELECT x1;
END;
SELECT x1;
END;
//
DELIMITER ;
调用
CALL proc();执行结果：
LABLES标号标号可以用在begin repeat while 或者loop 语句前，
语句标号只能在合法的语句前面使用。可以跳出循环，
使运行指令达到复合语句的最后一步。
ITERATE迭代
通过引用复合语句的标号,来重新开始复合语句
MySQL存储过程的基本函数字符串类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
数学类
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为小数位数] 注：
返回类型并非均为整数，如：
日期时间类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) //开平方
第20章：存储程序和函数
目录
20.1. 存储程序和授权表
20.2. 存储程序的语法
20.2.1. CREATE PROCEDURE和CREATE FUNCTION
20.2.2. ALTER PROCEDURE和ALTER FUNCTION
20.2.3. DROP PROCEDURE和和DROP FUNCTION
20.2.4. SHOW CREATE PROCEDURE和SHOW CREATE FUNCTION
20.2.5. SHOW PROCEDURE STATUS和SHOW FUNCTION STATUS
20.2.6. CALL语句
20.2.7. BEGIN ... END复合语句
20.2.8. DECLARE语句
20.2.9. 存储程序中的变量
20.2.10. 条件和处理程序
20.2.11. 光标
20.2.12. 流程控制构造
20.3. 存储程序、函数、触发程序和复制：常见问题
20.4. 存储子程序和触发程序的二进制日志功能
MySQL 5.1版支持存储程序和函数。一个存储程序是可以被存储在服务器中的一套SQL语句。一旦它被存储了，客户端不需要再重新发布单独的语句，而是可以引用存储程序来替代。
下面一些情况下存储程序尤其有用：
· 当用不同语言编写多客户应用程序，或多客户应用程序在不同平台上运行且需要执行相同的数据库操作之时。
· 安全极为重要之时。比如，银行对所有普通操作使用存储程序。这提供一个坚固而安全的环境，程序可以确保每一个操作都被妥善记入日志。在这样一个设置中，应用程序和用户不可能直接访问数据库表，但是仅可以执行指定的存储程序。
存储程序可以提供改良后的性能，因为只有较少的信息需要在服务器和客户算之间传送。代价是增加数据库服务器系统的负荷，因为更多的工作在服务器这边完成，更少的在客户端（应用程序）那边完成上。如果许多客户端机器（比如网页服务器）只由一个或少数几个数据库服务器提供服务，可以考虑一下存储程序。
存储程序也允许你在数据库服务器上有函数库。这是一个被现代应用程序语言共享的特征，它允许这样的内部设计，比如通过使用类。使用这些客户端应用程序语言特征对甚至于数据库使用范围以外的编程人员都有好处。
MySQL为存储程序遵循SQL:2003语法，这个语法也被用在IBM的DB2数据库上。
MySQL对存储程序的实现还在进度中。所有本章叙述的语法都被支持，在有限制或扩展的地方会恰当地指出来。有关使用存储程序的限制的更多讨论在附录 I, 特性限制里提到。
如20.4节，“存储子程序和触发程序的二进制日志功能”里所说的，存储子程序的二进制日志功能已经完成。
20.1. 存储程序和授权表
存储程序需要在mysql数据库中有proc表。这个表在MySQL 5.1安装过程中创建。如果你从早期的版本升级到MySQL 5.1 ，请确定更新你的授权表以确保proc表的存在。请参阅2.10.2节 “升级授权表”。
在MySQL 5.1中，授权系统如下考虑存储子程序：
· 创建存储子程序需要CREATE ROUTINE权限。
· 提醒或移除存储子程序需要ALTER ROUTINE权限。这个权限自动授予子程序的创建者。
· 执行子程序需要EXECUTE权限。然而，这个权限自动授予子程序的创建者。同样，子程序默认的SQL SECURITY 特征是DEFINER，它允许用该子程序访问数据库的用户与执行子程序联系到一起。
20.2. 存储程序的语法
20.2.1. CREATE PROCEDURE和CREATE FUNCTION
20.2.2. ALTER PROCEDURE和ALTER FUNCTION
20.2.3. DROP PROCEDURE和DROP FUNCTION
20.2.4. SHOW CREATE PROCEDURE和SHOW CREATE FUNCTION
20.2.5. SHOW PROCEDURE STATUS和SHOW FUNCTION STATUS
20.2.6. CALL语句
20.2.7. BEGIN ... END复合语句
20.2.8. DECLARE语句
20.2.9. 存储程序中的变量
20.2.10. 条件和处理程序
20.2.11. 光标
20.2.12. 流程控制构造
存储程序和函数是用CREATE PROCEDURE和CREATE FUNCTION语句创建的子程序。一个子程序要么是一个程序要么是一个函数。使用CALL语句来调用程序，程序只能用输出变量传回值。就像别其它函数调用一样，函数可以被从语句外调用（即通过引用函数名），函数能返回标量值。存储子程序也可以调用其它存储子程序。
在MySQL 5.1中，一个存储子程序或函数与特定的数据库相联系。这里有几个意思：
· 当一个子程序被调用时，一个隐含的USE db_name 被执行（当子程序终止时停止执行）。存储子程序内的USE语句时不允许的。
· 你可以使用数据库名限定子程序名。这可以被用来引用一个不在当前数据库中的子程序。比如，要引用一个与test数据库关联的存储程序p或函数f，你可以说CALL test.p()或test.f()。
· 数据库移除的时候，与它关联的所有存储子程序也都被移除。
MySQL 支持非常有用的扩展，即它允许在存储程序中使用常规的SELECT语句（那就是说，不使用光标或局部变量）。这个一个查询的结果包被简单地直接送到客户端。多SELECT语句生成多个结果包，所以客户端必须使用支持多结果包的MySQL客户端库。这意味这客户端必须使用至少MySQL 4.1以来的近期版本上的客户端库。
下面一节描述用来创建，改变，移除和查询存储程序和函数的语法。
JDBC：
what:J D B C JAVA DATABASE Connectivity
java 数据库连接(接口)
how
1.加载驱动**
2.设置三个参数,且获取连接对象
3.获取执行sql语句对象 PreparedStatement pstm
4.执行sql语句,且获取返回值类型
5.释放资源 public static void close(Connection conn,PreparedStatement pstm, ResultSet rs){}
心得：从某种意义上讲，学会学习比学会知识更重要，因为，学会学习，就有了用之不竭的知识。
学习必须循序渐进。学习任何知识，必须注重基本训练，要一步一个脚印，由易到难，扎扎实实地练好基本功，切忌好高鹜远，前面的内容没有学懂，就急着去学习后面的'知识；基本的习题没有做好，就一味去钻偏题、难题。这是十分有害的。
学习必须勤于思考。Java是一个重要的学习阶段。在这个期间要注意培养独立思考的能力。要防止那种死记硬背，不求甚解的倾向。学习中要多问几个为什么。一个问题可以从几个不同的方面去思考，做到举一反三，融会贯通。
学习必须一丝不苟。学习切忌似懂非懂。例如，习题做错了，这是常有的事，重要的是能自己发现错误并改正它。要在初中乃至小学学习阶段就要培养这种本领。这就要求我们对解题中的每一步推导能说出正确的理由，每一步都要有根据，不能想当然，马马虎虎。
学习必须善于总结。学完一章，要做个小结；学完一本书。要做个总结。总结很重要，不同的学科总结方法不尽相同。常做总结可帮助你进一步理解所学的知识，形成较完整的知识框架。
学习必须持之以恒。俗话说“水滴石穿”、“一口吃不成胖子”。因此，最好制定一个学习计划，常常自我监督，严格要求，每天或分阶段自己或让父母检查，是否完成了学习计划，为什么没有完成，怎样补救等等。总之，学习不能只凭热情，三日打鱼，两日晒网是做不成大事的。
学习方法，要因人而异、因学科而异，正如医生用药，不能千人一方。同学们应当从实际出发，根据自己的情况，发挥特长，摸索适合自己特点的有效方法。
