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) {
      }
     }
    } 

4、基本返回值存储过程示例

 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{

            }
        }
    }

5、集合返回值存储过程

 

 

//数据库端：
    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{
        }
       }
      }