準備工作：
1. 創建表
drop table T_TEST_PROCEDURE cascade constraints;
/*==============================================================*/
/*
Table: T_TEST_PROCEDURE
*/
/*==============================================================*/
create
table T_TEST_PROCEDURE (
ID
NUMBER(19) not null,
NAME
VARCHAR2(40),
AGE NUMBER(3),
constraint
PK_T_TEST_PROCEDURE primary key (ID)
);
2. 創建存儲過程
無返回值的存儲過程
CREATE OR REPLACE PROCEDURE
proctest (
ID IN NUMBER,
NAME IN VARCHAR2,
age
IN NUMBER
)
AS
BEGIN
INSERT INTO
t_test_procedure
(ID, NAME, age
)
VALUES (ID, NAME, age
);
END
proctest;
/
有非列表返回值的存儲過程
CREATE OR REPLACE PROCEDURE
proc_returnnolist (
p_id IN INTEGER,
p_name OUT
VARCHAR2,
p_age OUT INTEGER
)
AS
BEGIN
SELECT NAME,
age
INTO p_name, p_age
FROM t_test_procedure
WHERE ID =
p_id;
END proc_returnnolist;
/
返回結果集的存儲過程
要返回結果集，需要使用Oracle的package
CREATE OR REPLACE PACKAGE
test_proc_package
AS
TYPE testproccursor IS REF CURSOR;
END
test_proc_package;
存儲過程
CREATE OR REPLACE PROCEDURE
proc_returnlist (
testproccursor OUT
test_proc_package.testproccursor
)
AS
BEGIN
OPEN
testproccursor
FOR
SELECT *
FROM
t_test_procedure;
END proc_returnlist;
/
用Java調用存儲過程
1. 調用無返回值的存儲過程
CallableStatement callableStmt =
con
.prepareCall("{call
proctest(?,?,?)}");
callableStmt.setInt(1,
2);
callableStmt.setString(2, "Stephen");
callableStmt.setInt(3,
27);
callableStmt.execute();
callableStmt.close();
System.out.println("Success");
2. 調用返回非列表的存儲過程
CallableStatement callableStmt =
con
.prepareCall("call
proc_returnnolist(?,?,?)");
callableStmt.setInt(1,
3);
callableStmt.registerOutParameter(2,
Types.VARCHAR);
callableStmt.registerOutParameter(3,
Types.INTEGER);
callableStmt.execute();
String name =
callableStmt.getString(2);
int age = callableStmt.getInt(3);
callableStmt.close();
System.out.println("name:" + name + " age:" +
age);
3. 調用返回結果集的存儲過程
CallableStatement callableStmt =
con
.prepareCall("call
proc_returnlist(?)");
callableStmt.registerOutParameter(1,
OracleTypes.CURSOR);
callableStmt.execute();
ResultSet rs =
(ResultSet) callableStmt.getObject(1);
while (rs.next())
{
System.out.println("name:" + rs.getString("name") + "
age:"
+
rs.getInt("age"));
}
rs.close();
callableStmt.close();
