熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Java編程 >> Java核心技術 >> 正文

java調用oracle存儲過程

2013-11-23 19:12:41  來源: Java核心技術 

  一無返回值的存儲過程

  存儲過程為

  CREATE OR REPLACE PROCEDURE TESTA(PARA IN VARCHARPARA IN  VARCHAR)  AS

  BEGIN

  INSERT INTO HYQB_ID (I_IDI_NAME) VALUES (PARA PARA

  END TESTA

  然後呢在java裡調用時就用下面的代碼

    package comhyqsrc

  import javasql*

  import javasqlResultSet

  public class TestProcedureOne {

  public TestProcedureOne() {

  }

  public static void main(String[] args ){

  String driver = oraclejdbcdriverOracleDriver

  String strUrl = jdbcoraclethin@ hyq

  Statement stmt = null

  ResultSet rs = null

  Connection conn = null

  CallableStatement cstmt = null

  try {

  ClassforName(driver)

  conn =  DriverManagergetConnection(strUrl hyq hyq

  CallableStatement proc = null

  proc = connprepareCall({ call HYQTESTA(??) }

  procsetString(

  procsetString( TestOne

  procexecute()

  }

  catch (SQLException ex) {

  exprintStackTrace()

  }

  catch (Exception ex) {

  exprintStackTrace()

  }

  finally{

  try {

  if(rs != null){

  rsclose()

  if(stmt!=null){

  stmtclose()

  }

  if(conn!=null){

  connclose()

  }

  }

  }

  catch (SQLException ex) {

  }

  }

  }

  }

  當然了這就先要求要建張表TESTTB裡面兩個字段(I_IDI_NAME)

  二有返回值的存儲過程(非列表)

  存儲過程為

 CREATE OR REPLACE PROCEDURE TESTB(PARA IN VARCHARPARA OUT VARCHAR)  AS

  BEGIN

  SELECT INTO PARA FROM TESTTB WHERE I_ID= PARA

  END TESTB

  在java裡調用時就用下面的代碼

   package comhyqsrc

  public class TestProcedureTWO {

  public TestProcedureTWO() {

  }

  public static void main(String[] args ){

  String driver = oraclejdbcdriverOracleDriver

  String strUrl = jdbcoraclethin@hyq

  Statement stmt = null

  ResultSet rs = null

  Connection conn = null

  try {

  ClassforName(driver)

  conn =  DriverManagergetConnection(strUrl hyq hyq

  CallableStatement proc = null

  proc = connprepareCall({ call HYQTESTB(??) }

  procsetString(

  procregisterOutParameter( TypesVARCHAR)

  procexecute()

  String testPrint = procgetString(

  Systemoutprintln(=testPrint=is=+testPrint)

  }

  catch (SQLException ex) {

  exprintStackTrace()

  }

  catch (Exception ex) {

  exprintStackTrace()

  }

  finally{

  try {

  if(rs != null){

  rsclose()

  if(stmt!=null){

  stmtclose()

  }

  if(conn!=null){

  connclose()

  }

  }

  }

  catch (SQLException ex) {

  }

  }

  }

  }

  }


      注意這裡的procgetString()中的數值並非任意的而是和存儲過程中的out列對應的如果out是在第一個位置那就是procgetString(如果是第三個位置就是procgetString(當然也可以同時有多個返回值那就是再多加幾個out參數了

  三返回列表

  由於oracle存儲過程沒有返回值它的所有返回值都是通過out參數來替代的列表同樣也不例外但由於是集合所以不能用一般的參數必須要用pagkage了所以要分兩部分

    建一個程序包如下

   CREATE OR REPLACE PACKAGE TESTPACKAGE  AS

  TYPE Test_CURSOR IS REF CURSOR

  end TESTPACKAGE

  建立存儲過程存儲過程為

    CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGETest_CURSOR) IS

  BEGIN

  OPEN p_CURSOR FOR SELECT * FROM HYQTESTTB

  END TESTC

  可以看到它是把游標(可以理解為一個指針)作為一個out 參數來返回值的

  在java裡調用時就用下面的代碼

 package comhyqsrc

  import javasql*

  import javaioOutputStream

  import javaioWriter

  import javasqlPreparedStatement

  import javasqlResultSet

  import oraclejdbcdriver*

  public class TestProcedureTHREE {

  public TestProcedureTHREE() {

  }

  public static void main(String[] args ){

  String driver = oraclejdbcdriverOracleDriver

  String strUrl = jdbcoraclethin@hyq

  Statement stmt = null

  ResultSet rs = null

  Connection conn = null

  try {

  ClassforName(driver)

  conn =  DriverManagergetConnection(strUrl hyq hyq

  CallableStatement proc = null

  proc = connprepareCall({ call hyqtestc(?) }

  procregisterOutParameter(oraclejdbcOracleTypesCURSOR)

  procexecute()

  rs = (ResultSet)procgetObject(

  while(rsnext())

  {

  Systemoutprintln(<tr><td> + rsgetString() + </td><td>+rsgetString()+</td></tr>

  }

  }

  catch (SQLException ex) {

  exprintStackTrace()

  }

  catch (Exception ex) {

  exprintStackTrace()

  }

  finally{

  try {

  if(rs != null){

  rsclose()

  if(stmt!=null){

  stmtclose()

  }

  if(conn!=null){

  connclose()

  }

  }

  }

  catch (SQLException ex) {

  }

  }

  }

  }

  取得存儲過程返回的值

     CallableStatement cs = connprepareCall({call proc_fbquery(??)} //調用存儲過程cssetString(mem)cssetInt(n)csregisterOutParameter(oraclejdbcOracleTypesCURSOR)csexecute()rs=(ResultSet)csgetObject(

  對存儲過程賦值時

 

  CallableStatement cs= connprepareCall({call proc_fbquery(?)} //調用存儲過程csregisterOutParameter(oraclejdbcOracleTypesCURSOR)cssetCursorName(cusorName)  //提供result的名稱cssetString(rs)rs=csexecuteQuery()rs =(ResultSet)csgetObject(


From:http://tw.wingwit.com/Article/program/Java/hx/201311/26452.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.