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

程序员文章站 2022-05-10 16:12:15

...

--功能：采用存储过程、type组合来实现批量操作，以节省系统开销，提高效率。 --创建 Type bodies CREATE OR REPLACE TY

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