存储过程与Java调用

Java调用ORACLE的存储过程

一、创建 t_Test 表

CREATE TABLE t_Test(
f_XH VARCHAR(6) PRIMARY KEY,
f_Money Number,
f_PDate DATE );
/

二、加入数据
INSERT INTO t_Test SELECT 'A1',1.0,SYSDATE FROM DUAL;
INSERT INTO t_Test SELECT 'A2',2.0,SYSDATE FROM DUAL;
INSERT INTO t_Test SELECT 'A3',3.0,SYSDATE FROM DUAL;
INSERT INTO t_Test SELECT 'A4',4.0,SYSDATE FROM DUAL;

SELECT * FROM t_Test

三、创建游标 【cur_Test】 动态游标
CREATE OR REPLACE PACKAGE cur_Test
IS
  TYPE RefCursor IS Ref CURSOR;
END cur_Test;
/

四、创建存储过程 【p_GetMoney】
CREATE OR REPLACE PROCEDURE p_GetMoney
(
uCode  OUT NUMBER,
uMsg   OUT VARCHAR2,
uCur   OUT cur_Test.RefCursor,
uMoney IN  NUMBER
) IS
BEGIN
  uCode := 0;
  uMsg  := '操作成功';

  OPEN uCur FOR SELECT * FROM t_Test WHERE f_Money < uMoney;
  EXCEPTION
  WHEN Others THEN
  uCode := -1;
  uMsg  := '错误代码:' || SQLCODE || CHR(13) || '错误信息:' || SQLERRM;
END p_GetMoney;


五、创建函数【f_GetMoney】
CREATE OR REPLACE FUNCTION f_GetMoney
(
uMoney IN NUMBER
)
RETURN cur_Test.RefCursor
AS
 uCur cur_Test.RefCursor;
BEGIN
  OPEN uCur FOR SELECT f_XH,f_Money,f_PDate FROM t_Test WHERE f_Money < uMoney;
  Return uCur;
END;



Java 源码

六、Java调用p_GetMoney

import Java.sql.*;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;

public class