set serveroutput on
--ZADANIE 1--
CREATE OR REPLACE VIEW v_faktury (f_id_faktury, liczba_pozycji, suma_netto, suma_brutto) AS
SELECT f_id_faktury, COUNT(p_f_id_faktury),
SUM(round(p_cena_jednostkowa*p_ilosc, 2)),
SUM(round(((p_cena_jednostkowa*p_stawka_vat)+p_cena_jednostkowa)*p_ilosc, 2))
FROM faktury, pozycje
WHERE f_id_faktury = p_f_id_faktury
GROUP BY f_id_faktury;
--ZADANIE 2--
DECLARE
CURSOR zaplacona (nazwa_produktu pozycje.p_nazwa%type) IS
SELECT * FROM pozycje, faktury where p_f_id_faktury = f_id_faktury;
v_nazwa pozycje.p_nazwa%type;
produkt VARCHAR2(100);
BEGIN
produkt := 'majonez dekoracyjny';
FOR c_rec IN zaplacona(v_nazwa) LOOP
IF(UPPER(produkt) = UPPER(c_rec.p_nazwa)) THEN
DBMS_OUTPUT.PUT_LINE(c_rec.p_nazwa);
IF(c_rec.f_czy_zaplacona = 'T') THEN
DBMS_OUTPUT.PUT_LINE('Faktura ' || c_rec.p_f_id_faktury || ' zostala zaplacona w dniu ' || to_char(c_rec.f_data_platnosci, 'DD-MONTH-YY'));
ELSE
DBMS_OUTPUT.PUT_LINE('Faktura ' || c_rec.p_f_id_faktury || ' jest niezaplacona od ' || ROUND((SYSDATE-c_rec.f_data_platnosci)) || ' dni');
END IF;
END IF;
END LOOP;
END;
--ZADANIE 3--
DECLARE
CURSOR zaplacona (nazwa_produktu pozycje.p_nazwa%type) IS
SELECT * FROM pozycje, faktury where p_f_id_faktury = f_id_faktury;
v_nazwa pozycje.p_nazwa%type;
produkt VARCHAR2(100);
na_liscie BOOLEAN := FALSE;
BEGIN
produkt := 'kaszanka delikatesowa';
FOR c_rec IN zaplacona(v_nazwa) LOOP
IF(UPPER(produkt) = UPPER(c_rec.p_nazwa)) THEN
na_liscie := (UPPER(produkt) = UPPER(c_rec.p_nazwa));
DBMS_OUTPUT.PUT_LINE(c_rec.p_nazwa);
IF(c_rec.f_czy_zaplacona = 'T') THEN
DBMS_OUTPUT.PUT_LINE('Faktura ' || c_rec.p_f_id_faktury || ' zostala zaplacona w dniu ' || to_char(c_rec.f_data_platnosci, 'DD-MONTH-YY'));
ELSE
DBMS_OUTPUT.PUT_LINE('Faktura ' || c_rec.p_f_id_faktury || ' jest niezaplacona od ' || ROUND((SYSDATE-c_rec.f_data_platnosci)) || ' dni');
END IF;
END IF;
END LOOP;
IF NOT na_liscie THEN
DBMS_OUTPUT.PUT_LINE('Produkt ' || produkt || ' nie wystêpuje na ¿adnej fakturze');
END IF;
END;
--ZADANIE 4--
CREATE OR REPLACE FUNCTION kwota_faktury(id_faktura IN NUMBER)
RETURN NUMBER IS
wynik NUMBER;
BEGIN
SELECT SUM(round(((p_cena_jednostkowa*p_stawka_vat)+p_cena_jednostkowa)*p_ilosc, 2)) INTO wynik
FROM pozycje
WHERE p_f_id_faktury = id_faktura;
RETURN wynik;
END kwota_faktury;
SELECT f_id_faktury, KWOTA_FAKTURY(f_id_faktury) AS kwota_faktury FROM faktury;
--Zadanie 5--
ALTER TABLE faktury
ADD f_kara_za_zwloke NUMBER(4,2);
CREATE OR REPLACE PROCEDURE uaktualnij_faktury
AS
BEGIN
UPDATE faktury
SET f_kara_za_zwloke = (to_date('01-01-2008', 'DD-MM-YYYY')-f_data_platnosci)*0.001*(SELECT SUM(p_ilosc*p_cena_jednostkowa) FROM faktury, pozycje WHERE f_id_faktury = p_f_id_faktury)
WHERE f_czy_zaplacona = 'N';
END uaktualnij_faktury;
BEGIN
uaktualnij_faktury;
END;
SELECT * FROM faktury;
--Zadanie 6--
CREATE OR REPLACE PROCEDURE uaktualnij_faktury
AS
BEGIN
UPDATE faktury
SET f_kara_za_zwloke = nvl((to_date('01-01-2008', 'DD-MM-YYYY')-f_data_platnosci),0)*0.001*(SELECT SUM(p_ilosc*p_cena_jednostkowa) FROM faktury, pozycje WHERE f_id_faktury = p_f_id_faktury)
WHERE f_czy_zaplacona = 'N';
END uaktualnij_faktury;
INSERT INTO faktury (f_id_faktury, f_czy_zaplacona) VALUES (60, 'N');
BEGIN
uaktualnij_faktury;
END;
SELECT * FROM faktury;
--Zadanie 7--
CREATE TABLE historia (
id TIMESTAMP,
operacja VARCHAR(30)
);
CREATE OR REPLACE TRIGGER wyzwalacz
AFTER INSERT OR UPDATE OR DELETE ON faktury
DECLARE
zmienna VARCHAR2(30);
BEGIN
IF INSERTING THEN
zmienna := 'wstawienie faktury';
INSERT INTO historia(id, operacja)
VALUES (SYSTIMESTAMP, zmienna);
ELSIF UPDATING THEN
zmienna := 'modyfikacja faktury';
INSERT INTO historia(id, operacja)
VALUES (SYSTIMESTAMP, zmienna);
ELSIF DELETING THEN
zmienna := 'usuniêcie faktury';
INSERT INTO historia(id, operacja)
VALUES (SYSTIMESTAMP, zmienna);
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;
--Zadanie 8--
CREATE SEQUENCE faktury_seq
START WITH 80
INCREMENT BY 10
MINVALUE 10;
CREATE OR REPLACE TRIGGER adding
BEFORE INSERT ON faktury
FOR EACH ROW
BEGIN
IF (:NEW.f_id_faktury IS NULL) THEN
SELECT faktury_seq.NEXTVAL
INTO :NEW.f_id_faktury FROM DUAL;
END IF;
END;
INSERT INTO faktury(f_data_wystawienia, f_data_platnosci) VALUES (SYSDATE, SYSDATE + 14);
INSERT INTO faktury(f_id_faktury, f_data_wystawienia, f_data_platnosci) VALUES (70, SYSDATE, SYSDATE + 14);
SELECT * FROM faktury;
--Zadanie 9--
ALTER TABLE faktury
ADD f_liczba_pozycji NUMBER DEFAULT 0;
CREATE OR REPLACE FUNCTION liczba_pozycji(id_faktura IN NUMBER)
RETURN NUMBER IS
wynik NUMBER;
BEGIN
SELECT COUNT(p_f_id_faktury) INTO wynik
FROM pozycje
WHERE p_f_id_faktury = id_faktura;
RETURN wynik;
END liczba_pozycji;
UPDATE faktury
SET f_liczba_pozycji = liczba_pozycji(f_id_faktury)
WHERE f_id_faktury is NOT NULL;
CREATE OR REPLACE TRIGGER korekta
AFTER INSERT OR DELETE ON pozycje
BEGIN
IF INSERTING THEN
UPDATE faktury
SET f_liczba_pozycji = f_liczba_pozycji+1
WHERE f_id_faktury IS NOT NULL;
ELSIF DELETING THEN
UPDATE faktury
SET f_liczba_pozycji = f_liczba_pozycji-1
WHERE f_id_faktury IS NOT NULL;
END IF;
END;
CREATE OR REPLACE TRIGGER korekta2
BEFORE UPDATE OF p_f_id_faktury ON pozycje
FOR EACH ROW
BEGIN
RAISE_APPLICATION_ERROR(-20001,'Nie wolno przenieæ pozycji do innej faktury');
END;
SELECT * FROM faktury WHERE f_id_faktury = 280;
INSERT INTO pozycje (p_id_pozycji,p_f_id_faktury,p_lp,p_nazwa) VALUES (123,280,1,'sa³atka ledziowa');
SELECT * FROM faktury WHERE f_id_faktury = 280;
UPDATE pozycje SET p_f_id_faktury = 40 WHERE p_id_pozycji = 123;
SELECT * FROM pozycje;
DELETE FROM pozycje WHERE p_id_pozycji = 123;
