做一点,记一点 ~ Java调用Oracle存储过程

一、需求

        传入一个参数,返回一条或多条记录(列表)。

 

二、实现步骤

  1. 编写Oracle存储过程。

-- 声明包和包体的语句应该分两次执行,即使声明在前定义在后,如果一起执行依然会编译出错
--
调用能够返回多条记录的存储过程需要定义在包内 --声明包及包内的游标、存储过程 CREATE OR REPLACE PACKAGE pack_myPackage IS TYPE myCursor IS REF CURSOR; PROCEDURE proc_querySomeInfo(pNo IN VARCHAR2, myCursor OUT myCursor); END pack_myPackage;


--定义包体
CREATE OR REPLACE PACKAGE BODY pack_myPackage IS
PROCEDURE proc_querySomeInfo(pNo IN VARCHAR2, outcursor OUT myCursor) IS
BEGIN
  OPEN outcursor FOR
    SELECT * FROM tableA WHERE P_NO = pNo;
END proc_querySomeInfo;
END pack_myPackage;

  2. 编写Java代码

package vip.yaocn.test;

import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet;

/** * 存储过程测试 * * @author yacon */ public class ProcedureTest { public static void main(String[] args) throws Exception { String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl"; String driverName = "oracle.jdbc.driver.OracleDriver"; String username = "scott"; String password = "tiger"; Connection conn = null; CallableStatement cstmt = null; ResultSet rs = null; try { Class.forName(driverName); conn = DriverManager.getConnection(url, username, password); String callSql = "{call pack_myPackage.proc_getSomeInfo(?, ?) }"; cstmt = conn.prepareCall(callSql); cstmt.setString(1, "1001"); cstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR); cstmt.execute(); rs = (ResultSet) cstmt.getObject(2); if (rs != null) { while (rs.next()) { System.out.println(rs.getString(1)); } } } catch (Exception e) { e.printStackTrace(); } finally { try { if (rs != null) { rs.close(); } } catch (Exception e) { e.printStackTrace(); } try { if (cstmt != null) { cstmt.close(); } } catch (Exception e) { e.printStackTrace(); } try { if (conn != null) { conn.close(); } } catch (Exception e) { e.printStackTrace(); } } } }

三、 其他需求

  1. Java调用有返回值的Oracle存储过程(返回一个简单类型的值)

  (1)定义存储过程:

CREATE OR REPLACE PROCEDURE proc_A(param1 IN VARCHAR2, param2 OUT VARCHAR2) 
AS
BEGIN
    SELECT name INTO param2 FROM T_A WHERE id = param1; 
END; 

  (2)编写Java代码:

package vip.yaocn.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

/** * 存储过程测试 * * @author yacon */ public class ProcedureTest { public static void main(String[] args) throws Exception { String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl"; String driverName = "oracle.jdbc.driver.OracleDriver"; String username = "scott"; String password = "tiger"; Connection conn = null; CallableStatement cstmt = null; ResultSet rs = null; try { Class.forName(driverName); conn = DriverManager.getConnection(url, username, password); String callSql = "{call proc_A(?, ?) }"; cstmt = conn.prepareCall(callSql); cstmt.setString(1, "1001"); cstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR); cstmt.execute();
System.out.println("Output is:" + cstmt.getString(2)); 
} catch (Exception e) { e.printStackTrace(); } finally { try { if (rs != null) { rs.close(); } } catch (Exception e) { e.printStackTrace(); } try { if (cstmt != null) { cstmt.close(); } } catch (Exception e) { e.printStackTrace(); } try { if (conn != null) { conn.close(); } } catch (Exception e) { e.printStackTrace(); } } } }

  2. Java调用无返回值的Oracle存储过程(无返回值)

  (1)定义存储过程:

CREATE OR REPLACE PROCEDURE proc_B(param1 IN VARCHAR2, param2 IN VARCHAR2) 
AS
BEGIN
    INSERT INTO T_B VALUES(param1, param2);
END; 

  (2)编写Java代码:

package vip.yaocn.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

/** * 存储过程测试 * * @author yacon */ public class ProcedureTest { public static void main(String[] args) throws Exception { String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl"; String driverName = "oracle.jdbc.driver.OracleDriver"; String username = "scott"; String password = "tiger"; Connection conn = null; CallableStatement cstmt = null; ResultSet rs = null; try { Class.forName(driverName); conn = DriverManager.getConnection(url, username, password); String callSql = "{call proc_B(?, ?) }"; cstmt = conn.prepareCall(callSql); cstmt.setString(1, "1001"); cstmt.setString(2, "TEST"); cstmt.execute(); } catch (Exception e) { e.printStackTrace(); } finally { try { if (rs != null) { rs.close(); } } catch (Exception e) { e.printStackTrace(); } try { if (cstmt != null) { cstmt.close(); } } catch (Exception e) { e.printStackTrace(); } try { if (conn != null) { conn.close(); } } catch (Exception e) { e.printStackTrace(); } } } }

四、相关知识

   1. 在plsql developer中测试存储过程时可以在存储过程名上右击选择 “测试” ,如果需要提供参数可以在下方填入参数,如果没有参数则点击执行按钮即可。

posted @ 2018-05-18 23:00  马小琅  阅读(1434)  评论(0编辑  收藏  举报