Java初探Oracle(轉)

import java.sql.*;
import oracle.jdbc.driver.*;

public class BB {
private static Connection conn;
public BB() {
}

public static void main(String[] args){
Statement stmt;
ResultSet rst;
PreparedStatement pstmt,pstmt1;
CallableStatement cstmt,cstmt1,cstmt2,cstmt3;

/*
new oracle.jdbc.OracleDriver();
jdbc:oracle:thin:@hostname:port:database
*/

try{
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
conn = DriverManager.getConnection("jdbc:oracle:thin:@ruang:1521:orcl","xling","fp51gfa");

stmt = conn.createStatement();
rst = stmt.executeQuery("SELECT COUNT(*) FROM XLING_APP_GUID");
if(rst.next())
System.out.println(rst.getInt(1));

rst.close();

pstmt = conn.prepareStatement("UPDATE XLING_USER SET ID = ? WHERE ID = ?");
pstmt.setString(1,"xling");
pstmt.setString(2,"AAA");
pstmt.executeUpdate();

stmt.execute("DELETE FROM XLING_USER WHERE ID = 'fairy'");

pstmt1 = conn.prepareStatement("INSERT INTO XLING_USER (ID,DESP,PWD) VALUES (?,?,?)");
pstmt1.setString(1,"fairy");
pstmt1.setString(2,"王濤");
pstmt1.setString(3,"werewi");
pstmt1.execute();

/*
PL/SQL塊語法：注意，要完全符合PL/SQL語法，Oracle的VARCHAR2不是標準的SQL型別，所以使用Types.CHAR
*/
cstmt = conn.prepareCall("begin ? := xling_pkg_tools.rc4(?); end;");
cstmt.registerOutParameter(1,Types.CHAR);
cstmt.setString(2,"fp51gfa");
cstmt.execute();
System.out.println(cstmt.getString(1));

/*
SQL-92語法，呼叫Oracle裡的函式,注意：call只能小寫
*/
cstmt1 = conn.prepareCall("{? = call XLING_PKG_TOOLS.RC4(?)}");
cstmt1.registerOutParameter(1,Types.CHAR);
cstmt1.setString(2,"xling");
cstmt1.execute();
System.out.println(cstmt1.getString(1));

/*
用SQL-92格式呼叫Oracle裡的過程，第二個引數為OUT型別的引數
CREATE OR REPLACE PROCEDURE PRO_TEST(I_DATE_STRING IN VARCHAR2,O_DAYS OUT NUMBER) AS
BEGIN
SELECT SYSDATE - TO_DATE(I_DATE_STRING,'YYYYMMDD') INTO O_DAYS FROM DUAL;
END;
*/
cstmt2 = conn.prepareCall("{call PRO_TEST(?,?)}");
cstmt2.setString(1,"20060901");
cstmt2.registerOutParameter(2,Types.INTEGER);
cstmt2.execute();
System.out.println(cstmt2.getInt(2));

/*
用PL/SQL格式呼叫Oracle裡的過程
*/
cstmt3 = conn.prepareCall("begin PRO_TEST(?,?); END;");
cstmt3.setString(1,"20060101");
cstmt3.registerOutParameter(2,Types.INTEGER);
cstmt3.execute();
System.out.println(cstmt3.getInt(2));

cstmt3.close();
cstmt2.close();
cstmt1.close();
cstmt.close();
pstmt.close();
pstmt1.close();
stmt.close();
conn.close();
System.out.println("here");
}catch(SQLException e){
//e.printStackTrace();
System.out.println("ErrorCode:" + e.getErrorCode());//錯誤號
System.out.println("Message:" + e.getMessage());
StackTraceElement[] el = e.getStackTrace();
for(int i = 0;i System.out.println("錯誤行號：" + el[i].getLineNumber());
System.out.println("所在方法：" + el[i].getMethodName());
System.out.println("所在類：" + el[i].getClassName());
System.out.println("所在檔案：" + el[i].getFileName());
System.out.println();
}
}
}
}