Подякувати Студентському архіву довільною сумою
Admin
26.02.2023 12:38
Дякуємо, що користуєтесь нашим архівом!
Лабораторна робота № 4
Інформація про навчальний заклад
ВУЗ:
Інші
Інститут:
Не вказано
Факультет:
КН
Кафедра:
Кафедра СКС
Інформація про роботу
Рік:
2011
Тип роботи:
Лабораторна робота
Предмет:
Організація баз даних
Частина тексту файла (без зображень, графіків і формул):
Міністерство освіти та науки України НТУУ «КПІ»
Факультет прикладної математики
Кафедра «СКС»
Лабораторна робота № 4
з дисципліни «Організація баз даних»
Виконали:
Курс: 3
Група: КВ-93
Стельмах Георгій Миколайович
Свиридюк Ігор Володимирович
Київ 2011
Постановка задачі
Вивчити відповідні розділи документації за нижченаведеними
пунктами. Привести власні приклади використання даних конструкцій. Звіт
підготувати у вигляді файлу із прикладами.
1. Структура блоку PL/SQL. Опис процедур і функцій. Використання IN,
OUT. Пакети. Вбудовані пакети.
2. Оголошення DEFAULT, %TYPE, %ROWTYPE.
3. Логічні оператори. Обробка порожніх (NULL) значень.
4. Приклади вбудованих функцій.
5. Користувальницькі записи.
6. Умовні ітеративні конструкції.
7. Курсори. Курсори FOR.
8. Колекції в PL/SQL (асоціативні масиви (Associative arrays, index-by tables),
вкладені таблиці (Nested tables), VARRAY)
9. Обробка виняткових ситуацій.
Варіант №4:
Задано таблицю T1(F1 number, F2 varchar2(20)). Написати функцію, яка б :
1) переносила в таблицю Т2 дані з Т1, де F2 порожнє;
2) замінювала б у Т2 порожні значення на текст: ‘порожнє’. Решту значень перенести до колекції та підрахувати їхню кількість.
Початкове створення та заповнення:
1) Структура блоку PL/SQL. Опис процедур і функцій. Використання IN, OUT. Пакети. Вбудовані пакети.
1.1) Структура блоку PL/SQL
DECLARE
/* Declarative section: variables, types, and local subprograms. */
BEGIN
/* Executable section: procedural and SQL statements go here. */
/* This is the only section of the block that is required. */
EXCEPTION
/* Exception handling section: error handling statements go here. */
END;
1.2) Опис процедур і функцій
CREATE TABLE FOOTBALLERS (NAME varchar(20),
AGE INTEGER, CLUB varchar(20));
BEGIN
INSERT INTO FOOTBALLERS values ('Giggs', 35, 'Manchester United');
INSERT INTO FOOTBALLERS values ('Torres', 24, 'Liverpool');
INSERT INTO FOOTBALLERS values ('Tevez', 29, 'Manchester City');
INSERT INTO FOOTBALLERS values ('Fabregas', 23, 'Arsenal');
INSERT INTO FOOTBALLERS values ('Drogba', 30, 'Chelsea');
END;
SELECT * FROM FOOTBALLERS;
NAME AGE CLUB
-------------------- ----------------------- --------------------
Giggs 35 Manchester United
Torres 24 Liverpool
Tevez 29 Manchester City
Fabregas 23 Arsenal
Drogba 30 Chelsea
CREATE OR REPLACE PROCEDURE
ADD_FOOTBALLER (p_name varchar, p_age INTEGER, p_club varchar) AS
BEGIN
INSERT INTO FOOTBALLERS (NAME, AGE, CLUB)
VALUES (p_name, p_age, p_club);
END;
BEGIN
ADD_FOOTBALLER('Etoo', 25, 'Inter');
END;
NAME AGE CLUB
-------------------- ----------------------- --------------------
Giggs 35 Manchester United
Torres 24 Liverpool
Tevez 29 Manchester City
Fabregas 23 Arsenal
Drogba 30 Chelsea
Etoo 25 Inter
CREATE OR REPLACE FUNCTION GET_FOOTBALLER(p_age NUMBER)
RETURN VARCHAR IS VAR_FOOTBALLER VARCHAR(20);
BEGIN
SELECT NAME INTO VAR_FOOTBALLER
FROM FOOTBALLERS WHERE AGE = p_age;
RETURN VAR_FOOTBALLER;
END;
CREATE TABLE RES(name VARCHAR(20))
DECLARE
P_NAME VARCHAR(20);
BEGIN
P_NAME := GET_FOOTBALLER(35);
INSERT INTO RES VALUES(P_NAME);
END;
SELECT * FROM RES;
NAME
--------------------
Giggs
1.3) Використання IN, OUT
CREATE OR REPLACE PROCEDURE PROC(F_AGE OUT NUMBER, F_NAME IN VARCHAR, TMP IN OUT NUMBER) AS
BEGIN
SELECT AGE INTO F_AGE FROM FOOTBALLERS
WHERE NAME = F_NAME;
IF (F_AGE > 30 ) THEN
TMP := F_AGE;
END IF;
END;
CREATE TABLE RES2(VAR NUMBER);
DECLARE
P_AGE NUMBER;
P_NAME VARCHAR(20);
TMP NUMBER;
BEGIN
P_NAME := 'Giggs';
PROC(P_AGE, P_NAME, TMP);
INSERT INTO RES2 VALUES(TMP);
END;
VAR
-----------------------
35
1.4) Пакети
CREATE OR REPLACE PACKAGE FOOTBALL AS
PROCEDURE ADD_FOOTBALLER(P_NAME FOOTBALLERS.NAME%TYPE, P_AGE FOOTBALLERS.AGE%TYPE, P_CLUB FOOTBALLERS.CLUB%TYPE);
END FOOTBALL;
CREATE OR REPLACE PACKAGE BODY FOOTBALL AS
PROCEDURE ADD_FOOTBALLER(P_NAME FOOTBALLERS.NAME%TYPE, P_AGE FOOTBALLERS.AGE%TYPE, P_CLUB FOOTBALLERS.CLUB%TYPE) AS
BEGIN
INSERT INTO FOOTBALLERS VALUES (P_NAME, P_AGE, P_CLUB);
END;
END FOOTBALL;
BEGIN
FOOTBALL.ADD_FOOTBALLER('Del Piero', 37, 'Juventus');
END;
NAME AGE CLUB
-------------------- ----------------------- --------------------
Giggs 35 Manchester United
Torres 24 Liverpool
Tevez 29 Manchester City
Fabregas 23 Arsenal
Drogba 30 Chelsea
Etoo 25 Inter
Del Piero 37 Juventus
1.5) Вбудовані пакети
DBMS_ALERT - Notify a database event (asynchronous).
DBMS_DEBUG - Implement server-side debuggers and provide a way to
debug server-side PL/SQL program units.
DBMS_DESCRIBE - Describe the arguments of a stored procedure.
with full name translation and security checking.
DBMS_JOB - Schedule PL/SQL procedures that you want performed at
periodic intervals; also the job queue interface.
DBMS_LIBCACHE - Prepares the library cache on an Oracle instance by
extracting SQL and PL/SQL from a remote instance and compiling this SQL locally without execution.
DBMS_LOCK - Request, convert and release locks through Oracle Lock
Management services.
DBMS_METADATA - Retrieve complete database object definitions (metadata)
from the dictionary.
DBMS_MVIEW - Refresh snapshots that are not part of the same refresh group and purge logs. DBMS_SNAPSHOT is a synonym.
DBMS_OUTLN - Interface for procedures and functions associated with
management of stored outlines. Synonymous with OUTLN_PKG
DBMS_OUTPUT - Accumulate information in a buffer so that it can be
retrieved out later.
DBMS_RANDOM - A built-in random number generator.
Options to generate random numbers within a range or distribution.
DBMS_REFRESH - Create groups of snapshots that can be refreshed together
to a transactionally consistent point in time. Requires the Distributed Option.
DBMS_REPAIR - Repair data corruption.
DMBS_XMLQUERY - Database-to-XMLType functionality.
2) Оголошення DEFAULT, %TYPE, %ROWTYPE.
DECLARE
V_NAME FOOTBALLERS.NAME%TYPE;
K_AGE INTEGER;
V_AGE K_AGE%TYPE;
V_FOOTBALLER FOOTBALLERS%ROWTYPE;
BEGIN
V_NAME := 'Messi';
V_AGE := '24';
V_FOOTBALLER.NAME := V_NAME;
V_FOOTBALLER.AGE := V_AGE;
V_FOOTBALLER.CLUB := 'Barcelona';
FOOTBALL.ADD_FOOTBALLER(V_FOOTBALLER.NAME, V_FOOTBALLER.AGE, V_FOOTBALLER.CLUB);
END;
SELECT * FROM FOOTBALLERS;
NAME AGE CLUB
-------------------- ----------------------- --------------------
Giggs 35 Manchester United
Torres 24 Liverpool
Tevez 29 Manchester City
Fabregas 23 Arsenal
Drogba 30 Chelsea
Etoo 25 Inter
Del Piero 37 Juventus
Messi 24 Barcelona
3) Логічні оператори. Обробка порожніх (NULL) значень
DECLARE
AGE1 NUMBER;
AGE2 NUMBER;
BEGIN
SELECT AGE INTO AGE1 FROM FOOTBALLERS
WHERE NAME = 'Del Piero';
SELECT AGE INTO AGE2 FROM FOOTBALLERS
WHERE NAME = 'Messi';
IF AGE1 > AGE2 THEN
DBMS_OUTPUT.PUT_LINE('1 is bigger than 2');
ELSIF AGE1 < AGE2 THEN
DBMS_OUTPUT.PUT_LINE('2 is bigger than 1');
ELSIF AGE1 IS NULL OR AGE2 IS NULL THEN
BEGIN
IF AGE1 IS NULL THEN
DBMS_OUTPUT.PUT_LINE('1 is NULL');
END IF;
IF AGE2 IS NULL THEN
DBMS_OUTPUT.PUT_LINE('2 is NULL');
END IF;
END;
ELSE
DBMS_OUTPUT.PUT_LINE('1 is equal to 2');
END IF;
END;
Output: "1 is bigger than 2"
4) Приклади вбудованих функцій.
4.1 ) SELECT UPPER(NAME) FROM FOOTBALLERS;
UPPER(NAME)
--------------------
GIGGS
TORRES
TEVEZ
FABREGAS
DROGBA
ETOO
DEL PIERO
MESSI
4.2) SELECT LOWER(NAME) FROM FOOTBALLERS;
LOWER(NAME)
--------------------
giggs
torres
tevez
fabregas
drogba
etoo
del piero
messi
4.3) SELECT SUBSTR(NAME,1,3) FROM FOOTBALLERS;
SUBSTR(NAME,1,3)
----------------
Gig
Tor
Tev
Fab
Dro
Eto
Del
Mes
4.4) SELECT NAME FROM FOOTBALLERS
WHERE ASCII(NAME) = ASCII('D');
NAME
--------------------
Drogba
Del Piero
4.5) SELECT NAME FROM FOOTBALLERS
WHERE INSTR(NAME, 'Del') = 1;
NAME
--------------------
Del Piero
5) Користувальницькі записи
DECLARE
TYPE TFOOTBALLER IS RECORD (NAME FOOTBALLERS.NAME%TYPE, AGE FOOTBALLERS.AGE%TYPE, CLUB FOOTBALLERS.CLUB%TYPE);
T_FOOTBALLER TFOOTBALLER;
BEGIN
SELECT NAME, AGE, CLUB INTO T_FOOTBALLER FROM FOOTBALLERS WHERE NAME = 'Del Piero';
DBMS_OUTPUT.PUT_LINE(T_FOOTBALLER.NAME);
DBMS_OUTPUT.PUT_LINE(T_FOOTBALLER.AGE);
DBMS_OUTPUT.PUT_LINE(T_FOOTBALLER.CLUB);
END;
6) Умовні ітеративні конструкції
6.1) LOOP
DECLARE
COUNTER INTEGER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(COUNTER);
COUNTER := COUNTER + 1;
IF COUNTER > 5 THEN
EXIT;
END IF;
END LOOP;
END;
1
2
3
4
5
6.2) WHILE LOOP
DECLARE
COUNTER INTEGER := 1;
BEGIN
WHILE COUNTER <= 5 LOOP
DBMS_OUTPUT.PUT_LINE(COUNTER);
COUNTER := COUNTER + 1;
END LOOP;
END;
1
2
3
4
5
6.3) FOR LOOP
DECLARE
COUNTER INTEGER := 1;
BEGIN
FOR COUNTER IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE(COUNTER);
END LOOP;
END;
1
2
3
4
5
7) Курсори. Курсори FOR
CREATE TABLE CRSR_TMP (NAME varchar(20), AGE INTEGER,
CLUB varchar(20));
BEGIN
INSERT INTO CRSR_TMP values ('Del Piero', 35, 'Juventus');
INSERT INTO CRSR_TMP values ('Buffon', 33, 'Juventus');
INSERT INTO CRSR_TMP values ('Inzaghi', 38, 'Milan');
INSERT INTO CRSR_TMP values ('Cassano', 29, 'Milan');
INSERT INTO CRSR_TMP values ('De Rossi', 28, 'Roma');
INSERT INTO CRSR_TMP values ('Gilardino', 29, 'Fiorentina');
END;
DECLARE
P_NAME CRSR_TMP.NAME%TYPE;
CURSOR CRSR IS SELECT NAME FROM CRSR_TMP
WHERE CLUB = 'Juventus';
BEGIN
OPEN CRSR;
LOOP
FETCH CRSR INTO P_NAME;
EXIT WHEN CRSR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(P_NAME);
END LOOP;
CLOSE CRSR;
END;
Del Piero
Buffon
DECLARE
SUM_AGE INTEGER;
P_AGE INTEGER;
CURSOR CRSR IS SELECT AGE FROM CRSR_TMP
WHERE CLUB = 'Juventus';
BEGIN
SUM_AGE := 0;
P_AGE := 0;
FOR V_CRSR IN CRSR LOOP
SUM_AGE := SUM_AGE + V_CRSR.AGE;
DBMS_OUTPUT.PUT_LINE(V_CRSR.AGE);
END LOOP;
DBMS_OUTPUT.PUT_LINE(SUM_AGE);
END;
35
33
68
8) Колекції в PL/SQL (асоціативні масиви (Associative arrays, index-by tables),
вкладені таблиці (Nested tables), VARRAY)
8.1) Асоціативні масиви
DECLARE
TYPE TArr IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
AGES TArr;
SYM_AGE NUMBER;
BEGIN
AGES(1) := 5 ;
AGES(2) := 10 ;
AGES(3) := 15 ;
SYM_AGE := AGES(1) + AGES(2) + AGES(3);
DBMS_OUTPUT.PUT_LINE(SYM_AGE);
END;
30
DECLARE
TYPE TArr IS TABLE OF NUMBER INDEX BY VARCHAR2(100);
AGES TArr;
vINDEX VARCHAR2(100) := 'Del Piero';
BEGIN
AGES('Del Piero') := 35;
AGES('Buffon') := 33 ;
AGES('De Rossi') := 28;
DBMS_OUTPUT.PUT_LINE(AGES(vINDEX));
END;
35
8.2) Вкладені таблиці
DECLARE
TYPE TArr IS TABLE OF VARCHAR2(100);
P_LIST TArr := TArr();
BEGIN
FOR I IN 1..5 LOOP
P_LIST.EXTEND;
P_LIST(I) := 'Player '||TO_CHAR(I);
END LOOP;
FOR I IN P_LIST.FIRST..P_LIST.LAST LOOP
DBMS_OUTPUT.PUT_LINE(P_LIST(I));
END LOOP;
END;
Player 1
Player 2
Player 3
Player 4
Player 5
8.3) VARRAY
DECLARE
TYPE TArr IS VARRAY(100) OF NUMBER;
AGES TArr := TArr(35,33,29,25,27);
BEGIN
AGES.TRIM(2);
FOR I IN 1..AGES.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(AGES(I));
END LOOP;
END;
35
33
29
9) Обробка виняткових ситуацій
DECLARE
ERR EXCEPTION;
P_CLUB FOOTBALLERS.CLUB%TYPE;
BEGIN
SELECT CLUB INTO P_CLUB FROM FOOTBALLERS
WHERE NAME = 'Del Piero';
IF P_CLUB != 'Milan' THEN
RAISE ERR;
END IF;
EXCEPTION
WHEN ERR THEN
DBMS_OUTPUT.PUT_LINE('Hell YEAH!');
END;
Hell YEAH!
10) Задано таблицю T1(F1 number, F2 varchar2(20)). Написати функцію, яка б :
1) переносила в таблицю Т2 дані з Т1, де F2 порожнє;
2) замінювала б у Т2 порожні значення на текст: ‘порожнє’.
Решту значень перенести до колекції та підрахувати їхню кількість.
CREATE TABLE T1 (F1 NUMBER, F2 VARCHAR2(20));
CREATE TABLE T2 (F1 NUMBER, F2 VARCHAR2(20));
DECLARE
TYPE TArr IS TABLE OF VARCHAR2(20);
P_COLLECTION TArr := TArr();
P_COLL TArr := TArr();
FUNCTION WORK( P_COLLECTION IN OUT TArr ) RETURN TArr IS
P_F2 T1.F2%TYPE;
COUNTER INTEGER;
CURSOR CRSR IS SELECT F2 FROM T1 WHERE F2 IS NOT NULL;
BEGIN
INSERT INTO T2 SELECT * FROM T1 WHERE F2 IS NULL ;
UPDATE T2 SET F2 = 'EMPTY';
COUNTER := 1;
OPEN CRSR;
LOOP
FETCH CRSR INTO P_F2;
EXIT WHEN CRSR%NOTFOUND;
P_COLLECTION.EXTEND;
P_COLLECTION(COUNTER) := P_F2;
COUNTER := COUNTER + 1;
END LOOP;
CLOSE CRSR;
RETURN P_COLLECTION;
END;
BEGIN
INSERT INTO T1 VALUES(1,'P1');
INSERT INTO T1 VALUES(2,'');
INSERT INTO T1 VALUES(3,'P3');
INSERT INTO T1 VALUES(4,'');
INSERT INTO T1 VALUES(5,'P5');
P_COLL := WORK(P_COLLECTION);
DBMS_OUTPUT.PUT_LINE('Collection count:');
DBMS_OUTPUT.PUT_LINE(P_COLL.COUNT);
IF P_COLL.COUNT <> 0 THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('Collection content:');
FOR I IN P_COLL.FIRST..P_COLL.LAST LOOP
DBMS_OUTPUT.PUT_LINE(P_COLL(I));
END LOOP;
END;
END IF;
END;
Collection count:
3
Collection content:
P1
P3
P5
SELECT * FROM T2;
F1 F2
----------------------- --------------------
2 EMPTY
4 EMPTY
28.03.2013 01:03-
Ділись своїми роботами та отримуй миттєві бонуси!
0%
Оголошення від адміністратора