一:无返回值的存储过程
存储过程为:
CREATE OR REPLACE PROCEDURE zst(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) AS
BEGIN
INSERT INTO tablename (value1,value2) VALUES (PARA1, PARA2);
END zst;
java调用下面代码:
import java.sql.*;
import java.sql.ResultSet;
public class TestProcedureNoBack {
public TestProcedureNoBack () {
}
public static void main(String[] args ){
String driver = "oracle.jdbc.driver.OracleDriver";
String strUrl = "";
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
CallableStatement cstmt = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, " usename", " password");
CallableStatement proc = null;
proc = conn.prepareCall("{ call zst(?,?) }");
proc.setString(1, "10");
proc.setString(2, "Two");
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) {
}
}
}
二:有返回值的存储过程(非列表)
存储过程为:
CREATE OR REPLACE PROCEDURE backValue(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2) AS
BEGIN
SELECT colname INTO PARA2 FROM tablename WHERE value1= PARA1;
END backValue;
java调用代码:
public class TestProcedureBack {
public TestProcedureBack () {
}
public static void main(String[] args ){
String driver = "oracle.jdbc.driver.OracleDriver";
String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:sid";
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, "usename ", "password ");
CallableStatement proc = null;
proc = conn.prepareCall("{ call backValue(?,?) }");
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列对应的,java代码里注册时也需要匹配。
三:返回列表
由于oracle存储过程返回值都是通过out参数来实现的,对于列表不例外,由于是集合,就必须要用pagkage了.所以在建立存储过程之前要有相应的包存在:
1, 建一个程序包:
CREATE OR REPLACE PACKAGE TESTPACKAGE AS
TYPE Test_CURSOR IS REF CURSOR;
end TESTPACKAGE;
2,建立存储过程,存储过程为:
CREATE OR REPLACE PROCEDURE TEST(p_CURSOR out TESTPACKAGE.Test_CURSOR) IS
BEGIN
OPEN p_CURSOR FOR SELECT * FROM table;
END TEST;
java调用代码:
import java.sql.*;
import java.io.OutputStream;
import java.io.Writer;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import oracle.jdbc.driver.*;
public class TestProcedureTHREE {
public TestProcedureTHREE() {
}
public static void main(String[] args ){
String driver = "oracle.jdbc.driver.OracleDriver";
String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:sid";
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, "u", "");
CallableStatement proc = null;
proc = conn.prepareCall("{ call hyq.test(?) }");
proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
proc.execute();
rs = (ResultSet)proc.getObject(1);
while(rs.next())
{
System.out.println("<tr><td>" + rs.getString(1) + "</td><td>"+rs.getString(2)+"</td></tr>");
}
}
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) {
}
}
}
}