[Java]
CallableStatement
CallableStatement Sample Java / Program
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class CallableStatementDemo {
public static void main(String[] args) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Connection con = null;
CallableStatement cstmt = null;
ResultSet rs = null;
try {
con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");
cstmt = con.prepareCall("{call adjust(?,?)}");
cstmt.setString(1, "kts8395");
cstmt.setFloat(2, 0.3f);
cstmt.executeUpdate();
} catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
} finally {
try {
if(rs != null) rs.close();
if(cstmt != null) cstmt.close();
if(con != null) con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
=======================================================
function 에서 Java로 cursor 넘기기 ORACLE
=========================
1. ORACLE 처리
=========================
create or replace package types
as
type cursorType is ref cursor;
end;
/
create or replace function sp_ListEmp return types.cursortype
as
l_cursor types.cursorType;
begin
open l_cursor for select ename, empno from emp order by ename;
return l_cursor;
end;
/
=========================
2. JAVA Program
=========================
import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;
public class ReturnCursor
{
public static void main (String args [])
throws SQLException, ClassNotFoundException
{
String driver_class = "oracle.jdbc.driver.OracleDriver";
String query = "begin ? := sp_listEmp; end;";
Connection conn;
Class.forName(driver_class);
conn = DriverManager.getConnection(connect_string, "id", "passwd");
CallableStatement cstmt = conn.prepareCall(query);
cstmt.registerOutParameter(1,OracleTypes.CURSOR);
cstmt.execute();
ResultSet rset = (ResultSet)cstmt.getObject(1);
while (rset.next ())
System.out.println( rset.getString (1) + "\t" + rset.getInt (2) );
cstmt.close();
}
}
========================================================================================
CallableStatementTest.java(CallableStatement 예제)
: CallableStatement는 SQL의 스토어드프로시저(Stored Procedure)를 실행시키기 위해
사용되는 인터페이스
import java.sql.*;
public class CallableStatementTest{
public static void main(String[] args){
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:dbdsn", "id", "password");
CallableStatement cs = con.prepareCall("{call myStoredProcedure(?,?,?)}");
cs.setInt(1,2);
cs.registerOutParameter(2, java.sql.Types.VARCHAR);
cs.registerOutParameter(3, java.sql.Types.INTEGER);
cs.execute();
System.out.println("*name : "+ cs.getString(2) +"*age : "+ cs.getInt(3));
cs.close();
con.close();
}catch(Exception e){System.out.println(e);}
}
}
/*
create or replace function employ_ename (e_empno in emp.empno%TYPE) return varchar2
is
e_ename
varchar2(10) := '홍길동';
begin
select ename into e_ename from emp
where empno = e_empno;
return e_ename;
exception
when no_data_found or too_many_rows then
return e_ename;
end;
*/
import java.sql.*;
public class TestCallable {
public static void main(String args[]) throws Exception {
//예외처리를 main(String args[]) throws Exception 처름 하지 마세요
//여기서는 흐름을 쉽게 보이기 위해서 사용한 것입니다.
String driverClass = "oracle.jdbc.driver.OracleDriver";
String Url = "jdbc:oracle:thin:@localhost:1521:sid";
String User = "scott";
String Pwd = "*****";
Class.forName(driverClass);
Connection conn = DriverManager.getConnection(Url,User,Pwd);
// call 함수명
String call = "{? = call employ_ename(?) }";
int result = 0;
CallableStatement c = conn.prepareCall(call);
// IN parameter설정
c.setInt(2, 7566);
// Out parameter의 Type설정
c.registerOutParameter(1, java.sql.Types.VARCHAR );
// CallableStatement실행
c.executeUpdate();
// Out parameter의 값을 얻고, 출력한다.
System.out.println("result : " + c.getString(1));
conn.close();
}
}
