ORACLE存储过程范例

CREATE OR REPLACE TYPE MAP_TYPE AS OBJECT(COL nvarchar2(36),VAL nvarchar2(36))CREATE OR REPLACE TYPE MAP_TABLE AS TABLE OF MAP_TYPECREATE 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;