Relációs műveletek
=
>
<
>=
<=
<> (vagy
!=) - nem egyenlő
Fontosabb szimbólumok
( ) -
lista,
:= -
értékadás,
|| -
konkatenáció,
-- -
megjegyzés,
/* */
- megjegyzés.
Értékadás
<Változó> :=
<kifejezés>;
Példa.
String_1 := ‘Hello’ || ’World’ || ’!’;
10.1 Vezérlési struktúrák
10.1.1 IF parancs
IF logikai_kifejezés
THEN
parancsok;
[ELSEIF
logikai_kifejezés THEN
parancsok; ]
…….
[ELSE
parancsok;]
END IF;
Példa.
A következő blokk
IF parancsot tartalmazz.
DECLARE
V_helyek_szama
Szoba.helyek_szama;
v_Comment VARCHAR2(35);
BEGIN
SELECT termek INTO v_helyek_szama
FROM Szoba WHERE kod = 15;
IF v_helyek_szama < 50 THEN v_Comment := 'Kicsi';
ELSIF v_helyek_szama < 100 THEN v_Comment := 'Közepes';
ELSE v_Comment := 'Nagy';
END IF;
END;
DECLARE
K NUMBER := 7;
BEGIN
IF K < 5 THEN
INSERT INTO temp (col) VALUES ('Nagyon kicsi');
ELSIF Kr < 10 THEN
INSERT INTO temp (col) VALUES ('Megfelel');
ELSE
NULL;
END IF;
END;
A parancsok előtt << címke >> állhat:
<<
címke >> parancs
A címkét a << ,>> határoló-jelek közé kell helyezni.
A
GOTO <<
címke >>;
parancs a vezérlést a <<
címke >> után álló parancsra
adja. A GOTO parancs a vezérlést nem
adhatja a beágyazott blokkba, vagy FOR ciklus
, illetve IF parancs belsejébe.
Példa.
DECLARE
k BINARY_INTEGER := 1;
BEGIN
LOOP
INSERT INTO temp VALUES (k, 'Lépések
száma');
k := k + 1;
IF k >
50
THEN GOTO
Vége;
END IF;
END LOOP;
<<Vége>> INSERT INTO test (col) VALUES ('Vége!');
END;
A PL/SQL-ben három
fajta ciklus létezik.
10.1.4 Egyszerű (LOOP)
ciklus
LOOP
<Parancsok>
EXIT [WHEN feltététel]
END LOOP;
EXIT WHEN <feltététel> – feltételes kilépés a ciklusból (ha a
feltétel igaz).
Példa.
DECLARE
K
BINARY_INTEGER := 1;
BEGIN
LOOP
INSERT INTO test (num_col) VALUES (K);
K := K + 1;
EXIT WHEN K > 50;
END LOOP;
END;
Vagy
DECLARE
k BINARY_INTEGER
:= 1;
BEGIN
LOOP
INSERT INTO test VALUES (k, 'A ciklus indexe');
k := k + 1;
IF k > 50
THEN EXIT;
END IF;
END LOOP;
END;
Példa.
A következő ciklus a kurzor
soraival hajtja végre a műveleteket.
DECLARE
V1 VARCHAR2(20);
V2 VARCHAR2(20);
CURSOR Cursor_Students IS
SELECT nev, kod FROM students;
BEGIN
OPEN Cursor _Students;
LOOP
FETCH Cursor _Students INTO V1, V2;
EXIT WHEN Cursor_Students%NOTFOUND;
/*
Parancsok, a cilkus magja. */
END LOOP;
CLOSE Cursor_Students;
END;
A kurzornak következő fontosabb attribútumai vannak:
10.1.5 WHILE ciklus
WHILE <feltétel> LOOP
<Parancsok>
END LOOP;
Példa.
DECLARE
k BINARY_INTEGER := 1;
BEGIN
WHILE k <= 50 LOOP
INSERT INTO test VALUES (k, ' A ciklus
indexe’);
k := k + 1;
END LOOP;
END;
10.1.6 FOR ciklus
FOR <változó> IN
[REVERSE] i_min .. i_max LOOP
<Parancsok, Ciklus magja>
END LOOP;
A <változó>
felveszi i_min .. i_max minden
értékét növekvő, vagy REVERSE esetén
csökkenő irányban, és az adott érték mellett végrehajtódik a ciklus magja.
<változó>=i_min,
i_min+1, i_min+2,..., i_max;
Példa.
BEGIN
FOR k IN 1..50 LOOP
INSERT INTO test VALUES (k, ' A ciklus
indexe');
END LOOP;
END;
BEGIN
FOR k IN REVERSE 1..50 LOOP
INSERT INTO test VALUES (k, ' A ciklus indexe ');
END LOOP;
END;
10.1.7 KURZOR FOR ciklus
DECLARE
CURSOR Kurzor_Név IS
SELECT-parancs;
BEGIN
FOR Kurzor _Változó IN kurzor
LOOP
Parancsok
END LOOP;
END;
A kurzor FOR ciklus kényelmes eszköz a
kurzor alkalmazására, mivel ebben az esetben nincs szükség a kurzor megnyitására,
a sorainak leolvasására, és a kurzor bezárásába.
Példa.
DECLARE
...
CURSOR Cursor_Students IS
SELECT nev, kod FROM students;
BEGIN
K:=0;
FOR C_valt IN Cursor _Students;
LOOP
K:=K+1;
INSERT INTO test_tabla VALUES (C_valt.nev, K);
END LOOP;
END;
A PL/SQL program-konstrukcióknak blokk szerkezetűk van.
Két fajta blokk létezik
· névtelen blokk (Anonymous block)
· névvel rendelkező blokk (Named block).
A névtelen blokk a DECLARE vagy a BEGIN kulcsszóval kezdődik és az Oracle a blokkok mindegyik végrehajtása előtt újból kell lefordítja (compile). A névtelen blokk nem tárolódhat az AB-ban, és a program-egységek nem hivatkozhatnak rá.
A blokkot a következő program-egységek
- eljárások
- függvények
- csomagok (modulok)
- triggerek
tartalmazzák, és ezek a program-egységek tárolhatók az AB-ban.
A blokk általános struktúrája
[<<blokk_név>>]
[DECLARE ….]
BEGIN
….
[EXCEPTION….]
END;
A névtelen blokk nem
tartalmazz <<blokk_nev>>-et.
DECLARE ….- a változók
deklarálása (nem kötelező része a blokknak)
BEGIN...END; - a blokk törzse, a blokk egyetlen
kötelező része
EXCEPTION…. – a blokk kivételkezelője
(opcionális, nem kötelező része a blokknak).
Példa.
<<Pelda>>
DECLARE
v_Num1 NUMBER
:= 3;
v_Num2 NUMBER := 4;
v_String1 VARCHAR2(50) := 'Hello World!';
v_String2 VARCHAR2(50) := '-- ';
v_OutputStr VARCHAR2(50);
BEGIN
INSERT INTO test (num_col, char_col)
VALUES
(v_Num1, v_String1);
INSERT INTO test (num_col, char_col)
VALUES (v_Num2,
v_String2);
SELECT char_col INTO v_OutputStr
FROM test WHERE num_col = v_Num1;
DBMS_OUTPUT.PUT_LINE(v_OutputStr);
SELECT char_col INTO v_OutputStr
FROM test WHERE num_col = v_Num2;
DBMS_OUTPUT.PUT_LINE(v_OutputStr);
END Pelda;
10.3 Változók
deklarálása
A változókat
a DECLARE szekcióban deklaráljuk. Az
Oracle lehetőséget ad a saját típusokat szerkesztésére is. A változó
deklarálása:
Változó-neve típus
[CONSTANT] [NOT NULL] [:=
érték] ;
Leggyakoribb adattípusok:
·
VARCHAR2
·
NUMBER
·
DATE
·
BOOLEAN.
Numerikus típusok:
- DEC
- DECIMAL
- DOUBLE PRECISION
- INTEGER
- INT
- NUMERIC
- REAL
- SMALLINT
- BINARY_INTEGER (- 2147483647... 2147483647)
- NUMBER (m, n)
Példák
NUMBER 12.36
12.36
NUMBER (3) 123 123
NUMBER (3) 1234 HIBA
NUMBER (4,3) 1.234567 1.235
NUMBER (4,-3) 1234 1000
NUMBER (4,-1) 1234 1230
A BOOLEAN változó lehetséges értékei- TRUE, FALSE, NULL. Ha a lehetséges NULL értéket is figyelemben vesszük,
akkor a logikai műveletek táblázatait lehet leírni, mint három-értékű logikát.
AND (és) táblázat
|
AND
|
T
|
F
|
NULL
|
|
T
|
T
|
F
|
Ismeretlen
|
|
F
|
F
|
F
|
F
|
|
NULL
|
Ismeretlen
|
F
|
Ismeretlen
|
OR (vagy) táblázat
|
OR
|
T
|
F
|
NULL
|
|
T
|
T
|
T
|
T
|
|
F
|
T
|
F
|
Ismeretlen
|
|
NULL
|
T
|
Ismeretlen
|
Ismeretlen
|
NOT (nem) táblázat
|
NOT
|
|
|
T
|
F
|
|
F
|
T
|
|
NULL
|
Ismeretlen
|
Egy változó típusát egy AB tábla oszlopának típusa alapján
is lehet deklarálni a %TYPE
bejegyzéssel.
Name student.nev%TYPE
A Name változó
megkapja a student tábla nev oszlopának típusát. Ez a lehetőség
különösen akkor hasznos, amikor az oszlop típusa valami okból később
megváltozik, de a változó típusa is a %TYPE
alapján automatikusan megváltozik, ami azt jelenti, hogy a programozónak ezzel
a kérdéssel nem kell foglalkozni.
Példa.
DECLARE
Kod_diak NUMBER(5) := 10000;
V_Nev VARCHAR2(20);
BEGIN
SELECT Nev INTO V_Nev FROM students WHERE Id = Kod_diak;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO log_table (info) VALUES ('Nem
létezik a 10000 kóddal jelölt Diák!');
END;
Példa.
DECLARE
v_Num1 NUMBER
:= 1;
v_Num2 NUMBER
:= 2;
v_String1 VARCHAR2(50)
:= 'Hello World!';
v_String2 VARCHAR2(50)
:= '-- ';
v_OutputStr VARCHAR2(50);
BEGIN
INSERT INTO test (num_col, char_col) VALUES (v_Num1, v_String1);
INSERT INTO test (num_col, char_col) VALUES (v_Num2, v_String2);
SELECT char_col INTO v_OutputStr FROM test
WHERE num_col = v_Num1;
DBMS_OUTPUT.PUT_LINE(v_OutputStr);
SELECT char_col INTO
v_OutputStr FROM test WHERE num_col
= v_Num2;
DBMS_OUTPUT.PUT_LINE(v_OutputStr);
END;
10.4 Rekordok
A PL/SQL programokban rekordokat lehet létrehozni és utána alkalmazni. A rekord
deklarációval egy új adattípust hozhatunk létre. Gyakran a rekord struktúráját
úgy szerkesztik, hogy az megegyezzen egy tábla struktúrájának. Ebben az esetben
a tábla sorai könnyen átírhatók a rekordba.
Először a rekord-típust kell
deklarálni, és utána a rekord-változó megkaphatja a rekord-típust. A rekord mezőinek típusai PL/SQL adattípusúak
lehetnek, de a %TYPE használatával
hivatkozhatnak egy tábla oszlopának a típusára is. A mezőkhöz NOT NULL és DEFAULT záradékok tartozhatnak. A rekord mezőjére a következő
képen hivatkozhatunk
rekord_változó.mező
Példa.
TYPE
Diak_Record IS
RECORD (
Diak_Kod NUMBER
(5),
Vezetek_Nev VARCHAR2 (20),
Kereszt_Nev VARCHAR2 (20));
Diak_Info Diak_Record;
A Diak_Info változó
megkapja a Diak_Record típust.
DECLARE
TYPE t_Rec1Type IS RECORD (
Field1
NUMBER,
Field2
VARCHAR2(5));
TYPE t_Rec2Type IS RECORD (
Field1 NUMBER,
Field2 VARCHAR2(5));
v_Rec1 t_Rec1Type;
v_Rec2 t_Rec2Type;
/*
v_Rec1 és v_Rec2 típusai különbözőek!
*/
BEGIN
v_Rec1 := v_Rec2; /*
különböző típusok, HIBA !!! */
v_Rec1.Field1 := v_Rec2.Field1;
v_Rec2.Field2 := v_Rec2.Field2;
END;
DECLARE
TYPE Diak_Record IS RECORD (
Vezetek_Nev students.vezetek_nev%TYPE,
Kereszt_Nev students.kereszt_nev%TYPE,
Szak students.szak%TYPE);
V_Diak Diak_Record;
BEGIN
SELECT vezetek_nev, nev, szak INTO V_Diak
FROM students WHERE
ID = 10000;
END;
A %ROWTYPE bejegyzés
alkalmazása a rekord típusok deklarálásában
DECLARE
V_RoomRecord rooms%ROWTYPE
A %ROWTYPE
által a V_RoomRecord rekord megkapja a room
tábla struktúráját. Ez a lehetőség akkor lehet hasznos, ha például, a room tábla struktúrája megváltoztatjuk, de
a %ROWTYPE alapján a rekord
struktúrája is automatikusan megváltozik.
Az SQL parancsok
változókat tartalmazhatnak, és ezzel összekapcsolhatók egymással egy program parancsai.
Példa.
DECLARE
v_NumCredits classes.num_credits%TYPE;
BEGIN
v_NumCredits := 3;
UPDATE Classes SET num_credits =
v_NumCredits
WHERE szak = 'MAT' AND
tantargy = 101;
END;
Példa.
DECLARE
v_DiakRecord students%ROWTYPE;
v_szak classes.szak%TYPE;
v_
tantargy
classes.tantargy%TYPE;
BEGIN
SELECT * INTO V_DiakRecord
FROM students WHERE id = 10000;
SELECT department, course INTO v_szak, v_ tantargy
FROM classes WHERE kod = 99997;
END;
10.5 Objektum típusok
Az Objektumrelációs
adatbázis-kezelő rendszer (ORDBMS –
Object-Relation DataBase Management System) – támogatja mind a
relációs eszközöket (kulcs, ...) mind az objektumorientált eszközöket
(módszerek, ... ).
Az alkalmazások szempontjából fontos,
hogy a PL/SQL programokban bizonyos összetett adat-struktúrákat egységesen lehessen
kezelni. Ezzel a tulajdonsággal rendelkezik az objektum típus, amelyet absztrakt
adattípusnak lehet tekinteni.
Az absztrakt
adattípus – olyan adattípus,
amely több altípusból tevődik össze. Az objektum
típus attribútumokat és metódusokat
tartalmazhat. Általános esetben, amikor az objektum
típus metódusokat is tartalmaz, az két részből áll:
- az objektum deklarálásából (specifikation);
- az objektum törzséből.
Az objektum deklarálása az attribútumokat és a hozzá tartozó
metódusok listáját tartalmazza. Az objektumban legalább egy attribútumnak kell
lennie. Az objektumban a metódusok hiányozhatnak. Az objektum törzse a
metódusok kódját tartalmazza. Egy objektumot csak az a felhasználó hozhat
létre, aki a CREATE TYPE
privilégiummal rendelkezik. A CREATE
TYPE a RESOURCE szerephez tartozik. Más
felhasználó akkor hozhat létre új típust, ha CREATE ANY TYPE privilégiummal rendelkezik. Ezek a feltételek a CREATE TYPE BODY parancsra is
érvényesek.
AS OBJECT
(attribútum lista, [,metódusok listája])
Az attribútum deklarálása
hasonlít a változók deklarálása a blokkban, de nem tartalmazhat %TYPE opciót,
nem kaphat kezdő értéket, és a NOT NULL megszorítás
sem alkalmazható.
Az objektum az AB szótárához tartozik, és mivel az
objektumnak tulajdonosa kell, hogy legyen, ezért, ha a CREATE parancsban nincs megadva a felhasználó neve, akkor a deklarálandó objektumnak a tulajdonosa az
adott felhasználó lesz. Egy másik felhasználó esetén annak a nevét meg kell
adni. Csak az a felhasználó alkalmazhatja az objektumot, aki EXECUTE
privilégiummal rendelkezik.
Hivatkozás az objektum attribútumára:
Objektum_neve.attribútum
Példa.
CREATE OR REPLACE TYPE StudentObj AS
OBJECT (
ID NUMBER(5),
vezetek_nev VARCHAR2(20),
kereszt_nev VARCHAR2(20),
szak VARCHAR2(30),
kreditek NUMBER(3)
);
Példa.
CREATE OR REPLACE TYPE Car AS
OBJECT (
Model VARCHAR2(20),
Color VARCHAR2(20),
Cost NUMBER(6)
);
CREATE OR REPLACE TYPE Car_Garage AS
VARRAY(50) OF Car;
CREATE OR REPLACE TYPE Garage AS
OBJECT (
Adress VARCHAR2(100),
CarCount NUMBER
AllCar Car_Garage);
A Car – objekt típus az autó egyedeket, a Garage – a garázs egyedeket, a Car_
Garage –típus pedig az autók
csoportjait tartalmazzák.
DECLARE
Változó Objektum_típus;
DECLRE
MyCar Car;
MyGarage Garage;
A létrehozott típusok nem
tartalmaznak metódusokat, ezért ebben az esetben nincs szükség az objektum
törzsének deklarálására.
10.5.2 Metódusok
Mint már említettük, az objektum
deklarálása tartalmazhat az objektumhoz tartozó metódusok listáját. A metódusok
listája a következő deklarálási elemeket tartalmazhat:
[STATIC| MEMBER] PROCEDURE eljárás_deklarálása,
[STATIC| MEMBER] FUNCTION függvény_deklarálása,
……………………
Példa.
A Car objektumot kiegészítjük
metódusokkal:
CREATE OR REPLACE TYPE Car AS
OBJECT (
Model VARCHAR2(20),
Color VARCHAR2(20),
Cost NUMBER(6),
MEMBER FUNCTION GetCarInfo RETURN VARCHAR2,
PRAGMA RESTRICT_REFERENCES(GetCarInfo) );
A metódusokat az attribútumok után kell leírni. A PRAGMA RESTRICT_REFERENCES záradék
engedélyezi a metódusokra való hivatkozást az SQL-parancsokból.
A metódusok kódját az objektum törzsében kell leírni:
<metódusok_törzseinek_listája>
Példa.
CREATE OR REPLACE TYPE BODY Car AS
MEMBER FUNCTION GetCarInfo RETURN VARCHAR2
IS
BEGIN
RETURN Modell || ’’ ||
Color || ‘’ || Cost;
END GetCarInfo;
END;
Hivatkozás a metódusra:
Objektum_neve.Metódus
Példa.
Az SQL*Plus-ban
végrehajtjuk a következő programot:
DECLARE
Car1
Car:=
Car(’Audi’, ’Piros’, 3000000);
Car2
Car:=
Car(’BMW’, ’Fehér’, 2500000);
BEGIN
DBMS_OUTPUT.PUT_LINE(Car1.GetCarInfo);
DBMS_OUTPUT.PUT_LINE(Car2.GetCarInfo);
END;
Audi Piros
3000000
BMW Fehér
2500000
PL/SQL procedure successfully completed.
Az objektum kezdő értékekeit a konstruktor által legegyszerűbben megadni. Az
Oracle mindegyik objektum-típushoz létrehoz automatikusan egy konsztruktor-függvényt.
Például, a Garage
objektum konstruktora a következő függvény lesz:
FUNCTION
Garage(
Adress IN
VARCHAR2(100);
CarCount IN NUMBER
AllCar IN CarGarage)
RETURN Garage;
REPLACE
AS OBJECT
(<OBJECT-típus
deklaráció>);
A parancsban nem csak a
módosított elemeket kell megadni, hanem újból kell deklarálni azokat az
elemeket, amelyek nem váltóznak. A metódus kódjának a módosítása a
CREATE OR REPLACE TYPE BODY [felhasználó.]típus_neve AS| IS
<metódusok_törzseinek_listája>
paranccsal történik.
Az objektum-típus törlése több
változatban történhet:
Ebben az esetben az Oracle csak akkor
törli az objektum-típust, ha más objektum-típus nem hivatkozik rá.
·
DROP TYPE [felhasználó.]típus_neve
FORCE
A FORCE záradék az objektum-típus törlését engedélyezi még akkor is,
ha az adott objektum-típusra más AB-objektum-típus hivatkozik.
·
DROP TYPE BODY [felhasználó.]típus_neve
Ez a parancs törli az
objektum-típus törzsét, de nem törli az objektum-típus deklarációját.
Az Oracle fontos tulajdonsága,
hogy az objektumokat az AB táblákban lehet tárolni. Ez két változatban valósítható
meg. Az objektumot tárolni lehet, mint
·
Objektum-oszlop;
·
Objektum-sor.
10.5.5.1 Objektum-oszlop
Ebben az esetben az objektum a táblában ugyanúgy
oszloponként tárolódik, mint a többi elsődleges típus.
Példa.
CREATE TABLE Cars(
Key NUMBER
PRIMARY KEY,
OneCar Car);
A Cars tábla második oszlopa
objektum-típusú.
INSERT INTO Cars VALUES(1,
Car(’Skoda’,
’Piros’, 2000000 ) );
INSERT INTO Cars VALUES(2,
Car(’Lada’,
’Zöld’, 1200000 ) );
Az adatok
bevítelére a táblába az INSERT
parancs a Car konstruktort
alkalmazza.
A Car tábla tartalmát az SQL*Plus-ban így
lehet megjeleníteni
SELECT * FROM Cars;
KEY ONECAR(MODEL, COLOR,
COST)
1 CAR(’Skoda’,
’Piros’, 2000000)
2 CAR(’Lada’,
’Zöld’, 1200000 )
10.5.5.2 Objektum-sor
Ebben az esetben az objektum a
tábla egész sorával azonosul, és a tábla nem tartalmazhat más oszlopokat.
CREATE TABLE Tábla_név OF objektum-típus;
Példa.
CREATE TABLE CarRows OF Car;
INSERT INTO CarRows VALUES(Car(’Skoda’, ’Piros’,
2000000 ) );
INSERT INTO CarRows VALUES(Car(’Lada’,
’Zöld’, 1200000 ) );
SELECT * FROM CarsRows;
MODEL COLOR
COST
Skoda
Piros 2000000
Lada Zöld 1200000
Az SQL-parancsok végrehajtásának
módja nem változik, ha egy tábla objektum-oszlopokat is tartalmaz. Például, egy
objektum értékét egy vele azonos típusú objektum típusú változóba lehet átmásolni.
Végrehajtunk néhány DML-parancsot a Cars táblával.
DECLARE
C CAR;
Id
NUMBER;
BEGIN
SELECT MAX(Key) INTO Id FROM Cars;
SELECT OneCar INTO C FROM Cars
WHERE Key=Id;
C.Model:=’BMW’;
C.Color:=’Fekete’;
INSERT INTO Cars VALUES (Id+1, C);
END;
Az eredmény:
SELECT * FROM Cars;
KEY ONECAR(MODEL, COLOR, COST)
------
---------------------------------------------
1 CAR(’Skoda’, ’Piros’, 2000000)
2 CAR(’Lada’, ’Zöld’, 1200000 )
3
CAR(’BMW’,’Fekete’, 1200000)
Az SQL parancsokban az objektum attribútumaira csak úgy hivatkozhatunk,
hogy megadjuk a tábla másodlagos (alias)
nevét is.
Példa.
SELECT C.OneCar.Model FROM Cars C;
ONECAR.MODEL
------------------------
Skoda
Lada
BMW
Akkor is szükség van a tábla
másodlagos nevére, ha hivatkozni akarunk az objektum metódusára.
SELECT C.OneCar.GetCarInfo() FROM Cars C;
C.ONECAR.GETCARINFO()
----------------------------
Skoda Piros 2000000
Lada Zöld
1200000
BMW Fekete
1200000
Az objektum-sorok esetén az objektum attribútumait ugyanúgy lehet
alkalmazni, mintha azok hagyományos relációs tábla oszlopai lennének.
SELECT C.OneCar.GetCarInfo() FROM CarsRows C;
C.GETCARINFO()
----------------------------
Skoda Piros 2000000
Lada Zöld
1200000
BMW Fekete
1200000
Az Oracle a következő összetett
konstrukciót tartalmaz:
- Indexelt táblák (Index-by tables)
- Beágyazott táblák (Nested tables)
- Tömbök (Változó hosszuságuak),
amelyek objektum tulajdonsággal rendelkeznek, mivel tartalmaznak
attribútumokat és metódusokat.
Az indexelt táblák és a
beágyazott táblák a PL/SQL táblákat
alkotják. A beágyazott
táblákat az AB táblákban is lehet
tárolni (ezért kapták a nevüket). Az indexelt
táblák nem tárolódhatnak az AB táblákban, és csak a PL/SQL programokban
alkalmazhatók.
10.6.1 Indexelt táblák
Az indexelt táblát nem azonosak az adatbázis táblával!.
Az indexelt tábla szintaxisa hasonlít a tömb szintaxisára.
Mielőtt egy indexelt táblát deklarálnánk, egy PL/SQL blokkban létre kell hozni
a típusát:
TYPE tábla_tipus IS TABLE OF tipus
INDEX BY
BINARY_INTEGER;
A tábla típusa objektum
típusú is lehet.
Az INDEX BY
BINARY_INTEGER paraméter kulcs jellegű, kötelező az indexelt táblák esetén, de a
beágyazott táblákban nem alkalmazhatók.
Az indexelt tábla két oszlopot tartalmaz:
- KEY (kulcs)
- VALUE (érték)
A kulcs típusa – BINARY_INTEGER,
a kulcs lehetséges értékei
(-2147483647...+
2147483647),
a VALUE típusát a
deklarációban kell megadni.
Nem kötelező, hogy a tábla elemeinek indexei egymás utáni
értékeket kapjanak.
Példa.
TYPE t_CharacterTable IS TABLE OF VARCHAR2(10)
INDEX BY BINARY_INTEGER;
Az indexelt tábla deklarálása:
V_Characters t_CharacterTable;
Példa.
DECLARE
TYPE t_NameTable IS TABLE OF students.vezetek_nev%TYPE
INDEX BY
BINARY_INTEGER;
TYPE t_DateTable IS TABLE OF DATE
INDEX BY
BINARY_INTEGER;
V_ Names t_NameTable;
V_ Dates t_DateTable;
A tábla elemeire az index által hivatkozhatunk
Tabla_név(index)
BEGIN
V_ Names(1):= ’Szabó’;
V_ Dates(-4):= SYSDATE - 1;
END;
SET SERVEROUTPUT ON
DECLARE
TYPE t_StudentTable IS TABLE OF students%ROWTYPE
INDEX BY BINARY_INTEGER;
V_Diak
t_StudentTable;
BEGIN
SELECT * INTO V_Diak(10001)
FROM students WHERE id = 10001;
V_Diak(10001).vezetek_nev := 'Kovács';
DBMS_OUTPUT.PUT_LINE(V_Diak(10001).vezetek_nev);
END;
10.6.2 Beágyazott
táblák
A Beágyazott táblára úgy tekinthetünk, mint egy adatbázis
táblára, amelynek két oszlopa van
- KEY (kulcs)
- VALUE (érték)
(mint az indexelt táblában).
A beágyazott tábla egy
AB tábla oszlopa lehet.
A beágyazott tábla
típusának deklarálása:
TYPE tábla_típus IS TABLE OF
típus;
A tábla elemeinek inicializálása a konstruktor-függvény használatával történik. A létrehozott tábla-elemek kezdő
indexe csak egy lehet, és a következő értékei mindég csak eggyel növekedhetnek.
Példa.
DECLARE
K
INTEGER;
TYPE Num_Tab IS TABLE OF
NUMBER;
Tab_1 Num_Tab :=Num_Tab(-1);
Tab_2 Num_Tab :=Num_Tab(1, 2, 3, 5, 7);
Tab_3 Num_Tab :=Num_Tab( );
BEGIN
Tab_1(1):=12345;
FOR K IN 1..5 LOOP
DBMS_OUTPUT.PUT(Tab_2(K) || ‘ ‘);
END LOOP;
DBMS_OUTPUT.NEW_LINE;
END;
END;
1 2 3 5 7
A példában az inicializáláskor a táblák elemei a következő
értékeket kapták:
Tab_1(1)=( -1),
Tab_2(1)=(1), Tab_2(2)=(2), Tab_2(3)=(3),
Tab_2(4)=(5), Tab_2(15=(7);
A Tab_3 létezik, de egyetlen elemet sem tartalmaz. A
programban a Tab_1 tábla első eleme megváltozik (12345 értéket kap).
10.6.3 Tömbök
Az Oracle-ban használható tömb
megfelel a C és a Java nyelvekben alkalmazott tömböknek. A tömb elemeire ugyanúgy lehet hivatkozni, mint az indexelt, vagy a beágyazott táblák elemeire. A tömb indexnek a kezdő értéke mindig
egye, és eggyel növekszik.
A
tömb típus deklarálása:
TYPE típus_név IS VARRAY
<maximális_méret>
OF elemek_típusa [NOT NULL];
Az elemek_típusa
alap-, rekord, vagy objektum típusú lehet. Ezenkívül, a %TYPE segítségével az AB tábla
oszlop típusát lehet alkalmazni, a %ROWTYPE pedig az AB tábla sorai
alapján egy rekord típust hoz létre.
A NOT NULL nem
engedélyezi, hogy a tömb üres elemeket tartalmazzon.
Példa.
TYPE Num_List IS VARRAY
(20)
OF NUMBER(3) NOT NULL;
TYPE Car_List IS VARRAY
(100)
OF CarArr%ROWTYPE;
TYPE Car_Arr IS VARRAY
(20)
OF Car;
A tömb kezdő értékeit a konstruktorok által lehet megadni.
DECLARE
TYPE Var_Num IS VARRAY
(20) OF NUMBER;
Var_1
Var_Num := Var_Num(1, 2, 3);
BEGIN
DBMS_OUTPUT.PUT_LINE(Var_1(1));
Var_1(1):=15;
DBMS_OUTPUT.PUT_LINE(Var_1(1));
END;
1
15
A tömb méretét az EXTEND metódussal
lehet növelni.
Mivel a tömbök és beágyazott táblák objektum típusú
konstrukciók, azok metódusokkal is
rendelkeznek, az indexelt táblákhoz pedig
attribútumok tartoznak. A metódusokra, mint az attribútumokra is s következő képen
lehet hivatkozni:
konstrukció_eleme.metódus
vagy
konstrukció_eleme.attribútum
A metódusok csak a blokkokban alkalmazhatók, és nem az
SQL-parancsokban.
Az Oracle felismeri a következő beépített metódusokat:
- EXISTS (az eredmény típusa – BOOLEAN)
- COUNT (az eredmény típusa – NUMBER)
- FIRST (az eredmény típusa – BINARY_INTEGER)
- LAST (az eredmény típusa – BINARY_INTEGER)
- NEXT (az eredmény típusa – BINARY_INTEGER)
- PRIOR (az eredmény típusa – BINARY_INTEGER)
- EXTEND (új elemeket szúr be a konstrukcióba)
- TRIM (törli a konstrukció utolsó elemeit)
- DELETE (törli az elemeket a konstrukcióban)
10.6.4.1 EXISTS metódus.
Az EXISTS metódust akkor alkalmazzuk, ha akarjuk megállapítani, hogy
létezik-e az n-ik elem
EXISTS(n)
A metódus eredménye TRUE,
ha az adott elem létezik, különben FALSE.
Példa.
DECLARE
TYPE t_Vezetek_NevTable IS TABLE OF students.vezetek_nev%TYPE
INDEX BY BINARY_INTEGER;
Vezetek_Nevs t_Vezetek_NevTable;
BEGIN
Vezetek_Nevs(1) := 'Szabó';
Vezetek_Nevs(3) := 'Kiss';
IF Vezetek_Nevs.EXISTS(1) THEN
INSERT INTO test (char_col) VALUES
(
'Az 1 sor létezik!');
ELSE
INSERT INTO test (char_col) VALUES
(' Az 1 sor nem létezik!!');
END IF;
IF Vezetek_Nevs.EXISTS(2) THEN
INSERT INTO test (char_col) VALUES
(' A 2 sor létezik!');
ELSE
INSERT INTO test (char_col) VALUES
(' A 2 sor nem létezik!');
END IF;
END;
10.6.4.2 COUNT metódus
A COUNT metódus
nem tartalmaz paramétereket, és az eredménye a konstrukció elemeinek száma.
Példa.
DECLARE
TYPE Tabla_1 IS TABLE OF NUMBER
INDEX
BY BINARY_INTEGER;
Szamok Tabla_1;
Osszesen NUMBER;
BEGIN
FOR k IN 1..50 LOOP
Szamok(k) := k;
END LOOP;
Osszesen := Szamok.COUNT;
DBMS_OUTPUT.PUT_LINE(Osszesen);
END;
50
NEXT(n)
visszaadja az n
után következő indexet (növekvő irányban),
a PRIOR metódus pedig csökkenti a kulcs (KEY) értékét
PRIOR(n)
az n előtti
indexet kapjuk (csökkenő irányban).
Ha az adott értékű kulcs (n) nem létezik, akkor a NEXT
és PRIOR eredménye NULL lesz.
A FIRST
metódus az index első értékét adja, a LAST pedig – az utolsó értékét. A FIRST és a LAST
metódusok nem tartalmaznak paramétert.
Példa.
DECLARE
TYPE Nev_Table IS TABLE OF
students.nev%TYPE
INDEX BY BINARY_INTEGER;
v_Nev
Nev_Table;
v_Index BINARY_INTEGER;
BEGIN
-- Új
sorokat szúrunk be a táblába.
v_Nev(43) := 'Sándor';
v_Nev(50) := 'Mária';
v_Nev(47) := 'Iván';
v_Index := v_Nev.FIRST; -- v_Index=43
v_Index
:= v_Nev.LAST; -- v_Index=50
END;
Példa.
DECLARE
TYPE Szakok IS TABLE OF students.szak%TYPE
INDEX BY BINARY_INTEGER;
v_Szak t_Szakok;
v_Index BINARY_INTEGER;
BEGIN
v_Szak(-7) := 'Számítástechnika';
v_Szak(4) := 'Történelem';
v_Szak(5) := 'Matematika';
v_Index := v_Szak.FIRST;
LOOP -- a v_Index a
következő értékeket kapja a ciklusban- -7, 4, 5.
INSERT INTO test (num_col, char_col) VALUES (v_Index, v_Szak(v_Index));
EXIT WHEN v_Index = v_Szak.LAST;
v_Index := v_Szak.NEXT(v_Index);
END LOOP;
END;
Példa.
DECLARE
TYPE Char_Tab IS TABLE OF CHAR(1);
Char_1
Char_Tab:= Char_Tab(‘a’,
’b’, ’c’, ’d’, ’e’);
Ind INTEGER;
BEGIN
Ind:=Char_1.FIRST;
WHILE Ind<= Char_1.LAST LOOP
DBMS_OUTPUT.PUT(Char_1(Ind));
Ind:=Char_1.NEXT(Ind);
END LOOP;
DBMS_OUTPUT.NEW_LINE;
Ind:=Char_1.LAST;
WHILE Ind >= Char_1.FIRST LOOP
DBMS_OUTPUT.PUT(Char_1(Ind));
Ind:=Char_1.PRIOR(Ind);
END LOOP;
DBMS_OUTPUT.NEW_LINE;
END;
abcde
edcba
10.6.4.4 EXTEND metódus
Az EXTEND metódus
új elemeket szúr be a konstrukcióba. A metódus három formában alkalmazható
·
EXTEND
·
EXTEND(n)
·
EXTEND(n, i)
Az EXTEND paraméterek
nélkül a konstrukció végére NULL (üres) elemet szúr be;
EXTEND (n) a konstrukció végére n NULL
(üres) elemet szúr be;
EXTEND (n, i) az i
számú elemet n-szer a konstrukció
végére másolja át.
Ha a konstrukció a NOT
NULL záradékkal volt létrehozva, akkor az EXTEND csak az utolsó formájában alkalmazható.
Példa.
A SQL*Plus-ban:
DECLARE
TYPE Num_Tab IS TABLE OF NUMBER;
TYPE Num_Var IS VARRAY(25) OF NUMBER;
Tab_1 Num_Tab :=Num_Tab(1, 2, 3,
4, 5);
Tab_2 Num_Var :=Num_Var(1, 2, 3,
4, 5);
BEGIN
Tab_1(26) := -7; -- A Tábla 26-ik eleme nem létezik
EXCEPTION
WHEN SUBSCRIPT_BEYOND_COUNT
THEN
DBMS_OUTPUT.PUT_LINE(‘A Tábla 26-ik eleme nem létezik’);
END;
--A
PL/SQL táblát lehet bővíteni:
Tab_1.EXTEND(30);
Tab_1(26) := -7; -- most már
Tab_1(26) létezik
-- A tömböt csak a maximális méretig (25) lehet bővíteni.
Tab_2(26) := -7;
EXCEPTION
WHEN SUBSCRIPT_OUTSIZE_LIMIT
THEN
DBMS_OUTPUT.PUT_LINE(‘Nem sikerült a tömb méretét növelni a 30-ik elemig’);
END;
END;
10.6.4.5 TRIM metódus
A TRIM metódus
törli a konstrukció utolsó elemét (elemeit). Két formája létezik
TRIM és
TRIM(n)
A TRIM az utolsó
elemet törli. A TRIM(n) törli az utolsó n elemet. Ha n>COUNT,
akkor SUBSCRIPT_BEJOND_COUNT kivételes szituáció következik be. A TRIM végrehajtása után a COUNT értéke is megváltozik.
10.6.4.6 DELETE metódus
A DELETE metódus
egy vagy több elemet töröl az indexelt vagy beágyazott táblából. A DELETE
a tömbök esetén nem alkalmazható. Három formája van
DELETE;
DELETE(n);
DELETE(n,
m)
DELETE
törli az egész táblát.
DELETE(n)
azt az elemet törli, amelynek az indexe = n.
DELETE(n,
m) azokat az elemeket törli, amelyeknek az indexe n és m között van.
Példa.
DELETE (5) --
törli az 5. elemet
DELETE(5, 8) -- törli az
5,6,7,8 indexű elemeket
DECLARE
TYPE Tabla_ertekek IS TABLE OF VARCHAR2(10)
INDEX BY BINARY_INTEGER;
Ertekek
Tabla_ertekek;
BEGIN
Ertekek(1) := 'Egy';
Ertekek(3) := 'Három';
Ertekek(-2) := 'Minusz kettő';
Ertekek(0)
:= 'Zeró';
Ertekek(100) := 'Száz';
DBMS_OUTPUT.PUT_LINE('Törlés előtt, összesen=' || Ertekek.COUNT);
Ertekek.DELETE(100);
DBMS_OUTPUT.PUT_LINE('Első törlés után, összesen=' || Ertekek.COUNT);
Ertekek.DELETE(1,3); -- Törli az 1 és 3 indexű elemeket
DBMS_OUTPUT.PUT_LINE('Második törlés után, összesen=' || Ertekek.COUNT);
Ertekek.DELETE; -- Törli az összes elemet
DBMS_OUTPUT.PUT_LINE(' Utólsó törlés után, összesen=' || Ertekek.COUNT);
END;
A beágyazott táblákat és a tömböket (indexelt táblákat nem) lehet tárolni az adatbázisban.
Az összetett konstrukciókat akkor lehet bevinni az AB táblába, ha mint az SQL,
mint a PL/SQL-parancsokból láthatók
(elérhető) a konstrukció típusa. Ez csak akkor lehetséges, ha a konstrukció a CREATE
TYPE paranccsal volt
létrehozva, mint objektum típus, és nem lokálisan egy PL/SQL-blokkban.
Példa.
CREATE OR REPLACE TYPE NameList
AS
VARRAY(20) OF VACHAR2(30);
Miután létrehoztuk a
NameList típust, utána ezt a típust alkalmazhatjuk a blokkokban,
eljárásokban, és csomagokban a változók deklarálására.
DECLARE
TYPE DateList
AS
VARRAY(10) OF DATE;
v_DateList DateList;
v_Names NameList;
BEGIN
NULL;
END;
Az a típus, amelyet a CREATE OR REPLACE TYPE parancs hozott
létre (például, NameList), az globális típus.
A DateList- lokális típus, mivel az
csak az adott blokkban érvényes, és az AB-ból nem érhető el.
Az AB tábla
oszlopa tömb-típusú is lehet. Ebben
az esetben a tábla egy sorához egy tömb tartozik. Tehát, a különböző sorok más
és más tömböket tartalmazhatnak.
Példa.
CREATE OR REPLACE TYPE ArrStr
AS
VARRAY(20) OF VACHAR2(30);
CREATE TABLE Varray_Tab
(
Id NUMBER
PRIMARY KEY,
Name VARCHAR2(20),
VarStr ArrStr);
INSERT INTO Varray_Tab
VALUES(1, ’Gyümölcsök’,
ArrStr(’Körte’, ’Alma’, ’Szilva’));
INSERT INTO Varray_Tab
VALUES(2, ’Zöldségek’,
ArrStr(’Káposzta’, ’Paradicsom’));
Az AB tábla oszlopában
beágyazott táblák is tárolódhatnak. Ebben az esetben a tábla mindegyik sora
tartalmaz egy hozzá tartozó beágyazott
táblát.
Példa.
CREATE OR REPLACE TYPE Nested_Table
AS
TABLE OF NUMBER;
CREATE TABLE Test_Nested
(
Id NUMBER,
Name VARCHAR2(20),
Tab_Values Nested_Table)
NESTED TABLE
Tab_Values STORE AS N_Tab;
Az N_Tab a tárolási tábla (store table) neve, és ez a tábla a Tab_Values beágyazott táblákat tárolja.
Ez azt is jelenti, hogy a Test_Nested
tábla a beágyazott táblákat direkt módon nem tárolja, hanem csak a mutatókat
tárolja a beágyazott táblákra. Új sorok beszúrása a tárolt táblák esetén is az INSERT
SQL DML paranccsal történhet.
INSERT INTO Test_Nested
VALUES(1, ’első sor’,
Nested_Table(1, 1, 1, 2, 2, 3, 3));
INSERT INTO Test_Nested
VALUES(2, ’második sor’,
Nested_Table(5, 6));
INSERT INTO Test_Nested
VALUES(1, ’harmadik sor’, Nested_Table(7, 8, 9, 10, 11, 12));
Az adatok módosítására az UPDATE parancsot alkalmazzuk, az adatok
törlésére pedig – a DELETE
parancsot. Az összetett konstrukciókat az AB táblából a SELECT paranccsal változókba lehet
átírni (mint alap típusú adatokat). Amikor egy beágyazott táblát átírunk egy PL/SQL változóba, akkor a változó
elemei megkapják az index értékeit növekvő sorrendbe egytől kezdve, az index
maximális értéke pedig COUNT lesz. A
következő példák bemutatják, hogy kaphatjuk meg a beágyazott tábla elemeit.
SELECT Tab_Values FROM Test_Nested
WHERE Id = 1;
TAB_VALUES
-------------------
NESTEDTABLE(1, 1, 1, 2, 2, 3, 3)
Egy másik lehetőség-
SELECT * FROM TABLE (SELECT Tab_Values
FROM Test_Nested WHERE
Id = 1);
COLUMN_VALUE
------------------------
1
1
1
2
2
3
3
SELECT * FROM TABLE (SELECT Tab_Values
FROM Test_Nested WHERE
Id = 1)
WHERE Column_Value>1
ORDER BY Column_Value DESC;
COLUMN_VALUE
------------------------
3
3
2
2
SELECT * FROM TABLE (SELECT Varray_Tab.VarStr
FROM Varray_Tab WHERE
Id = 1);
COLUMN_VALUE
------------------------
Körte
Alma
Szilva
Nincsenek megjegyzések:
Megjegyzés küldése