카테고리 없음

oracle plsql bulk collect

een 2020. 5. 21. 11:06
DECLARE
TYPE array_of_numbers IS varray(100) OF varchar2(20);
		
 cities_ids array_of_numbers;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Begin...');
  WITH tab1  AS (
            SELECT  'Stockholm' AS id FROM DUAL
  UNION ALL SELECT  'Tokyo'     AS id FROM DUAL
  UNION ALL SELECT  'Helsinki'  AS id FROM DUAL
  )
  select id BULK COLLECT into cities_ids from tab1;

    FOR i IN 1..cities_ids.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(cities_ids(i));
    END LOOP;

END;

https://blogs.oracle.com/oraclemagazine/bulk-processing-with-bulk-collect-and-forall

https://stackoverflow.com/questions/24488620/how-to-select-into-array-of-numbers-in-oracle-pl-sql