Oracle PL/SQL - przykłady z kodem

Opublikowany: 03-05-2011 14:01 przez Krystian

Postanowiłem podzielić się starymi zadaniami z uczelni. Nic wielkiego - dziewięć przykładów z Oracle'owego PL/SQL, ale komuś zainteresowanemu tym tematem może się przydać. Zadania raczej wzięte z życia, tzn. coś podobnego może być przydatne w rzeczywistej bazie.


No dobra, to zaczynamy. Dany jest następujący logiczny schemat bazy danych:



Każda faktura jest identyfikowana przez unikalny identyfikator (f_id_faktury). Na fakturze umieszczone są data wystawienia (f_data_wystawienia) oraz data płatności (f_data_platnosci), przy czym data płatności musi być późniejsza niż data wystawienia faktury. Z każdą fakturą związana jest flaga oznaczająca, czy dana faktura została już zapłacona (f_czy_zaplacona, pole przyjmuje wartości ‘T’ i ‘N’). Faktura składa się z pozycji, każda pozycja posiada sztuczny identyfikator (p_id_pozycji) oraz atrybut p_f_id_faktury będący kluczem obcym wskazującym na fakturę, której dotyczy dana pozycja faktury. Każda pozycja faktury posiada liczbę porządkową (p_lp), poszczególne pozycje w ramach faktury są numerowane począwszy od liczby 1. Pozycja faktury posiada ponadto swoją nazwę (p_nazwa), ilość zakupionego towaru (p_ilosc), oraz cenę jednostkową (p_cena_jednostkowa) za towar (cena netto za sztukę, kilogram, litr, itp.) Towary opisywane przez pozycje faktur mogą podlegać trzem różnym stawkom podatku VAT (p_stawka_vat, pole przyjmuje wartości: 0%, 7%, 15% i 22%). Schemat w formie pliku .sql znajdziecie w tym miejscu.


Zadania:

Zad 1. Napisz polecenie języka SQL które stworzy perspektywę v_faktury prezentującą, dla każdej faktury, identyfikator faktury, oraz liczbę pozycji faktury i sumaryczne kwoty faktury netto i brutto (dla każdej pozycji należy pomnożyć ilość zakupionego towaru przez cenę jednostkową, dla kolumny brutto należy dodatkowo uwzględnić podatek VAT). Nie zapomnij o zaokrągleniu wyniku do dwóch miejsc dziesiętnych. Rozwiązanie:


DROP VIEW v_faktury;
CREATE VIEW v_faktury AS
SELECT P_F_ID_FAKTURY AS "ID FAKTURY", COUNT(*) AS "LICZBA POZYCJI", ROUND(SUM(p.P_ILOSC*p.P_CENA_JEDNOSTKOWA),2) AS "SUMA NETTO", ROUND(SUM(p.P_ILOSC*(p.P_CENA_JEDNOSTKOWA+p.P_CENA_JEDNOSTKOWA*p.P_STAWKA_VAT)),2) AS "SUMA BRUTTO" FROM Faktury f join Pozycje p ON F_ID_FAKTURY=P_F_ID_FAKTURY GROUP BY P_F_ID_FAKTURY;

Zad 2. Napisz anonimowy blok PL/SQL zawierający kursor sparametryzowany. Parametrem kursora jest nazwa produktu. Kursor przebiega przez wszystkie faktury zawierające podany produkt i sprawdza, czy dana faktura została już zapłacona. Jeśli nie została zapłacona, to program drukuje identyfikator faktury i liczbę dni spóźnienia z płatnością. Jeśli faktura została zapłacona, to program drukuje identyfikator faktury i datę płatności. W bloku PL/SQL otwórz kursor, przekaż nazwę produktu przez zmienną środowiskową i wyświetl zawartość kursora za pomocą procedury DBMS_OUTPUT.PUT_LINE(). Rozwiązanie:


SET SERVEROUTPUT ON
VARIABLE produkt VARCHAR2(100)
VARIABLE data NUMBER
DECLARE
data2 NUMBER(4);
CURSOR test (nazwa CHAR) IS
SELECT * FROM Faktury join Pozycje ON
F_ID_FAKTURY=P_F_ID_FAKTURY WHERE P_NAZWA = :produkt; BEGINv :produkt:='majonez dekoracyjny';
FOR costam IN test(:produkt) LOOP
IF (costam.f_czy_zaplacona='T')
THEN
DBMS_OUTPUT.PUT_LINE('Faktura o numerze ' || costam.F_ID_FAKTURY || ' zostala oplacona w dniu ' || costam.f_data_platnosci || ' Dziekujemy!');
ELSE
:data:=(sysdate-costam.f_data_platnosci);
data2:=:data;
DBMS_OUTPUT.PUT_LINE('Faktura o numerze ' || costam.F_ID_FAKTURY || ' nie zostala opłacona, termin został przekroczony o ' || data2 || ' dni!');
END IF;
END LOOP;
:produkt:='jajka delikatesowe 10szt.';
FOR costam IN test(:produkt) LOOP
IF (costam.f_czy_zaplacona='T')
THEN
DBMS_OUTPUT.PUT_LINE('Faktura o numerze ' || costam.F_ID_FAKTURY || ' zostala oplacona w dniu ' || costam.f_data_platnosci || ' Dziekujemy!');
ELSE
:data:=(sysdate-costam.f_data_platnosci);
data2:=:data;
DBMS_OUTPUT.PUT_LINE('Faktura o numerze ' || costam.F_ID_FAKTURY || ' nie zostala opłacona, termin został przekroczony o ' || data2 || ' dni!');
END IF;
END LOOP;
END;

Zad 3. Dodaj do powyższego bloku PL/SQL procedurę obsługi błędu polegającego na podaniu nazwy nieistniejącego produktu. Rozwiązanie:


SET SERVEROUTPUT ON
VARIABLE produkt VARCHAR2(100)
VARIABLE data NUMBER
DECLARE
data2 NUMBER(4);
test123 NUMBER(4);
CURSOR test (nazwa CHAR) IS
SELECT * FROM Faktury join Pozycje ON
F_ID_FAKTURY=P_F_ID_FAKTURY WHERE P_NAZWA = :produkt;
BEGIN
:produkt:='kaszanka delikatesowa';
FOR costam IN test(:produkt) LOOP
IF (costam.f_czy_zaplacona='T')
THEN
DBMS_OUTPUT.PUT_LINE('Faktura o numerze ' || costam.F_ID_FAKTURY || ' zostala oplacona w dniu ' || costam.f_data_platnosci || ' Dziekujemy!');
ELSE
:data:=(sysdate-costam.f_data_platnosci);
data2:=:data;
DBMS_OUTPUT.PUT_LINE('Faktura o numerze ' || costam.F_ID_FAKTURY || ' nie zostala opłacona, termin został przekroczony o ' || data2 || ' dni!');
END IF;
END LOOP;
SELECT COUNT(*) INTO test123
FROM pozycje WHERE P_NAZWA = :produkt;
IF (test123<1) THEN
DBMS_OUTPUT.PUT_LINE('Produkt ' || :produkt || ' nie występuje na żadnej fakturze');
END IF;
END;

Zad 4. Napisz samodzielną funkcję KWOTA_FAKTURY(f_id_faktury NUMBER) która przyjmuje, jako parametr, identyfikator faktury, i zwraca sumaryczną kwotę na fakturze (iloczyn ilości i ceny jednostkowej netto wszystkich pozycji powiększonych o kwoty należnego podatku). Pamiętaj, aby poprawnie zaokrąglić wynik. Rozwiązanie:


CREATE OR REPLACE FUNCTION KWOTA_FAKTURY (f_id_faktury IN NUMBER)
RETURN NUMBER IS
cena NUMBER (10,2);
BEGIN
SELECT
SUM(ROUND((p_ilosc * p_cena_jednostkowa),4)+ROUND((p_ilosc*p_cena_jednostkowa*p_stawka_vat),4))
INTO cena FROM pozycje WHERE p_f_id_faktury = f_id_faktury;
RETURN cena;
END KWOTA_FAKTURY;
/
SELECT F_ID_FAKTURY AS "Numer faktury", KWOTA_FAKTURY (F_ID_FAKTURY) AS "Suma" FROM Faktury;

Zad 5. Dodaj do tabeli FAKTURY atrybut f_kara_za_zwloke. Następnie, napisz procedurę o nazwie UAKTUALNIJ_FAKTURY(), która dla wszystkich niezapłaconych faktur przeliczy i uaktualni wartość atrybutu f_kara_za_zwloke. Przyjmij, że za każdy dzień spóźnienia w płatności faktury naliczana jest kara w wysokości 0.1% (jeden promil, jedna dziesiąta procenta) kwoty netto faktury. Pamiętaj, że kara za zwłokę naliczana jest z dokładnością do groszy (a nie np. setnych części grosza). Wylicz stan kar na dzień 1 stycznia 2008 roku. Rozwiązanie:


<>ALTER<> TABLE FAKTURY ADD f_kara_za_zwloke NUMBER(10,2);
SELECT * FROM faktury;
CREATE OR REPLACE PROCEDURE UAKTUALNIJ_FAKTURY IS zmienna NUMBER;
stop NUMBER;
BEGIN
SELECT MAX(F_ID_FAKTURY ) INTO zmienna FROM Faktury;
stop:=0;
LOOP
UPDATE FAKTURY f SET (f_kara_za_zwloke) = (SELECT ROUND((TO_DATE('08_01_01','YY_MM_DD')-f_data_platnosci)*ROUND(SUM(
p.P_ILOSC*p.P_CENA_JEDNOSTKOWA),2)*'0,001',2) FROM Faktury f join Pozycje p ON F_ID_FAKTURY=P_F_ID_FAKTURY WHERE f_czy_zaplacona='N' AND P_F_ID_FAKTURY=zmienna GROUP BY f_data_platnosci) WHERE f_czy_zaplacona='N' AND F_ID_FAKTURY=zmienna;
EXIT WHEN (zmienna = stop);
zmienna:=zmienna-1;
END LOOP;
END;
/
BEGIN UAKTUALNIJ_FAKTURY;
END;
/
SELECT * FROM faktury;

Zad 6. Dodaj do procedury UAKTUALNIJ_FAKTURY() obsługę błędu polegającego na tym, że faktura nie posiada żadnych pozycji faktury. W takim przypadku wartością atrybutu f_kara_za_zwloke jest wartość 0. Dodaj nową niezapłaconą fakturę bez pozycji faktury i sprawdź działanie procedury. Rozwiązanie:


<>ALTER<> TABLE FAKTURY ADD f_kara_za_zwloke NUMBER(10,2);
INSERT INTO faktury (f_id_faktury, f_czy_zaplacona) VALUES (60, 'N');
SELECT * FROM faktury;
CREATE OR REPLACE PROCEDURE UAKTUALNIJ_FAKTURY IS zmienna NUMBER;
stop NUMBER;
innazmienna NUMBER;
BEGIN
SELECT MAX(F_ID_FAKTURY ) INTO zmienna FROM Faktury;
stop:=0;
LOOP
EXIT WHEN (zmienna = stop);
SELECT COUNT(*) INTO innazmienna FROM Pozycje WHERE P_F_ID_FAKTURY=zmienna;
IF (innazmienna=0) THEN
UPDATE FAKTURY f SET (f_kara_za_zwloke) = '0' WHERE F_ID_FAKTURY= zmienna;
ELSE
UPDATE FAKTURY f SET (f_kara_za_zwloke) = (SELECT ROUND((TO_DATE('08_01_01','YY_MM_DD')-f_data_platnosci)*ROUND(SUM(
p.P_ILOSC*p.P_CENA_JEDNOSTKOWA),2)*'0,001',2) FROM Faktury f join Pozycje p ON F_ID_FAKTURY=P_F_ID_FAKTURY WHERE f_czy_zaplacona='N' AND P_F_ID_FAKTURY=zmienna GROUP BY f_data_platnosci) WHERE f_czy_zaplacona='N' AND F_ID_FAKTURY=zmienna;
END IF;
zmienna:=zmienna-1;
END LOOP;
END;
/
BEGIN UAKTUALNIJ_FAKTURY;
END;
/
SELECT * FROM faktury;

Zad 7. Stwórz wyzwalacz (lub trzy oddzielne wyzwalacze) który będzie odnotowywał każdą modyfikację tabeli FAKTURA. Zbuduj tablę HISTORIA(id TIMESTAMP, operacja VARCHAR(30)) i umieszczaj tam znacznik czasowy (SYSTIMESTAMP) każdej operacji INSERT, UPDATE, DELETE kierowanej do tabeli FAKTURA oraz nazwę wykonanej operacji. Sprawdź działanie wyzwalacza. Rozwiązanie:


<>CREATE<> TABLE HISTORIA(id TIMESTAMP, operacja VARCHAR(30));

CREATE OR REPLACE TRIGGER test <>AFTER<> INSERT OR UPDATE OR DELETE ON faktury
BEGIN
IF INSERTING THEN INSERT INTO Historia (id, operacja) VALUES (SYSTIMESTAMP, 'wstawienie faktury');
ELSIF UPDATING THEN INSERT INTO Historia (id, operacja) VALUES (SYSTIMESTAMP, 'modyfikacja faktury');
ELSIF DELETING THEN INSERT INTO Historia (id, operacja) VALUES (SYSTIMESTAMP, 'usunięcie faktury');
END IF;
END;
/

INSERT INTO faktury(f_id_faktury, f_data_wystawienia, f_czy_zaplacona) VALUES (70, SYSDATE, 'N');

UPDATE faktury SET f_data_platnosci = f_data_wystawienia + 14 WHERE f_id_faktury = 70;

DELETE FROM faktury WHERE f_id_faktury = 70;

SELECT * FROM historia;

Zad 8. Utwórz sekwencję faktury_seq. Napisz wyzwalacz umożliwiający dodawanie nowej faktury bez podania wartości klucza podstawowego (wartość klucza powinna być wczytana z sekwencji przez wyzwalacz, ale tylko w przypadku, gdy użytkownik nie podał wartości klucza podstawowego!). Rozwiązanie (nie jest w pełni poprawne ponieważ dodawanie następuję za pośrednictwem perspektywy, a nie bezpośrednio):


drop view faktury_perpektywa;

CREATE VIEW faktury_perpektywa AS SELECT f_id_faktury, f_data_wystawienia, f_data_platnosci, f_czy_zaplacona from faktury;

SELECT * From faktury;

CREATE OR REPLACE TRIGGER test2 INSTEAD OF INSERT ON faktury_perpektywa
BEGIN
if :new.f_id_faktury is null then
INSERT INTO faktury (f_id_faktury, f_data_wystawienia, f_data_platnosci) VALUES ((SELECT (MAX(f_id_faktury)+10) FROM faktury_perpektywa), :new.f_data_wystawienia, :new.f_data_platnosci);
end if;
END test;
/

INSERT INTO faktury_perpektywa(f_data_wystawienia, f_data_platnosci) VALUES (SYSDATE, SYSDATE + 14);

SELECT * From faktury;

Zad 9. Dodaj do tabeli FAKTURY atrybut f_liczba_pozycji. Zainicjalizuj wartości tego atrybutu we wszystkich krotkach relacji FAKTURY. Stwórz wyzwalacz, który będzie pielęgnował wartość atrybutu f_liczba_pozycji. Po każdej modyfikacji tabeli POZYCJE (dodanie lub usunięcie pozycji) odpowiednio zmodyfikuj atrybut. Uwaga: nie wolno przenosić pozycji faktury między fakturami, wyzwalacz musi zapobiec takiej operacji! Rozwiązanie:


<>ALTER<> TABLE FAKTURY ADD f_liczba_pozycji NUMBER(10);

SELECT * FROM Faktury;

DECLARE innazmienna2 NUMBER;
zmienna NUMBER;
stop NUMBER;
BEGIN
SELECT MAX(F_ID_FAKTURY ) INTO zmienna FROM Faktury; FOR i IN 0 .. zmienna LOOP
SELECT COUNT(*) INTO innazmienna2 FROM Pozycje WHERE P_F_ID_FAKTURY=i;
IF (innazmienna2=0) THEN UPDATE FAKTURY f SET (F_LICZBA_POZYCJI) = '0' WHERE F_ID_FAKTURY=i;
ELSE
UPDATE Faktury f SET F_LICZBA_POZYCJI = (SELECT COUNT(*) FROM Faktury f join Pozycje p ON F_ID_FAKTURY=P_F_ID_FAKTURY WHERE F_ID_FAKTURY=i GROUP BY P_F_ID_FAKTURY ) WHERE F_ID_FAKTURY=i;
END IF;
END LOOP;
END;
/

SELECT * FROM Faktury;

//Teraz wyzwalacz pielegnujący f_liczba_pozycji
drop trigger zadanie9;

SELECT * FROM Faktury;

CREATE OR REPLACE TRIGGER zadanie9
AFTER INSERT OR DELETE ON pozycje for each row
BEGIN
IF INSERTING THEN
UPDATE faktury
SET f_liczba_pozycji = f_liczba_pozycji + 1
WHERE f_id_faktury = :NEW.p_f_id_faktury;
ELSIF DELETING THEN
UPDATE faktury
SET f_liczba_pozycji = f_liczba_pozycji - 1
WHERE f_id_faktury = :OLD.p_f_id_faktury;
END IF;
END zadanie9;
/

INSERT INTO pozycje (p_id_pozycji,p_f_id_faktury,p_lp,p_nazwa) VALUES (123,60,1,'sałatka śledziowa');
SELECT * FROM Faktury;

CREATE OR REPLACE TRIGGER zadanie9_update
BEFORE UPDATE OF p_f_id_faktury ON pozycje
BEGIN
RAISE_APPLICATION_ERROR(-20001,'Nie wolno przenieść pozycji do innej faktury');
END zadanie9_update;
/

Mam nadzieję, że przyda się komuś kto zaczyna zabawę z bazami (-:


bro
Brak komentarzy