sobota, 20 listopada 2010

Kurs Oracle PL/SQL. Instrukcje SQL w PL/SQL


Stosowanie zapytań z grupy DML w programach PL/SQL niewiele różni się od standardowych zapytań SQL. Obowiązuje tutaj jednak szereg zasad, wykonanie niektórych czynności dostępnych w SQL jest tutaj niemożliwe. O wszystkich ograniczeniach oraz korzyściach wynikających z zasad przyjętych dla języka PL/SQL będę pisał stopniowo przy okazji omawiania kolejnych rodzajów zapytań.
Polecenie INSERT w PL/SQL nie różni się od jego standardowej formy,jednak mamy tutaj możliwość podstawiania zmiennych wartości do zapytania. Na poniższej ilustracji prezentuję instrukcję INSERT z wykorzystaniem zmiennych. Musimy pamiętać, by podstawiane zmienne były takiego typu jak kolumna do której wstawiamy dane, lub przynajmniej typu konwertowalnego na typ kolumny. 



W PL/SQL istnieje opcjonalna klauzula RETURNING INTO pozwalająca na zapisanie do zmiennej wartości pochodzących z rekordu wstawianego przez polecenie INSERT. Taka możliwość staje się bardzo użyteczna, gdy zechcemy uzyskać ID właśnie wstawionego rekordu.




W powyższym przykładzie wstawiłem nowy wiersz do tabeli jobs, a przy pomocy klauzuli RETURNING INTO uzyskałem ID wstawionego wiersza. Wartość ID została przypisana do zmiennej ID której typ został zdeklarowany na podstawie typu kolumny job_id z tabeli jobs. Taka możliwość nabiera ogromnego znaczenia, jeśli wartość wstawiana do kolumny klucza głównego pochodzi z sekwencji, a mamy zamiar operować na właśnie wstawionych danych.
Widoczna na przykładzie instrukcja dbms_output.put_line to w rzeczywistości procedura put_line zawierająca się w pakiecie dbms_output a służąca do wypisywania danych na ekran.


Na poniższej ilustracji widać efekt działania tej procedury. Aby włączyć widok na którym widoczne będą komunikaty takie jak poniższy, należy przejść do menu „View” i kliknąć DBMS_Output, a następnie w widocznym niżej oknie kliknąć krzyżyk i wybrać właściwe połączenie.






 

Zapytania typu SELECT


(!) Przed przejściem do dalszego opisu warto zapamiętać bardzo istotne zasady dotyczące stosowania zapytania SELECT w PL/SQL.

  • Zapytanie musi zwracać zawsze dokładnie jeden wiersz z bazy.
  • W zapytaniu stosujemy dodatkową klauzulę INTO z listą zmiennych do których przypisujemy wartości z pól wyników
  • Lista zmiennych w klauzuli INTO musi być równa liczbie kolumn zwracanych w wyniku zapytania. Takie same muszą być również typy kolumn i zmiennych według kolejności.


Do tej pory korzystając z SQL otrzymywaliśmy informacje w postaci wyniku na konsoli. W PL/SQL tego typu konsoli nie ma. Jest konsola na którą możemy wypisać informacje, ale musimy w tym celu skorzystać z pakietu dbms_output. Wynik możemy natomiast przypisać do zmiennych, przetworzyć i wykonać inne czynności czego nie mogliśmy zrobić z zwykłym SQL.
Aby dane zwrócone w wyniku zapytania przypisać do zmiennych, musimy wykorzystać klauzulę INTO. Pamiętając o zasadach wypisanych powyżej tego akapitu , po wymienieniu kolumn z tabeli wymieniamy zmienne do których wynik ma zostać zapisany.



W podobny sposób możemy przypisać wynik do zmiennej utworzonej przy pomocy omawianego wcześniej atrybutu %ROWTYPE.

 


Możemy również pobrać wartość wiersza, przetworzyć ją a następnie wrzucić z powrotem do tabeli w jednym bloku PL/SQL. Widać to na poniższym przykładzie. Musiałem zmienić employee_ID- ponieważ to pole jest kluczem głównym dla tabeli employees, oraz pole email, ponieważ na kolumnę email w tabeli employees nałożony jest constraint UNIQUE.






Ten temat omawiam na poniższych szkoleniach:
• Programowanie w PL/SQL
• Podstawy SQL i PL/SQL
Możesz w nich uczestniczyć, a jako czytelnik tego bloga otrzymasz 10% zniżki - poinformuj o tym fakcie konsultanta.

2 komentarze:

  1. Mam problem z wyświetleniem wszystkich wierszy z tabeli JOBS ze schematu użytkownika HR.
    Będę wdzięczny za pomoc.

    CREATE OR REPLACE PROCEDURE P IS
    R NUMBER; -- przechowuje ilość wierszy
    I NUMBER; -- przechowuje z rownum
    W NUMBER := 1; -- licznik w pętli
    JI JOBS.JOB_ID%TYPE;
    JT JOBS.JOB_TITLE%TYPE;
    MINS JOBS.MIN_SALARY%TYPE;
    MAXS JOBS.MAX_SALARY%TYPE;

    BEGIN
    SELECT COUNT(*) INTO R FROM JOBS;
    DBMS_OUTPUT.PUT_LINE(R||' WIERSZY w tabeli JOBS');
    WHILE W != R LOOP
    SELECT JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY,ROWNUM INTO JI,JT,MINS,MAXS,I FROM JOBS WHERE ROWNUM=W;
    DBMS_OUTPUT.PUT_LINE(JI|| ' ' || JT || ' ' || MINS || ' ' ||MAXS );
    DBMS_OUTPUT.PUT_LINE(W);
    W:=W+1;
    DBMS_OUTPUT.PUT_LINE(W);
    END LOOP;
    END;

    Kompilacja przebiega bez problemu.
    Następnie wywołuję procedurę:

    BEGIN
    P;
    END;

    Wynik z dbms output:
    19 WIERSZY DO WYSWIETLENIA
    AD_PRES President 20000 40000
    1
    2

    Script output:
    BEGIN
    P;
    END;
    Error report:
    ORA-01403: no data found
    ORA-06512: at "HR.P", line 13
    ORA-06512: at line 2
    01403. 00000 - "no data found"
    *Cause:
    *Action:

    Rozumiem co oznacza wygenerowany błąd, jednak nie wiem czemu tak się dzieje. Ktoś może pomóc?


    OdpowiedzUsuń
  2. Problem jest z ROWNUM
    Zmień jeden wiersz na choćby taki jak niżej.

    SELECT * INTO JI,JT,MINS,MAXS,I FROM (SELECT JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY,ROWNUM ow from jobs) b WHERE b.OW=W;

    Pozdrawiam
    Michał Biegański

    OdpowiedzUsuń