PL/SQL

-- create table about director
CREATE TABLE directors ( id INT NOT NULL PRIMARY KEY, name VARCHAR2(255) NOT NULL, born NUMERIC(4) NOT NULL CHECK (born BETWEEN 1900 AND 2022), gender VARCHAR2(255) NOT NULL, country VARCHAR2(255) NOT NULL );
CREATE SEQUENCE directors_sequence;
CREATE
OR REPLACE TRIGGER directors_on_insert BEFORE INSERT
ON directors FOR EACH ROW
BEGIN
   SELECT
      directors_sequence.NEXTVAL INTO :NEW.id
   FROM
      dual;
END
;
SELECT
   *
FROM
   directors;
DELETE
FROM
   directors;
-- delete all data
-- create table about studios
CREATE TABLE studios ( id INT NOT NULL PRIMARY KEY, name VARCHAR2(255) NOT NULL, founded NUMERIC(4) NOT NULL CHECK (founded BETWEEN 1800 AND 2022), country VARCHAR2(255) NOT NULL );
CREATE SEQUENCE studios_sequence;
CREATE
OR REPLACE TRIGGER studios_on_insert BEFORE INSERT
ON studios FOR EACH ROW
BEGIN
   SELECT
      studios_sequence.NEXTVAL INTO :NEW.id
   FROM
      dual;
END
;
-- create table about movies
CREATE TABLE movies ( id INT NOT NULL PRIMARY KEY, title VARCHAR2(255) NOT NULL, YEAR NUMERIC(4) NOT NULL CHECK (YEAR BETWEEN 1900 AND 2022), director NUMERIC(10) NOT NULL, genre VARCHAR2(255) NOT NULL, rating NUMERIC(2) NOT NULL CHECK (rating BETWEEN 0 AND 10), studio NUMERIC(10) NOT NULL, FOREIGN KEY (studio) REFERENCES studios(id), FOREIGN KEY (director) REFERENCES directors(id) );
CREATE SEQUENCE movies_sequence;
CREATE
OR REPLACE TRIGGER movies_on_insert BEFORE INSERT
ON movies FOR EACH ROW
BEGIN
   SELECT
      movies_sequence.NEXTVAL INTO :NEW.id
   FROM
      dual;
END
;
-- mysql create table about actors
CREATE TABLE actors ( id INT NOT NULL PRIMARY KEY, name VARCHAR2(255) NOT NULL, born NUMERIC(4) NOT NULL CHECK (born BETWEEN 1900 AND 2022), gender VARCHAR2(255) NOT NULL, country VARCHAR2(255) NOT NULL );
CREATE SEQUENCE actors_sequence;
CREATE
OR REPLACE TRIGGER actors_on_insert BEFORE INSERT
ON actors FOR EACH ROW
BEGIN
   SELECT
      actors_sequence.NEXTVAL INTO :NEW.id
   FROM
      dual;
END
;
-- create table about movieActors
CREATE TABLE movieactors ( id INT NOT NULL PRIMARY KEY, movie_id INT, actor_id INT, FOREIGN KEY (actor_id) REFERENCES actors(id), FOREIGN KEY (movie_id) REFERENCES movies(id) );
CREATE SEQUENCE movieactors_sequence;
CREATE
OR REPLACE TRIGGER movieactors_on_insert BEFORE INSERT
ON movieactors FOR EACH ROW
BEGIN
   SELECT
      movieactors_sequence.NEXTVAL INTO :NEW.id
   FROM
      dual;
END
;
-- create table about reviews
CREATE TABLE reviews ( id INT NOT NULL PRIMARY KEY, movie_id NUMERIC(10) NOT NULL, review VARCHAR2(255), rating NUMERIC(2) NOT NULL CHECK (rating BETWEEN 0 AND 10), date_r DATE, FOREIGN KEY (movie_id) REFERENCES movies(id) );
CREATE SEQUENCE reviews_sequence;
CREATE
OR REPLACE TRIGGER reviews_on_insert BEFORE INSERT
ON reviews FOR EACH ROW
BEGIN
   SELECT
      reviews_sequence.NEXTVAL INTO :NEW.id
   FROM
      dual;
END
;
-- inserts for directors
INSERT INTO
   directors ( name, born, gender, country )
VALUES
   (
      'Frank Darabont',
      1959,
      'male',
      'France'
   )
;
INSERT INTO
   directors ( name, born, gender, country )
VALUES
   (
      'Francis Ford Coppola',
      1939,
      'male',
      'USA'
   )
;
INSERT INTO
   directors ( name, born, gender, country )
VALUES
   (
      'Christopher Nolan',
      1970,
      'male',
      'UK'
   )
;
-- inserts for studios
INSERT INTO
   studios ( name, founded, country )
VALUES
   (
      'Castle Rock Entertainment',
      1987,
      'USA'
   )
;
INSERT INTO
   studios ( name, founded, country )
VALUES
   (
      'Paramount Pictures',
      1912,
      'USA'
   )
;
INSERT INTO
   studios ( name, founded, country )
VALUES
   (
      'Warner Bros.',
      1923,
      'USA'
   )
;
-- inserts for movies
INSERT INTO
   movies ( title, YEAR, director, genre, rating, studio )
VALUES
   (
      'The Shawshank Redemption',
      1994,
      1,
      'Drama',
      9.2,
      1
   )
;
INSERT INTO
   movies ( title, YEAR, director, genre, rating, studio )
VALUES
   (
      'The Godfather',
      1972,
      2,
      'Crime',
      9.2,
      2
   )
;
INSERT INTO
   movies ( title, YEAR, director, genre, rating, studio )
VALUES
   (
      'The Godfather: Part II',
      1974,
      2,
      'Crime',
      9.0,
      2
   )
;
INSERT INTO
   movies ( title, YEAR, director, genre, rating, studio )
VALUES
   (
      'The Dark Knight',
      2008,
      3,
      'Action',
      9.0,
      3
   )
;
INSERT INTO
   movies ( title, YEAR, director, genre, rating, studio )
VALUES
   (
      'The Green Mile',
      1999,
      1,
      'Drama',
      8.6,
      1
   )
;
BEGIN
   -- inserts for actors
   -- The Shawshank Redemption
   INSERT INTO
      actors ( name, born, gender, country )
   VALUES
      (
         'Tim Robbins',
         1958,
         'male',
         'USA'
      )
;
INSERT INTO
   actors ( name, born, gender, country )
VALUES
   (
      'Morgan Freeman',
      1937,
      'male',
      'USA'
   )
;
-- The Godfather
INSERT INTO
   actors ( name, born, gender, country )
VALUES
   (
      'Marlon Brando',
      1924,
      'male',
      'USA'
   )
;
INSERT INTO
   actors ( name, born, gender, country )
VALUES
   (
      'Al Pacino',
      1940,
      'male',
      'USA'
   )
;
-- The Godfather: Part II  alpacino
INSERT INTO
   actors ( name, born, gender, country )
VALUES
   (
      'Robert De Niro',
      1943,
      'male',
      'USA'
   )
;
-- The Dark Knight
INSERT INTO
   actors ( name, born, gender, country )
VALUES
   (
      'Christian Bale',
      1974,
      'male',
      'UK'
   )
;
INSERT INTO
   actors ( name, born, gender, country )
VALUES
   (
      'Heath Ledger',
      1979,
      'male',
      'Australia'
   )
;
-- The Green Mile
INSERT INTO
   actors ( name, born, gender, country )
VALUES
   (
      'Tom Hanks',
      1956,
      'male',
      'USA'
   )
;
INSERT INTO
   actors ( name, born, gender, country )
VALUES
   (
      'Michael Clarke Duncan',
      1957,
      'male',
      'USA'
   )
;
END
;
BEGIN
   -- inserts for reviews
   INSERT INTO
      reviews ( movie_id, review, rating, date_r )
   VALUES
      (
         1,
         'Great movie!',
         9,
         TO_DATE('2019-01-01', 'YYYY-MM-DD')
      )
;
INSERT INTO
   reviews ( movie_id, review, rating, date_r )
VALUES
   (
      1,
      'Awesome!',
      10,
      TO_DATE('2019-01-02', 'YYYY-MM-DD')
   )
;
INSERT INTO
   reviews ( movie_id, review, rating, date_r )
VALUES
   (
      2,
      'Pretty good!',
      9,
      TO_DATE( '2010-11-22', 'YYYY-MM-DD')
   )
;
INSERT INTO
   reviews ( movie_id, review, rating, date_r )
VALUES
   (
      2,
      'I liked it!',
      8,
      TO_DATE( '2010-11-23', 'YYYY-MM-DD')
   )
;
INSERT INTO
   reviews ( movie_id, review, rating, date_r )
VALUES
   (
      3,
      'Great movie!',
      9,
      TO_DATE('2019-01-01', 'YYYY-MM-DD')
   )
;
INSERT INTO
   reviews ( movie_id, review, rating, date_r )
VALUES
   (
      3,
      'Awesome!',
      10,
      TO_DATE('2019-01-02', 'YYYY-MM-DD')
   )
;
INSERT INTO
   reviews ( movie_id, review, rating, date_r )
VALUES
   (
      4,
      'Pretty good!',
      9,
      TO_DATE( '2010-11-22', 'YYYY-MM-DD')
   )
;
INSERT INTO
   reviews ( movie_id, review, rating, date_r )
VALUES
   (
      4,
      'I liked it!',
      8,
      TO_DATE( '2010-11-23', 'YYYY-MM-DD')
   )
;
INSERT INTO
   reviews ( movie_id, review, rating, date_r )
VALUES
   (
      5,
      'Great movie!',
      9,
      TO_DATE( '2019-01-01', 'YYYY-MM-DD')
   )
;
INSERT INTO
   reviews ( movie_id, review, rating, date_r )
VALUES
   (
      5,
      'Awesome!',
      10,
      TO_DATE('2019-01-02', 'YYYY-MM-DD')
   )
;
-- inserts for movieActors
INSERT INTO
   movieactors ( movie_id, actor_id )
VALUES
   (
      1,
      1
   )
;
INSERT INTO
   movieactors ( movie_id, actor_id )
VALUES
   (
      1,
      2
   )
;
INSERT INTO
   movieactors ( movie_id, actor_id )
VALUES
   (
      2,
      3
   )
;
INSERT INTO
   movieactors ( movie_id, actor_id )
VALUES
   (
      2,
      4
   )
;
INSERT INTO
   movieactors ( movie_id, actor_id )
VALUES
   (
      3,
      4
   )
;
INSERT INTO
   movieactors ( movie_id, actor_id )
VALUES
   (
      3,
      5
   )
;
INSERT INTO
   movieactors ( movie_id, actor_id )
VALUES
   (
      4,
      6
   )
;
INSERT INTO
   movieactors ( movie_id, actor_id )
VALUES
   (
      4,
      7
   )
;
INSERT INTO
   movieactors ( movie_id, actor_id )
VALUES
   (
      5,
      8
   )
;
INSERT INTO
   movieactors ( movie_id, actor_id )
VALUES
   (
      5,
      9
   )
;
END
;
-- insert, update, delete triggers
CREATE
OR REPLACE TRIGGER movies_log AFTER INSERT
OR
UPDATE
   OR
   DELETE
      ON movies FOR EACH ROW
      BEGIN
         IF inserting
      THEN
         INSERT INTO
            LOG ( table_name, action, date_l )
         VALUES
            (
               'movies', 'insert', SYSDATE
            )
;
ELSIF updating
THEN
   INSERT INTO
      LOG ( table_name, action, date_l )
   VALUES
      (
         'movies', 'update', SYSDATE
      )
;
ELSIF deleting
THEN
   INSERT INTO
      LOG ( table_name, action, date_l )
   VALUES
      (
         'movies', 'delete', SYSDATE
      )
;
      END
      IF;
END
;
CREATE
OR REPLACE TRIGGER actors_log AFTER INSERT
OR
UPDATE
   OR
   DELETE
      ON actors FOR EACH ROW
      BEGIN
         IF inserting
      THEN
         INSERT INTO
            LOG ( table_name, action, date_l )
         VALUES
            (
               'actors', 'insert', SYSDATE
            )
;
ELSIF updating
THEN
   INSERT INTO
      LOG ( table_name, action, date_l )
   VALUES
      (
         'actors', 'update', SYSDATE
      )
;
ELSIF deleting
THEN
   INSERT INTO
      LOG ( table_name, action, date_l )
   VALUES
      (
         'actors', 'delete', SYSDATE
      )
;
      END
      IF;
END
;
CREATE
OR REPLACE TRIGGER studios_log AFTER INSERT
OR
UPDATE
   OR
   DELETE
      ON studios FOR EACH ROW
      BEGIN
         IF inserting
      THEN
         INSERT INTO
            LOG ( table_name, action, date_l )
         VALUES
            (
               'studios', 'insert', SYSDATE
            )
;
ELSIF updating
THEN
   INSERT INTO
      LOG ( table_name, action, date_l )
   VALUES
      (
         'studios', 'update', SYSDATE
      )
;
ELSIF deleting
THEN
   INSERT INTO
      LOG ( table_name, action, date_l )
   VALUES
      (
         'studios', 'delete', SYSDATE
      )
;
      END
      IF;
END
;
CREATE
OR REPLACE TRIGGER reviews_log AFTER INSERT
OR
UPDATE
   OR
   DELETE
      ON reviews FOR EACH ROW
      BEGIN
         IF inserting
      THEN
         INSERT INTO
            LOG ( table_name, action, date_l )
         VALUES
            (
               'reviews', 'insert', SYSDATE
            )
;
ELSIF updating
THEN
   INSERT INTO
      LOG ( table_name, action, date_l )
   VALUES
      (
         'reviews', 'update', SYSDATE
      )
;
ELSIF deleting
THEN
   INSERT INTO
      LOG ( table_name, action, date_l )
   VALUES
      (
         'reviews', 'delete', SYSDATE
      )
;
      END
      IF;
END
;
CREATE
OR REPLACE TRIGGER movieactors_log AFTER INSERT
OR
UPDATE
   OR
   DELETE
      ON movieactors FOR EACH ROW
      BEGIN
         IF inserting
      THEN
         INSERT INTO
            LOG ( table_name, action, date_l )
         VALUES
            (
               'movieActors', 'insert', SYSDATE
            )
;
ELSIF updating
THEN
   INSERT INTO
      LOG ( table_name, action, date_l )
   VALUES
      (
         'movieActors', 'update', SYSDATE
      )
;
ELSIF deleting
THEN
   INSERT INTO
      LOG ( table_name, action, date_l )
   VALUES
      (
         'movieActors', 'delete', SYSDATE
      )
;
      END
      IF;
END
;
CREATE
OR REPLACE TRIGGER directors_log AFTER INSERT
OR
UPDATE
   OR
   DELETE
      ON directors FOR EACH ROW
      BEGIN
         IF inserting
      THEN
         INSERT INTO
            LOG ( table_name, action, date_l )
         VALUES
            (
               'directors', 'insert', SYSDATE
            )
;
ELSIF updating
THEN
   INSERT INTO
      LOG ( table_name, action, date_l )
   VALUES
      (
         'directors', 'update', SYSDATE
      )
;
ELSIF deleting
THEN
   INSERT INTO
      LOG ( table_name, action, date_l )
   VALUES
      (
         'directors', 'delete', SYSDATE
      )
;
      END
      IF;
END
;
-- create sequence
CREATE SEQUENCE log_sequence;
-- create trigger
CREATE
OR REPLACE TRIGGER log_trigger BEFORE INSERT
ON LOG FOR EACH ROW
BEGIN
   SELECT
      log_sequence.NEXTVAL INTO :NEW.id
   FROM
      dual;
END
;
--egy csomag ami a módosítókat tartalmazza, insert, update, delete
CREATE
OR REPLACE PACKAGE modifiers AS FUNCTION berak(table_name VARCHAR2, micsoda VARCHAR2) RETURN NUMBER;
FUNCTION frissit(table_name VARCHAR2, micsoda VARCHAR2) RETURN NUMBER;
FUNCTION kivesz(table_name VARCHAR2, micsoda VARCHAR2) RETURN NUMBER;
END
;
CREATE
OR REPLACE PACKAGE BODY modifiers AS FUNCTION berak(table_name VARCHAR2, micsoda VARCHAR2) RETURN NUMBER IS sql0 VARCHAR2(1000);
BEGIN
   sql0 := 'insert into ' || table_name || ' values (' || micsoda || ')';
EXECUTE IMMEDIATE sql0;
RETURN SQL % rowcount;
END
;
FUNCTION frissit(table_name VARCHAR2, micsoda VARCHAR2) RETURN NUMBER IS sql0 VARCHAR2(1000);
BEGIN
   sql0 := 'update ' || table_name || ' set ' || micsoda;
EXECUTE IMMEDIATE sql0;
RETURN SQL % rowcount;
END
;
FUNCTION kivesz(table_name VARCHAR2, micsoda VARCHAR2) RETURN NUMBER IS sql0 VARCHAR2(1000);
BEGIN
   sql0 := 'delete from ' || table_name || ' where ' || micsoda;
EXECUTE IMMEDIATE sql0;
RETURN SQL % rowcount;
END
;
END
;
-- például
BEGIN
   DBMS_OUTPUT.put_line(modifiers.berak('movies', '1, 1, 1, 1, 1, 1, 1, 1, 1, 1'));
DBMS_OUTPUT.put_line(modifiers.frissit('movies', 'id = 1, title = ''asd'''));
DBMS_OUTPUT.put_line(modifiers.kivesz('reviews', 'id = 5'));
END
;
-- egy csomag ami mindenféle selecteket tartalmaz
CREATE
OR REPLACE PACKAGE queries AS PROCEDURE dynamic_select_5(table_name2 VARCHAR2);
PROCEDURE directormovies(director_name CHAR);
PROCEDURE movie_count_with_higher_rating(rating_be NUMBER);
END
;
CREATE
OR REPLACE PACKAGE BODY queries AS PROCEDURE dynamic_select_5( table_name2 VARCHAR2) IS TYPE cur_typ IS REF CURSOR;
c CUR_TYP;
query_str CHAR(2000);
str_1 VARCHAR2(200);
str_2 VARCHAR2(200);
str_3 VARCHAR2(200);
str_4 VARCHAR2(200);
str_5 VARCHAR2(200);
sql_all VARCHAR2(1000);
sql_5 VARCHAR2(1000);
BEGIN
   SELECT
      listagg(column_name , ', ') INTO sql_all
   FROM
      user_tab_columns
   WHERE
      table_name = table_name2;
SELECT
   regexp_substr(sql_all, '[^,]+,[^,]+,[^,]+,[^,]+,[^,]+') "REGEXP_SUBSTR" INTO sql_5
FROM
   dual;
DBMS_OUTPUT.Put_line(sql_5);
query_str := 'SELECT ' || sql_5 || ' FROM ' || table_name2;
OPEN c FOR query_str;
LOOP FETCH c INTO str_1,
str_2,
str_3,
str_4,
str_5;
EXIT
WHEN
   c % NOTFOUND;
DBMS_OUTPUT.Put_line(str_1 || ' ' || str_2 || ' ' || str_3 || ' ' || str_4 || ' ' || str_5);
END
LOOP;
CLOSE c;
EXCEPTION
WHEN
   OTHERS
THEN
   DBMS_OUTPUT.Put_line('Error, nincs ilyen tabla');
END
;
PROCEDURE directormovies (director_name IN CHAR) AS CURSOR cur IS
SELECT
   title,
   YEAR
FROM
   movies
   inner join
      directors
      ON movies.director = directors.id
WHERE
   directors.name LIKE director_name || '%';
cv cur % ROWTYPE;
BEGIN
   OPEN cur;
FETCH cur INTO cv;
IF cur % NOTFOUND
THEN
   DBMS_OUTPUT.Put_line('Nincs ' || director_name || ' rendezoju film!');
ELSE
   DBMS_OUTPUT.put_line('Cim: ' || cv.title || ' Ev: ' || cv.YEAR);
LOOP FETCH cur INTO cv;
EXIT
WHEN
   cur % NOTFOUND;
DBMS_OUTPUT.put_line('Cim: ' || cv.title || ' Ev: ' || cv.YEAR);
END
LOOP;
END
IF;
CLOSE cur;
EXCEPTION
WHEN
   OTHERS
THEN
   DBMS_OUTPUT.Put_line('Error, nincs ilyen rendezo');
END
;
PROCEDURE movie_count_with_higher_rating (rating_be IN NUMBER) AS CURSOR cur IS
SELECT
   COUNT(*) AS db
FROM
   movies
WHERE
   rating > rating_be;
cv cur % ROWTYPE;
BEGIN
   OPEN cur;
FETCH cur INTO cv;
IF (cv.db = 0)
THEN
   DBMS_OUTPUT.Put_line('Nincs olyan film ami ' || rating_be || ' pontal tobbet kapott volna!');
ELSE
   DBMS_OUTPUT.Put_line('Ennel magasabb ratingu filmek szama: ' || cv.db);
END
IF;
CLOSE cur;
EXCEPTION
WHEN
   OTHERS
THEN
   DBMS_OUTPUT.Put_line('Error');
END
;
END
;
--Például
BEGIN
   queries.directormovies('Frank Darabont');
END
;