Материал: tbd56

ФЕДЕРАЛЬНОЕ АГЕНТСТВО СВЯЗИ

Ордена Трудового Красного Знамени

Федеральное государственное бюджетное образовательное учреждение высшего образования

МОСКОВСКИЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ СВЯЗИ И ИНФОРМАТИКИ

ФАКУЛЬТЕТ ИНФОРМАЦИОННЫХ ТЕХНОЛОГИЙ

ЛАБОРАТОРНАЯ РАБОТА №5-6

по дисциплине «ТЕХНОЛОГИИ БАЗ ДАНЫХ»

Кафедра ИСУиА

Выполнил:

МОСКВА, 2018 г.

Цель работы:

Знакомство с PL/SQL, разработка процедур.

Задачи:

Освоить создание и запуск процедур с помощью PL/SQL.

Ход работы:

Модифицируем процедуру CustomerInsert, данную в работе, в соответствии с требованиями из задания. Текст процедуры приведен ниже:

CREATE OR REPLACE PROCEDURE CustomerInsert(

newname IN CHAR, newstreet IN CHAR, newcity IN CHAR, newstate IN CHAR,

newzip IN CHAR, newareacode IN CHAR, newphone IN CHAR, artistnationality IN CHAR

) AS rowcount INT(4);

CURSOR artistcursor IS

SELECT ArtistID FROM ARTIST WHERE Nationality = artistnationality;

BEGIN

SELECT COUNT(*) INTO rowcount FROM CUSTOMER WHERE

Name = newname AND Street = newstreet AND City = newcity AND

State = newstate AND Zip = newzip AND Area_Code = newareacode AND Phone_Number = newphone;

IF rowcount > 0 THEN

BEGIN

DBMS_OUTPUT.PUT_LINE ('Client already exists in database. No actions were made');

RETURN;

END;

END IF;

INSERT INTO CUSTOMER VALUES (CustID.NextVal, newname, newstreet, newcity, newstate, newzip, newareacode, newphone);

FOR artist IN artistcursor LOOP

INSERT INTO CUSTOMER_ARTIST (CustomerID, ArtistID) VALUES (CustID.CurrVal, ARTIST.ArtistID);

END LOOP;

DBMS_OUTPUT.PUT_LINE('New client has been added');

END;

/

Добавим нового клиента с помощью этой процедуры (рисунок 1):

Рисунок 1 – Выполнение процедуры CustomerInsert

Добавим в таблицу CUSTOMER новую колонку Second_Name и модифицируем процедуру 2 в соответствии с этим. Текст процедуры представлен ниже:

CREATE OR REPLACE PROCEDURE NewCustomerWithTransaction(

newname IN char, newsecondname IN char, newareacode IN char, newphone IN char,

artistname IN char, worktitle IN char, workcopy IN char, price IN number

) AS

rowcount integer(2);

tid int;

aid int;

CURSOR transcursor IS

SELECT TransactionID, ARTIST.ArtistID FROM ARTIST, WORK, TRANSACTION WHERE Name=artistname AND Title = worktitle AND Copy = workcopy AND TRANSACTION.CustomerID IS NULL AND ARTIST.ArtistID = WORK.ArtistID AND WORK.WorkID = TRANSACTION.WorkID;

BEGIN

SELECT Count(*) INTO rowcount FROM CUSTOMER WHERE Name = newname AND Area_code = newareacode AND Phone_number = newphone AND Second_Name = newsecondname;

IF rowcount > 0 THEN

BEGIN

DBMS_OUTPUT.PUT_LINE('Customer already exists');

RETURN;

END;

END IF;

INSERT INTO CUSTOMER (CustomerID,Name,Area_code, Phone_number, Second_Name) VALUES (CustID.NextVal, newname, newareacode, newphone, newsecondname);

rowcount:=0;

DBMS_OUTPUT.PUT_LINE('Customer has been added');

FOR trans IN transcursor

LOOP

DBMS_OUTPUT.PUT_LINE('Got here');

tid:=trans.TransactionID;

aid:=trans.ArtistID;

rowcount:=rowcount+1;

END LOOP;

IF rowcount > 1 THEN

BEGIN

ROLLBACK;

DBMS_OUTPUT.PUT_LINE('Wrong data in ARTIST/WORK/TRANSACTION');

RETURN;

END;

END IF;

IF rowcount = 0 THEN

BEGIN

ROLLBACK;

DBMS_OUTPUT.PUT_LINE('No available rows in TRANSACTION');

RETURN;

END;

END IF;

DBMS_OUTPUT.PUT_LINE(tid);

UPDATE TRANSACTION SET CustomerID = CustID.CurrVal, Salesprice = price,

PurchaseDate = SysDate WHERE TransactionID = tid;

DBMS_OUTPUT.PUT_LINE('Client has been added to the Database');

INSERT INTO CUSTOMER_ARTIST (ArtistID, CustomerID) VALUES (aid, CustID.CurrVal);

END;

/

Вызовем данную процедуру и проверим ее выполнение (рисунки 2-4):

Р исунок 2 – Компиляция процедуры

Рисунок 3 – Проверка выполнения процедуры

Рисунок 4 – Проверка выполнения процедуры

Создадим свою процедуру WorkInsertion. Ее текст приведен ниже:

CREATE OR REPLACE PROCEDURE WorkInsertion(

newworkname IN char, newcopy IN char, newartistname IN char, newinptice IN char

)

AS

rowcount int(2);

artid number(38);

BEGIN

SELECT COUNT(*) INTO rowcount FROM WORK WHERE Title=newworkname AND ArtistID = (SELECT ArtistID FROM ARTIST WHERE Name = newartistname);

IF rowcount>0 THEN

BEGIN

DBMS_OUTPUT.PUT_LINE ('This work is already in database');

RETURN;

END;

END IF;

SELECT ArtistID INTO artid FROM ARTIST WHERE Name = newartistname;

INSERT INTO WORK(WorkID, Title, Copy, ArtistID) VALUES (WorkID.NextVal, newworkname, newcopy, artid);

INSERT INTO TRANSACTION(TransactionID, DateAcquired, AcquisitionPrice, WorkID) VALUES (TransID.NextVal, SysDate, newinptice, WorkID.CurrVal);

END;

/

Данная процедура добавляет новую картину художника и создает свободную транзакцию на ее покупку. Скомпилируем ее и проверим работу (рисунки 5-6).

Рисунок 5 – Компиляция и выполнение процедуры

Рисунок 6 – Результат выполнения процедуры