Posted on 2007-07-17 22:00
kooyee 阅读(766)
评论(0) 编辑 收藏 所属分类:
Java 、
Database数据库技术
import java.sql.*;
public class StProcExample 
{
public static void main(String[] args)
throws SQLException {
int ret_code;
Connection conn = null;
try {
//Load and register Oracle driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
//Establish a connection
conn = DriverManager.getConnection("jdbc:oracle:thin:@training:1521:
Oracle", "oratest", "oratest");
int i_deptno = 10;
CallableStatement pstmt = conn.prepareCall("{call p_highest_
paid_emp(?,?,?,?)}");
pstmt.setInt(1, i_deptno);
pstmt.registerOutParameter(2, Types.INTEGER);
pstmt.registerOutParameter(3, Types.VARCHAR);
pstmt.registerOutParameter(4, Types.FLOAT);
pstmt.executeUpdate();
int o_empno = pstmt.getInt(2);
String o_ename = pstmt.getString(3);
float o_sal = pstmt.getFloat(4);
System.out.print("The highest paid employee in dept "
+i_deptno+" is: "+o_empno+" "+o_ename+" "+o_sal);
pstmt.close();
conn.close();
} catch (SQLException e) {ret_code = e.getErrorCode();
System.err.println(ret_code + e.getMessage()); conn.close();}
}
}
在database中调用function
Select ValidWorkingDay(param1,prasm2) as IsWorking from dual
Select {t '14:20:50'} as ThisDate from dual
Select UCA_VALIDATE('123456','WW','1') as Answer from dual
或者
SELECT ename FROM emp WHERE hiredate = {t '12:00:00'}"
Update语句
For example, instead of using the fn keyword in embedded SQL92 syntax:
Statement stmt = conn.createStatement ();
stmt.executeUpdate("UPDATE emp SET ename = {fn CONCAT('My', 'Name')}");
Use Oracle SQL syntax:
stmt.executeUpdate("UPDATE emp SET ename = CONCAT('My', 'Name')");
Function Call Syntax
Oracle's JDBC drivers support the following procedure and function call syntax:
Procedure calls (without a return value):
{ call procedure_name (argument1, argument2,...) }
Function calls (with a return value):
{ ? = call procedure_name (argument1, argument2,...) }
当用于call Function时，在等号= 前的？是statment的第一个parameter. 是out出来的值