OracleSHit

--------------------------------------------------------
--  File created - Tuesday-September-17-2019
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table CLIENT
--------------------------------------------------------

  CREATE TABLE "CLIENT"
   (    "ID" NUMBER,
    "NAME" VARCHAR2(32 BYTE)
    "PHONE" VARCHAR2(32 BYTE)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  DDL for Table DESTINATIONS
--------------------------------------------------------

  CREATE TABLE "DESTINATIONS"
   (    "ID" NUMBER,
    "NAME" VARCHAR2(32 BYTE)
    "TOTALRESERVATIONS" NUMBER,
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  DDL for Table POSITIONS
--------------------------------------------------------

  CREATE TABLE "POSITIONS"
   (    "ID" NUMBER,
    "NAME" VARCHAR2(32 BYTE)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  DDL for Table RESERVATIONS
--------------------------------------------------------

  CREATE TABLE "RESERVATIONS"
   (    "ID" NUMBER,
    "DESTINATION_ID" NUMBER,
    "CLIENT_ID" NUMBER,
    "EMPLOYEE_ID" NUMBER,
    "RDATE" DATE,
    "RHOUR" TIMESTAMP (6),
    "PRICE" FLOAT(126)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  DDL for Table EMPLOYEE
--------------------------------------------------------

  CREATE TABLE "EMPLOYEE"
   (    "ID" NUMBER,
    "POSITION_ID" NUMBER DEFAULT NULL,
    "NAME" VARCHAR2(32 BYTE),
    "PHONE" VARCHAR2(32 BYTE)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
REM INSERTING INTO CLIENT
SET DEFINE OFF;
REM INSERTING INTO DESTINATIONS
SET DEFINE OFF;
REM INSERTING INTO POSITIONS
SET DEFINE OFF;
REM INSERTING INTO RESERVATIONS
SET DEFINE OFF;
REM INSERTING INTO EMPLOYEE
SET DEFINE OFF;
--------------------------------------------------------
--  DDL for Index CLIENT_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "CLIENT_PK" ON "CLIENT" ("ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  DDL for Index DESTINATIONS_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "DESTINATIONS_PK" ON "DESTINATIONS" ("ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  DDL for Index POSITIONS_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "POSITIONS_PK" ON "POSITIONS" ("ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  DDL for Index RESERVATIONS_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "RESERVATIONS_PK" ON "RESERVATIONS" ("ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  DDL for Index EMPLOYEE_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "EMPLOYEE_PK" ON "EMPLOYEE" ("ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  Constraints for Table CLIENT
--------------------------------------------------------

  ALTER TABLE "CLIENT" ADD CONSTRAINT "CLIENT_PK" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE;

  ALTER TABLE "CLIENT" MODIFY ("ID" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table DESTINATIONS
--------------------------------------------------------

  ALTER TABLE "DESTINATIONS" ADD CONSTRAINT "DESTINATIONS_PK" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE;

  ALTER TABLE "DESTINATIONS" MODIFY ("ID" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table POSITIONS
--------------------------------------------------------

  ALTER TABLE "POSITIONS" ADD CONSTRAINT "POSITIONS_PK" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE;

  ALTER TABLE "POSITIONS" MODIFY ("ID" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table RESERVATIONS
--------------------------------------------------------

  ALTER TABLE "RESERVATIONS" ADD CONSTRAINT "RESERVATIONS_PK" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE;

  ALTER TABLE "RESERVATIONS" MODIFY ("ID" NOT NULL ENABLE);

  ALTER TABLE "RESERVATIONS" MODIFY ("DESTINATION_ID" NOT NULL ENABLE);

  ALTER TABLE "RESERVATIONS" MODIFY ("CLIENT_ID" NOT NULL ENABLE);

  ALTER TABLE "RESERVATIONS" MODIFY ("EMPLOYEE_ID" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table EMPLOYEE
--------------------------------------------------------

  ALTER TABLE "EMPLOYEE" ADD CONSTRAINT "EMPLOYEE_PK" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE;

  ALTER TABLE "EMPLOYEE" MODIFY ("ID" NOT NULL ENABLE);

  ALTER TABLE "EMPLOYEE" MODIFY ("POSITION_ID" NOT NULL ENABLE);
--------------------------------------------------------
--  Ref Constraints for Table RESERVATIONS
--------------------------------------------------------

  ALTER TABLE "RESERVATIONS" ADD CONSTRAINT "RESERVATIONS_FK1" FOREIGN KEY ("DESTINATION_ID")
      REFERENCES "DESTINATIONS" ("ID") ENABLE;

  ALTER TABLE "RESERVATIONS" ADD CONSTRAINT "RESERVATIONS_FK2" FOREIGN KEY ("CLIENT_ID")
      REFERENCES "CLIENT" ("ID") ENABLE;

  ALTER TABLE "RESERVATIONS" ADD CONSTRAINT "RESERVATIONS_FK3" FOREIGN KEY ("EMPLOYEE_ID")
      REFERENCES "EMPLOYEE" ("ID") ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table EMPLOYEE
--------------------------------------------------------

  ALTER TABLE "EMPLOYEE" ADD CONSTRAINT "EMPLOYEE_FK1" FOREIGN KEY ("ID")
      REFERENCES "POSITIONS" ("ID") ENABLE;


--------------------------------------------------------
--  Auto Increment Triggers
--------------------------------------------------------

CREATE SEQUENCE employee_id_seq;
CREATE SEQUENCE client_id_seq;
CREATE SEQUENCE positions_id_seq;
CREATE SEQUENCE reservations_id_seq;
CREATE SEQUENCE destinations_id_seq;

CREATE TRIGGER employee_bi
BEFORE INSERT ON employee
FOR EACH ROW
BEGIN
    SELECT employee_id_seq.NEXTVAL
    INTO :NEW.id
    FROM dual;
END;

CREATE TRIGGER client_bi
BEFORE INSERT ON client
FOR EACH ROW
BEGIN
    SELECT client_id_seq.NEXTVAL
    INTO :NEW.id
    FROM dual;
END;

CREATE TRIGGER positions_bi
BEFORE INSERT ON positions
FOR EACH ROW
BEGIN
    SELECT positions_id_seq.NEXTVAL
    INTO :NEW.id
    FROM dual;
END;

CREATE TRIGGER reservations_bi
BEFORE INSERT ON reservations
FOR EACH ROW
BEGIN
    SELECT reservations_id_seq.NEXTVAL
    INTO :NEW.id
    FROM dual;
END;

CREATE TRIGGER destinations_bi
BEFORE INSERT ON destinations
FOR EACH ROW
BEGIN
    SELECT destinations_id_seq.NEXTVAL
    INTO :NEW.id
    FROM dual;
END;

--------------------------------------------------------
--  PROCEDURES
--------------------------------------------------------

DECLARE
    mSelectedRows   NUMBER(2);
    mClient         client%ROWTYPE;
BEGIN

    ------------------
    -- Select Rows
    ------------------

    SELECT  id, name, phone
    INTO    mClient
    FROM    client
    WHERE   phone LIKE '+359%'
    AND     name LIKE 'Kol%';


    --------------------------
    -- If one row is found,
    -- print it
    --------------------------

    IF sql%found THEN
        mSelectedRows := sql%rowcount;
        DBMS_OUTPUT.put_line(mSelectedRows || ' row selected');
        DBMS_OUTPUT.put_line('Name: ' || mClient.name || ' -- Phone: ' || mClient.phone);
    END IF;

    ---------------
    -- Exceptions
    ---------------
    EXCEPTION

    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.put_line('No rows found!');

    WHEN TOO_MANY_ROWS THEN
        mSelectedRows := sql%rowcount;
        DBMS_OUTPUT.put_line(mSelectedRows || ' rows that match found');
END;

CREATE OR REPLACE PROCEDURE createAutoIncrement(
    tableName       VARCHAR2,
    columnNameAI    VARCHAR2
)
IS
    sequenceName            VARCHAR2(64);
    sequenceStatement       VARCHAR2(64);
    triggerStatement        VARCHAR2(256);
BEGIN

    --------------------------------------------------
    -- Check if table exists
    --------------------------------------------------
    FOR CUR_VAL IN (
        SELECT  COUNT(*) AS tableCount
        FROM    tab
        WHERE   tname LIKE tableName
    )
    LOOP
        ----------------------------------------------
        -- If table does not exist, inform user
        -- and exit procedure
        ----------------------------------------------
        IF CUR_VAL.tableCount < 1 THEN
            DBMS_OUTPUT.put_line('Table ' || tableName || ' does not exist');
            RETURN;
        END IF;
    END LOOP;

    --------------------------------------------------
    -- Build sequence name
    --------------------------------------------------
    sequenceName        := tableName || '_id_seq';

    --------------------------------------------------
    -- Check if sequence exists
    --------------------------------------------------
    FOR CUR_VAL IN (
        SELECT  COUNT(*) AS seqCount
        FROM    user_sequences
        WHERE   sequence_name LIKE UPPER(sequenceName)
    )
    LOOP
        ----------------------------------------------
        -- If sequence exists, inform user
        -- and exit procedure
        ----------------------------------------------
        IF CUR_VAL.seqCount > 0 THEN
            DBMS_OUTPUT.put_line('Sequence already exists');
            RETURN;
        END IF;
    END LOOP;

    --------------------------------------------------
    -- Check if trigger already exists
    --------------------------------------------------
    FOR CUR_VAL IN (
        SELECT  COUNT(*) AS trgCount
        FROM    user_triggers
        WHERE   trigger_Name LIKE UPPER(tableName || '_bi')
    )
    LOOP
        ----------------------------------------------
        -- If trigger exists, inform user
        -- and exit procedure
        ----------------------------------------------
        IF CUR_VAL.trgCount > 0 THEN
            DBMS_OUTPUT.put_line('Trigger already exists');
            RETURN;
        END IF;
    END LOOP;

    --------------------------------------------------
    -- Inform User and settle into local variable
    --------------------------------------------------
    DBMS_OUTPUT.put_line('Building sequence...!');
    sequenceStatement := 'CREATE SEQUENCE ' || sequenceName;

    --------------------------------------------------
    -- Execute Create sequence statement
    --------------------------------------------------
    DBMS_OUTPUT.put_line('Sequence built!');
    EXECUTE IMMEDIATE sequenceStatement;

    --------------------------------------------------
    -- Inform user about query execution
    --------------------------------------------------
    DBMS_OUTPUT.put_line('Sequence executed!');

    --------------------------------------------------
    -- Building Trigger Query
    -- And executing it to
    -- create the trigger
    -- for auto-incrementing
    -- value
    --------------------------------------------------

    DBMS_OUTPUT.put_line('Building trigger query...');
    triggerStatement := 'CREATE TRIGGER ' || tableName || '_bi
    BEFORE INSERT ON ' || tableName  || '
    FOR EACH ROW
    BEGIN
        SELECT ' || sequenceName || '.nextval
        INTO :new.' || columnNameAI || '
        FROM dual;
    END;';

    --------------------------------------------------
    -- Debug
    --------------------------------------------------
    DBMS_OUTPUT.put_line(triggerStatement);

    --------------------------------------------------
    -- Trigger query created and informed user
    --------------------------------------------------
    DBMS_OUTPUT.put_line('Trigger query created!');

    --------------------------------------------------
    -- Executing create trigger statement and
    -- informing the user
    --------------------------------------------------
    EXECUTE IMMEDIATE triggerStatement;

    DBMS_OUTPUT.put_line('Trigger query executed!');
END;


--------------------------------------------------------
--  Free Reservations for destination
--------------------------------------------------------
DECLARE
    destinationName destinations.name%TYPE;
    freeSpots       destinations.totalreservations%TYPE;
    totalSpots      destinations.totalreservations%TYPE;
BEGIN
    ------------------------------
    -- Get user input
    ------------------------------
    destinationName := '&name';

    ------------------------------
    -- Select total reservations
    -- and free reservations
    -- for the inputted
    -- destination into
    -- freeSpots and totalSpots
    -- local variables
    ------------------------------
    SELECT
        totalreservations,
        (totalreservations - (SELECT COUNT(*)
        FROM
            reservations
        LEFT JOIN
            destinations
        ON
            reservations.destination_id = destinations.id
        WHERE
            destinations.name LIKE destinationName))
        AS
            FreeSlots
    INTO
        totalSpots,
        freeSpots
    FROM
        destinations
    WHERE
        name LIKE destinationName;

    ------------------------------
    -- See free spots
    ------------------------------
    DBMS_OUTPUT.put_line(freeSpots || ' out of ' || totalSpots || ' reservations are free for ' || destinationName);

    ------------------------------
    -- Foolproof handling
    -- and clairvoyance of
    -- basic errors
    ------------------------------
    EXCEPTION

    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.put_line('No rows found!');

    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.put_line('Too many rows found! Must be exactly one. Please check your SQL!');

END;

--------------------------------------------------------
--  CURSORS
--------------------------------------------------------

CURSOR returnClientType (mPhone IN VARCHAR2)
    RETURN client%ROWTYPE

IS
    SELECT  *
    FROM    client
    WHERE   phone LIKE mPhone;


--------------------------------------------------------
--  SELECT Statements
--------------------------------------------------------

--------------------------------------
-- Reservations between two dates
--------------------------------------

SELECT
    COUNT(*) AS reservationsCount
FROM
    reservations
LEFT JOIN
    employee
ON
    reservations.employee_id = employee.id
WHERE
    reservations.rdate
    BETWEEN
        TO_DATE('&date', 'dd/mm/yyyy')
    AND
        TO_DATE('&seconddate', 'dd/mm/yyyy');

--------------------------------------
-- Tickets by date for client
--------------------------------------

SELECT
    *
FROM
    reservations
LEFT JOIN
    client
ON
    reservations.client_id = client.id
ORDER BY
    RDATE;

--------------------------------------
-- Last 5 Reservations in DESC order
--------------------------------------

SELECT
    *
FROM
    (SELECT * FROM reservations ORDER BY id DESC) reservationsReversed
RIGHT JOIN
    employee
ON
    reservationsReversed.employee_id = employee.id
WHERE
    ROWNUM <= 5
ORDER BY
    ROWNUM DESC;


-----------------------------------------
-- Reservation by:
--
-- destination
-- date
-- time
--
-----------------------------------------

SELECT
    *
FROM
    reservations
LEFT JOIN
    destinations
ON
    reservations.destination_id = destinations.id
WHERE
    name LIKE '&name'
    AND
        rdate LIKE TO_DATE('&date', 'dd/mm/yyyy')
    AND
        TO_CHAR(CAST(rhour AS DATE), 'HH24:MI')
        LIKE
            TO_CHAR(CAST(TO_TIMESTAMP('&hour', 'HH24:MI') AS DATE), 'HH24:MI');

--------------------------------------------------------
--  INSERT Statements
--------------------------------------------------------

INSERT INTO RESERVATIONS (ID,DESTINATION_ID,CLIENT_ID,EMPLOYEE_ID,RDATE,RHOUR,PRICE) VALUES (1,1,2,1,TO_DATE('2019-09-18','RRRR-MM-DD'),TO_TIMESTAMP('2019-09-18 11:24:25.338000000','RRRR-MM-DD HH24:MI:SSXFF'),500);
INSERT INTO RESERVATIONS (ID,DESTINATION_ID,CLIENT_ID,EMPLOYEE_ID,RDATE,RHOUR,PRICE) VALUES (2,1,1,1,TO_DATE('2019-09-17','RRRR-MM-DD'),TO_TIMESTAMP('2019-09-07 11:38:24.341000000','RRRR-MM-DD HH24:MI:SSXFF'),340);


INSERT INTO POSITIONS (ID,NAME) VALUES (1,'Manager');
INSERT INTO POSITIONS (ID,NAME) VALUES (2,'Employee');
INSERT INTO POSITIONS (ID,NAME) VALUES (3,'Example');


INSERT INTO DESTINATIONS (ID,NAME, TOTALRESERVATIONS) VALUES (1,'Varna', 15);
INSERT INTO DESTINATIONS (ID,NAME, TOTALRESERVATIONS) VALUES (2,'Sofia', 4);
INSERT INTO DESTINATIONS (ID,NAME, TOTALRESERVATIONS) VALUES (3,'Moscow', 50);
INSERT INTO DESTINATIONS (ID,NAME, TOTALRESERVATIONS) VALUES (4,'Shanghai', 12);


INSERT INTO CLIENT (ID,NAME,PHONE) VALUES (1,'Kolev','+359876181955');
INSERT INTO CLIENT (ID,NAME,PHONE) VALUES (2,'Ivan','+359876181954');


INSERT INTO EMPLOYEE (ID,POSITION_ID,NAME,PHONE) VALUES (1,1,'Kolev','+359876181954');

--------------------------------------------------------
--  UPDATE Statements
--------------------------------------------------------

UPDATE
    reservations
SET
    price = 890
WHERE
    id = 1;


UPDATE
    positions
SET
    name = 'Database Engineer'
WHERE
    id = 3;

--------------------------------------------------------
--  DELETE Statements
--------------------------------------------------------

DELETE FROM
    destinations
WHERE
    id = 4;