package testcase;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
/**
* 调用有返回值的存储过程
* @author study_monkey
* 数据库新建表
* create table Test_tbl
(
i_id INTEGER,
i_name VARCHAR2(20)
)
*新建存储过程
* CREATE OR REPLACE PROCEDURE Test_ProduceOut(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2) AS
BEGIN
select i_name into PARA2 from TEST_pro where i_id = PARA1;
END Test_ProduceOut;
* @param args
*/
public class TestCallProduceOut {
public TestCallProduceOut() {
}
private static final String DRIVER="oracle.jdbc.driver.OracleDriver";
private static final String URL="jdbc:oracle:thin:@127.0.0.1:1521:testdb";
private String name="username";
private static String pwd="password";
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
CallableStatement proc = null;
public void callProduce(){
try {
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL,name,pwd);
proc = conn.prepareCall("{ call Test_ProduceOut(?,?) }"); //调用存储过程
proc.setString(1, "1"); //第一个输入参数
proc.registerOutParameter(2, Types.VARCHAR);//第二个参数输出参数,是VARCHAR类型的
proc.execute();//执行
String getOut= proc.getString(2);//获得输出参数
System.out.println("存储过程返回的值是："+getOut);
}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) {
}
}
}
public static void main(String[] args) {
TestCallProduceOut test = new TestCallProduceOut();
test.callProduce();
}
}
网友评论