2020. április 20., hétfő

SQL alapismeretek

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;

10.1.2  Üres ( NULL) parancs

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;

10.1.3    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;

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;
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;

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;

10.2       Blokkok
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;

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ípusolyan 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.

10.5.1 Objektum típus létrehozása

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.

10.5.1.1 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.

10.5.2.1  Metódusok implementálása
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.

10.5.3    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;

10.5.4    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.

10.5.5    Objektumok az adatbázisban 
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

10.5.6    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+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;

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

10.6.4.3 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

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;

10.6.5   Ö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

Nincsenek megjegyzések:

Megjegyzés küldése