A könyv példáinak nagy része a következő adatbázissémára épül.
Tábláink egy egyszerű könyvtári nyilvántartórendszert modelleznek. Példáinkon keresztül megmutatjuk, hogyan oldhatók meg PL/SQL segítségével az ilyen rendszerben felmerülő problémák.
Az adatbázisséma a könyvtár működési szabályainak egy részét képes biztosítani. Így például nem kölcsönözhet könyvet az, aki nincs beíratkozva és nem lehet olyan könyvet kölcsönözni, amellyel a könyvtár nem rendelkezik. Lehetnek azonban olyan logikai szabályok, amelyeket a séma nem tud kezelni, ezért programozói eszközökkel kell azok teljesülését biztosítani. A mi könyvtárunkban csak néhány ilyen szabály van, amelyekre a példák során többször fogunk hivatkozni. Ezek a következők:
Minden könyv kölcsönzési ideje egységesen 30 nap.
Egy könyv kölcsönzése legfeljebb kétszer hosszabbítható, tehát egy könyv maximum 90 napig lehet valakinél.
Minden kölcsönző csak a számára megszabott mennyiségű (kvótányi) könyvet kölcsönözheti egyszerre.
A séma tulajdonosa és az általunk használt felhasználó neve PLSQL, jelszava szintén PLSQL. Ha a példákban az adatbázis-objektumokat sémanévvel minősítjük, akkor ezt fogjuk használni.
A sémát létrehozó SQL utasítások a következők:
CREATE TYPE T_Szerzok IS
VARRAY (10) OF VARCHAR2(50)
/
CREATE TABLE Konyv (
id NUMBER,
ISBN VARCHAR2(30) NOT NULL,
Cim VARCHAR2(100) NOT NULL,
Kiado VARCHAR2(100) NOT NULL,
Kiadasi_ev VARCHAR2(10) NOT NULL,
Szerzo T_Szerzok NOT NULL,
Keszlet NUMBER NOT NULL,
Szabad NUMBER NOT NULL,
CONSTRAINT konyv_pk PRIMARY KEY (id),
CONSTRAINT konyv_szabad CHECK (Szabad >= 0)
)
/
CREATE SEQUENCE konyv_seq START WITH 100 INCREMENT BY 5
/
CREATE TYPE T_Tetel IS OBJECT(
Konyv_id NUMBER,
Datum DATE
)
/
CREATE TYPE T_Konyvek IS TABLE OF T_Tetel
/
CREATE TABLE Ugyfel (
id NUMBER,
Nev VARCHAR2(100) NOT NULL,
Anyja_neve VARCHAR2(50) NOT NULL,
Lakcim VARCHAR2(100) NOT NULL,
Tel_szam VARCHAR2(20),
Foglalkozas VARCHAR2(50),
Beiratkozas DATE NOT NULL,
Max_konyv NUMBER DEFAULT 10 NOT NULL,
Konyvek T_Konyvek DEFAULT T_Konyvek(),
CONSTRAINT ugyfel_pk PRIMARY KEY (id)
) NESTED TABLE Konyvek STORE AS ugyfel_konyvek
/
CREATE SEQUENCE ugyfel_seq START WITH 100 INCREMENT BY 5
/
CREATE TABLE Kolcsonzes (
Kolcsonzo NUMBER NOT NULL,
Konyv NUMBER NOT NULL,
Datum DATE NOT NULL,
Hosszabbitva NUMBER DEFAULT 0 NOT NULL,
Megjegyzes VARCHAR2(200),
CONSTRAINT kolcsonzes_fk1 FOREIGN KEY (Kolcsonzo)
REFERENCES Ugyfel(Id),
CONSTRAINT kolcsonzes_fk2 FOREIGN KEY (Konyv)
REFERENCES Konyv(Id)
)
/
CREATE TABLE Kolcsonzes_naplo (
Konyv_isbn VARCHAR2(30) NOT NULL,
Konyv_cim VARCHAR2(100) NOT NULL,
Ugyfel_nev VARCHAR2(100) NOT NULL,
Ugyfel_anyjaneve VARCHAR2(50) NOT NULL,
Elvitte DATE NOT NULL,
Visszahozta DATE NOT NULL,
Megjegyzes VARCHAR2(200)
)
/
A sémát példaadatokkal feltöltő SQL utasítások:
/*
Konyv:
id NUMBER PRIMARY KEY,
ISBN VARCHAR2(30) NOT NULL,
Cim VARCHAR2(100) NOT NULL,
Kiado VARCHAR2(100) NOT NULL,
Kiadasi_ev VARCHAR2(10) NOT NULL,
Szerzo T_Szerzok NOT NULL,
Keszlet NUMBER NOT NULL,
Szabad NUMBER NOT NULL
*/
/* Az SQL*Plus escape karakterét \(backslash)-re állítjuk,
mert & karakter is van a sztringekben.
Ezt vegye figyelembe, ha nem SQL*Plus-t használ! */
SET ESCAPE \
INSERT INTO konyv VALUES (
5, 'ISBN 963 19 0297 8',
'A római jog története és institúciói',
'Nemzeti Tankönyvkiadó Rt.', 1996,
T_Szerzok('dr. Földi András', 'dr. Hamza Gábor'),
20, 19
)
/
INSERT INTO konyv VALUES (
10, 'ISBN 963 8453 09 5',
'A teljesség felé',
'Tericum Kiadó', 1995,
T_Szerzok('Weöres Sándor'),
5, 4
)
/
INSERT INTO konyv VALUES (
15, 'ISBN 963 9077 39 9',
'Piszkos Fred és a többiek',
'Könyvkuckó Kiadó', 2000,
T_Szerzok('P. Howard', 'Rejtő Jenő'),
5, 4
)
/
INSERT INTO konyv VALUES (
20, 'ISBN 3-540-42206-4',
'ECOOP 2001 - Object-Oriented Programming',
'Springer-Verlag', 2001,
T_Szerzok('Jorgen Lindskov Knudsen (Ed.)', 'Gerthard Goos', 'Juris
Hartmanis','Jan van Leeuwen'),
3, 2
)
/
INSERT INTO konyv VALUES (
25, 'ISBN 963 03 9005 1',
'Java - start!',
'Logos 2000 Bt.', 1999,
T_Szerzok('Vég Csaba', 'dr. Juhász István'),
10, 9
)
/
INSERT INTO konyv VALUES (
30, 'ISBN 1-55860-456-1',
'SQL:1999 Understanding Relational Language Components',
'Morgan Kaufmann Publishers', 2002,
T_Szerzok('Jim Melton', 'Alan R. Simon'),
3, 1
)
/
INSERT INTO konyv VALUES (
35, 'ISBN 0 521 27717 5',
'A critical introduction to twentieth-century American drama - Volume 2',
'Cambridge University Press', 1984,
T_Szerzok('C. W. E: Bigsby'),
2, 0
)
/
INSERT INTO konyv VALUES (
40, 'ISBN 0-393-95383-1',
'The Norton Anthology of American Literature - Second Edition - Volume 2',
'W. W. Norton \& Company, Inc.', 1985,
T_Szerzok('Nina Baym', 'Ronald Gottesman', 'Laurence B. Holland',
'Francis Murphy', 'Hershel Parker', 'William H. Pritchard',
'David Kalstone'),
2, 1
)
/
INSERT INTO konyv VALUES (
45, 'ISBN 963 05 6328 2',
'Matematikai zseblexikon',
'TypoTeX Kiadó', 1992,
T_Szerzok('Denkinger Géza', 'Scharnitzky Viktor', 'Takács Gábor',
'Takács Miklós'),
5, 3
)
/
INSERT INTO konyv VALUES (
50, 'ISBN 963-9132-59-4',
'Matematikai Kézikönyv',
'TypoTeX Kiadó', 2000,
T_Szerzok('I. N. Bronstejn', 'K. A. Szemangyajev', 'G. Musiol', 'H. Mühlig'),
5, 3
)
/
/*
Ugyfel:
id NUMBER PRIMARY KEY,
Nev VARCHAR2(100) NOT NULL,
Anyja_neve VARCHAR2(50) NOT NULL,
Lakcim VARCHAR2(100) NOT NULL,
Tel_szam VARCHAR2(20),
Foglalkozas VARCHAR2(50),
Beiratkozas DATE NOT NULL,
Max_konyv NUMBER DEFAULT 10 NOT NULL,
Konyvek T_Konyvek DEFAULT T_Konyvek()
A nevek és a címek kitalált adatok, így az irányítószámok,
városok, utcanevek a valóságot nem tükrözik, ám a célnak
tökéletesen megfelelnek.
*/
INSERT INTO ugyfel VALUES (
5, 'Kovács János', 'Szilágyi Anna',
'4242 Hajdúhadház, Jókai u. 3.', '06-52-123456',
'Középiskolai tanár', '00-MÁJ. -24', 10,
T_Konyvek()
)
/
INSERT INTO ugyfel VALUES (
10, 'Szabó Máté István', 'Szegedi Zsófia',
'1234 Budapest, IX. Kossuth u. 51/b.', '06-1-1111222',
NULL, '01-MÁJ. -23', 10,
T_Konyvek(T_Tetel(30, '02-ÁPR. -21'),
T_Tetel(45, '02-ÁPR. -21'),
T_Tetel(50, '02-ÁPR. -21'))
)
/
INSERT INTO ugyfel VALUES (
15, 'József István', 'Ábrók Katalin',
'4026 Debrecen, Bethlen u. 33. X./30.', '06-52-456654',
'Programozó', '01-SZEPT-11', 5,
T_Konyvek(T_Tetel(15, '02-JAN. -22'),
T_Tetel(20, '02-JAN. -22'),
T_Tetel(25, '02-ÁPR. -10'),
T_Tetel(45, '02-ÁPR. -10'),
T_Tetel(50, '02-ÁPR. -10'))
)
/
INSERT INTO ugyfel VALUES (
20, 'Tóth László', 'Nagy Mária',
'1122 Vác, Petőfi u. 15.', '06-42-154781',
'Informatikus', '1996-ÁPR. -01', 5,
T_Konyvek(T_Tetel(30, '02-FEBR. -24'))
)
/
INSERT INTO ugyfel VALUES (
25, 'Erdei Anita', 'Cserepes Erzsébet',
'2121 Hatvan, Széchenyi u. 4.', '06-12-447878',
'Angol tanár', '1997-DEC. -05', 5,
T_Konyvek(T_Tetel(35, '02-ÁPR. -15'))
)
/
INSERT INTO ugyfel VALUES (
30, 'Komor Ágnes', 'Tóth Eszter',
'1327 Budapest V., Kossuth tér 8.', NULL,
'Egyetemi hallgató', '00-JÚN. -11', 5,
T_Konyvek(T_Tetel(5, '02-ÁPR. -12'),
T_Tetel(10, '02-MÁRC. -12'))
)
/
INSERT INTO ugyfel VALUES (
35, 'Jaripekka Hämälainen', 'Pirkko Lehtovaara',
'00500 Helsinki, Lintulahdenaukio 6. as 15.', '+358-9-1234567',
NULL, '01-AUG. -24', 5,
T_Konyvek(T_Tetel(35, '02-MÁRC. -18'),
T_Tetel(40, '02-MÁRC. -18'))
)
/
/*
Konzisztenssé tesszük az adatbázist a megfelelő kolcsonzes
bejegyzésekkel.
*/
/* Kölcsönző: Szabó Máté István */
INSERT INTO kolcsonzes VALUES (
10, 30, '02-ÁPR. -21', 0, NULL
)
/
INSERT INTO kolcsonzes VALUES (
10, 45, '02-ÁPR. -21', 0, NULL
)
/
INSERT INTO kolcsonzes VALUES (
10, 50, '02-ÁPR. -21', 0, NULL
)
/
/* Kölcsönző: József István */
INSERT INTO kolcsonzes VALUES (15, 15, '02-JAN. -22', 2, NULL);
INSERT INTO kolcsonzes VALUES (15, 20, '02-JAN. -22', 2, NULL);
INSERT INTO kolcsonzes VALUES (15, 25, '02-ÁPR. -10', 0, NULL);
INSERT INTO kolcsonzes VALUES (15, 45, '02-ÁPR. -10', 0, NULL);
INSERT INTO kolcsonzes VALUES (15, 50, '02-ÁPR. -10', 0, NULL);
/* Kölcsönző: Tóth László */
INSERT INTO kolcsonzes VALUES (20, 30, '02-FEBR. -24', 2, NULL);
/* Kölcsönző: Erdei Anita */
INSERT INTO kolcsonzes VALUES (25, 35, '02-ÁPR. -15', 0, NULL);
/* Kölcsönző: Komor Ágnes */
INSERT INTO kolcsonzes VALUES (30, 5, '02-ÁPR. -12', 0, NULL);
INSERT INTO kolcsonzes VALUES (30, 10, '02-MÁRC. -12', 1, NULL);
/* Kölcsönző: Jaripekka Hämälainen */
INSERT INTO kolcsonzes VALUES (35, 35, '02-MÁRC. -18', 0, NULL);
INSERT INTO kolcsonzes VALUES (35, 40, '02-MÁRC. -18', 0, NULL);
/
Mivel az azonosítókkal megadott kölcsönzési rekordok és a beágyazott táblák is nehezen olvashatók, létrehozhatunk egy nézetet, amely átláthatóbbá teszi adatainkat:
/* A kölcsönzések ügyfél-könyv párjai. */
CREATE VIEW ugyfel_konyv AS
SELECT u.id AS ugyfel_id, u.nev AS Ugyfel,
k.id AS konyv_id, k.cim AS Konyv
FROM ugyfel u, konyv k
WHERE k.id IN (SELECT konyv_id FROM TABLE(u.konyvek))
ORDER BY UPPER(u.nev), UPPER(k.cim)
/
Ennek tartalma a következő:
SQL> SELECT * FROM ugyfel_konyv;
UGYFEL_ID UGYFEL
---------- ----------------------------------------------------------------
KONYV_ID KONYV
---------- ----------------------------------------------------------------
25 Erdei Anita
35 A critical introduction to twentieth-century American drama - Volume 2
35 Jaripekka Hämälainen
35 A critical introduction to twentieth-century American drama - Volume 2
35 Jaripekka Hämälainen
40 The Norton Anthology of American Literature - Second Edition - Volume 2
15 József István
20 ECOOP 2001 - Object-Oriented Programming
15 József István
25 Java - start!
15 József István
50 Matematikai Kézikönyv
15 József István
45 Matematikai zseblexikon
15 József István
15 Piszkos Fred és a többiek
30 Komor Ágnes
5 A római jog története és institúciói
30 Komor Ágnes
10 A teljesség felé
10 Szabó Máté
50 Matematikai Kézikönyv
10 Szabó Máté
45 Matematikai zseblexikon
10 Szabó Máté
30 SQL:1999 Understanding Relational Language Components
20 Tóth László
30 SQL:1999 Understanding Relational Language Components
14 sor kijelölve.
A példák futtatási környezete
A könyv példáit egy 32 bites processzoron futó Linux operációs rendszeren telepített Oracle 10g Release 2 adatbázis-kezelőben futtattuk.
Az Oracle tulajdonságai:
Verzió: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0.
Adatbázis karakterészlete: AL32UTF8. Ez egy ASCII alapú Unicode karakterkészlet, amely több-bájtos karaktereket is tartalmaz.
NLS_LANG környezeti változó értéke: Hungarian_Hungary.EE8ISO8859P2.
A példákat SQL*Plusban futtattuk. Fontos, hogy az SQL*Plus a példák által kiírt sorokat csak akkor jeleníti meg, ha a SERVEROUTPUT engedélyezve van. A mi futtató környezetünkben a következő parancs állította be ennek az értékét:
SET SERVEROUTPUT ON SIZE 10000 FORMAT WRAPPED
Fontos, hogy WRAPPED formátumot használjunk, mert más formátumoknál az SQL*Plus átformázza a szerver pufferét.