--功能:采用存储过程、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
Oracle编写带数组参数的存储过程
最新推荐文章于 2023-05-17 16:55:34 发布
![](https://img-home.csdnimg.cn/images/20240711042549.png)