Hajas Csilla Adatbazisok-I.gyakorlat

10.GYAKORLAT (ADATBÁZISOK)

Témakör: Oracle PL/SQL témakör - 2.gyakorlata: Oracle Példatár 9.fej.pdf
SQL DML utasítások PL/SQL programban, kurzorok, kurzorattribútumok

- 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

   2.rész: DML utasítások programban, 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
>> 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. FOR LOOP utasítás Implicit kurzor, Explicit kurzor
>> példa-6-41 6.6.6.3 FOR UPDATE kurzor módosításhoz - hibák javítása:
-- 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]

PL/SQL 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.