Oracle_12.1_Java开发2日通_2014.2(1)

Oracle12c支持JDK6和JDK7


oracle.jdbc,oracle.sql包


jdbc:oracle:thin:hr/hr@localhost:1521:oracle


默认一个Statement对象同时只能有一个ResultSet对象能被读取

因此要读取多个ResultSet要用多个Statement

ResultSet会被自动关闭,在那个Statement被关闭,被重新运行或多个结果集中查看下一个ResultSet时


SELECT * FROM Employees WHERE ID=xyz;//每次后面的字串改变时,都会重新编译一次Statement

// JDBC syntax
CallableStatement cs1 = conn.prepareCall
                        ( "{call proc (?,?)}" ) ; // stored proc
CallableStatement cs2 = conn.prepareCall
                        ( "{? = call func (?,?)}" ) ; // stored func
// Oracle PL/SQL block syntax
CallableStatement cs3 = conn.prepareCall
                        ( "begin proc (?,?); end;" ) ; // stored proc
CallableStatement cs4 = conn.prepareCall
                        ( "begin ? := func(?,?); end;" ) ; // stored func

 

create or replace function foo (val1 char)
return char as
begin
return val1 || 'suffix';
end;

 

CallableStatement cs = conn.prepareCall ("begin ? := foo(?); end;");
cs.registerOutParameter(1,Types.CHAR);
cs.setString(2, "aa");
cs.executeUpdate();
String result = cs.getString(1);

 

CREATE OR REPLACE PROCEDURE INSERT_EMPLOYEE (p_first_name  employees.first_
name%type, 
  p_last_name    employees.last_name%type,
  p_email        employees.email%type,
  p_phone_number employees.phone_number%type,
  p_job_id       employees.job_id%type,
  p_salary       employees.salary%type
) 
AS
BEGIN
  INSERT INTO Employees VALUES (EMPLOYEES_SEQ.nextval, p_first_name , 
    p_last_name , p_email , p_phone_number, SYSDATE, p_job_id, 
    p_salary,.30,100,80);
END insert_employee;

 

public String addEmployeeSP(String first_name, String last_name, 
  String email, String phone_number, String job_id,
  int salary) throws SQLException {
 
  try {
    getDBConnection(); 
    sqlString = "begin hr.insert_employee(?,?,?,?,?,?); end;";
    CallableStatement callstmt = conn.prepareCall(sqlString);
    callstmt.setString(1, first_name);
    callstmt.setString(2, last_name);
    callstmt.setString(3, email);
    callstmt.setString(4, phone_number);
    callstmt.setString(5, job_id);
    callstmt.setInt(6, salary);
    System.out.println("\nInserting with stored procedure: " + 
                       sqlString);
 
    callstmt.execute();
    return "success"; 
  }
  catch ( SQLException ex ) {
    System.out.println("Possible source of error: Make sure you have created the 
stored procedure"); 
    logException( ex ); 
    return "failure";
  }
}