ICA0016 Oracle programmeerimiskeeled SQL ja PLSQL 14. Ülesanne (0)

14. Ülesanne    Koostage funktsioon, mis kasutab ülesandes 8 loodud raamatukogu laenutuste andmebaasi. 
Funktsiooni parameetriks (argumendiks) on lugeja isikukood ja funktsiooni väärtuseks (return-value) 
on selle isikukoodiga lugeja poolt tasumata viiviste summa:   a) viivis on 0, kui lugejal pole võlgu – kõik laenutatud raamatud on tähtajaks (kahe nädala jooksul) 
tagastatud;   b) tagastamistähtaja ületanud raamatute puhul arvutatakse viivist 0,5% raamatu hinnast iga hilinetud 
päeva kohta    1) Registreerunud lugejate andmete tabel (nimed, isikukoodid, aadress, telefon, e-mail);  
 
CREATE TABLE lugejad(isikukood NUMBER(11), nimi VARCHAR2(30), aadress VARCHAR2(100),  
telefon VARCHAR2(20), email VARCHAR2(30),  
CONSTRAINT isikukood_pk PRIMARY KEY (isikukood));  
 
2) Raamatukogus olevate raamatute tabel (shiffer, autor(id), pealkiri, eksemplari hind, eksemplaride arv);  
 
CREATE TABLE raamatud(shiffer VARCHAR2(50), autorid VARCHAR2(100), pealkiri VARCHAR2(100),  
hind NUMBER(4,2), kogus NUMBER(3,0),  
CONSTRAINT raamatud_pk PRIMARY KEY (shiffer));  3) Laenutuste tabel (laenutaja isikukood, raamatu shiffer, laenutamise kuupäev, tagastamise kuupäev).  
 
CREATE TABLE laenutused(  
laenutaja_isikukood NUMBER(11), raamatu_shiffer VARCHAR2(50), laenutatud DATE, tagastatud DATE);  
 
 
ALTER TABLE laenutused ADD CONSTRAINT lugeja_fk FOREIGN KEY (laenutaja_isikukood)  
REFERENCES lugejad(isikukood);  
ALTER TABLE laenutused ADD CONSTRAINT raamat_fk FOREIGN KEY (raamatu_shiffer)  
REFERENCES raamatud(shiffer);  4) Dummy data  INSERT INTO lugejad VALUES ('12345678901', 'Chuck Norris', 'Iirise 25-6', '+372123223', '[email protected]');  
INSERT INTO lugejad VALUES ('23456789012', 'Mihkel Jordaan', 'Kaseke 15-10', '+372223123', '[email protected]');  
INSERT INTO raamatud VALUES ('159753', 'Uuno', 'Lasteraamat', '15', '20');  
INSERT INTO raamatud VALUES ('112233', 'Trump', 'Trumpi autobiograafia', '25', '10');  
INSERT INTO raamatud VALUES ('556677', 'Taavi', 'Teine Lasteraamat', '10', '10');  
INSERT INTO laenutused VALUES ('12345678901', '159753', sysdate-200, sysdate-2);  
INSERT INTO laenutused VALUES ('23456789012', '112233', sysdate-20, sysdate-2);  
INSERT INTO laenutused VALUES ('23456789012', '556677', sysdate-20, NULL);     


-----------------------package--------------------------------  CREATE OR REPLACE PACKAGE raamatukogu 
IS 
    g_viivis CONSTANT NUMBER := 0.5; 
    FUNCTION arvuta_tasumata_viiviste_summa (p_isikukood VARCHAR) RETURN NUMBER; 
END raamatukogu; 
/ 
 
 
CREATE OR REPLACE PACKAGE BODY raamatukogu 
IS 
    FUNCTION arvuta_viivis_hinnast (hind IN NUMBER) 
        RETURN NUMBER 
    IS 
    BEGIN 
        RETURN hind * g_viivis / 100; 
    END; 
 
    FUNCTION arvuta_tasumata_viiviste_summa (p_isikukood VARCHAR)  
        RETURN NUMBER 
    IS 
        kogu_volg NUMBER := 0; 
        CURSOR volad IS SELECT r.hind, TRUNC(SYSDATE - la.laenutatud - 14) vola_paevad 
            FROM lugejad lu, raamatud r, laenutused la 
            WHERE la.laenutaja_isikukood = lu.isikukood AND la.raamatu_shiffer = r.shiffer AND lu.isikukood = 
p_isikukood AND la.tagastatud IS NULL AND TRUNC(SYSDATE - la.laenutatud) > 14; 
    BEGIN    
        FOR volg IN volad LOOP 
            kogu_volg := kogu_volg + arvuta_viivis_hinnast(volg.hind) * volg.vola_paevad;  
        END LOOP; 
        RETURN ROUND(kogu_volg, 2); 
    END; 
 
END raamatukogu; 
/ 
 
-----------------------package--------------------------------   
SELECT raamatukogu.arvuta_tasumata_viiviste_summa(12345678901) FROM dual; 
     
SELECT raamatukogu.arvuta_tasumata_viiviste_summa(23456789012) FROM dual;