CREATE OR REPLACE
TYPE US_SLNB.VARCHAR_ARRAY AS TABLE OF VARCHAR(100)
;
/*------------------------------------------------------------------------------
------------------------------------------------------------------------------*/
CREATE OR REPLACE function US_SLNB.TEST_F1 (p_strings VARCHAR_ARRAY)
RETURN NUMBER
IS
PRAGMA AUTONOMOUS_TRANSACTION;
V_STR VARCHAR2(100) := '';
V_WH_CD VARCHAR2(100) := '';
V_RESULT_QTY NUMBER:= 0;
begin
for n in 1..p_strings.count
LOOP
V_WH_CD := p_strings(n);
V_STR := V_STR || V_WH_CD || ',';
DBMS_OUTPUT.PUT_LINE('V_STR =' || V_STR);
end loop;
DBMS_OUTPUT.PUT_LINE('V_STR =' || SUBSTR(V_STR, 1, LENGTH(V_STR)-2));
V_STR := SUBSTR(V_STR, 1, LENGTH(V_STR)-1) ;
DBMS_OUTPUT.PUT_LINE('V_STR ====[' || V_STR || ']');
WITH STOC AS (
SELECT 'a' AS CD, 10 AS QTY FROM DUAL
UNION ALL SELECT 'b' AS CD, 20 AS QTY FROM DUAL
UNION ALL SELECT 'c' AS CD, 40 AS QTY FROM DUAL
)
SELECT SUM(QTY)
INTO V_RESULT_QTY
FROM STOC
WHERE CD IN
(select regexp_substr(V_STR,'[^,]+', 1, level) from dual
connect by regexp_substr(V_STR, '[^,]+', 1, level) is not null)
;
DBMS_OUTPUT.PUT_LINE('V_RESULT_QTY =' || V_RESULT_QTY);
return V_RESULT_QTY;
end;
/*------------------------------------------------------------------------------
------------------------------------------------------------------------------*/
select TEST_F1(VARCHAR_ARRAY('a', 'c')) from dual;