Adatbázis_I

9-10.gyak. PL/SQL programozás I. (kurzorhasználat)

-- gépes III. témakör: Oracle PL/SQL, alapok, kurzorhasználat
> 3.1. PL/SQL alapok, változóhasználat, vezérlési szerkezetek
> 3.2. SELECT INTO, hivatkozási és összetett adattípusok
> 3.3. PL/SQL kurzor, explicit kurzor, for update záradék
> PL/SQL FELADATOK-1.

SEGÉDANYAGOK: ORACLE PL/SQL:
Oracle® Database 11g Release 2 Documentation Library
- Oracle PL/SQL Language Reference 11gR2 HTML PDF
- Oracle PL/SQL Packages and Types Ref.11gR2 HTML PDF
- Az Oracle dokumentációban szereplő példák kipróbálásához:
>> Lekérdezésekhez: createHRsyn
>> Módosításhoz: createHRtables

További segédanyagok a PL/SQL-hez:
- Cinkóczki László, Oracle Junior, 2012. márc.8-i ea: PL/SQL
- Nikovits Tibor (ELTE) PL/SQL összefoglaló: NT_Plsql.htm
- Kósa Balázs (ELTE) PL/SQL összefoglaló: KB_Plsql.pdf
- Ullman/Chang (Standford) Using Oracle PL/SQL
- PL/SQL Technology Center (Oracle 11g PL/SQL)

3.1. PL/SQL alapok, változóhasználat, vezérlési szerkezetek

PL/SQL (névtelen) blokkok szerkezete
-- PL/SQL Language Reference 11gR2 >> 2.fej. Alapok
[1_deklarációs_rész] 2_végrehajtható_rész [3_kivételkezelő_rész]

Gyakorló feladatok az Oracle Példatárból
-- Lásd Feladatok.pdf (8.fejezet feladatai) -- Ehhez: cr_dept_emp.sql

Változóhasználat, az adatok képernyőre való kiíratása
1a_valtozok.sql - -- Oracle DBMS_OUTPUT csomag PUT_LINE eljárása
-- Változóhasználat: i.) az SQL*Plus felhasználói változói,
ii.) az SQL*Plus környezeti változói, iii.) a PL/SQL változói.
-- Írjunk egy PL/SQL blokkot tartalmazó SQL*Plus szkipt programot, amely
a felhasználótól bekér egy egész számot! Ha ez a szám nagyobb 100-nál,
akkor a PL/SQL blokkban, egyébként pedig a PL/SQL blokkot követő
gazdanyelvi (SQL*Plus) környezetben írassa ki

Amikor a lekérdezés egy sort ad vissza: SELECT INTO utasítás
1b_select_into.sql - A SELECT ... INTO utasítás a PL/SQL-ben
-- Határozzuk meg egy PL/SQL program segítségével a felhasználó által
megadott telephelyen dolgozók béröszegét. A telephelynek a várost (loc)
adjuk meg (mint például Boston, Chicaco, Dallas vagy New York).
-- Ennek a feladatnak további megoldásaira a következő szakaszban
visszatérünk (lásd 2a_select_into.sql és további megoldásait is).

Vezérlési szerkezetek a PL/SQL-ben
-- PL/SQL Language Reference 11gR2 >> 4.fej. Control Statements
-- A programozási nyelvekből ismert egyszerű példák
1c_if_then.sql - Feltételes utasítás (két egész szám összege páros-e)
1d_loop_ciklus.sql - LOOP ciklus (a Fibonacci-sorozat elemei)
1e_while_ciklus.sql - WHILE ciklus (az Euklideszi algoritmus)
1f_for_ciklus.sql - FOR ciklus (páratlan számok négyzetösszege)

További gyakorló feladatok: Lásd Példatár "8.fejezet.
Változóhasználat, vezérlési szerkezetek a PL/SQL-ben" feladatai

3.2. SELECT INTO, hivatkozási és összetett adattípusok
-- SELECT INTO csak akkor használható, ha a lekérdezés pontosan egy sort ad.
   Ha a lekérdezés több sorral tér vissza kurzort kell használni, lásd #3.3. Kurzor
-- Példák rekordok, kollekciók (tömbök) plsql_peldak.sql (Nikovits Tibor)
-- PL/SQL Language Reference 11gR2 >> 3.fej. PL/SQL Data Types
-- PL/SQL Language Reference 11gR2 >> 5.fej. Collections and Records

Gyakorló feladatok az Oracle Példatárból
-- Lásd Feladatok.pdf (9.fejezet feladatai) -- Ehhez: cr_dept_emp.sql

Hivatkozási adattípus, %TYPE, %ROWTYPE
2a_select_into.sql - A SELECT ... INTO utasítás
-- Határozzuk meg egy PL/SQL program segítségével a felhasználó által
megadott telephelyen dolgozók béröszegét. A telephelynek a várost (loc)
adjuk meg (mint például Boston, Chicaco, Dallas vagy New York).
-- Lásd a korábbi 1b_select_into feladat I.megoldását is, csak most
hivatkozási típus deklarálásával oldjuk meg a feladatot: II.megoldás.
Közvetlen összegzéssel: III.mo. Tagonkénti összesítéssel: IV.mo.
-- Valamint rejtett kurzorral való másféle megoldására még visszatérünk.

Rekord adattípus, gyűjtőtábla típus
2b_gyujtotabla.sql - Összetett típus, rekord, gyűjtőtábla
- Írjunk PL/SQL programot, amely meghatározza a 7698 azonosítójú
    dolgozó nevét gyűjtőtábla használatával.

További gyakorló feladatok: Lásd Példatár "9.fejezet.
Hivatkozási és összetett adattípusok" feladatai

3.3. Kurzor, explicit kurzor, for update záradék
-- Példák kurzor használatára: plsql_kurzor.sql (Nikovits Tibor)
-- PL/SQL Language Reference 11gR2 >> 6.fej. Static SQL/Cursors

A ZH-n a tűzfalon keresztül is elérhető és használható a dokumentáció:
http://medusa.inf.elte.hu/ tanszéki honlapon alul Oracle linken található
Online documentation for Oracle 11g R2 bal oldali menüből kiválasztva
a harmadik sorban Database Administration és itt a harmadik dobozban:
SQL, PL/SQL, and PL/SQL Packages

SQL Language Reference
SQL Language Quick Reference
PL/SQL Language Reference
PL/SQL Packages and Types Reference

HTML PDF
HTML PDF
HTML PDF
HTML PDF

Itt érdemes átnézni a PL/SQL Language Reference példáit, például
6.fejezet Static SQL/Cursors részben "FOR UPDATE"-re keresve
"Example 6-43" példa hasonló, mint az alábbi 3a_kurzor.sql feladat:
- Az Oracle dokumentációban szereplő példák kipróbálásához:
>> Lekérdezésekhez: createHRsyn
>> Módosításhoz: createHRtables

Gyakorló feladatok az Oracle Példatárból
-- Lásd Feladatok.pdf (9.fejezet feladatai) -- Ehhez: cr_dept_emp.sql

3a_kurzor.sql - Kurzor, kurzorattribútumok (%FOUND, %NOTFOUND)
- Növeljük meg a hivatalnokok (CLERK) fizetését a saját fizetésük 20%-ával!
(hibas_mo.sql -> tanulságok leszűrése!)

3b_impl_kurzor.sql - Implicit kurzor, kurzorhasználat FOR ciklusban
- Hozzuk létre a dolgozo táblát az emp táblából, és bővítsük azt egy sorszám
oszloppal. Ezt töltsük fel 1-től kiindulva egyesével növekvő értékkel minden
dolgozó esetén a dolgozók nevének ábécé sorrendje szerint.

3c_expl_kurzor.sql - Explicit kurzor
- Előző 3b feladat másik megoldása

3d_current_of.sql - Explicit kurzor, FOR UPDATE, CURRENT OF
- Előző 3b, 3c feladat harmadik megoldása

További gyakorló feladatok: Lásd Példatár "9.fejezet.
Hivatkozási és összetett adattípusok, kurzor, ROWID" feladatai

PL/SQL FELADATOK-1

-- Táblák és az eddigi sql feladatok: table_dolgozo.txt
-- Ehhez a táblák létrehozása: create_dolgozo.txt

1.) Hello World program (alap program, kiírás a képernyőre)
Írjon PL/SQL blokkot tartalmazó script programot, amely a felhasználótól
bekér egy egész számot és bekéri a nevét, majd a PL/SQL blokkból kiírja
"Szia <X.Y. (név)>, a kedvenc számod ez: <szám> " (mo: '&nev' és &szam)

2.) Írjuk ki KING fizetését (olvasás táblából változóba), abban az esetben,
ha ismert, hogy pontosan egy KING nevű dolgozó szerepel a táblában!

3.) Írjuk ki KING belépési dátumát és fizetését különböző formátumokban,
mint például 1981.11.17, 1981-november-17, stb (dátum formátumok)
(pontosan egy sorból álló több oszlopos lekérdezés)

4.) Tegyük be a dolgozók nevét egy plsql tömbbe, és írjuk ki az utolsó előtti sort!
(plsql tömb használata)
-- Segítségképpen: Tábla típus definiálása:
     TYPE tábla_típus_neve IS TABLE OF adattípus INDEX BY BINARY_INTEGER;
ahol az adattípus lehet %TYPE vagy %ROWTYPE-al megadva,
lehet előre definiált típus vagy általunk definiált rekordtípus.
Tábla deklarálása: tábla_név tábla_típus_neve;
   Hivatkozás a tábla elemeire: tábla_név(index)
PL/SQL tábla attribútumok, például: tábla_név.COUNT hány eleme van a táblának.

-- Feladatok kurzorhasználatra:
5.) Írjuk ki a dolgozók nevét és fizetését!
(több sort visszaadó lekérdezés, kurzor használata)

6.) Írjuk ki a 3. 5. és 8. legnagyobb fizetésű dolgozó nevét, fizetését!
(kurzor attribútumok)

7.) Írjuk ki a dolgozók nevét és fizetését mind a 3-féle kurzorhasználattal!

8.) Írjuk ki azon dolgozók nevét és fizetését, akik fizetése nagyobb mint
a felhasználó által megadott szám!

9.) Írjuk ki azon dolgozók nevét, fizetését és osztálykódját, akik a felhasználó
által megadott osztályon dolgoznak! A felhasználó által megadott betű
legyen A, R, S (Accounting ...) a 10, 20, 30-as osztály esetén.
   (paraméteres kurzor)

Fel a lap tetejére

Vissza az AB1gyak oldalára (főmenü)