CREATE OR REPLACE TYPE MAP_TYPE AS OBJECT(COL nvarchar2(36),VAL nvarchar2(36))
CREATE OR REPLACE TYPE MAP_TABLE AS TABLE OF MAP_TYPE
CREATE OR REPLACE TYPE ID_ARRAY AS TABLE OF NVARCHAR2(36)
create or replace procedure MODEL_ORACLE_PROCEDURE(
I_ARR IN id_array,
O_RESULT OUT VARCHAR2, O_MESSAGE OUT VARCHAR2) is
V_LOGGER_STEP NUMBER default 0;
V_MATCH VARCHAR(36);
V_FIELD VARCHAR(36);
V_MAP MAP_TABLE:=MAP_TABLE();
V_MAP_INDEX NUMBER default 1;
begin
V_LOGGER_STEP := '1';
O_MESSAGE := '开始遍历要ARR';
FOR i IN I_ARR.First .. I_ARR.LAST LOOP
V_FIELD:=I_ARR(i);
BEGIN
SELECT FIELD1
INTO V_MATCH
FROM TABLE
WHERE FIELD2 = V_FIELD;
EXCEPTION
WHEN NO_DATA_FOUND THEN
CONTINUE;
END;
V_MAP.Extend;--插入第一条数据前也要先扩充数组
V_MAP(V_MAP_INDEX):=MAP_TYPE(V_FIELD,V_MATCH);
V_MAP_INDEX:=V_MAP_INDEX+1;
END LOOP;
IF V_MAP.COUNT<5 THEN
O_RESULT := '1';
RETURN;
ELSIF V_MAP.COUNT=5 THEN
O_RESULT := '0';
RETURN;
ELSE
UPDATE TABLE SET FIELD1=V_MAP(5).VAL
WHERE FIELD2 IN (SELECT COL FROM TABLE(V_MAP));
END IF;
O_RESULT := '1';
RETURN;
EXCEPTION
WHEN OTHERS THEN
O_RESULT := '0';
O_MESSAGE := O_MESSAGE || ' ';
P_CM_LOGGER('MODEL_ORACLE_PROCEDURE',
SYSDATE,
V_LOGGER_STEP,
'',
O_MESSAGE,
'');
RETURN;
end MODEL_ORACLE_PROCEDURE;
ORACLE存储过程范例
最新推荐文章于 2022-12-05 22:33:37 发布