PL/SQL 1. rész Procedural Language extension to SQL
Szintaxis • utasítást ; zárja le • PL/SQL blokk lezárása: / • kis- és nagybetű egyenértékű (az utasításokban a kulcsszavakat szoktuk nagybetűvel írni, de nem kötelező) • megjegyzés: REM vagy --, többsoros /* */ • használat előtt deklarálni kell a változókat, azonosítókat, eljárásokat • ** hatványozás, != nem egyenlő, || karakterlánc összefűzés • egy PL/SQL program egy vagy több blokkból áll, a blokkok egymásba ágyazhatók
Blokk felépítése [blokk név] [DECLARE [lokális változók] ] BEGIN [utasítás blokk] [EXCEPTION [kivételkezelés] ] END [blokk név]; /
Megjegyzés: • A törtvonal akkor kell, ha több utasítást, blokkot szeretnénk egy szkriptbe írni
.
• A törtvonalat lehet -al helyettesíteni
Blokk jellemzői • Egy PL/SQL program egy vagy több blokkból is állhat • A blokkok egymásba ágyazhatók • Blokk lehet: • anonymus blokk (névtelen) – nem tárolódik le az adatbázisban • névvel ellátott • alprogram (paraméterezhetőek): • függvény (FUNCTION) - van visszatérési értéke • eljárás (PROCEDURE)
• PL/SQL blokk tartalmazhat: SQL parancsfájl, eljárás vagy függvény, befogadó nyelvi program, trigger
Példa VAR X NUMBER DECLARE a NUMBER; BEGIN a := 3; :X := a + 3; END; . PRINT X
ACCEPT szoveg VARCHAR(30) PROMPT 'Adj meg valami szöveget'; DECLARE a VARCHAR(60); BEGIN a := &szoveg; dbms_output.put_line(a); END; /
DECLARE szegmens
• PL/SQL adattípusok:
• Egyszerű adatok: NUMBER, VARCHAR2…, CLOB, BLOB • Új típusok PL-SQL-ben: • pls_integer = binary_integer • pls_float = binary_float • pls_double = binary_double
• Összetett típusok: rekord, tábla, tömb • Pointer (= mutató): hivatkozási típus
• Nem PL/SQL adattípusok: • SQL*Plus változók (helyettesítő, hozzárendelt) – PL/SQL blokkon kívül a deklaráció • helyettesítő váltózóra hivatkozás: &a • hozzárendelt változóra hivatkozás: :a
Deklaráció szintaxisa • változónév [CONSTANT] adattípus [NOT NULL] [DEFAULT érték] • CONSTANT és NOT NULL esetén a kezdőértékadás kötelező • DEFAULT helyett ":=" is írható. • Példák: szoveg VARCHAR2(50); szam NUMBER DEFAULT 10; szam NUMBER := 10; egy CONSTANT NUMBER := 1;
Példa DECLARE a CONSTANT NUMBER := 3; b NUMBER NOT NULL := 5; BEGIN :X := :X + a + b + 3; END; / PRINT X
DBMS_OUTPUT (PL/SQL csomag) • Kiíratni a DBMS_OUTPUT csomag segítségével lehet • Használatát a SET SERVEROUTPUT ON SQL*Plus paranccsal engedélyezni kell • Föbb kiíratási függvényei: • PUT: ugyanabba a sorba ír több outputot • NEW_LINE: sor végét jelzi • PUT_LINE: minden outputot külön sorba ír
• Egy bufferbe íródik először a kiírandó szöveg. Ha sikeresen lefutott a program, akkor a buffer tartalma a képernyőre íródik.
Példa SET SERVEROUTPUT ON ACCEPT nev PROMPT 'Kerem adja meg a nevét: ' DECLARE szoveg varchar2(50); BEGIN szoveg := CONCAT('&nev',' sikeresen végrehajtotta a programot!'); DBMS_OUTPUT.PUT_LINE (szoveg); END;
Változó értékadás a SELECT... INTO... utasítással • SELECT utasítás PL/SQL blokkon belüli használatakor az INTO alparanccsal változó(k)hoz rendelünk értéket • Csak akkor fut le helyesen, ha pontosan egy értéket ad vissza DECLARE CREATE TABLE T1( e INTEGER, f INTEGER ); INSERT INTO T1 VALUES(1, 3); INSERT INTO T1 VALUES(2, 4);
a NUMBER; b NUMBER; BEGIN SELECT e,f INTO a,b FROM T1 WHERE e>1; INSERT INTO T1 VALUES(b,a); END;
Rekordtípus • Rekord deklarálása: • TYPE rekordtípusnév IS RECORD (mezőnév típus,..., mezőnév típus);
• Rekord típusú váltózó létrehozása: • változónév rekordtípusnév;
• Hivatkozás a komponensekre: • változónév.mezőnév
Változó deklarálás adattábla típusokból • Tábla egy mezőjének típusára hivatkozás: • változónév tábla.mező%TYPE;
• Rekord típusú változó létrehozása (sor típusú változó): • változónév tábla%ROWTYPE;
• Hivatkozás egy oszlopára: • változónév.oszlop;
Példa Rekord használatára DECLARE TYPE dolgozo_rekord IS RECORD (adoszam INTEGER, nev CHAR(30), lakcim VARCHAR(50)); egy_dolgozo dolgozo_rekord; BEGIN egy_dolgozo.nev = 'Kovacs'; END;
Példa TYPE használatára DECLARE fizetes emp.sal%TYPE; BEGIN SELECT avg(sal) INTO fizetes FROM emp; DBMS_OUTPUT.PUT_LINE('Átlagos fizetés: ' || fizetes); END;
Példa ROWTYPE használatára DECLARE v_sor emp%ROWTYPE; BEGIN SELECT * INTO v_sor FROM emp WHERE ename LIKE 'WARD'; DBMS_OUTPUT.PUT_LINE(v_sor.ename || ' fizetése: ' || v_sor.sal); END;
Vezérlési szerkezetek IF IF feltétel THEN utasítások [ELSIF feltétel THEN utasítások] … [ELSE utasítások] END IF;
Példa DECLARE v_avgber emp.sal%TYPE; szoveg VARCHAR2(50); BEGIN SELECT AVG(sal) INTO v_avgber FROM emp; IF v_avgber < 2000 THEN szoveg:='kevesebb mint ketezer'; ELSIF (v_avgber > 2000) AND (v_avgber <= 10000) THEN szoveg:='ketezer es tizezer között'; ELSE szoveg:='tizezer folott'; END IF; DBMS_OUTPUT.PUT_LINE(szoveg); END;
CASE • CASE valtozo, WHEN ertek THEN, ..., ELSE, END CASE; • Példa: CASE num WHEN 1 THEN dbms_output.put_line('Egy'); ... WHEN 9 THEN dbms_output.put_line('Kilenc'); ELSE dbms_output.put_line('Nem egyjegyu'); END CASE;
LOOP Ciklusutasítások • Végtelen ciklus kilépési ponttal: LOOP utasítások [EXIT;] [EXIT WHEN feltétel;] END LOOP;
Példa DECLARE v_sorsz dept.deptno%TYPE := 10; v_megnev dept.dname%TYPE; BEGIN LOOP SELECT dname INTO v_megnev FROM dept WHERE deptno = v_sorsz; DBMS_OUTPUT.PUT_LINE(v_megnev); v_sorsz := v_sorsz +10; EXIT WHEN v_sorsz > 30; END LOOP; END;
WHILE cilkus • WHILE feltétel, LOOP, END LOOP; DECLARE v_sorsz dept.deptno%TYPE := 10; v_megnev dept.dname%TYPE; BEGIN WHILE v_sorsz < 40 LOOP SELECT dname INTO v_megnev FROM dept WHERE deptno = v_sorsz; DBMS_OUTPUT.PUT_LINE(v_megnev); v_sorsz := v_sorsz +10; END LOOP; END;
FOR ciklus • FOR ciklusváltozó IN [REVERSE] alsóhatár .. felsohatár LOOP, END LOOP; BEGIN FOR num IN 1..10 LOOP DBMS_OUTPUT.PUT_LINE(num ||'^2='||num*num); END LOOP; END;
Gyűjtőtáblák kezelése • részben listaszerűen, részben tömbszerűen kezelhető adatstruktúra • oszlopai: adat (oszlop vagy rekord típusú lehet), index (BINARY INTEGER) • mérete nem korlátozott • tömbként indexelhető, ahol az index NEGATÍV is lehet: adat(index) • új sorokkal bővíthető és törölhető --> hézagok lehetnek benne • létrehozás: TYPE táblatípusnév IS TABLE OF {oszloptípus | rekordtípus} INDEX BY BINARY_INTEGER • az INSERT, UPDATE, FETCH, SELECT utasításokkal kezelhető
Gyűjtőtábla deklarálása • Létrehozás: TYPE táblatípusnév IS TABLE OF {oszloptípus | rekordtípus} INDEX BY BINARY_INTEGER • Tábla típusú változó deklarálása: • változónév táblatípusnév;
• Összes rekord egy gyűjtőtáblába: • SELECT * bulk collect INTO valtozonev FROM dept;
• az INSERT, UPDATE, FETCH, SELECT utasításokkal kezelhető, de soraira változónév(index) módon is hivatkozhatunk. Ha nem létező indexre hivatkozunk, NO_DATA_FOUND kivétel keletkezik.
Gyűjtőtábla metódusok • • • • • •
EXISTS(n) - igaz, ha létezik az n-edik elem COUNT - táblában lévő elemek száma FIRST - tábla első indexértéke LAST - tábla utolsó indexértéke NEXT - a táblában a következő index értéke DELETE(n) - az n-edik elem törlése
Példa DECLARE TYPE tipus IS TABLE OF dept% ROWTYPE INDEX BY BINARY_INTEGER; valtozo tipus; ind BINARY_INTEGER := 1; BEGIN LOOP SELECT * INTO valtozo(ind) FROM dept WHERE deptno = ind * 10; ind := ind + 1; EXIT WHEN ind > 4; END LOOP; ind := valtozo.FIRST; LOOP DBMS_OUTPUT.PUT_LINE( valtozo(ind).dname); ind := ind + 1; EXIT WHEN ind > valtozo.LAST; END LOOP; END;
Kivételkezelés [blokk név] [DECLARE [lokális változók] ] BEGIN [utasítás blokk] [EXCEPTION [kivételkezelés] ] END [blokk név]; / • Kivételek típusai:
EXCEPTION WHEN kivétel [OR kivétel ...] THEN utasítások [WHEN kivétel [OR kivétel ...] THEN utasítások] ... [WHEN OTHERS THEN utasítások]
• Rendszer által definiált • Felhasználó által definiált
• A WHEN OTHERS rész a fel nem sorolt kivételek elfogására szolgál
Rendszerbeli kivételek • NO_DATA_FOUND: SELECT utasitas nem ad vissza sort • TOO_MANY_ROWS: egy sort kellett volna visszaadnia egy SELECT-nek, de többet kaptunk • INVALID_NUMBER: karakterlánc sikertelen számmá konvertálása • DUP_VAL_ON_INDEX: kulcsfeltétel megsértése
Példa DECLARE v_ber emp.sal%TYPE; v_nev emp.ename%TYPE; BEGIN v_ber := 3000; SELECT ename INTO v_nev FROM emp WHERE sal = v_ber; DBMS_OUTPUT.PUT_LINE(v_nev); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Nincs ilyen fizetés'); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('Több embernek is ez a fizetése'); WHEN OTHERS THEN DBMS_OUTPUT. PUT_LINE('Egyéb hiba'); END;
Felhasználói kivételek • a DECLARE szakaszban: • kivételnév EXCEPTION;
• végrehajtható szegmensben kivétel dobása: • RAISE kivételnév;
Felhasználói kivételek DECLARE nincs_vevo EXCEPTION; BEGIN DBMS_OUTPUT.PUT_LINE('Ez vegrehajtodik'); RAISE nincs_vevo; DBMS_OUTPUT.PUT_LINE('Ez nem hajtodik vegre'); EXCEPTION WHEN nincs_vevo THEN DBMS_OUTPUT.PUT_LINE('Nincs ilyen vevo'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Egyéb hiba'); END;
Feladatok 1. Kérj be két egész számot, és döntsd el, hogy az összegük páros vagy páratlan! 2. Adj egy programot, ami kiírja az EMP tábla sorainak számát, és a tárolt dolgozók átlagfizetését. 3. Adj meg egy programot, ami létrehozza a FIBON(n,ertek) táblát, és feltölti azt n=100-ig a Fibonacci számokkal
1. Feladatok megoldása SET SERVEROUTPUT ON; ACCEPT elso PROMT 'elso:'; ACCEPT masodik PROMT 'masodik:'; DECLARE osszeg NUMBER; BEGIN osszeg:=&elso+&masodik; IF MOD(osszeg,2)=0 THEN DBMS_OUTPUT.PUT_LINE('Paros'); ELSE DBMS_OUTPUT.PUT_LINE('Paratlan'); END IF; END;
2. Feladatok megoldása SET SERVEROUTPUT ON DECLARE sorok NUMBER; atlag_fiz NUMBER; BEGIN SELECT COUNT(*), AVG(sal) INTO sorok, atlag_fiz FROM EMP; DBMS_OUTPUT.PUT_LINE('Sorok szama: ' || sorok); DBMS_OUTPUT.PUT_LINE('Atlag fizetes: ' || atlag_fiz); END;
3. Feladatok megoldása DROP TABLE FIBON; CREATE TABLE FIBON ( n NUMBER(5) PRIMARY KEY, ertek NUMBER(28) ); INSERT INTO FIBON VALUES(0, 0); INSERT INTO FIBON VALUES(1, 1); DECLARE f_szam FIBON.ertek%TYPE; f_1 FIBON.ertek%TYPE; f_2 FIBON.ertek%TYPE; BEGIN FOR i IN 2..100 LOOP SELECT ertek INTO f_1 FROM FIBON WHERE n=i-1; SELECT ertek INTO f_2 FROM FIBON WHERE n=i-2; INSERT INTO FIBON VALUES (i, f_1+f_2); END LOOP; END;
