DROP TABLE CUSTOMER;
DROP TABLE EMPLOYEE;
DROP TABLE SURGERY;
DROP TABLE SURGEMP;
DROP TABLE SUPPLIER;
DROP TABLE MEDICINE;
DROP TABLE SURGMED;

ALTER SESSION SET NLS_LANGUAGE="ENGLISH";

CREATE TABLE CUSTOMER
(IDC NUMBER(4) PRIMARY KEY,
CNAME VARCHAR(10),
CSURN VARCHAR(15),
CNUM NUMBER(9),
CBIRTH DATE,
CSTART DATE);

INSERT INTO CUSTOMER VALUES
(0001, 'JULIA', 'KONOPKO', 512303767, TO_DATE ('11-JUN-1991', 'DD-MON-YYYY'), TO_DATE('13-FEB-2016', 'DD-MON-YYYY'));
INSERT INTO CUSTOMER VALUES
(0002, 'JANEK', 'SIENKIEWICZ', 657828329, TO_DATE ('03-JUL-1998', 'DD-MON-YYYY'), TO_DATE('29-MAR-2017', 'DD-MON-YYYY'));
INSERT INTO CUSTOMER VALUES
(0003, 'SEBASTIAN', 'MAZUR', 288826572, TO_DATE ('29-OCT-1982', 'DD-MON-YYYY'), TO_DATE('13-OCT-2017', 'DD-MON-YYYY'));
INSERT INTO CUSTOMER VALUES
(0004, 'BARTEK', 'SZYMSKI', 598794611, TO_DATE ('17-JAN-1959', 'DD-MON-YYYY'), TO_DATE('07-JAN-2017', 'DD-MON-YYYY'));
INSERT INTO CUSTOMER VALUES
(0005, 'MARCIN', 'KORNACKI', 893211174, TO_DATE ('15-MAR-1989', 'DD-MON-YYYY'), TO_DATE('27-APR-2018', 'DD-MON-YYYY'));
INSERT INTO CUSTOMER VALUES
(0006, 'ANJELA', 'KOLENDA', 525136483, TO_DATE ('30-DEC-1984', 'DD-MON-YYYY'), TO_DATE('01-JAN-2018', 'DD-MON-YYYY'));
INSERT INTO CUSTOMER VALUES
(0007, 'MAREK', 'TONCZAK', 164527899, TO_DATE ('26-FEB-1993', 'DD-MON-YYYY'), TO_DATE('09-MAR-2017', 'DD-MON-YYYY'));
INSERT INTO CUSTOMER VALUES
(0008, 'ANNA', 'LANGO', 875509092, TO_DATE ('12-MAY-1972', 'DD-MON-YYYY'), TO_DATE('26-JUN-2016', 'DD-MON-YYYY'));
INSERT INTO CUSTOMER VALUES
(0009, 'JAKUB', 'STRELAU', 163422228, TO_DATE ('11-MAR-1994', 'DD-MON-YYYY'), TO_DATE('03-JUL-2017', 'DD-MON-YYYY'));
INSERT INTO CUSTOMER VALUES
(0010, 'MICHAL', 'KUSIAK', 382338209, TO_DATE ('23-OCT-1988', 'DD-MON-YYYY'), TO_DATE('9-MAY-2018', 'DD-MON-YYYY'));

CREATE TABLE EMPLOYEE
(IDE NUMBER(4) PRIMARY KEY,
ENAME VARCHAR(10),
ESURN VARCHAR(15),
ENUM NUMBER(9),
EHIRE DATE,
POSIT VARCHAR(15),
SAL NUMBER(7, 2));

INSERT INTO EMPLOYEE VALUES
(0010, 'RACHEL', 'GREEN', 846299046, TO_DATE('17-SEP-2014', 'DD-MON-YYYY'), 'NURSE', 2100);
INSERT INTO EMPLOYEE VALUES
(0011, 'CHANDLER', 'BING', 275645637, TO_DATE('25-DEC-2012', 'DD-MON-YYYY'), 'ASSISTANT', 2900);
INSERT INTO EMPLOYEE VALUES
(0012, 'MONICA', 'GELLER', 768490036, TO_DATE('09-APR-2013', 'DD-MON-YYYY'), 'NURSE', 2300);
INSERT INTO EMPLOYEE VALUES
(0013, 'PHOEBEY', 'BUFFAY', 711657326, TO_DATE('23-FEB-2014', 'DD-MON-YYYY'), 'ASSISTANT', 3100);
INSERT INTO EMPLOYEE VALUES
(0014, 'JOEY', 'TRIBBIANI', 337258779, TO_DATE('03-DEC-2014', 'DD-MON-YYYY'), 'DENTIST', 4500);
INSERT INTO EMPLOYEE VALUES
(0015, 'ROSS', 'GELLER', 432280402, TO_DATE('11-OCT-2015', 'DD-MON-YYYY'), 'DENTIST', 4200);

select * from employee;

CREATE TABLE SURGERY
(IDS NUMBER(4) PRIMARY KEY,
SDATE DATE,
PRICE NUMBER(7, 2),
IDC NUMBER(4),
SDES VARCHAR(20),
FOREIGN KEY (IDC) REFERENCES CUSTOMER(IDC));

INSERT INTO SURGERY VALUES
(0100, TO_DATE('19-NOV-2017', 'DD-MON-YYYY'), 520, 0001, 'Root canal treatment');
INSERT INTO SURGERY VALUES
(0101, TO_DATE('30-MAR-2017', 'DD-MON-YYYY'), 750, 0002, 'Root canal treatment');
INSERT INTO SURGERY VALUES
(0102, TO_DATE('19-NOV-2017', 'DD-MON-YYYY'), 810, 0001, 'Dental extractions');
INSERT INTO SURGERY VALUES
(0103, TO_DATE('03-JAN-2018', 'DD-MON-YYYY'), 660, 0003, 'Root canal treatment');
INSERT INTO SURGERY VALUES
(0104, TO_DATE('26-FEB-2017', 'DD-MON-YYYY'), 730, 0004, 'Dental Implants');
INSERT INTO SURGERY VALUES
(0105, TO_DATE('19-MAY-2018', 'DD-MON-YYYY'), 1200, 0005, 'Dental extractions');
INSERT INTO SURGERY VALUES
(0106, TO_DATE('13-MAR-2018', 'DD-MON-YYYY'), 890, 0006, 'Biopsiest');
INSERT INTO SURGERY VALUES
(0107, TO_DATE('01-APR-2017', 'DD-MON-YYYY'), 210, 0007, 'Root canal treatment');
INSERT INTO SURGERY VALUES
(0108, TO_DATE('19-NOV-2017', 'DD-MON-YYYY'), 1580, 0008, 'Reconstr. surgery');
INSERT INTO SURGERY VALUES
(0109, TO_DATE('19-NOV-2017', 'DD-MON-YYYY'), 810, 0009, 'Dental extractions');
INSERT INTO SURGERY VALUES
(0110, TO_DATE('30-JUL-2016', 'DD-MON-YYYY'), 920, 0010, 'Dental extractions');
INSERT INTO SURGERY VALUES
(0111, TO_DATE('30-MAY-2018', 'DD-MON-YYYY'), 1180, 0005, 'Jaw Surgery');
INSERT INTO SURGERY VALUES
(0112, TO_DATE('2-MAY-2018', 'DD-MON-YYYY'), 1510, 0010, 'Dental implants');

CREATE TABLE SURGEMP
(IDSE NUMBER(4) PRIMARY KEY,
IDS NUMBER(4),
IDE NUMBER(4),
FOREIGN KEY (IDS) REFERENCES SURGERY(IDS),
FOREIGN KEY (IDE) REFERENCES EMPLOYEE(IDE));

INSERT INTO SURGEMP VALUES
(0293, 0100, 0011);
INSERT INTO SURGEMP VALUES
(0213, 0100, 0014);
INSERT INTO SURGEMP VALUES
(0746, 0101, 0014);
INSERT INTO SURGEMP VALUES
(0946, 0102, 0013);
INSERT INTO SURGEMP VALUES
(0239, 0102, 0014);
INSERT INTO SURGEMP VALUES
(0281, 0103,0015);
INSERT INTO SURGEMP VALUES
(0381, 0104, 0013);
INSERT INTO SURGEMP VALUES
(0846, 0104, 0015);
INSERT INTO SURGEMP VALUES
(0245, 0105, 0013);
INSERT INTO SURGEMP VALUES
(0321, 0105, 0015);
INSERT INTO SURGEMP VALUES
(0753, 0106, 0014);
INSERT INTO SURGEMP VALUES
(0928, 0107, 0014);
INSERT INTO SURGEMP VALUES
(0422, 0107, 0015);
INSERT INTO SURGEMP VALUES
(0751, 0108, 0013);
INSERT INTO SURGEMP VALUES
(0421, 0109, 0014);
INSERT INTO SURGEMP VALUES
(0810, 0110, 0015);
INSERT INTO SURGEMP VALUES
(0921, 0110, 0014);
INSERT INTO SURGEMP VALUES
(0634, 0111, 0011);
INSERT INTO SURGEMP VALUES
(0674, 0111, 0014);
INSERT INTO SURGEMP VALUES
(0563, 0112, 0015);


CREATE TABLE SUPPLIER
(IDSUP NUMBER(4) PRIMARY KEY,
SUPNAME VARCHAR(20),
SUPDATE DATE,
SUPNUM NUMBER(9),
SUPLOC VARCHAR(15));

INSERT INTO SUPPLIER VALUES
(1000, 'MOMO', TO_DATE('12-JAN-2016', 'DD-MON-YYYY'), 947321245, 'AUSTRALIA');
INSERT INTO SUPPLIER VALUES
(1001, 'PHIENCY', TO_DATE('24-OCT-2017', 'DD-MON-YYYY'), 546277725, 'ENGLAND');

CREATE TABLE MEDICINE
(IDM NUMBER(4) PRIMARY KEY,
MNAME VARCHAR(20),
MDATEBUY DATE,
IDSUP NUMBER(4),
FOREIGN KEY (IDSUP) REFERENCES SUPPLIER(IDSUP));

INSERT INTO MEDICINE VALUES
(7563, 'QUARYEBS', TO_DATE('12-JAN-2016', 'DD-MON-YYYY'), 1000);
INSERT INTO MEDICINE VALUES
(7564, 'QUARYEBS', TO_DATE('12-JAN-2016', 'DD-MON-YYYY'), 1000);
INSERT INTO MEDICINE VALUES
(7565, 'QUARYEBS', TO_DATE('12-JAN-2016', 'DD-MON-YYYY'), 1000);
INSERT INTO MEDICINE VALUES
(7566, 'QUARYEBS', TO_DATE('12-JAN-2016', 'DD-MON-YYYY'), 1000);
INSERT INTO MEDICINE VALUES
(7567, 'QUARYEBS', TO_DATE('12-JAN-2016', 'DD-MON-YYYY'), 1000);
INSERT INTO MEDICINE VALUES
(7568, 'QUARYEBS', TO_DATE('12-JAN-2016', 'DD-MON-YYYY'), 1000);
INSERT INTO MEDICINE VALUES
(7569, 'QUARYEBS', TO_DATE('12-JAN-2016', 'DD-MON-YYYY'), 1000);
INSERT INTO MEDICINE VALUES
(7570, 'QUARYEBS', TO_DATE('12-JAN-2016', 'DD-MON-YYYY'), 1000);
INSERT INTO MEDICINE VALUES
(7571, 'QUARYEBS', TO_DATE('12-JAN-2016', 'DD-MON-YYYY'), 1000);
INSERT INTO MEDICINE VALUES
(7572, 'MEOW JYD', TO_DATE('23-APR-2017', 'DD-MON-YYYY'), 1001);
INSERT INTO MEDICINE VALUES
(7573, 'MEOW JYD', TO_DATE('23-APR-2017', 'DD-MON-YYYY'), 1001);
INSERT INTO MEDICINE VALUES
(7574, 'MEOW JYD', TO_DATE('23-APR-2017', 'DD-MON-YYYY'), 1001);
INSERT INTO MEDICINE VALUES
(7575, 'MEOW JYD', TO_DATE('23-APR-2017', 'DD-MON-YYYY'), 1001);
INSERT INTO MEDICINE VALUES
(7576, 'MEOW JYD', TO_DATE('23-APR-2017', 'DD-MON-YYYY'), 1001);
INSERT INTO MEDICINE VALUES
(7577, 'FEWYE', TO_DATE('02-MAY-2017', 'DD-MON-YYYY'), 1000);
INSERT INTO MEDICINE VALUES
(7578, 'FEWYE', TO_DATE('02-MAY-2017', 'DD-MON-YYYY'), 1000);
INSERT INTO MEDICINE VALUES
(7579, 'FEWYE', TO_DATE('02-MAY-2017', 'DD-MON-YYYY'), 1000);
INSERT INTO MEDICINE VALUES
(7580, 'FEWYE', TO_DATE('02-MAY-2017', 'DD-MON-YYYY'), 1000);
INSERT INTO MEDICINE VALUES
(7581, 'FEWYE', TO_DATE('02-MAY-2017', 'DD-MON-YYYY'), 1000);
INSERT INTO MEDICINE VALUES
(7582, 'FEWYE', TO_DATE('02-MAY-2017', 'DD-MON-YYYY'), 1000);
INSERT INTO MEDICINE VALUES
(7583, 'FEWYE', TO_DATE('02-MAY-2017', 'DD-MON-YYYY'), 1000);

CREATE TABLE SURGMED
(IDSM NUMBER(4) PRIMARY KEY,
IDS NUMBER(4),
IDM NUMBER(4),
FOREIGN KEY (IDS) REFERENCES SURGERY(IDS),
FOREIGN KEY (IDM) REFERENCES MEDICINE(IDM));

INSERT INTO SURGMED VALUES
(9000, 0100, 7576);
INSERT INTO SURGMED VALUES
(9001, 0107, 7581);
INSERT INTO SURGMED VALUES
(9002, 0112, 7563);
INSERT INTO SURGMED VALUES
(9003, 0112, 7582);

--        QUERIES

-- UNION show all the employees and customers(id, name, surname, phone number)
SELECT IDE, ENAME, ESURN, ENUM
FROM EMPLOYEE
UNION
SELECT IDC, CNAME, CSURN, CNUM
FROM CUSTOMER;

-- GROUP
--1 show all the customers who spent more than 800 for surgeries

SELECT C.CNAME, C.CSURN,  SUM(S.PRICE)
FROM CUSTOMER C, SURGERY S
WHERE  S.IDC = C.IDC
GROUP BY C.CNAME, C.CSURN
having sum(s.price) > 800;

--2 show all the suppliers who supplied medicines more than 5 times
SELECT S.SUPNAME, COUNT(M.IDM)
FROM SUPPLIER S, MEDICINE M
where s.idsup = m.idsup
GROUP BY S.SUPNAME
having count(idm) > 5;



--CORRELATED ??? show all the surgeries that cost more than average price
SELECT c.cname, s.sdate, s.price
from customer c, surgery s
where s.price = (select max(price) from surgery where idc = c.idc) and s.idc = c.idc;

select c.cname, s.price, s.sdate
from customer c, surgery s
where c.idc = s.idc
order by c.cname;

-- show total year income of all positions(nurses, dentists, assistans) and their amount
SELECT POSIT, SUM(SAL)*12, COUNT(IDE)
FROM EMPLOYEE
GROUP BY POSIT
ORDER BY POSIT;

-- show all the employees who were hired before Tribbiani;
SELECT IDE, ENAME, ESURN, EHIRE
FROM EMPLOYEE
WHERE EHIRE < (SELECT EHIRE FROM EMPLOYEE WHERE ESURN='TRIBBIANI')
ORDER BY EHIRE DESC;

--         TRIGGERS


SET ServerOutput ON
Create or replace trigger R222
After insert or update
on EMPLOYEE
For each row
Begin
DBMS_output.put_line('MY TRIGGER WORKS');
End;

-- 1 AFETR UPDATE  increase salary if position was changed to dentist
SET ServerOutput ON
CREATE OR REPLACE TRIGGER TRIGG11
AFTER UPDATE
ON EMPLOYEE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
    IF :NEW.SAL < 1600 THEN
    DBMS_OUTPUT.PUT_LINE('THE SALARY IS TOO SMALL');
    END IF;
END;

DBMS_OUTPUT.ENABLE;
UPDATE EMPLOYEE
SET SAL = 500
WHERE ENAME='RACHEL';

--   AFTER INSERT
SET ServerOutput ON
CREATE OR REPLACE TRIGGER TRIGG12
BEFORE INSERT
ON EMPLOYEE
REFERENCING NEW AS NEW OLD AS OLD
DECLARE
    COUNT_N INTEGER; COUNT_A INTEGER; COUNT_D INTEGER;
BEGIN
    SELECT COUNT(ENAME) INTO COUNT_N FROM EMPLOYEE WHERE EMPLOYEE.POSIT = 'NURSE';
    SELECT COUNT(ENAME) INTO COUNT_A FROM EMPLOYEE WHERE EMPLOYEE.POSIT = 'ASISSTANT';
    SELECT COUNT(ENAME) INTO COUNT_D FROM EMPLOYEE WHERE EMPLOYEE.POSIT = 'DENTIST';
    DBMS_OUTPUT.PUT_LINE('THERE ARE '||COUNT_N||' NURSES, '||COUNT_A||' ASSISTANTS, '||COUNT_D||' DENTISTS');
END;


INSERT INTO EMPLOYEE VALUES
(0020, 'RACH', 'LOWT', 275645636, TO_DATE('17-jan-2016', 'DD-MON-YYYY'), 'NURSE', 1800);
DBMS_OUTPUT.ENABLE;



--   AFTER DELETE print name and surname of deleted employee
SET ServerOutput ON
CREATE OR REPLACE TRIGGER TRIGG13
AFTER DELETE
ON EMPLOYEE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE OLD_ID INTEGER; OLD_N VARCHAR(12); OLD_S VARCHAR(12);
BEGIN
OLD_ID := :OLD.IDE;
OLD_N := :OLD.ENAME;
OLD_S := :OLD.ESURN;
    delete from surgemp where(ide = OLD_ID);
    DBMS_OUTPUT.PUT_LINE( OLD_n||' ' ||OLD_S||' WAS DELETED FROM TABLE EMPLOYEE');
END;

DELETE FROM EMPLOYEE WHERE IDE='0020';
DBMS_OUTPUT.ENABLE;

--2 BEFORE UPDATE
CREATE OR REPLACE TRIGGER TRIGG21
BEFORE UPDATE
ON SURGERY
FOR EACH ROW
BEGIN
IF :NEW.PRICE > 5000 THEN
DBMS_OUTPUT.PUT_LINE('TOO EXPENSIVE');
END IF;
END;

UPDATE SURGERY
SET PRICE = '1800'
WHERE SDES = 'Jaw Surgery';
DBMS_OUTPUT.ENABLE;
--  BEFORE INSERT don't allow to insert customer who's age is less than 16
CREATE OR REPLACE TRIGGER TRIG22
BEFORE INSERT
ON CUSTOMER
FOR EACH ROW
DECLARE
CUS_age INTEGER;
BEGIN
   SELECT MONTHS_BETWEEN(TO_DATE(sysdate,'DD-MON-YYYY'), TO_DATE(:new.CBIRTH,'DD-MON-YYYY'))/12
   INTO CUS_AGE FROM DUAL;
   IF (CUS_AGE < 16) THEN
      RAISE_APPLICATION_ERROR(-20000,'CUSTOMER age must be greater than or equal to 16.');
    END IF;
END;

INSERT INTO CUSTOMER VALUES
(0018, 'MARCIN', 'KORNACKI', 893211174, TO_DATE('15-MAR-2006', 'DD-MON-YYYY'), TO_DATE('27-APR-2018', 'DD-MON-YYYY'));
DBMS_OUTPUT.ENABLE;



CREATE OR REPLACE TRIGGER NONC
BEFORE INSERT
ON CUSTOMER
FOR EACH ROW
BEGIN
SELECT NVL(MAX(IDC)+1, 1) INTO :NEW.IDC FROM CUSTOMER;
END;

CREATE OR REPLACE TRIGGER NONE
BEFORE INSERT
ON EMPLOYEE
FOR EACH ROW
BEGIN
SELECT NVL(MAX(IDE)+1, 1) INTO :NEW.IDE FROM EMPLOYEE;
END;

CREATE OR REPLACE TRIGGER NONM
BEFORE INSERT
ON MEDICINE
FOR EACH ROW
BEGIN
SELECT NVL(MAX(IDM)+1, 1) INTO :NEW.IDM FROM MEDICINE;
END;

CREATE OR REPLACE TRIGGER NONSUP
BEFORE INSERT
ON SUPPLIER
FOR EACH ROW
BEGIN
SELECT NVL(MAX(IDSUP)+1, 1) INTO :NEW.IDSUP FROM SUPPLIER;
END;

CREATE OR REPLACE TRIGGER NONSE
BEFORE INSERT
ON SURGEMP
FOR EACH ROW
BEGIN
SELECT NVL(MAX(IDSE)+1, 1) INTO :NEW.IDSE FROM SURGEMP;
END;

CREATE OR REPLACE TRIGGER NONS
BEFORE INSERT
ON SURGERY
FOR EACH ROW
BEGIN
SELECT NVL(MAX(IDS)+1, 1) INTO :NEW.IDS FROM SURGERY;
END;

CREATE OR REPLACE TRIGGER NONSM
BEFORE INSERT
ON SURGMED
FOR EACH ROW
BEGIN
SELECT NVL(MAX(IDSM)+1, 1) INTO :NEW.IDSM FROM SURGMED;
END;







CREATE OR REPLACE PROCEDURE ADD_CUSTOMER(
    NEWNAME CUSTOMER.CNAME%TYPE,
    NEWSUR CUSTOMER.CSURN%TYPE,
    NEWNUM CUSTOMER.CNUM%TYPE,
    NEWBIRTH CUSTOMER.CBIRTH%TYPE
)
AS
    TOO_YOUNG EXCEPTION;
    NEWIDC CUSTOMER.IDC%TYPE;
    NEWSTART CUSTOMER.CSTART%TYPE;
BEGIN
    SELECT MAX(IDC) INTO NEWIDC FROM CUSTOMER;

    IF (MONTHS_BETWEEN(SYSDATE, NEWBIRTH)/12 < 18) THEN RAISE TOO_YOUNG;
    ELSE

    INSERT INTO CUSTOMER ("IDC", "CNAME", "CSURN", "CNUM", "CBIRTH", "CSTART")
    VALUES (NEWIDC+1, NEWNAME, NEWSUR, NEWNUM, NEWBIRTH, SYSDATE);

    END IF;

    EXCEPTION
    WHEN TOO_YOUNG THEN DBMS_OUTPUT.PUT_LINE('CUSTOMER IS TOO YOUNG');
END;

SET SERVEROUTPUT ON;

SELECT * FROM CUSTOMER;

EXEC ADD_CUSTOMER('ADAM', 'JAKUBOWICZ', 627194566, TO_DATE ('11-JUN-2003', 'DD-MON-YYYY'));

CREATE OR REPLACE PROCEDURE RAISE_SALARY(
    EPOSIT EMPLOYEE.POSIT%TYPE
)
AS
    CURSOR C IS SELECT IDE FROM EMPLOYEE WHERE POSIT = EPOSIT;
    TEMP EMPLOYEE.IDE%TYPE;
    NO_SUCH_POSITION EXCEPTION;
    PNUM NUMBER(4);
BEGIN
    SELECT COUNT(IDE) INTO PNUM FROM EMPLOYEE WHERE POSIT = EPOSIT;
    IF PNUM = 0 THEN RAISE NO_SUCH_POSITION;
    ELSE
        OPEN C;
        LOOP
            FETCH C INTO TEMP;
            EXIT WHEN C%NOTFOUND;
             UPDATE EMPLOYEE SET SAL = SAL*1.1 WHERE IDE = TEMP;
        END LOOP;
        CLOSE C;
    END IF;
    EXCEPTION
    WHEN NO_SUCH_POSITION THEN DBMS_OUTPUT.PUT_LINE('NO EMPLOYEES WITH SUCH POSITION');
END;

SELECT * FROM  EMPLOYEE;

EXEC RAISE_SALARY('NANNY');
EXEC RAISE_SALARY('NURSE');

CREATE OR REPLACE PROCEDURE ADD_EMPLOYEE(
    NEWNAME EMPLOYEE.ENAME%TYPE,
    NEWSURN EMPLOYEE.ESURN%TYPE,
    NEWNUM EMPLOYEE.ENUM%TYPE,
    NEWPOSIT EMPLOYEE.POSIT%TYPE
)
AS
    NEWIDE EMPLOYEE.IDE%TYPE;
    NEWHIRE EMPLOYEE.EHIRE%TYPE;
    NEWSAL EMPLOYEE.SAL%TYPE;
    NO_SALARY EXCEPTION;
BEGIN
    NEWHIRE := SYSDATE;
    IF NEWPOSIT = 'NURSE' THEN NEWSAL := 800;
    ELSE IF NEWPOSIT = 'DENTIST' THEN NEWSAL := 3500;
        ELSE IF NEWPOSIT = 'ASSISTANT' THEN NEWSAL := 2000;
            ELSE RAISE NO_SALARY;
            END IF;
        END IF;

    SELECT MAX(IDE) INTO NEWIDE FROM EMPLOYEE;

    INSERT INTO EMPLOYEE("IDE", "ENAME", "ESURN", "ENUM", "EHIRE", "POSIT", "SAL")
    VALUES (NEWIDE+1, NEWNAME, NEWSURN, NEWNUM, NEWHIRE, NEWPOSIT, NEWSAL);

    END IF;

    EXCEPTION
    WHEN NO_SALARY THEN DBMS_OUTPUT.PUT_LINE('CANNOT ADD SUCH POSITION WITHOUT SALARY');
END;

SELECT * FROM EMPLOYEE;

EXEC ADD_EMPLOYEE('RAYRAY', 'GREEN', 264810002, 'ASSISTANT');

CREATE OR REPLACE PROCEDURE COUNT_SURG(
    EMPLOYEE EMPLOYEE.IDE%TYPE,
    COUNTER OUT NUMBER
)
AS
BEGIN
    SELECT COUNT(IDSE) INTO COUNTER FROM SURGEMP WHERE IDE = EMPLOYEE;

END;

CREATE OR REPLACE TRIGGER ADD_SURGEMP
BEFORE INSERT
ON SURGEMP
REFERENCING NEW AS NEW
FOR EACH ROW
DECLARE
    COUNTER NUMBER;
BEGIN
    COUNT_SURG(:NEW.IDE, COUNTER);
    IF   counter > 5 THEN
        UPDATE EMPLOYEE SET SAL = SAL*1.1 WHERE IDE = :NEW.IDE;
    END IF;
END;

SELECT * FROM EMPLOYEE;

INSERT INTO SURGEMP VALUES
(0293, 0101, 0015);

CREATE OR REPLACE FUNCTION COUNT_SURG_CUS(
    CUSTOMER_NAME CUSTOMER.CNAME%TYPE,
    CUSTOMER_SURN CUSTOMER.CSURN%TYPE
)
RETURN NUMBER
AS
    COUNTER NUMBER;
    CUSTOM CUSTOMER.IDC%TYPE;
    NO_SUCH_CUSTOMER EXCEPTION;
    COUN NUMBER;
BEGIN
    SELECT COUNT(IDC) INTO COUN FROM CUSTOMER WHERE CNAME = CUSTOMER_NAME AND CSURN = CUSTOMER_SURN;
    IF COUN != 1 THEN RAISE NO_SUCH_CUSTOMER;
    ELSE
    SELECT IDC INTO CUSTOM FROM CUSTOMER WHERE CNAME = CUSTOMER_NAME AND CSURN = CUSTOMER_SURN;
    SELECT COUNT(IDS) INTO COUNTER FROM SURGERY WHERE CUSTOM = IDC;

    RETURN COUNTER;
    END IF;
    EXCEPTION
    WHEN NO_SUCH_CUSTOMER THEN DBMS_OUTPUT.PUT_LINE('NO SUCH CUSTOMER');
END;

CREATE OR REPLACE PROCEDURE CHANGE_SUPPLIER (
    SUPPLIER_NAME SUPPLIER.IDSUP%TYPE,
    SUPPLIER_NEW_NAME SUPPLIER.IDSUP%TYPE
)
AS
    CURSOR C IS SELECT IDM FROM MEDICINE WHERE IDSUP = SUPPLIER_NAME;
    TEMP MEDICINE.IDM%TYPE;
BEGIN
    OPEN C;
    LOOP
        FETCH C INTO TEMP;
        EXIT WHEN C%NOTFOUND;
        UPDATE MEDICINE SET IDSUP = SUPPLIER_NEW_NAME WHERE IDM = TEMP;
    END LOOP;
        CLOSE C;

END;

SELECT * FROM MEDICINE;

EXEC CHANGE_SUPPLIER(1001, 1000);