Примеры. Примеры следующая инструкция предоставляет select и delete привилегии пользователю

Практикум

Вопросы и ответы

Примеры

• Следующая инструкция предоставляет SELECT и DELETE привилегии пользователю. Опция WHITH GRANT OPTION дает пользователю GRANT полномочия:

GRANT SELECT, DELETE ON COUNTRY TO CHLOE WITH GRANT OPTION;

• Следующая инструкция предоставляет EXECUTE привилегии на процедуру другим процедурам и пользователю:

GRANT EXECUTE ON PROCEDURE GET_EMP_PROJ TO PROCEDURE ADD_EMP_PROJ_LUIS;

1. При создании таблицы обязательно ли в ее имени использовать суффикс _tbl?

Определенно нет. Вас ничего не принуждают его использовать. Например, таблице с информацией о служащих можно назначить либо одно из следующих имен, либо любое другое, которое будет соответствовать хранимым в этой таблице данным:

EMPLOYEE EMP_TBL EMPLOYEE TBL EMPLOYEE^TABLE WORKER

1. Почему при удалении таблицы так важно указывать имя соответствующей схемы?

Вот вам непридуманная история о молодом администраторе базы данных, удалившем таблицу. Один программист создал в рамках своей схемы таблицу с именем точно таким же, как у таблицы с производственной информацией. Прошло некоторое время, и он из компании уволился. При попытке ликвидации его учетной записи оператор DROP USER вернул ошибку из-за каких-то принадлежащих программисту объектов, оставшихся в базе данных. Исследование проблемы показало, что таблица, созданная тем программистом, не нужна и по отношению к ней был применен оператор DROP TABLE.

Все прошло прекрасно, но возникла другая проблема – оказалось, что администратор применил оператор DROP TABLE, войдя в базу данных под именем производственной схемы. Как было бы хорошо, если бы администратор указал имя схемы или владельца удаляемой таблицы. Да, была удалена не та таблица из не той схемы. На восстановление производственной базы данных потребовалось почти восемь часов.

Тесты

1. Будет ли работать следующий оператор CREATE TABLE? Если нет, то что нужно в нем исправить?

CREATE TABLE EMPLOYEE_TBL AS (

SSN NUMBER(9) NOT NULL,

LAST_NAME VARCHAR(20) NOT NULL

FIRST_NAME VARCHAR(20) NOT NULL,

MIDDLE_NAME VARCHAR(20) NOT NULL,

ST ADDRESS VARCHAR2(30) NOT NULL,

CITY CHAR(20) NOT NULL,

STATE CHAR(2) NOT NULL,

ZIP NUMBER(4) NOT NULL,

DATE HIRED DATE;

1. Можно ли удалить столбец из таблицы?

SET NAMES WIN1251;

SET SQL DIALECT 3;

CREATE DATABASE 'C:\Temp\FifthOcean.gdb' USER 'boss' PASSWORD 'ray8)bow' PAGE_SIZE 1024 DEFAULT CHARACTER SET WIN1251;

CREATE TABLE customer (

id_customer INTEGER NOT NULL,

sity VARCHAR(40) NOT NULL,

company_name VARCHAR(100) NOT NULL,

juridical_person VARCHAR(200) NOT NULL,

bank_properties BLOB SUB_TYPE 1,

adress VARCHAR(100) NOT NULL,

account_adress VARCHAR(100) NOT NULL,

comment BLOB SUB_TYPE 1

);

ALTER TABLE customer

ADD PRIMARY KEY (id_customer);

GRANT ALL ON customer TO ACCOUNTANT;

GRANT ALL ON customer TO MANAGER;

CREATE TABLE how_to_contact_customer (

id_contact INTEGER NOT NULL,

id_customer INTEGER NOT NULL,

contact_method VARCHAR(50) NOT NULL,

contact VARCHAR(50) NOT NULL,

comment BLOB SUB_TYPE 1

);

ALTER TABLE how_to_contact_customer

ADD PRIMARY KEY (id_contact);

ALTER TABLE how_to_contact_customer

ADD FOREIGN KEY (id_customer)

REFERENCES customer (id_customer)

ON UPDATE CASCADE

ON DELETE CASCADE;

GRANT ALL ON how_to_contact_customer TO ACCOUNTANT;

GRANT ALL ON how_to_contact_customer TO MANAGER;

CREATE TABLE contact_person (

id_person INTEGER NOT NULL,

id_customer INTEGER NOT NULL,

last_name VARCHAR(50),

first_name VARCHAR(50),

second_name VARCHAR(50),

place VARCHAR(100),

comment BLOB SUB_TYPE 1

);

ALTER TABLE contact_person

ADD PRIMARY KEY (id_person);

ALTER TABLE contact_person

ADD FOREIGN KEY (id_customer)

REFERENCES customer (id_customer)

ON UPDATE CASCADE

ON DELETE CASCADE;

GRANT ALL ON contact_person TO ACCOUNTANT;

GRANT ALL ON contact_person TO MANAGER;

CREATE TABLE how_to_contact_person (

id_contact INTEGER NOT NULL,

id_person INTEGER NOT NULL,

contact_method VARCHAR(50) NOT NULL,

contact VARCHAR(50) NOT NULL,

comment BLOB SUB_TYPE 1

);

ALTER TABLE how_to_contact_person

ADD PRIMARY KEY (id_contact);

ALTER TABLE how_to_contact_person

ADD FOREIGN KEY (id_person)

REFERENCES contact_person (id_person)

ON UPDATE CASCADE

ON DELETE CASCADE;

GRANT ALL ON how_to_contact_person TO ACCOUNTANT;

GRANT ALL ON how_to_contact_person TO MANAGER;

CREATE TABLE beer_delivery (

id_delivery INTEGER NOT NULL,

id_customer INTEGER NOT NULL,

delivery_date TIMESTAMP NOT NULL,

delivery_account_number VARCHAR(50) NOT NULL,

mtb_number VARCHAR(20) NOT NULL,

mtb_volume DECIMAL(8,2) NOT NULL,

delivery_account_volume DECIMAL(8,2) NOT NULL,

beer_type VARCHAR(20) NOT NULL,

gift DECIMAL(8,2) NOT NULL,

sale_price DECIMAL(6,2) NOT NULL,

swap_beer DECIMAL(8,2) NOT NULL,

lack_beer DECIMAL(8,2) NOT NULL,

mtd_empty_date TIMESTAMP,

ablution_period INTEGER NOT NULL,

comment BLOB SUB_TYPE 1

);

ALTER TABLE beer_delivery

ADD PRIMARY KEY (id_delivery);

ALTER TABLE beer_delivery

ADD FOREIGN KEY (id_customer)

REFERENCES customer (id_customer)

ON UPDATE CASCADE

ON DELETE CASCADE;

GRANT ALL ON beer_delivery TO ACCOUNTANT;

GRANT ALL ON beer_delivery TO MANAGER;

CREATE TABLE sale (

id_sale INTEGER NOT NULL,

id_delivery INTEGER NOT NULL,

sale_date TIMESTAMP NOT NULL,

sale_volume DECIMAL(12,2) NOT NULL,

comment BLOB SUB_TYPE 1

);

ALTER TABLE sale

ADD PRIMARY KEY (id_sale);

ALTER TABLE sale

ADD FOREIGN KEY (id_delivery)

REFERENCES beer_delivery (id_delivery)

ON UPDATE CASCADE

ON DELETE CASCADE;

GRANT ALL ON sale TO ACCOUNTANT;

GRANT ALL ON sale TO MANAGER;

CREATE TABLE payment (

id_payment INTEGER NOT NULL,

id_delivery INTEGER NOT NULL,

pay_date TIMESTAMP NOT NULL,

pay_amount DECIMAL(12,2) NOT NULL,

comment BLOB SUB_TYPE 1

);

ALTER TABLE payment

ADD PRIMARY KEY (id_payment);

ALTER TABLE payment

ADD FOREIGN KEY (id_delivery)

REFERENCES beer_delivery (id_delivery)

ON UPDATE CASCADE

ON DELETE CASCADE;

GRANT ALL ON payment TO ACCOUNTANT;

GRANT SELECT, REFERENCES ON payment TO MANAGER;

CREATE TABLE ablution (

id_ablution INTEGER NOT NULL,

id_delivery INTEGER NOT NULL,

ablution_date TIMESTAMP NOT NULL,

ablution_volume DECIMAL(6, 2) NOT NULL,

comment BLOB SUB_TYPE 1

);

ALTER TABLE ablution

ADD PRIMARY KEY (id_ablution);

ALTER TABLE ablution

ADD FOREIGN KEY (id_delivery)

REFERENCES beer_delivery (id_delivery)

ON UPDATE CASCADE

ON DELETE CASCADE;

GRANT ALL ON ablution TO ACCOUNTANT;

GRANT ALL ON ablution TO MANAGER;

CREATE PROCEDURE SUM_ABLUTION (ID INTEGER)

RETURNS (SUMMA DECIMAL(8, 2))

AS

DECLARE VARIABLE SUM_TEMP DECIMAL(8, 2);

BEGIN

SELECT SUM(ablution_volume)

FROM ablution a

WHERE a.id_delivery =:ID

INTO:SUM_TEMP;

IF(SUM_TEMP IS NULL) THEN SUM_TEMP = 0;

SUMMA = SUM_TEMP;

SUSPEND;

END;

GRANT EXECUTE ON PROCEDURE SUM_ABLUTION TO ACCOUNTANT, MANAGER;

ALTER TABLE beer_delivery

ADD total_ablution DECIMAL(8,2) COMPUTED BY

((SELECT SUMMA

FROM SUM_ABLUTION(beer_delivery.id_delivery)));

ALTER TABLE beer_delivery

ADD total_price DECIMAL(8,2) COMPUTED BY

(((delivery_account_volume - gift - total_ablution) * sale_price));

CREATE TABLE equipment_delivery (

id_delivery INTEGER NOT NULL,

id_customer INTEGER NOT NULL,

delivery_date TIMESTAMP NOT NULL,

delivery_account_number VARCHAR(50) NOT NULL,

co2_quantity INTEGER NOT NULL,

co2_sale_price DECIMAL(6,2) NOT NULL,

glass_05_quantity INTEGER NOT NULL,

glass_05_sale_price DECIMAL(6,2) NOT NULL,

glass_033_quantity INTEGER NOT NULL,

glass_033_sale_price DECIMAL(6,2) NOT NULL,

birdekel_quantity INTEGER NOT NULL,

birdekel_sale_price DECIMAL(6,2) NOT NULL,

serviette_quantity INTEGER NOT NULL,

serviette_sale_price DECIMAL(6,2) NOT NULL,

flag_quantity INTEGER NOT NULL,

flag_sale_price DECIMAL(6,2) NOT NULL,

total_price DECIMAL(8,2) COMPUTED BY

(co2_quantity * co2_sale_price +

glass_05_quantity * glass_05_sale_price +

glass_033_quantity * glass_033_sale_price +

birdekel_quantity * birdekel_sale_price +

serviette_quantity * serviette_sale_price +

flag_quantity * flag_sale_price),

comment BLOB SUB_TYPE 1

);

ALTER TABLE equipment_delivery

ADD PRIMARY KEY (id_delivery);

ALTER TABLE equipment_delivery

ADD FOREIGN KEY (id_customer)

REFERENCES customer (id_customer)

ON UPDATE CASCADE

ON DELETE CASCADE;

GRANT ALL ON equipment_delivery TO ACCOUNTANT;

GRANT ALL ON equipment_delivery TO MANAGER;

CREATE TABLE equipment_delivery_full (

id_delivery INTEGER NOT NULL,

id_customer INTEGER NOT NULL,

delivery_date TIMESTAMP NOT NULL,

delivery_account_number VARCHAR(50) NOT NULL,

comment BLOB SUB_TYPE 1

);

ALTER TABLE equipment_delivery_full

ADD PRIMARY KEY (id_delivery);

ALTER TABLE equipment_delivery_full

ADD FOREIGN KEY (id_customer)

REFERENCES customer (id_customer)

ON UPDATE CASCADE

ON DELETE CASCADE;

GRANT ALL ON equipment_delivery_full TO ACCOUNTANT;

GRANT ALL ON equipment_delivery_full TO MANAGER;

CREATE TABLE accessories (

id_accessories INTEGER NOT NULL,

id_delivery INTEGER NOT NULL,

accessories_name VARCHAR(100) NOT NULL,

accessories_quantity DECIMAL(8,2) NOT NULL,

accessories_price DECIMAL(8,2) NOT NULL,

full_price DECIMAL(9,2) COMPUTED BY

(accessories_quantity * accessories_price),

comment BLOB SUB_TYPE 1

);

ALTER TABLE accessories

ADD PRIMARY KEY (id_accessories);

ALTER TABLE accessories

ADD FOREIGN KEY (id_delivery)

REFERENCES equipment_delivery_full (id_delivery)

ON UPDATE CASCADE

ON DELETE CASCADE;

GRANT ALL ON accessories TO ACCOUNTANT;

GRANT ALL ON accessories TO MANAGER;

CREATE PROCEDURE EQUIPMENT_DELIV_PRICE (ID INTEGER)

RETURNS (PRICE DECIMAL(12, 2))

AS

DECLARE VARIABLE PRICE_TEMP DECIMAL(12, 2);

BEGIN

SELECT SUM(full_price)

FROM accessories a

WHERE a.id_delivery =:ID

INTO:PRICE_TEMP;

IF(PRICE_TEMP IS NULL) THEN PRICE_TEMP = 0;

PRICE = PRICE_TEMP;

SUSPEND;

END;

GRANT EXECUTE ON PROCEDURE EQUIPMENT_DELIV_PRICE TO ACCOUNTANT, MANAGER;

CREATE PROCEDURE SALE_VOLUME (ID INTEGER)

RETURNS (VOL DECIMAL(12, 2))

AS

DECLARE VARIABLE VOL_TEMP DECIMAL(12, 2);

BEGIN

SELECT MAX(sale_volume)

FROM sale a

WHERE a.id_delivery =:ID

INTO:VOL_TEMP;

IF(VOL_TEMP IS NULL) THEN VOL_TEMP = 0;

VOL = VOL_TEMP;

SUSPEND;

END;

GRANT EXECUTE ON PROCEDURE SALE_VOLUME TO ACCOUNTANT, MANAGER;

ALTER TABLE beer_delivery

ADD last_sale_volume DECIMAL(12,2) COMPUTED BY

((SELECT VOL

FROM SALE_VOLUME(beer_delivery.id_delivery)));

CREATE PROCEDURE SUM_PAY (ID INTEGER)

RETURNS (SUMMA DECIMAL(12, 2))

AS

DECLARE VARIABLE SUM_TEMP DECIMAL(12, 2);

BEGIN

SELECT SUM(pay_amount)

FROM payment a

WHERE a.id_delivery =:ID

INTO:SUM_TEMP;

IF(SUM_TEMP IS NULL) THEN SUM_TEMP = 0;

SUMMA = SUM_TEMP;

SUSPEND;

END;

GRANT EXECUTE ON PROCEDURE SUM_PAY TO ACCOUNTANT, MANAGER;

ALTER TABLE beer_delivery

ADD pay_amount DECIMAL(12,2) COMPUTED BY

((SELECT SUMMA

FROM SUM_PAY(beer_delivery.id_delivery)));

ALTER TABLE beer_delivery

ADD debt DECIMAL(12,2) COMPUTED BY

(total_price - pay_amount);

CREATE PROCEDURE IS_ACTIVE (ID INTEGER)

RETURNS (ACTIVE_DELIV SMALLINT)

AS

DECLARE VARIABLE DATE_TEMP TIMESTAMP;

BEGIN

SELECT mtd_empty_date

FROM beer_delivery a

WHERE a.id_delivery =:ID

INTO:DATE_TEMP;

ACTIVE_DELIV = 0;

IF(DATE_TEMP IS NULL) THEN ACTIVE_DELIV = 1;

SUSPEND;

END;

GRANT EXECUTE ON PROCEDURE IS_ACTIVE TO ACCOUNTANT, MANAGER;

ALTER TABLE beer_delivery

ADD active_delivery SMALLINT COMPUTED BY

((SELECT ACTIVE_DELIV

FROM IS_ACTIVE(beer_delivery.id_delivery)));

CREATE PROCEDURE IS_EXPIRED (ID INTEGER, PERIOD INTEGER)

RETURNS (PAY_EXP SMALLINT)

AS

DECLARE VARIABLE DATE_TEMP TIMESTAMP;

DECLARE VARIABLE LAST_PAY_DATE_TEMP TIMESTAMP;

DECLARE VARIABLE DEBT_TEMP DECIMAL(12,2);

BEGIN

SELECT delivery_date, debt

FROM beer_delivery a

WHERE a.id_delivery =:ID

INTO:DATE_TEMP,:DEBT_TEMP;

SELECT MAX(pay_date)

FROM payment a

WHERE a.id_delivery =:ID

INTO:LAST_PAY_DATE_TEMP;

PAY_EXP = 0;

IF((((current_timestamp - DATE_TEMP) > PERIOD) AND (DEBT_TEMP > 0))

OR

((LAST_PAY_DATE_TEMP - DATE_TEMP) > PERIOD)) THEN PAY_EXP = 1;

SUSPEND;

END;

GRANT EXECUTE ON PROCEDURE IS_EXPIRED TO ACCOUNTANT, MANAGER;

CREATE PROCEDURE ABLUTION_IS_EXPIRED (ID INTEGER)

RETURNS (ABLUTION_EXP SMALLINT)

AS

DECLARE VARIABLE DATE_TEMP TIMESTAMP;

DECLARE VARIABLE END_DATE_TEMP TIMESTAMP;

DECLARE VARIABLE PERIOD_TEMP INTEGER;

BEGIN

SELECT MAX(ablution_date)

FROM ablution a

WHERE a.id_delivery =:ID

INTO:DATE_TEMP;

SELECT ablution_period

FROM beer_delivery a

WHERE a.id_delivery =:ID

INTO:PERIOD_TEMP;

SELECT mtd_empty_date

FROM beer_delivery a

WHERE a.id_delivery =:ID

INTO:END_DATE_TEMP;

IF(DATE_TEMP IS NULL)

THEN

BEGIN

SELECT delivery_date

FROM beer_delivery a

WHERE a.id_delivery =:ID

INTO:DATE_TEMP;

END

ABLUTION_EXP = 0;

IF(((current_timestamp - DATE_TEMP) > PERIOD_TEMP) AND

(END_DATE_TEMP IS NULL))

THEN ABLUTION_EXP = 1;

EXIT;

END;

GRANT EXECUTE ON PROCEDURE ABLUTION_IS_EXPIRED TO ACCOUNTANT, MANAGER;

CREATE PROCEDURE SET_TIMESTAMP_NULL_BEER_DELIVERY (ID INTEGER)

AS

BEGIN

UPDATE beer_delivery a

SET mtd_empty_date = NULL

WHERE a.id_delivery =:ID;

EXIT;

END;

GRANT EXECUTE ON PROCEDURE SET_TIMESTAMP_NULL_BEER_DELIVERY TO ACCOUNTANT, MANAGER;

CREATE PROCEDURE IS_NULL_MTD_EMPTY_DATE (ID INTEGER)

RETURNS (IS_NULL INTEGER)

AS

DECLARE VARIABLE DATA_TEMP TIMESTAMP;

BEGIN

SELECT mtd_empty_date

FROM beer_delivery a

WHERE a.id_delivery =:ID

INTO:DATA_TEMP;

IS_NULL = 0;

IF(DATA_TEMP IS NULL) THEN IS_NULL = 1;

SUSPEND;

END;

GRANT EXECUTE ON PROCEDURE IS_NULL_MTD_EMPTY_DATE TO ACCOUNTANT, MANAGER;

CREATE PROCEDURE IS_NULL_DELIVERY_DATE (ID INTEGER)

RETURNS (IS_NULL INTEGER)

AS

DECLARE VARIABLE DATA_TEMP TIMESTAMP;

BEGIN

SELECT delivery_date

FROM beer_delivery a

WHERE a.id_delivery =:ID

INTO:DATA_TEMP;

IS_NULL = 0;

IF(DATA_TEMP IS NULL) THEN IS_NULL = 1;

SUSPEND;

END;

GRANT EXECUTE ON PROCEDURE IS_NULL_DELIVERY_DATE TO ACCOUNTANT, MANAGER;

CREATE PROCEDURE IS_NULL_SALE_DATE (ID INTEGER)

RETURNS (IS_NULL INTEGER)

AS

DECLARE VARIABLE DATA_TEMP TIMESTAMP;

BEGIN

SELECT sale_date

FROM sale a

WHERE a.id_sale =:ID

INTO:DATA_TEMP;

IS_NULL = 0;

IF(DATA_TEMP IS NULL) THEN IS_NULL = 1;

SUSPEND;

END;

GRANT EXECUTE ON PROCEDURE IS_NULL_SALE_DATE TO ACCOUNTANT, MANAGER;

CREATE PROCEDURE IS_NULL_PAY_DATE (ID INTEGER)

RETURNS (IS_NULL INTEGER)

AS

DECLARE VARIABLE DATA_TEMP TIMESTAMP;

BEGIN

SELECT pay_date

FROM payment a

WHERE a.id_payment =:ID

INTO:DATA_TEMP;

IS_NULL = 0;

IF(DATA_TEMP IS NULL) THEN IS_NULL = 1;

SUSPEND;

END;

GRANT EXECUTE ON PROCEDURE IS_NULL_PAY_DATE TO ACCOUNTANT, MANAGER;

CREATE PROCEDURE IS_NULL_ABLUTION_DATE (ID INTEGER)

RETURNS (IS_NULL INTEGER)

AS

DECLARE VARIABLE DATA_TEMP TIMESTAMP;

BEGIN

SELECT ablution_date

FROM ablution a

WHERE a.id_ablution =:ID

INTO:DATA_TEMP;

IS_NULL = 0;

IF(DATA_TEMP IS NULL) THEN IS_NULL = 1;

SUSPEND;

END;

GRANT EXECUTE ON PROCEDURE IS_NULL_ABLUTION_DATE TO ACCOUNTANT, MANAGER;

CREATE PROCEDURE IS_NULL_EQ_DELIVERY_DATE (ID INTEGER)

RETURNS (IS_NULL INTEGER)

AS

DECLARE VARIABLE DATA_TEMP TIMESTAMP;

BEGIN

SELECT delivery_date

FROM equipment_delivery a

WHERE a.id_delivery =:ID

INTO:DATA_TEMP;

IS_NULL = 0;

IF(DATA_TEMP IS NULL) THEN IS_NULL = 1;

SUSPEND;

END;

GRANT EXECUTE ON PROCEDURE IS_NULL_EQ_DELIVERY_DATE TO ACCOUNTANT, MANAGER;

CREATE PROCEDURE IS_NULL_EQ_DELIVERY_DATE_FULL (ID INTEGER)

RETURNS (IS_NULL INTEGER)

AS

DECLARE VARIABLE DATA_TEMP TIMESTAMP;

BEGIN

SELECT delivery_date

FROM equipment_delivery_full a

WHERE a.id_delivery =:ID

INTO:DATA_TEMP;

IS_NULL = 0;

IF(DATA_TEMP IS NULL) THEN IS_NULL = 1;

SUSPEND;

END;

GRANT EXECUTE ON PROCEDURE IS_NULL_EQ_DELIVERY_DATE_FULL TO ACCOUNTANT, MANAGER;

ALTER TABLE equipment_delivery_full

ADD equipment_price DECIMAL(12,2) COMPUTED BY

((SELECT PRICE

FROM EQUIPMENT_DELIV_PRICE(equipment_delivery_full.id_delivery)));

ALTER TABLE beer_delivery

ADD payment_expired SMALLINT COMPUTED BY

((SELECT PAY_EXP

FROM IS_EXPIRED(beer_delivery.id_delivery, 30)));

ALTER TABLE customer

ADD number_total_deliveries INTEGER COMPUTED BY

((SELECT COUNT(*)

FROM beer_delivery

WHERE beer_delivery.id_customer=customer.id_customer));

ALTER TABLE customer

ADD number_active_deliveries INTEGER COMPUTED BY

((SELECT COUNT(*)

FROM beer_delivery

WHERE (beer_delivery.id_customer=customer.id_customer

AND beer_delivery.mtd_empty_date IS NULL)));

ALTER TABLE customer

ADD total_sale_volume DECIMAL(12,2) COMPUTED BY

((SELECT SUM(last_sale_volume)

FROM beer_delivery

WHERE (beer_delivery.id_customer=customer.id_customer)));

ALTER TABLE customer

ADD total_active_sale_volume DECIMAL(12,2) COMPUTED BY

((SELECT SUM(last_sale_volume)

FROM beer_delivery

WHERE (beer_delivery.id_customer=customer.id_customer

AND beer_delivery.mtd_empty_date IS NULL)));

ALTER TABLE customer

ADD total_pay_amount DECIMAL(12,2) COMPUTED BY

((SELECT SUM(pay_amount)

FROM beer_delivery

WHERE (beer_delivery.id_customer=customer.id_customer)));

ALTER TABLE customer

ADD total_active_pay_amount DECIMAL(12,2) COMPUTED BY

((SELECT SUM(pay_amount)

FROM beer_delivery

WHERE (beer_delivery.id_customer=customer.id_customer

AND beer_delivery.mtd_empty_date IS NULL)));

ALTER TABLE customer

ADD number_expired_deliveries INTEGER COMPUTED BY

((SELECT COUNT(*)

FROM beer_delivery

WHERE (beer_delivery.id_customer=customer.id_customer

AND beer_delivery.payment_expired = 1)));

ALTER TABLE customer

ADD total_debt_amount DECIMAL(12,2) COMPUTED BY

((SELECT SUM(debt)

FROM beer_delivery

WHERE (beer_delivery.id_customer=customer.id_customer)));

ALTER TABLE customer

ADD total_active_debt_amount DECIMAL(12,2) COMPUTED BY

((SELECT SUM(debt)

FROM beer_delivery

WHERE (beer_delivery.id_customer=customer.id_customer

AND beer_delivery.mtd_empty_date IS NULL)));

ALTER TABLE customer

ADD beer_on_sale DECIMAL(8,2) COMPUTED BY

((SELECT SUM(delivery_account_volume - gift)

FROM beer_delivery

WHERE (beer_delivery.id_customer=customer.id_customer

AND beer_delivery.mtd_empty_date IS NULL)));

CREATE VIEW general_info AS

SELECT sity, company_name, total_sale_volume, total_active_sale_volume,

total_pay_amount, total_active_pay_amount, total_debt_amount,

total_active_debt_amount, beer_on_sale, number_expired_deliveries

FROM customer;

GRANT ALL ON general_info TO ACCOUNTANT;

GRANT ALL ON general_info TO MANAGER;

CREATE ASCENDING INDEX customer_sity_ind ON customer(sity);

CREATE ASCENDING INDEX customer_company_name_ind ON customer(company_name);

CREATE ASCENDING INDEX customer_sity_company_name_ind ON customer(sity, company_name);

CREATE ASCENDING INDEX how_to_contact_customer_contact_method_ind ON how_to_contact_customer(contact_method);

CREATE ASCENDING INDEX contact_person_last_name_ind ON contact_person(last_name);

CREATE ASCENDING INDEX contact_person_place_ind ON contact_person(place);

CREATE ASCENDING INDEX contact_person_place_last_name_ind ON contact_person(place, last_name);

CREATE ASCENDING INDEX how_to_contact_person_contact_method_ind ON how_to_contact_person(contact_method);

CREATE ASCENDING INDEX beer_delivery_delivery_date_ind ON beer_delivery(delivery_date);

CREATE ASCENDING INDEX sale_sale_date_ind ON sale(sale_date);

CREATE ASCENDING INDEX payment_pay_date_ind ON payment(pay_date);

CREATE ASCENDING INDEX equipment_delivery_delivery_date_ind ON equipment_delivery(delivery_date);

CREATE ASCENDING INDEX sale_id_delivery_sale_date_ind ON sale(id_delivery, sale_date);

CREATE ASCENDING INDEX how_to_contact_customer_customer_id_contact_method_ind ON how_to_contact_customer(id_customer, contact_method);

CREATE ASCENDING INDEX contact_person_customer_id_place_last_name_ind ON contact_person(id_customer, place, last_name);

CREATE ASCENDING INDEX how_to_contact_person_id_person_contact_method_ind ON how_to_contact_person(id_person, contact_method);

CREATE ASCENDING INDEX beer_delivery_id_customer_delivery_date_ind ON beer_delivery(id_customer, delivery_date);

CREATE ASCENDING INDEX payment_id_delivery_pay_date_ind ON payment(id_delivery, pay_date);

CREATE ASCENDING INDEX equipment_delivery_id_customer_delivery_date_ind ON equipment_delivery(id_customer, delivery_date);

CREATE ASCENDING INDEX ablution_id_delivery_ablution_date_ind ON ablution(id_delivery, ablution_date);

CREATE ASCENDING INDEX equipment_delivery_full_delivery_date_ind ON equipment_delivery_full(delivery_date);

CREATE ASCENDING INDEX equipment_delivery_full_id_customer_delivery_date_ind ON equipment_delivery_full(id_customer, delivery_date);

CREATE ASCENDING INDEX equipment_delivery_full_id_delivery_delivery_date_ind ON equipment_delivery_full(id_delivery, delivery_date);

CREATE ASCENDING INDEX accessories_accessories_name_ind ON accessories(accessories_name);

CREATE ASCENDING INDEX accessories_id_accessories_accessories_name_ind ON accessories(id_accessories, accessories_name);

CREATE ASCENDING INDEX accessories_id_delivery_accessories_name_ind ON accessories(id_delivery, accessories_name);

CREATE GENERATOR GEN_EQUIPMENT_DELIVERY_FULL_ID_DELIVERY;

SET GENERATOR GEN_EQUIPMENT_DELIVERY_FULL_ID_DELIVERY TO 10;

CREATE PROCEDURE GET_ID_DELIVERY_EQUIPMENT_DELIVERY_FULL

RETURNS (ID INTEGER)

AS

BEGIN

ID = GEN_ID(GEN_EQUIPMENT_DELIVERY_FULL_ID_DELIVERY, 1);

END;

GRANT EXECUTE ON PROCEDURE GET_ID_DELIVERY_EQUIPMENT_DELIVERY_FULL TO ACCOUNTANT, MANAGER;

CREATE GENERATOR GEN_ACCESSORIES_ID_ACCESSORIES;

SET GENERATOR GEN_ACCESSORIES_ID_ACCESSORIES TO 10;

CREATE PROCEDURE GET_ID_ACCESSORIES_ACCESSORIES

RETURNS (ID INTEGER)

AS

BEGIN

ID = GEN_ID(GEN_ACCESSORIES_ID_ACCESSORIES, 1);

END;

GRANT EXECUTE ON PROCEDURE GET_ID_ACCESSORIES_ACCESSORIES TO ACCOUNTANT, MANAGER;

CREATE GENERATOR GEN_CUSTOMER_ID_CUSTOMER;

SET GENERATOR GEN_CUSTOMER_ID_CUSTOMER TO 10;

CREATE PROCEDURE GET_ID_CUSTOMER

RETURNS (ID INTEGER)

AS

BEGIN

ID = GEN_ID(GEN_CUSTOMER_ID_CUSTOMER, 1);

END;

GRANT EXECUTE ON PROCEDURE GET_ID_CUSTOMER TO ACCOUNTANT, MANAGER;

CREATE GENERATOR GEN_BEER_DELIVERY_ID_DELIVERY;

SET GENERATOR GEN_BEER_DELIVERY_ID_DELIVERY TO 10;

CREATE PROCEDURE GET_ID_BEER_DELIVERY

RETURNS (ID INTEGER)

AS

BEGIN

ID = GEN_ID(GEN_BEER_DELIVERY_ID_DELIVERY, 1);

END;

GRANT EXECUTE ON PROCEDURE GET_ID_BEER_DELIVERY TO ACCOUNTANT, MANAGER;

CREATE GENERATOR GEN_CONTACT_PERSON_ID_PERSON;

SET GENERATOR GEN_CONTACT_PERSON_ID_PERSON TO 10;

CREATE PROCEDURE GET_ID_PERSON

RETURNS (ID INTEGER)

AS

BEGIN

ID = GEN_ID(GEN_CONTACT_PERSON_ID_PERSON, 1);

END;

GRANT EXECUTE ON PROCEDURE GET_ID_PERSON TO ACCOUNTANT, MANAGER;

CREATE GENERATOR GEN_EQUIPMENT_DELIVERY_ID_DELIVERY;

SET GENERATOR GEN_EQUIPMENT_DELIVERY_ID_DELIVERY TO 10;

CREATE PROCEDURE GET_ID_EQUIPMENT_DELIVERY

RETURNS (ID INTEGER)

AS

BEGIN

ID = GEN_ID(GEN_EQUIPMENT_DELIVERY_ID_DELIVERY, 1);

END;

GRANT EXECUTE ON PROCEDURE GET_ID_EQUIPMENT_DELIVERY TO ACCOUNTANT, MANAGER;

CREATE GENERATOR GEN_HOW_TO_CONTACT_CUSTOMER_ID_CONTACT;

SET GENERATOR GEN_HOW_TO_CONTACT_CUSTOMER_ID_CONTACT TO 10;

CREATE PROCEDURE GET_ID_CUSTOMER_CONTACT

RETURNS (ID INTEGER)

AS

BEGIN

ID = GEN_ID(GEN_HOW_TO_CONTACT_CUSTOMER_ID_CONTACT, 1);

END;

GRANT EXECUTE ON PROCEDURE GET_ID_CUSTOMER_CONTACT TO ACCOUNTANT, MANAGER;

CREATE GENERATOR GEN_HOW_TO_CONTACT_PERSON_ID_CONTACT;

SET GENERATOR GEN_HOW_TO_CONTACT_PERSON_ID_CONTACT TO 10;

CREATE PROCEDURE GET_ID_PERSON_CONTACT

RETURNS (ID INTEGER)

AS

BEGIN

ID = GEN_ID(GEN_HOW_TO_CONTACT_PERSON_ID_CONTACT, 1);

END;

GRANT EXECUTE ON PROCEDURE GET_ID_PERSON_CONTACT TO ACCOUNTANT, MANAGER;

CREATE GENERATOR GEN_PAYMENT_ID_PAYMENT;

SET GENERATOR GEN_PAYMENT_ID_PAYMENT TO 10;

CREATE PROCEDURE GET_ID_PAYMENT

RETURNS (ID INTEGER)

AS

BEGIN

ID = GEN_ID(GEN_PAYMENT_ID_PAYMENT, 1);

END;

GRANT EXECUTE ON PROCEDURE GET_ID_PAYMENT TO ACCOUNTANT, MANAGER;

CREATE GENERATOR GEN_SALE_ID_SALE;

SET GENERATOR GEN_SALE_ID_SALE TO 10;

CREATE PROCEDURE GET_ID_SALE

RETURNS (ID INTEGER)

AS

BEGIN

ID = GEN_ID(GEN_SALE_ID_SALE, 1);

END;

GRANT EXECUTE ON PROCEDURE GET_ID_SALE TO ACCOUNTANT, MANAGER;

CREATE GENERATOR GEN_ABLUTION_ID_ABLUTION;

SET GENERATOR GEN_ABLUTION_ID_ABLUTION TO 10;

CREATE PROCEDURE GET_ID_ABLUTION

RETURNS (ID INTEGER)

AS

BEGIN

ID = GEN_ID(GEN_ABLUTION_ID_ABLUTION, 1);

END;

GRANT EXECUTE ON PROCEDURE GET_ID_ABLUTION TO ACCOUNTANT, MANAGER;

Манипуляция данными

На этом уроке вы ознакомитесь с той частью SQL, которая называется языком манипуляций данными (Data Manipulation Language – DML). DML является частью SQL, используемой при изменении данных и таблиц в реляционных базах данных

Основными на этом уроке будут следующие темы.

• Обзор языка манипуляций данными
• Инструкции по манипуляции данными в таблицах
• Концепции, лежащие в основе размещения данных в таблицах
• Удаление данных из таблиц
• Изменение данных в таблицах