Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- 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
- ;
- -- 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
- -- 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
- ;
- -- TRIGGERS
- -- 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
- ;
- -- CSOMAGOK
- --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
- ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement