exec proc_CSCO_SalesFctByStore 100,200,'name',@mg output
print @mg 在Java中调用import java.sql.*
public class Test{
public static void main(String args[]) throws Exception {//加载驱动DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver())//获得连接
Connection conn = DriverManager.getConnection("jdbc:odbc:mydata", "sa","")//创建存储过程的对象
CallableStatement c = conn.prepareCall("{call proc_CSCO_SalesFctByStore(?,?,?,?)}")//给存储过程的第一个参数设置值
c.setInt(1, 100)//给存储过程的第一个参数设置值
c.setInt(2, 10000)//给存储过程的第一个参数设置值
c.setString(3, user)//注册存储过程的第四个参数
c.registerOutParameter(4, java.sql.Types.VARCHAR)//执行存储过程
c.execute()//得到存储过程的输出参数值
1. 存储过程以及类型定义如下:--The array in oracle
CREATE OR REPLACE TYPE idArray AS TABLE OF VARCHAR2(20)
--package header
CREATE OR REPLACE PACKAGE Lib_Package AS
PROCEDURE Book_Check_Procedure(ids IN idArray, exist OUT NUMBER)
END Lib_Package
--package body
CREATE OR REPLACE PACKAGE BODY Lib_Package AS
PROCEDURE Book_Check_Procedure( ids IN idArray, exist OUT NUMBER) AS v_Index BINARY_INTEGERBEGIN v_Index:= ids.FIRSTLOOP SELECT COUNT(*) INTO exist FROM Lib_Duplicate WHERE status='Lent' AND book_id=ids(v_Index) EXIT WHEN v_Index=ids.LAST OR exist>0 v_Index:= ids.NEXT(v_Index)END LOOPEND Book_Check_Procedure
END Lib_Package
2.在Java中调用上面的存储过程
(1) 在Oracle中定义数组类型idArray (2) 在java构造数组并转换成Oracle中定义的数组类型,调用存储过程
/** * 当要删除图书时,检查是否仍然有图书复本处于借出状态 */ public boolean checkBookStatus(String[] bookIds) throws DataAccessException {
boolean flag = false Connection conn = null OracleCallableStatement cstmt = null ArrayDescriptor desc = null ARRAY bookIdArray = null int count = 0 String sql = "{call LIB_PACKAGE.Book_Check_Procedure(?,?)}"
DbDriverManager dbManager = DbDriverManager.getInstance() conn = dbManager.getConnection(Constants.DATABASE)
try { cstmt = (OracleCallableStatement) conn.prepareCall(sql)
//定义oracle中的数组类型 desc = ArrayDescriptor.createDescriptor("IDARRAY", conn) bookIdArray = new ARRAY(desc, conn, bookIds)
cstmt.setObject(1, bookIdArray, oracle.jdbc.OracleTypes.ARRAY) cstmt.registerOutParameter(2, Types.INTEGER) cstmt.execute() count = cstmt.getInt(2)
log.info(this.getClass() + ".checkBookStatus: count = " + count)
DbOperHelp.closeStatement(this.getClass(), cstmt) DbOperHelp.closeConnection(this.getClass(), conn) } catch (SQLException e) {
log.error(this.getClass() + ".checkBookStatus-->SQLException: " + e.getMessage()) DbOperHelp.closeStatement(this.getClass(), cstmt) DbOperHelp.closeConnection(this.getClass(), conn) throw new DataAccessException( "When check the books, there is a SQLException: " + e.getMessage(), e.getCause()) }
if (count >0) {
flag = true }
return flag}