mirror of
https://github.com/beekeeper-studio/beekeeper-studio.git
synced 2026-03-13 10:12:54 +08:00
415 lines
8.6 KiB
SQL
415 lines
8.6 KiB
SQL
ALTER SESSION SET CONTAINER=SAKILA;
|
|
|
|
|
|
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;
|
|
/
|