CallableStatement cs;
try {
// Call a function with no parameters; the function returns a VARCHAR
// Prepare the callable statement
cs = connection.prepareCall("{? = call myfunc}");
// Register the type of the return value
cs.registerOutParameter(1, i);
// Execute and retrieve the returned value
cs.execute();
String retValue = cs.getString(1);
// Call a function with one IN parameter; the function returns a VARCHAR
cs = connection.prepareCall("{? = call myfuncin(?)}");
// Register the type of the return value
cs.registerOutParameter(1, Types.VARCHAR);
// Set the value for the IN parameter
cs.setString(2, "a string");
// Execute and retrieve the returned value
cs.execute();
retValue = cs.getString(1);
// Call a function with one OUT parameter; the function returns a VARCHAR
cs = connection.prepareCall("{? = call myfuncout(?)}");
// Register the types of the return value and OUT parameter
cs.registerOutParameter(1, Types.VARCHAR);
cs.registerOutParameter(2, Types.VARCHAR);
// Execute and retrieve the returned values
cs.execute();
retValue = cs.getString(1); // return value
String outParam = cs.getString(2); // OUT parameter
// Call a function with one IN/OUT parameter; the function returns a VARCHAR
cs = connection.prepareCall("{? = call myfuncinout(?)}");
// Register the types of the return value and OUT parameter
cs.registerOutParameter(1, Types.VARCHAR);
cs.registerOutParameter(2, Types.VARCHAR);
// Set the value for the IN/OUT parameter
cs.setString(2, "a string");
// Execute and retrieve the returned values
cs.execute();
retValue = cs.getString(1); // return value
outParam = cs.getString(2); // IN/OUT parameter
} catch (SQLException e) {
}
StoredFunction.java
package func;
import java.sql.*;
public class StoredFunction {
private static String driver = "oracle.jdbc.OracleDriver";
private static String url = "jdbc:oracle:thin:@localhost:1521:orcl";
public static void main(String[] args){
//func01();
//func02();
//func03();
func04();
}
public static Connection getConnection(){
try{
Class.forName(driver);
return DriverManager.getConnection(url,"scott","tiger");
}catch(Exception e){e.printStackTrace();}
return null;
}
public static void func01(){
Connection conn = getConnection();
try{
Statement stmt = conn.createStatement();
//파라미터 없는 함수 선언 및 실행
String func = " create or replace function func01 "+
"return varchar2 "+
"is "+
"name varchar2(20);"+
"begin "+
"select ename into name from emp where empno=7369; "+
"return name; "+
"end func01;";
// SQLPlus에서 위처럼 생성된 함수는 일반 SQL문장에서 오라클 함수처럼 사용될 수 있다. 아래의 예 참조
// select func01 from dual;
stmt.executeUpdate(func);
stmt.close();
System.out.println("함수 저장성공");
CallableStatement cstmt = conn.prepareCall("{?=call func01 }");
cstmt.registerOutParameter(1, Types.VARCHAR);
cstmt.executeQuery();
String ename = cstmt.getString(1);
System.out.println("사원이름: "+ename);
close(conn);
}catch(Exception e){e.printStackTrace();}
}
public static void func02(){
Connection conn = getConnection();
try{
Statement stmt = conn.createStatement();
// 파라미처를 한개 가진 함수 선언
String func = " create or replace function func01(num number)"+
"return varchar2 "+
"is "+
"name varchar2(20);"+
"begin "+
"select ename into name from emp where empno=num; "+
"return name; "+
"end func01;";
// SQLPlus에서 실행하려면.... select func01(7369) from dual;
stmt.executeUpdate(func);
stmt.close();
System.out.println("함수 저장성공");
CallableStatement cstmt = conn.prepareCall("{?=call func01(?)}");
cstmt.registerOutParameter(1, Types.VARCHAR);
cstmt.setInt(2, 7369);
cstmt.executeQuery();
String ename = cstmt.getString(1);
System.out.println("사원이름: "+ename);
close(conn);
}catch(Exception e){e.printStackTrace();}
}
public static void func03(){
Connection conn = getConnection();
try{
Statement stmt = conn.createStatement();
// 파라미처를 2개 가진 함수 선언
String func = " create or replace function func01(num number, p_job OUT varchar2)"+
"return varchar2 "+
"is "+
"name varchar2(20);"+
"begin "+
"select ename, job into name, p_job from emp where empno=num; "+
"return name; "+
"end func01;";
stmt.executeUpdate(func);
stmt.close();
System.out.println("함수 저장성공");
CallableStatement cstmt = conn.prepareCall("{?=call func01(?,?)}");
cstmt.registerOutParameter(1, Types.VARCHAR);
cstmt.setInt(2, 7369);
cstmt.registerOutParameter(3, Types.VARCHAR);
cstmt.executeQuery();
String ename = cstmt.getString(1);
System.out.println("사원이름: "+ename);
String job = cstmt.getString(3);
System.out.println("직무 : "+job);
close(conn);
}catch(Exception e){e.printStackTrace();}
}
public static void func04(){
Connection conn = getConnection();
try{
Statement stmt = conn.createStatement();
// IN OUT 파라미처를 한개 가진 함수 선언
String func = " create or replace function func01(str IN OUT varchar2) "+
"return number "+
"is "+
"salary number; "+
"begin "+
"select sal,job into salary, str from emp where ename=str; "+
"return salary; "+
"end func01; ";
stmt.executeUpdate(func);
stmt.close();
System.out.println("함수 저장성공");
CallableStatement cstmt = conn.prepareCall("{?=call func01(?)}");
cstmt.registerOutParameter(1, Types.VARCHAR);
cstmt.setString(2, "SMITH");
cstmt.registerOutParameter(2, Types.VARCHAR);
cstmt.executeQuery();
int salary = cstmt.getInt(1);
System.out.println("급여: "+salary);
String job = cstmt.getString(2);
System.out.println("직무 : "+job);
close(conn);
}catch(Exception e){e.printStackTrace();}
}
public static void close(Connection conn)throws Exception{
if(conn!=null)conn.close();
}
}