关于Oracle存储过程的介绍,我就不多啰嗦了。
建立存储过程
- CREATE OR REPLACE PROCEDURE test_pro3(x OUT INT,y OUT int)
- IS
- BEGIN
- x := 20;
- y := 30;
- END;
查询存储过程:
- SQL> SELECT text FROM all_source WHERE TYPE = 'PROCEDURE' AND name = 'TEST_PRO3';
- TEXT
- --------------------------------------------------------------------------------
- PROCEDURE test_pro3(x OUT INT,y OUT int)
- IS
- BEGIN
- x := 20;
- y := 30;
- END;
- 已选择6行。
JAVA调用存储过程的code:
- package votory;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.*;
- import java.sql.ResultSet;
- public class ConneDB {
- public ConneDB() {
- }
- public static void main(String[] args) {
- String driver = "oracle.jdbc.driver.OracleDriver";
- String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:SID";
- Statement stmt = null;
- ResultSet rs = null;
- Connection conn = null;
- CallableStatement cstmt = null;
- try {
- Class.forName(driver);
- conn = DriverManager.getConnection(strUrl, "scott", "tiger");
- CallableStatement proc = null;
- proc = conn.prepareCall("{ call test_pro3(?,?)}");
- proc.registerOutParameter(1, Types.INTEGER);
- proc.registerOutParameter(2, Types.INTEGER);
- proc.execute();
- String test = proc.getString(1);
- String test2 = proc.getString(2);
- System.out.println(test + " " + test2);
- System.out.println("DONE..............");
- } 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) {
- }
- }
- }
- }
结果如下:
- 20 30
- DONE..............
如何定义和处理一个游标呢:
- CREATE OR REPLACE PACKAGE testpackage
- IS
- TYPE test_cursor IS REF CURSOR;
- END;
- CREATE OR REPLACE PROCEDURE test_pro4(p_cursor OUT testpackage.test_cursor)
- IS
- BEGIN
- OPEN p_cursor FOR SELECT * FROM a;
- END;
总算是搞明白一个简单例子。。。。。呵呵