Oracle存储过程调用 /11 详例实现java调用ORACLE的存储过程代码 本篇文章主要是在java中调用ORACLE存储过程,并把要用到的存储过程和代码列了出来,给大家做一个参考: 一:无返回值的存储过程 存储过程为: CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) AS BEGIN INSERT INTO HYQ.B_ID (I_ID,I_NAME) VALUES (PARA1, PARA2); END TESTA; 然后呢,在java里调用时就用下面的代码: package com.hyq.src; import java.sql.*; import java.sql.ResultSet; public class TestProcedureOne { public TestProcedureOne() { } public static void main(String[] args ){ String driver = "oracle.jdbc.driver.OracleDriver"; String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521: hyq "; Statement stmt = null; ResultSet rs = null; Connection conn = null; CallableStatement cstmt = null; try { Class.forName(driver); conn = DriverManager.getConnection(strUrl, " hyq ", " hyq "); CallableStatement proc = null; proc = conn.prepareCall("{ call HYQ.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) { } } } } 当然了,这就先要求要建张表TESTTB,里面两个字段(I_ID,I_NAME)。 二:有返回值的存储过程(非列表) 存储过程为: CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2) AS Oracle存储过程调用 /11 BEGIN SELECT INTO PARA2 FROM TESTTB WHERE I_ID= PARA1; END TESTB; 在java里调用时就用下面的代码: package com.hyq.src; public class TestProcedureTWO { public TestProcedureTWO() { } public static void main(String[] args ){ String driver = "oracle.jdbc.driver.OracleDriver"; String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:hyq"; Statement stmt = null; ResultSet rs = null; Connection conn = null; try { Class.forName(driver); conn = DriverManager.getConnection(strUrl, " hyq ", " hyq "); CallableStatement proc = null; proc = conn.prepareCall("{ call HYQ.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参数了。 Jsp中调用Oracle存储过程的小例子 *执行条insert语句并传参数*/ create or replace procedure p_test2(i in number) as begin insert Oracle存储过程调用 /11 o t values (i,\'x\'||to_char(i)); commit; end; / <%@ page language="java" contentType="text/html;char=gb2312"%> <%@page import="java.sql.*"%>
aaa | bbb |
" + rs.getString(1) + " | " + rs.getString(2) + " |
<% out.pr(conn.isClosed); %>