Skip to content
Permalink
Branch: master
Find file Copy path
Find file Copy path
1 contributor

Users who have contributed to this file

411 lines (371 sloc) 9.06 KB
BEGIN
FOR r IN (SELECT * FROM user_types) LOOP
EXECUTE IMMEDIATE 'DROP TYPE ' || r.type_name || ' FORCE';
END LOOP;
END;
/
CREATE TYPE LANGUAGE_T AS OBJECT (
language_id SMALLINT,
name CHAR(20),
last_update DATE
);
/
CREATE TYPE LANGUAGES_T AS TABLE OF LANGUAGE_T;
/
CREATE TYPE FILM_T AS OBJECT (
film_id int,
title VARCHAR(255),
description CLOB,
release_year VARCHAR(4),
language LANGUAGE_T,
original_language LANGUAGE_T,
rental_duration SMALLINT,
rental_rate DECIMAL(4,2),
length SMALLINT,
replacement_cost DECIMAL(5,2),
rating VARCHAR(10),
special_features VARCHAR(100),
last_update DATE
);
/
CREATE TYPE FILMS_T AS TABLE OF FILM_T;
/
CREATE TYPE ACTOR_T AS OBJECT (
actor_id numeric,
first_name VARCHAR(45),
last_name VARCHAR(45),
last_update DATE
);
/
CREATE TYPE ACTORS_T AS TABLE OF ACTOR_T;
/
CREATE TYPE CATEGORY_T AS OBJECT (
category_id SMALLINT,
name VARCHAR(25),
last_update DATE
);
/
CREATE TYPE CATEGORIES_T AS TABLE OF CATEGORY_T;
/
CREATE TYPE FILM_INFO_T AS OBJECT (
film FILM_T,
actors ACTORS_T,
categories CATEGORIES_T
);
/
CREATE TYPE COUNTRY_T AS OBJECT (
country_id SMALLINT,
country VARCHAR(50),
last_update DATE
);
/
CREATE TYPE CITY_T AS OBJECT (
city_id int,
city VARCHAR(50),
country COUNTRY_T,
last_update DATE
);
/
CREATE TYPE ADDRESS_T AS OBJECT (
address_id int,
address VARCHAR(50),
address2 VARCHAR(50),
district VARCHAR(20),
city CITY_T,
postal_code VARCHAR(10),
phone VARCHAR(20),
last_update DATE
);
/
CREATE TYPE CUSTOMER_T AS OBJECT (
customer_id INT,
first_name VARCHAR(45),
last_name VARCHAR(45),
email VARCHAR(50),
address ADDRESS_T,
active CHAR(1),
create_date DATE,
last_update DATE
);
/
CREATE TYPE CUSTOMERS_T AS TABLE OF CUSTOMER_T;
/
CREATE TYPE CUSTOMER_RENTAL_HISTORY_T AS OBJECT (
customer CUSTOMER_T,
films FILMS_T
);
/
CREATE OR REPLACE PACKAGE RENTALS AS
FUNCTION GET_ACTOR(p_actor_id INT) RETURN ACTOR_T;
FUNCTION GET_ACTORS RETURN ACTORS_T;
FUNCTION GET_CUSTOMER(p_customer_id INT) RETURN CUSTOMER_T;
FUNCTION GET_CUSTOMERS RETURN CUSTOMERS_T;
FUNCTION GET_FILM(p_film_id INT) RETURN FILM_T;
FUNCTION GET_FILMS RETURN FILMS_T;
FUNCTION GET_CUSTOMER_RENTAL_HISTORY(p_customer_id INT) RETURN CUSTOMER_RENTAL_HISTORY_T;
FUNCTION GET_CUSTOMER_RENTAL_HISTORY(p_customer CUSTOMER_T) RETURN CUSTOMER_RENTAL_HISTORY_T;
FUNCTION GET_FILM_INFO(p_film_id INT) RETURN FILM_INFO_T;
FUNCTION GET_FILM_INFO(p_film FILM_T) RETURN FILM_INFO_T;
END RENTALS;
/
CREATE OR REPLACE PACKAGE BODY RENTALS AS
FUNCTION GET_ACTOR(p_actor_id INT) RETURN ACTOR_T IS
v_result ACTOR_T;
BEGIN
SELECT ACTOR_T(
a.actor_id,
a.first_name,
a.last_name,
a.last_update
)
INTO v_result
FROM actor a
WHERE a.actor_id = p_actor_id;
RETURN v_result;
END GET_ACTOR;
FUNCTION GET_ACTORS RETURN ACTORS_T IS
v_result ACTORS_T;
BEGIN
SELECT ACTOR_T(
a.actor_id,
a.first_name,
a.last_name,
a.last_update
)
BULK COLLECT INTO v_result
FROM actor a;
RETURN v_result;
END GET_ACTORS;
FUNCTION GET_CUSTOMER(p_customer_id INT) RETURN CUSTOMER_T IS
v_result CUSTOMER_T;
BEGIN
SELECT CUSTOMER_T(
c.customer_id,
c.first_name,
c.last_name,
c.email,
ADDRESS_T(
a.address_id,
a.address,
a.address2,
a.district,
CITY_T(
i.city_id,
i.city,
COUNTRY_T(
o.country_id,
o.country,
o.last_update
),
i.last_update
),
a.postal_code,
a.phone,
a.last_update
),
c.active,
c.create_date,
c.last_update
)
INTO v_result
FROM customer c
LEFT JOIN address a ON c.address_id = a.address_id
LEFT JOIN city i ON a.city_id = i.city_id
LEFT JOIN country o ON i.country_id = o.country_id
WHERE c.customer_id = p_customer_id;
RETURN v_result;
END GET_CUSTOMER;
FUNCTION GET_CUSTOMERS RETURN CUSTOMERS_T IS
v_result CUSTOMERS_T;
BEGIN
SELECT CUSTOMER_T(
c.customer_id,
c.first_name,
c.last_name,
c.email,
ADDRESS_T(
a.address_id,
a.address,
a.address2,
a.district,
CITY_T(
i.city_id,
i.city,
COUNTRY_T(
o.country_id,
o.country,
o.last_update
),
i.last_update
),
a.postal_code,
a.phone,
a.last_update
),
c.active,
c.create_date,
c.last_update
)
BULK COLLECT INTO v_result
FROM customer c
LEFT JOIN address a ON c.address_id = a.address_id
LEFT JOIN city i ON a.city_id = i.city_id
LEFT JOIN country o ON i.country_id = o.country_id;
RETURN v_result;
END GET_CUSTOMERS;
FUNCTION GET_FILM(p_film_id INT) RETURN FILM_T IS
v_result FILM_T;
BEGIN
SELECT FILM_T(
f.film_id,
f.title,
f.description,
f.release_year,
NVL2(l1.language_id,
LANGUAGE_T(
l1.language_id,
l1.name,
l1.last_update
),
NULL
),
NVL2(l2.language_id,
LANGUAGE_T(
l2.language_id,
l2.name,
l2.last_update
),
NULL
),
f.rental_duration,
f.rental_rate,
f.length,
f.replacement_cost,
f.rating,
f.special_features,
f.last_update
)
INTO v_result
FROM film f
LEFT JOIN language l1 ON f.language_id = l1.language_id
LEFT JOIN language l2 ON f.original_language_id = l2.language_id
WHERE f.film_id = p_film_id;
RETURN v_result;
END GET_FILM;
FUNCTION GET_FILMS RETURN FILMS_T IS
v_result FILMS_T;
BEGIN
SELECT FILM_T(
f.film_id,
f.title,
f.description,
f.release_year,
NVL2(l1.language_id,
LANGUAGE_T(
l1.language_id,
l1.name,
l1.last_update
),
NULL
),
NVL2(l2.language_id,
LANGUAGE_T(
l2.language_id,
l2.name,
l2.last_update
),
NULL
),
f.rental_duration,
f.rental_rate,
f.length,
f.replacement_cost,
f.rating,
f.special_features,
f.last_update
)
BULK COLLECT INTO v_result
FROM film f
LEFT JOIN language l1 ON f.language_id = l1.language_id
LEFT JOIN language l2 ON f.original_language_id = l2.language_id;
RETURN v_result;
END GET_FILMS;
FUNCTION GET_CUSTOMER_RENTAL_HISTORY(p_customer_id INT) RETURN CUSTOMER_RENTAL_HISTORY_T IS
BEGIN
RETURN GET_CUSTOMER_RENTAL_HISTORY(GET_CUSTOMER(p_customer_id));
END GET_CUSTOMER_RENTAL_HISTORY;
FUNCTION GET_CUSTOMER_RENTAL_HISTORY(p_customer CUSTOMER_T) RETURN CUSTOMER_RENTAL_HISTORY_T IS
v_films FILMS_T;
BEGIN
SELECT GET_FILM(f.film_id)
BULK COLLECT INTO v_films
FROM (
SELECT DISTINCT f.film_id
FROM film f
LEFT JOIN inventory i ON i.film_id = f.film_id
LEFT JOIN rental r ON r.inventory_id = i.inventory_id
WHERE r.customer_id = p_customer.customer_id
) f
ORDER BY f.film_id;
RETURN CUSTOMER_RENTAL_HISTORY_T(
p_customer,
v_films
);
END GET_CUSTOMER_RENTAL_HISTORY;
FUNCTION GET_FILM_INFO(p_film_id INT) RETURN FILM_INFO_T IS
BEGIN
RETURN GET_FILM_INFO(GET_FILM(p_film_id));
END GET_FILM_INFO;
FUNCTION GET_FILM_INFO(p_film FILM_T) RETURN FILM_INFO_T IS
v_actors ACTORS_T;
v_categories CATEGORIES_T;
BEGIN
SELECT ACTOR_T(
a.actor_id,
a.first_name,
a.last_name,
a.last_update
)
BULK COLLECT INTO v_actors
FROM actor a
JOIN film_actor fa ON fa.actor_id = a.actor_id
WHERE fa.film_id = p_film.film_id
ORDER BY a.actor_id;
SELECT CATEGORY_T(
c.category_id,
c.name,
c.last_update
)
BULK COLLECT INTO v_categories
FROM category c
JOIN film_category fc ON fc.category_id = c.category_id
WHERE fc.film_id = p_film.film_id
ORDER BY c.category_id;
RETURN FILM_INFO_T(
p_film,
v_actors,
v_categories
);
END GET_FILM_INFO;
END RENTALS;
/
CREATE PACKAGE customers AS
TYPE person IS RECORD (
first_name VARCHAR2(50),
last_name VARCHAR2(50)
);
FUNCTION get_customer(p_customer_id NUMBER) RETURN person;
END customers;
/
CREATE PACKAGE BODY customers AS
FUNCTION get_customer(p_customer_id NUMBER) RETURN person IS
v_person customers.person;
BEGIN
SELECT c.first_name, c.last_name
INTO v_person
FROM customer c
WHERE c.customer_id = p_customer_id;
RETURN v_person;
END get_customer;
END customers;
/
You can’t perform that action at this time.