Java 调用Oracle的PLSQL入门

1.建表

2.建立plsql–无返回结果create or replace procedure pro_insert_dept(v_deptno in number, v_dname in varchar2, v_loc in varchar2) isbegin insert into dept(deptno, dname, loc) values(v_deptno, v_dname, v_loc);end;–一个返回结果create or replace procedure pro_select_dept(v_deptno in number, v_dname out varchar2) asbegin select dname INTO v_dname FROM dept WHERE deptno = v_deptno;end;–结果集CREATE OR REPLACE PACKAGE deptPackage AS TYPE DEPT_CURSOR IS REF CURSOR;end;CREATE OR REPLACE PROCEDURE pro_select_list_dept(p_CURSOR out deptPackage.DEPT_CURSOR) ISBEGINOPEN p_CURSOR FOR SELECT * FROM dept;END;

3.java执行public class ProDetpTest {String driver = "oracle.jdbc.driver.OracleDriver";String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";Statement stmt = null;ResultSet rs = null;Connection conn = null;CallableStatement cstmt = null;CallableStatement proc = null;@Beforepublic void init() {try {Class.forName(driver);conn = DriverManager.getConnection(strUrl, "scott", "*****");} catch (Exception e) {e.printStackTrace();}}@Testpublic void proWithNoBack() {try {proc = conn.prepareCall("{call pro_insert_dept(?,?,?)}");proc.setInt(1, 70);proc.setString(2, "TestOne");proc.setString(3, "北京");proc.execute();} catch (SQLException ex2) {ex2.printStackTrace();} catch (Exception ex2) {ex2.printStackTrace();} finally {try {if (rs != null) {rs.close();if (stmt != null) {stmt.close();}if (conn != null) {conn.close();}}} catch (SQLException ex1) {}}}/** * 有返回值的存储过程(非列表) */@Testpublic void proWithBack() {try {proc = conn.prepareCall("{call pro_select_dept(?,?)}");proc.setInt(1, 50);proc.registerOutParameter(2, Types.VARCHAR);proc.execute();String testPrint = proc.getString(2);System.out.println("=name=is=" + testPrint);} catch (SQLException ex2) {ex2.printStackTrace();} catch (Exception ex2) {ex2.printStackTrace();} finally {try {if (rs != null) {rs.close();if (stmt != null) {stmt.close();}if (conn != null) {conn.close();}}} catch (SQLException ex1) {}}}@Testpublic void proWithList() {try {proc = conn.prepareCall("{call pro_select_list_dept(?)}");proc.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);proc.execute();rs = (ResultSet) proc.getObject(1);while (rs.next()) {//System.out.println(rs.getInt(1) + " " + rs.getString(2) + " " + rs.getString(3));System.out.println(rs.getInt("DEPTNO") + " " + rs.getString("DNAME") + " " + rs.getString("LOC"));}} catch (SQLException ex2) {ex2.printStackTrace();} catch (Exception ex2) {ex2.printStackTrace();} finally {try {if (rs != null) {rs.close();if (stmt != null) {stmt.close();}if (conn != null) {conn.close();}}} catch (SQLException ex1) {}}}

，午餐，晚餐。或许吃得不好，可是却依旧为对方擦去嘴角的油渍。