II. RF KMF DokuWiki

Bevezetés
A PL/SQL programozási nyelvet a következő Oracle komponensekben alkalmazzák:

• SQL*Plus
• Oracle Forms
• Oracle Reports
• Oracle Graphics
• Oracle Application Server

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

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

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áni parancsra irányítja. A GOTO parancs a vezérlést nem adhatja át a beágyazott blokkba, vagy a 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.

Egyszerű (LOOP) ciklus

     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;

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 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ékek mellett végre lesz hajtva 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;

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 egy hasznos 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 egységinek 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 blokkot mindegyik végrehajtása előtt újból lefordítja (compile). A névtelen blokk nem tárolódik az AB-ban, és a program-egységek nem hivatkozhatnak rá.
A blokkokat a következő program-egységek tartalmazzák:

• eljárások
• függvények
• somagok (modulok)
• triggerek

Ezek a program-egységek tárolódnak az AB-ban.

A blokk struktúrája

  [<<blokk_név>>]
     [DECLARE ….]
  BEGIN
     ….
  [EXCEPTION….]
  END;

A névtelen blokk nem tartalmazza a «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 szerkeszteni saját típusokat. Egy 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. А logikai műveletek táblázatát leírhatjuk, mint három-értékű logikát.

AND (és) táblázat

OR (vagy) táblázat

NOT (nem) táblázat

A változó típusát 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 hasznos lehet, amikor az oszlop típusa később megváltozik, de az igy deklarált változó típusa is a %TYPE alapján automatikusan megváltozik. Ebben az esetben 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;

A PL/SQL programokban rekordokat lehet létrehozni és 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ával. 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ípusa 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, és a %ROWTYPE alapján a rekord struktúrája automatikusan megváltozik.
Az SQL parancsok változókat tartalmazhatnak, és ez által a program parancsai összekapcsolhatók egymással.

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, … ) is.
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.

Objektum típusok 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, ezért az objektumnak tulajdonosa kell, hogy legyen. Ha a CREATE parancsban nincs megadva a felhasználó neve, akkor a deklarálandó objektumnak a tulajdonosa az adott felhasználó lesz. 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 – objektum típusú és az autó egyedeket tartalmazza, a Garage – a garázs egyedeket, a Car_ Garage –típus pedig az autó csoportjait tartalmazza.

Objektum típusú változó deklarálása DECLARE Változó Objektum_típus;

    DECLRE
       MyCar  		Car;
       MyGarage   		Garage;

A példában 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.

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.

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.

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áltoznak. A metódus kódjának 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.

Objektum-típus törlése
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öröli az objektum-típus törzsét, de nem töröli az objektum-típus deklarációját.

Objektumok az adatbázisban
Az Oracle fontos tulajdonsága, hogy az AB táblákban objektumokat lehet tárolni. Ez két változatban valósítható meg. Az objektumot tárolni lehet, mint

• Objektum-oszlop;
• Objektum-sor.

Objektum-oszlop
Ebben az esetben az objektum a táblában ugyanúgy oszloponként tárolódik, mint a többi alaptípusok.

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 ) 

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 

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;

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;

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;

Az objektum-sorok esetén az objektum attribútumaira ugyanúgy lehet hivatkozni, mint a hagyományos relációs tábla oszlopaira.

       SELECT C.OneCar.GetCarInfo() FROM CarsRows C;

Az Oracle a következő összetett konstrukciókat 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ákat nem tárolni az AB táblákban, és csak a PL/SQL programokban alkalmazhatók.

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;

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 elemének kezdő indexe csak egy lehet, és a következő értékei mindig 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 egy, é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 opoció 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;

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;

COUNT metódus
A COUNT metódus nem tartalmaz paramétereket, 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öröli a konstrukció utolsó elemét (elemeit). Két formája létezik:

        TRIM és TRIM(n).

A TRIM az utolsó elemet töröli. A TRIM(n) töröli 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 tömbök a DELETE nem alkalmazható. Három formája van:

    DELETE;
    DELETE(n);
    DELETE(n, m)

DELETE töröli az egész táblát.
DELETE(n) azt az elemet töröli, amelynek az indexe = n.
DELETE(n, m) azokat az elemeket töröli, amelyeknek az indexe n és m között van.

     DELETE (5)    -- töröli az 5. elemet
     DELETE(5, 8)  --  töröli az 5,6,7,8 indexű elemeket

Példa.

    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 lehet tárolni az adatbázisban. Az indexelt táblákat – nem. 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. Ez azt jelenti, hogy 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;

Egy másik lehetőség:

     SELECT * FROM TABLE (SELECT Tab_Values 
      FROM Test_Nested  	WHERE Id  = 1);

     SELECT * FROM TABLE (SELECT Tab_Values 
            FROM Test_Nested  	WHERE Id  = 1)
            WHERE Column_Value>1
            ORDER BY Column_Value DESC;

      SELECT * FROM TABLE (SELECT Varray_Tab.VarStr 
         FROM Varray_Tab  	WHERE Id  = 1);