opbp

1.Kreirajte funkciju koja uzima naziv odjela te izracunava i ispisuje vrijednost prosjecne
dnevne, mjesecne i godišnje isplacene place u tome odjelu (npr. za odjel Projektiranje)? Za
racunanje koristite 22 radna dana u mjesecu, 8 radnih sati u danu.

   CREATE OR REPLACE FUNCTION izracun_placa (odjel odjel.naziv%TYPE)
   RETURN VARCHAR2 IS
   prosjecna_placa VARCHAR2(100);

   BEGIN
        SELECT ROUND(AVG(PLACA/22/8),2)||' '||ROUND(AVG(PLACA),2)||' '||ROUND(AVG(PLACA*12),2)  INTO prosjecna_placa
        FROM RADNIK WHERE SODJEL IN(SELECT SODJEL FROM ODJEL WHERE NAZIV =odjel);

        RETURN prosjecna_placa;

   END izracun_placa;
   /

SELECT izracun_placa('PROJEKTIRANJE') FROM DUAL;

_________________________________________________________________________________________________________________
2.
Kreirajte funkciju koja uzima šifru radnika te vraca ime i prezime tog radnika, kao i ime i
prezime njegovog neposredno nadredenog.

   CREATE OR REPLACE FUNCTION ime_radnika(sifra IN RADNIK.SRADNIK%TYPE)
   RETURN VARCHAR2 IS
   ime VARCHAR2(200);

   BEGIN
   SELECT r.ime ||' ' || r.prezime ||' ' || m.ime ||' ' || m.prezime INTO ime
   FROM RADNIK r INNER JOIN RADNIK m ON m.SRADNIK = r.SNADR WHERE r.SRADNIK = sifra AND r.SNADR = m.SRADNIK;
   RETURN ime;
   END ime_radnika;
   /

SELECT ime_radnika(3220) FROM DUAL;



--------------------------------------------------------------------------------------------
3.
Kreirati samostalnu proceduru koja ce prihvatiti naziv posla te postotak za koji tim radnicima
želite smanjiti placu. Procedura preko naziva posla selektira trenutne place radnika te ih
smanjuje za unešeni postotak. Ako traženi posao ne postoji, poziva se odgovarajuca greška.
Takoder, najmanja placa radnika ne smije biti manja od 6 000 kn – u tome slucaju ispisati
odgovarajucu (korisnicku) iznimku, korištenjem RAISE naredbe.

CREATE OR REPLACE  PROCEDURE smanjenje_place(naziv RADNIK.POSAO%TYPE, postotak IN NUMBER) IS
PLACA RADNIK.PLACA%TYPE;
min_placa RADNIK.PLACA%TYPE := 6000;
premala_placa EXCEPTION;

BEGIN
SELECT MIN(PLACA) INTO PLACA FROM RADNIK WHERE POSAO = naziv;
IF naziv NOT IN ('ANALITICAR','TRG.PUTNIK', 'DIREKTOR', 'SAVJETNIK','VODITELJ') THEN
 RAISE_APPLICATION_ERROR(-20568, 'Posao ne postoji!!!');
ELSIF PLACA < min_placa THEN RAISE premala_placa;
 ELSE
UPDATE RADNIK SET PLACA = PLACA -(PLACA*postotak/100) WHERE POSAO = naziv;
END IF;
EXCEPTION
WHEN premala_placa THEN DBMS_OUTPUT.PUT_LINE('Placa je premala!!!');
END smanjenje_place;
/


EXECUTE smanjenje_place('TRG.PUTNIK', 1);
EXECUTE smanjenje_place('TRG', 1); --> za provjeru dal ispisuje da posao ne postoji

-----------------------------------------------------------------------------------
4.
Kreirajte samostalnu proceduru PROMJENA_ODJEL koja mijenja naziv odjela u tablici ODJEL
(sodjel, naziv, lokacija). Paziti da šifra odjela mora biti unesena (ne smije biti NULL). U
suprotnom pozvati odgovarajucu iznimku, koristeci RAISE_APPLICATION_ERROR naredbu.
Takoder, neka se ispisuje poruka „Naziv odjela je prekratak!“, ako naziv odjela ima manje od
3 znaka.
Za test promijenite odjel ODRZAVANJE u TESTIRANJE.

CREATE OR REPLACE PROCEDURE promjena_odjela(sifra ODJEL.SODJEL%TYPE, stari_naziv ODJEL.NAZIV%TYPE, novi_naziv ODJEL.NAZIV%TYPE, lokacija ODJEL.LOKACIJA%TYPE) IS

BEGIN
IF sifra IS NULL THEN
RAISE_APPLICATION_ERROR(-20145,'Sifra odjela ne moze biti NULL.');
ELSIF LENGTH(novi_naziv) < 3 THEN
DBMS_OUTPUT.PUT_LINE('Naziv odjela je prekratak!!!');
ELSE
UPDATE ODJEL  SET NAZIV = novi_naziv WHERE NAZIV = stari_naziv;
END IF;
END promjena_odjela;
/


EXECUTE promjena_odjela(50, 'ODRZAVANJE','TESTIRANJE','ZG');
EXECUTE promjena_odjela('', 'TESTIRANJE', 'ZG'); ---> provjera za null
EXECUTE promjena_odjela(50, 'TE', 'ZG'); ----> provjera za manje od 3 slova
______________________________________________________________________________________________________________________
5.
Ubacite u paket ZADATAK funkcije i PROCEDURE iz prethodnih zadatka.

CREATE OR REPLACE PACKAGE zadatak IS
FUNCTION izracun_placa (odjel odjel.naziv%TYPE) RETURN VARCHAR2;
FUNCTION ime_radnika(sifra IN RADNIK.SRADNIK%TYPE)RETURN VARCHAR2;
PROCEDURE smanjenje_place(naziv RADNIK.POSAO%TYPE, postotak IN NUMBER);
PROCEDURE promjena_odjela(sifra ODJEL.SODJEL%TYPE, novi_naziv ODJEL.NAZIV%TYPE, lokacija ODJEL.LOKACIJA%TYPE);
END ZADATAK;
/

CREATE OR REPLACE PACKAGE BODY zadatak IS
FUNCTION izracun_placa (odjel odjel.naziv%TYPE)
   RETURN VARCHAR2 IS
   prosjecna_placa VARCHAR2(100);

   BEGIN
        SELECT ROUND(AVG(PLACA/22/8),2)||' '||ROUND(AVG(PLACA),2)||' '||ROUND(AVG(PLACA*12),2)  INTO prosjecna_placa
        FROM RADNIK WHERE SODJEL IN(SELECT SODJEL FROM ODJEL WHERE NAZIV =odjel);

        RETURN prosjecna_placa;

   END izracun_placa;

FUNCTION ime_radnika(sifra IN RADNIK.SRADNIK%TYPE)
   RETURN VARCHAR2 IS
   ime VARCHAR2(500);

   BEGIN
   SELECT r.ime ||' ' || r.prezime ||' ' || m.ime ||' ' || m.prezime INTO ime
   FROM RADNIK r LEFT OUTER JOIN RADNIK m ON m.SRADNIK = r.SNADR WHERE r.SRADNIK = sifra AND r.SNADR = m.SRADNIK;
   RETURN ime;
   END ime_radnika;

PROCEDURE smanjenje_place(naziv RADNIK.POSAO%TYPE, postotak IN NUMBER) IS
PLACA RADNIK.PLACA%TYPE;
min_placa RADNIK.PLACA%TYPE := 6000;
premala_placa EXCEPTION;
BEGIN

IF naziv NOT IN ('ANALITICAR','TRG.PUTNIK', 'DIREKTOR', 'SAVJETNIK','VODITELJ') THEN
 RAISE_APPLICATION_ERROR(-20568, 'Posao ne postoji!!!');
ELSIF PLACA < min_placa THEN RAISE premala_placa;
 ELSE
UPDATE RADNIK SET PLACA = PLACA -(PLACA*postotak/100) WHERE POSAO = naziv;
END IF;
EXCEPTION
WHEN premala_placa THEN DBMS_OUTPUT.PUT_LINE('Placa je premala!!!');
END smanjenje_place;

PROCEDURE promjena_odjela(sifra ODJEL.SODJEL%TYPE, stari_naziv ODJEL.NAZIV%TYPE, novi_naziv ODJEL.NAZIV%TYPE, lokacija ODJEL.LOKACIJA%TYPE) IS

BEGIN
IF sifra IS NULL THEN
RAISE_APPLICATION_ERROR(-20145,'Sifra odjela ne moze biti NULL.');
ELSIF LENGTH(novi_naziv) < 3 THEN
DBMS_OUTPUT.PUT_LINE('Naziv odjela je prekratak!!!');
ELSE
UPDATE ODJEL  SET NAZIV = novi_naziv WHERE NAZIV = stari_naziv;
END IF;
END promjena_odjela;
/


EXECUTE promjena_odjela(50, 'ODRZAVANJE','TESTIRANJE','ZG');

END zadatak;
/

SELECT zadatak.ime_radnika(3220) FROM dual; -----> za pozivanje funkcije
EXECUTE zadatak.promjena_odjela(50,'TESTIRANJE', 'ZG'); -----> za procedure













CREATE TABLE RADNIK (SRADNIK NUMBER NOT NULL, IME VARCHAR2(20) NOT NULL, PREZIME VARCHAR2(30) NOT NULL,
POSAO VARCHAR2(20) NOT NULL, SNADR NUMBER, DATZAP DATE NOT NULL, PLACA NUMBER NOT NULL, DODATAK NUMBER,
SODJEL NUMBER NOT NULL);

INSERT INTO RADNIK (SRADNIK, IME, PREZIME, POSAO, SNADR, DATZAP,
PLACA, SODJEL)
VALUES(3069, 'JAKOV', 'JAKIC', 'ANALITICAR', 3602, '17.DECEMBER.1981',
8000, 20);

INSERT INTO RADNIK
VALUES (3199, 'LOVRO', 'LOVRIC', 'TRG. PUTNIK', 3398, '22.FEBRUARY.1981',
16000, 3000, 30);

INSERT INTO RADNIK
VALUES (3221, 'PETAR', 'PETRIC', 'TRG. PUTNIK', 3398, '22.FEBRUARY.1981',
12500, 5000, 30);

INSERT INTO RADNIK (SRADNIK, IME, PREZIME, POSAO, SNADR, DATZAP,
PLACA, SODJEL)
VALUES(3266, 'MARKO', 'MARKIC', 'VODITELJ', 3539, '02.APRIL.1981',
29750, 20);

INSERT INTO RADNIK
VALUES (3354, 'JOSIP', 'MARKOVIC', 'TRG. PUTNIK', 3398, '28.SEPTEMBER.1981',
12500, 14000, 30);

INSERT INTO RADNIK (SRADNIK, IME, PREZIME, POSAO, SNADR, DATZAP,
PLACA, SODJEL)
VALUES(3398, 'DANIEL', 'CINDRIC', 'VODITELJ', 3539, '01.JANUARY.1981',
28500, 30);

INSERT INTO RADNIK (SRADNIK, IME, PREZIME, POSAO, SNADR, DATZAP,
PLACA, SODJEL)
VALUES(3482, 'IVAN', 'IVIC', 'VODITELJ', 3539, '09.JUNE.1981',
24500, 10);

INSERT INTO RADNIK (SRADNIK, IME, PREZIME, POSAO, SNADR, DATZAP,
PLACA, SODJEL)
VALUES(3488, 'BOZIDAR', 'BOZIC', 'SAVJETNIK', 3266, '09.NOVEMBER.1981',
30000, 20);

INSERT INTO RADNIK (SRADNIK, IME, PREZIME, POSAO, DATZAP,
PLACA, SODJEL)
VALUES(3539, 'STJEPAN', 'STIPIC', 'DIREKTOR', '17.NOVEMBER.1981',
50000, 10);

INSERT INTO RADNIK
VALUES (3544, 'GORAN', 'STIMAC', 'TRG. PUTNIK', 3598, '08.SEPTEMBER.1981',
15000, 0, 30);

INSERT INTO RADNIK (SRADNIK, IME, PREZIME, POSAO, SNADR, DATZAP,
PLACA, SODJEL)
VALUES(3576, 'MATEJ', 'MARKOVIC', 'ANALITICAR', 3488, '23.SEPTEMBER.1981',
11000, 20);

INSERT INTO RADNIK (SRADNIK, IME, PREZIME, POSAO, SNADR, DATZAP,
PLACA, SODJEL)
VALUES(3609, 'ANTE', 'ANTIC', 'ANALITICAR', 3398, '03.DECEMBER.1981',
9500, 30);

INSERT INTO RADNIK (SRADNIK, IME, PREZIME, POSAO, SNADR, DATZAP,
PLACA, SODJEL)
VALUES(3602, 'FILIP', 'NOSIC', 'SAVJETNIK', 3266, '03.DECEMBER.1981',
30000, 20);

INSERT INTO RADNIK (SRADNIK, IME, PREZIME, POSAO, SNADR, DATZAP,
PLACA, SODJEL)
VALUES(3634, 'DEAN', 'PERTIC', 'ANALITICAR', 3482, '23.JANUARY.1982',
13000, 10);









CREATE TABLE ODJEL(SODJEL NUMBER NOT NULL, NAZIV CHAR(15), LOKACIJA CHAR(20));

INSERT INTO ODJEL (SODJEL, NAZIV, LOKACIJA)
VALUES(10, 'ZAJ_SLUZBA', 'RIJEKA');

INSERT INTO ODJEL (SODJEL, NAZIV, LOKACIJA)
VALUES(20, 'PRIPREMA', 'VISOKOVO');

INSERT INTO ODJEL (SODJEL, NAZIV, LOKACIJA)
VALUES(30, 'PROJEKTIRANJE', 'MATULJI');

INSERT INTO ODJEL (SODJEL, NAZIV, LOKACIJA)
VALUES(40, 'ISTRAZIVANJE', 'RIJEKA');


A - GRUPA


1.

SET UNDERLINE =

SELECT IME, ORGANIZATOR,
COUNT(DECODE(razina, 'OSNOVNA', STECAJ, 0)) "OSNOVNA RAZINA",
COUNT(DECODE(razina, 'NAPREDNA', STECAJ, 0)) "NAPREDNA RAZINA",
COUNT(STECAJ) "UKUPNO PO ORGANIZATORU",
COUNT(0) PRAZNO
FROM pposcic.TECAJ, pposcic.ORGANIZATOR
WHERE PPOSCIC.TECAJ.SORG = PPOSCIC.ORGANIZATOR.SORG
GROUP BY PPOSCIC.ORGANIZATOR.IME;


BREAK ON PRAZNO

COMPUTE COUNT OF "OSNOVNA RAZINA" ON PRAZNO
COMPUTE COUNT OF "NAPREDNA RAZINA" ON PRAZNO
COMPUTE COUNT OF "UKUPNO PO ORGANIZATORU"

COLUMN "OSNOVNA RAZINA" FORMAT 999
COLUMN "NAPREDNA RAZINA" FORMAT 999
COLUMN "UKUPNO PO ORGANIZATORU" FORMAT 999

TTITLE CENTER 'BROJ TECAJEVA PO ORGANIZATORIMA ZA POJEDINU RAZINU' SKIP 2

COLUMN PRAZNO NOPRINT

__________________________________________________________________________________________________
__________________________________________________________________________________________________


2.


CREATE OR REPLACE PACKAGE PLO IS

FUNCTION PROSJECNA_CIJENA;
PROCEDURE nove
(NAZIV PPOSCIC.PLOC.NAZIV_PLOC%TYPE, SIFRA PPOSCIC.PLOC.SIFR_PLOC%TYPE, SIRINA1 PPOSCIC.PLOC.SIRINA%TYPE,
DUZINA1 PPOSCIC.PLOC.DUZINA%TYPE, SIFRA1 PPOSCIC.PLOC.SIFR_DOB%TYPE, CIJENA1 PPOSCIC.PLOC.CIJENA%TYPE);

END PLO;

-----------------------------------------------

CREATE OR REPLACE PACKAGE BODY PLO IS

PROCEDURE nove
(NAZIV PPOSCIC.PLOC.NAZIV_PLOC%TYPE, SIFRA PPOSCIC.PLOC.SIFR_PLOC%TYPE, SIRINA1 PPOSCIC.PLOC.SIRINA%TYPE,
DUZINA1 PPOSCIC.PLOC.DUZINA%TYPE, SIFRA1 PPOSCIC.PLOC.SIFR_DOB%TYPE, CIJENA1 PPOSCIC.PLOC.CIJENA%TYPE)
IS
BEGIN

INSERT INTO PPOSCIC.PLOC(NAZIV_PLOC, SIFR_PLOC, SIRINA, DUZINA, SIFR_DOB, CIJENA) VALUES
(NAZIV, SIFRA, SIRINA1, DUZINA1, SIFRA1, CIJENA1)

IF PPOSCIC.PLOC.SIRINA<100 AND PPOSCIC.SIRINA>500 THEN
RAISE_APPLICATION_ERROR(-20131,'SIFRA NIJE VALJANA');
END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('DOBAVLJAC NE POSTOJI');
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('SIFRA NIJE JEDINSTVENA');

END nove;

FUNCTION PROSJECNA_CIJENA
RETURN PPOSCIC.PLOC.CIJENA%TYPE IS
PROSJECNA_CIJENA PPOSCIC.PLOC.CIJENA%TYPE
BEGIN

IF PPOSCIC.PLOC.SIRINA=150 AND PPOSCIC.PLOC.DUZINA=150 THEN
SELECT AVG(CIJENA) INTO PROSJECNA_CIJENA FROM PPOSCIC.PLOC;
END IF;
RETURN PROSJECNA_CIJENA;
END PROSJECNA_CIJENA;


END PLO;
_____________________________________________________________________________________

A)

CREATE OR REPLACE PROCEDURE nove
(NAZIV PPOSCIC.PLOC.NAZIV_PLOC%TYPE, SIFRA PPOSCIC.PLOC.SIFR_PLOC%TYPE, SIRINA1 PPOSCIC.PLOC.SIRINA%TYPE,
DUZINA1 PPOSCIC.PLOC.DUZINA%TYPE, SIFRA1 PPOSCIC.PLOC.SIFR_DOB%TYPE, CIJENA1 PPOSCIC.PLOC.CIJENA%TYPE)
IS
BEGIN

INSERT INTO PPOSCIC.PLOC(NAZIV_PLOC, SIFR_PLOC, SIRINA, DUZINA, SIFR_DOB, CIJENA) VALUES
(NAZIV, SIFRA, SIRINA1, DUZINA1, SIFRA1, CIJENA1)

IF PPOSCIC.PLOC.SIRINA<100 AND PPOSCIC.SIRINA>500 THEN
RAISE_APPLICATION_ERROR(-20131,'SIFRA NIJE VALJANA');
END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('DOBAVLJAC NE POSTOJI');
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('SIFRA NIJE JEDINSTVENA');

END nove;
--------------------------------------------------

EXECUTE nove('PILLER', 'ZZ332', 350, 350, 1134, 432.33);

_________________________________________________________________________________________

B)

CREATE OR REPLACE FUNCTION PROSJECNA_CIJENA
RETURN PPOSCIC.PLOC.CIJENA%TYPE IS
PROSJECNA_CIJENA PPOSCIC.PLOC.CIJENA%TYPE
BEGIN

IF PPOSCIC.PLOC.SIRINA=150 AND PPOSCIC.PLOC.DUZINA=150 THEN
SELECT AVG(CIJENA) INTO PROSJECNA_CIJENA FROM PPOSCIC.PLOC;
END IF;
RETURN PROSJECNA_CIJENA;
END PROSJECNA_CIJENA;

__________________________________________________________________________________________

C)

CREATE OR REPLACE FUNCTION KOLIKO(METRI NUMBER) RETURN NUMBER(6,2)
IS
KOLIKO NUMBER(6,2)
SIR PPOSCIC.PLOC.SIRINA%TYPE
DUZ PPOSCIC.PLOC.DUZINA%TYPE
POVRSINA NUMBER(6,2)

BEGIN
POVRSINA := SIR*DUZ;

SELECT DISTINCT COUNT(NAZIV_PLOC) || ', ' || NAZIV_PLOC KOLICINA FROM PPOSCIC.PLOC GROUP BY NAZIV_PLOC;

END KOLIKO;




OPBP šalabahter
SQL
Kreiranje nove tablice: CREATE TABLE ime_tablice (atr1 tip [NOT NULL], atr2 tip [NOT NULL], ... ) ;
Izmjena tablice: ALTER TABLE ime_tablice ADD (atr tip [, ATR TIP]) ; ili ALTER TABLE ime_tablice MODIFY (atr modifikacija);
Unos podataka:
INSERT INTO naziv_tablice VALUES(vrijednost_atr1, vrijednost_atr2,…);
INSERT INTO naziv_tablice (ATR1, ATR2, …) VALUES (vrijednost_atr1, vrijednost_atr2,…);
Ažuriranje tablice: UPDATE naziv_tablice SET atribut1=izraz1[, atribut2=izraz2] [WHERE kriterij selekcije n-torki];
Brisanje podataka: DELETE [FROM] naziv_tablice [WHERE kriterij selekcije n-torki];
Brisanje tablice: DROP TABLE ime_tablice;
Kreiranje pogleda: CREATE VIEW naziv_pogleda[(nazivi atributa pogleda)] AS SELECT . . .
Upiti:
SELECT [DISTINCT] {*|atribut|izraz [alias],…}
FROM [korisnik.]tablica
[WHERE uvjet(i)]
[GROUP BY group_by_uvjet]
[ORDER BY atribut];
Klauzule koje pritom možemo koristiti: IN/NOT IN, BETWEEN ... AND, IS (NOT) NULL,
ORDER BY atribut1 [ ,atribut2,... ASC/DESC], GROUP BY atribut1 [ ,atribut2,...), HAVING
Alias: SELECT naziv_stupca [AS] novi_naziv FROM naziv_tablice;
Logički operatori: SELECT atribut1 [, atribut2, ...] FROM naziv_tablice WHERE uvjet1 AND / OR / NOT uvjet2;
LIKE
% predstavlja string od 0 ili više znakova
_ predstavlja poziciju jednog znaka.
Funkcije za dobivanje zbirnih informacija:
AVG(atribut), SUM(atribut), MIN(atribut), MAX(atribut), COUNT (*/atribut/DISTINCT atribut)
Aritmetičke funkcije: POWER (broj, n), ROUND (broj [,d]), TRUNC (broj [,d]), ABS (broj), SIGN (broj), MOD (broj1, broj2),
SQRT (broj)
Funkcije nad nizom znakova: string1 || string2, LENGTH(str) , SUBSTR(str, i,[, d]) , INSTR(str, sstr [, i]) , UPPER(str) ,
LOWER(str) , TO_NUM (str) , TO_CHAR(str) , LPAD(str, len [, CHAR])