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

--功能：采用存储过程、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