10.GYAKORLAT (ADATBÁZISOK)
Témakör: PL/SQL--2: Statikus SQL,
kurzorok 6.fej.
Static SQL/Cursors
>> I.RÉSZ
- DML és TCL utasítások programban, implicit kurzor attribútumok
>> II.RÉSZ
-
Lekérdezések, explicit kurzorok, lásd még Példatár 9.fej.pdf
>> III.RÉSZ
- PL/SQL gyakorló feladatsor +Példatár
Feladatok.pdf/9.fej.
-
Oracle PL/SQL doksi: Oracle
Database 12.2 - PL/SQL
Language Reference
Ez az egyetlen megengedett
segédlet, amit a ZH írás
alatt
is használhatunk
.pdf-ben: https://docs.oracle.com/../database-pl-sql-language-reference.pdf
>> a
doksiból nézünk példákat:
List
of Examples (PL/SQL Lang.Ref.12.2)
>> 10.gyakorlaton a 6.fejezet
lényegi
részét, fontos példáit
dolgozzuk fel.
I.RÉSZ - DML prog.ban, implicit kurzorok
>> 6.fej.
Static SQL/Cursors
-- Ismétlés:
1-2.feladatok, 9.gyakorlatról, lásd példa-2-25 2.6.2
SELECT INTO
>> példa-6-1
6.1.1 DML és TCL utasítások a
programban, implicit
kurzor
>> példa-6-4
6.2.1.4 Implicit kurzor attribútumok SQL%ROWCOUNT
II.RÉSZ - Lekérdezések,
explicit kurzorok
>> 6.fej.
Static SQL/Cursors
>> példa-6-6
6.2.2.3 Fetching, explicit
kurzorok deklarálása
és használata
>> példa-6-11
6.2.2.6 Paraméteres explicit kurzorok
>> példa-6-17
6.2.2.7.4 Explicit kurzor attribútumok, c%ROWCOUNT
>> példa-6-18-példa-6-20
6.3.2. Kurzorhasználat FOR-ciklusban (zh-án ne!)
>> folyt.köv.gyak: FOR UPDATE kurzor >> 6.fej.
Static SQL/Cursors
-- Jön: példa-6-41
6.6.6.3 FOR UPDATE
kurzor módosításhoz, de javítások:
-- Ne adjon ki COMMIT
utasítást a FOR UPDATE
módosításra megnyitott
kurzor
lekérdezési ciklusán belül,
mert a ciklus második
lépésében a
FETCH nem
működik olyan kurzorra, amely már nem
érvényes (hiba).
--
Módosítható kurzort
használjunk, ha a sorok értékeit
módosítani vagy
a sorokat
törölni szeretnénk, ekkor
a kurzorban a FOR UPDATE
záredékot használjunk.
Módosítás esetén a FETCH
által utoljára lehívott,
azaz
aktuális sor azonosítása a CURRENT
OF
kurzor záradékkal történik.
-- A végén
zárja be a
kurzort. (Javítsuk ki a fenti hibákat,
próbáljuk ki.)
-- Figyelem! Ennek a
demó
példa lefuttatásánál a
PL/SQL blokk előtti
drop
table emp; create table emp as select * from hr.employees;
felülírja
az Oracle Példatár feladatainál
használt korábbi emp
táblát,
-- ezért emp
helyett emp_temp táblát célszerűbb
használjunk, vagy utána
az Oracle Példatár
feladataihoz: cr_dept_emp.txt
scriptet újra lefuttatjuk.
[Megj.: A
feladatsorban viszont
a korábbi táblákban
dolgozunk: createDolg]
III.RÉSZ
- PL/SQL GYAKORLÓ FELADATSOR --2
-- A feladatokat most is a
saját
Dolgozo-Osztaly táblákra kell
megírni, ehhez
a
táblákat létrehozó script,
mint a DML-hez volt: createDolg
(no
constraint)
--
A képernyőre való kiíráshoz
a
DBMS_OUTPUT.PUT_LINE() használatához,
a PL/SQL blokk
előtt állítsuk be: SET SERVEROUTPUT ON
-- -- -- --
-- 1.)
/* SELECT ... INTO v1
Írjunk meg egy függvényt, amelyik
visszaadja egy
adott fizetési kategóriába
tartozó
dolgozók átlagfizetését,
lásd példa-2-25 2.6.2
SELECT INTO
*/
CREATE OR REPLACE FUNCTION kat_atlag(n integer) RETURN number IS
Tesztelés:
SELECT kat_atlag(2) FROM dual;
-- -- -- --
-- 2.)
/* SELECT ... INTO v1, v2
Írjunk meg egy procedúrát, amelyik
kiírja
azon dolgozók számát és
átlagfizetését,
akiknek a belépési dátuma a
paraméterül megadott nevű napon (pl.
'Hétfő') volt.
*/
CREATE OR REPLACE PROCEDURE nap_atl(d varchar2) IS
...
set serveroutput on
call nap_atl('Csütörtök'); --
példa output: Dolgozók száma: 4,
Átlag fiz: 2481,25
Tippek:
1. to_char(hiredate, 'Day', 'nls_date_language=hungarian')
2. vigyázzunk a to_char által visszaadott nap
névre, a végén
szóközök lehetnek
-- -- -- --
-- 3.)
/* Insert, Delete, Update
Adjuk meg egy főnök
azonosítóját, és
töröljük a
közvetlen beosztottjait, és
írassuk ki hány beosztottja volt,
lásd példa-6-4
implicit kurzor attribútumok.
-- -- --
--4.)
/* Insert, Delete, Update
Írjunk meg egy procedúrát, amelyik
megnöveli
azoknak a dolgozóknak a fizetését,
akiknek a fizetési kategóriája
ugyanaz, mint a
procedúra paramétere.
A növelés
mértéke a dolgozó
osztályában előforduló legkisebb
fizetés legyen.
A procedúra a módosítás
után
írja ki a módosított (új)
fizetések
átlagát
két tizedesjegyre kerekítve.
*/
CREATE OR REPLACE PROCEDURE kat_novel(p_kategoria NUMBER) IS
...
set serveroutput on
execute kat_novel(2);
-- -- -- --
-- 5.)
/* Cursor, több
sort
visszaadó
lekérdezés, kurzor
használata, lásd példa-6-6
Írjuk ki a dolgozók
nevét
és fizetését!
*/
-- -- -- --
-- 6.)
/* Cursor, több soros select, kurzor attribútumok,
lásd példa-6-17
Írjuk ki a 3. 5.
és 8. legnagyobb
fizetésű dolgozó nevét,
fizetését!
*/
-- -- -- --
-- 7.)
/* Cursor, több soros select,
felh.változók: &num_valt,
'&char_valt'
Írjuk ki azon
dolgozók nevét
és fizetését, akik fizetése
nagyobb mint
egy olyan
szám, amelyet a
felhasználó fog majd futás
közben megadni!
*/
-- -- -- --
-- 8.)
/* Cursor, több soros select,
felh.változók: &num_valt,
'&char_valt'
Í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,
vagyis az onev (Accounting,
...) kezdőbetűje a 10, 20,
30-as
osztály esetén.
*/
-- -- -- --
-- 9.)
/* Cursor, több soros select, paraméteres kurzor,
lásd példa-6-11
Írjunk meg egy procedúrát, amelyik
veszi a
paraméterül megadott osztály
dolgozóit
ábécé szerinti sorrendben,
és kiírja
a foglalkozásaikat egy karakterláncban
összefűzve.
*/
CREATE OR REPLACE PROCEDURE print_foglalkozas(o_nev varchar2) IS
...
set serveroutput on
call print_foglalkozas('ACCOUNTING');
-- példa output: MANAGER-PRESIDENT-CLERK
-- -- -- --
-- 10.)
/* Az előző feladatot most függvényként
írjuk meg, majd hívjuk meg a
beadandóhoz.
Írjunk meg egy függvényt, amelyik veszi
a paraméterül megadott osztály
dolgozóit
ábécé szerinti sorrendben,
és visszaadja a foglalkozásaikat egy
karakterláncban
összefűzve.
*/
CREATE OR REPLACE FUNCTION get_foglalkozas(o_nev varchar2) RETURN
varchar2 IS
...
SELECT get_foglalkozas('ACCOUNTING') FROM dual;
-- példa output: MANAGER-PRESIDENT-CLERK
További PL/SQL
gyakorló feladatok az Oracle
Példatárból
Fejezze be önállóan
a fenti
feladatokból a hiányzó feladatokat, a
köv.gyakorlaton
folytatjuk az Oracle
Példatár Feladatok.pdf
9.fejezet feladatait a köv.órán
folytatjuk.
[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 a
korábbi dolgozo,
osztaly tábláinkat!]
+1.) 9.3. Feladat: Hozzunk
létre a
dolgozo táblából egy dolg_temp
táblát, majd
írjunk PL/SQL programot,
amely megnöveli ebben a felhasználó
által
megadott
foglalkozású dolgozók
fizetését 1000 USD-ral.
+2.) 9.5. Feladat: Hozzunk létre a
dolgozo
táblából egy dolg_temp
táblát, majd
írjunk PL/SQL programot,
amely megnöveli ebben a felhasználó
által
megadott
százalékértékkel minden, az
átlagfizetésnél alacsonyabb
fizetéssel
rendelkező
dolgozó fizetését.
+3.) 9.6. Feladat: Hozzunk létre a
dolgozo
táblából egy dolg_temp
táblát, majd
írjunk PL/SQL programot,
amely foglalkozásonként megnöveli ebben
a legkisebb
fizetésű dolgozók
bérét a foglalkozási csoportjukban
legnagyobb
fizetés és
az ugyanitt
számított
átlagfizetés
különbségének
20%-ával.
+4.) 9.8. Feladat: Hozzunk létre a
dolgozo
táblából egy dolg_temp
táblát, majd
írjunk PL/SQL programot, amely megnöveli ebben
azoknak a főnököknek a
fizetését, akiknek egynél
több beosztottjuk van.
A
növelés
mértéke: annyiszor N USD, ahány
beosztottjuk van.
Az N értékét
a
felhasználó adja
meg. A fizetés csökkenő
értéke szerint
rendezve listázza
a
főnököket.
+5.) 9.9. Feladat: Hozzunk létre a
dolgozo
táblából egy dolg_temp
táblát, majd
írjunk PL/SQL programot, amely megnöveli ebben
azoknak a dolgozóknak
a
fizetését, akiknek
az azonosítója páros és
fizetésük kisebb, mint e csoport
átlagfizetése. A
növelés e
fizetéskülönbség 20%-a.