Oracle PL/SQL – jak załadować zawartość tabeli do kolekcji PL/SQL

W tym poście przedstawię jak pobrać zawartość tabeli bazodanowej do kolekcji PL/SQL.

Pokażemy kilka z dostępnych możliwości wraz z kodem opartym o użytkownika HR.

Zaczniemy od zwięzłego przeglądu kolekcji, jakie mamy dostępne w Oracle:

Indeksowane przez BINARY_INTEGER – dostępne są wszystkie elementy w kolekcji z zakresu typu binary_integer. W momencie przypisywania do danego elementu kolekcji alokowana jest dla niego pamięć. Nie musi to być typ BINARY_INTEGER – jaki inny typ może zostać użyty opiszę w jednym z kolejnych postów.

TYPE nazwa IS TABLE OF typ INDEX BY binary_integer;

Zagnieżdżone: przed przypisaniem wartości do elementu kolekcji musi zostać zainicjalizowany – w innym wypadku będzie błąd. Elementy indeksowane są kolejnymi liczbami od 1.

TYPE nazwa IS TABLE OF typ;

VARRAY – o określonej maksymalnej długości. Podobnie jak zagnieżdżone wymagają
zainicjalizowania elementu przed jego przypisaniem. Elementy indeksowane są kolejnymi liczbami od 1 do rozmiaru maksymalnego.

TYPE nazwa IS VARRAY(n) OF typ;

Zatem do dzieła!

Do załadowania zawartości tabeli użyjemy kolekcji indeksowanych przez BINARY_INTEGER oraz kolekcji zagnieżdżonych.

1. Ładowanie przy użyciu kursorów

DECLARE
TYPE t_typ IS TABLE OF employees%ROWTYPE INDEX BY BINARY_INTEGER;
kolekcja t_typ;
i NUMBER:=0;
BEGIN
FOR rekord IN (SELECT * FROM employees) LOOP
  i:=i+1;
  kolekcja(i):=rekord;
END LOOP;
END;
DECLARE
TYPE t_typ IS TABLE OF employees%ROWTYPE;
kolekcja t_typ:=t_typ();
i NUMBER:=0;
BEGIN
FOR rekord IN (SELECT * FROM employees) LOOP
  i:=i+1;
  kolekcja.EXTEND;
  kolekcja(i):=rekord;
END LOOP;
END;

Jak widać w powyższym przykładzie – konieczna jest inicjalizacja oraz “poszerzanie” kolekcji przy użyciu extend.

2. Ładowanie przez przetwarzanie masowe – BULK COLLECT

DECLARE
  TYPE t_tablica IS TABLE OF employees%ROWTYPE;
  tablica t_tablica;
BEGIN
    SELECT *
    BULK COLLECT INTO tablica
    FROM employees;
END;

Jak widać nie ma konieczności inicjalizowania kolekcji

DECLARE
  TYPE t_tablica IS TABLE OF employees%ROWTYPE INDEX BY BINARY_INTEGER;
  tablica t_tablica;
BEGIN
    SELECT *
    BULK COLLECT INTO tablica
    FROM employees;
END;

Więcej o kolekcjach i przetwarzaniu masowym w kolejnych postach.

 

This entry was posted in Oracle, PL/SQL, PL/SQL Podstawy and tagged , , , , . Bookmark the permalink.

3 Responses to Oracle PL/SQL – jak załadować zawartość tabeli do kolekcji PL/SQL

  1. Czytelnik says:

    Chciałbym zapytać jak wyświetlić kolekcje..np podaje przyklad poniżej
    1) ładowanie przy użyciu kursorów:

    declare
    Type t_typ IS TABLE OF employees%ROWTYPE INDEX BY BINARY_INTEGER;
    kolekcja t_typ;
    i NUMBER:=0;
    begin
    for rekord in (select * from employees)
    loop
    i:=i+1;
    kolekcja(i):=rekord;
    end loop;

    --2) wyświetlenie kolekcji
    for k in kolekcja.first..kolekcja.last
    loop
    dbms_output.put_line(kolekcja(k)); //pojawił błąd 'wrong number or types of arguments in call to 'PUT_LINE''
    end loop;
    end;

    I pojawił się błąd, ze nie może wstawić kolekcje w put_line… moze trzeba jakos konwertować …?? nie wiem jak to
    zrobić?? Proszę o odpowiedz..

  2. Witam,

    Elementami kolekcji jest rekord przechowujący pola o nazwach zgodnych z nazwami kolumn w tabeli employees. Mówi o tym ta linijka:

    Type t_typ IS TABLE OF employees%ROWTYPE INDEX BY BINARY_INTEGER;

    Oznacza to, że aby wyświetlić zawartość kolekcji trzeba wyświetlać każde pole oddzielnie. Put_line nie jest w stanie wyświetlić rekordu, ponieważ nic nie wie na temat nazw jego pól. Możemy wyświetlić pola rekordów z kolekcji np. tak:


    for k in kolekcja.first..kolekcja.last
    loop
    dbms_output.put_line(kolekcja(k).employee_id);
    dbms_output.put_line(kolekcja(k).first_name);
    dbms_output.put_line(kolekcja(k).last_name);
    -- i tak dalej
    end loop;
    end;

Leave a Reply

Your email address will not be published.