使用struts2+spring3+Oracle的存储过程

来源：互联网发布：数据库系统是什么编辑：程序博客网时间：2024/05/03 07:59
用Java调用Oracle存储过程一、 无返回值的存储过程测试表:-- Create tablecreate table TESTTB(  ID   VARCHAR2(30),  NAME VARCHAR2(30))tablespace BOM  pctfree 10  initrans 1  maxtrans 255  storage  (    initial 64K    minextents 1    maxextents unlimited  );例: 存储过程为(当然了，这就先要求要建张表TESTTB,里面两个字段（I_ID，I_NAME）。)：CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2, PARA2 IN VARCHAR2) ASBEGIN  INSERT INTO BOM.TESTTB(ID, NAME) VALUES (PARA1, PARA2);END TESTA;在Java里调用时就用下面的代码：package com.yiming.procedure.test;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class TestProcedureDemo1 { public TestProcedureDemo1() { } public static void main(String[] args) {  String driver = "Oracle.jdbc.driver.OracleDriver";  String strUrl = "jdbc:Oracle:thin:@10.20.30.30:1521:vasms";  Statement stmt = null;  ResultSet rs = null;  Connection conn = null;  CallableStatement proc = null;  try {   Class.forName(driver);   conn = DriverManager.getConnection(strUrl, "bom", "bom");   proc = conn.prepareCall("{ call BOM.TESTA(?,?) }");   proc.setString(1, "100");   proc.setString(2, "TestOne");   proc.execute();  } catch (SQLException ex2) {   ex2.printStackTrace();  } catch (Exception ex2) {   ex2.printStackTrace();  } finally {   try {    if (rs != null) {     rs.close();     if (stmt != null) {      stmt.close();     }     if (conn != null) {      conn.close();     }    }   } catch (SQLException ex1) {   }  } }}二、 有返回值的存储过程（非列表）例：存储过程为：CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2, PARA2 OUT VARCHAR2) ASBEGIN  SELECT NAME INTO PARA2 FROM TESTTB WHERE ID = PARA1;END TESTB;在Java里调用时就用下面的代码：package com.yiming.procedure.test;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.sql.Types;public class TestProcedureDemo2 { public static void main(String[] args) {  String driver = "Oracle.jdbc.driver.OracleDriver";  String strUrl = "jdbc:Oracle:thin:@10.20.30.30:1521:vasms";  Statement stmt = null;  ResultSet rs = null;  Connection conn = null;  CallableStatement proc = null;  try {   Class.forName(driver);   conn = DriverManager.getConnection(strUrl, "bom", "bom");   proc = conn.prepareCall("{ call BOM.TESTB(?,?) }");   proc.setString(1, "100");   proc.registerOutParameter(2, Types.VARCHAR);   proc.execute();   String testPrint = proc.getString(2);   System.out.println("=testPrint=is=" + testPrint);  } catch (SQLException ex2) {   ex2.printStackTrace();  } catch (Exception ex2) {   ex2.printStackTrace();  } finally {   try {    if (rs != null) {     rs.close();     if (stmt != null) {      stmt.close();     }     if (conn != null) {      conn.close();     }    }   } catch (SQLException ex1) {   }  } }}注意，这里的proc.getString(2)中的数值2并非任意的，而是和存储过程中的out列对应的，如果out是在第一个位置，那就是proc.getString(1)，如果是第三个位置，就是proc.getString(3)，当然也可以同时有多个返回值，那就是再多加几个out参数了。三、 返回列表由于Oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了.所以要分两部分，1. 建一个程序包。如下：CREATE OR REPLACE PACKAGE TESTPACKAGE AS  TYPE TEST_CURSOR IS REF CURSOR;end TESTPACKAGE;2. 建立存储过程，存储过程为：CREATE OR REPLACE PROCEDURE TESTC(P_CURSOR out TESTPACKAGE.TEST_CURSOR) ISBEGIN  OPEN P_CURSOR FOR    SELECT * FROM BOM.TESTTB;END TESTC;可以看到，它是把游标（可以理解为一个指针），作为一个out 参数来返回值的。在Java里调用时就用下面的代码：在这里要注意，在执行前一定要先把Oracle的驱动包放到class路径里，否则会报错的。package com.yiming.procedure.test;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class TestProcedureDemo3 { public static void main(String[] args) {  String driver = "Oracle.jdbc.driver.OracleDriver";  String strUrl = "jdbc:Oracle:thin:@10.20.30.30:1521:vasms";  Statement stmt = null;  ResultSet rs = null;  Connection conn = null;  CallableStatement proc = null;  try {   Class.forName(driver);   conn = DriverManager.getConnection(strUrl, "bom", "bom");   proc = conn.prepareCall("{ call bom.testc(?) }");   proc.registerOutParameter(1, Oracle.jdbc.OracleTypes.CURSOR);   proc.execute();   rs = (ResultSet) proc.getObject(1);   while (rs.next()) {    System.out.println("<tr><td>" + rs.getString(1) + "</td><td>"      + rs.getString(2) + "</td></tr>");   }  } catch (SQLException ex2) {   ex2.printStackTrace();  } catch (Exception ex2) {   ex2.printStackTrace();  } finally {   try {    if (rs != null) {     rs.close();     if (stmt != null) {      stmt.close();     }     if (conn != null) {      conn.close();     }    }   } catch (SQLException ex1) {   }  } }}
0 0