发表于： 2018-03-22 08:25:33
5 643
一、最近完成的事情
继续JDBC的学习，学习了数据库的存储过程和JDBC Statements
总结如下：写在了segmentfault里了。现在复制过来
## 数据库的存储过程总结：
在学习JDBC的时候，`CallableStatement`对象出现了一个数据库存储过程，对这一概念不是很了解。所以就查阅相关资料，总结一下
还没总结好，，，，发布保存一下。。。
##什么是存储过程？
根据百度百科的解释，存储过程是Store Procedure，是在大型数据库系统中，一组为了完成特定功能的SQL语句集，它存储在数据库中，经过一次编译后再次调用就不需要编译了。
笔者认为可以把它认为是一个语言的方法，它也有存储过程名、存储过程参数、返回值。
我们通过指定存储过程的名字并给出参数（如果带的话）来执行它。
## 存储过程的设计规则
首先，我们已经知道了，存储过程是一系列sql语句的集合。我们可以通过存储过程来创建其他数据库对象。同时也可以在存储过程中创建本地临时表，或者引用本地临时表。如果在存储过程内来创建本地临时表的话，临时表仅为存储过程而存在，退出该存储过程后，临时表将消失。
存储过程还可以调用另一个存储过程，被调用的存储过程可以访问访问
存储过程中的参数的最大数目为 2100。
存储过程中的局部变量的最大数目仅受可用内存的限制。
根据可用内存的不同，存储过程最大可达 128 MB
## 实现存储过程
```
CREATE PROCEDURE Procedure_Name
--Procedure_Name为存储过程名(不能以阿拉伯数字开头)，在一个数据库中触发器名是唯一的。名字的长度不能超过个字。PROCEDURE可以简写为PROC。
@Param1 Datatype,@Param2 Datatype
--@Param1和@Param2为存储过程的参数，Datatype为参数类型,多个参数用逗号隔开,最多允许个参数。
AS --存储过程要执行的操作
BEGIN
--BEGIN跟END组成一个代码块，可以写也可以不写，如果存储过程中执行的SQL语句比较复杂，用BEGIN和END会让代码更加整齐，更容易理解。
END
GO --GO就代表结操作完毕
exec Procedure_Name [参数名] --调用存储过程Procedure_Name。
drop procedure Procedure_Name --删除存储过程Procedure_Name，不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程
show procedure status --显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等
show create procedure Procedure_Name --显示存储过程Procedure_Name的详细信息
exec sp_helptext Procedure_Name --显示你这个Procedure_Name这个对象创建文本
```
更加详细的内容我们看下面：
```
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
```
#### 详细说明参数
1. procedure_name:存储过程的名称，前面加#为局部存储过程，加##全局存储过程。
2. number：可选的参数，用来对同名的过程分组，以便用一条`DROP PROCEDURE`语句即可将同组的过程一起去除。
如：名为orders的应用程序使用的过程可以名为`orderproc;1` 和 `orderproc;2`.使用`DROP PROCEDURE orderproc`语句将去除整个组。
3. @parameter：存储过程的参数。可以有一个或者多个。用户必须在执行过程中提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多有2100个参数。
对于参数命名的规则：要用@符号作为第一个字符，参数名必须符合标识符的规则。
4. data_type:参数的数据类型。所有数据类型（包括text、ntext、image）均可以用作存储过程的参数。不过cursor数据类型只能用于OUTPU参数。如果指定数据类型为cursor，同事也必须指定VARYING和OUTPUT关键字。
5.VARYING:指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标cursor参数
6. default：参数的默认值。如果定义了默认值，不必指定该参数的值就可以执行过程。默认值必须为常量或者是NULL。如果过程将该参数使用LIKE关键字，那么默认值中可以包含通配符（%、_ 、[]和[^])。
7. OUTPUT：表明参数是返回参数。该选项的值可以返回给EXEC[UTE]。使用OUTPUT参数可以将信息返回给调用过程。Text、ntext和image参数可以用作OUTPUT参数。
8. RECOMPILE:表明SQL Server不会缓存该过程的计划，该过程将在运行时重新编译，在使用非典型值或者临时值而不希望缓存在内存中执行计划时，请使用RECOMPILE 选项
9. ENCRYPTION：表示SQL Server 加密syscomments表中包含CREATE PROCEDURE语句文本的条目。使用ENCRYPTION可以防止将过程作为SQL Server赋值的一部分发布。说明在升级过程中，Sql Server利用存储在syscomments中的加密注释来重新创建加密过程。
10. FOR REPLICATION：指定不能在订阅服务器上执行为复制创建的存储过程。使用FOR REPLICATION选项创建的存储过程可用作存储过程筛选。
11. AS:指定过程要执行的操作
12. sql_statement:过程中要包含的任意数目和类型的Transact-SQL语句。但有一些限制。
## 实例操作
现有一个Student表。
下面是无参存储过程：
选出Student表中所有的信息：
```
create proc StuProc
as //此处as可省略不写
begin//begin和end是一对，不可以只写一个
select S#,Sname,Sage,Ssex from student
end
go
```
有参数的存储过程：
```
create proc StuProc
@sname varchar(100)
as
begin
select S#,Sname,Sage,Ssex from student where sname = @sname
end
go
exec StuProc '赵雷' //执行语句
```
上面是在外部给变量赋值，也可以直接在内部设置默认值
```
create proc StuProc
@sname varchar(100) = '赵雷'
as
begin
select S#,Sname,Ssex from student where sname = @sname
end
go
exec StuProc
```
也可以把变量的内容输出，使用output
```
create proc StuProc
@sname varchar(100),
@IsRight int output//传出参数
as
if exists(select s#,Sname,Sage,Ssex from student wheere sname = @sname)
set @IsRight = 1
else
set @IsRight = 0
go
declare @IsRight int
exec StuProc '赵雷',@IsRight output
select @IsRight
```
## 几个问题
问：存储过程在实际项目中用的多吗？
答：凡事都有利有弊，存储过程也是一样。在商业数据库应用中，例如金融、企业、政府等等，存储过程的使用非常广泛，有多方面的原因，例如：存储过程一旦调试完成通过后就能稳定运行，这与各个业务在一段时间内是相对稳定和确定是匹配的；存储过程大大地减少了业务系统与数据库的交互，一定程度降低了业务系统与数据库的耦合，例如即使业务系统与应用系统不在同一城市，对性能的影响也可控（100条SQL语句交互一次，即使延时由同城1ms增加到异地50ms，也只是增加49ms，如果交互100次，则增加4900ms）。在互联网行业，存储过程很少使用，一个重要的原因是MySQL的广泛使用，而MySQL的存储过程的功能很弱（跟商业数据库相比）；另外也跟互联网行业变化快有一定的关系。
问：存储过程到底有什么用？
答：优点是大数据量的情况下提高计算效率，缺点是存储过程与系统代码分离，有时可能随手一动存储过程，造成与代码的不一致，不好进行版本控制。
## 参考资料
[什么时候用存储过程---存储过程的好处](blog.csdn.net/guo00110211/article/details/30037313)
[详细全面解析sql存储过程](https://www.w3cschool.cn/sql/sql-storage.html)
JDBC的Statements
通过`DriverManager`的`Connection`，我们获得了与数据库连接之后，我们就要与数据库进行交互了，用Java语言与数据库进行交互就需要用到JDBC的`Statement`,`CallableStatement`,`PreparedStatement`
首先这些都是接口！不能由他们直接创建对象。这三个有很多的共同点。
PreparedStatement 继承了Statement接口，相当于扩展了一些功能。
## 创建对象
这三个在使用之前都需要`Connection`创建他们的对象。
虽然都是由Connection对象来调用方法来创建，但是他们的方法还是不一样的。
以下 `conn`代表着数据库的连接
#### 创建Statement对象：
`conn.createStatement()`
值得注意的是，这个方法不接受任何参数。
#### 创建PreparedStatement
`conn.preparedStatement(SQL)`
这个SQL是预编译的语句，用`?`，来占位。例如：
```
String SQL = "Update Employees SET age = ? WHERE id = ?";
```
这个`？`以后是通过`setXXX()`方法来进行输入的。如果忘记绑定，就会输出SQLException。
例如：`setString(1,"0880");`其中第一个参数是要填充的位置，从1开始，第二个是填充的值。
#### 创建CallableStatement对象
Callable对象用于执行对数据库存储过程的调用
关于存储过程的相关信息，在另一篇文章中，虽然没有完全理解，但是还是有一定的认识的。
以下代码片段显示了如何创建一个`CallableStatement`对象。
```
CallableStatement cstmt = null;
try {
String strSQL - "{call getEmpName (?,?)}";
cstmt = conn.prepareCall(strSQL);
...
}
catch (SQLException e) {
...
}
finally {
...
}
```
这其中，`strSQL`代表的是存储过程，带有两个参数占位符。
`CallableStatement`对象和`PreparedStatement`对象一样，在执行语句之前，必须将值绑定到所有参数，否则将跑出一个`SQLException`异常。
看一下oracle存储过程：
```
CREATE OR REPLACE PROCEDURE getEmpName
(EMP_ID IN NUMBER, EMP_FIRST OUT VARCHAR) AS
BEGIN
SELECT first INTO EMP_FIRST
FROM Employees
WHERE ID = EMP_ID;
END;
```
可以发现这里面有三个参数类型，`IN`和`OUT`和`INOUT`
首先PreparedStatement对象只是用
`IN`参数
Callable可以使用上面三个参数类型。
更加详细的：
IN：创建SQL语句时其参数值是未知的，使用setXXX()方法将值绑定到IN参数。
OUT：由SQL语句返回的参数值，可以使用getXXX()方法从OUT参数中检索值。
INOUT：提供输入和输出的参数。使用`setXXX()`方法绑定变量并使用getXXX()方法检索值。
见了上面的三个类型。
> 【实例】使用CallableStatement的示例。
确保在`EMP`数据库中创建了getEmpName()存储过程，可以使用Mysql查询来创建。
```
DELIMITER $$
DROP PROCEDURE IF EXISTS `EMP`.`getEmpName` $$
CREATE PROCEDURE `EMP`.`getEmpName`
(IN EMP_ID INT, OUT EMP_FIRST VARCHAR(255))
BEGIN
SELECT first INTO EMP_FIRST
FROM Employees
WHERE ID = EMP_ID;
END $$
DELIMITER ;
```
查询emp数据库的所有存储过程，如下语句：
```
mysql> select `name` from mysql.proc where db = 'emp' and `type` = 'PROCEDURE';
+------------+
| name |
+------------+
| getEmpName |
+------------+
1 row in set (0.00 sec)
```
然后再编写JDBC代码：
```
//1. 导包
import java.sql.*;
public class JDBCCallableStatement{
//数据库驱动和URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/EMP";
//数据库用户名和密码
static final String USER = "root";
static final String PASD = "123456";
public static void main(String[] args){
//创建两个对象 Connnection、和 CallableStatement
Connection conn = null;
CallableStatement stmt = null；
//注册驱动(注意跑出错误）
try{
Class.forName(JDBC_DRIVER);
System.out.println("连接数据库中。。。");
//打开连接
conn = DriverManager.getConnection(DB_URL,USER,PASS);
//创建statement对象
String sql = "{call getEmpName(?,?)}";
stmt = conn.prepareCall(sql);
//先绑定IN参数，然后绑定OUT参数
int empID = 102;
stmt.setInt(1,empID);//将ID设置成102
//因为第二个参数是OUT，所以用register它。 stmt.registerOutParameter(2,java.sql,Types.VARCHAR);
//使用excute方法去存储过程
System.out.println("Executing stored procedure");
stmt.execute();//因为在存储过程中有相应的sql语句，所以相当于给存储过程传入参数后就不需要在为stmt传入参数了。
//执行完毕后，亚欧使用getXXX方法，接收employee name
String empName = stmt.getString(2);
System.out.println("Emp Name with ID:" + empID+ "is " + empName);
//关闭连接。
stmt.close();
conn.close();
}catch (SQLException se){
//se.printStackTrace();
}catch (Exception e){
e.printStackTrace();
}finally{
//最后关闭资源
try{
if(stmt!=null)
stmt.close();
}catch(SQLException se2){
}//什么都做不了
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}
}
System.out.println("GoodBye！");
}
}
```
经过这个就很明了了。
## 执行SQL语句
建完以上的Statement对象、CallableStatement对象、PreparedStatement对象。我们就需要通过他们执行Sql语句，和数据库进行交互了!!!
这三个大体是相同的。
#### Statement对象
三个方法：
`boolean execute (String SQL) `： 如果可以检索到ResultSet对象，则返回一个布尔值true; 否则返回false。使用此方法执行SQLDDL语句或需要使用真正的动态SQL，可使用于执行创建数据库，创建表的SQL语句等等。
`int executeUpdate (String SQL)`: 返回受SQL语句执行影响的行数。使用此方法执行预期会影响多行的SQL语句，例如:INSERT，UPDATE或DELETE语句。
`ResultSet executeQuery(String SQL)`：返回一个ResultSet对象。 当您希望获得结果集时，请使用此方法，就像使用SELECT语句一样。
#### PreparedStatement对象
由于PreparedStatement对象，在创建对象的过程中就已经传入了sql语句，并通过SetXXX()方法传入了参数。所以`Statemetn`的三个方法对`PreparedStatement`都适用，并且都没有参数。`execute()` `executeQuery()` `executeUpdate()`
#### CallableStatement对象
暂时还不清楚，不过笔者认为，应该是使用`excute()`就可以了。
## 关闭连接
使用`close()`函数，即可。
二、明天完成的事情三、遇到的问题 四、收获
返回列表
评论