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

最新推荐文章于 2024-04-18 17:39:04 发布

「已注销」

最新推荐文章于 2024-04-18 17:39:04 发布

阅读量451

点赞数

本文链接：https://blog.csdn.net/gcg0036/article/details/41728185

版权

本文介绍Oracle12c环境下使用JDK6和JDK7进行JDBC编程的方法，包括如何处理多个ResultSet对象及CallableStatement的使用示例。通过具体的PL/SQL块语法和存储过程调用，展示如何利用Oracle JDBC驱动实现高效的数据交互。

摘要由CSDN通过智能技术生成

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";
  }
}