Java代码中执行存储过程和函数

电玩女神 2023-10-17 22:56 57阅读 0赞

**一：先看几个概念**：

**首先，自己参考了几篇文章，写的很不错，自己也借鉴一下，然后会在最后贴出自己在项目中使用到的存储过程，已经实现过程，大家可以做个对比，实现方法不同。http://www.cnblogs.com/liunanjava/p/4261242.html**

过程和函数，它们被编译后保存在数据库中，称为持久性存储模块（Persistent Stored Module，PSM），可以反复调用，运行速度快。**不同之处是函数必须指定返回类型。**

**Java调用结构：**

**存储过程：\{call <procedure-name>\[(<arg1>,<arg2>, …)\]\} 
函数：\{?= call <procedure-name>\[(<arg1>,<arg2>, …)\]\}**。

如果要调用存储过程，则使用第一种语法，就是开头不带问号的语法，call 后面是过程名，  
如果没有参数，可以省略小括号。  
如果要调用函数，则使用第二种语法，开头带有一个问号加等号，实际上这个问号就是一个占位符，这个问号总是调用函数的第一个占位符。其它部分与过程的语法相同。

**二、CallableStatement 执行存储过程**：

##### 2.1、建立基类 #####

**\[java\]** view plain copy

print ?

1. package com.pb.emp.dao;
2. 
3. import java.sql.Connection;
4. import java.sql.DriverManager;
5. import java.sql.PreparedStatement;
6. import java.sql.ResultSet;
7. import java.sql.SQLException;
8. 
9. import com.pb.emp.untily.ConfigManager;
10. 
11. 
12. public class BaseDao \{
13. protected Connection conn;
14. protected PreparedStatement ps;
15. protected ResultSet rs;
16. 
17. //建立连接
18. public boolean getConnection()\{
19. String driver=ConfigManager.getInstance().getString(”jdbc.driver\_class”);
20. String url=ConfigManager.getInstance().getString(”jdbc.connection.url”);
21. String username=ConfigManager.getInstance().getString(”jdbc.connection.username”);
22. String password=ConfigManager.getInstance().getString(”jdbc.connection.password”);
23. 
24. try \{
25. Class.forName(driver);
26. conn=DriverManager.getConnection(url,username, password);
27. \} catch (ClassNotFoundException e) \{
28. // TODO Auto-generated catch block
29. e.printStackTrace();
30. return false;
31. \} catch (SQLException e) \{
32. // TODO Auto-generated catch block
33. e.printStackTrace();
34. return false;
35. \}
36. return true;
37. \}
38. 
39. //增加，修改，删除
40. public int executeUpdate(String sql, Object\[\] params)\{
41. getConnection();
42. int updateRow=0;
43. try \{
44. ps=conn.prepareStatement(sql);
45. //填充占位符
46. for(int i=0;i<params.length;i++)\{
47. ps.setObject(i+1, params\[i\]);
48. \}
49. updateRow = ps.executeUpdate();
50. \} catch (SQLException e) \{
51. // TODO Auto-generated catch block
52. e.printStackTrace();
53. \}
54. return updateRow;
55. \}
56. //
57. //查询
58. public ResultSet executeSQL(String sql, Object\[\] params)\{
59. getConnection();
60. 
61. try \{
62. ps=conn.prepareStatement(sql);
63. //填充占位符
64. for(int i=0;i<params.length;i++)\{
65. ps.setObject(i+1, params\[i\]);
66. \}
67. rs = ps.executeQuery();
68. \} catch (SQLException e) \{
69. // TODO Auto-generated catch block
70. e.printStackTrace();
71. \}
72. return rs;
73. \}
74. 
75. // 关闭资源
76. public boolean closeResource() \{
77. if(rs!=null)\{
78. try \{
79. rs.close();
80. \} catch (SQLException e) \{
81. // TODO Auto-generated catch block
82. e.printStackTrace();
83. return false;
84. \}
85. \}
86. if(ps!=null)\{
87. try \{
88. ps.close();
89. \} catch (SQLException e) \{
90. // TODO Auto-generated catch block
91. e.printStackTrace();
92. return false;
93. \}
94. \}
95. 
96. if(conn!=null)\{
97. try \{
98. conn.close();
99. \} catch (SQLException e) \{
100. // TODO Auto-generated catch block
101. e.printStackTrace();
102. return false;
103. \}
104. \}
105. return true;
106. \}
107. \}

package com.pb.emp.dao; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import com.pb.emp.untily.ConfigManager; public class BaseDao \{ protected Connection conn; protected PreparedStatement ps; protected ResultSet rs; //建立连接 public boolean getConnection()\{ String driver=ConfigManager.getInstance().getString(“jdbc.driver\_class”); String url=ConfigManager.getInstance().getString(“jdbc.connection.url”); String username=ConfigManager.getInstance().getString(“jdbc.connection.username”); String password=ConfigManager.getInstance().getString(“jdbc.connection.password”); try \{ Class.forName(driver); conn=DriverManager.getConnection(url,username, password); \} catch (ClassNotFoundException e) \{ // TODO Auto-generated catch block e.printStackTrace(); return false; \} catch (SQLException e) \{ // TODO Auto-generated catch block e.printStackTrace(); return false; \} return true; \} //增加，修改，删除 public int executeUpdate(String sql, Object\[\] params)\{ getConnection(); int updateRow=0; try \{ ps=conn.prepareStatement(sql); //填充占位符 for(int i=0;i<params.length;i++)\{ ps.setObject(i+1, params\[i\]); \} updateRow = ps.executeUpdate(); \} catch (SQLException e) \{ // TODO Auto-generated catch block e.printStackTrace(); \} return updateRow; \} // //查询 public ResultSet executeSQL(String sql, Object\[\] params)\{ getConnection(); try \{ ps=conn.prepareStatement(sql); //填充占位符 for(int i=0;i<params.length;i++)\{ ps.setObject(i+1, params\[i\]); \} rs = ps.executeQuery(); \} catch (SQLException e) \{ // TODO Auto-generated catch block e.printStackTrace(); \} return rs; \} // 关闭资源 public boolean closeResource() \{ if(rs!=null)\{ try \{ rs.close(); \} catch (SQLException e) \{ // TODO Auto-generated catch block e.printStackTrace(); return false; \} \} if(ps!=null)\{ try \{ ps.close(); \} catch (SQLException e) \{ // TODO Auto-generated catch block e.printStackTrace(); return false; \} \} if(conn!=null)\{ try \{ conn.close(); \} catch (SQLException e) \{ // TODO Auto-generated catch block e.printStackTrace(); return false; \} \} return true; \} \}

##### 2.2、执行不带参但是有返回值的存储过程 #####

下面建立存储过程

**\[sql\]** view plain copy

print ?

1.  –查询emp表记录数
2.  CREATE OR REPLACE PROCEDURE getEmpCount(v\_count OUT NUMBER)
3.  AS
4.  BEGIN
5.  SELECT COUNT(\*) INTO v\_count FROM emp;
6.  END;

–查询emp表记录数 CREATE OR REPLACE PROCEDURE getEmpCount(v\_count OUT NUMBER) AS BEGIN SELECT COUNT(\*) INTO v\_count FROM emp; END;

调用

**\[java\]** view plain copy

print ?

1.  //执行不带参但是有返回值的存储过程获取emp表总记录数
2.  public int getTotalCountProc()\{
3.  //定义一个变量来接收结果
4.  int totalCount=0;
5.  //声明CallableStatement对象
6.  CallableStatement proc=null;
7.  String sql=”\{call getEmpCount(?)\}”;
8.  
9.  try \{
10. //建立连接
11. getConnection();
12. //CallableStatement对象
13. proc=conn.prepareCall(sql);
14. //将数据库对象数据类型注册为java中的类型
15. proc.registerOutParameter(1, Types.INTEGER);
16. //执行
17. proc.execute();
18. //接收返回值
19. totalCount=proc.getInt(1);
20. \} catch (SQLException e) \{
21. // TODO Auto-generated catch block
22. e.printStackTrace();
23. \}
24. 
25. return totalCount;
26. \}

//执行不带参但是有返回值的存储过程获取emp表总记录数 public int getTotalCountProc()\{ //定义一个变量来接收结果 int totalCount=0; //声明CallableStatement对象 CallableStatement proc=null; String sql=”\{call getEmpCount(?)\}”; try \{ //建立连接 getConnection(); //CallableStatement对象 proc=conn.prepareCall(sql); //将数据库对象数据类型注册为java中的类型 proc.registerOutParameter(1, Types.INTEGER); //执行 proc.execute(); //接收返回值 totalCount=proc.getInt(1); \} catch (SQLException e) \{ // TODO Auto-generated catch block e.printStackTrace(); \} return totalCount; \}

##### 2.3、执行带参带返回值的存储过程 #####

**\[sql\]** view plain copy

print ?

1.  –根据部门编号和姓名查询人数
2.  CREATE OR REPLACE PROCEDURE getEmpCount(v\_deptno NUMBER, v\_ename VARCHAR2,v\_count OUT NUMBER)
3.  AS
4.  BEGIN
5.  SELECT COUNT(\*) INTO v\_count FROM emp
6.  WHERE ename LIKE ‘%’||v\_ename||‘%’ AND deptno=v\_deptno;
7.  END;

–根据部门编号和姓名查询人数 CREATE OR REPLACE PROCEDURE getEmpCount(v\_deptno NUMBER, v\_ename VARCHAR2,v\_count OUT NUMBER) AS BEGIN SELECT COUNT(\*) INTO v\_count FROM emp WHERE ename LIKE ‘%’||v\_ename||’%’ AND deptno=v\_deptno; END;

**\[java\]** view plain copy

print ?

1.  //执行带参带返回值的存储过程
2.  public int getTotalCountProc1(int deptno,String ename)\{
3.  //定义一个变量来接收结果
4.  int totalCount=0;
5.  //声明CallableStatement对象
6.  CallableStatement proc=null;
7.  String sql=”\{call getEmpCount(?,?,?)\}”;
8.  //建立连接
9.  getConnection();
10. //CallableStatement对象
11. try \{
12. proc=conn.prepareCall(sql);
13. //设置占位符
14. //Object \[\] params=\{deptno,ename\};
15. //只设置输入参数即可
16. proc.setInt(1, deptno);
17. proc.setString(2, ename);
18. //proc.setInt(3, totalCount);
19. 将数据库对象数据类型注册为java中的类型，将输出参数转换
20. proc.registerOutParameter(3, Types.INTEGER);
21. //执行
22. proc.execute();
23. //获取结果
24. totalCount=proc.getInt(3);
25. \} catch (SQLException e) \{
26. // TODO Auto-generated catch block
27. e.printStackTrace();
28. \}finally\{
29. this.closeResource();
30. if(proc!=null)\{
31. try \{
32. proc.close();
33. \} catch (SQLException e) \{
34. // TODO Auto-generated catch block
35. e.printStackTrace();
36. \}
37. \}
38. \}
39. 
40. return totalCount;
41. 
42. \}

//执行带参带返回值的存储过程 public int getTotalCountProc1(int deptno,String ename)\{ //定义一个变量来接收结果 int totalCount=0; //声明CallableStatement对象 CallableStatement proc=null; String sql=”\{call getEmpCount(?,?,?)\}”; //建立连接 getConnection(); //CallableStatement对象 try \{ proc=conn.prepareCall(sql); //设置占位符 //Object \[\] params=\{deptno,ename\}; //只设置输入参数即可 proc.setInt(1, deptno); proc.setString(2, ename); //proc.setInt(3, totalCount); 将数据库对象数据类型注册为java中的类型，将输出参数转换 proc.registerOutParameter(3, Types.INTEGER); //执行 proc.execute(); //获取结果 totalCount=proc.getInt(3); \} catch (SQLException e) \{ // TODO Auto-generated catch block e.printStackTrace(); \}finally\{ this.closeResource(); if(proc!=null)\{ try \{ proc.close(); \} catch (SQLException e) \{ // TODO Auto-generated catch block e.printStackTrace(); \} \} \} return totalCount; \}

##### 2.4、执行返回值为游标的存储过程 #####

**\[sql\]** view plain copy

print ?

1.  –查询员工所有信息
2.  CREATE OR REPLACE PROCEDURE emp\_cur(emp\_cur OUT SYS\_REFCURSOR)
3.  AS
4.  BEGIN
5.  OPEN emp\_cur FOR SELECT \* FROM emp;
6.  END;

–查询员工所有信息 CREATE OR REPLACE PROCEDURE emp\_cur(emp\_cur OUT SYS\_REFCURSOR) AS BEGIN OPEN emp\_cur FOR SELECT \* FROM emp; END;

**\[java\]** view plain copy

print ?

1. //执行返回值为游标的存储过程 游标名emp\_cur
2. public List<emp> getempProc1()\{
3. List<emp> emplist=new ArrayList<emp>();
4. String sql=”\{call emp\_cur(?) \}”;
5. //声明CallableStatement对象
6. CallableStatement proc=null;
7. //建立连接
8. getConnection();
9. 
10. try \{
11. //执行
12. proc=conn.prepareCall(sql);
13. //注册类型为数据库游标类型
14. proc.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
15. //接收结果集
16. proc.execute();
17. //获取结果第一个对象
18. rs=(ResultSet) proc.getObject(1);
19. while(rs.next())\{
20. int empno=rs.getInt(“empno”);
21. String ename=rs.getString(”ename”);
22. String job=rs.getString(”job”);
23. int mgr=rs.getInt(“mgr”);
24. Date hiredate=rs.getDate(”hiredate”);
25. double sal=rs.getDouble(“sal”);
26. double comm=rs.getDouble(“comm”);
27. int deptno=rs.getInt(“deptno”);
28. //声明Emp对象
29. Emp emp=new Emp();
30. //将得到的值添加到对象中
31. emp.setEmpno(empno);
32. emp.setEname(ename);
33. emp.setJob(job);
34. emp.setMgr(mgr);
35. emp.setHiredate(hiredate);
36. emp.setSal(sal);
37. emp.setComm(comm);
38. emp.setDeptno(deptno);
39. //将对象添加到集合
40. emplist.add(emp);
41. \}
42. \} catch (SQLException e) \{
43. // TODO Auto-generated catch block
44. e.printStackTrace();
45. \}finally\{
46. this.closeResource();
47. if(proc!=null)\{
48. try \{
49. proc.close();
50. \} catch (SQLException e) \{
51. // TODO Auto-generated catch block
52. e.printStackTrace();
53. \}
54. \}
55. \}
56. 
57. 
58. 
59. return emplist;
60. 
61. \}</emp></emp></emp>

//执行返回值为游标的存储过程 游标名emp\_cur public List<emp> getempProc1()\{ List<emp> emplist=new ArrayList<emp>(); String sql=”\{call emp\_cur(?) \}”; //声明CallableStatement对象 CallableStatement proc=null; //建立连接 getConnection(); try \{ //执行 proc=conn.prepareCall(sql); //注册类型为数据库游标类型 proc.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR); //接收结果集 proc.execute(); //获取结果第一个对象 rs=(ResultSet) proc.getObject(1); while(rs.next())\{ int empno=rs.getInt(“empno”); String ename=rs.getString(“ename”); String job=rs.getString(“job”); int mgr=rs.getInt(“mgr”); Date hiredate=rs.getDate(“hiredate”); double sal=rs.getDouble(“sal”); double comm=rs.getDouble(“comm”); int deptno=rs.getInt(“deptno”); //声明Emp对象 Emp emp=new Emp(); //将得到的值添加到对象中 emp.setEmpno(empno); emp.setEname(ename); emp.setJob(job); emp.setMgr(mgr); emp.setHiredate(hiredate); emp.setSal(sal); emp.setComm(comm); emp.setDeptno(deptno); //将对象添加到集合 emplist.add(emp); \} \} catch (SQLException e) \{ // TODO Auto-generated catch block e.printStackTrace(); \}finally\{ this.closeResource(); if(proc!=null)\{ try \{ proc.close(); \} catch (SQLException e) \{ // TODO Auto-generated catch block e.printStackTrace(); \} \} \} return emplist; \}</emp></emp></emp>

以上看出，需要将输出的参数，和结果注册，输入的参数不要注册,

但输入参数需要设置占位符。

**三、执行函数**。

##### 3.1 、函数功能为根据雇员id 返回姓名 #####

**\[sql\]** view plain copy

print ?

1.  CREATE OR REPLACE FUNCTION getename(v\_empno NUMBER)
2.  RETURN VARCHAR2
3.  AS
4.  v\_ename VARCHAR2(20);
5.  
6.  BEGIN
7.  SELECT ename INTO v\_ename FROM emp WHERE empno=v\_empno;
8.  RETURN v\_ename;
9.  END;

CREATE OR REPLACE FUNCTION getename(v\_empno NUMBER) RETURN VARCHAR2 AS v\_ename VARCHAR2(20); BEGIN SELECT ename INTO v\_ename FROM emp WHERE empno=v\_empno; RETURN v\_ename; END;

**\[java\]** view plain copy

print ?

1.  public void getenamefun(int empno)\{
2.  //sql
3.  String ename=”“;
4.  String sql=”\{?=call getename(?)\}”;//注意和存储过程的区别
5.  CallableStatement fun=null;
6.  getConnection();
7.  try \{
8.  fun=conn.prepareCall(sql);
9.  fun.setInt(2, empno);
10. fun.registerOutParameter(1, Types.VARCHAR);
11. fun.execute();
12. ename=fun.getString(1);
13. System.out.println(ename);
14. \} catch (SQLException e) \{
15. // TODO Auto-generated catch block
16. e.printStackTrace();
17. \}
18. 
19. \}

public void getenamefun(int empno)\{ //sql String ename=”“; String sql=”\{?=call getename(?)\}”;//注意和存储过程的区别 CallableStatement fun=null; getConnection(); try \{ fun=conn.prepareCall(sql); fun.setInt(2, empno); fun.registerOutParameter(1, Types.VARCHAR); fun.execute(); ename=fun.getString(1); System.out.println(ename); \} catch (SQLException e) \{ // TODO Auto-generated catch block e.printStackTrace(); \} \}  
其它的方法与过程一样，只是多了个返回值类型。

——————————————————————

**四：接下来说下自己项目中的，直接看代码。**

创建储存过程的代码基本一样，只是Java调用存储过程的时候，跟上边调用的方法不同，大家可以自行理解：

**4.1:更新订单状态，返回状态值**

**\[sql\]** view plain copy

print ?

1. DELIMITER 
 
 &nbsp;-------------先定义语句的结束符号&nbsp;&nbsp;</li><li class="">&nbsp;&nbsp;</li><li class="alt">USE&nbsp;\`zhangzi\_tester\` &nbsp;-------------先定义语句的结束符号&nbsp;&nbsp;</li><li class="">&nbsp;&nbsp;</li><li class="alt">USE&nbsp;\`zhangzi\_tester\` 
2. 
3. DROP PROCEDURE IF EXISTS \`pay\_orderState\`
 
 &nbsp;&nbsp;</li><li class="">&nbsp;&nbsp;</li><li class="alt">CREATE&nbsp;&nbsp;PROCEDURE&nbsp;\`pay\_orderState\`(&nbsp;&nbsp;</li><li class="">&nbsp;&nbsp;&nbsp;&nbsp;IN&nbsp;serNumbner&nbsp;VARCHAR&nbsp;(32),&nbsp;&nbsp;</li><li class="alt">&nbsp;&nbsp;&nbsp;&nbsp;IN&nbsp;itemId&nbsp;VARCHAR&nbsp;(32),&nbsp;&nbsp;</li><li class="">&nbsp;&nbsp;&nbsp;&nbsp;IN&nbsp;captions&nbsp;INT,&nbsp;&nbsp;</li><li class="alt">&nbsp;&nbsp;&nbsp;&nbsp;IN&nbsp;cfcaPayTime&nbsp;VARCHAR(32),&nbsp;&nbsp;</li><li class="">&nbsp;&nbsp;&nbsp;&nbsp;IN&nbsp;payDate&nbsp;VARCHAR(30),&nbsp;&nbsp;</li><li class="alt">&nbsp;&nbsp;&nbsp;&nbsp;OUT&nbsp;state&nbsp;VARCHAR&nbsp;(2))&nbsp;&nbsp;</li><li class="">BEGIN&nbsp;&nbsp;</li><li class="alt">&nbsp;&nbsp;&nbsp;&nbsp;DECLARE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;maxMoney&nbsp;INT&nbsp;DEFAULT&nbsp;0;&nbsp;&nbsp;</li><li class="">&nbsp;&nbsp;DECLARE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;countMoney&nbsp;INT&nbsp;DEFAULT&nbsp;0;&nbsp;&nbsp;</li><li class="alt">&nbsp;&nbsp;DECLARE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;caps&nbsp;INT&nbsp;DEFAULT&nbsp;0;&nbsp;&nbsp;</li><li class="">START&nbsp;TRANSACTION;--&nbsp;配置事务&nbsp;&nbsp;</li><li class="alt">SELECT&nbsp;&nbsp;IFNULL(max\_finance\_money,0)&nbsp;INTO&nbsp;maxMoney&nbsp;FROM&nbsp;&nbsp;t\_item\_info&nbsp;WHERE&nbsp;&nbsp;&nbsp;id&nbsp;=&nbsp;itemId;&nbsp;&nbsp;</li><li class="">SELECT&nbsp;&nbsp;IFNULL(SUM(capital),0)&nbsp;INTO&nbsp;countMoney&nbsp;FROM&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;t\_order\_info&nbsp;WHERE&nbsp;fk\_item\_id&nbsp;=&nbsp;itemId&nbsp;AND&nbsp;pay\_status&nbsp;=&nbsp;'01';&nbsp;&nbsp;</li><li class="alt">&nbsp;&nbsp;&nbsp;IF&nbsp;maxMoney&nbsp;>=&nbsp;captions+countMoney&nbsp;&nbsp;THEN&nbsp;&nbsp;--&nbsp;判断有没有超募&nbsp;&nbsp;</li><li class="">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;IF&nbsp;&nbsp;maxMoney&nbsp;=&nbsp;countMoney+captions&nbsp;THEN&nbsp;&nbsp;</li><li class="alt">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UPDATE&nbsp;&nbsp;t\_item\_info&nbsp;SET&nbsp;full\_flag='01'&nbsp;WHERE&nbsp;id=&nbsp;itemId;&nbsp;&nbsp;</li><li class="">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;END&nbsp;IF;&nbsp;&nbsp;</li><li class="alt">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UPDATE&nbsp;t\_order\_info&nbsp;SET&nbsp;pay\_status&nbsp;=&nbsp;'01',order\_complete\_date&nbsp;=&nbsp;cfcaPayTime,&nbsp;pay\_date=&nbsp;payDate&nbsp;&nbsp;WHERE&nbsp;&nbsp;&nbsp;serial\_number&nbsp;=&nbsp;serNumbner;&nbsp;--&nbsp;没有超募&nbsp;&nbsp;</li><li class="">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SET&nbsp;state&nbsp;='1';&nbsp;--&nbsp;未超募&nbsp;返回1&nbsp;&nbsp;&nbsp;</li><li class="alt">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ELSE&nbsp;&nbsp;</li><li class="">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UPDATE&nbsp;t\_order\_info&nbsp;SET&nbsp;pay\_status&nbsp;=&nbsp;'03',order\_complete\_date&nbsp;=&nbsp;cfcaPayTime,&nbsp;pay\_date=&nbsp;&nbsp;payDate&nbsp;&nbsp;WHERE&nbsp;&nbsp;serial\_number&nbsp;=&nbsp;serNumbner;&nbsp;--&nbsp;没有超募&nbsp;&nbsp;</li><li class="alt">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SET&nbsp;state&nbsp;='3';&nbsp;--&nbsp;已超募&nbsp;返回3&nbsp;&nbsp;</li><li class="">&nbsp;&nbsp;&nbsp;&nbsp;END&nbsp;IF;&nbsp;&nbsp;</li><li class="alt">COMMIT;&nbsp;&nbsp;&nbsp;--&nbsp;事务结束提交&nbsp;&nbsp;</li><li class="">END &nbsp;&nbsp;</li><li class="">&nbsp;&nbsp;</li><li class="alt">CREATE&nbsp;&nbsp;PROCEDURE&nbsp;\`pay\_orderState\`(&nbsp;&nbsp;</li><li class="">&nbsp;&nbsp;&nbsp;&nbsp;IN&nbsp;serNumbner&nbsp;VARCHAR&nbsp;(32),&nbsp;&nbsp;</li><li class="alt">&nbsp;&nbsp;&nbsp;&nbsp;IN&nbsp;itemId&nbsp;VARCHAR&nbsp;(32),&nbsp;&nbsp;</li><li class="">&nbsp;&nbsp;&nbsp;&nbsp;IN&nbsp;captions&nbsp;INT,&nbsp;&nbsp;</li><li class="alt">&nbsp;&nbsp;&nbsp;&nbsp;IN&nbsp;cfcaPayTime&nbsp;VARCHAR(32),&nbsp;&nbsp;</li><li class="">&nbsp;&nbsp;&nbsp;&nbsp;IN&nbsp;payDate&nbsp;VARCHAR(30),&nbsp;&nbsp;</li><li class="alt">&nbsp;&nbsp;&nbsp;&nbsp;OUT&nbsp;state&nbsp;VARCHAR&nbsp;(2))&nbsp;&nbsp;</li><li class="">BEGIN&nbsp;&nbsp;</li><li class="alt">&nbsp;&nbsp;&nbsp;&nbsp;DECLARE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;maxMoney&nbsp;INT&nbsp;DEFAULT&nbsp;0;&nbsp;&nbsp;</li><li class="">&nbsp;&nbsp;DECLARE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;countMoney&nbsp;INT&nbsp;DEFAULT&nbsp;0;&nbsp;&nbsp;</li><li class="alt">&nbsp;&nbsp;DECLARE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;caps&nbsp;INT&nbsp;DEFAULT&nbsp;0;&nbsp;&nbsp;</li><li class="">START&nbsp;TRANSACTION;--&nbsp;配置事务&nbsp;&nbsp;</li><li class="alt">SELECT&nbsp;&nbsp;IFNULL(max\_finance\_money,0)&nbsp;INTO&nbsp;maxMoney&nbsp;FROM&nbsp;&nbsp;t\_item\_info&nbsp;WHERE&nbsp;&nbsp;&nbsp;id&nbsp;=&nbsp;itemId;&nbsp;&nbsp;</li><li class="">SELECT&nbsp;&nbsp;IFNULL(SUM(capital),0)&nbsp;INTO&nbsp;countMoney&nbsp;FROM&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;t\_order\_info&nbsp;WHERE&nbsp;fk\_item\_id&nbsp;=&nbsp;itemId&nbsp;AND&nbsp;pay\_status&nbsp;=&nbsp;'01';&nbsp;&nbsp;</li><li class="alt">&nbsp;&nbsp;&nbsp;IF&nbsp;maxMoney&nbsp;>=&nbsp;captions+countMoney&nbsp;&nbsp;THEN&nbsp;&nbsp;--&nbsp;判断有没有超募&nbsp;&nbsp;</li><li class="">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;IF&nbsp;&nbsp;maxMoney&nbsp;=&nbsp;countMoney+captions&nbsp;THEN&nbsp;&nbsp;</li><li class="alt">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UPDATE&nbsp;&nbsp;t\_item\_info&nbsp;SET&nbsp;full\_flag='01'&nbsp;WHERE&nbsp;id=&nbsp;itemId;&nbsp;&nbsp;</li><li class="">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;END&nbsp;IF;&nbsp;&nbsp;</li><li class="alt">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UPDATE&nbsp;t\_order\_info&nbsp;SET&nbsp;pay\_status&nbsp;=&nbsp;'01',order\_complete\_date&nbsp;=&nbsp;cfcaPayTime,&nbsp;pay\_date=&nbsp;payDate&nbsp;&nbsp;WHERE&nbsp;&nbsp;&nbsp;serial\_number&nbsp;=&nbsp;serNumbner;&nbsp;--&nbsp;没有超募&nbsp;&nbsp;</li><li class="">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SET&nbsp;state&nbsp;='1';&nbsp;--&nbsp;未超募&nbsp;返回1&nbsp;&nbsp;&nbsp;</li><li class="alt">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ELSE&nbsp;&nbsp;</li><li class="">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UPDATE&nbsp;t\_order\_info&nbsp;SET&nbsp;pay\_status&nbsp;=&nbsp;'03',order\_complete\_date&nbsp;=&nbsp;cfcaPayTime,&nbsp;pay\_date=&nbsp;&nbsp;payDate&nbsp;&nbsp;WHERE&nbsp;&nbsp;serial\_number&nbsp;=&nbsp;serNumbner;&nbsp;--&nbsp;没有超募&nbsp;&nbsp;</li><li class="alt">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SET&nbsp;state&nbsp;='3';&nbsp;--&nbsp;已超募&nbsp;返回3&nbsp;&nbsp;</li><li class="">&nbsp;&nbsp;&nbsp;&nbsp;END&nbsp;IF;&nbsp;&nbsp;</li><li class="alt">COMMIT;&nbsp;&nbsp;&nbsp;--&nbsp;事务结束提交&nbsp;&nbsp;</li><li class="">END 
4. 
5. DELIMITER ;—改成默认的分号;

DELIMITER

————−先定义语句的结束符号USE‘zhangzitester‘  — — — — − 先 定 义 语 句 的 结 束 符 号 U S E ‘ z h a n g z i t e s t e r ‘

DROP PROCEDURE IF EXISTS \`pay\_orderState\`

CREATEPROCEDURE‘payorderState‘(INserNumbnerVARCHAR(32),INitemIdVARCHAR(32),INcaptionsINT,INcfcaPayTimeVARCHAR(32),INpayDateVARCHAR(30),OUTstateVARCHAR(2))BEGINDECLAREmaxMoneyINTDEFAULT0;DECLAREcountMoneyINTDEFAULT0;DECLAREcapsINTDEFAULT0;STARTTRANSACTION;–配置事务SELECTIFNULL(maxfinancemoney,0)INTOmaxMoneyFROMtiteminfoWHEREid=itemId;SELECTIFNULL(SUM(capital),0)INTOcountMoneyFROMtorderinfoWHEREfkitemid=itemIdANDpaystatus=‘01′;IFmaxMoney>=captions\+countMoneyTHEN–判断有没有超募IFmaxMoney=countMoney\+captionsTHENUPDATEtiteminfoSETfullflag=′01′WHEREid=itemId;ENDIF;UPDATEtorderinfoSETpaystatus=‘01′,ordercompletedate=cfcaPayTime,paydate=payDateWHEREserialnumber=serNumbner;–没有超募SETstate=′1′;–未超募返回1ELSEUPDATEtorderinfoSETpaystatus=‘03′,ordercompletedate=cfcaPayTime,paydate=payDateWHEREserialnumber=serNumbner;–没有超募SETstate=′3′;–已超募返回3ENDIF;COMMIT;–事务结束提交END  C R E A T E P R O C E D U R E ‘ p a y o r d e r S t a t e ‘ ( I N s e r N u m b n e r V A R C H A R ( 32 ) , I N i t e m I d V A R C H A R ( 32 ) , I N c a p t i o n s I N T , I N c f c a P a y T i m e V A R C H A R ( 32 ) , I N p a y D a t e V A R C H A R ( 30 ) , O U T s t a t e V A R C H A R ( 2 ) ) B E G I N D E C L A R E m a x M o n e y I N T D E F A U L T 0 ; D E C L A R E c o u n t M o n e y I N T D E F A U L T 0 ; D E C L A R E c a p s I N T D E F A U L T 0 ; S T A R T T R A N S A C T I O N ; – 配 置 事 务 S E L E C T I F N U L L ( m a x f i n a n c e m o n e y , 0 ) I N T O m a x M o n e y F R O M t i t e m i n f o W H E R E i d = i t e m I d ; S E L E C T I F N U L L ( S U M ( c a p i t a l ) , 0 ) I N T O c o u n t M o n e y F R O M t o r d e r i n f o W H E R E f k i t e m i d = i t e m I d A N D p a y s t a t u s = ‘ 01 ′ ; I F m a x M o n e y >= c a p t i o n s + c o u n t M o n e y T H E N – 判 断 有 没 有 超 募 I F m a x M o n e y = c o u n t M o n e y + c a p t i o n s T H E N U P D A T E t i t e m i n f o S E T f u l l f l a g = ′ 01 ′ W H E R E i d = i t e m I d ; E N D I F ; U P D A T E t o r d e r i n f o S E T p a y s t a t u s = ‘ 01 ′ , o r d e r c o m p l e t e d a t e = c f c a P a y T i m e , p a y d a t e = p a y D a t e W H E R E s e r i a l n u m b e r = s e r N u m b n e r ; – 没 有 超 募 S E T s t a t e = ′ 1 ′ ; – 未 超 募 返 回 1 E L S E U P D A T E t o r d e r i n f o S E T p a y s t a t u s = ‘ 03 ′ , o r d e r c o m p l e t e d a t e = c f c a P a y T i m e , p a y d a t e = p a y D a t e W H E R E s e r i a l n u m b e r = s e r N u m b n e r ; – 没 有 超 募 S E T s t a t e = ′ 3 ′ ; – 已 超 募 返 回 3 E N D I F ; C O M M I T ; – 事 务 结 束 提 交 E N D

DELIMITER ;—改成默认的分号;

**4.2：Java调用存储过程代码**

**\[java\]** view plain copy

print ?

1.  /\*
2.   \* 订单支付完成调用存储过程接口，修改订单支付状态
3.   \*/
4.  @SuppressWarnings(“unchecked”)
5.  public String orderState(final String serNumbner,final String itemId,final int captions,final String bankNotificationTime)\{
6.  String param2Value=null;
7.  try\{
8.  param2Value = (String)jdbc.execute(
9.  new CallableStatementCreator() \{
10. public CallableStatement createCallableStatement(Connection con) throws SQLException \{
11. String storedProc = ”\{call pay\_orderState(?,?,?,?,?,?)\}”;// 调用的sql 
12. CallableStatement cs = con.prepareCall(storedProc);
13. cs.setString(1, serNumbner);
14. cs.setString(2, itemId);
15. cs.setInt(3, captions);
16. cs.setString(4, bankNotificationTime);
17. cs.setString(5,DateTimeUtils.getNowTime());
18. cs.registerOutParameter(6, Types.VARCHAR);
19. return cs;
20. \}
21. \}, new CallableStatementCallback() \{
22. public Object doInCallableStatement(CallableStatement cs) throws SQLException,DataAccessException\{
23. cs.execute();
24. return cs.getString(6);// 获取输出参数的值 
25. \}
26. \});
27. \}catch(Exception ex)\{
28. throw new RuntimeException(ex);
29. \}
30. return param2Value;
31. \}

/\* \* 订单支付完成调用存储过程接口，修改订单支付状态 \*/ @SuppressWarnings(“unchecked”) public String orderState(final String serNumbner,final String itemId,final int captions,final String bankNotificationTime)\{ String param2Value=null; try\{ param2Value = (String)jdbc.execute( new CallableStatementCreator() \{ public CallableStatement createCallableStatement(Connection con) throws SQLException \{ String storedProc = “\{call pay\_orderState(?,?,?,?,?,?)\}”;// 调用的sql CallableStatement cs = con.prepareCall(storedProc); cs.setString(1, serNumbner); cs.setString(2, itemId); cs.setInt(3, captions); cs.setString(4, bankNotificationTime); cs.setString(5,DateTimeUtils.getNowTime()); cs.registerOutParameter(6, Types.VARCHAR); return cs; \} \}, new CallableStatementCallback() \{ public Object doInCallableStatement(CallableStatement cs) throws SQLException,DataAccessException\{ cs.execute(); return cs.getString(6);// 获取输出参数的值 \} \}); \}catch(Exception ex)\{ throw new RuntimeException(ex); \} return param2Value; \}

**4.3：再附上两段代码，有关Java调用储存过程的**

**\[java\]** view plain copy

print ?

1.  @Resource(name = “jdbcTemplate”)
2.  private JdbcTemplate jdbc;

@Resource(name = “jdbcTemplate”) private JdbcTemplate jdbc;

**\[xml\]** view plain copy

print ?

1. <bean id=“jdbcTemplate” class=“org.springframework.jdbc.core.JdbcTemplate”>
2. <property name=“dataSource” ref=“dataSource”>
3. </property></bean>

**4.4:最后一张图片–mysql执行储存过程**

![Center][]

[Center]: https://img-blog.csdn.net/20171018183421545?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvYnV5YW9zaHVvaHVhMQ==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center