mysql的存储过程的数组参数_Oracle编写带数组参数的存储过程

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

，

本文原创发布php中文网，转载请注明出处，感谢您的尊重！