Hajas Csilla Adatbazisok-I.gyakorlat

9.GYAKORLAT (ADATBÁZISOK)

9.-10.-11.gyakorlatok témaköre: Oracle PL/SQL
Az Oracle PL/SQL-hez az alábbi informatikai alapismeretekre van szükség:
- a szabvány SQL és az Oracle SQL ismerete, sqldeveloper használata,
- az eljárásorientált és az objektumorientált programozási nyelvek alap-
eszközeinek fogalmi szintű ismerete,
- programozási gyakorlat (például C, C++ vagy Java) programozási nyelven.

Áttekintés - PL/SQL gyakorlatok témakörei:
--- 09.gyakorlat (Oracle DB PL/SQL Lang.Ref. 1-5.fej)
1.1. PROGRAMEGYSÉGEK: BLOKK, [TÁROLT] ALPROGRAMOK
1.2. PROGRAMOK TESZTELÉSE, ELJÁRÁSHÍVÁS, FÜGGVÉNYHÍVÁS
I/O: DBMS_OUTPUT CSOMAG PUT_LINE ELJÁRÁS HASZNÁLATA
DBMS_RANDOM CSOMAG VALUE FÜGGVÉNY HASZNÁLATA
1.3. BLOKK-1 DEKLARÁCIÓS RÉSZ-1: VÁLTOZÓK DEKLARÁLÁSA
   BLOKK-2 VÉGREHAJTHATÓ RÉSZ-1: ÉRTÉKADÁS, SELECT INTO
1.4. BLOKK-1 DEKLARÁCIÓS RÉSZ-2: ALPROGRAMOK DEKLARÁLÁSA
   BLOKK-2 VÉGREHAJTHATÓ RÉSZ-2: VEZÉRLŐ UTASÍTÁSOK
1.5. KÜLSŐ ÉS BELSŐ VÁLTOZÓK HASZNÁLATA

--- 10.gyakorlat (Oracle DB PL/SQL Lang.Ref. 6.fej )
2.1. VÉGREHAJTHATÓ RÉSZ-3: SQL DML ÉS TRANZ.KEZ. UTASÍTÁSOK
2.2. KURZOROK ÁTEKINTÉSE, IMPLICIT ÉS EXPLICIT KURZOROK
2.3. A LEKÉRDEZÉS EREDMÉNYHALMAZAINAK FELDOLGOZÁSA
2.4. KURZOR-VÁLTOZÓK, KURZOR-ATTRIBÚTUMOK, KIFEJEZÉSEK
2.5. FOR UPDATE MÓDOSÍTHATÓ KURZOROK

  --- 11.gyakorlat (Oracle DB PL/SQL Lang.Ref. 8-11.fej)
3.1. BLOKK-3 HIBA- ÉS KIVÉTELKEZELŐ RÉSZ
3.2. TÁROLT ALPROGRAMOK
3.3. CSOMAGOK
3.4. TRIGGEREK
3.5. A REKURZÍV ELJUT FELADAT PL/SQL PROGRAMMAL

- PL/SQL elsajátításához segédletek:
Az egyetlen megengedett segédlet, amit a ZH írás alatt használhatunk:
   PL/SQL doksi: Oracle Database Rel.12.2 - PL/SQL Language Reference
>> a doksiból nézünk példákat: List of Examples (PL/SQL Lang.Ref.12.2)

- További segédanyagok a PL/SQL feldolgozásához:
   >> SQL/PSM, PL/SQL: 08.adatb.ea.pdf (korábbi BSc-2008 AB1EA/HajasCs.)
   >> Oracle PL/SQL: Oracle Junior EA >> Oracle PL/SQL.pdf
   >> Oracle PL/SQL segédanyagok: eduPLSQL mappában => innen lásd például:
   >> plsql_progr.pdf -- Forrás Tankönyvtár: PL/SQL programozás Oracle-ben

1.1. PROGRAMEGYSÉGEK: BLOKK, [TÁROLT] ALPROGRAMOK
- blokk, alprogram (eljárás, függvény)
- tárolt eljárás, tárolt függvény
- csomagok
-- -- -- -- -- --
-- PL/SQL blokk felépítése
[<< label >> -- (címke, opcionális) ]
[DECLARE    -- (BLOKK-1 Deklarációs rész, opcionális)
-- változók, alprogramok deklarálása ]

BEGIN      -- (BLOKK-2 Végrehajtható rész, kötelező)
-- végrehajtható utasítások, például van NULL;

[EXCEPTION -- (BLOKK-3 Kivételkezelő rész, opcionális
-- hiba- és kivételkezelés (lásd később 11.gyak) ]
END;
/

-- Blokk típusok
a.) Névtelen blokk
[DECLARE]
BEGIN
-- végrehajtható utasítások;
[EXCEPTION]
END;

b.) Procedure/Eljárás
[CREATE OR REPLACE] PROCEDURE név
IS
BEGIN
-- végrehajtható utasítások;
[EXCEPTION]
END;

c.) Function/Függvény
[CREATE OR REPLACE] FUNCTION név
RETURN adattípus
IS
BEGIN
-- végrehajtható utasítások;
RETURN érték;
[EXCEPTION]
END;

-- -- -- -- -- -- részletek később lesz a 11.gyakorlaton
PL/SQL eljárások és függvények: 8.fej. PL/SQL Alprogramok
>> 8.1. Alprogramokról áttekintés: Miért van szükség alprogramokra, előnyök
-- Példák procedurákra/függvényekre: pl-proc-fv.txt további példák később...
>> példa2-17 Változók hatásköre, láthatósága, élettartalma (2.fej. Alapok)
>> példa4-1 Megnézzük az IF-THEN utasításnál az eljárást (4.fej. Utasítások)
>> 8.5.fej. Példák példa8-1 PL/SQL eljárásra és példa8-2 PL/SQL függvényre
>> 8.7.fej. Paraméterátadás; 8.9.fej.Túlterhelés példa8-26 túlterhelésre példa

1.2. PROGRAMOK TESZTELÉSE, ELJÁRÁSHÍVÁS, FÜGGVÉNYHÍVÁS

a.) ELJÁRÁSHÍVÁS:
   I/O: DBMS_OUTPUT CSOMAG PUT_LINE ELJÁRÁS HASZNÁLATA

- Input/Output PL/SQL-ben nincs I/O utasítás, általában a be- és kimenet (I/O)
SQL utasításokkal történik, az adatokat az adatbázis tábláiban tároljuk, és
ott módosítjuk vagy onnan lekérdezzük ezeket a táblákat.
- Minden más PL/SQL I/O pl. képernyőre való kíiírás az Oracle Database által
biztosított PL/SQL csomagokkal történik.
- DBMS_OUTPUT csomag link: DBMS_OUTPUT.html (PL/SQL Packages)
DBMS_OUTPUT.PUT_LINE (item IN VARCHAR2) eljárásra példa:

-- Tesztelés:
SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE ('Szia Világ!');
END;
/

- PL/SQL feladat: Írjuk át a fenti programot: Szia ....! (nevet bekérni) ehhez:
- Ismétlés: Felhasználói változó, Példatár 4.fej.pdf (Interaktív környezet)
>> innen felhasználói (helyettesítő változók használata SQL utasításokban
volt 7.gyak. INSERT 2.feladatát, de most bekérjük az új sorhoz az értékeket,
   &dkod (numerikus), '&dnev' (karaktersorozat) típusú helyettesítő változókba.

b.) FÜGGVÉNYHÍVÁS:
DBMS_RANDOM CSOMAG VALUE FÜGGVÉNY HASZNÁLATA

- DBMS_RANDOM csomag link: DBMS_RANDOM.html (PL/SQL Packages)
   DBMS_RANDOM.VALUE(AlsóHatár, FelsőHatár) függvény használata.

-- Tesztelés:
SELECT DBMS_RANDOM.VALUE(0, 10000) from dual;

- SQL DDL feladat: 8.gyak. Készítsünk egy dolg3 nevű nézettáblát
a dolgozo tábla (dkod, dnev, fizetes, jutalek) oszlopaihoz vegyünk fel
egy új nyeremény nevű oszlopot, amit úgy töltsünk fel adatokkal, hogy
0 és 10000 közötti véletlen egész számot százasokra kerekítve adjunk
hozzá a jutalékhoz, ha a jutalék ismeretlen, akkor tekintsük 0 értéknek.

1.3. BLOKK-1 DEKLARÁCIÓS RÉSZ-1: VÁLTOZÓK DEKLARÁLÁSA
   BLOKK-2 VÉGREHAJTHATÓ RÉSZ-1: ÉRTÉKADÁS, SELECT INTO

   PL/SQL alapok, PL/SQL blokk >> 2.fej. PL/SQL alapok -> 2.6.rész
[1_deklarációs_rész] 2_végrehajtható_rész [3_kivételkezelő_rész]
SET SERVEROUTPUT ON;
>> példa-2-24 deklarációk-és-értékadás, típusok, %TYPE, %ROWTYPE
-- Az itt szereplő példák kipróbálásához hozzunk létre szinonimákat:
create or replace synonym departments for hr.departments;
create or replace synonym employees for hr.employees;
-- Egészítsük ki: például legyen DATE típusú változó is, illetve
nézzünk %TYPE és %ROWTYPE használatára további példákat!
[ opcionális, részletek: 3.fej. PL/SQL adattípusok, [ 5.fej. Kollekciók ]]

>> példa-2-25 Értékadás változónak a SELECT INTO utasítással
-- Itt a 2-25 példa futtatásához át kell tenni az end;-et a legvégére!
SELECT INTO-t akkor használjuk, ha a lekérdezés pontosan egy sort
ad! Ha a lekérdezés nem ad vissza sort vagy több sorral tér vissza,
akkor kurzort kell használni, ez lesz a köv.10.gyakorlat tananyaga,
   illetve a 11.gyakorlaton a hiba- és kivételkezelésnél is visszatérünk!

1.4. BLOKK-1 DEKLARÁCIÓS RÉSZ-2: ALPROGRAMOK DEKLARÁLÁSA
   BLOKK-2 VÉGREHAJTHATÓ RÉSZ-2: VEZÉRLŐ UTASÍTÁSOK

   Feltételes utasítások, ciklusok >> 4.fej. PL/SQL vezérlő utasítások
   >> példa-4-4 if-then-elsif utasítás
!!! Ebben a példában figyeljük meg a deklarációs részben az eljárást,
írjuk át az itt szereplő eljárást CREATE OR REPLACE PROCEDURE-re!
   >> példa-4-6 egyszerű case utasítás, és itt írjuk át grade := 'B' -> '&B'
   írjuk át helyettesítési változóra: a felhasználó adja meg az értékét!
   >> példa-4-10 alap LOOP ciklus utasítás EXIT WHEN kilépés a ciklusból
   >> példa-4-15 FOR ciklus utasítás
>> példa-4-28 WHILE ciklus utasítás

1.5. KÜLSŐ ÉS BELSŐ VÁLTOZÓK HASZNÁLATA

  Változóhasználat, vezérlési szerkezetek a PL/SQL-ben: Példatár 8.fej.pdf
  >> innen egy példa: 1a_valtozok_pelda_Oracle_Peldatar_8fej.txt

   PL/SQL FELADATSOR --1
-- A DBMS_OUTPUT.PUT_LINE() képernyőre való kiíráshoz állítsuk be:
SET SERVEROUTPUT ON

-- -- -- --
-- 1.)
/* Az első feladat képernyőre kiírás: Írjuk ki PL/SQL blokkból: 'Szia Világ!'
*/

-- -- -- --
-- 2.)
/* Írjuk ki KING fizetését és jutalékát (olvasás táblából változókba), ha ismert,
  hogy pontosan egy KING nevű dolgozó szerepel a táblában (SELECT INTO).
SELECT INTO csak akkor működik, ha a lekérdezés pontosan egy sort ad,
erre a feladatra visszatérünk a 11.gyakorlaton a hiba- és kivételkezelésnél.
*/

-- -- -- --
-- 3.)
/* Írjunk meg egy függvényt, amelyik eldönti egy számról, hogy prím-e.
igen/nem -> 1/0 értékeket adja vissza a függvény
*/

CREATE OR REPLACE FUNCTION prim(n integer) RETURN number IS

-- Tesztelés:
SELECT prim(101) from dual;
SELECT prim(108) from dual;
SELECT prim(26388279066623) from dual;

-- -- -- --
-- 4.)
/* Írjunk meg egy procedúrát, amelyik kiírja az n-edik Fibonacchi számot
   fib_1 = 0, fib_2 = 1, fib_3 = 1, fib_4 = 2, . . .
fib_i = a megelőző kettő összege
*/

CREATE OR REPLACE PROCEDURE fib(n integer) IS

-- Tesztelés:
SET SERVEROUTPUT ON
BEGIN
fib(10);
END;
/
-- vagy lehet így is
set serveroutput on
execute fib(10);

-- -- -- --
-- 5.)
/* Írjunk meg egy függvényt, amelyik visszaadja két szám legnagyobb közös osztóját
*/

CREATE OR REPLACE FUNCTION lnko(p1 integer, p2 integer) RETURN number IS

-- Tesztelés:
SELECT lnko(3570,7293) FROM dual;

-- -- -- --
-- 6.)
/* Írjunk meg egy függvényt, amelyik visszaadja n faktoriálisát
*/

CREATE OR REPLACE FUNCTION faktor(n integer) RETURN integer IS

-- Tesztelés:
SELECT faktor(10) FROM dual;

-- -- -- --
-- 7.)
/* Írjunk meg egy függvényt, amelyik megadja, hogy hányszor fordul elő
   egy karakterláncban -> p1 egy másik részkarakterlánc -> p2
*/

CREATE OR REPLACE FUNCTION hanyszor(p1 VARCHAR2, p2 VARCHAR2) RETURN integer IS

-- Tesztelés:
SELECT hanyszor ('ab c ab ab de ab fg', 'ab') FROM dual;

-- -- -- --
-- 8.)
/* Írjunk meg egy függvényt, amelyik visszaadja a paraméterként szereplő '+'-szal
   elválasztott számok összegét.
*/

CREATE OR REPLACE FUNCTION osszeg(p_char VARCHAR2) RETURN number IS

-- Tesztelés:
SELECT osszeg('1 + 4 + 13 + -1 + 0') FROM dual;

GYAKORLÁS:
Fejezze be önállóan a fenti feladatokból a hiányzó feladatokat, és gyakoroljon
oldja meg önállóan az Oracle Példatár Feladatok.pdf 8.fejezet 8.1-8.9.feladatait!
  [Megjegyzés: Az Oracle Példatár ezeket a táblákat használja: cr_dept_emp.txt
   az emp és dept táblák helyett használjuk itt is a dolgozo, osztaly tábláinkat!]