2012. április 27. TARTALOM

KENDE MÁRIA - NAGY ISTVÁN

PL/SQL FELADATGYŰJTEMÉNY

TARTALOM 9. Foglalkozás Hivatkozási és összetett adattípusok, kurzor, ROWID ............................................... 2 10. Foglalkozás Összefoglaló feladatok ................................................................................................ 15 Melléklet Dinamikus SQL .......................................................................................................... 35 Irodalom .......................................................................................................................... 37

2012. április 27. #13_PL-SQL Feladatgyűjtemény (07)

-1-

9. Foglalkozás

Hivatkozási és összetett adattípusok, kurzor, ROWID Részletesebben lásd [2]: 9. fejezet,

FELADATGYŰJTEMÉNY

9.1. Feladat Írjon egy olyan PL/SQL programot, amely az emp táblából létrehoz egy dolgozó táblát, és minden dolgozó fizetését megemeli a saját fizetésének véletlenszám (0-100) értékű százalékával. 9.2. Feladat Írjon PL/SQL programot, amely az emp táblából létrehoz egy dolgozó táblát, és megnöveli a felhasználó által megadott azonosítójú főnök minden beosztottjának fizetését azok átlagfizetésének 10%-val. 9.3. Feladat Írjon PL/SQL programot, mely az emp táblából létrehoz egy dolgozó táblát, és megnöveli a felhasználó által megadott nevű főnök minden beosztottjának fizetését azok átlagfizetésének 10%-val. 9.4. Feladat Írjon egy PL/SQL szkript programot az alábbi feladatok megoldására: Hozzon létre egy dolgozó nevű táblát az emp táblának a felhasználó által megadott telephelyű (loc) dolgozóiból, változtassa meg az mgr oszlop nevét partner névre, törölje a tartalmát, és töltse fel az alábbi módon értékkel: Két dolgozó partnere lehet egymásnak, ha még egyiküknek sincs partnere, és a felhasználó által megadott értéknél nem több a fizetésük különbsége. A partnerek azonosítói szerepeljenek egymás partner oszlopában. (Senki önmagának nem lehet partnere, és a partnerség szimmetrikus tulajdonság, azaz ha A partnere B, akkor B partnere A.) Ellenőrizze a megoldást új dolgozó felvitelével, és a partnerkeresés megismétlésével. TOVÁBBI FELADATOK T9.5. Feladat Írjon egy olyan PL/SQL programot, amely az emp táblából létrehoz egy dolgozó táblát és bővíti egy nyeremény oszloppal. A nyeremény a beosztottak számára a főnökök átlagfizetésének véletlenszerű százaléka (0-50), a főnököknek pedig a beosztottak összfizetésének véletlenszerű százaléka (10-50). Listázza a dolgozó táblát a nyereményhúzás előtt és után is. T9.6. Feladat Írjon egy olyan PL/SQL programot, amely az emp táblából létrehoz egy dolgozó táblát és bővíti egy pár nevű oszloppal. Ezt az oszlopot töltse fel az ’oooo’ karaktersorozattal. Egy kereső dolgozó partnere az, aki ugyanabban a munkakörben dolgozik és páros hónapban lépett be. Írja be kereső pár oszlopába a megtalált partner nevét, illetve a partner pár oszlopába a kereső nevét. Egy partner csak egyszer fordulhat elő.

#13_PL-SQL Feladatgyűjtemény (07)

-2-

MEGOLDÁSGYŰJTEMÉNY

9.1. Feladat Írjon egy olyan PL/SQL programot, amely az emp táblából létrehoz egy dolgozó táblát, és minden dolgozó fizetését megemeli a saját fizetésének véletlenszám (0-100) értékű százalékával. Megoldás 1. A tábla létrehozása SET numwidth 5 DROP TABLE dolgozó; CREATE TABLE dolgozó AS SELECT * FROM emp; SELECT * FROM dolgozó; SET serveroutput ON DECLARE szorzo NUMBER; CURSOR dolgozo IS SELECT empno,sal FROM dolgozó; BEGIN FOR i IN dolgozo LOOP szorzo := ROUND(DBMS_RANDOM.VALUE(0,100)/100,2); DBMS_OUTPUT.PUT_LINE('A szorzó értéke: '||szorzo||' 'emelés értéke:'|| (i.sal * szorzo)); UPDATE dolgozó SET sal = sal + (sal * szorzo) WHERE empno = i.empno; END LOOP; END; / SELECT * FROM dolgozó; SET numwidth 10

Eredmény A tábla eldobva. A tábla létrejött. EMPNO ----7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900

ENAME ---------SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES

JOB MGR HIREDATE SAL COMM DEPTNO --------- ----- --------- ----- ----- -----CLERK 7902 80-DEC-17 800 20 SALESMAN 7698 81-FEB-20 1600 300 30 SALESMAN 7698 81-FEB-22 1250 500 30 MANAGER 7839 81-ÁPR-02 2975 20 SALESMAN 7698 81-SZE-28 1250 1400 30 MANAGER 7839 81-MÁJ-01 2850 30 MANAGER 7839 81-JÚN-09 2450 10 ANALYST 7566 87-ÁPR-19 3000 20 PRESIDENT 81-NOV-17 5000 10 SALESMAN 7698 81-SZE-08 1500 0 30 CLERK 7788 87-MÁJ-23 1100 20 CLERK 7698 81-DEC-03 950 30


-3-

'||

7902 FORD 7934 MILLER

ANALYST CLERK

7566 81-DEC-03 7782 82-JAN-23

3000 1300

20 10

14 sor kijelölve. A A A A A A A A A A A A A A

szorzó szorzó szorzó szorzó szorzó szorzó szorzó szorzó szorzó szorzó szorzó szorzó szorzó szorzó

értéke: értéke: értéke: értéke: értéke: értéke: értéke: értéke: értéke: értéke: értéke: értéke: értéke: értéke:

,71 ,28 ,71 ,34 ,12 ,99 ,66 ,65 ,54 ,95 ,7 ,48 ,76 ,37

emelés értéke:568 emelés értéke:448 emelés értéke:887,5 emelés értéke:1011,5 emelés értéke:150 emelés értéke:2821,5 emelés értéke:1617 emelés értéke:1950 emelés értéke:2700 emelés értéke:1425 emelés értéke:1783,6 emelés értéke:456 emelés értéke:2280 emelés értéke:481

A PL/SQL eljárás sikeresen befejeződött. EMPNO ----7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934

ENAME ---------SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER

JOB MGR HIREDATE SAL COMM DEPTNO --------- ----- ---------- ----- ----- -----CLERK 7902 1980-12-17 1368 20 SALESMAN 7698 1981-02-20 2048 300 30 SALESMAN 7698 1981-02-22 2138 500 30 MANAGER 7839 1981-04-02 3987 20 SALESMAN 7698 1981-09-28 1400 1400 30 MANAGER 7839 1981-05-01 5672 30 MANAGER 7839 1981-06-09 4067 10 ANALYST 7566 1987-04-19 4950 20 PRESIDENT 1981-11-17 7700 10 SALESMAN 7698 1981-09-08 2925 0 30 CLERK 7788 1987-05-23 4332 20 CLERK 7698 1981-12-03 1406 30 ANALYST 7566 1981-12-03 5280 20 CLERK 7782 1982-01-23 1781 10

14 sor kijelölve.

9.2. Feladat Írjon PL/SQL programot, amely az emp táblából létrehoz egy dolgozó táblát, és megnöveli a felhasználó által megadott azonosítójú főnök minden beosztottjának fizetését azok átlagfizetésének 10%-val. Megoldás SET numwidth 5 SET serveroutput ON ACCEPT azonositó PROMPT 'Kérem a főnök azonosítóját: ' DROP TABLE dolgozó; CREATE TABLE dolgozó AS SELECT * FROM emp; SELECT * FROM dolgozó; CREATE OR REPLACE VIEW megadott(dempno,dname,djob,dmgr,dhiredate, dsal,dcomm,ddeptno, átlag) AS SELECT dolgozó.*, átlag FROM dolgozó, (SELECT ROUND(AVG(sal)) AS átlag, mgr AS azon FROM dolgozó GROUP BY mgr HAVING mgr = &azonositó) al #13_PL-SQL Feladatgyűjtemény (07)

-4-

WHERE al.azon = dolgozó.mgr; SELECT * FROM megadott; BEGIN UPDATE dolgozó SET sal = sal + 0.1 * (SELECT DISTINCT átlag FROM megadott) WHERE mgr = (SELECT DISTINCT dmgr FROM megadott); END; / SELECT * FROM dolgozó; SET numwidth 10

Eredmény Kérem a főnök azonosítóját: 7698 A tábla eldobva. A tábla létrejött. EMPNO ----7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934



14 sor kijelölve. régi 9: új 9:

HAVING mgr = &azonositó) al HAVING mgr = 7698) al

A nézet létrejött. DEMPNO -----7499 7521 7654 7844 7900

DNAME ---------ALLEN WARD MARTIN TURNER JAMES

DJOB DMGR DHIREDATE DSAL DCOMM DDEPTNO ÁTLAG --------- ----- ---------- ----- ----- ------- ----SALESMAN 7698 1981-02-20 1600 300 30 1310 SALESMAN 7698 1981-02-22 1250 500 30 1310 SALESMAN 7698 1981-09-28 1250 1400 30 1310 SALESMAN 7698 1981-09-08 1500 0 30 1310 CLERK 7698 1981-12-03 950 30 1310

5 sor kijelölve. 5 sor módosítva. EMPNO ----7369 7499 7521 7566 7654 7698

ENAME ---------SMITH ALLEN WARD JONES MARTIN BLAKE

JOB MGR HIREDATE SAL COMM DEPTNO --------- ----- ---------- ----- ----- -----CLERK 7902 1980-12-17 800 20 SALESMAN 7698 1981-02-20 1731 300 30 SALESMAN 7698 1981-02-22 1381 500 30 MANAGER 7839 1981-04-02 2975 20 SALESMAN 7698 1981-09-28 1381 1400 30 MANAGER 7839 1981-05-01 2850 30


-5-

7782 7788 7839 7844 7876 7900 7902 7934

CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER

MANAGER ANALYST PRESIDENT SALESMAN CLERK CLERK ANALYST CLERK

7839 1981-06-09 7566 1987-04-19 1981-11-17 7698 1981-09-08 7788 1987-05-23 7698 1981-12-03 7566 1981-12-03 7782 1982-01-23

2450 3000 5000 1631 2548 1081 3000 1300

0

10 20 10 30 20 30 20 10

14 sor kijelölve.

9.3. Feladat Írjon PL/SQL programot, mely az emp táblából létrehoz egy dolgozó táblát, és megnöveli a felhasználó által megadott nevű főnök minden beosztottjának fizetését azok átlagfizetésének 10%-val. Megoldás1 SET numwidth 5; BREAK ON mgr DROP TABLE dolgozó; CREATE TABLE dolgozó AS SELECT * FROM emp; SELECT * FROM dolgozó;

Eredmény A tábla eldobva. A tábla létrejött. EMPNO ----7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934


JOB MGR HIREDATE SAL COMM DEPTNO --------- ----- ---------- ----- ----- -----CLERK 7902 1980-12-17 800 20 SALESMAN 7698 1981-02-20 1600 300 30 SALESMAN 1981-02-22 1250 500 30 MANAGER 7839 1981-04-02 2975 20 SALESMAN 7698 1981-09-28 1250 1400 30 MANAGER 7839 1981-05-01 2850 30 MANAGER 1981-06-09 2450 10 ANALYST 7566 1987-04-19 3000 20 PRESIDENT 1981-11-17 5000 10 SALESMAN 7698 1981-09-08 1500 0 30 CLERK 7788 1987-05-23 2548 20 CLERK 7698 1981-12-03 950 30 ANALYST 7566 1981-12-03 3000 20 CLERK 7782 1982-01-23 1300 10

14 sor kijelölve.

1. Lépés. Hozzunk létre egy nézetet a beosztottak átlagának kiszámítására. CREATE OR REPLACE VIEW beoátlag AS SELECT mgr, ROUND(AVG(sal)) AS átlag FROM dolgozó WHERE mgr IS NOT NULL GROUP BY mgr; SELECT * FROM beoátlag;

Eredmény A nézet létrejött.


-6-

MGR ÁTLAG ----- ----7566 3000 7698 1310 7782 1300 7788 2548 7839 2758 7902 800 6 sor kijelölve.

2. Lépés. Nézet létrehozása a dolgozó, főnök adataival és az átlaggal együtt. CREATE OR REPLACE VIEW BEOSZT AS SELECT d.empno AS dazon, d.ename AS dnév, f.empno AS fazon, f.ename AS fnév, átlag FROM dolgozó d, dolgozó f, Beoátlag beo WHERE d.mgr = f.empno(+) AND f.empno = beo.mgr; SELECT * FROM beoszt;

Eredmény A nézet létrejött. DAZON ----7788 7902 7499 7521 7654 7844 7900 7934 7876 7566 7698 7782 7369

DNÉV FAZON FNÉV ÁTLAG ---------- ----- ---------- ----SCOTT 7566 JONES 3000 FORD 7566 JONES 3000 ALLEN 7698 BLAKE 1310 WARD 7698 BLAKE 1310 MARTIN 7698 BLAKE 1310 TURNER 7698 BLAKE 1310 JAMES 7698 BLAKE 1310 MILLER 7782 CLARK 1300 ADAMS 7788 SCOTT 2548 JONES 7839 KING 2758 BLAKE 7839 KING 2758 CLARK 7839 KING 2758 SMITH 7902 FORD 800

13 sor kijelölve.

2. Lépés. Írjuk meg a növelést mindazoknak, akik a felhasználó által megadott főnök beosztottai, és listázzuk főnökazonosító szerint rendezve. ACCEPT név PROMPT 'Kérem a főnök nevét: ' BEGIN UPDATE dolgozó SET sal = sal + 0.1 * (SELECT DISTINCT átlag FROM beoszt WHERE UPPER(fnév) = UPPER('&név')) WHERE dolgozó.mgr IN (SELECT fazon FROM beoszt WHERE UPPER(fnév) = UPPER('&név')); END; / SELECT * FROM dolgozó ORDER BY mgr; SET numwidth 10 CLEAR BREAKS


-7-

Eredmény Kérem a főnök nevét: blake régi 6: új 6: régi 9: új 9:

WHERE UPPER(fnév) = UPPER('&név')) WHERE UPPER(fnév) = UPPER('blake')) WHERE UPPER(fnév) = UPPER('&név')); WHERE UPPER(fnév) = UPPER('blake'));

A PL/SQL eljárás sikeresen befejeződött. EMPNO ----7788 7902 7499 7521 7900 7844 7654 7934 7876 7566 7782 7698 7369 7839

ENAME ---------SCOTT FORD ALLEN WARD JAMES TURNER MARTIN MILLER ADAMS JONES CLARK BLAKE SMITH KING

JOB MGR HIREDATE SAL COMM DEPTNO --------- ----- ---------- ----- ----- -----ANALYST 7566 1987-04-19 3000 20 ANALYST 1981-12-03 3000 20 SALESMAN 7698 1981-02-20 1731 300 30 SALESMAN 1981-02-22 1381 500 30 CLERK 1981-12-03 1081 30 SALESMAN 1981-09-08 1631 0 30 SALESMAN 1981-09-28 1381 1400 30 CLERK 7782 1982-01-23 1300 10 CLERK 7788 1987-05-23 2548 20 MANAGER 7839 1981-04-02 2975 20 MANAGER 1981-06-09 2450 10 MANAGER 1981-05-01 2850 30 CLERK 7902 1980-12-17 800 20 PRESIDENT 1981-11-17 5000 10

14 sor kijelölve.

Megoldás2 SET numwidth 5 ACCEPT név PROMPT 'Kérem a főnök nevét: ' DROP TABLE dolgozó; CREATE TABLE dolgozó AS SELECT * FROM emp; SELECT * FROM dolgozó; CREATE OR REPLACE VIEW megadott(dempno,dname,djob,dmgr,dhiredate,dsal,dcomm,ddeptno,átlag) AS SELECT dolgozó.*, átlag FROM dolgozó, (SELECT ROUND(AVG(sal)) AS átlag, mgr AS azon FROM dolgozó GROUP BY mgr) al WHERE al.azon = dolgozó.mgr; SELECT * FROM megadott; CREATE OR REPLACE VIEW fönöknév(azon,fnév) AS SELECT DISTINCT főnök.empno, főnök.ename FROM emp dolg, emp főnök WHERE dolg.mgr = főnök.empno(+); SELECT * FROM fönöknév; CREATE OR REPLACE VIEW jo(fnév,azonos,dnév,átlag) AS SELECT fnév, dempno, dname, #13_PL-SQL Feladatgyűjtemény (07)

-8-

átlag FROM fönöknév, megadott WHERE UPPER(fnév) = UPPER('&név') azon = dmgr; SELECT * FROM jo;

AND

CREATE OR REPLACE VIEW növ(emeles) AS SELECT DISTINCT 0.1*átlag FROM jo; SELECT * FROM növ; BEGIN UPDATE dolgozó SET sal = sal + (SELECT emeles FROM növ) WHERE empno = (SELECT azonos FROM jo WHERE dolgozó.ename = dnév); END; / SELECT * FROM dolgozó;

Eredmény Kérem a főnök nevét: blake A tábla eldobva. A tábla létrejött. EMPNO ----7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934



14 sor kijelölve. A nézet létrejött. DEMPNO -----7902 7788 7900 7844 7654 7521 7499 7934 7876 7782 7698 7566 7369

DNAME ---------FORD SCOTT JAMES TURNER MARTIN WARD ALLEN MILLER ADAMS CLARK BLAKE JONES SMITH

DJOB DMGR DHIREDATE DSAL DCOMM DDEPTNO ÁTLAG --------- ----- ---------- ----- ----- ------- ----ANALYST 7566 1981-12-03 3000 20 3000 ANALYST 7566 1987-04-19 3000 20 3000 CLERK 7698 1981-12-03 950 30 1310 SALESMAN 7698 1981-09-08 1500 0 30 1310 SALESMAN 7698 1981-09-28 1250 1400 30 1310 SALESMAN 7698 1981-02-22 1250 500 30 1310 SALESMAN 7698 1981-02-20 1600 300 30 1310 CLERK 7782 1982-01-23 1300 10 1300 CLERK 7788 1987-05-23 2548 20 2548 MANAGER 7839 1981-06-09 2450 10 2758 MANAGER 7839 1981-05-01 2850 30 2758 MANAGER 7839 1981-04-02 2975 20 2758 CLERK 7902 1980-12-17 800 20 800

13 sor kijelölve. #13_PL-SQL Feladatgyűjtemény (07)

-9-

A nézet létrejött. régi 6: WHERE UPPER(fnév) =UPPER('&név') AND új 6: WHERE UPPER(fnév) =UPPER('blake') AND A nézet létrejött. FNÉV AZONOS DNÉV ÁTLAG ---------- ------ ---------- ----BLAKE 7499 ALLEN 1310 BLAKE 7521 WARD 1310 BLAKE 7654 MARTIN 1310 BLAKE 7844 TURNER 1310 BLAKE 7900 JAMES 1310 5 sor kijelölve. A nézet létrejött. A PL/SQL eljárás sikeresen befejeződött. EMPNO ----7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934



14 sor kijelölve.

9.4. Feladat Írjon egy PL/SQL szkript programot az alábbi feladatok megoldására: Hozzon létre egy dolgozó nevű táblát az emp táblának a felhasználó által megadott telephelyű (loc) dolgozóiból, változtassa meg az mgr oszlop nevét partner névre, törölje a tartalmát, és töltse fel az alábbi módon értékkel: Két dolgozó partnere lehet egymásnak, ha még egyiküknek sincs partnere, és a felhasználó által megadott értéknél nem több a fizetésük különbsége. A partnerek azonosítói szerepeljenek egymás partner oszlopában. (Senki önmagának nem lehet partnere, és a partnerség szimmetrikus tulajdonság, azaz ha A partnere B, akkor B partnere A.) Ellenőrizze a megoldást új dolgozó felvitelével, és a partnerkeresés megismétlésével. 1. Megoldás (Explicit Kurzorral) -------------------------------------- Pari1.sql -- MEGOLDÁS (Explicit Kurzorral) ------------------------------------SET serveroutput ON ACCEPT th PROMPT 'Telephely: ' ACCEPT fizk PROMPT 'FizetésKülönbség: '


- 10 -

DROP TABLE dolgozo; CREATE TABLE dolgozo AS SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE loc = UPPER('&th')); ALTER TABLE dolgozo RENAME COLUMN mgr TO partner; UPDATE dolgozo SET partner = NULL; SELECT * FROM dolgozo; DECLARE CURSOR KurzorA IS SELECT * FROM dolgozo FOR UPDATE; CURSOR KurzorB IS SELECT * FROM dolgozo FOR UPDATE; AR dolgozo%ROWTYPE; BR dolgozo%ROWTYPE; FK emp.sal%TYPE; BEGIN FK := &fizk; OPEN KurzorA; LOOP FETCH KurzorA INTO AR; EXIT WHEN KurzorA%NOTFOUND; SELECT partner INTO AR.partner FROM dolgozo WHERE dolgozo.empno=AR.empno; IF AR.partner IS NULL THEN OPEN KurzorB; LOOP FETCH KurzorB INTO BR; EXIT WHEN KurzorB%NOTFOUND; SELECT partner INTO BR.partner FROM dolgozo WHERE dolgozo.empno=BR.empno; IF (BR.partner IS NULL) AND (BR.empno != AR.empno) AND (ABS(AR.sal-BR.sal) <= FK) THEN DBMS_OUTPUT.PUT_LINE(AR.empno); DBMS_OUTPUT.PUT_LINE(BR.empno); UPDATE dolgozo SET partner = AR.empno WHERE CURRENT OF KurzorB; UPDATE dolgozo SET partner = BR.empno WHERE CURRENT OF KurzorA; DBMS_OUTPUT.PUT_LINE('----------'); #13_PL-SQL Feladatgyűjtemény (07)

- 11 -

EXIT; END IF; END LOOP; CLOSE KurzorB; END IF; END LOOP; CLOSE KurzorA; END; / SELECT * FROM dolgozo; -------------------------------------

2. Megoldás (Implicit Kurzorral) -------------------------------------- Pari2.sql -- MEGOLDÁS (Implicit Kurzorral) ------------------------------------SET serveroutput ON ACCEPT th PROMPT 'Telephely: ' ACCEPT fizk PROMPT 'FizetésKülönbség: ' DROP TABLE dolgozo; CREATE TABLE dolgozo AS SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE loc = UPPER('&th')); ALTER TABLE dolgozo RENAME COLUMN mgr TO partner; UPDATE dolgozo SET partner = NULL; SELECT * FROM dolgozo; DECLARE FK emp.sal%TYPE; BEGIN FK := &fizk; FOR AR IN (SELECT * FROM dolgozo) LOOP SELECT partner INTO AR.partner FROM dolgozo WHERE dolgozo.empno=AR.empno; IF AR.partner IS NULL THEN FOR BR IN (SELECT * FROM dolgozo) LOOP SELECT partner INTO BR.partner FROM dolgozo WHERE dolgozo.empno=BR.empno; IF (BR.partner IS NULL) AND #13_PL-SQL Feladatgyűjtemény (07)

- 12 -

(BR.empno != AR.empno) AND (ABS(AR.sal-BR.sal) <= FK) THEN DBMS_OUTPUT.PUT_LINE(AR.empno); DBMS_OUTPUT.PUT_LINE(BR.empno); UPDATE dolgozo SET partner = AR.empno WHERE dolgozo.empno=BR.empno; UPDATE dolgozo SET partner = BR.empno WHERE dolgozo.empno=AR.empno; DBMS_OUTPUT.PUT_LINE('----------'); EXIT; END IF; END LOOP; END IF; END LOOP; END; / SELECT * FROM dolgozo; -------------------------------------

Futási eredmény (mindkét esetben) SQL> @pari1 vagy @pari2 Telephely: dallas FizetésKülönbség: 1000 A tábla eldobva. régi 6: új 6:

WHERE loc = UPPER('&th')) WHERE loc = UPPER('dallas'))

A tábla létrejött. A tábla módosítva. 5 sor módosítva. EMPNO ---------7566 7902 7369 7788 7876

ENAME ---------JONES FORD SMITH SCOTT ADAMS

JOB PARTNER HIREDATE SAL COMM DEPTNO --------- ---------- ---------- ---------- ---------- ---------MANAGER 1981-04-02 2975 20 ANALYST 1981-12-03 3000 20 CLERK 1980-12-17 800 20 ANALYST 1982-12-09 3000 20 CLERK 1983-01-12 1100 20

5 sor kijelölve.

régi 12: új 12: 7566 7902 ---------7369 7876 ----------

FK := &fizk; FK := 1000;


- 13 -

A PL/SQL eljárás sikeresen befejeződött. EMPNO ---------7566 7902 7369 7788 7876

ENAME ---------JONES FORD SMITH SCOTT ADAMS

JOB PARTNER HIREDATE SAL COMM DEPTNO --------- ---------- ---------- ---------- ---------- ---------MANAGER 7902 1981-04-02 2975 20 ANALYST 7566 1981-12-03 3000 20 CLERK 7876 1980-12-17 800 20 ANALYST 1982-12-09 3000 20 CLERK 7369 1983-01-12 1100 20

5 sor kijelölve.


- 14 -

10. Foglalkozás

Összefoglaló Feladatok Részletesebben lásd [2]: 9., és 10. fejezet

FELADATGYŰJTEMÉNY

10.1. Feladat Hozzon létre egy dolgozó nevű táblát az emp tábla eladóiból (salesman), és változtassa meg az mgr oszlop nevét partner névre, melyet az alábbi módon töltsön fel értékkel: Két dolgozó partnere lehet egymásnak, ha még egyiküknek sincs partnere, és 150 USD-nél nem több a fizetésük különbsége. A partnerek azonosítói szerepeljenek egymás partner oszlopában. Ellenőrizze a megoldást új dolgozó felvitelével, és a partnerkeresés megismétlésével. 10.2. Feladat Hozzon létre egy dolgozó nevű táblát az emp tábla rekordjaiból, és bővítse egy partner nevű oszloppal, melynek típusa egyezzen meg az ename oszlop típusával. Készítsen egy SQL*Plus programba ágyazott PL/SQL blokkot, amely megkisérli a dolgozókat párosítani az alábbi szempontok alapján: - minden dolgozó táblabeli dolgozó partnere csak dolgozó táblabeli dolgozó lehet, - önmagának senki nem lehet partnere, - mindenki legfeljebb egy valakinek lehet partnere, - két dolgozó partnere lehet egymásnak, ha ugyanaz a főnökük és megegyezik a munkakörük. 10.3. Feladat 3.1. Hozzon létre egy dolgozó nevű táblát az emp tábla rekordjaiból. 3.2. Készítsen triggert a dolgozó táblába történő ellenőrzött adatbevitelhez, ahol az alábbi adatbeviteli feltételeket a trigger által meghívott tárolt alprogramokban vizsgálja meg: a.) Részleg feltétel: Egy új dolgozó részlegeként csak már létező részleg azonosítója adható meg. b.) Fizetési feltétel: Egy új dolgozó fizetésként a részlegében és vele azonos munkakörben dolgozók átlagfizetésének legalább a 3/4-ét kapja, ha a részlegében nincs vele azonos munkakörű, akkor a részlegében dolgozók átlagfizetésének 2/3-ánál legalább 300 USD-vel többet, ha pedig a részlegében ő az első dolgozó, akkor legalább 2000 USD-t. 3.3. A trigger és a tárolt eljárások ellenőrzése érdekében végezze el a szükséges adatfelviteleket. 3.4. Az elkészített megoldás a tárolandó dolgozó nevét írja vissza a képernyőre, és sikeres adatfelvitel esetén írja ki a dolgozó nevét, majd az "OK!" üzenetet, míg sikertelen adatfelvitel esetén a "HIBA:" üzenetet, és a sikertelenség okát. 3.5. Utolsó lépésként listázza a dolgozó táblát, majd állítsa vissza az eredeti táblatartalmat.


- 15 -

MEGOLDÁSGYŰJTEMÉNY

10.1. Feladat Hozzon létre egy dolgozó nevű táblát az emp tábla eladóiból (salesman), és változtassa meg az mgr oszlop nevét partner névre, melyet az alábbi módon töltsön fel értékkel: Két dolgozó partnere lehet egymásnak, ha még egyiküknek sincs partnere, és 150 USD-nél nem több a fizetésük különbsége. A partnerek azonosítói szerepeljenek egymás partner oszlopában. Ellenőrizze a megoldást új dolgozó felvitelével, és a partnerkeresés megismétlésével. A. Megoldás (Megoldási próbálkozás SQL-ben) 0. lépés (Az SQL*Plus környezet inicializálása) -- Az emp tábla inicializálása (ezúttal elhagyjuk) -- @INIT1 -- Feltételezve, hogy az INIT1.sql a BIN könyvtárban van -- A dátum és megjelenítés inicializálása ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'; SET numwidth 5

-- Az esetlegesen létező azonos nevű objektumok törlése DROP VIEW dolgozó; DROP TABLE dolgozó; A munkamenet módosítva. ORA-00942: a tábla vagy a nézet nem létezik ORA-00942: a tábla vagy a nézet nem létezik

1. lépés (Konzisztens adattábla létrehozása) CREATE OR REPLACE VIEW DolgozóNézet AS SELECT * FROM emp WHERE LOWER(job) = 'salesman'; SELECT * FROM DolgozóNézet; DROP TABLE dolgozó; CREATE TABLE dolgozó AS SELECT empno, ename, job, CASE WHEN mgr IN (SELECT empno FROM DolgozóNézet) THEN mgr ELSE NULL END AS mgr, hiredate, sal, comm, deptno FROM DolgozóNézet; DROP VIEW DolgozóNézet; SELECT * FROM dolgozó; A nézet létrejött. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ---------- ----- ----- -----7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 A tábla létrejött. EMPNO ENAME JOB #13_PL-SQL Feladatgyűjtemény (07)

MGR HIREDATE

SAL

- 16 -

COMM DEPTNO

----7654 7499 7844 7521

---------MARTIN ALLEN TURNER WARD

--------- ----- ---------- ----- ----- -----SALESMAN 1981-09-28 1250 1400 30 SALESMAN 1981-02-20 1600 300 30 SALESMAN 1981-09-08 1500 0 30 SALESMAN 1981-02-22 1250 500 30

2. lépés (Oszlopmódosítás és adattörlés) ALTER TABLE dolgozó RENAME COLUMN mgr TO partner; -- A partner mező ezúttal az alábbi törlés nélkül is üres UPDATE dolgozó SET partner = NULL; SELECT * FROM dolgozó; A tábla módosítva. EMPNO ENAME JOB PARTNER HIREDATE SAL COMM DEPTNO ----- ---------- --------- ------- ---------- ----- ----- -----7654 MARTIN SALESMAN 1981-09-28 1250 1400 30 7499 ALLEN SALESMAN 1981-02-20 1600 300 30 7844 TURNER SALESMAN 1981-09-08 1500 0 30 7521 WARD SALESMAN 1981-02-22 1250 500 30

3. lépés (Adatfeltöltés) -- Partnerkereső segédnézet létrehozása CREATE OR REPLACE VIEW PartnerKeresés(DolgozóID,DolgozóFiz, PartnerID,PartnerFiz) AS SELECT D.empno AS DolgozóID, D.sal AS DolgozóFiz, P.empno AS PartnerID, P.sal AS PartnerFiz FROM dolgozó D, dolgozó P WHERE ABS(D.sal - P.sal) <= 150 AND D.empno != P.empno; SELECT * FROM PartnerKeresés; A nézet létrejött. DOLGOZÓID DOLGOZÓFIZ PARTNERID PARTNERFIZ --------- ---------- --------- ---------7654 1250 7521 1250 7499 1600 7844 1500 7844 1500 7499 1600 7521 1250 7654 1250

-- Adatmódosítás UPDATE dolgozó SET partner = (SELECT PartnerID FROM PartnerKeresés WHERE DolgozóID = empno); SELECT * FROM dolgozó; 4 sor EMPNO ----7654 7499 7844 7521

módosítva. ENAME ---------MARTIN ALLEN TURNER WARD

JOB PARTNER HIREDATE SAL COMM DEPTNO --------- ------- ---------- ----- ----- -----SALESMAN 7521 1981-09-28 1250 1400 30 SALESMAN 7844 1981-02-20 1600 300 30 SALESMAN 7499 1981-09-08 1500 0 30 SALESMAN 7654 1981-02-22 1250 500 30

4. lépés (Ellenőrzés rekordfelvitellel) INSERT INTO dolgozó VALUES(6666,'SIGORSZKI','SALESMAN',NULL,'2005-10-30',1250,NULL,30); 1 sor létrejött.

UPDATE dolgozó #13_PL-SQL Feladatgyűjtemény (07)

- 17 -

SET partner = (SELECT PartnerID FROM PartnerKeresés WHERE DolgozóID = empno); SET partner = (SELECT PartnerID * Hiba a(z) 2. sorban: ORA-01427: egysoros allekérdezés egynél több sorral tér vissza

>> HIBÁS az adatmódosítás! >> Derítsük ki a hiba okát, és készítsünk jó adatmódosító utasítást SELECT * FROM PartnerKeresés; DOLGOZÓID DOLGOZÓFIZ PARTNERID PARTNERFIZ --------- ---------- --------- ---------7654 1250 7521 1250 7654 1250 6666 1250 7499 1600 7844 1500 7844 1500 7499 1600 7521 1250 7654 1250 7521 1250 6666 1250 6666 1250 7654 1250 6666 1250 7521 1250 8 sor kijelölve.

>> A HIBA oka, hogy egy dolgozónak több partnere is lehet a fizetés feltétel szerint SELECT MaxPartnerID FROM (SELECT DolgozóID, MAX(PartnerID) FROM PartnerKeresés GROUP BY DolgozóID) WHERE DolgozóID = 7654;

AS MaxPartnerID

MAXPARTNERID -----------7521

>> Így minden dolgozó a legnagyobb azonosítójú (a fizetésre vonatkozó feltételt teljesítő) >> dolgozót kapja partnerként. >> (Ez egy módszer arra, hogy kijelöljünk egyet a lehetséges elemek közül, >> de ilyen módszer nem mindig található!) -- ÚJABB PRÓBÁLKOZÁS -- A partner mező inicializálása UPDATE dolgozó SET partner = NULL; -- A partner mező beállítása (feltöltése) UPDATE dolgozó SET partner = (SELECT MaxPartnerID FROM (SELECT DolgozóID, MAX(PartnerID) FROM PartnerKeresés GROUP BY DolgozóID) WHERE DolgozóID = empno); SELECT * FROM dolgozó; 5 sor EMPNO ----7654 7499 7844 7521 6666

módosítva. ENAME ---------MARTIN ALLEN TURNER WARD SIGORSZKI

AS MaxPartnerID

JOB PARTNER HIREDATE SAL COMM DEPTNO --------- ------- ---------- ----- ----- -----SALESMAN 7521 1981-09-28 1250 1400 30 SALESMAN 7844 1981-02-20 1600 300 30 SALESMAN 7499 1981-09-08 1500 0 30 SALESMAN 7654 1981-02-22 1250 500 30 SALESMAN 7654 2005-10-30 1250 30


- 18 -

>> Még mindig HIBÁS az adatmódosítás! >> A HIBA oka, hogy a legnagyobb azonosítójú dolgozót többen is tudják választani. >> Ezt úgy küszöböljük ki, hogy mielőtt egy dolgozó-azonosítót partnerkódként kiosztunk, >> megvizsgáljuk, hogy azt nem kapta-e már meg valaki. >> (Ezt algebrailag felírva: {a} ⊆ B ⇔ {a} \ B ≠ ∅, ahol {a} a vizsgálandó dolgozó-azonosító, >> vagyis a "(SELECT MaxPartnerID..." utasításrész, amelyik bár csak egyetlen elemet ad ki, >> azért mégis csak halmaz, hiszen lista.) -- ÚJABB PRÓBÁLKOZÁS 2. -- A partner mező inicializálása UPDATE dolgozó SET partner = NULL; -- A partner mező beállítása (feltöltése) UPDATE dolgozó SET partner = (SELECT MaxPartnerID FROM (SELECT DolgozóID, MAX(PartnerID) AS MaxPartnerID FROM PartnerKeresés GROUP BY DolgozóID) WHERE DolgozóID = empno) WHERE NOT EXISTS (SELECT MaxPartnerID FROM (SELECT DolgozóID, MAX(PartnerID) AS MaxPartnerID FROM PartnerKeresés GROUP BY DolgozóID) WHERE DolgozóID = empno MINUS SELECT partner FROM dolgozó); SELECT * FROM dolgozó; 0 sor módosítva. EMPNO ----7654 7499 7844 7521 6666

ENAME ---------MARTIN ALLEN TURNER WARD SIGORSZKI

JOB PARTNER HIREDATE SAL COMM DEPTNO --------- ------- ---------- ----- ----- -----SALESMAN 1981-09-28 1250 1400 30 SALESMAN 1981-02-20 1600 300 30 SALESMAN 1981-09-08 1500 0 30 SALESMAN 1981-02-22 1250 500 30 SALESMAN 2005-10-30 1250 30

>> Még mindig HIBÁS az adatmódosítás! >> Mi lehet a HIBA oka, hiszen ez az adatmódosítás már igazán mindent figyelembe vesz? >> ???? (Ötletek küldhetők az oktat123@freemail címre.)


- 19 -

B. Megoldás (Megoldás PL/SQL-ben – Rejtett kurzorral és ROWID-val) 1. lépés (Konzisztens adattábla létrehozása) ugyanaz, mint az A. Megoldásban a 0. és az 1. lépés (lásd ott a szükséges SQL-utasításokat, illetve a DataLoad_A.sql szkript programot) 2. lépés (Oszlopmódosítás, rekordhozzáadás) ugyanaz, mint az A. Megoldásban a 2. és a 3. lépés (lásd ott a szükséges SQL-utasításokat, illetve a DataLoad_A.sql szkript programot) 3. lépés (Adatfeltöltés - ROWID használattal) -- DataLoad_B.sql -------------------------------------------------------------- Adatfeltöltés rejtett kurzorral és ROWID használattal --- (Értelmezését lásd a 12_KurzorHasználat.doc szkriptben) --------------------------------------------------------------- Utolsó javítás dátuma: 2005.11.22. ------------------------------------------------------------SET serveroutput ON DECLARE v_Egyik dolgozó.partner%TYPE; v_Másik dolgozó.partner%TYPE; BEGIN -- A dolgozó tábla inicializálása UPDATE dolgozó SET partner = NULL; -- A fejléc kiíratása DBMS_OUTPUT.PUT_LINE('DOLGOZÓ ' || 'Fizetése ' || 'PARTNER ' || 'Fizetése'); DBMS_OUTPUT.PUT_LINE('==================================='); -- A külső ciklus (végigmegy az összes dolgozó rekordján) FOR EgyikDolgozó IN (SELECT dolgozó.*, ROWID AS RecID FROM dolgozó) LOOP -- A partner azonosító lekérdezése a külső ciklus számára: SELECT partner INTO v_Egyik FROM dolgozó WHERE ROWID = EgyikDolgozó.RecID; -- A belső ciklus (ez is végigmegy az összes dolgozó rekordján) FOR MásikDolgozó IN (SELECT dolgozó.*, ROWID AS RecID FROM dolgozó) LOOP -- A partner azonosító lekérdezése a belső ciklus számára: SELECT partner INTO v_Másik FROM dolgozó WHERE ROWID = MásikDolgozó.RecID; ------

Ha a külső ciklus aktuális dolgozójának fizetése és a belső ciklus aktuális dolgozójának fizetése között a különbség nem több 150 USD-nél, és a két dolgozó nem azonos, és egyiknek sincs még partnere,


- 20 -

IF ABS(EgyikDolgozó.sal - MásikDolgozó.sal) <= 150 EgyikDolgozó.empno != MásikDolgozó.empno v_Egyik IS NULL v_Másik IS NULL THEN -- akkor állítsuk be a partner-azonosítót: UPDATE dolgozó SET partner = MásikDolgozó.empno WHERE ROWID = EgyikDolgozó.RecID;

AND AND AND

-- és a beállítást visszafelé is végezzük el: UPDATE dolgozó SET partner = EgyikDolgozó.empno WHERE ROWID = MásikDolgozó.RecID; -- Kiíratás DBMS_OUTPUT.PUT_LINE(EgyikDolgozó.empno EgyikDolgozó.sal MásikDolgozó.empno MásikDolgozó.sal);

||' ||' ||'

'

|| '|| ' ||

-- Kiugrás a ciklusból -- Az egyezés és módosítás után kiugrás a belső ciklusból, és -- ugrás a külső ciklus következő rekordjára EXIT; END IF; -- A belső (MásikDolgozó) ciklus bezárása END LOOP; -- A külső (EgyikDolgozó) ciklus bezárása END LOOP; END; / SELECT * FROM dolgozó; -- End Of DataLoad_B.sql DOLGOZÓ Fizetése PARTNER Fizetése =================================== 7654 1250 7521 1250 7499 1600 7844 1500 A PL/SQL eljárás sikeresen befejeződött. EMPNO ----7654 7499 7844 7521 6666


JOB PARTNER HIREDATE SAL COMM DEPTNO --------- ------- ---------- ----- ----- -----SALESMAN 7521 1981-09-28 1250 1400 30 SALESMAN 7844 1981-02-20 1600 300 30 SALESMAN 7499 1981-09-08 1500 0 30 SALESMAN 7654 1981-02-22 1250 500 30 SALESMAN 2005-10-30 1250 30


- 21 -

C. Megoldás (Megoldás PL/SQL-ben – Rejtett kurzorral, ROWID nélkül) 1. lépés (Konzisztens adattábla létrehozása) ugyanaz, mint az A. Megoldásban a 0. és az 1. lépés (lásd ott a szükséges SQL-utasításokat, illetve a DataLoad_A.sql szkript programot) 2. lépés (Oszlopmódosítás, rekordhozzáadás) ugyanaz, mint az A. Megoldásban a 2. és 3. lépés (lásd ott a szükséges SQL-utasításokat, illetve a DataLoad_A.sql szkript programot) 3. lépés (Adatfeltöltés - ROWID használat nélkül) A feladat megoldható ezúttal ROWID használata nélkül is, mivel van olyan, a rekordokat egyértelműen azonosító oszlop (empno), mely a kurzor-ciklusban nem változik meg. -- DataLoad_C.sql -------------------------------------------------------------- Adatfeltöltés rejtett kurzorral, ROWID nélkül --- (Értelmezését lásd a 12_KurzorHasználat.doc szkriptben) --------------------------------------------------------------- Utolsó javítás dátuma: 2005.11.22. ------------------------------------------------------------SET serveroutput ON DECLARE v_Egyik dolgozó.partner%TYPE; v_Másik dolgozó.partner%TYPE; BEGIN -- A dolgozó tábla inicializálása UPDATE dolgozó SET partner = NULL; -- A fejléc kiíratása DBMS_OUTPUT.PUT_LINE('DOLGOZÓ ' || 'Fizetése ' || 'PARTNER ' || 'Fizetése'); DBMS_OUTPUT.PUT_LINE('==================================='); -- A külső ciklus (végigmegy az összes dolgozó rekordján) FOR EgyikDolgozó IN (SELECT * FROM dolgozó) LOOP -- A partner azonosító lekérdezése a külső ciklus számára: SELECT partner INTO v_Egyik FROM dolgozó WHERE empno = EgyikDolgozó.empno; -- A belső ciklus (ez is végigmegy az összes dolgozó rekordján) FOR MásikDolgozó IN (SELECT * FROM dolgozó) LOOP -- A partner azonosító lekérdezése a belső ciklus számára: SELECT partner INTO v_Másik FROM dolgozó WHERE empno = MásikDolgozó.empno; ------

Ha a külső ciklus aktuális dolgozójának fizetése és a belső ciklus aktuális dolgozójának fizetése között a különbség nem több 150 USD-nél, és a két dolgozó nem azonos, és egyiknek sincs még partnere,


- 22 -

IF ABS(EgyikDolgozó.sal - MásikDolgozó.sal) <= 150 EgyikDolgozó.empno != MásikDolgozó.empno v_Egyik IS NULL v_Másik IS NULL THEN -- akkor állítsuk be a partner-azonosítót: UPDATE dolgozó SET partner = MásikDolgozó.empno WHERE empno = EgyikDolgozó.empno;

AND AND AND

-- és a beállítást visszafelé is végezzük el: UPDATE dolgozó SET partner = EgyikDolgozó.empno WHERE empno = MásikDolgozó.empno; -- Kiíratás DBMS_OUTPUT.PUT_LINE(EgyikDolgozó.empno EgyikDolgozó.sal MásikDolgozó.empno MásikDolgozó.sal);

||' ||' ||'

'

|| '|| ' ||

-- Kiugrás a ciklusból -- Az egyezés és módosítás után kiugrás a belső ciklusból, és -- ugrás a külső ciklus következő rekordjára EXIT; END IF; -- A belső (MásikDolgozó) ciklus bezárása END LOOP; -- A külső (EgyikDolgozó) ciklus bezárása END LOOP; END; / SELECT * FROM dolgozó; -- End Of DataLoad_C.sql DOLGOZÓ Fizetése PARTNER Fizetése =================================== 7654 1250 7521 1250 7499 1600 7844 1500 A PL/SQL eljárás sikeresen befejeződött. EMPNO ----7654 7499 7844 7521 6666


JOB PARTNER HIREDATE SAL COMM DEPTNO --------- ------- ---------- ----- ----- -----SALESMAN 7521 1981-09-28 1250 1400 30 SALESMAN 7844 1981-02-20 1600 300 30 SALESMAN 7499 1981-09-08 1500 0 30 SALESMAN 7654 1981-02-22 1250 500 30 SALESMAN 2005-10-30 1250 30


- 23 -

D. Hibás Megoldás Tekintsük az előző, C. Megoldást, ám ezúttal ne a dolgozó táblából, hanem az egyes ciklusok rejtett kurzoraiból kérdezzük le egyszerű értékadással (az adott ciklus ciklusváltozóján keresztül) az aktuális rekord partner értékét (lásd a DataLoad_D.sql szkript programot). Eredményként az alábbi táblát kapjuk: EMPNO ----7654 7499 7844 7521 6666


JOB PARTNER HIREDATE SAL COMM DEPTNO --------- ------- ---------- ----- ----- -----SALESMAN 7521 1981-09-28 1250 1400 30 SALESMAN 7844 1981-02-20 1600 300 30 SALESMAN 7499 1981-09-08 1500 0 30 SALESMAN 6666 1981-02-22 1250 500 30 SALESMAN 7521 2005-10-30 1250 30

Ez láthatóan hibás (Martinnak és Sigorszkinak ugyanaz a partner jutott). Mi a hiba oka? A magyarázat a kurzorok alaptermészetében keresendő: A kurzorbeli lekérdezés eredménye egy zárt memóriaterületre kerül, melyet nem befolyásol azon adattábla tartalmának megváltozása, melyre a kurzorlekérdezés történt.


- 24 -

2. Feladat Hozzon létre egy dolgozó nevű táblát az emp tábla rekordjaiból, és bővítse egy partner nevű oszloppal, melynek típusa egyezzen meg az ename oszlop típusával. Készítsen egy SQL*Plus programba ágyazott PL/SQL blokkot, amely megkisérli a dolgozókat párosítani az alábbi szempontok alapján: - minden dolgozó táblabeli dolgozó partnere csak dolgozó táblabeli dolgozó lehet, - önmagának senki nem lehet partnere, - mindenki legfeljebb egy valakinek lehet partnere, - két dolgozó partnere lehet egymásnak, ha ugyanaz a főnökük és megegyezik a munkakörük. Megoldás SET serveroutput ON SET numwidth 5 DROP VIEW dolgozó; DROP TABLE dolgozó; CREATE TABLE dolgozó AS SELECT * FROM emp; ALTER TABLE dolgozó DROP COLUMN partner; ALTER TABLE dolgozó ADD partner VARCHAR2(6) DEFAULT '---'; DECLARE i_Partner dolgozó.partner%Type; j_Partner dolgozó.partner%Type; BEGIN FOR i IN (SELECT * FROM dolgozó) LOOP SELECT partner INTO i_Partner FROM dolgozó WHERE empno = i.empno; FOR j IN (SELECT * FROM dolgozó) LOOP SELECT partner INTO j_Partner FROM dolgozó WHERE empno = j.empno; IF (i.mgr = j.mgr) AND (i.job = j.job) AND (i.empno <> j.empno) AND (i_Partner = '---') AND (j_Partner = '---') THEN DBMS_OUTPUT.PUT_LINE(i.ename||i_Partner||' j.ename||j_Partner); UPDATE dolgozó SET partner = j.ename WHERE empno = i.empno; UPDATE dolgozó SET partner = i.ename WHERE empno = j.empno; EXIT; END IF; END LOOP; #13_PL-SQL Feladatgyűjtemény (07)

- 25 -

'||

END LOOP; END; / SELECT * FROM dolgozó; SET numwidth 10

Eredmény A A A A

tábla tábla tábla tábla

eldobva. létrejött. módosítva. módosítva.

ALLEN--- WARD--JONES--- BLAKE--MARTIN--- TURNER--SCOTT--- FORD--A PL/SQL eljárás sikeresen befejeződött. EMPNO ----7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934


JOB MGR HIREDATE SAL COMM DEPTNO PARTNE --------- ----- ---------- ----- ----- ------ -----CLERK 7902 1980-12-17 800 20 --SALESMAN 7698 1981-02-20 1600 300 30 WARD SALESMAN 7698 1981-02-22 1250 500 30 ALLEN MANAGER 7839 1981-04-02 2975 20 BLAKE SALESMAN 7698 1981-09-28 1250 1400 30 TURNER MANAGER 7839 1981-05-01 2850 30 JONES MANAGER 7839 1981-06-09 2450 10 --ANALYST 7566 1987-04-19 3000 20 FORD PRESIDENT 1981-11-17 5000 10 --SALESMAN 7698 1981-09-08 1500 0 30 MARTIN CLERK 7788 1987-05-23 1100 20 --CLERK 7698 1981-12-03 950 30 --ANALYST 7566 1981-12-03 3000 20 SCOTT CLERK 7782 1982-01-23 1300 10 ---

14 sor kijelölve.


- 26 -

10.3. Feladat 3.1. Hozzon létre egy dolgozó nevű táblát az emp tábla rekordjaiból. 3.2. Készítsen triggert a dolgozó táblába történő ellenőrzött adatbevitelhez, ahol az alábbi adatbeviteli feltételeket a trigger által meghívott tárolt alprogramokban vizsgálja meg: a.) Részleg feltétel: Egy új dolgozó részlegeként csak már létező részleg azonosítója adható meg. b.) Fizetési feltétel: Egy új dolgozó fizetésként a részlegében és vele azonos munkakörben dolgozók átlagfizetésének legalább a 3/4-ét kapja, ha a részlegében nincs vele azonos munkakörű, akkor a részlegében dolgozók átlagfizetésének 2/3-ánál legalább 300 USD-vel többet, ha pedig a részlegében ő az első dolgozó, akkor legalább 2000 USD-t. 3.3. A trigger és a tárolt eljárások ellenőrzése érdekében végezze el a szükséges adatfelviteleket. 3.4. Az elkészített megoldás a tárolandó dolgozó nevét írja vissza a képernyőre, és sikeres adatfelvitel esetén írja ki a dolgozó nevét, majd az "OK!" üzenetet, míg sikertelen adatfelvitel esetén a "HIBA:" üzenetet, és a sikertelenség okát. 3.5. Utolsó lépésként listázza a dolgozó táblát, majd állítsa vissza az eredeti táblatartalmat. Megoldás 0. lépés (Az SQL*Plus környezet inicializálása) -- Az emp tábla inicializálása (ezúttal elhagyjuk) -- @INIT1 -- Feltételezve, hogy az INIT1.sql a BIN könyvtárban van -- A dátum és megjelenítés inicializálása ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'; SET numwidth 5

-- Az esetlegesen létező azonos nevű objektumok törlése DROP VIEW dolgozó; DROP TABLE dolgozó;

-- A PL/SQL kiíratások engedélyezése SET serveroutput ON; A munkamenet módosítva. ORA-00942: a tábla vagy a nézet nem létezik ORA-00942: a tábla vagy a nézet nem létezik

1. lépés feladata 3.1. Hozzon létre egy dolgozó nevű táblát az emp tábla rekordjaiból. 1. lépés feladatának megoldása DROP TABLE dolgozó; CREATE TABLE dolgozó AS SELECT * FROM emp; SELECT * FROM dolgozó; ORA-00942: a tábla vagy a nézet nem létezik A tábla létrejött. EMPNO ----7839 7698 7782 7566 7654 7499

ENAME ---------KING BLAKE CLARK JONES MARTIN ALLEN

JOB MGR HIREDATE SAL COMM DEPTNO --------- ----- ---------- ----- ----- -----PRESIDENT 1981-11-17 5000 10 MANAGER 7839 1981-05-01 2850 30 MANAGER 7839 1981-06-09 2450 10 MANAGER 7839 1981-04-02 2975 20 SALESMAN 7698 1981-09-28 1250 1400 30 SALESMAN 7698 1981-02-20 1600 300 30


- 27 -

7844 7900 7521 7902 7369 7788 7876 7934

TURNER JAMES WARD FORD SMITH SCOTT ADAMS MILLER

SALESMAN CLERK SALESMAN ANALYST CLERK ANALYST CLERK CLERK

7698 7698 7698 7566 7902 7566 7788 7782

1981-09-08 1981-12-03 1981-02-22 1981-12-03 1980-12-17 1987-04-19 1987-05-23 1982-01-23

1500 950 1250 3000 800 3000 1100 1300

0 500

30 30 30 20 20 20 20 10

14 sor kijelölve.

2. lépés feladata 3.2. Készítsen triggert a dolgozó táblába történő ellenőrzött adatbevitelhez, ahol az alábbi adatbeviteli feltételeket a trigger által meghívott tárolt alprogramokban vizsgálja meg: a.) Részleg feltétel: Egy új dolgozó részlegeként csak már létező részleg azonosítója adható meg. b.) Fizetési feltétel: Egy új dolgozó fizetésként a részlegében és vele azonos munkakörben dolgozók átlagfizetésének legalább a 3/4-ét kapja, ha a részlegében nincs vele azonos munkakörű, akkor a részlegében dolgozók átlagfizetésének 2/3-ánál legalább 300 USD-vel többet, ha pedig a részlegében ő az első dolgozó, akkor legalább 2000 USD-t. 3.4. Az elkészített megoldás a tárolandó dolgozó nevét írja vissza a képernyőre, és sikeres adatfelvitel esetén írja ki a dolgozó nevét, majd az "OK!" üzenetet, míg sikertelen adatfelvitel esetén a "HIBA:" üzenetet, és a sikertelenség okát. 2. lépés feladatának megoldása Megjegyzés: m1. A részlegfeltétel ellenőrzése érdekében létre kell hoznunk a részleg táblát is. m2. A 3.4. pontbeli követelményt célszerű a triggerben, illetve a trigger által meghívott tárolt eljárásokban megvalósítani. 2.1. lépés (A részleg tábla létrehozása) -- Az esetlegesen létező azonos nevű objektumok törlése DROP VIEW részleg; DROP TABLE részleg;

-- A részleg tábla létrehozása CREATE TABLE részleg AS SELECT * FROM dept;

SELECT * FROM részleg; ORA-00942: a tábla vagy a nézet nem létezik ORA-00942: a tábla vagy a nézet nem létezik A tábla létrejött. DEPTNO -----10 20 30 40

DNAME -------------ACCOUNTING RESEARCH SALES OPERATIONS

LOC ------------NEW YORK DALLAS CHICAGO BOSTON

2.2. lépés (A részlegfeltételt ellenőrző tárolt alprogram létrehozása) -- A részleglétezést ellenőrző tárolt alprogram -- Ennek két kimenete van attól függően, hogy létezik-e a megadott részleg, -- vagy nem (ez a hiba ág). CREATE OR REPLACE PROCEDURE Részlegfeltétel(RészlegID IN NUMBER) AS Előfordulás INTEGER; #13_PL-SQL Feladatgyűjtemény (07)

- 28 -

BEGIN SELECT COUNT(*) INTO Előfordulás FROM részleg WHERE deptno = RészlegID; IF Előfordulás = 0 -- F-a. FELTÉTEL! THEN RAISE_APPLICATION_ERROR(-20110, '>>HIBA: A Részlegfeltétel nem teljesül!'); ELSE DBMS_OUTPUT.PUT_LINE('OK! '|| '(A Részlegfeltétel teljesül.)'); END IF; END; / SHOW ERROR Az eljárás létrejött. Nincsenek hibák.

2.3. lépés (A fizetési feltételt ellenőrző tárolt alprogram létrehozása) -- A fizetési feltételt ellenőrző tárolt alprogramban két vizsgálatot kell elvégezni. -- Először azt, hogy van-e már a megadott részlegben dolgozó, vagy nincs, -- Ha van dolgozó, akkor pedig azt, hogy van-e azonos munkakörű, vagy nincs. CREATE OR REPLACE PROCEDURE Fizetésfeltétel(RészlegID IN NUMBER, Munkakör IN VARCHAR2, Fizetés IN NUMBER) IS RészlegbeliDolgozók INTEGER; MunkakörbeliDolgozók INTEGER; RészlegÁtlag dolgozó.sal%TYPE; MunkakörÁtlag dolgozó.sal%TYPE; FizetésHatár dolgozó.sal%TYPE; BEGIN -- Az új dolgozó részlegében már dolgozók számának meghatározása SELECT COUNT(*) INTO RészlegbeliDolgozók FROM dolgozó WHERE deptno = RészlegID; -- A fizetés vizsgálata a részlegbeli dolgozók számától függően IF RészlegbeliDolgozók = 0 THEN -- Ekkor az új dolgozó az első dolgozó a részlegében IF Fizetés >= 2000 -- F-b3. FELTÉTEL! THEN DBMS_OUTPUT.PUT_LINE('OK! '|| '(Az új fizetés legalább 2000 USD.)'); ELSE RAISE_APPLICATION_ERROR(-20113, '>>HIBA: Az új fizetés kisebb 2000 USD-nél!'); END IF; ELSE -- Ekkor már vannak mások is az új dolgozó részlegében -- Kérdés: Vannak-e azonos munkakörbeliek is? SELECT COUNT(*) INTO MunkakörbeliDolgozók FROM dolgozó WHERE deptno = RészlegID AND UPPER(job) = UPPER(Munkakör); -- A fizetés vizsgálata a részlegbeli dolgozók munkakörétől függően #13_PL-SQL Feladatgyűjtemény (07)

- 29 -

IF MunkakörbeliDolgozók = 0 THEN -- Nincs azonos munkakörű a részlegben SELECT AVG(sal) INTO RészlegÁtlag FROM dolgozó GROUP BY deptno HAVING deptno = RészlegID;

több.)');

FizetésHatár := ROUND(2/3 * RészlegÁtlag) + 300; DBMS_OUTPUT.PUT_LINE('> 2/3 * RészlegÁtlag + 300 = '|| FizetésHatár); IF Fizetés >= FizetésHatár -- F-b2. FELTÉTEL! THEN DBMS_OUTPUT.PUT_LINE('OK! '|| '(Az új fizetés a részlegátlag 2/3-ánál min. 300 USD-vel

ELSE RAISE_APPLICATION_ERROR(-20112, '>>HIBA: Az új fizetés kisebb, ' || 'mint a részlegátlag 300 USD-vel megnövelt értéke!'); END IF; ELSE -- Van azonos munkakörű a részlegben SELECT AVG(sal) INTO MunkakörÁtlag FROM dolgozó GROUP BY deptno, job HAVING deptno = RészlegID AND UPPER(job) = UPPER(Munkakör);

FizetésHatár := ROUND(3/4 * MunkakörÁtlag); DBMS_OUTPUT.PUT_LINE('> 3/4 * MunkakörÁtlag = '|| FizetésHatár); IF Fizetés >= FizetésHatár -- F-b1. FELTÉTEL! THEN DBMS_OUTPUT.PUT_LINE('OK! '|| '(Az új fizetés a részlegbeli munkaköri átlag 3/4-nél több.)'); ELSE RAISE_APPLICATION_ERROR(-20111, '>>HIBA: Az új fizetés kisebb, ' || 'mint a részlegbeli munkaköri átlag 3/4-e!'); END IF; END IF; END IF; END; / SHOW ERROR Az eljárás létrejött. Nincsenek hibák.

Megjegyzés A fenti tárolt eljárásban a SELECT AVG(sal) INTO RészlegÁtlag FROM dolgozó GROUP BY deptno HAVING deptno = RészlegID;

helyett írhattuk volna SELECT AVG(sal) INTO RészlegÁtlag FROM dolgozó #13_PL-SQL Feladatgyűjtemény (07)

- 30 -

WHERE deptno = RészlegID;

valamint a SELECT AVG(sal) INTO MunkakörÁtlag FROM dolgozó GROUP BY deptno, job HAVING deptno = RészlegID AND UPPER(job) = UPPER(Munkakör);

helyett írhattuk volna SELECT AVG(sal) INTO MunkakörÁtlag FROM dolgozó WHERE deptno = RészlegID AND UPPER(job) = UPPER(Munkakör);

2.4. lépés (Az adatbevitel-felügyelő trigger létrehozása) -- A sortrigger a feltételvizsgáló tárolt alprogramokat hívja meg. DROP TRIGGER AdatFelügyelő; CREATE OR REPLACE TRIGGER AdatFelügyelő BEFORE INSERT ON dolgozó FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE ('> A felviendő dolgozó: ' ||:new.ename); Részlegfeltétel(:new.deptno); Fizetésfeltétel(:new.deptno, :new.job, :new.sal); DBMS_OUTPUT.PUT_LINE (:new.ename || ': OK!'); END; / SHOW ERROR ORA-04080: 'ADATFELÜGYELŐ' trigger nem létezik A trigger létrejött. Nincsenek hibák.

3. lépés feladata 3.3. A trigger és a tárolt eljárások ellenőrzése érdekében végezze el a szükséges adatfelviteleket. 3. lépés feladatának megoldása (A felügyelt adatbevitel ellenőrzése) Az alábbiakban ellenőrizni kell az F-a., F-b1., F-b2. és F-b3. feltételek mindegyikének teljesülését és nem teljesülését. Ennek célszerű módja olyan adatfelviteleket készíteni, melyekben mindig csak egy feltétel nem-teljesülését és teljesülését vizsgáljuk. Mivel az F-a. feltétel teljesülése az összes többihez szükséges, így mindössze hét adatfelvitel szükséges a teljes ellenőrzéshez. 3.1. lépés (Adatlekérdezések az ellenőrzéshez) -- Részlegenkénti munkaköri átlagok (az F-b1. feltétel vizsgálatához) SELECT deptno job ROUND(AVG(sal)*3/4) FROM dolgozó GROUP BY deptno, job; RÉSZLEG ---------10 10 10 20 20 20 30 30 30

AS Részleg, AS Munkakör, AS "RészlegMunkakörÁtlag 3/4-e"

MUNKAKÖR RészlegMunkakörÁtlag 3/4-e --------- -------------------------CLERK 975 MANAGER 1838 PRESIDENT 3750 CLERK 713 ANALYST 2250 MANAGER 2231 CLERK 713 MANAGER 2138 SALESMAN 1050


- 31 -

9 sor kijelölve.

-- Részlegátlagok (az F-b2. feltétel vizsgálatához) SELECT deptno ROUND(AVG(SAL)*2/3)+300 FROM dolgozó GROUP BY deptno;

AS Részleg, AS "2/3-ad RészlegÁtlag + 300"

RÉSZLEG 2/3-ad RészlegÁtlag + 300 ---------- ------------------------10 2244 20 1750 30 1344

3.2. lépés (Ellenőrzések) -- 1. Ellenőrzés: -- Az F-a. (Részlegfeltétel) NEM-teljesülésének vizsgálata INSERT INTO dolgozó VALUES(1234,'Péter(a)','SALESMAN',7698,'2005.12.11',2000,NULL,50); > A felviendő dolgozó: Péter(a) INSERT INTO dolgozó * Hiba a(z) 1. sorban: ORA-20110: >>HIBA: A Részlegfeltétel nem teljesül! ORA-06512: a(z) "SCOTT.RÉSZLEGFELTÉTEL", helyen a(z) 11. sornál ORA-06512: a(z) "SCOTT.ADATFELÜGYELŐ", helyen a(z) 3. sornál ORA-04088: hiba a(z) 'SCOTT.ADATFELÜGYELŐ' trigger futása közben

-- 2. Ellenőrzés: -- Az F-a. (Részlegfeltétel) teljesülése mellett -- az F-b1. (Fizetési feltétel, RészlegMunkakör Fiz.átlag 3/4-e) vizsgálata INSERT INTO dolgozó VALUES(1234,'János1(b1)','SALESMAN',7698,'2005.12.11',1049,NULL,30); INSERT INTO dolgozó VALUES(1234,'János2(b1)','SALESMAN',7698,'2005.12.11',1050,NULL,30); > A felviendő dolgozó: János1(b1) OK! (A Részlegfeltétel teljesül.) > 3/4 * MunkakörÁtlag = 1050 INSERT INTO dolgozó * Hiba a(z) 1. sorban: ORA-20111: >>HIBA: Az új fizetés kisebb, mint a részlegbeli munkaköri átlag 3/4-e! ORA-06512: a(z) "SCOTT.FIZETÉSFELTÉTEL", helyen a(z) 78. sornál ORA-06512: a(z) "SCOTT.ADATFELÜGYELŐ", helyen a(z) 4. sornál ORA-04088: hiba a(z) 'SCOTT.ADATFELÜGYELŐ' trigger futása közben > A felviendő dolgozó: János2(b1) OK! (A Részlegfeltétel teljesül.) > 3/4 * MunkakörÁtlag = 1050 OK! (Az új fizetés a részlegbeli munkaköri átlag 3/4-nél több.) János2(b1): OK! 1 sor létrejött.

-- 3. Ellenőrzés: -- Az F-a. (Részlegfeltétel) teljesülése mellett -- az F-b2. (Fizetési feltétel, 2/3-ad RészlegÁtlag + 300) NEM-teljesülésének vizsgálata INSERT INTO dolgozó VALUES(1234,'Éva1(b2)','TANULÓ',7698,'2005.12.11',1749,NULL,20); INSERT INTO dolgozó VALUES(1234,'Éva2(b2)','TANULÓ',7698,'2005.12.11',1750,NULL,20); > A felviendő dolgozó: Éva1(b2) OK! (A Részlegfeltétel teljesül.) #13_PL-SQL Feladatgyűjtemény (07)

- 32 -

> 2/3 * RészlegÁtlag + 300 = 1750 INSERT INTO dolgozó * Hiba a(z) 1. sorban: ORA-20112: >>HIBA: Az új fizetés kisebb, mint a részlegátlag 300 USD-vel megnövelt értéke! ORA-06512: a(z) "SCOTT.FIZETÉSFELTÉTEL", helyen a(z) 57. sornál ORA-06512: a(z) "SCOTT.ADATFELÜGYELŐ", helyen a(z) 4. sornál ORA-04088: hiba a(z) 'SCOTT.ADATFELÜGYELŐ' trigger futása közben > A felviendő dolgozó: Éva2(b2) OK! (A Részlegfeltétel teljesül.) > 2/3 * RészlegÁtlag + 300 = 1750 OK! (Az új fizetés a részlegátlag 2/3-ánál min. 300 USD-vel több.) Éva2(b2): OK! 1 sor létrejött.

-- 4. Ellenőrzés: -- Az F-a. (Részlegfeltétel) teljesülése mellett -- az F-b3. (Fizetési feltétel, a részlegben még nincs dolgozó) NEM-teljesülésének vizsgálata INSERT INTO dolgozó VALUES(1234,'Ágnes1(b3)','SALESMAN',7698,'2005.12.11',1999,NULL,40); INSERT INTO dolgozó VALUES(1234,'Ágnes2(b3)','SALESMAN',7698,'2005.12.11',2000,NULL,40); > A felviendő dolgozó: Ágnes1(b3) OK! (A Részlegfeltétel teljesül.) INSERT INTO dolgozó * Hiba a(z) 1. sorban: ORA-20113: >>HIBA: Az új fizetés kisebb 2000 USD-nél! ORA-06512: a(z) "SCOTT.FIZETÉSFELTÉTEL", helyen a(z) 27. sornál ORA-06512: a(z) "SCOTT.ADATFELÜGYELŐ", helyen a(z) 4. sornál ORA-04088: hiba a(z) 'SCOTT.ADATFELÜGYELŐ' trigger futása közben > A felviendő dolgozó: Ágnes2(b3) OK! (A Részlegfeltétel teljesül.) OK! (Az új fizetés legalább 2000 USD.) Ágnes2(b3): OK! 1 sor létrejött.

4. lépés feladata 3.5. Utolsó lépésként listázza a dolgozó táblát, majd állítsa vissza az eredeti táblatartalmat. 4. lépés feladatának megoldása -- A dolgozó tábla listázása és adatvisszaállítás SELECT * FROM dolgozó; ROLLBACK; SELECT * FROM dolgozó; EMPNO ---------7839 7698 7782 7566 7654 7499 7844 7900 7521 7902 7369 7788 7876 7934

ENAME ---------KING BLAKE CLARK JONES MARTIN ALLEN TURNER JAMES WARD FORD SMITH SCOTT ADAMS MILLER

JOB MGR HIREDATE SAL COMM DEPTNO --------- ---------- ---------- ---------- ---------- ---------PRESIDENT 1981-11-17 5000 10 MANAGER 7839 1981-05-01 2850 30 MANAGER 7839 1981-06-09 2450 10 MANAGER 7839 1981-04-02 2975 20 SALESMAN 7698 1981-09-28 1250 1400 30 SALESMAN 7698 1981-02-20 1600 300 30 SALESMAN 7698 1981-09-08 1500 0 30 CLERK 7698 1981-12-03 950 30 SALESMAN 7698 1981-02-22 1250 500 30 ANALYST 7566 1981-12-03 3000 20 CLERK 7902 1980-12-17 800 20 ANALYST 7566 1987-04-19 3000 20 CLERK 7788 1987-05-23 1100 20 CLERK 7782 1982-01-23 1300 10


- 33 -

1234 János2(b1) SALESMAN 1234 Éva2(b2) TANULÓ 1234 Ágnes2(b3) SALESMAN

7698 2005-12-11 7698 2005-12-11 7698 2005-12-11

1050 1750 2000

30 20 40

17 sor kijelölve. A visszaállítás befejeződött. EMPNO ---------7839 7698 7782 7566 7654 7499 7844 7900 7521 7902 7369 7788 7876 7934

ENAME ---------KING BLAKE CLARK JONES MARTIN ALLEN TURNER JAMES WARD FORD SMITH SCOTT ADAMS MILLER

JOB MGR HIREDATE SAL COMM DEPTNO --------- ---------- ---------- ---------- ---------- ---------PRESIDENT 1981-11-17 5000 10 MANAGER 7839 1981-05-01 2850 30 MANAGER 7839 1981-06-09 2450 10 MANAGER 7839 1981-04-02 2975 20 SALESMAN 7698 1981-09-28 1250 1400 30 SALESMAN 7698 1981-02-20 1600 300 30 SALESMAN 7698 1981-09-08 1500 0 30 CLERK 7698 1981-12-03 950 30 SALESMAN 7698 1981-02-22 1250 500 30 ANALYST 7566 1981-12-03 3000 20 CLERK 7902 1980-12-17 800 20 ANALYST 7566 1987-04-19 3000 20 CLERK 7788 1987-05-23 1100 20 CLERK 7782 1982-01-23 1300 10

14 sor kijelölve.


- 34 -

Melléklet

Dinamikus SQL

Dinamikus SQL PROMPT >>> első példa >>> SET SERVEROUTPUT ON ACCEPT azon PROMPT 'Kérem a keresendő név azonosítóját: ' DECLARE nev VARCHAR2(20); azon NUMBER(4); sqlsztring VARCHAR2(100); BEGIN sqlsztring := 'SELECT ename FROM emp WHERE empno = :1'; EXECUTE IMMEDIATE sqlsztring INTO nev USING &azon; DBMS_OUTPUT.PUT_LINE('A keresett név: end; /

'||nev);

SQL> @ aa1 Adja meg a(z) azon értékét: 7788 régi 10: USING &azon; új 10: USING 7788; A keresett név: SCOTT A PL/SQL eljárás sikeresen befejeződött.

Interaktív dinamikus sql. A lekérdezést, vagy PL/SQL blokkot szrtingként adjuk meg a végrehajtó blokkban. Hivás az EXECUTE IMMEDIATE és a Select vagy PL/SQL blokk sztringként Amennyiben interaktívan akarjuk használni a dinamikus SQL-t, akkor & -tel hivatkozunk rá. jobb megoldás ha paraméterezetten használjuk. Ekkor a paraméterek sorszámát adjuk meg a SELECT-ben, és a USING záradék után írjuk a paramétert. PROMPT >>>> második példa >>>> ACCEPT azon PROMPT 'Kérem a keresendő rekord azonosítóját: ' DECLARE nev Varchar2(20); azon NUMBER(4); foglalkoz emp.job%type; fizetes emp.sal%TYPE; reszleg emp.deptno%TYPE; sqlsztring VARCHAR2(100); BEGIN sqlsztring := 'SELECT ename, job, sal, deptno '|| 'FROM emp'|| ' WHERE empno = :1'; EXECUTE IMMEDIATE sqlsztring INTO nev,foglalkoz, fizetes, reszleg USING &azon; #13_PL-SQL Feladatgyűjtemény (07)

- 35 -

DBMS_OUTPUT.PUT_LINE (' A keresett ember rekordja: '); DBMS_OUTPUT.PUT_LINE ('*********************************'); DBMS_OUTPUT.PUT_LINE (nev ||' '|| foglalkoz ||' '|| fizetes ||' '|| reszleg); end; / SQL> @ aa >>>> második példa >>>> Kérem a keresendő rekord azonosítóját: 7654 régi 14: USING &azon; új 14: USING 7654; A keresett ember rekordja: ********************************* MARTIN SALESMAN 1250 30 A PL/SQL eljárás sikeresen befejeződött.


- 36 -

IRODALOM [1] Kende M. – Kotsis D. – Nagy I.: Adatbázis-kezelés az Oracle-rendszerben, Panem, 2002. [2] Kende M. – Nagy I.: Oracle Példatár (SQL, PL/SQL), Panem, 2005.


- 37 -

2012. április 27. TARTALOM

Recommend Documents