grant select tabla to public;
--- analizállás
Analyze Table tmp Estimate Statistics Sample 33 Percent;
--- táblák és mezök listája
select * from ALL_TAB_COLUMNS where column_name like '%SAJAT%'
--- SQL történet
select * from dba_hist_sqlstat
select * from dba_hist_snapshot
---hosszú futás idő
select * from v$session_longops
----- select kikérése
select * from v$sql
SELECT * FROM v$sqlarea
select * from v$sqltext
select * from v$sqltext_with_newlines
select sesion.sid,
sesion.username,
optimizer_mode,
hash_value,
address,
cpu_time,
elapsed_time,
sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.username is not null
and sesion.username like 'K%'
SELECT SQL_FULLTEXT FROM GV$SQLAREA
SELECT * FROM GV$SQLAREA where sql_text like 'Select%sajat%'
---- adatbázis használat / várható futásidő
SELECT
username,
OPNAME,
message,
TO_CHAR (a.start_time, 'yyyy.mm.dd hh24:mi:ss') as started,
ROUND ((a.sofar / a.totalwork) * 100, 2) as complete,
TO_CHAR (TRUNC (a.time_remaining / 3600))
|| ':'
|| TO_CHAR (TRUNC (MOD (a.time_remaining, 3600) / 60), 'fm00')
|| ':'
|| TO_CHAR (TRUNC (MOD (MOD (a.time_remaining, 3600), 60)), 'fm00')
as timeremaining
FROM v$session_longops a
WHERE (sofar / totalwork) * 100 != 100
and totalwork != 0
and username like 'K%'
ORDER BY started DESC
Párhuzaqmosítás
ALTER SESSION ENABLE PARALLEL DML;
case when line=1 then 'create or replace ' else null end
||regexp_replace(text,'['||chr(10)||chr(13)||']+$') as SOROK
where 1=1
and type='FUNCTION'
and owner='USER_NEV'
and name='F_USER_PRIVATE_FUNCTION'
order by line;
SELECT DECOMPOSE('ol' || COMPOSE('e' || UNISTR('\0301'))) FROM dual;
ALTER SESSION SET NLS_TERRITORY = 'AMERICA';
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’ || ’!’;
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;
Címkék és GOTO parancs
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.
LOOP
<Parancsok>
EXIT [WHEN feltététel]
END LOOP;
EXIT - feltétel nélküli kilépés a ciklusból,
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:
· %FOUND – sort lehet leolvasni a kurzorból;
· %NOTFOUND – nincs több sor a kurzorban;
· %ISOPEN – meg van-e nyitva a kurzor;
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;
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;
REVERSE esetén- <változó>I=i_max, i_max-1, i_max-2,..., i_min.
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;
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;
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;
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;
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.
CREATE [OR REPLACE] TYPE [felhasználó].típus_neve
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.
Objektum típusú változó deklarálása
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:
CREATE [OR REPLACE] TYPE BODY [felhasználó].típus_neve AS| IS
<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.
Kezdő érték bevitele az objektumba
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;
Objektumok módosítása és törlése
ALTER TYPE [felhasználó.]<típus_neve>
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:
· DROP TYPE [felhasználó.]típus_neve
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.
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 )
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
Objektumok az SQL parancsokban
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+
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
Összetett konstrukciók (COLLECTIONS)
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.
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;
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;
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).
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.
Összetett konstrukciók metódusai
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)
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, PRIOR, FIRST és LAST metódusok
A NEXT metódus növeli a kulcs (KEY) értékét. A
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
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;
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.
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;
Összetett konstrukciók alkalmazása az adatbázisban
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