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.
Mam problem z wyświetleniem wszystkich wierszy z tabeli JOBS ze schematu użytkownika HR.
OdpowiedzUsuń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?
Problem jest z ROWNUM
OdpowiedzUsuń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
Deklaracja zmiennej v_id w ostatnim przykładzie jest zbędna bo tego pola w ogóle nie wykorzystujemy.
OdpowiedzUsuńdeclare
v_pracownik employees%rowtype;
begin
select * into v_pracownik from employees where employee_id=199;
v_pracownik.employee_id:=v_pracownik.employee_id+14300;
v_pracownik.email:='jakisinny@adres.pl';
insert into employees values v_pracownik;
end;