Oracle编写带数组参数的存储过程

scvmm

• [Shell] 纯文本查看 复制代码

  --功能：采用存储过程、type组合来实现批量操作，以节省系统开销，提高效率。    
    
          
          
  --创建 Type bodies    
  CREATE OR REPLACE TYPE TYPE_ARRAY AS OBJECT    
  (    
    ID     NUMBER(10),    
    REMARK VARCHAR2(10)    
  )    
  --创建 Types    
  CREATE OR REPLACE TYPE TYPE_ARRAY_TBL AS TABLE OF TYPE_ARRAY    
  --创建表    
  CREATE TABLE T_TEMP(ID NUMBER(10) NOT NULL, REMARK NUMBER(10))    
  --创建存储过程    
   CREATE OR REPLACE PROCEDURE PROC_ARRAY_PARAM(TYPE_OBJECT IN TYPE_ARRAY_TBL) IS  
         
   BEGIN  
     INSERT INTO T_TEMP  
       (ID, REMARK)  
       SELECT ID, REMARK  
         FROM THE (SELECT CAST(TYPE_OBJECT AS TYPE_ARRAY_TBL) FROM DUAL);  
         
     FOR I IN 1 .. TYPE_OBJECT.COUNT LOOP  
       DELETE FROM T_TEMP WHERE ID = TO_NUMBER(TYPE_OBJECT(I));  
     END LOOP;  
     COMMIT;  
   END;  
         
   END PROC_ARRAY_PARAM;  
    
    
  --创建包    
  CREATE OR REPLACE PACKAGE PKG_PARAM AS    
    TYPE ARRAY_PARAMS IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER; --先定义包，这个就相当于一个数组    
    PROCEDURE PROC_PARAM(PARAMS IN ARRAY_PARAMS);    
  END PKG_PARAM;    
  --创建包体    
  CREATE OR REPLACE PACKAGE BODY PKG_PARAM AS    
    PROCEDURE PROC_PARAM(PARAMS IN ARRAY_PARAMS) AS    
      I NUMBER := 1; --这个可以不写    
    BEGIN    
      SAVEPOINT SP1;    
      FOR I IN 1 .. PARAMS.COUNT LOOP    
        DELETE FROM T_TEMP WHERE ID = TO_NUMBER(PARAMS(I));    
      END LOOP;    
      COMMIT;    
    EXCEPTION    
      WHEN OTHERS THEN    
        ROLLBACK TO SAVEPOINT SP1;    
    END PROC_PARAM;    
  END PKG_PARAM;  

    
  

Java调用存储过程：

• [Shell] 纯文本查看 复制代码

  package com.test.oracle;  
    
  import java.sql.Connection;  
    
  import java.sql.DriverManager;  
    
  import java.sql.PreparedStatement;  
    
  /** 调用存储过程 */  
  public class ProcTest {  
        
      public static void main(String[] args) {  
    
          try {  
    
              Class.forName("oracle.jdbc.driver.OracleDriver");  
    
              String url = "jdbc:oracle:thin:@192.168.1.1:1521:orcl";  
    
              Connection con = DriverManager.getConnection(url, "sys", "sys");  
    
              PreparedStatement pstmt = null;  
    
              String sql = "{call PROC_ARRAY_PARAM(?)}";  
    
              pstmt = con.prepareCall(sql);  
    
              Object[][] object1 = new Object[10][5];  
    
              int max = 3615142;// 由于表有索引  
    
              for (int i = 0; i < 10; i++) {  
    
                  object1[i][0] = ++max;  
    
                  object1[i][1] = 222;  
    
                  object1[i][2] = 333;  
    
                  object1[i][3] = 444;  
    
                  object1[i][4] = 555;  
    
              }  
    
              oracle.sql.ArrayDescriptor desc = oracle.sql.ArrayDescriptor.createDescriptor("TYPE_ARRAY_TBL", con);  
    
              oracle.sql.ARRAY array = new oracle.sql.ARRAY(desc, con, object1);  
    
              pstmt.setArray(1, array);  
    
              pstmt.executeUpdate();  
    
          } catch (Exception e) {  
    
              e.printStackTrace();  
    
          }  
    
      }  
  }  

  
  

0

有竞争才有进步嘛

dickrong

与时俱进，你我共赴高潮!

永不落伍

看帖回帖是美德！:lol

julley

自从我变成了狗屎,就再也没有人踩在我头上了。

king71

真是 收益 匪浅

jinquan26

昨天，系花对我笑了一下，乐得我晚上直数羊，一只羊，两只羊，三只羊……