Oracle高級編程

Oracle高級編程

Oracle的高級編程主要是涉及到數據庫的十大對象的使用，在這裡，和大家分享一下我在學習這些技能時的一些筆記。希望對大家有所幫助！

閱讀本文之前，您還可以查閱我之前關於數據庫方面的介紹：

Oracle DDL基本操作
Oracle基本數據類型
Oracle數據庫備份與恢復

接下來介紹一下 Oracle的高級編程。

1、存儲過程返回單值參數

*call + 包名 + 存儲過程名(傳入、傳出值)
String str = "{call smsbusiness.deleteZhZMember(?,?,?)}";


*建立連接
Connection conn = null;
conn = DriverManager.getConnection();

*使用java.sql.*類
CallableStatement cs = conn.prepareCall(str);

*傳入參數
cs.setInt(1,id);
cs.setInt(2,-2);

*設置out值
cs.registerOutputParameter(3,Types.NUMBERIC);

*執行
cs.excuse();

2、存儲過程返回多值參數

*包的建立
CREATE OR REPLACE PACKAGE TESTPACKAGE AS
TYPE Test_CURSOR IS REF CURSOR;
END TESTPACKAGE;

*游標的建立
CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR) IS
BEGIN
OPEN p_CURSOR FOR SELECT * FROM grade;
END TESTC;


*Java的調用
CallableStatement cs = conn.prepareCall(sqlStr);
ResultSet rs = (ResultSet)cs.getObject(2);

3、無返回值存儲過程示例

 CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) AS
    BEGIN
      INSERT INTO HYQ.B_ID (I_ID,I_NAME) VALUES (PARA1, PARA2);
    END TESTA;

package com.hyq.src; 
   import java.sql.*;
   import java.sql.ResultSet; 

   public class TestProcedureOne {
     public TestProcedureOne() {

     }

     public static void main(String[] args ){

       String driver = "oracle.jdbc.driver.OracleDriver";
       String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521: hyq ";
       Statement stmt = null;
       ResultSet rs = null;
       Connection conn = null;
       CallableStatement cstmt = null;
 
       try {
          Class.forName(driver);
          conn = DriverManager.getConnection(strUrl, " zl ", "123456");
          CallableStatement proc = null; //創建執行存儲過程的對象
          proc = conn.prepareCall("{ call HYQ.TESTA(?,?) }"); //設置存儲過程 call為關鍵字.
          proc.setString(1, "100"); //設置第一個輸入參數
          proc.setString(2, "TestOne");//設置第二個輸入參數
          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) {
      }
     }
    } 

 CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2) AS
   BEGIN
       SELECT INTO PARA2 FROM TESTTB WHERE I_ID= PARA1;
   END TESTB;

 //Java代碼：
    package com.hyq.src;
    public class TestProcedureTWO {
        public TestProcedureTWO() {
        }

        public static void main(String[] args ){
            String driver = "oracle.jdbc.driver.OracleDriver";
            String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:zl";
            Statement stmt = null;
            ResultSet rs = null;
            Connection conn = null;

            try {
                Class.forName(driver);
                conn = DriverManager.getConnection(strUrl, " hyq ", " hyq ");
                CallableStatement proc = null;
                proc = conn.prepareCall("{ call zl.TESTB(?,?) }"); //設置存儲過程
                proc.setString(1, "100");//設置第一個參數輸入參數
                proc.registerOutParameter(2, Types.VARCHAR);//第二個參數輸出參數,是VARCHAR類型的
                proc.execute();//執行
                String testPrint = proc.getString(2);//獲得輸出參數
                System.out.println("=testPrint=is="+testPrint);
            }catch(Exception e){
	
            }finally{

            }
        }
    }

//數據庫端：
    CREATE OR REPLACE PACKAGE TESTPACKAGE  AS
        TYPE Test_CURSOR IS REF CURSOR;
    end TESTPACKAGE;

    CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR) IS
    BEGIN
        OPEN p_CURSOR FOR SELECT * FROM HYQ.TESTTB;
    END TESTC;

  //Java代碼：
    package com.hyq.src;
    import java.sql.*;
    import java.io.OutputStream;
    import java.io.Writer;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import oracle.jdbc.driver.*;  
 
    public class TestProcedureTHREE {
        public TestProcedureTHREE() {
        }

    public static void main(String[] args ){
        String driver = "oracle.jdbc.driver.OracleDriver";
        String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:zl";
        Statement stmt = null;
        ResultSet rs = null;
        Connection conn = null;
 
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(strUrl, "hyq", "hyq");
 
            CallableStatement proc = null;
            proc = conn.prepareCall("{ call hyq.testc(?) }"); //存儲過程 hyq包下的
            proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);//設置輸出參數是一個游標.第一個參數,游標類型
            proc.execute();//執行
            rs = (ResultSet)proc.getObject(1); //獲得第一個參數是一個游標,轉化成ResultSet類型
 
            while(rs.next()) //獲得數據
            {
                System.out.println(rs.getString(1) +rs.getString(2));
             }
        }catch (Exception ex2) {
            ex2.printStackTrace();
        }finally{
        }
       }
      }