Java 调用Oracle的PLSQL入门

1.建表

http://blog.csdn.net/shihuacai/article/details/8889205

2.建立plsql

--无返回结果
create or replace procedure pro_insert_dept(v_deptno in number, v_dname in varchar2, v_loc in varchar2) is
begin
  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)  as
begin
   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) IS
BEGIN
    OPEN 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;

	@Before
	public void init() {
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(strUrl, "scott", "*****");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Test
	public 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) {

			}
		}
	}

	/**
	 * 有返回值的存储过程(非列表)
	 */
	@Test
	public 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) {
			}
		}
	}

	@Test
	public 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) {
			}
		}
	}