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

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

数据库  /  管理员 发布于 2020-03-08 13:33:15   23

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 syntaxCallableStatement cs1 = conn.prepareCall                        ( "{call proc (?,?)}" ) ; // stored procCallableStatement cs2 = conn.prepareCall                        ( "{? = call func (?,?)}" ) ; // stored func// Oracle PL/SQL block syntaxCallableStatement cs3 = conn.prepareCall                        ( "begin proc (?,?); end;" ) ; // stored procCallableStatement cs4 = conn.prepareCall                        ( "begin ? := func(?,?); end;" ) ; // stored func

 

create or replace function foo (val1 char)return char asbeginreturn 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) ASBEGIN  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";  }}