VIK Wiki - Felhasználó közreműködései [hu]

AdatbServerProgJegyzet20060414

2013-03-31T19:04:18Z

2001:738:2001:2078:0:208:37:0: Átirányítás ide: Adatbázisok szerver oldali programozása Előadás, 2006.04.14.

#REDIRECT [[Adatbázisok_szerver_oldali_programozása_Előadás,_2006.04.14.]]

Adatbázisok szerver oldali programozása Előadás, 2006.04.14.

2013-03-31T19:04:16Z

2001:738:2001:2078:0:208:37:0: Új oldal, tartalma: „{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060414}}  <style> code.pre { white-space: pre; display:block; me…”

{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060414}}



<style>
code.pre { white-space: pre; display:block; mergin-top: 2px; margin-bottom: 2px;}
li { line-height: 18px; }
</style>

<br/> [[AdatbServerProgJegyzet20060407|<<]] Előadás, 2006.04.14. [[AdatbServerProgJegyzet20060421|>>]]

__TOC__

==Dinamikus SQL==

Az '''''EXECUTE IMMEDIATE''''' segítségével stringet lefuttathatunk SQL utasításként.

Tipikus hasznalata:
* DDL, DCL utasitások tárolt eljárásban
* lekérdezés dinamikus összeállítása

===Előnyei:===
* DDL, DCL utasítások
* '''WHERE''' feltétel dinamikusan összeállítható
* egész utasítás dinamikusan összeállítható

===Hátrányai:===
* nincs előoptimalizálva
* végrehajtási terv futtatáskor készül el
* csak futtatáskor van szintaktikai ellenőrzés
* csak futtatáskor van jogoultság ellenőrzés

===Szintaxis:===
<pre>
EXECUTE IMMEDIATE dinamikus_string
[INTO változó1, változó2, ...]
[USING [{IN | OUT | IN OUT}] kötés1,
[{IN | OUT | IN OUT}] kötés2, ...]
[{RETURN | RETURNING} INTO kötés1, kötés2, ...]
</pre>

* A dinamikus sztring tartalmazza a lefuttatandó SQL utasítást.
* Az opcionális INTO utáni változókba kerülnek be az adatok, ha az SQL utasítás visszatér valamilyen egysoros eredménnyel.
* Az opcionális USING kulcsszó után bemenő paramétereket lehet megadni, amiket a lekerdezesen belül ott lehet hasznalni, ahol egy SQL utasitasban kifejezést lehet hasznalni. A kimenő paraméterek visszatérési értéket kaphatnak.

Dinamikus SQL string lehet
* egy utasítás (; nélkül)
* PL/SQL blokk (;-vel)

====Pl.:====
<pre>
1.)
DECLARE
sql_stmt VARCHAR2(500);
nev, anyag, anyag_nev %TYPE;
anyagid INT;
mennyiseg INT;
BEGIN
sql_stmt := 'SELECT anyag_nev
FROM anyag
WHERE anyag_id = :1';
EXECUTE IMMEDIATE sql_stmt
INTO nev
USING 3;

sql_stmt := 'UPDATE anyag
SET anyag_raktardb = anyag_maxraktar-anygag_minraktar
WHERE anyag_id = :x
RETURNING anyag_raktardb INTO :2';
EXECUTE IMMEDIATE sql_stmt
USING 2
RETURNING INTO mennyiseg;
END;

2.)
DECLARE
sql_stmt VARCHAR2(500);
nev
egysegnev
ar
TYPE anyag_antype IS REF CURSOR;
anyag_an anyag_antype;
BEGIN
sql_stmt := 'SELECT anyag_nev, anyag_egysegar, egyseg_nev
FROM anyag, egyseg
WHERE anyag_egyseg = egyseg_id AND angyag_id BETWEEN :1 AND :2';
OPEN anyag_an FOR sql_stmt USING 2, 15;
LOOP
FETCH anyag_an INTO nev, ar, egysegnev;
EXIT WHEN anyag_qan%NOTFOUND;
DBMS_OUTPUT(...);
END LOOP;
CLOSE anyag_an;
END;

3.)
CREATE PROCEDURE delete_r (table_name VARCHAR2, condition VARCHAR2 DEFAULT NULL)
IS
where_feltetel VARCHAR2(100) := 'WHERE ' || condition;
BEGIN
IF condition IS NULL THEN where_feltetel := NULL;
END IF;
EXECUTE IMMEDIATE 'DELETE FROM "' || table_name || '" ' || where_feltetel;
END;
</pre>

==Eljárás futtatása==

BEGIN
delete_news('anyag', 'anyagnev LIKE''%O%''');
END

'''vagy'''

Command window-ban:
EXEC delete_news();

==Data dictionary (csak olvasható nézertek)==

* séma objektumok definiciója
* séma objektumok helyfoglalási adatai
* oszlopok ''default'' értéka
* ''constraint'' információk
* felhasználók nevei, jogosultságai
* általános DB információk

===USER_TABLES, ALL_TABLES nézetek:===
* owner, table_name, temporary, ...
* csak a relációs táblákat tartalmazza
===USER_ALL_TABLES, ALL_ALL_TABLES===
* az összes objektumot tartalmazza
* + tabla_type attributum
===ALL_TAB_COLUMNS, USER_TAB_COLUMNS===
* table_name, column_name, data_type, data_length, data_precision, nullable, ...
* elsőhöz meg a owner is van
===ALL_TAB_COMMENTS, USER_TAB_COMMENTS===
* table_name, table_type, comments
* elsőhöz owner
* comment felvétele: <pre>COMMENT ON anyag IS '...';</pre>
===ALL_COL_COMMENTS, USER_COL_COMMENTS===
* <pre> COMMENT ON COLUMN anyag.anyag_id IS '...';</pre>
===ALL_CONSTRAINTS, USER_CONSTRAINTS===
* (owner), constraint_name, constraint_type (C check, P primary, U, R, V with check option, O read-only nézet), r_owner, r_constraint_name
(with check option: csak olyan sorokat enged beszúrni a nézetbe, amire teljesül a nézet definiáló SELECT WHERE feltétele)
===USER_TRIGGERS, ALL_TRIGGERS===
===USER_ERRORS, ALL_ERRORS===

-- [[StMa|Matyi]] - 2006.06.09.

[[Category:Valaszthato]]

AdatbServerProgJegyzet20060516

2013-03-31T19:04:15Z

2001:738:2001:2078:0:208:37:0: Átirányítás ide: Adatbázisok szerver oldali programozása Labor, 2006.05.16.

#REDIRECT [[Adatbázisok_szerver_oldali_programozása_Labor,_2006.05.16.]]

Adatbázisok szerver oldali programozása Labor, 2006.05.16.

2013-03-31T19:04:12Z

2001:738:2001:2078:0:208:37:0: Új oldal, tartalma: „{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060516}}  <style> code.pre { white-space: pre; display: block; m…”

{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060516}}



<style>
code.pre { white-space: pre; display: block; margin-top: 2px; margin-bottom: 2px; }
li { line-height: 18px; }
</style>

<br/> [[AdatbServerProgJegyzet20060512|<<]] Labor, 2006.05.16.

1. Mire jó a =%TYPE= és a =%ROWTYPE=?
* =%TYPE=: oszlop típusa
* =%ROWTYPE=: sor típus

Előny: nem kell emlékezni a típusra (típus hosszra), típus egyszerűen megváltoztatható
<code class="pre">DECLARE
a anyag_nev%TYPE
b anyag_nev%ROWTYPE</code>

2. Nested table vs index by tabl

{| border="1"
| || '''nested''' || '''index by'''
|-
| '''méret''' || korlátlan, de EXTEND-del kell bővíteni || korlátlan
|-
| '''index sűrű vagy ritka?''' || sűrű, de lehet ritka is || ritka
|-
| '''init nélkül''' || NULL || üres
|}

3. Kurzornak hogy tudunk paramétert átadni? Hogyan kell deklarálni és használni?
<code class="pre">DECLARE CURSOR c1(param típus) IS SELECT ... WHERE param=oszlop
BEGIN
OPEN c1(érték)
...</code>

4. Oracle kivételek csoportosítása
* rendszer
** névvel ellátott
** név nélküli
* user
** felhasználói
* RAISE_APPLICATION_ERROR

5.
Hatáskör def: ahol tudunk a változóra hivatkozni
Láthatóság: ahol az egyszerű nevével tudunk rá hivatkozni (az a blokk, ahol deklaráltuk)

<code class="pre"><<címke>>
DECLARE a INT
BEGIN
DECLARE a VARCHAR(2);
BEGIN
címke.a
a
END
END</code>

6. Típus precedencia, mi a szerepe?
Típusok sorba vannak rendezve, az operátorok a magasabb precedenciájú típussal térnek vissza

7.
CLOSE:
DEALLOCATE:

8. inserted, delete átmeneti táblákkal

9. Hogyan adhat vissza információt egy tárolt eljárás SQL szerveren?
* státusz INT
* eredményhalmaz
* kimeneti paraméterek
* globális kurzor
Tárolt eljárás futtatása: EXEC [@status=]eljárásnév param1, param2, ...

10

{| border="1"
| '''kurzor''' || '''kurzorváltozó'''
|}

kurzor:
<code class="pre">DECLARE
CURSOR c[(param)] IS SELECT...
BEGIN
OPEN c(5);</code>

kurzor változó:
<code class="pre">DECLARE
TYPE c_t IS REF CURSOR;
c c_t;
BEGIN
OPEN c1 FOR SELECT ...</code>

Közös kód:
<code class="pre"> LOOP
FETCH c INTO valt
EXIT WHEN c%NOTFOUND
END LOOP
CLOSE c</code>

kurzor: FOR ciklus csak erre használható
kurzor változó: másik lekérdezésre is leírható

11. Tranzakciós módok SQL Serveren

{| border="1"
| || '''magyarázat''' || '''Hogyan váltunk oda?'''
|-
| '''auto commit''' || Minden utasítás külön tranzakció || default vagy SET IMPLICIT_TRANSACTIONS OFF
|-
| '''explicit''' || explicit kezdet és vég || BEGIN TRAN ... COMMIT/ROLLBACK
|-
| '''implicit''' || adatmódosító utasítás új tranzakciót nyit, COMMIT/ROLLBACK zárja le || SET IMPLICIT_TRANSACTIONS ON
|}

12. Naplózzuk, ha egy diáknak módosul az osztályzata
<code class="pre">CREATE TABLE naplo (
naploid INT PRIMARY KEY,
diakid CHAR(5),
examid INT,
datum DATE,
targynev VARCHAR(50),
targykod CHAR(8),
regi INT,
uj INT
);</code>

<code class="pre">CREATE TRIGGER naplozo
AFTER UPDATE ON student_exam
FOR EACH ROW
WHEN (old.sx_grade != new.sx_grade)
DECLARE
tnev VARCHAR(50);
tkod CHAR(8);
BEGIN
SELECT sub_name, sub_id INTO tnev, tkod
FROM subject, course, exam
WHERE sub_id = cou_subjectid
AND ex_courseid = cou_id
AND ex_id = :new.sx_examid;
INSERT INTO naplo VALUES(
nep_seq.NEXTVAL,
:new.sx_studentid,
:new.sx_examid,
sysdate,
tnev,
tkod,
:old.sx_grade,
:new.sx_grade);
END;</code>

13.
<code class="pre">create or replace trigger terem_trigger
before insert on teacher_room
for each row
declare
def int;
teadept char(4);
roomdept char(4);
begin
if (:new.tr_default = 1) then
select count(tr_default) into def from teacher_room
where tr_teacherid = :new.tr_teacherid and tr_default=1;
if (def=0) then
select tea_dept into teadept from teacher where tea_id=:new.tr_teacherid;
select room_dept into roomdept from room where room_id=:new.tr_roomid;
if (teadept != roomdept) then
raise_application_error(-20102, 'Rossz épületben van');
end if;
else
raise_application_error(-20101, 'Már van alapértelmezett terem');
end if;
else
select count(*) into def from teacher_room
where tr_teacherid=:new.tr_teacherid and tr_default=0;
if (def>3) then
raise_application_error(-20103, 'Túl sok terem');
end if;
end if;
end terem_trigger;</code>

14.

<code class="pre">create or replace procedure anyag_listazas(anev varchar2, fszam out int, tszam out int) is
cursor c1 is select felkesz_nev from felkesz, felkesz_anyag, anyag
where anyag_nev=anev and anyag_id=fa_anyagid and felkesz_id=fa_felkeszid;
cursor c2 is select termek_nev from termek, termek_anyag, anyag
where anyag_nev=anev and anyag_id=ta_anyagid and termek_id=ta_termekid;
begin
-- ugyanez a termékre is
 
fszam:=0;
dbms_output.put_line('A ' ||| anev || ' a következõ félkészekben szerepel');
for c1_rec in c1 loop
dbms_output.put_line(' ' | c1_rec.felkesz_nev);
fszam := c1%rowcount;
end loop;
if (fszam=0) then
dbms_output.put_line(' -----');
end if;
end anyag_listazas;</code>

16.
<code class="pre">create procedure listazas
as
declare c1 cursor for select sub_id, sub_name from subject
declare @subid char(8), @subname char(50)
open c1
fetch c1 into @subid, @subname
while @@fetch_status=0
begin
print @subid + ' ' + @subname
declare c2 cursor for
select distinct cou_semestername from course where cou_subjectid=@subid
declare @sem char(5)
open c2
fetch c2 into @sem
while @@fetch_status=0
begin
print ' ' + @sem
fetch c2 into @sem
end
deallocate c2
fetch c1 into @subid, @subname
end
deallocate c1
go</code>

-- [[PallosPeter|Peti]] - 2006.05.16.

[[Category:Valaszthato]]

AdatbServerProgJegyzet20060509

2013-03-31T19:04:10Z

2001:738:2001:2078:0:208:37:0: Átirányítás ide: Adatbázisok szerver oldali programozása Labor, 2006.05.09.

#REDIRECT [[Adatbázisok_szerver_oldali_programozása_Labor,_2006.05.09.]]

Adatbázisok szerver oldali programozása Labor, 2006.05.09.

2013-03-31T19:04:08Z

2001:738:2001:2078:0:208:37:0: Új oldal, tartalma: „{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060509}}  <style> code.pre { white-space: pre; display: block; m…”

{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060509}}



<style>
code.pre { white-space: pre; display: block; margin-top: 2px; margin-bottom: 2px; }
li { line-height: 18px; }
</style>

<br/> [[AdatbServerProgJegyzet20060505|<<]] Labor, 2006.05.09. [[AdatbServerProgJegyzet20060512|>>]]

==T-SQL Triggerek, Kivételek==

<ol>

<li> Írj triggert, ami csak akkor enged felvenni egy kurzust egy hallgatónak, ha a limitnél
kevesebben jelentkeztek eddig rá! Tegyük fel, hogy csak kliens oldalról
engedélyezünk kurzus felvételt, így egyszerre biztosan egy sort szeretnénk beszúrni.

<code class="pre">CREATE TRIGGER courselimit ON student_course INSTEAD OF INSERT
AS
DECLARE @limit INT, @studentcount INT
SELECT @limit=cou_limit FROM course, inserted
WHERE cou_id=inserted.sc_courseid
SELECT @studentcount=COUNT(*) FROM student_course, inserted
WHERE student_course.sc_courseid=inserted.sc_courseid
IF (@studentcount<@limit)
INSERT INTO student_course SELECT * FROM inserted
ELSE
RAISERROR('kurzus betelt', 10, 1)
GO</code>

'''Tesztelés:'''

<code class="pre">UPDATE course SET cou_limit=10 WHERE cou_id=6
INSERT INTO student_course (sc_studentid, sc_courseid, sc_date)
VALUES ('SDFQWE', 6, getdate())</code>

<li> Oldd meg az előző feladatot úgy, hogy feltesszük, hogy egyszerre több sort is be
tudunk szúrni a táblába!

<code class="pre">CREATE TRIGGER courselimit ON student_course INSTEAD OF INSERT
AS
SET NOCOUNT ON
DECLARE @limit INT, @studentcount INT
DECLARE @studentid CHAR(6), @courseid INT, @date DATETIME
DECLARE cc CURSOR FOR
SELECT * FROM inserted
OPEN cc
FETCH cc INTO @studentid, @courseid, @date
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @limit=cou_limit FROM course WHERE cou_id=@courseid
SELECT @studentcount=COUNT(*) FROM student_course
WHERE sc_courseid=@courseid
IF @studentcount<@limit
BEGIN
INSERT INTO student_course VALUES (@studentid, @courseid, @date)
print @studentid + ' jelentkezett a ' + convert(VARCHAR, @courseid) + ' kurzusra'
END ELSE
print @studentid + ' nem fért bele a ' + convert(VARCHAR, @courseid) + ' kurzusba'
FETCH cc INTO @studentid, @courseid, @date
END
CLOSE cp
DEALLOCATE cp
GO</code>

'''Tesztelés:'''

<code class="pre">UPDATE course SET cou_limit=10 WHERE cou_id=6
DELETE FROM student_course WHERE sc_courseid=6
INSERT INTO student_course SELECT stud_id, 6, getdate() FROM student</code>

<li> Írj tárolt eljárást, amivel egy diák egy kurzusra tud jelentkezni. Az eljárás bemenő
paramétere a diák neptun kódja, a szemeszter kód és a tárgy kódja legyen. Az
eljáráson belül kapd el a hibákat, és írasd ki, hogy mi volt a hiba! Próbáld ki az
eljárást különböző hibákat generálva!

<code class="pre">CREATE PROCEDURE targyjelentkezes
@student CHAR(6),
@subject CHAR(8),
@semester CHAR(5),
@coursenumber INT
AS
SET NOCOUNT ON
BEGIN TRY
INSERT INTO student_course
SELECT @student, cou_id, getdate() FROM course
WHERE @subject=cou_subjectid
AND @semester=cou_semestername
AND @coursenumber=cou_coursenumber
IF @@rowcount=0
RAISERROR('nincs ilyen kurzus', 10, 1)
END TRY
BEGIN CATCH
IF @@ERROR=0
print 'ok'
ELSE IF ERROR_NUMBER()=2627
print 'többszörös jelentkezés'
ELSE BEGIN
print 'egyéb hiba'
print ERROR_NUMBER()
print ERROR_MESSAGE()
END
END CATCH
GO</code>

'''Tesztelés:'''

<code class="pre">EXEC targyjelentkezes 'XSFXCF', 'VIFO1016', '20012', 5</code>

<li> Készíts egy nézetet, ami a következ- nev2 oszlopokat tartalmazza: név, mennyiseg,
egység, ár! Az egység a nézetben ne kóddal jelenjen meg, hanem megnevezéssel! Az
ár származtatott értékként kerüljön kiszámításra a nézeten belül. Írj triggert, aminek
segítségével új félkész terméket lehet felvenni az adatbázisba a létrehozott nézeten
keresztül! Amennyiben a beadott egység még nem szerepel az egység táblában, úgy
az kerüljön felvételre. A beadott ár értéket ignoráld, mivel az ár egy származtatott
érték!

<li> A VIAU9159 nev2 C# project MainClien.exe futtatható kódja egy korlátozott
funkcionalitásokkal bíró kliens felületet nyújt a tanár-diák adatbázishoz. A feladat a
kliens által meghívott tárolt eljárások megírása, aminek következtében a programot
lehet használni.

A program m2ködése:

A program segítségével a különböz- diákokhoz tárgyakat lehet felvenni, illetve
leadni.
A belépéskor a felhasználói név és a jelszó megadása után megjelenik egy felület, ami
különböz- listákat tartalmaz.
A bal oldali lista a diákok listája. Ennek feltöltésére a program a getStudents
tárolt eljárást hívja meg. A tárolt eljárás visszatérése egy adathalmaz, aminek az els-
oszlopában a diák azonosítója áll, a másodikban pedig az, amit meg szeretnénk
jeleníteni a listában.

A félév kódja legördül- menü feltöltéséhez a getSemesters tárolt eljárást kell
meghívni. Ennek is a visszatér- adathalmazának els- oszlopa tartalmazza a
szemeszter kódját, a második oszlop pedig a megjelenítend- szöveget. Itt érdemes
használni az el-z- gyakorlatok egyikén megírt szemeszter név konvertáló függvényt.
Ha kiválasztunk egy szemesztert, az adott szemeszterhez az alsó listában felsorolásra
kerülnek az adott szemeszter kurzusai. Ezt a getCoursesOfSemester
@semester char(5) tárolt eljárás hívásával lehet megtenni. Az eljárás, mint az
eddigiek két oszloppal tér vissza. Az els-ben a kurzus azonosítója áll, a másodikban a
megjelenítend- szöveg. Itt érdemes a tárgy kódját, a tárgy nevét, a kurzus típusát
(el-adás, gyakorlat vagy labor a CASE függvény használatával), tárgyért felel-s tanár
nevét kiíratni.

A legfels- listában a kiválasztott diák által az adott félévben felvett kurzusok
szerepelnek. Ezt a getStudentsCourses @studid char(6), @semester
char(5) tárolt eljárás hívásával lehet megoldani. Az eljárás azonos módon kell,
hogy visszaadja az adatokat, mint a kurzust listázó eljárás.
A jelentkezik gomb megnyomásakor a kiválasztott hallgatóhoz felvételre kerül a
kiválasztott kurzus. Ezt az addCourseToStudent @studid char(6),
@couid int eljárás hívásával lehet megtenni. Az eljárásnak le kell ellen-riznie,
hogy még befér-e a hallgató az adott kurzusra. Ha igen, fel kell venni, ha nem, akkor
egy hibát kell generálni a kliens felé. Ügyelj a tranzakció kezelésre is!
A lead gombbal a kiválasztott hallgató kiválasztott kurzusát lehet törölni a listából.
Ezt a deleteCourseFromStudent @studid char(6), @couid int
tárolt eljárással lehet megtenni.

Írd meg a szövegben megjelölt tárolt eljárásokat! Ügyelj a tranzakció kezelésre és a
hiba kezelésre! Hibát a RAISERROR függvénnyel lehet dobni a kliens felé (a
részleteket nézd meg a helpben!)

<li> Módosítsd úgy a szemeszter kurzusainak listáját listázó tárolt eljárást, hogy
tartalmazza a kurzus létszámkorlátját, és a jelentkezettek számát is!
</ol>

-- [[PallosPeter|Peti]] - 2006.05.09.

[[Category:Valaszthato]]

AdatbServerProgJegyzet20060502

2013-03-31T19:04:07Z

2001:738:2001:2078:0:208:37:0: Átirányítás ide: Adatbázisok szerver oldali programozása Labor, 2006.05.02.

#REDIRECT [[Adatbázisok_szerver_oldali_programozása_Labor,_2006.05.02.]]

Adatbázisok szerver oldali programozása Labor, 2006.05.02.

2013-03-31T19:04:01Z

2001:738:2001:2078:0:208:37:0: Új oldal, tartalma: „{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060502}}  <style> code.pre { white-space: pre; display: block; m…”

{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060502}}



<style>
code.pre { white-space: pre; display: block; margin-top: 2px; margin-bottom: 2px; }
li { line-height: 18px; }
</style>

<br/> [[AdatbServerProgJegyzet20060428|<<]] Labor, 2006.05.02. [[AdatbServerProgJegyzet20060505|>>]]

==T-SQL Kurzorok, Tárolt eljárások, Függvények==

<ol>
<li> Írj tárolt eljárást, ami lekérdezi a FUTAM_PILOTA tábla adatait!

<code class="pre">CREATE PROCEDURE list_futam_pilota
AS
SELECT * FROM futam_pilota
GO</code>

<li> Írj tárolt eljárást, aminek bemeneti paramétere egy helyszín pálya neve és a szezon,
eredménye pedig az adott futam eredménye (pilóta neve, helyezése, ideje, teljesített
körök száma)! Az eljárás kimeneti paramétere legyen az eredményhalmaz sorainak a
száma! Írj batch szkriptet, ami meghívja az eljárást, a kimeneti paraméter értékét
pedig kiírja a kimenetre!

<code class="pre">CREATE PROCEDURE eredmenylista
@palyanev VARCHAR(50),
@szezon INT,
@sorokszama INT OUTPUT
AS
SELECT nev, helyezes, ido, teljesitettkorokszama
FROM pilota, futam_pilota, futam, helyszin
WHERE palya = @palyanev
AND ev = @szezon
AND futam_pilota.futamid = futam.futamid
AND futam_pilota.pilotaid = pilota.pilotaid
AND futam.helyszinid = helyszin.helyszinid
ORDER BY ISNULL(helyezes, 9999)
SET @sorokszama = @@ROWCOUNT
GO</code>

<code class="pre">DECLARE @sorokszama INT
EXEC eredmenylista 'Melbourne', 2005, @sorokszama OUTPUT
print @sorokszama</code>

<li> Írj tárolt eljárást, ami a kimenetre listázza (print utasítás) a bemeneti paraméterben
megkapott évben csapatonként a pilóták adatait. Az eljárás státusz információban
jelölje, hogy a bemeneti paraméter alapján sikerült-e eredményt szolgáltatnia, azaz
volt-e legalább egy csapat az adott szezonban! Az eljárást próbáld is ki!

Az eredmény így nézzen ki:

<code class="pre">Renault (Flavio Briatore)
Fernando Alonso (Elsoszamu pilota)
Giancarlo Fisichella (Masodszamu pilota)
Franck Montagny (Teszt pilota)
McLaren Mercedes (Ron Dennis)
Kimi Raikkönen (Elsoszamu pilota)
Juan-Pablo Montoya (Masodszamu pilota)
Alexander Wurz (Teszt pilota)
Pedro de la Rosa (Teszt pilota)
...</code>

<code class="pre">CREATE PROCEDURE csapattaglista
@csapatszezonid INT
AS
DECLARE cp CURSOR FOR
SELECT nev,
CASE pilotatipus WHEN '1' THEN 'Elsőszámú pilóta'
WHEN '2' THEN 'Másodszámú pilóta'
WHEN 'T' THEN 'Teszt pilóta'
END
FROM pilota, csapat_pilota
WHERE csapatszezonid = @csapatszezonid
AND csapat_pilota.pilotaid = pilota.pilotaid
ORDER BY pilotatipus, nev
DECLARE @nev NVARCHAR(50), @pilotatipus VARCHAR(30)
OPEN cp
FETCH cp INTO @nev, @pilotatipus
WHILE @@FETCH_STATUS=0
BEGIN
print ' ' + @nev + ' (' + @pilotatipus + ')'
FETCH cp INTO @nev, @pilotatipus
END
CLOSE cp
DEALLOCATE cp
GO</code>

<code class="pre">CREATE PROCEDURE csapatlista
@szezon INT
AS
DECLARE cc CURSOR FOR
SELECT csapatszezonid, nev, fonoknev
FROM csapat, csapat_szezon
WHERE ev = @szezon
AND csapat_szezon.csapatid = csapat.csapatid
DECLARE @csapatszezonid INT, @nev NVARCHAR(50), @fonoknev NVARCHAR(50)
OPEN cc
FETCH cc INTO @csapatszezonid, @nev, @fonoknev
WHILE @@FETCH_STATUS=0
BEGIN
print @nev + ' (' + @fonoknev + ')'
EXEC csapattaglista @csapatszezonid
FETCH cc INTO @csapatszezonid, @nev, @fonoknev
END
CLOSE cc
DEALLOCATE cc
GO</code>

<code class="pre">EXEC csapatlista 2005</code>

<li> Írj függvényt, ami kiszámítja a bementként megkapott pilóta szintén bemenetként
kapott évben elért eddigi pontszámát! Írj lekérdezést, ami minden pilóta neve mellé
kiírja a pontszámát!

<code class="pre">CREATE FUNCTION pilotapontszam(
@pilota NVARCHAR(50),
@szezon INT
) RETURNS INT
AS BEGIN
DECLARE @pontszam INT
SELECT
@pontszam = SUM(CASE helyezes
WHEN 1 THEN 10
WHEN 2 THEN 8
WHEN 3 THEN 6
WHEN 4 THEN 5
WHEN 5 THEN 4
WHEN 6 THEN 3
WHEN 7 THEN 2
WHEN 8 THEN 1
ELSE 0
END)
FROM futam, futam_pilota, pilota
WHERE ev = @szezon
AND nev = @pilota
AND futam_pilota.futamid = futam.futamid
AND futam_pilota.pilotaid = pilota.pilotaid
RETURN @pontszam
END
GO</code>

<code class="pre">print dbo.pilotapontszam('Jarno Trulli', 2005)</code>

<li> Írj egy tábla visszatérésű függvényt, aminek bemenete egy évszám, a tábla pedig
tartalmazza az adott évben szerződésben álló pilóták addig elért pontjainak az
összegét! A függvényt próbáld is ki!

</ol>

-- [[PallosPeter|Peti]] - 2006.05.02.

[[Category:Valaszthato]]

AdatbServerProgJegyzet20060421

2013-03-31T19:03:59Z

2001:738:2001:2078:0:208:37:0: Átirányítás ide: Adatbázisok szerver oldali programozása Előadás, 2006.04.21.

#REDIRECT [[Adatbázisok_szerver_oldali_programozása_Előadás,_2006.04.21.]]

Adatbázisok szerver oldali programozása Előadás, 2006.04.21.

2013-03-31T19:03:55Z

2001:738:2001:2078:0:208:37:0: Új oldal, tartalma: „{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060421}}  <style> code.pre { white-space: pre; display: block; m…”

{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060421}}



<style>
code.pre { white-space: pre; display: block; mergin-top: 2px; margin-bottom: 2px; }
code.precompact { white-space: pre; display: block; mergin-top: 2px; margin-bottom: 2px; line-height:13px; }
li { line-height: 18px; }
</style>

<br/> [[AdatbServerProgJegyzet20060418|<<]] Előadás, 2006.04.21. [[AdatbServerProgJegyzet20060425|>>]]

__TOC__

==Microsoft SQL Server bevezetés==

===Rendszer objektumok és kapcsolataik===

<code class="precompact">+------= * +-----------+ * +-------------+=
| példány |-------|| adatbázis ||-------|| felhasználó
|}
+------= +-----------+ +-------------+</code>=

===Rendszer adatbázisok===

* Master: rendszer szintű információk
** SQL init adatok
** többi adatbázis helye
* TempDB: átmeneti táblák és tárolt eljárások
* Model: ez alapján jön létre egy új adatbázis
* MSDB: SQL Server Agent használja
** figyelmeztetések
** munkák ütemezése

adat file: *.mdf, *.ndf
<br/>
log file: *.ldf

A rendszer szintű információkat az =sp_= kezdetű tárolt eljárásokkal lehet lekérdezni és müdosítani.

==Identity==

Kulcsgeneráló objektum. 1 táblának legfeljebb 1 identity oszlopa lehet.

<code class="pre">CREATE TABLE anyag (
anyag_id INT IDENTITY PRIMARY KEY,
anyag_nev VARCHAR(50) NOT NULL,
lejar DATETIME
)</code>

Megadható a kulcs kezdőértéke és növekménye is: =IDENTITY(start, incr)=. Default érték: 1-től indul és egyesével nő.

* Beszúrás generált kulccsal: =INSERT INTO anyag VALUES ('cukor', NULL)=
* Beszúrás felhasználó által definiált kulccsal: <code class="pre">SET IDENTITY_INSERT anyag ON
<br/>INSERT INTO anyag(anyag_id, anyag_nev) VALUES(2, 'liszt')
<br/>SET IDENTITY_INSERT anyag OFF</code> Az =INSERT= utasításon belül ilyenkor kötelező felsorolni az oszlopokat.
* Session utoljára generált kulcsának lekérdezése (globális változó, nem táblafüggő): =SELECT @@IDENTITY=
* Adott táblába utoljára beszúrt kulcs: =IDENT_CURRENT(táblanév)=
* Identity kezdeti beállításainak lekérdezése: =IDENT_SEED(táblanév)=, =IDENT_INCR(táblanév)=

==SQL Server programozása==

A szerver Transact-SQL (T-SQL) nyelven programozható.
A végrehajtás batchenként történik, a batcheket =GO= utasítás zárja le. Az utasítások végére nem kell pontosvessző.

===Komment===

* egysoros: =--=
* többsoros: =/* */=, egymásba ágyazható (az Oracle-ben nem)

===Azonosítók===

* Szabályos: ([A-Z0-9_]|@{1,2}#{1,2})[A-Z0-9_$]*
** =@=: lokális változó vagy paraméter
** =@@=: rendszerváltozó vagy -függvény
** =#=: átmeneti tábla vagy eljárás
** =##=: globális átmeneti tábla vagy eljárás
* Delimited: [ ] vagy " " jelek közé kell írni, speciális karaktereket is tartalmazhat
* Deklaráció: =DECLARE @változónév típus, @változónév típus, ...=
<br/> Alapértéket nem lehet megadni, a változók <pre>NULL</pre>-ra inicializálódnak. Változó bárhol deklarálható, nem csak blokk elején.
* Értékadás:
** =SET @a = 5=
** =SET @a = (SELECT ...)=. A =SELECT= 0 vagy 1 soros és 1 oszlopos eredménnyel kell, hogy visszatérjen, különben hiba történik. Ha 0 sorral tér vissza, a változó =NULL= értéket kap.
** =SELECT @a=oszlop1, @b=oszlop2, ... FROM ...=
<br/> Ha a =SELECT= 0 sorral tér vissza, <pre>NULL</pre>-t vesz föl minden változó, több soros eredmény esetén az utolsó sor mezői kerülnek a változókba. A legutolsó SQL utasítás által érintett sorok számát a =@@ROWCOUNT= változó adja vissza.

===Adattípusok===

* Egészek
** =BIGINT=: 64 bit, előjeles
** =INT=: 32 bit, előjeles
** =SMALLINT=: 16 bit, előjeles
** =TINYINT=: 8 bit, előjel nélküli
** =BIT=: 0 vagy 1 értékű
* Fixpontos
** =DECIMAL=, =NUMERIC=: legfeljebb 38 jegyű
** =MONEY=, =SMALLMONEY=
* Lebegőpontos
** =FLOAT=: 8 byte-os lebegőpontos, 15 számjegy pontosságú
** =REAL=: 4 byte-os lebegőpontos, 7 számjegy pontosságú
* Dátum
** =DATETIME=: 1753. január 1. és 9999. december 31. közötti időpontot tárol
** =SMALLDATETIME=: 1900. január 1. és 2079. január 6. közötti időpontot tárol
** Elfogadott dátum formátumok: ='mm/dd/yyyy'=, ='mm-dd-yyyy'=, ='yyyy-mm-dd'=, stb.
** a =SET DATEFORMAT= utasítással egyéb formátumokban is beolvasható a dátum
* String
** =CHAR=, =VARCHAR=: <=8000 byte-os fix/változó hosszúságú string
** =NCHAR=, =NVARCHAR=: <=8000 byte-os (4000 karakteres) fix/változó hosszúságú unicode string
** =TEXT=, =NTEXT=: <=2 GB hosszú string, a string műveletek nem alkalmazhatók rá
** =VARCHAR(MAX)=, =NVARCHAR(MAX)=: <=2 GB hosszú string, string függvények alkalmazhatók rá, az SQL Server 2005-ben jelentek meg.
** =BINARY=, =VARBINARY=: <=8000 byte-os fix/változó hosszúságú bináris string
** =IMAGE=: <=2 GB-os bináris string
* Egyéb
** =BOOLEAN=: implicit típus, csak kifejezésben lehet használni, nem lehet oszlopnak vagy változónak értékül adni
** =CURSOR=
** =SQL_VARIANT=: típus nélküli változó
** =TABLE=: eredményhalmazt tárol későbbi feldolgozásig
** =TIMESTAMP=
** =UNIQUEIDENTIFIER=: globálisan egyedi azonosító
** =XML=

====Típus precedencia====

Ha egy operátor 2 oldalán különböző típusok állnak, az eredmény a magasabb precedenciájú típus lesz.

=SQL_VARIANT= > =DATETIME= > =SMALLDATETIME= > =FLOAT= > =REAL= > =DECIMAL= > =MONEY= > =SMALLMONEY= > =BIGINT= > =INT= > =SMALLINT= > ... > =NVARCHAR= > =VARCHAR= > =CHAR=

A fenti precedencia sor következménye pl. az, hogy stringet és számot csak típuskonverzió után lehet összefűzni. Castolni a =CONVERT(típusnév[(paraméterek)], érték)= függvénnyel lehet. Pl. =CONVERT(numeric(10,2), 1.23456)=

===Operátorok===
egy operandusú műveletek: =+=, =-=, =~=
====operátor precedencia:====
1 =+ - ~= előjel
2 =* / %=
3 =+ -= additív és összefűző
4 =< > ...=
5 =^ & |=
6 =NOT=
7 =AND=
8 =ALL ANY=
9 =SET=

===Batch===
====Def.:====
amit egyszerre küldünk a szervernek, utasítások sorozata
====Kliens:====
* Query Analyzer (2000)
* Management Studio (2005)
====Blokk:====
batch vagy =BEGIN ... END=

===Vezérlési szerkezetek===

====Feltételes utasítások====
=IF bool_kif utasítás ELSE utasítás=
* több utasítás esetén =BEGIN END= közé kell tenni
=IF (bool értékű SQL utasítás) utasítás ELSE utasítás=

=====Pl.:=====
<pre>
IF (EXISTS (SELECT * FROM anyag))
print('nem üres')
ELSE
print('üres')
</pre>

* =THEN= és =ELSE= ágban ugyan arra a táblára kell vonatkoznia a =CREATE TABLE= ill. a =SELECT= utasításoknak

====Ciklus====
=WHILE bool_kifejezés utasítás=
* kilépes: =BREAK=
* folytatás: =CONTINUE=
====Ugrás====
=GOTO címke=
* címke def.: =cimke:=
* =RETURN=
====Várakozás====
=WAIT FOR {DELAY idő | TIME időpont}=
====Case függvény====
<pre>
CASE bemeneti kif.
WHEN kif. THEN kif.
...
[ELSE kif.]
END
</pre>
=====Pl.:=====
<pre>
SELECT CASE egyseg_nev
WHEN 'l' THEN 'liter'
WHEN 'dl' THEN 'deci'
ELSE egyseg_nev
END egysnev
FROM anyag
ORDER BY egysnev
</pre>
* ha nem, tud illeszteni és nincs ELSE-ág akkor =NULL= lesz az eredmény
====Searched case:====
<pre>
CASE
WHEN bool_kif THEN kif.
...
[ELSE kif.]
END
</pre>

-- [[PallosPeter|Peti]] - 2006.04.25.
-- [[StMa|Matyi]] - 2006.07.03.

[[Category:Valaszthato]]

AdatbServerProgJegyzet20060428

2013-03-31T19:03:52Z

2001:738:2001:2078:0:208:37:0: Átirányítás ide: Adatbázisok szerver oldali programozása Előadás, 2006.04.28.

#REDIRECT [[Adatbázisok_szerver_oldali_programozása_Előadás,_2006.04.28.]]

Adatbázisok szerver oldali programozása Előadás, 2006.04.28.

2013-03-31T19:03:49Z

2001:738:2001:2078:0:208:37:0: Új oldal, tartalma: „{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060428}}  <style> code.pre { white-space: pre; display:block; me…”

{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060428}}



<style>
code.pre { white-space: pre; display:block; mergin-top: 2px; margin-bottom: 2px;}
li { line-height: 18px; }
</style>

<br/> [[AdatbServerProgJegyzet20060425|<<]] Előadás, 2006.04.28. [[AdatbServerProgJegyzet20060502|>>]]

__TOC__

==MSSQL kurzorok==

=DECLARE @valt INT=
=SELECT @valt=anyag_id FROM anyag=

<br/>
<br/>
* 0 sor -> NULL
* több sor -> utoló sor eredménye

'''Ha minden sorra szükségünk van akkor kurzort kell hasznalni'''

1 deklarálás - lekérdezés megadásaű
2 megnyitás - lekérdezés lefut
3 navigálás - mindkét irányban, adatmódosító utasítások is engedélyezett (Oracle-vel ellentétben)
4 bezárás
5 elengedés

===SQL92 deklaráció:===

=DECLARE nev [INSENSITIVE][SCROLL] CURSOR FOR lekerdezes [FOR {READ ONLY | UPDATE [OF osznev]}]=

<br/>
<br/>
* *INSENSITIVE*: kommitált törlések és módosítások látkatóak
* *SCROLL*: mindkét irányban lehet FETCH-elni
* *READONLY*: kurzoron keresztül em módosíthatunk, de látja a közben kommitált változásokat
* *UPDATE*: módosítás engedályezett
* *UPDATE OF ...*: csak a megadott oszlopokat lehet módosítani

===Transact SQL deklaráció:===
<pre>
DECLARE nev CURSOR [{LOCAL | GLOBAL}]
[{FORWARD ONLY | SCROLL}]
[{STATIC | KEYSET | DYNAMIC | FAST_FORWARD}]
[{READONLY | SCROLL_LOCKS | OPTIMISTIC}]
[TYPE_WARNING]
FOR lekerdezes
[FOR UPDATE [OF ...]]
</pre>

<br/>
* *LOCAL*: csak tárolt eljáráson / batchen / triggeren belül él
* *GLOBAL*: sessionön belül
* *FORWARD ONLY*: csak előre léphetünk (default ha FAST_FORWARD)
* *SCROLL*: (default, ha STATIC|KEYSETDYNAMIC)
* *STATIC*: nem módosítható
* *KEYSET*: csak nem kulcsok módosíthatók
* *DYNAMIC*: bármi módosítható
* *FAST_FORWARD*: read only + forward
* *SCROLL_LOCK*: zárolja a sorokat, fast_forward mellett tiltott (minden featch-nél zárol nem pedig lekérdezésnél)
* '''OPTIMISTIC''' csak akkor módosít egy sort, ha más addig nem módosította már kívülről
* *TYPE_WARNING*: warning-ot ad implicit konverzió esetén

=OPEN [GLOBAL] nev= -> lekérdezés lefut</br></br>
* @@CURSOR_ROWS: érintett sorok száma
=FETCH [{NEXT | PRIOR || FIRST || LAST || ABSOLUTE ért. REALTIVE ért.}]=
=[FROM] [GLOBAL] nev [INTO @valt., ...]=

* *ABSOLUTE*:
** n>0 n. sor
** n<0 végétől az n. sor
** n = 0 aktuális sor
* *RELATIVE*: aktuális sor + n

=CLOSE [GLOBAL] nev=
* eredményhalmazt és zárakat elengedjük
* adatstruktúra megmarad, újra megnyitható
* csak nyitott kurzorra nyitható

=DEALLOCATE [GLOBAL] nev=
* megszűnik egy referencia az adatstruktúráról
* ha az összes referencia megszünt a struktura is felszabadúl

====Kurzorváltozó====
'''korzorváltozó''' = referencia egy létező kurzorra
<br/>
=DECLARE @nev CURSOR=

===Ciklosszervezés===
'''@@FETCH_STATUS'''
* 0: sikeres
* -1: nem sikerült
* -2: hiányzott a sor azért nem sikerült
<pre>
DECLARE c1 CURSOR FOR SELECT anyag_id, anyag_nev FROM anyag
DECLARE @id INT, @nec VARCHAR(50)
//EZEK NEM ÍRHATÓ EGY DECLARE RÉSZBE
OPEN c1
FETCH c1 INTO @id, @nev
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CONVERT(VARCHAR, @id)+':'+@nev
FETCH c1 INTO @id, @nev
END
CLOSE c1
DEALLOCATE c1
</pre>
===Módosítás===
* UPDATE, DELETE engedélyezett, INSERT nem
* WHERE CURRENT OF -t kell hasznalni
* Ha a lekérdezés során a sorok sorrendjet megvaltoztatjuk (ORDER, GROUP, stb.) akkor nem hasznalhatunk módosítást

==Tárolt eljárások==
* lehet IN, OUT paraméter (INOUT nem)
* DDL, és DCL utasításokat is tartalmazhat
* egymásba ágyazható
* státusz információval visszatérhet a hívó felé

1 rendszer tárolt eljárásai sp_-vel kezdődnek
2 lokális: felhasználói adatbázisban vannak
3 átmeneti:
** lokális: #-kal kezdődnek
** globális: ##-kal kezdődik
4 külső tárolt eljárások
** Dll-ként vannak implementálva és xp_-al kezdődik
** SQL Server 2005-ben CLR tárolt eljárások is írhatók

===Kedvenc vzsgakérdés===
'''Tárolt eljárás négyféle módon térhet vissza információval:'''
* kimeneti paraméter (skalar-, kurzor változó)
* visszatérési érték (mindig INT)
* eredményhalmaz
* globális kurzor
===létrehozása===
<pre>
CREATE PROC[EDURE] nev [;number]
[@param típus [VARYING][=default érték]]
[WITH {ENCRYPTION | RECOMPLIE | EXECUTE AS {CALLER | SELF | OWNER | 'user name'}}]
AS
utasítások
GO
</pre>
* BEGIN ... END is hasznalható de csak a GO-nal lesz vége
* default érték: híváskor az utolsó paraméterek elhagyhatók, a közbülsők DEFAULT kulcsszóval helyettesíthetők, ha van default értékük

===Hívás:===
=EXEC[UTE] {[@vissza=]nev[;szam]}=
=[[@param=] {ért. | @valt[OUTPUT] [DEFAULT]}]=
===Módosítás===
=ALTER PROC ...=
===Törlés===
=DROP PROC ...=

<br/>
'''nincs CREATE OR REPLACE'''
===Pl.:===
<pre>

CREATE PROCEDURE anyag_lista
@tol INT,
@ig INT
AS
SELECT anyag_nev FROM anyag WHERE anyag_id BETWEEN @tol AND @ig
GO

EXEC anyag_lista 3,5

</pre>

==Függvények==
* beépített függvények (rowset, aggregate (oszlop fv.), skalár (pl.: =getdate()= ) )
* felhasználói függvények
** skalár
** in-line table valued -> 1 select
** multistatement table valued -> több utasítás, kimenet egy

<br/>
'''ROWSET : ott használjuk, ahol az SQL utasításban táblát használnánk (pl.: =FROM= után)'''

===Skalár függvények===
* @@FETCH, STATUS, getdate(), datepart()
* '''matematikai''' : abs, sin
* '''metaadat''' : col_name, col_length
* '''strinr''' : lawer, substring
* '''rendszer''' : convert, isnull, current_user
* '''rendszer statisztikák'''
* '''szöveg és képmanipuláló fv.-ek'''
* '''biztonsági'''

===Skalár felhasználói fv.===
<pre>
CREATE FUNCTION név (@p típus [=def.érték], ...)
RETURNS skalár típus
AS
BEGIN
függvénytörzs
RETURN érték
END
</pre>

* paraméterszám < 1024
* híváskor minden paramétert meg kell adni, vagy DEFAULT kulcsszót használni, nem hagyható el a végéről

====Pl:====
<pre>
CREATE FUNCTION felkesz_ar (@bid INT) RETURNS float
AS
BEGIN
DECLARE @ar float
SELECT @ar = sum(...) FROM anyag, felkesz, ...
RETURN @ar
END
</pre>

<br/>
'''Hívás:'''
* =SELECT ap60.felkesz_ar(5)=
* =SELECT ap60.felkesz_ar(felkesz_id) FROM felkesz=

===Inline table valued===
<pre>
CREATE FUNCTION név (@p típus(adat)) RETURNS TABLE AS
RETURN lekérdezés
END
</pre>

<br/>
'''hívás:''' =SELECT * FROM ap60.név(paraméter)=

===Tábla visszatérésű több soros===
<pre>
CREATE FUNCTION név (@p típus(adat)) RETURNS TABLE @valt TABLE (oszlop típus, ...)
AS
BEGIN
függvény törzs // (pl.: INSERT INTO @valt ...)
RETURN
END
</pre>

===Módosítása===
=ALTER FUNCTION név=

<br/>
'''A három típus nem átjárható'''

===Törlés===
=DROP FUNCTION név=

===Függvényen belül nem használható:===
* =PRINT=
* =INSERT= csak visszatérési táblára

===Függvényen belül használható:===
* értékadó utasítás
* programvezérlő ut.
* lokális vált., kurzor
* változóba pakoló =SELECT=
* lokális táblára =INSERT=, =UPDATE=, =DELETE=

-- [[StMa|Matyi]] - 2006.08.12.

[[Category:Valaszthato]]

AdatbServerProgJegyzet20060425

2013-03-31T19:03:46Z

2001:738:2001:2078:0:208:37:0: Átirányítás ide: Adatbázisok szerver oldali programozása Labor, 2006.04.25.

#REDIRECT [[Adatbázisok_szerver_oldali_programozása_Labor,_2006.04.25.]]

Adatbázisok szerver oldali programozása Labor, 2006.04.25.

2013-03-31T19:03:45Z

2001:738:2001:2078:0:208:37:0: Új oldal, tartalma: „{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060425}}  <style> code.pre { white-space: pre; display: block; m…”

{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060425}}



<style>
code.pre { white-space: pre; display: block; margin-top: 2px; margin-bottom: 2px; }
li { line-height: 18px; }
</style>

<br/> [[AdatbServerProgJegyzet20060421|<<]] Labor, 2006.04.25. [[AdatbServerProgJegyzet20060428|>>]]

==Programvezérlő utasítások==

<ol>
<li>Indíts egy Microsoft Server Management Studiot! A New Query ikonra kattintva
nyiss meg egy ablakot, ahol a T-SQL utasításokat fogod tudni majd futtatni! Futtasd
le a minta adatbázisokat létrehozó scripteket!

<li>Deklarálj egy változót egy pilóta nevének tárolására. Kérdezd le a pilóták nevét ebbe
a változóba a két tanult módon (<pre>SELECT @val = oszlop FROM ...</pre>, <pre>SET @val =
(SELECT oszlop FROM ...)</pre>). Vizsgáld meg, hogy mi történik az egyik, és mi a
másik esetben!

<code class="pre">DECLARE @pilotanev nvarchar(100)
SELECT @pilotanev = nev FROM pilota
PRINT @pilotanev
SET @pilotanev = (SELECT nev FROM pilota
WHERE pilotaid = (SELECT MIN(pilotaid) FROM pilota))
PRINT @pilotanev
SET @pilotanev = (SELECT nev FROM pilota)
PRINT @pilotanev</code>

<li>Módosítsd úgy az előbbi scriptedet, hogy a lekérdezés a legutolsó futam nyertes
pilótájának a nevét adja vissza!

<code class="pre">DECLARE @pilotanev nvarchar(100)
SELECT @pilotanev = nev FROM pilota, futam_pilota, futam
WHERE futam.futamid = futam_pilota.futamid
AND pilota.pilotaid = futam_pilota.pilotaid
AND ev = (SELECT MAX(ev) FROM futam)
AND helyezes = 1
PRINT @pilotanev</code>

<li>Módosítsd az előző lekérdezést úgy, hogy a pilóta neve mellett a futam dátumát is ki
tud íratni a képernyőre, és írd is ki egy sorba a pilóta nevét és a futam dátumát! Elég
az egyik típusú értékadást használni (amelyiket ebben az esetben még lehet).

<code class="pre">DECLARE @pilotanev nvarchar(100)
DECLARE @futamdatum datetime
SELECT @pilotanev = nev, @futamdatum = datum FROM pilota, futam_pilota, futam
WHERE futam.futamid = futam_pilota.futamid
AND pilota.pilotaid = futam_pilota.pilotaid
AND datum = (SELECT MAX(datum) FROM futam, futam_pilota
WHERE futam.futamid = futam_pilota.futamid)
AND helyezes = 1
PRINT @pilotanev + ' ' + convert(varchar, @futamdatum)</code>

<li>Használj dátummanipuláló függvényeket, amiknek a segítségével az előző
lekérdezésben megkapott dátumot a neked tetsző formában tudod kiíratni (csak év,
hónap, nap kiíratása kell). Használhatod a DATEPART függvényt, vagy az
egyszerűbben használható YEAR, MONTH és DAY függvényeket.

<code class="pre">DECLARE @pilotanev nvarchar(100)
DECLARE @futamdatum datetime
SELECT @pilotanev = nev, @futamdatum = datum FROM pilota, futam_pilota, futam
WHERE futam.futamid = futam_pilota.futamid
AND pilota.pilotaid = futam_pilota.pilotaid
AND datum = (SELECT MAX(datum) FROM futam, futam_pilota
WHERE futam.futamid = futam_pilota.futamid)
AND helyezes = 1
PRINT @pilotanev +
' ' + convert(varchar, YEAR(@futamdatum)) +
'-' + convert(varchar, MONTH(@futamdatum)) +
'-' + convert(varchar, DAY(@futamdatum))</code>

<li>Az SQL Server 2005-ben már lehetőség van a módosító utasítások eredményének
kinyerésére az OUTPUT kifejezés segítségével. Az éppen beszúrt vagy éppen törölt
értékekre az INSERTED és a DELETED átmeneti táblákra való hivatkozással
kaphatjuk meg, az eredményt pedig vagy eredményhalmazként kaphatjuk meg, vagy
átmeneti tábla illetve tábla típus deklarálásával (a részleteket nézd meg a Books
Online-ban). Ezek alapján módosítsd az anyag táblát, és emeld meg minden 100 Ft-nál
olcsóbb anyag árát 10%-kal, a módosított anyagok nevét, régi és új árát pedig
eredményhalmazként nyerd ki!

<code class="pre">UPDATE anyag SET anyag_egysegar = anyag_egysegar*1.1
OUTPUT deleted.anyag_nev nev, deleted.anyag_egysegar regiar, inserted.anyag_egysegar ujar
WHERE anyag_egysegar<100</code>

<li>Felhasználva az előző feladatban nyert ismereteidet, hozz létre egy tábla típusú
változót, majd csökkentsd az anyagok árát 10%-kal. Az eredményt az átmeneti
táblába tedd, amiből pedig egy lekérdezéssel nyerd ki azon módosított anyagok
adatait, amiknek az új ára kisebb, mint 30 Ft!

<code class="pre">DECLARE @tempanyag TABLE(nev VARCHAR(100), regiar REAL, ujar REAL)
UPDATE anyag SET anyag_egysegar = anyag_egysegar*0.9
OUTPUT deleted.anyag_nev, deleted.anyag_egysegar, inserted.anyag_egysegar
INTO @tempanyag
WHERE anyag_egysegar<100
SELECT * FROM @tempanyag WHERE ujar<30</code>

<li>Készíts egy napló táblát az anyag árainak változásának nyomon követésére. A napló
tartalmazzon egy egyedi azonosítót, egy dátumot, amikor az árváltozás bekövetkezett,
az anyag nevét, a régi és az új árát. Módosítsd az előző batch scriptedet úgy, hogy az
átmeneti táblából a 30 Ft-nál olcsóbb termékek adatai a napló táblába kerüljenek. Az
ár értékeket csak két tizedes jegyig tároljad! Kérdezd le az utoljára beszúrt adat
azonosítóját, és azt írasd ki a kimenetre!

<code class="pre">CREATE TABLE anyagar_naplo (
id INT PRIMARY KEY IDENTITY,
nev VARCHAR(100),
regiar REAL,
ujar REAL,
datum DATETIME
)
GO
 
DECLARE @tempanyag TABLE(nev VARCHAR(100), regiar REAL, ujar REAL)
UPDATE anyag SET anyag_egysegar = anyag_egysegar*0.9
OUTPUT deleted.anyag_nev, deleted.anyag_egysegar, inserted.anyag_egysegar
INTO @tempanyag
WHERE anyag_egysegar<100
 
INSERT INTO anyagar_naplo
SELECT nev, convert(numeric(10,2), regiar), convert(numeric(10,2), ujar), getdate()
FROM @tempanyag
WHERE ujar<30</code>

<li>Írj egy lekérdezést, ami kilistázza az XSFXCF neptun kódú diák összes osztályzatát
mind számmal, mind pedig szöveges formátumban. Az osztályzatok előtt szerepeljen,
hogy melyik félévben melyik tárgyból kapta az adott osztályzatot. A félév kódot is
konvertáld át szöveges formátummá. A szemeszterkód egy ötjegyű szám, aminek első
négy jegye az évet jelenti, míg az ötödik számjegye az év félévét jelenti. Például a
2002 év őszi félévét a 20022 jelöli, míg a 2003 tavaszi félévet a 20031 kód jelöli.
Megjelenítéskor az első esetben „2002/2003 I. felev” a második esetben pedig
„2002/2003 II. felev” szöveget szeretnénk látni. A megoldásban használni kell a
Adatbázisok szerver oldali programozása SQL Server T-SQL Programvezérlő utasítások
convert() konverziós függvényt, ami az explicit adattípus konverziót teszi lehetővé,
illetve a substring() függvényt. A függvények működését a help-ben nézd meg!
Az eredmény így nézzen ki:
</ol>
-- [[PallosPeter|Peti]] - 2006.04.25.

[[Category:Valaszthato]]

AdatbServerProgJegyzet20060505

2013-03-31T19:03:44Z

2001:738:2001:2078:0:208:37:0: Átirányítás ide: Adatbázisok szerver oldali programozása Előadás, 2006.05.05.

#REDIRECT [[Adatbázisok_szerver_oldali_programozása_Előadás,_2006.05.05.]]

Adatbázisok szerver oldali programozása Előadás, 2006.05.05.

2013-03-31T19:03:43Z

2001:738:2001:2078:0:208:37:0: Új oldal, tartalma: „{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060505}}  <style> code.pre { white-space: pre; display:block; me…”

{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060505}}



<style>
code.pre { white-space: pre; display:block; mergin-top: 2px; margin-bottom: 2px;}
li { line-height: 18px; }
</style>

<br/> [[AdatbServerProgJegyzet20060502|<<]] Előadás, 2006.05.05. [[AdatbServerProgJegyzet20060509|>>]]

__TOC__

==T-SQL Triggerek==

===Esemény lehet:===
* INSERT
* UPDATE
* DELETE
* SQL Server 2005-ben DDL utasítások is
===Milyen objektumra definiálható DML trigger?===
* tábla
* nézet
===Mikor fut le?===
* esemény után (csak táblára) kulcsszó: =AFTER= ~ =FOR=
* helyette (táblára és nézetre is) kulcsszó: =INSTEAD OF=

'''Kedvenc vizsgakérdés: Triggerek csoportosítása.'''

===Pl.:===
<pre>
CREATE TRIGGER nev ON {tábla|nezet}
WITH ENCRYPTIONS {FTER|FOR|INSTEAD OF} {DELETE|INSERT|UPDATE}
AS SQLutasítások

</pre>
'''régi adatok: deleted.'''

'''új adatok: inserted.'''

''ezek átmeneti táblák, táblaként kell kezelni''

* '''SQL Serveren csak utasítás trigger van, sor trigger nincs.'''
* '''Sor szintű vizsgálatot csak kurzorral lehet csinálni.'''

'''''Módosúlt oszlopok lekérdezése:'''''

=IF update(oszlopnév) ...=

* =INSERT= : minden oszlopra true-t ad vissza
* =DELETE= : nem használható
* =UPDATE= : TRUE ha modosúlt

=IF COLUMNS_UPDATED(bitmaszk) ...= - n. oszlop 1 << (n-1)

===Megkötések:===
* batchen belül egyedüli utasításnak kell lennie, addig tart, ameddig a batch
* 1 trigger 1 táblára definiálható
* 1 táblára több trigger is lehet, ezeknek a lefutási sorrendje véletlen

===Módosítás:===
=ALTER TRIGGER=

===Törlés:===
=DROP TRIGGER=, tábla törlése esetén magától törlődik

===Pl.:===

=ALTER TABLE felkesz ADD felkesz_ar FLOAT=

-feltesszük hogy már ki van töltve-

<pre>
CREATE TRIGGER felkesz_ar_update ON felkesz_angyag
AFTER INSERT, DELETE, UPDATE
AS
DECLARE @db1 INT, @db2 INT
SELECT @db1 = COUNT(*) FROM deleted
SELECT @db2 = COUNT(*) FROM inserted
IF @db1 > 0 AND @db2 > 0 //update volt
BEGIN
UPDATE felkesz SET felkesz_ar = felkesz_ar - deleted.fa_mennyiseg * anyag_egysegar + inserted.fa_mennyiseg * anyag_egysegar
FROM anyag, deleted, inserted
WHERE anyag_id =inserted.fa_anyagid AND felkesz:id = deleted.fa_felkeszid
END
IF @db2 = 0 //delete volt
BEGIN
...
END
IF @db1 = 0 //insert volt
BEGIN
...
END
GO

</pre>

Ha fa_anyagid vagy fa_felkeszid is módosulhat, külön le kell kezelni.

===DDL trigger (2005)===

=CREATE, DROP, ALTER, GRANT, DENY, REVOKE= utasításokra írható.

<pre>
CREATE TRIGGER nev ON {ALL SERVER|DATABASE}
[WITH {ENCRYPTION|EXECUTE AS {CALLER|SELF|username}}]
{FOR|AFTER} {esemény típus| esemény csoport} [,...]
AS
SQL utasítások

</pre>
'''esemény lehet pl.:'''

adatbázis hatókörű:
* =ALTER_FUNCTION=
* =CREATE_TABLE=
* =CREATE_USER=
* =DROP_TRIGGER=
szerver hatókörű:
* =CREATE_DATABASE=
* =DROP_LOGIN=

'''esemény csopotok pl.:'''
* =DDL_TABLE_EVENTS=
* =DDL_FUNCTION_EVENTS=

==Hibakezelés:==

===Hiba szerkezete:===
* szám
* üzenet
* fontosság
* állapotkód
* eljárásnév, ahol generálódott
* sor száma

'''Szerver hibák:''' master.dbo.sysmessages

Felhasználói hibák: sysmessages
* '''bővítés''' : sp_addmessage
* '''törlés''' : sp_dropmessage

=@@ERROR= = 0 , ha sikeres volt az utolsó utasítás, egyebkent '''hibakód'''

===Hiba dobása:===
=RAISERROR ({sg_id|msg_str}, severity, state, [arg1,...]) [WITH option1, ...]=

===SQL Server 2005 exception kezelés===
<pre>
BEGIN TRY
SQL utasítások
END TRY
BEGIN CATCH
SQL utasítások
END CATCH
</pre>

'''Olyan hibákat kap el, aminek a fontossága > 10, és nem bontja a kapcsolatot'''

===Megkötések:===
* nem tarthat több batch-en/blokk-on keresztül
* felhasználói függvényben nem használható

===Hiba paramétereit lekérő függvények:===
* =ERROR_NUMBER()=
* =ERROR_SEVERITY()=
* =ERROR_STATE()=
* =ERROR_PROCEDURE()=
* =ERROR_LINE()=
* =ERROR_MESSAGE()=

-- [[StMa|Matyi]] - 2006.09.23.

[[Category:Valaszthato]]

AdatbServerProgJegyzet20060418

2013-03-31T19:03:42Z

2001:738:2001:2078:0:208:37:0: Átirányítás ide: Adatbázisok szerver oldali programozása Labor, 2006.04.18.

#REDIRECT [[Adatbázisok_szerver_oldali_programozása_Labor,_2006.04.18.]]

Adatbázisok szerver oldali programozása Labor, 2006.04.18.

2013-03-31T19:03:41Z

2001:738:2001:2078:0:208:37:0: Új oldal, tartalma: „{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060418}}  <style> code.pre { white-space: pre; display: block; m…”

{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060418}}



<style>
code.pre { white-space: pre; display: block; margin-top: 2px; margin-bottom: 2px; }
li { line-height: 18px; }
</style>

<br/> [[AdatbServerProgJegyzet20060414|<<]] Labor, 2006.04.18. [[AdatbServerProgJegyzet20060421|>>]]

==Dinamikus SQL==

<ol>
<li> Hozz létre egy csomagot, amiben a gyakorlat során elkészítendő feladatokat fogod
megvalósítani!
<li> Írj egy tárolt eljárást, ami egy tábla sorait törli ki. Az eljárás bemenete a tábla neve és
a törlend( sorokat definiáló feltétel legyen. Amennyiben nincs megadva feltétel, az
eljárás a bemenetként kapott tábla összes sorát törölje. Az eljárást próbáld is ki!

<code class="pre">CREATE OR REPLACE PACKAGE dynasql IS
PROCEDURE deletefrom(tablename VARCHAR2, condition VARCHAR2);
END dynasql;</code>

<code class="pre">CREATE OR REPLACE PACKAGE BODY dynasql IS
PROCEDURE deletefrom(tablename VARCHAR2, condition VARCHAR2) IS
query VARCHAR2(500);
BEGIN
query := 'DELETE FROM ' ||| tablename |||| ' WHERE ' || NVL(condition, '0=0');
DBMS_OUTPUT.PUT_LINE(query);
EXECUTE IMMEDIATE query;
END;
END dynasql;</code>

=EXEC dynasql.deletefrom('exam', 'ex_id=6');=

<li> Hozz létre egy típust, amiben tetszőleges számú tábla nevét tudod majd tárolni!
<li> Írj egy tárolt eljárást, aminek bemenő paramétere táblák neveinek tömbje, feladata
pedig, hogy kitörölje a bemenetként megkapott táblákat. A sikeres vagy sikertelen
törlésekről a kimenetre írj üzenetet. Dobj hibát, amennyiben nem lehet az összes
táblát kitörölni, mert esetleg a listából hiányzik egy olyan tábla, ami hivatkozik egy
listabeli táblára.

<code class="pre">CREATE OR REPLACE PACKAGE dynasql IS
TYPE tablalista IS TABLE OF VARCHAR2(30);
PROCEDURE droptables(tables tablalista);
END dynasql;</code>

<code class="pre">CREATE OR REPLACE PACKAGE BODY dynasql IS
tablanemletezik EXCEPTION;
tablanemtorolheto EXCEPTION;
 
PRAGMA EXCEPTION_INIT(tablanemletezik, -00942);
PRAGMA EXCEPTION_INIT(tablanemtorolheto, -02449);
 
PROCEDURE droptables(tables tablalista) IS
query VARCHAR2(500);
torolve INT := 0;
BEGIN
FOR i IN tables.FIRST..tables.LAST LOOP
FOR i IN tables.FIRST..tables.LAST LOOP
BEGIN
query := 'DROP TABLE ' | tables(i);
EXECUTE IMMEDIATE query;
torolve := torolve+1;
DBMS_OUTPUT.PUT_LINE('Törölve: ' | tables(i));
EXCEPTION
WHEN tablanemletezik THEN
DBMS_OUTPUT.PUT_LINE('Nem létezik: ' | tables(i));
WHEN tablanemtorolheto THEN
DBMS_OUTPUT.PUT_LINE('Nem törölhető: ' | tables(i));
END;
END LOOP;
END LOOP;
IF torolve<tables.COUNT THEN
RAISE_APPLICATION_ERROR(-20100, 'Nem sikerült minden táblát törölni');
END IF;
END;
END dynasql;</code>

<code class="pre">DECLARE
t dynasql.tablalista;
BEGIN
t:=dynasql.tablalista('felkesz', 'anyag', 'felkesz_anyag');
dynasql.droptables(t);
END;</code>
</ol>
-- [[PallosPeter|Peti]] - 2006.04.18.

[[Category:Valaszthato]]

AdatbServerProgJegyzet20060407

2013-03-31T19:03:40Z

2001:738:2001:2078:0:208:37:0: Átirányítás ide: Adatbázisok szerver oldali programozása Előadás, 2006.04.07.

#REDIRECT [[Adatbázisok_szerver_oldali_programozása_Előadás,_2006.04.07.]]

Adatbázisok szerver oldali programozása Előadás, 2006.04.07.

2013-03-31T19:03:39Z

2001:738:2001:2078:0:208:37:0: Új oldal, tartalma: „{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060407}}  <style> code.pre { white-space: pre; display: block; m…”

{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060407}}



<style>
code.pre { white-space: pre; display: block; mergin-top: 2px; margin-bottom: 2px;}
li { line-height: 18px; }
</style>

<br/> [[AdatbServerProgJegyzet20060404|<<]] Előadás, 2006.04.07. [[AdatbServerProgJegyzet20060411|>>]]

__TOC__

==Tranzakciók==

===Definíció===
Tranzakciónak hívjuk a munka 1 logikai egységét. 1 tranzakció 1 vagy több SQL utasításból állhat.

Tranzakció tulajdonságai (ACID) — gyakori vizsgakérdés:
* atomicity: vagy minden utasítása végrehajtódik, vagy egyik sem
* consistency: a tranzakció konzisztens állapotból konzisztens állapotba visz
* isolation: a tranzakció elvárt eredménye megegyezik azzal, mintha a tranzakció egyedül futna
* durability: véglegesítés után nem veszhet el az információ

===Tranzakció határai===
* kezdete: 1 futtatható SQL utasítás
* vége:
** explicit =COMMIT= vagy =ROLLBACK=
** implicit: DDL utasítás (=CREATE=, =DROP=, =RENAME=, =ALTER=) kiadásával vagy a kapcsolat bezárásával automatikusan commitol. A DDL utasítás mindenképp külön tranzakcióban fut.
** implicit =ROLLBACK=: ha az adatbázis kapcsolat hiba köveztében szakad meg

====COMMIT====
* véglegesítés
* a változtatások a többiek számára is láthatóvá válnak
* zárak felszabadítása
* az adat lemezre kerül
* naplózás

====ROLLBACK====
* visszafejti az utasításokat a tranzakció kezdetéig
* Oracle megoldása a visszafejtésre: ''redo log'', azaz minden utasítás végrehajtásakor letárolja az utasítás inverzét is
* a tranzakcióban definiálhatók biztonsági pontok (=SAVEPOINT=): a tranzakció nem csak a legelejére görgethető vissza, hanem egy biztonsági pontig is
** =SAVEPOINT= definiálása: =SAVEPOINT s.p. neve;=
** visszagörgetés: =ROLLBACK TO s.p. neve;=
** ha azonos néven definiálunk 2 savepointot, az utolsó felülírja a többit
** rekurzív függvényben a <pre>SAVEPOINT</pre>-ok a stacken tárolódnak, de csak a legbelső érhető el
** az utasítás szintű visszafejtést az Oracle implicit <pre>SAVEPOINT</pre>-ok segítségével végzi

===Konkurens tranzakciók===

Konkurens tranzakciók esetén a következő problémák merülhetnek fel:
* _dirty read_: a tranzakció olyan adatot olvas, amit egy másik nem kommittáltan írt
* _nem megismételhető olvasás_: az adathalmazt újra beolvasva azt látjuk, hogy egy már kommittált tranzakció módosította vagy törölt belőle
* _fantom olvasás_: olvasás közben egy másik tranzakció új sorokat szúr be

A problémák megoldására az SQL'92 szabvány 4 izolációs szintet különít el. A táblázat mezői azt jelentik, hogy adott izolációs szinten adott hiba előfordulhat-e.

{| border="1"
| || '''Dirty read''' || '''Non-repeatable read''' || '''Phantom read'''
|-
| '''Read uncommitted''' || igen || igen || igen
|-
| '''Read committed''' || nem || igen || igen
|-
| '''Repeatable read''' || nem || nem || igen
|-
| '''Serializable''' || nem || nem || nem
|}

Az Oracle 3 izolációs szintet vezetett be:
* read committed: alapbeállítás
** a tranzakciók olyan adatokat látnak, amiket a '''lekérdezés''' előtt kommittáltak
** akkor érdemes használni, ha kevés ütközésre számítunk
* serializable
** a tranzakciók olyan módosításokat adatokat látnak, amiket a '''tranzakció''' kezdete előtt kommittáltak
** nagy adatbázis, várhatóan kevés ütközés (2 tranzakció azonos sort próbál módosítani), rövid, kevés sort érintő módosító tranzakciók esetén érdemes használni
* read only
** a tranzakciók olyan módosításokat adatokat látnak, amiket a '''tranzakció''' kezdete előtt kommittáltak
** a tranzakció csak olvashat

====Példa====

===Zárak kezelése===

====Sor szintű zárak====

====Sor szintű zárak kurzorral====

====Tábla szintű zárolás====

===Autonóm tranzakció===

-- [[PallosPeter|Peti]] - 2006.04.10.

[[Category:Valaszthato]]

AdatbServerProgJegyzet20060404

2013-03-31T19:03:38Z

2001:738:2001:2078:0:208:37:0: Átirányítás ide: Adatbázisok szerveroldali programozása Labor, 2006.04.04.

#REDIRECT [[Adatbázisok_szerveroldali_programozása_Labor,_2006.04.04.]]

Adatbázisok szerveroldali programozása Labor, 2006.04.04.

2013-03-31T19:03:37Z

2001:738:2001:2078:0:208:37:0: Új oldal, tartalma: „{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060404}}  <style> code.pre { white-space: pre; display: block; m…”

{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060404}}



<style>
code.pre { white-space: pre; display: block; margin-top: 2px; margin-bottom: 2px; }
li { line-height: 18px; }
</style>

<br/> [[AdatbServerProgJegyzet20060331|<<]] Labor, 2006.04.04. [[AdatbServerProgJegyzet20060407|>>]]

==PL/SQL Csomagok==

<ol>
<li> Készíts el egy csomagot a Forma-1-es adatbázis különböző műveleteinek egységbe
zárására! Készíts eljárásokat, amiknek segítségével új csapatot, új szezont és új pilótát lehet felvenni. Definiálj globális kivételeket a különböző, az eljárások során fellépő kivételekhez (Hibás bemeneti paraméter, már létező csapat/szezon/pilóta stb.)!

'''Csomag deklaráció'''

<code class="pre">CREATE OR REPLACE PACKAGE forma1_insert is
csapatletezik EXCEPTION;
csapatnevhianyzik EXCEPTION;
PROCEDURE ujcsapat(csnev VARCHAR2, csszekhely VARCHAR2);
END forma1_insert;</code>

'''Csomag törzs'''

<code class="pre">CREATE OR REPLACE PACKAGE BODY forma1_insert IS
PROCEDURE ujcsapat(csnev VARCHAR2, csszekhely VARCHAR2) IS
id INT;
db INT;
BEGIN
IF csnev IS NULL THEN
RAISE csapatnevhianyzik;
END IF;
SELECT count(csapatid) INTO db FROM csapat WHERE nev=csnev OR szekhely=csszekhely;
IF db>0 THEN
RAISE csapatletezik;
END IF;
SELECT NVL(MAX(csapatid),0)+1 INTO id FROM csapat;
INSERT INTO csapat VALUES(id, csnev, csszekhely);
END;
END forma1_insert;</code>

<li> Írj egy blokkot, ami kipróbálja a csomag egyes eljárásait! A blokkon belül kapd el az
esetlegesen fellépő csomagbeli kivételeket, és értesítsd az eseményről a kliens oldalt
(alkalmazás hiba dobása megfelelő hibaüzenettel).

<code class="pre">BEGIN
forma1_insert.ujcsapat('1', 'x');
EXCEPTION
WHEN forma1_insert.csapatletezik THEN
DBMS_OUTPUT.put_line('Csapatnév már létezik');
WHEN forma1_insert.csapatnevhianyzik THEN
DBMS_OUTPUT.put_line('Csapatnév nincs megadva');
END;</code>

<li> Egészítsd ki a forma1-es csomagot az alábbi funkciókkal. Egy eljárást kell
elkészítened, aminek bemenő paramétereként egy csapat összetételét kell megadni
(szezon, csapatnév, pilóták neve típussal) az eljáráson belül pedig az adott szezonba
az adott csapathoz a megadott pilóták kerülnek felvételre. Amennyiben a csapat az
adott szezonban még nem létezik, akkor az eljárás felvesz egy új bejegyzést a
CSAPAT_SZEZON táblába, de egy hibaüzenettel jelzi, hogy hiányos adatokkal
dolgozott. Hozz létre egy rekord típust, ami az eljárás bemenete lesz, és hozd létre az
eljárást is!

<code class="pre">CREATE OR REPLACE PACKAGE forma1_insert IS
csapatletezik EXCEPTION;
csapatnevhianyzik EXCEPTION;
csapatnemletezik EXCEPTION;
 
TYPE pilotalista IS TABLE OF csapat.nev%TYPE;
 
PROCEDURE ujcsapat(csnev VARCHAR2, csszekhely VARCHAR2);
PROCEDURE csapatot_feltolt(szezon INT, csnev VARCHAR2,
pilota1 VARCHAR2, pilota2 VARCHAR2, pilotat pilotalista);
END forma1_insert;</code>

<code class="pre">CREATE OR REPLACE PACKAGE BODY forma1_insert is
FUNCTION get_csapatid(csapatnev VARCHAR2) RETURN INT IS
result INT;
BEGIN
SELECT csapatid INTO result FROM csapat WHERE nev=csapatnev;
RETURN result;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
 
-- megkeres vagy beszúr egy pilótát, és visszaadja az id-jét
FUNCTION get_pilotaid(pnev VARCHAR2) RETURN INT IS
result INT;
BEGIN
SELECT pilotaid INTO result FROM pilota WHERE nev=pnev;
RETURN result;
EXCEPTION
WHEN NO_DATA_FOUND THEN
SELECT NVL(MAX(pilotaid),0)+1 INTO result FROM pilota;
INSERT INTO pilota(pilotaid, nev) VALUES(result, pnev);
RETURN result;
END;
 
PROCEDURE csapatot_feltolt(szezon INT, csnev VARCHAR2,
pilota1 VARCHAR2, pilota2 VARCHAR2, pilotat pilotalista)
IS
csid INT;
csszid INT;
pid INT;
i INT;
BEGIN
csid := get_csapatid(csnev);
SELECT NVL(MAX(csapatszezonid),0)+1 INTO csszid FROM csapat_szezon;
INSERT INTO csapat_szezon(csapatszezonid, csapatid, ev)
VALUES(csid, csszid, szezon);
 
pid := get_pilotaid(pilota1);
INSERT INTO csapat_pilota VALUES(csszid, pid, '1');
pid := get_pilotaid(pilota2);
INSERT INTO csapat_pilota VALUES(csszid, pid, '2');
 
i := pilotat.FIRST;
WHILE i IS NOT NULL LOOP
pid := get_pilotaid(pilotat(i));
INSERT INTO csapat_pilota VALUES(csszid, pid, '2');
i := pilotat.NEXT(i);
END LOOP;
 
IF csid IS NULL THEN
RAISE csapatnemletezik;
END IF;
END;
END forma1_insert;</code>

<li> Készíts egy blokkot, ami létrehoz egy csapat összeállítást, ás az előző feladatban
létrehozott eljárást hívja meg. Amennyiben az eljárás hiányos adat hibával tér vissza,
a csapat szezonális adatait is vidd be a blokkon belül!

<li> Egészítsd ki a forma1-es csomagot egy eljárással, aminek két bemenő paramétere egy
csapat neve és egy szezon, egy kimenő paramétere pedig egy REF CURSOR típusú
változó, amibe az adott csapat adott évben futott versenyzőinek eredményét
tartalmazza.

<li> Készíts blokkot, ami meghívja az előző feladatban megírt eljárást, és a kimenetre
(DBMS_OUTPUT.PUT_LINE) kiírja az eredméyt.

<li> Készíts el egy csomagot a cukrász adatbázisbeli műveletek egységbe zárása céljából!
A csomag tartalmazzon két eljárást. Az egyik eljárás tegye lehetővé különböző
alapanyagok, félkészek vagy termékek felvételét az adatbázisba. Az eljárás
paraméterei közt szerepeljen az adat típusa, és ezek alapján az eljárás szúrja be a
megfelelő táblába az adatokat. Az azonosítót szekvenciával generáljad! A második
eljárás tegye lehetővé félkész termékek receptjét összeállítani, magyarul a két
bemeneti paramétere egy anyag és egy félkész neve legyen, valamint az anyag
mennyisége. Próbáld ki a csomagot egy teszt blokk segítségével. Ellenőrizd, hogy jól
működnek-e az eljárások.

<li> Egészítsd ki a cukrász csomagot a következők szerint. A csomag tartalmazzon egy
eljárást, aminek egy kimenő paramétere van, ami a még nem teljesített rendeléseket
tartalmazza. (Feltételezzük, hogy van egy rendelés tábla, ha nincs, hozd azt létre!) A
kimenet egy rekordokból álló tömb legyen, amely tartalmazza a rendelés azonosítóját,
az áru nevét, típusát, mennyiségét, a rendelés dátumát valamint a forintban és euróban
vett árát. Az euró árfolyamát a csomagon belül egy változóban tároljuk, és az első
használatkor kerül meghatározásra.
* Tervezd meg a csomag részeit (globális és lokális függvények, eljárások, globális és lokális típusok, inicializáló paraméter.) Alapértelmezésben az euró árfolyamát konstans értékre állítsuk be!
* Valósítsd meg az egyes eljárásokat és függvényeket, és egy teszt blokk segítségével próbáld is ki a csomagot!
* Készíts egy függvényt, ami véletlen módon határozza meg az euró árfolyamát, ahol az euró alap árfolyama legyen 240,15 Ft, és a véletlen ingadozás legyen 0-20 között.

<li> Az előadáson ismertetett jogosultság ellenőrző rendszert valósítsd meg. A feladat a
következő volt:

A tanár-diák adatbázisban a diákok nyilvántartására nem célszerű létrehozni annyi
Oracle felhasználót, amennyi diák van. Helyette a STUDENT táblában tároljuk el az
egyes diákok azonosítóit és jelszavait, és mindenki egységesen ugyanazzal az Oracle
felhasználóval léphet be. Létre kell ezért hozni egy mechanizmust, ami biztosítja,
hogy egy diák csak akkor tudjon különböző műveleteket végrehajtani az
adatbázisban, ha valóban belépett. Készíts egy csomagot, aminek változójában el
tudjuk tárolni, hogy egy adott diák belépett-e vagy sem! Írd meg a belépő eljárást, és
a belépést ellenőrző függvényt!

Írj triggert, ami csak akkor enged adatmódosító utasítást lefuttatni a vizsga
jelentkezés táblán, ha a diák belépett a rendszerbe.

<li> Módosítsd az előző feladatban megírt csomagot úgy, hogy a tanárok jogosultságait is
tudja kezelni. Egy vizsgát csak egy tanári jogosultsággal rendelkező felhasználó
írhasson ki, míg egy vizsgára diák is vagy akár tanár is jelentkezhessen. A diák
adatait pl. E-mail cím vagy jelszó, csak az adott diák tudja módosítani! Írd meg hozzá a szükséges triggereket vagy tárol eljárásokat!
</ol>

-- [[PallosPeter|Peti]] - 2006.04.04.

[[Category:Valaszthato]]

AdatbServerProgJegyzet20060314

2013-03-31T19:03:33Z

2001:738:2001:2078:0:208:37:0: Átirányítás ide: Adatbázisok szerver oldali programozása Labor, 2006.03.14.

#REDIRECT [[Adatbázisok_szerver_oldali_programozása_Labor,_2006.03.14.]]

Adatbázisok szerver oldali programozása Labor, 2006.03.14.

2013-03-31T19:03:32Z

2001:738:2001:2078:0:208:37:0: Új oldal, tartalma: „{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060314}}  <br/> << Labor,…”

{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060314}}



<br/> [[AdatbServerProgJegyzet20060310|<<]] Labor, 2006.03.14. [[AdatbServerProgJegyzet20060317|>>]]

<ol>
<li> Készíts egy anonim blokkot, ami sorra kiírja a kimenetre a páros számokat 1-től 100-ig, majd a páratlan számokat visszafele 100-tól 1-ig!
<pre>BEGIN
FOR i IN 1..50 LOOP
DBMS_OUTPUT.put_line(i*2);
END LOOP;
FOR i IN REVERSE 1..50 LOOP
DBMS_OUTPUT.put_line(i*2-1);
END LOOP;
END;</pre>

<li> Készíts egy anonim blokkot, aminek van két paramétere, amiket futás előtt állíthatunk, az egyik a pilóta azonosítója, a másik egy évszám. A blokk belsejében írd ki (<pre>DBMS_OUTPUT.put_line</pre>), hogy az adott pilóta az adott szezonban milyen típusú pilótaként (Elsőszámú, Másodszámú, Teszt) szerepelt a Forma-1 világában. Amennyiben hibás paramétereket adtak meg, akkor azt írd ki, hogy nincs megfelelő adat.

<pre>DECLARE
pilota_id int := 103;
evszam int := 2005;
pilota_tipus int;
BEGIN
SELECT pilotatipus INTO pilota_tipus
FROM csapat_pilota, csapat_szezon
WHERE csapat_pilota.csapatszezonid = csapat_szezon.csapatszezonid AND
csapat_pilota.pilotaid = pilota_id AND
csapat_szezon.ev = evszam;

CASE pilota_tipus
WHEN '1' THEN DBMS_OUTPUT.put_line('Elsőszámú');
WHEN '2' THEN DBMS_OUTPUT.put_line('Másodszámú');
WHEN 'T' THEN DBMS_OUTPUT.put_line('Teszt');
ELSE DBMS_OUTPUT.put_line('Nincs megfelelő adat');
END CASE;
END;</pre>

<li> Definiálj egy tízelemű egész számok tárolására alkalmas tömböt! Töltsd fel a tömb elemeit sorra páros számokkal! Ezek után írasd ki a tömb elemeit a kimenetre. A megoldáskor ne használd ki, hogy tudod a tömb méretét!

<pre>DECLARE
TYPE tomb_tipus IS TABLE OF int;
tomb tomb_tipus;
i int;
BEGIN
tomb:=tomb_tipus();
FOR i IN 1..10 LOOP
tomb.EXTEND;
tomb(i):=i*2;
END LOOP;

i:=tomb.FIRST;
LOOP
EXIT WHEN i IS NULL;
DBMS_OUTPUT.put_line(tomb(i));
i:=tomb.NEXT(i);
END LOOP;
END;</pre>

<li> Írj egy PL/SQL blokkot, ami kurzor használata nélkül az anyag tábla sorait kiírja a kimenetre!

<pre>DECLARE
min_id int;
max_id int;
db int;
anyag_sor anyag%ROWTYPE;
BEGIN
SELECT min(anyag_id) INTO min_id FROM anyag;
SELECT max(anyag_id) INTO max_id FROM anyag;
FOR i IN min_id..max_id LOOP
SELECT count(anyag_id) INTO db FROM anyag WHERE anyag_id=i;
IF db=1 THEN
SELECT * into anyag_sor FROM anyag WHERE anyag_id=i;
DBMS_OUTPUT.put_line(anyag_sor.anyag_id || ' ' || anyag_sor.anyag_nev);
END IF;
END LOOP;
END;</pre>

<li> Oldd meg az előző feladatot explicit kurzor használatával! Hasonlítsa össze a két megoldást!

<pre>DECLARE
CURSOR c IS SELECT anyag_id id, anyag_nev nev FROM anyag;
BEGIN
FOR rec IN c LOOP
DBMS_OUTPUT.put_line(rec.id || ' ' || rec.nev);
END LOOP;
END;</pre>

<li> Oldd meg az előző feladatot implicit kurzort használó kurzor for ciklussal!

<pre>BEGIN
FOR rec IN (SELECT anyag_id, anyag_nev FROM anyag) LOOP
DBMS_OUTPUT.PUT_LINE(rec.anyag_id || ' ' || rec.anyag_nev);
END LOOP;
END;</pre>

<li> Listázd ki, hogy a gyümölcstorta milyen összetevőkből áll. A lista tartalmazza az alapanyagokat, amiket közvetlen a tortához kell felhasználni, és a félkész termékeket. Minden félkész termékek alatt egy kis listában jelenjen meg, hogy az adott félkész termék milyen alapanyagokból tevődik össze. Az alapanyag neve mellett a mennyiséget és a mértékegységet is írasd ki.

Minta:

<pre>A Csokitorta osszetevoi:
porcukor(.02 kg)
tejszinhab
tejszin(4 dl)
cukor(.1 kg)
csoki krem
kakao(750 g)
tej(1 l)
cukor(.5 kg)
piskota
tojas(6 db)
liszt(.25 kg)
cukor(.06 kg)</pre>

<pre>DECLARE
termeknev termek.termek_nev%TYPE := 'Gyumolcstorta';
BEGIN
DBMS_OUTPUT.put_line('A(z) ' || termeknev || ' összetevõi');
FOR rec IN (
SELECT anyag.anyag_nev, termek_anyag.ta_mennyiseg, egyseg.egyseg_nev
FROM termek, termek_anyag, anyag, egyseg
WHERE termek.termek_id = termek_anyag.ta_termekid AND
anyag.anyag_id = termek_anyag.ta_anyagid AND
egyseg.egyseg_id = anyag.anyag_egyseg AND
termek.termek_nev = termeknev
) LOOP
DBMS_OUTPUT.put_line(' ' || rec.anyag_nev || ' (' || rec.ta_mennyiseg ||
' ' || rec.egyseg_nev || ')');
END LOOP;

FOR rec IN (
SELECT felkesz.felkesz_id, felkesz.felkesz_nev
FROM termek, termek_felkesz, felkesz
WHERE termek.termek_id = termek_felkesz.tf_termekid AND
felkesz.felkesz_id = termek_felkesz.tf_felkeszid AND
termek.termek_nev = termeknev
) LOOP
DBMS_OUTPUT.put_line(' ' || rec.felkesz_nev);
FOR rec2 IN (
SELECT anyag.anyag_nev, felkesz_anyag.fa_mennyiseg, egyseg.egyseg_nev
FROM felkesz, felkesz_anyag, anyag, egyseg
WHERE felkesz.felkesz_id = felkesz_anyag.fa_felkeszid AND
anyag.anyag_id = felkesz_anyag.fa_anyagid AND
anyag.anyag_egyseg = egyseg.egyseg_id AND
felkesz.felkesz_id = rec.felkesz_id
) LOOP
DBMS_OUTPUT.put_line(' ' || rec2.anyag_nev || ' (' ||
rec2.fa_mennyiseg || ' ' || rec2.egyseg_nev || ')');
END LOOP;
END LOOP;
END;</pre>

<li> Hozz létre egy struktúrát, ami félkész termékek receptjeinek tárolására szolgál. Hozz létre egy tömböt az összes félkész termék receptjeinek a tárolására, és töltsd fel adatokkal. Ellenőrzésképpen írasd ki a tömb tartalmát a kimenetre!

</ol>

-- [[PallosPeter|Peti]] - 2006.03.14.

[[Category:Valaszthato]]

AdatbServerProgJegyzet20060324

2013-03-31T19:03:31Z

2001:738:2001:2078:0:208:37:0: Átirányítás ide: Adatbázisok szerver oldali programozása Előadás, 2006.03.24.

#REDIRECT [[Adatbázisok_szerver_oldali_programozása_Előadás,_2006.03.24.]]

Adatbázisok szerver oldali programozása Előadás, 2006.03.24.

2013-03-31T19:03:30Z

2001:738:2001:2078:0:208:37:0: Új oldal, tartalma: „{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060324}}  <style> code.pre { white-space: pre; display:block; me…”

{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060324}}



<style>
code.pre { white-space: pre; display:block; mergin-top: 2px; margin-bottom: 2px;}
code.precompact { white-space: pre; display:block; mergin-top: 2px; margin-bottom: 2px; line-height: 12px; }
li { line-height: 18px; }
</style>

<br/> [[AdatbServerProgJegyzet20060321|<<]] Előadás, 2006.03.24. [[AdatbServerProgJegyzet20060328|>>]]

__TOC__

==Triggerek==

A trigger egy adatbázis eseményre automatikusan lefutó tárolt eljárás. Explicite nem hívható.

Típusai:
* DML triggerek: =INSERT=, =UPDATE= vagy =DELETE= eseménzekre fut le
* DDL triggerek: =CREATE=, =ALTER=, =DROP=, =GRANT=, =REVOKE= eseményekre fut le
* Szerver események: Startup, Shutdown
* Felhasználói események: Logon, Logoff

===DML triggerek===
* esemény lehet: =INSERT=, =UPDATE= vagy =DELETE=
* nézethez vagy táblához rendelhető hozzá
* esemény előtt (<pre>BEFORE</pre>), után (<pre>AFTER</pre>) vagy helyett (<pre>INSTEAD OF</pre>) fut le
* az esemény bekövetkeztekor lefuthat egyszer vagy minden sorra

====Szintaktika====

<code class="pre">CREATE OR REPLACE TRIGGER név
{BEFORE | AFTER || INSTEAD OF} {INSERT || DELETE UPDATE [OF oszlop1, ...]}
[OR további események ...]
ON {táblanév | nézetnév} [FOR EACH ROW]
[WHEN feltétel]
PL/SQL blokk</code>

* Az =INSTEAD OF= csak nézetre használható. Akkor van értelme, ha a nézet több táblából származik. Ilyenkor az Oracle nem támogatja az =INSERT=, =UPDATE= és =DELETE= parancsokat, nekünk kell megírnunk, hogy mi történjen.
* A =BEFORE= és az =AFTER= csak táblára működik.
* Az =UPDATE= trigger alapesetben bármelyik oszlop változása esetén kiváltódik, de az =OF= kulcsszóval szűkíthető kevesebb oszlopra.
* A tábla törlésével a kapcsolódó triggerek is törlődnek

====BEFORE használata====
* A =BEFORE= az utasítás előtt fut le, ha exceptiont dobunk, az utasítás nem hajtódik végre
* Leggyakoribb célok
** azonosító generálás
** logolás, ha a próbálkozást is naplózzuk (pl. biztonsági log)
** táblaméret korlátozása (<pre>INSERT</pre> előtt, pl. a vizsgára csak 20 ember jelentkezhet)
** származtatott mező kitöltése

====AFTER használata====
* származtatott mező kitöltése másik táblában, mint amire az SQL utasítás hivatkozik
* sikeres műveletek naplózása

====Triggerek lefutási sorrendje====
* =BEFORE= utasításra vonatkozó trigger
* minden sorra
** =BEFORE= sorra vonatkozó trigger
** utasítás
** =AFTER= sorra vonatkozó trigger
* =AFTER= utasításra vonatkozó trigger
* Ha 1 táblára több azonos típusú trigger is vonatkozik, a lefutási sorrend nem definiált

====Hivatkozás az éppen beszúrt vagy törölt adatokra====
* csak =FOR EACH ROW= trigger esetén használható
* Beszúrandó (<pre>INSERT</code) vagy módosított (<code>UPDATE</pre>) érték: =:new.oszlopnév=
* Módosítandó (<pre>UPDATE</code) vagy törlendőt (<code>DELETE</pre>) érték: =:old.oszlopnév=
* A =BEFORE= triggerekben a =:new= értékek írhatók, az =:old= értékek csak olvashatók
* Az =AFTER= triggerekben mindkettő csak olvasható

===DDL triggerek===

<code class="pre">CREATE OR REPLACE TRIGGER név
{BEFORE | AFTER} DDL esemény [OR DDL esemény ...]
ON SCHEMA
[WHEN feltétel]
PL/SQL blokk</code>

===Szerver triggerek===

<code class="pre">CREATE OR REPLACE TRIGGER név
{BEFORE | AFTER} adatbázis esemény [OR adatbázis esemény ...]
ON DATABASE
[WHEN feltétel]
PL/SQL blokk</code>

===Példák===

====ID generálás====

<code class="pre">CREATE TRIGGER agyag_idtr BEFORE INSERT ON anyag FOR EACH ROW
BEGIN
SELECT anyag_seq.NEXTVAL INTO :new.anyag_id FROM DUAL;
END;</code>

====Kurzus létszám korlátozása====

Relációs séma:
<code class="precompact">
+------= +----------------+ +-----------+=
| Student |-------|| Student_course ||-------|| Course
|}
+------= +----------------+ +-----------+=
| cou_limit |
|}
+--------==
</code>
<code class="pre">CREATE OR REPLACE TRIGGER diak_limit
BEFORE INSERT ON student_course FOR EACH ROW
DECLARE
db INT;
dblimit INT;
BEGIN
SELECT count(*) INTO db FROM student_course WHERE sc_courseid = :new.sc_courseid;
SELECT cou_limit INTO dblimit FROM course WHERE cou_id = :new.sc_courseid;
IF db>=dblimit THEN
RAISE_APPLICATION_ERROR(-20111, 'Betelt');
END IF;
END;</code>

====Trigger szintű constraintek====

TODO:
* Formula 1 relációs séma
* milyen constrainteknek van értelme jelen esetben
* kód a helyezés számítására

-- [[PallosPeter|Peti]] - 2006.03.27.

[[Category:Valaszthato]]

AdatbServerProgJegyzet20060224

2013-03-31T19:03:29Z

2001:738:2001:2078:0:208:37:0: Átirányítás ide: Adatbázisok szerver oldali programozása Előadás, 2006.02.24.

#REDIRECT [[Adatbázisok_szerver_oldali_programozása_Előadás,_2006.02.24.]]

Adatbázisok szerver oldali programozása Előadás, 2006.02.24.

2013-03-31T19:03:28Z

2001:738:2001:2078:0:208:37:0: Új oldal, tartalma: „{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060224}}  <style> code.pre { white-space: pre; display:block; me…”

{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060224}}



<style>
code.pre { white-space: pre; display:block; mergin-top: 2px; margin-bottom: 2px;}
li { line-height: 18px; }
</style>

<br/> [[AdatbServerProgJegyzet20060221|<<]] Előadás, 2006.02.24. [[AdatbServerProgJegyzet20060303|>>]]

__TOC__

==Oracle és Sql Server 2005 közötti különbségek==

* String összefűzés
** Oracle: =SELECT vezetéknév ||| ' ' || keresztnév FROM felhasználók=
** MS SQL: =SELECT vezetéknév + ' ' + keresztnév FROM felhasználók=
<br/> Szám és string összefűzésénél a számot először konvertálni kell: =CONVERT(varchar, oszlopnév)=

* =NULL= érték kezelés
** Oracle: =NVL(oszlop, érték)=
** MS SQL: =ISNULL(oszlop, érték)=

* Aktuális dátum
** Oracle: =SELECT sysdate FROM DUAL=
<br/> A =DUAL= egy 1 soros beépített tábla. Oracle-ben nem hagyható el a =SELECT= mögül a =FROM=, ezért kell trükközni.
** MS SQL: =SELECT getdate()=

* Stringből dátum
** Oracle: =INSERT INTO tábla VALUES(TO_DATE('2006-02-24', 'yyyy-mm-dd'))=
** MS SQL: automatikusan konvertál stringből, többféle formátumot felismer (pl. '2006-02-24')

* Első 5 sor
** Oracle: =rownum<=5=. Operátorként itt csak < és <= megengedett. Ha egy táblának kérjük valamilyen rendezés (<pre>ORDER BY ...</pre>) szerinti első néhány sorát, a rendezést allekérdezébe (beágyazott lekérdezésbe) kell rakni, mert különben fordított sorrendben értékeli ki az <pre>ORDER BY</pre>-t és a <pre>rownum<=5</pre>-öt. (tehát először kapjuk meg a rendezetlen tábla első öt sorát, majd az <pre>ORDER BY</pre> csak a lekért rendezetlen öt sort rendezi) <code class="pre">SELECT * FROM (
<br/> SELECT id, name
<br/> FROM students ORDER BY name) a
<br/>WHERE rownum<=5</code>
** MS SQL: =SELECT TOP 5 id, name FROM student ORDER BY name=

* Intervallum kiválasztása
** Oracle: <code class="pre">SELECT * FROM
<br/> (SELECT id, name FROM student ORDER BY name) a
<br/>WHERE rownum<=10
<br/>MINUS
<br/>SELECT * FROM
<br/> (SELECT id, name FROM student ORDER BY name) b
<br/>WHERE rownum<=5</code>
** MS SQL: <code class="pre">SELECT * FROM
<br/> (SELECT TOP 10 id, name FROM student ORDER BY név) a
<br/>EXCEPT
<br/>SELECT * FROM
<br/> (SELECT TOP 5 id, name FROM student ORDER BY név) b</code>

* Táblanevek lekérdezése
** Oracle: =SELECT table_name FROM user_tables=
** MS SQL: =sp_tables=

* Logolás
** Oracle: =DBMS_OUTPUT.PUT_LINE('a')=
** MS SQL: =print 'a'=

* Azonosító generálás (primary key oszlop automatikus generálása):
** ld. [[AdatbServerProgJegyzet20060217#generate_identifier|2006.02.17. jegyzet]]

==PL/SQL==

===Tárolt eljárások===

Az Oracle-ben minden utasítást blokkba kell zárni. Az utasításokat és a blokkokat pontosvessző zárja le.

====Anonim blokk====

<code class="pre">[<<cimke>>]
[DECLARE
változó deklaráció, lokális eljárások]
BEGIN
utasítások, blokkok
[EXCEPTION
kivételkezelés]
END;</code>

====Eljárás====

<code class="pre">PROCEDURE név(paraméterek)
[IS
...]
BEGIN
...
[EXCEPTION
...]
END;</code>

====Függvény====

<coe class="pre">FUNCTION név(paraméterek)
RETURN típus
[IS
...]
BEGIN
...
[EXCEPTION
...]
END;</code>

===Komment===

* Egysoros: =--=
* Többsoros: =/* */=, nem ágyazhatók egymásba

===Változók===

* azonosító: [a-zA-Z][a-zA-Z0-9$_]*, nem case sensitive és legfeljebb 30 hosszú. Ha az azonosító ettől különböző formájú, idézőjelek közé kell tenni. A hosszba az idézőjeleket is bele kell számolni.
* deklaráció: ha nem adunk meg kezdőértéket, <pre>NULL</pre>-t vesz fel.<code class="pre">DECLARE
<br/> szam1 INT;
<br/> szam2 INT := 3;
<br/> szam3 INT DEFAULT 5;
<br/> datum DATE NOT NULL := '2006-FEB-12';
<br/> pi CONSTANT REAL := 3.14;
<br/> diak_nev student.stud_name%TYPE;
<br/> diak_rekord student%ROWTYPE;</code>
* értékadás: <code class="pre">BEGIN
<br/> szám1 := 3;
<br/> SELECT id INTO szam2 FROM ...;
<br/> =SELECT id,id2 INTO szam3,szam4 FROM ...;</code> A <pre>SELECT</pre>-nek pontosan 1 sorral kell visszatérnie, különben <pre>NO_DATA_FOUND</pre> vagy <pre>TOO_MANY_ROWS</pre> exceptiont dob.
* névelfedés
** az SQL oszlopnév elfedi a változónevet
** a belső blokkban deklarált változó elfedi a külsőt
** az elfedett változóra <i>cimkenév.változónév</i> alakban tudunk hivatkozni
* változó hatásköre = a kódnak azon része, ahol tudunk rá hivatkozni
* változó láthatósága = az a blokk, amiben a változót dekaráltuk

===Vezérlési szerkezetek===

Minden vezérlési szerkezetet megelőzhet egy <pre><<cimkenév>></pre> formátumú cimke.

* =NULL=: üres utasítás. A blokkokban mindenképpen szerepelni kell utasításnak. Ha pl. egy tárolt eljárást nem akarunk megírni, <pre>NULL</pre>-t írunk a törzsébe, hogy leforduljon.

* =GOTO cimke=: nem ugorhat bele alblokkba, vagy ki a blokkból. Az =EXCEPTION= részből nem ugorhat vissza a saját blokkjába.

====Elágazások====

* <code class="pre">IF feltétel THEN
<br/> utasítások;
<br/>[ELSIF feltétel THEN
<br/> utasítások;]
<br/>[ELSE
<br/> utasítások;]
<br/>END IF;</code>

* Searched case (ugyanaz, mint az =IF=) <code class="pre">CASE
<br/>WHEN keresési feltétel THEN
<br/> utasítások;
<br/>[ELSE
<br/> utasítások;]
<br/>END CASE;</code>

* <code class="pre">CASE szelektor
<br/>WHEN kif THEN
<br/> utasítások;
<br/>[ELSE
<br/> utasítások;]
<br/>END CASE;</code>

====Ciklusok====

A ciklusokból az =EXIT;= utasítással lehet kilépni. Feltételes kilépés: =EXIT WHEN feltétel;=.

* végtelen ciklus: <code class="pre">LOOP
<br/> utasítások;
<br/>END LOOP;</code>

* <code class="pre">WHILE feltétel LOOP
<br/> utasítások;
<br/>END LOOP;</code>

* <code class="pre">FOR változó IN [REVERSE] alsó..felső LOOP
<br/> utasítások;
<br/>END LOOP;</code>A változó helyben van deklarálva, a hatásköre a ciklus belseje, és nem módosítható. A ciklus határai lehetnek változók, de összetett kifejezés már nem.

===Példa===

Az =ember(id, név)= táblából írjuk ki a neveket. Megjegyzés: gányolunk egy kicsit, kurzorokkal sokkal szebben megoldható a feladat.

<code class="pre">DECLARE
minid INT;
maxid INT;
db INT;
embernev ember.nev%TYPE;
BEGIN
SELECT min(id), max(id) INTO minid, maxid FROM ember;
FOR i IN minid..maxid LOOP
SELECT count(*) INTO db FROM ember WHERE id=i;
IF db=1 THEN
SELECT nev INTO embernev FROM ember WHERE id=i;
DBMS_OUTPUT.PUT_LINE(embernev);
END IF;
END LOOP;
END;</code>

-- [[PallosPeter|Peti]] - 2006.02.26.

[[Category:Valaszthato]]

AdatbServerProgJegyzet20060328

2013-03-31T19:03:25Z

2001:738:2001:2078:0:208:37:0: Átirányítás ide: Adatbázisok szerver oldali programozása Labor, 2006.03.28.

#REDIRECT [[Adatbázisok_szerver_oldali_programozása_Labor,_2006.03.28.]]

Adatbázisok szerver oldali programozása Labor, 2006.03.28.

2013-03-31T19:03:24Z

2001:738:2001:2078:0:208:37:0: Új oldal, tartalma: „{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060328}}  <style> code.pre { white-space: pre; display: block; m…”

{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060328}}



<style>
code.pre { white-space: pre; display: block; margin-top: 2px; margin-bottom: 2px; }
li { line-height: 18px; }
</style>

<br/> [[AdatbServerProgJegyzet20060324|<<]] Labor, 2006.03.28. [[AdatbServerProgJegyzet20060331|>>]]

==Tárolt alprogramok==

<ol>
<li>Írj egy tárolt eljárást, aminek a segítségével kurzusokat lehet felvenni. Az eljárásnak
két bemenő paramétere van, a diák azonosító, és a kurzusazonosító. Az eljárás a
következő esetekben generáljon hibát:
* ha a kurzus már betelt
* ha a hallgató már jelentkezett erre a kurzusra
* ha nem létezik a paraméterként beadott diák vagy kurzus.

<li>Írj egy tárolt eljárást, aminek a segítségével ki lehet törölni egy hallgatót egy
kurzusról. Az eljárásnak legyen két bemenő paramétere, a diák azonosító és a törölni
kívánt kurzusazonosító. Az eljárás akkor dobjon hibát, ha nem létezik a törölni kívánt
diák vagy kurzus.

<li>Írj függvényt, aminek két bemeneti paramétere van, a pilóta azonosítója és egy
évszám, visszatérési értéke pedig a pilóta adott évben szerzett pontjainak az összege!

<li>Írj függvényt, aminek bemenete egy helyezés, visszatérési értéke pedig a helyezéshez
tartozó pontszám!
</ol>

==Triggerek==

<ol>
<li>Írj egy triggert, ami az anyag táblába való beszúrás esetén automatikusan generál
azonosítót, ha a beszúrni kívánt adatok közt nincs megadva az azonosító!

<code class="pre">CREATE SEQUENCE anyag_seq START WITH 100 INCREMENT BY 1
CREATE OR REPLACE TRIGGER anyag_idtr BEFORE INSERT ON anyag FOR EACH ROW
BEGIN
IF :new.anyag_id IS NULL THEN
SELECT anyag_seq.NEXTVAL INTO :new.anyag_id FROM DUAL;
END IF;
END;</code>

<li>Írj egy triggert, ami csak akkor engedi, hogy egy hallgató felvegyen egy kurzust, ha a
kurzus még nincs betelve.

<li>Készíts el egy nézetet, aminek segítségével a FELKESZ_ANYAG táblát láthatjuk, de
az anyag és a félkész nevét láthatjuk, nem pedig a kódját. Ezek után írj egy triggert,
ami lehetővé teszi, hogy új anyagot adjunk hozzá a félkészhez a nézeten keresztül!

'''View létrehozása'''

<code class="pre">CREATE VIEW felkeszanyag AS
SELECT felkesz_nev, anyag_nev, fa_mennyiseg
FROM felkesz, anyag, felkesz_anyag
WHERE felkesz.felkesz_id = felkesz_anyag.fa_felkeszid
AND anyag.anyag_id = felkesz_anyag.fa_anyagid</code>

'''Trigger létrehozása'''

<code class="pre">CREATE OR REPLACE TRIGGER felkesz_anyag_insert
INSTEAD OF INSERT ON felkeszanyag FOR EACH ROW
DECLARE
anyagid INT;
felkeszid INT;
BEGIN
SELECT anyag_id INTO anyagid FROM anyag
WHERE anyag_nev = :new.anyag_nev;
SELECT felkesz_id INTO felkeszid FROM felkesz
WHERE felkesz_nev = :new.felkesz_nev;
INSERT INTO felkesz_anyag (fa_felkeszid, fa_anyagid, fa_mennyiseg)
VALUES (felkeszid, anyagid, :new.fa_mennyiseg);
END;</code>

'''Tesztelés'''

<code class="pre">INSERT INTO felkeszanyag VALUES ('tejszinhab', 'liszt', 1);
COMMIT;</code>

<li>Módosítsd a PILOTA_FUTAM táblát úgy, hogy legyen egy oszlop a pilóta
pontszámának tárolására is (ez származtatott adat, a PILOTA_FUTAM tábla alapján
számítható). Készíts triggert, ami karbantartja az új oszlop értékét, bármilyen
módosítást is hajtunk végre a pilóta pontszámát érintő táblában! Próbálj meg olyan
megoldást találni, ami a lehető legkevesebb mővelettel oldja meg a feladatot!

<code class="pre">ALTER TABLE futam_pilota ADD pontszam INT</code>

<code class="pre">CREATE OR REPLACE TRIGGER pilota_pontszam
BEFORE INSERT OR UPDATE
ON futam_pilota FOR EACH ROW
BEGIN
SELECT decode(:new.helyezes, NULL, 0, 1, 10, 2, 8, 3, 6, 4, 5, 5, 4, 6, 3, 7, 2, 8, 1, 0)
INTO :new.pontszam
FROM dual;
END;</code>

<li>Készíts nézetet, ami az alábbi mezőket tartalmazza: évszám, csapat neve, pilóta neve,
pilóta típusa az adott csapatban az adott évben. Készíts triggert, ami lehetővé teszi új
pilótát felvenni egy adott csapatba a nézeten keresztül.

<li>Készíts triggert, ami biztosítja, hogy egy évben egy pilóta csak egy csapatban
szerepelhessen, valamint egy csapatban egyszerre csak egy elsőszámú és egy
másodszámú pilóta lehessen, de akármennyi teszt pilóta.

<li>A cukrász adatbázisban módosítsd a FELKESZ táblát úgy, hogy egy mezőben a
félkész egységárát is el tudjuk tárolni. Ez egy származtatott adat lesz, annak az
érdekében, hogy a termék árának a lekérdezése egyszerőbb és gyorsabb legyen. Az új
oszlop a (felkesz_ar float). Írd meg a triggereket, amik karban tartják ezt a mezőt.
Triggereket az alábbi eseményekre kell írni:
* Egy anyag ára megváltozott.
* Új anyag kerül a félkész anyaglistájába.
* Egy anyagot törölnek a félkész anyaglistájából.
* Megváltozik a mennyisége a félkész által tartalmazott anyagnak (FELKESZ_ANYAG táblában).

<code class="pre">ALTER TABLE felkesz ADD felkesz_ar FLOAT</code>

<li>A cukrászat adatbázist egészítsd ki az alábbi rendelés táblával!
<code class="pre">CREATE TABLE rendeles
(
rendeles_id INT CONSTRAINT rendeles_primary_key PRIMARY KEY,
rendeles_datum date NOT NULL,
rendeles_anyagid int NOT NULL,
rendeles_mennyiseg float NOT NULL,
rendeles_rendezve date
)</code>
Írj triggert, ami felad egy újabb rendelést, ha valamely anyag mennyisége a minimális
érték alá csökken. A rendelés mennyisége legyen annyi, hogy utána a maximális
mennyiség legyen a raktárban.
</ol>

-- [[PallosPeter|Peti]] - 2006.03.28.

[[Category:Valaszthato]]

AdatbServerProgJegyzet20060331

2013-03-31T19:03:23Z

2001:738:2001:2078:0:208:37:0: Átirányítás ide: Adatbázisok szerver oldali programozása Előadás, 2006.03.31.

#REDIRECT [[Adatbázisok_szerver_oldali_programozása_Előadás,_2006.03.31.]]

Adatbázisok szerver oldali programozása Előadás, 2006.03.31.

2013-03-31T19:03:21Z

2001:738:2001:2078:0:208:37:0: Új oldal, tartalma: „{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060331}}  <style> code.pre { white-space: pre; display: block; m…”

{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060331}}



<style>
code.pre { white-space: pre; display: block; mergin-top: 2px; margin-bottom: 2px;}
li { line-height: 18px; }
</style>

<br/> [[AdatbServerProgJegyzet20060328|<<]] Előadás, 2006.03.31. [[AdatbServerProgJegyzet20060404|>>]]

__TOC__

==Csomagok==

A csomag egy olyan séma objektum, ami logikailag összetartozó elemeket tartalmaz. Részei:
* Típusok
* Alprogramok
* Kivételek

A csomagok 2 részből állnak:
* nyilvános specifikáció (interfész)
* privát törzs (implementáció)

===Csomagok célja===

<ol>
<li> logikailag összetartozó függvények egységbe zárása
<li> olyan eljárás létrehozása, aminek a kimenő paramétere eredményhalmaz
* SQL Serverben van lehetőség eljáráson belül több sort visszaadó <pre>SELECT</pre>-ek hívására. Az eredményt a kliens olvassa ki.
* Oracle-ben az eredményt kötelező változóban elhelyezni (<pre>SELECT ... INTO</pre>). Több soros kimenetet csak kurzor változóval lehet kezelni, amit vagy lokálisan, vagy csomagon belül kell deklarálni. A Java támogatja a kurzor típust, a Visual Studioban csak külön plugin telepítése után lehet kiolvasni a kurzorváltozó tartalmát.
</ol>

===Csomagok előnyei===
* modularitás
* egyszerű programfejlesztés: a specifikáció fordítható a törzs nélkül is
* információ elrejtés
* bővített funkcionalitás: a változók és a kurzorok a session végéig élnek, az alprogramok megosztva használhatják őket
* teljesítmény: lehetőség van tranzakciók között infót átadni táblák használata nélkül (1 sessionben)

===Felhasználó által definiált csomagok===

Vizsgán az Oracle rész egyik feladata biztosan csomagkezelésről fog szólni.

====Szintaxis====

'''Csomag fejléc'''

<code class="pre">CREATE [OR REPLACE] PACKAGE csomagnév
[AUTHID {CURRENT_USER|DEFINER}]
{IS|AS}
kollekció típusok
altípusok
konstansok
kivételek
objektumok
kurzor deklarációk
függvény deklarációk
eljárás deklarációk
END [csomagnév];</code>

* Az =AUTHID= azt határozza meg, hogy a csomag alprogramjai kinek a nevében fussanak.
* A deklarációs részben utoljára kell felsorolni a függvényeket és az eljárásokat.

'''Csomag törzs'''

<code class="pre">CREATE [OR REPLACE] PACKAGE BODY csomagnév
...
lokális változók
kurzor törzsek
függvény törzsek
eljárás törzsek
[BEGIN
utasítások;]
END [csomagnév];</code>

* A csomag törzsében az alprogramok deklarációjának karakterről karakterre meg kell egyeznie a csomag fejlécében található deklarációval.
* A =BEGIN= és =END= közötti szakasz az első függvényhívás előtt fut le.

====Példa: jogosultságkezelés a tanár-diák adatbázisban====

<code class="pre">ALTER TABLE student ADD stud_pwd VARCHAR2(20);
UPDATE student SET stud_pwd='jelszo' WHERE stud_id='XSFXCF';
 
CREATE PACKAGE userlogin IS
hibasjelszo EXCEPTION;
nincsbelepve EXCEPTION;
PROCEDURE login(id CHAR, passwd VARCHAR2);
PROCEDURE logout;
FUNCTION is_logged_in(hibatdob BOOLEAN) RETURN BOOLEAN;
END;
 
CREATE PACKAGE BODY userlogin IS
loggedin BOOLEAN;
 
PROCEDURE login(id CHAR, passwd VARCHAR2) IS
jelszo VARCHAR2(20);
BEGIN
SELECT stud_pwd INTO jelszo FROM student WHERE stud_id=id;
IF passwd!=jelszo THEN
RAISE hibasjelszo;
ELSE
loggedin:=TRUE;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
loggedin:=FALSE;
RAISE hibasjelszo;
END;
 
PROCEDURE logout IS
BEGIN
loggedin:=FALSE;
END;
 
FUNCTION is_logged_in(dobhibat BOOLEAN)
RETURN boolean IS
BEGIN
IF dobhibat AND loggedin=FALSE THEN
RAISE nincsbelepve;
END;
RETURN loggedin;
END;
 
BEGIN
loggedin:=FALSE;
END userlogin;</code>

====Példa a userlogin csomag használata====

<code class="pre">CREATE PROCEDURE getcourses(diakid CHAR, szemeszter CHAR)
IS
belepve BOOLEAN;
CURSOR c IS SELECT sub_id, sub_name
FROM subject, course, sutdent_course
WHERE sc_studentid = diakid
AND sc_courseid = cou_id
AND cou_semestername = szemeszter
AND cou_subjectid = sub_id;
BEGIN
belepve:=userlogin.is_logged_in(true);
FOR crec IN c LOOP
DBMS_OUTPUT.PUT_LINE(crec.sub_id ||| ' ' || crec.sub_name);
END LOOP;
EXCEPTION
WHEN userlogin.nincsbelepve
RAISE_APPLICATION_ERROR(-20101, 'nincs belépve');
END;
 
DECLARE
code VARCHAR2(20);
message VARCHAR2(200);
BEGIN
getcourses('XSFXCF', '20012');
EXCEPTION
WHEN OTHERS THEN
code:=SQLCODE;
message:=SQLERRM;
DBMS_OUTPUT.PUT_LINE(code ||| ' ' || message);
 
userlogin.login('XSFXCF', 'jelszo');
getcourses('XSFXCF', '20012');
 
userlogin.logout;
getcourses('XSFXCF', '20012');
END;</code>

====Példa kurzor típusú kimenetre====

<code class="pre">CREATE PACKAGE tanar_csomag IS
TYPE tanarlista_type IS REF CURSOR;
PROCEDURE tanar_lista(tanszekkod CHAR, tanarlista OUT tanarlista_type);
END;
 
CREATE PACKAGE BODY tanar_csomag IS
PROCEDURE tanar_lista(tanszekkod CHAR, tanarlista OUT tanarlista_type) IS
BEGIN
OPEN tanarlista FOR
SELECT tea_grade ||| ' ' |||| tea_lname |||| ' ' || tea_fname, tea_id, status_id
FROM teacher, sort_status
WHERE tea_status=status_id AND tea_dept=tanszekkod
ORDER BY status_id;
END;
END tanar_csomag;</code>

'''Használat:'''

<code class="pre">DECLARE
tanlist tanar_csomag.tanar_lista('AUT', tanlist);
LOOP
FETCH tanlist INTO nev, kod, statusz;
EXIT WHEN tanlista%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(...);
END LOOP;
END;</code>

===Beépített csomagok===

Vizsgakérdés lehet, hogy soroljunk fel hármat.

* =DBMS_OUTPUT=: standard output
* =STANDARD=: kivételek, sysdate, sum, ...
* =DBMS_LOB=: large objectek kezelése
* =DBMS_PIPE=: sessionök közti kommunikáció
* =UTL_FILE=: filekezelés
* =DBMS_LOCK=:
* =DBMS_RANDOM=: véletlenszám generálás

-- [[PallosPeter|Peti]] - 2006.04.02.

[[Category:Valaszthato]]

AdatbServerProgJegyzet20060321

2013-03-31T19:03:20Z

2001:738:2001:2078:0:208:37:0: Átirányítás ide: Adatbázisok szerver oldali programozása Labor, 2006.03.21.

#REDIRECT [[Adatbázisok_szerver_oldali_programozása_Labor,_2006.03.21.]]

Adatbázisok szerver oldali programozása Labor, 2006.03.21.

2013-03-31T19:03:19Z

2001:738:2001:2078:0:208:37:0: Új oldal, tartalma: „{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060321}}  <style> pre { margin-top: 2px; margin-bottom: 2px; } li …”

{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060321}}



<style> pre { margin-top: 2px; margin-bottom: 2px; } li { line-height: 18px; } </style>

<br/> [[AdatbServerProgJegyzet20060317|<<]] Labor, 2006.03.21. [[AdatbServerProgJegyzet20060324|>>]]

<ol>
<li>Készíts egy tárolt eljárást, aminek bemeneti paramétere egy pilóta neve, két kimeneti paramétere pedig a pilóta magassága és súlya! Az eljárást próbáld ki SQL Windowból, a kimeneti paramétereket a DBMS_OUTPUT.PUT_LINE metódussal írasd ki a
kimenetre!

<ul>
<li>Program Window:
<pre>CREATE OR REPLACE PROCEDURE pilotaparams(pnev IN VARCHAR2, magassag OUT INT, suly OUT INT) IS
BEGIN
SELECT pilota.magassag, pilota.suly INTO magassag, suly
FROM pilota
WHERE pilota.nev = pnev;
END pilotaparams;</pre>

<li>SQL Window:
<pre>DECLARE
magassag INT;
suly INT;
BEGIN
pilotaparams('Cristijan Albers', magassag, suly);
DBMS_OUTPUT.PUT_LINE('Magassag: ' || magassag || ' Suly: ' || suly);
END;</pre>
</ul>

<li>Próbáld ki az előző feladatban megoldott eljárás hívását a Test Window-ból! A Test Window egy olyan környezet, ahol lehetőség van környezeti változókat használni a
blokkon belül. Ha környezeti változóban adjuk meg a bemeneti és a két kimeneti
paramétert, akkor az eredményt az ablak alsó felében láthatjuk, nem kell nekünk extra
kiíratni a kimenetre. A környezeti változók deklarálása is a Test Window alsó
ablakában lehetséges, ahol meg kell adni a változó nevét és típusát, ha bemeneti
paraméter, akkor az értékét is. A változóra a blokkon belül a kettőspont változónévvel lehet hivatkozni. (Pl. a változó neve legyen pilotanev, akkor a blokkon belül =:pilotanev= kifejezéssel hivatkozunk rá.)

<pre>BEGIN
pilotaparams(:pilotanev, magassag, suly);
END;</pre>

Test Window alján:

{| border="1"
| '''Variable''' || '''Type''' || '''Value'''
|-
| pilotanev || String || Cristijan Albers
|-
| magassag || Integer ||
|-
| suly || Integer ||
|}

F8-ra kitölti a hiányzó értékeket.

<li>Egészítsd ki az előző feladatot hibakezeléssel is. Amennyiben nem létezik a
bemenetként megadott pilóta, akkor a kimeneti értékek -1 legyenek!

<pre>CREATE OR REPLACE PROCEDURE pilotaparams(pnev IN VARCHAR2, magassag OUT INT, suly OUT INT) IS
BEGIN
SELECT pilota.magassag, pilota.suly INTO magassag, suly
FROM pilota
WHERE pilota.nev = pnev;
EXCEPTION
WHEN NO_DATA_FOUND THEN
magassag := -1;
suly := -1;
END pilotaparams;</pre>

<li>Készíts egy tárolt eljárást, aminek bemeneti paramétere egy csapat neve és egy
évszám! Az eljárás listázza ki a kimenetre az adott csapat adott évben szerződtetett
pilótáit!

<pre>CREATE OR REPLACE PROCEDURE csapat_szezon_pilotak(csnev IN VARCHAR2, szev IN int) IS
BEGIN
FOR rec IN (
SELECT pilota.nev
FROM csapat, csapat_pilota, csapat_szezon, pilota
WHERE csapat.csapatid = csapat_szezon.csapatid
AND csapat_szezon.csapatszezonid = csapat_pilota.csapatszezonid
AND pilota.pilotaid = csapat_pilota.pilotaid
AND csapat.nev = csnev
AND csapat_szezon.ev = szev
) LOOP
DBMS_OUTPUT.PUT_LINE(rec.nev);
END LOOP;
END csapat_szezon_pilotak;</pre>

<li>Írj egy olyan eljárást, aminek az egyetlen bemenő paramétere az évszám, és az adott szezonban létező csapatok pilótáit listázza ki a kimenetre szépen formázva (a csapat nevek alatt bentebb vannak felsorolva a pilóták nevei)!

<pre>CREATE OR REPLACE PROCEDURE szezon_pilotak(szev IN int) IS
BEGIN
FOR rec IN (
SELECT csapat.csapatid, csapat.nev
FROM csapat, csapat_szezon
WHERE csapat.csapatid = csapat_szezon.csapatid
AND csapat_szezon.ev = szev
) LOOP
DBMS_OUTPUT.PUT_LINE(rec.nev);
FOR rec2 IN (
SELECT pilota.nev
FROM csapat_pilota, csapat_szezon, pilota
WHERE csapat_szezon.csapatszezonid = csapat_pilota.csapatszezonid
AND pilota.pilotaid = csapat_pilota.pilotaid
AND rec.csapatid = csapat_szezon.csapatid
AND csapat_szezon.ev = szev
) LOOP
DBMS_OUTPUT.PUT_LINE(' ' || rec2.nev);
END LOOP;
END LOOP;
END szezon_pilotak;</pre>

<li>Írj egy tárolt eljárást, aminek segítségével egy csapatot és egy pilótát tudunk egy szezonban összerendelni. Az eljárás bemenő paramétere a csapat neve, a pilóta neve, a szezon évszáma és a pilóta típusa legyen. Használd a hibakezelési lehetőségeket az alábbi szituációkra, és mindegyikről külön küldj hibaüzenetet:

* Nem létezik az adott nevő csapat vagy az adott évben nem indult.
* Nem létezik az adott pilóta.
* Nincs ilyen szezon definiálva az adatbázisban.
* Már tagja az adott pilóta az adott csapatnak az adott évben.
* Ha a pilóta típusa 1 vagy 2 és ilyen már létezik, akkor vegye fel másik számúnak, ha az még nem foglalt, vagy ha már mindkét pozíció foglalt, akkor teszt pilótának, és küldjön erről üzenetet.

<pre>CREATE OR REPLACE PROCEDURE szezon_pilotak(
csapatnev VARCHAR2;
pilotanev VARCHAR2;
szezonev INT;
pilotatipus CHAR;
) IS
BEGIN
-- to be continued...
END;</pre>

<li>Írj függvényt, ami a bemeneteként kapott FLOAT típusú számot szöveges
formátummá konvertálja a következők szerint:
óra:perc:másodperc.ezredmásodperc!

<li>Írj lekérdezést, ami az előző feladatban megírt függvény segítségével kilistázza a 2005-ös Ausztrál Nagydíj eredményét (pilóta név, helyezés, idő)!

<li>Írj lekérdezést, ami az előző feladatban megírt lekérdezést egészíti ki olyan módon, hogy egy újabb oszlopban az adott időeredmény és az első helyezett eredményének a különbsége is megjelenik! Az eredményedet ellenőrizd le a http://www.formula1.com oldalon található táblázattal! Az eredmény így nézzen ki:

TODO: táblázat

<li>Írj egy függvényt, ami a tanév szemesztereinek következőképpen megadott kódját
szöveges, emberek számára egyértelmően értelmezhetővé konvertálja! A
szemeszterkód egy ötjegyő szám, aminek első négy jegye az évet jelenti, míg az
ötödik számjegye az év félévét jelenti. Például a 2002 év őszi félévét a 20022 jelöli,
míg a 2003 tavaszi félévet a 20031 kód jelöli. Megjelenítéskor az első esetben
„2002/2003 I. félév” a második esetben pedig „2002/2003 II. félév” szöveget
szeretnénk látni. A megírt függvényt próbáld ki néhány minta bemenettel!

<li>Az előző feladatban megírt függvény segítségével listázd ki a tanár-diák adatbázis =SEMESTER= tábla adatait!
</ol>
-- [[PallosPeter|Peti]] - 2006.03.21.

[[Category:Valaszthato]]

AdatbServerProgJegyzet20060307

2013-03-31T19:03:17Z

2001:738:2001:2078:0:208:37:0: Átirányítás ide: Adatbázisok szerver oldali programozása Labor, 2006.03.07.

#REDIRECT [[Adatbázisok_szerver_oldali_programozása_Labor,_2006.03.07.]]

Adatbázisok szerver oldali programozása Labor, 2006.03.07.

2013-03-31T19:03:14Z

2001:738:2001:2078:0:208:37:0: Új oldal, tartalma: „{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060307}}  <br/> << Labor,…”

{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060307}}



<br/> [[AdatbServerProgJegyzet20060303|<<]] Labor, 2006.03.07. [[AdatbServerProgJegyzet20060310|>>]]

<ol>
<li> Futtasd le a minta adatbázisok menüpont alatt található scripteket, amik létrehozzák a CUKRÁSZ adatbázis tábláit, és feltöltik egységes minta adatokkal!

* File / New / Command Window
* A [https://avalon.aut.bme.hu/~reni/aszp/script.html]-ről letölthető 2 SQL scriptet lefuttatni.
* All objects helyett csak a My objects-et mutassa.
* Tables / Refresh
* Cukrász adatbázis séma grafikusan: https://avalon.aut.bme.hu/~reni/aszp/script/uj/sema/cukrasz_sema.pdf

<li> Listázd ki, megnevezés szerint fordított ABC sorrendben, hogy milyen félkész
termékek vannak (minden adatot)!

* File / New / SQL Window
* =SELECT * FROM felkesz ORDER BY felkesz_nev DESC;=
* Ha a találatok nem férnek ki az ablakba, csak az első néhányat jeleníti meg. A többi a dupla zöld lefele nyíllal kérhető le.

<li> Listázd ki, hogy a gyümölcstorta alap milyen alapanyagokból áll, és melyikből mennyi kell! (név, mennyiség, egység)

<pre>SELECT anyag_nev AS nev,
fa_mennyiseg || ' ' || egyseg_nev AS mennyiseg
FROM felkesz, felkesz_anyag, anyag, egyseg
WHERE felkesz.felkesz_id = felkesz_anyag.fa_felkeszid
AND anyag.anyag_id = felkesz_anyag.fa_anyagid
AND anyag.anyag_egyseg = egyseg.egyseg_id
AND felkesz.felkesz_nev = 'gyumolcstorta alap'</pre>

<li> Listázd ki azokat a termékeket, amihez kell vaníliáscukor!

<pre>SELECT termek_nev
FROM termek, termek_anyag, anyag
WHERE termek.termek_id = termek_anyag.ta_termekid
AND anyag.anyag_id = termek_anyag.ta_anyagid
AND anyag_nev = 'vaniliascukor'
UNION
SELECT termek_nev
FROM termek, termek_felkesz, felkesz, felkesz_anyag, anyag
WHERE termek.termek_id = termek_felkesz.tf_termekid
AND felkesz.felkesz_id = termek_felkesz.tf_felkeszid
AND felkesz.felkesz_id = felkesz_anyag.fa_felkeszid
AND anyag.anyag_id = felkesz_anyag.fa_anyagid
AND anyag_nev = 'vaniliascukor'</pre>

<li> Listázd ki azokat a termékeket, amikhez nem kell vaníliáscukor!

<pre>SELECT termek_nev FROM termek
MINUS
(SELECT termek_nev
FROM termek, termek_anyag, anyag
WHERE termek.termek_id = termek_anyag.ta_termekid
AND anyag.anyag_id = termek_anyag.ta_anyagid
AND anyag_nev = 'vaniliascukor'
UNION
SELECT termek_nev
FROM termek, termek_felkesz, felkesz, felkesz_anyag, anyag
WHERE termek.termek_id = termek_felkesz.tf_termekid
AND felkesz.felkesz_id = termek_felkesz.tf_felkeszid
AND felkesz.felkesz_id = felkesz_anyag.fa_felkeszid
AND anyag.anyag_id = felkesz_anyag.fa_anyagid
AND anyag_nev = 'vaniliascukor')</pre>

<li> Listázd ki a legolcsóbb anyagot!

<pre>SELECT anyag_nev FROM anyag
WHERE anyag_egysegar = (SELECT MIN(anyag_egysegar) FROM anyag)</pre>

<li> Listázd ki, az alapanyag neve szerint sorrendben, hogy a gyümölcstorta milyen
alapanyagokból készül (név, mennyiség)!

<pre>SELECT anyag_nev
FROM termek, termek_anyag, anyag
WHERE termek.termek_id = termek_anyag.ta_termekid
AND anyag.anyag_id = termek_anyag.ta_anyagid
AND termek.termek_nev = 'Gyumolcstorta'
UNION
SELECT anyag_nev
FROM termek, termek_felkesz, felkesz, felkesz_anyag, anyag
WHERE termek.termek_id = termek_felkesz.tf_termekid
AND felkesz.felkesz_id = termek_felkesz.tf_felkeszid
AND felkesz.felkesz_id = felkesz_anyag.fa_felkeszid
AND anyag.anyag_id = felkesz_anyag.fa_anyagid
AND termek.termek_nev = 'Gyumolcstorta'</pre>

<li> Számold ki, hogy egy gyümölcstorta alap mennyibe kerül!

<pre>SELECT SUM(fa_mennyiseg*anyag_egysegar/felkesz_mennyiseg) AS ar
FROM felkesz, felkesz_anyag, anyag
WHERE felkesz.felkesz_id = felkesz_anyag.fa_felkeszid
AND anyag.anyag_id = felkesz_anyag.fa_anyagid
AND felkesz.felkesz_nev = 'gyumolcstorta alap'</pre>

<li> Számold ki, hogy az egyes félkész termékek hány forintba kerülnek!
<li> Listázd ki, hogy mely félkész termékek olcsóbbak 150 Ft-nál!
<li> Számold ki, hogy egy gyümölcstorta mennyibe kerül!
<li> Számold ki, hogy az egyes termékek hány forintba kerülnek!
</ol>
-- [[PallosPeter|Peti]] - 2006.03.07.

[[Category:Valaszthato]]

AdatbServerProgJegyzet20060221

2013-03-31T19:03:09Z

2001:738:2001:2078:0:208:37:0: Átirányítás ide: Adatbázisok szerver oldali programozása Labor, 2006.02.21.

#REDIRECT [[Adatbázisok_szerver_oldali_programozása_Labor,_2006.02.21.]]

Adatbázisok szerver oldali programozása Labor, 2006.02.21.

2013-03-31T19:03:07Z

2001:738:2001:2078:0:208:37:0: Új oldal, tartalma: „{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060221}}  <style> ol li { line-height: 18px; } </style> <br/> [[A…”

{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060221}}



<style> ol li { line-height: 18px; } </style>

<br/> [[AdatbServerProgJegyzet20060217|<<]] Labor, 2006.02.21. [[AdatbServerProgJegyzet20060224|>>]]

Labor honlapja: https://avalon.aut.bme.hu/~reni/aszp


* Oracle kliens: PL/SQL Developer
<br/> Jelszó az adatbázishoz: https://avalon.aut.bme.hu/~reni/aszp/jelentkezes/aszp/userkod.php
<br/> A felhasználónév és a jelszó egyaránt az ott kapott kód.
* Minta adatbázis (Cukrász illetve Tanár-diák): https://avalon.aut.bme.hu/~reni/aszp/script.html
<br/> A script a File/New/Command Window-ból futtatható.
* A nézetet érdemes átállítani úgy, hogy az All objects helyett csak a My objects-et mutassa.
* A script futtatása után frissíteni kell a fa táblákat felsoroló ágát.
* SQL lekérdezést az SQL Window-ban kell írni, komment: =--= vagy =/* */=. Itt nem kell pontosvesszőzni, és egyszerre csak 1 utasítást fogad el.
* Alprogramok
** Tárolt eljárást a Program Windowban érdemes írni, mert fordításkor itt kijelzi a hibákat, míg a Command Windowban nem. Fordítás: F8.
** Az eljáráshívást SQL Window-ban =BEGIN= és =END;= közé kell ágyazni, Command Windowban pedig <pre>EXEC</pre>-et kell eléírni
** Ha a Command Windowból futtatjuk az eljárást, alapból nem jelenik meg az output, előbb be kell írni egy =SET SERVEROUTPUT ON;= sort.
* Labor feladat: https://avalon.aut.bme.hu/~reni/aszp/gyakorlat.html

-- [[PallosPeter|Peti]] - 2006.03.07.

[[Category:Valaszthato]]

AdatbServerProgJegyzet20060317

2013-03-31T19:03:06Z

2001:738:2001:2078:0:208:37:0: Átirányítás ide: Adatbázisok szerver oldali programozása Előadás, 2006.03.17.

#REDIRECT [[Adatbázisok_szerver_oldali_programozása_Előadás,_2006.03.17.]]

Adatbázisok szerver oldali programozása Előadás, 2006.03.17.

2013-03-31T19:03:04Z

2001:738:2001:2078:0:208:37:0: Új oldal, tartalma: „{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060317}}  <style> code.pre { white-space: pre; display: block; m…”

{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060317}}



<style>
code.pre { white-space: pre; display: block; margin-top: 2px; margin-bottom: 2px;}
li { line-height: 18px; }
</style>

<br/> [[AdatbServerProgJegyzet20060314|<<]] Előadás, 2006.03.17. [[AdatbServerProgJegyzet20060321|>>]]

__TOC__

==Kivételek kezelése==

===Kivétel típusok===

* Rendszer kivételek
** Névvel ellátott kivételek (név, sorszám, üzenet)
** Név nélküli kivételek (sorszám, üzenet)
* Felhasználói kivételek
* Alkalmazás hibák

====Névvel ellátott kivételek====

* Kivétel lekezelése: <code class="pre">DECLARE ...
<br/>BEGIN ...
<br/>EXCEPTION
<br/> WHEN kivételnév [OR kivételnév2 [...]] THEN utasítások;
<br/> WHEN OTHERS THEN utasítások;
<br/>END;</code>
* Az =OTHERS= blokknak a =EXCEPTION= blokk végére kell kerülnie.
* Gyakori, névvel ellátott kivételek:
** =NO_DATA_FOUND=
** =TOO_MANY_ROWS=
** =DUP_VAL_ON_INDEX=: olyan sort akarunk beszúrni, amivel megszegjük a kulcs vagy a =UNIQUE= oszlop egyediségére vonatkozó megkötést
** =VALUE_ERROR=: pl. =NULL= értéket akarunk beszúrni =NOT NULL= oszlopba
** =ZERO_DIVIDE=
** =CURSOR_ALREADY_OPEN=: kétszer nyitunk meg egy kurzort
** =INVALID_CURSOR=
** =CASE_NOT_FOUND=: a =CASE= utasítás szelektora egyik ágra sem illeszkedik
* A nevek a =STANDARD= csomagban vannak összegyűjtve. Nem kell deklarálni őket.
* Kivétel generálása: =RAISE NO_DATA_FOUND;=

====Név nélküli rendszer kivételek====
* A =WHEN OTHERS= blokkban egyszerre elkaphatók
* Elnevezhetők a =PRAGMA= utasítás segítségével: <code class="pre">DECLARE kivételnév EXCEPTION;
<br/>PRAGMA EXCEPTION_INIT(kivételnév, szám);</code>
* Az előbbi módon a rendszer kivételnevek is felüldefiniálhatók. Az eredeti kivételre a =STANDARD= előtaggal hivatkozhatunk. Pl. =WHEN STANDARD.NO_DATA_FOUND THEN ...=
* Kivétel generálása: =RAISE kivételnév;=

====Felhasználói kivételek====
<ul>
<li>Szintaktika:
<code class="pre">DECLARE
kivételnév EXCEPTION;
BEGIN
...
RAISE kivételnév;
...
EXCEPTION
WHEN kivételnév THEN ...
END;</code>
</ul>

====Alkalmazás hibák====
* Hiba kiváltása: =RAISE_APPLICATION_ERROR(hibakód, üzenet[, {FALSE|TRUE}])=
** hibakód: -20999 és -20000 közötti szám
** üzenet: =VARCHAR(2048)=
** A 3. paraméter =TRUE= értéke esetén a hiba a stackbe kerül, különben felülírja az előző hibát. A paraméter alapértelmezett értéke =FALSE=

===Kivételek terjedése===
* A =DECLARE= és =BEGIN= között keletkezett hibát csak kívül lehet elkapni.
* Ugyanez igaz az =EXCEPTION= és az =END= között keletkezett kivételre is.
* A =BEGIN= és az =EXCEPTION= közötti kivétel az =EXCEPTION= blokkban lekezelhető. Ha itt nem történik meg, akkor a külső blokk =EXCEPTION= részébe terjed tovább.
* A belső blokkban lokálisan deklarált hibát a külső blokknak csak a =WHEN OTHERS= részében lehet elkapni, mert kívül nem látható a neve.

===Hibakód és hibaüzenet===

<ul>
<li> Az =SQLCODE= illetve az =SQLERRM= függvényekkel kérdezhető le.
<li> Ezek a függvények nek használhatók SQL utasítások belsejében, az értéküket először változóba kell kiolvasni.
<li> Példa a hibák naplózására:
<code class="pre">DECLARE
hibakod INT;
uzenet VARCHAR2(512);
BEGIN
...
EXCEPTION
WHEN OTHERS THEN
szam := SQLCODE;
uzenet := SQLERRM;
INSERT INTO naplo VALUES (szam, uzenet, sysdate);
END;</code>
</ul>

====Hibakódok====
* Belső kivétel: <0
* =NO_DATA_FOUND=: 100
* Felhasználói kivétel: 1
* Nincs hiba: 0

====Hibaüzenetek====
* A rendszerüzenetek =VARCHAR2(512)= típusúak
* Felhasználói kivétel esetén a ='User Defined Exception'= üzenetet kapjuk
* Siker esetén ='ORA-0000'= az üzenet

==Alprogramok==

===Alprogramok típusai, terminológia===

* Lehetnek eljárások vagy függvények
* Lehetnek tároltak vagy lokálisak
* Részei: deklaráció, kód, kivételkezelés
* _Formális paraméter_: a paraméter neve
* _Aktuális paraméter_: a paraméter értéke

===Eljárások és függvények===

<ul>
<li> Eljárás deklarációja:
<code class="pre">[CREATE [OR REPLACE]] PROCEDURE eljárásnév [(paraméterek)]
[AUTHID {DEFINER|CURRENT_USER}]
{IS|AS}
lokális deklaráció
BEGIN
...
EXCEPTION
...
END [eljárásnév];</code>
<li> Függvény deklarációja:
<code class="pre">[CREATE [OR REPLACE]] FUNCTION függvénynév [(paraméterek)]
RETURN típus
[AUTHID {DEFINER|CURRENT_USER}]
{IS|AS}
lokális deklaráció
BEGIN
...
RETURN érték;
EXCEPTION
...
END [függvénynév];</code>
<li>Paraméter deklaráció:
<br/> =név [IN|OUT [NOCOPY]||IN OUT [NOCOPY]] típus [{:=DEFAULT} kifejezés]=
</ul>

====Paraméter módok====

* A típus nem tartalmazhat megkötést, mint pl. =VARCHAR2(50)=
* =IN=: alapértelmezett mód, eljáráson belül konstansként viselkedik, lehet default értéke
* =OUT=: változóként viselkedik, változóval kell meghívni. Hívás után <pre>NULL</pre>-ra inicializálódik, ezért nem lehet =NOT NULL= megkötést tenni a típusra, különben =VALUE_ERROR= hibát kapunk.
* =OUT NOCOPY=: referenciaként adódik át a változó. Vigyáznunk kell, ha kivétel történik, mert inkonzisztens állapotban maradhat a változó értéke
* =IN OUT=: értékadás bal és jobb oldalán is állhat

====Eljárás hívása====

* =BEGIN= és =END= között =eljárásnév(paraméterek);=
* Command windowból =EXEC eljárásnév(paraméterek);=
* Paraméterek átadása
** A paraméter lehet konstans, változó vagy kifejezés
** Paraméterátadás pozíció alapján:
<br/> =eljárásnév(érték, ...);=
** Paraméterátadás név alapján:
<br/> =eljárásnév(formális paraméter=>aktuális paraméter, ...);=
** Kevert paraméterátadás: az első néhány paramétert pozíció alapján, a többit név alapján adjuk át.
* Függvényhívás történhet
** Kifejezés részeként
** =SELECT= belsejében, pl. =SELECT f(1) FROM DUAL;=. Ilyenkor a függvénynek SQL típussal kell visszatérnie, a boolean például tiltott.

===Példák===

====Tárolt eljárás hibakezeléssel====

<code class="pre">CREATE OR REPLACE PROCEDURE anyag_beszur
(azonosito INT, nev VARCHAR2, egyszegnev VARCHAR2)
IS
egysegid INT;
BEGIN
SELECT egyseg_id INTO egysegid FROM egyseg
WHERE UPPER(egyseg_nev) = UPPER(egysegnev);
INSERT INTO anyag(anyag_id, anyag_nev, anyag_egyseg)
VALUES(azonosito, ne, egysegid);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20101, 'Nincs ilyen egység');
WHEN DUP_VAL_ON_INDEX THEN
BEGIN
SELECT anya_id INTO aid FROM anyag
WHERE UPPER(anyag_nev) = UPPER(nev);
RAISE_APPLICATION_ERROR(-20102, 'Az anyag létezik ' ||| aid || ' azonosítóval');
EXCEPTION
WHEN NO_DATA_FOUND THEN
SELECT MAX(anyag_id)+1 INTO aid FROM anyag;
RAISE_APPLICATION_ERROR(-20103, 'Dupla azonosító, ajánlott: ' | aid');
END;
END anyag_beszur;</code>

====Tárolt eljárás név nélküli hiba kezelésével====

<code class="pre">CREATE OR REPLACE PROCEDURE recept_beszur
(aid INT, fid INT, mennyiseg FLOAT) -- aid=anyag id, fid=félkész id
IS
referencia EXCEPTION;
PRAGMA EXCEPTION_INIT(referencia, -2291);
BEGIN
INSERT INTO felkesz_anyag VALUES(aid, fid, mennyiseg);
EXCEPTION
WHEN referencia THEN
RAISE ... -- itt még lehetne ellenőrizni, hogy melyik oszlop okozza
WHEN DUP_VAL_ON_INDEX THEN
RAISE ...
END;</code>

====Tárolt függvény hibakezeléssel====

<code class="pre">CREATE OR REPLACE FUNCTION pilota_pontszam(pid INT, fid INT)
RETURN INT
IS
result INT;
sorszam INT;
BEGIN
SELECT helyezes [[INTo]] sorszam FROM futam_pilota
WHERE futam_id=fid AND pilota_id=pid;
CASE sorszam
WHEN 1 THEN result:=10;
WHEN 2 THEN result:=8;
WHEN 3 THEN result:=6;
...
WHEN 8 THEN result:=1;
ELSE result:=0;
END CASE;
RETURN result;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE ...
END;</code>

====Tárolt eljárás hívása====

<code class="pre">SELECT nev, helyezes, pilota_pontszam(pilotaid, futamid) pont
FROM futam_pilota NATURAL JOIN pilota
WHERE futamid=51001
ORDER BY helyezes, pont DESC;</code>

A =WHERE= után nem lehet =pont= néven hivakozni a függvény eredményére, ki kell írni az egész függvényhívást.

-- [[PallosPeter|Peti]] - 2006.03.17.

[[Category:Valaszthato]]

AdatbServerProgJegyzet20060310

2013-03-31T19:03:03Z

2001:738:2001:2078:0:208:37:0: Átirányítás ide: Adatbázisok szerver oldali programozása Előadás, 2006.03.10.

#REDIRECT [[Adatbázisok_szerver_oldali_programozása_Előadás,_2006.03.10.]]

Adatbázisok szerver oldali programozása Előadás, 2006.03.10.

2013-03-31T19:03:02Z

2001:738:2001:2078:0:208:37:0: Új oldal, tartalma: „{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060310}}  <br/> << Előad…”

{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060310}}



<br/> [[AdatbServerProgJegyzet20060307|<<]] Előadás, 2006.03.10. [[AdatbServerProgJegyzet20060314|>>]]

__TOC__

==Kurzorok==

A következő kód hibás, mert ha a =SELECT= nulla sorral tér vissza, exceptiont kapunk:
<pre>DECLARE
myid INT:=3;
nev VARCHAR(50);
BEGIN
SELECT anyag_nev INTO nev FROM anyag WHERE anyag_id=myid;
DBMS_OUTPUT.PUT_LINE(nev);
END;</pre>

Egy tábla sorain kurzor segítségével lehet végiglépkedni.

* Explicit kurzor: a programozó hozza létre
* Implicit kurzor: az Oracle hozza létre azokra a lekérdezésekre, amikre nincs explicit kurzor.

===Kurzorok szintaktikája===

* Kurzor deklarálása: =CURSOR kurzor_név [(param1, ...)] [RETURN visszatérési_típus] IS lekérdezés;=
<br/> A paraméterek láthatósága lokális.
* Kurzor megnyitása: =OPEN kurzor_név [(érték1, ...)];=
* Léptetés a következő sorra, és beolvasás: =FETCH kurzor_név INTO változó;=
<br/> A =FETCH= csak az =OPEN= és a =CLOSE= között használható.
* Összes sor kiolvasása egyszerre: =FETCH kurzor_név BULK COLLECT INTO tömb;=
* Kurzor bezárása: =CLOSE kurzornév;=

===Attribútumok===

* =kurzor_név%ISOPEN=: true, ha a kurzor meg van nyitva.
* =kurzor_név%NOTFOUND=: false, ha az utolsó =FETCH= sorral tért vissza. Első =FETCH= előtt =NULL=.
* =kurzor_név%FOUND=: true, ha az utolsó =FETCH= sorral tért vissza. Első =FETCH= előtt =NULL=.
* =kurzor_név%ROWCOUNT=: a <pre>FETCH</pre>-elt sorok száma. Első =FETCH= előtt 0.
* =kurzor_név%ROWTYPE=: a kurzorral fesorolható sorok típusa. Csak akkor kérdezhető le, ha a =RETURN= után megadtunk visszatérési típust.

Az implicit kurzorok az utolsó SQL utasításról adnak információt. Az attribútmait az =SQL%attribútum_név= kifejezéssel lehet lekérdezni.

* =SQL%ISOPEN=: mindig false.
* =SQL%NOTFOUND=: true, ha az utolsó =SELECT=, =INSERT=, =UPDATE= vagy =INSERT= 0 sort érintett.
* =SQL%FOUND=: false, ha az utolsó =SELECT=, =INSERT=, =UPDATE= vagy =INSERT= 0 sort érintett.
* =SQL%ROWCOUNT=: az utolsó SQL utasítás által érintett sorok száma.

==Példák==

===Felsorolás LOOP ciklussal===

<pre>DECLARE
CURSOR c IS SELECT anyag_id, anyag_nev FROM anyag;
id INT;
nev anyag.anyag_nev%TYPE;
BEGIN
OPEN c;
LOOP
FETCH c INTO id, nev;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(id || ' ' || nev);
END LOOP;
END;</pre>

===Felsorolás FOR ciklussal===

A FOR automatikusan megnyitja illetve lezárja a kurzort. A ciklusváltozót nem kell deklarálni, a típusa =kurzor%ROWTYPE= lesz.

Szintaktika: =FOR rekord IN kurzor[(param1, ...)] LOOP= ... =END LOOP=;

<pre>DECLARE
CURSOR c IS SELECT anyag_id id, anyag_nev nev FROM anyag;
BEGIN
FOR crec IN c LOOP
DBMS_OUTPUT.PUT_LINE(crec.id || ' ' || crec.nev);
END LOOP;
END;</pre>

===BULK COLLECT===

<pre>DECLARE
CURSOR c IS SELECT anyag_nev, anyag_id FROM anyag;
TYPE nev_type IS TABLE OF anyag.anyag_nev%TYPE;
TYPE id_type IS TABLE OF anyag.anyag_id%TYPE;
nev_tomb nev_type;
id_tomb id_type;
BEGIN
OPEN c;
FETCH c BULK COLLECT INTO nev_tomb, id_tomb;
CLOSE c;
END;</pre>

===Implicit kurzor FOR ciklusban===

<pre>BEGIN
FOR crec IN (SELECT anyag_id, anyag_nev FROM anyag) LOOP
DBMS_OUTPUT.PUT_LINE(crec.anyag_id || ' ' || crec.anyag_nev);
END LOOP;
END;</pre>

===Rekord típusú kurzor===

<pre>DECLARE
TYPE anyag_rec IS RECORD (id INT, nev VARCHAR2(50));
CURSOR c RETURN anyag_rec IS
SELECT anyag_id, anyag_nev FROM anyag;
rec c%ROWTYPE;
BEGIN
OPEN c;
LOOP
FETCH c INTO rec;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(rec.anyag_id || ' ' || rec.anyag_nev);
END LOOP;
END;</pre>

==Kurzor változók==

Az eddig használt kurzorok konstansok voltak.
* A kurzor változóhoz dinamikusan lehet hozzákötni a lekérdezést. Megnyitáskor az előző értékeket elfelejti. Az újbóli megnyitás előtt nem szükséges lezárni.
* A kurzor változót is meg kell nyitni és be kell zárni.
* A =BULK COLLECT= használható rá, de a FOR ciklus nem.
* A =%ROWTYPE= attribútum csak akkor használható, ha a változónak deklaráltuk a visszatérési típusát.

===Szintaktika===

* Deklaráció: =TYPE típusnév IS REF CURSOR [RETURN visszatérési_típus];=
* Megnyitás: =OPEN {kurzor_változó | :host_kurzor_változó} FOR {lekérdezés dinamikus string} [USING bind_argument1, ...];=
* Olvasás: =FETCH {kurzor_változó | :host_kurzor_változó} [BULK COLLECT] INTO változó1, ...=

==Példák==

===Egyszerű felsorolás===

<pre>DECLARE
TYPE c_type IS REF CURSOR;
c c_type;
nev VARCHAR2(50);
BEGIN
OPEN c FOR SELECT anyag_nev FROM anyag;
LOOP
FETCH c INTO nev;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(nev);
END LOOP;
OPEN c FOR SELECT stud_lname FROM student
LOOP
FETCH c INTO nev;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(nev);
END LOOP;
CLOSE c;
END;</pre>

===Összetett lekérdezés===

<pre>DECLARE
CURSOR c IS
SELECT felkesz_nev, CURSOR(
SELECT anyag_nev, fa_mennyiseg, egyseg_nev
FROM anyag, felkesz_anyag, egyseg
WHERE anyag_id=fa_anyagid AND fa_felkesz_id=f.felkesz_id AND egyseg_id=anyag_egyseg)
FROM felkesz f ORDER BY felkesz_nev;
TYPE refcursor IS REF CURSOR;
nev VARCHAR2(100);
TYPE anyagdatatype IS RECORD
(nev VARCHAR2(100), mennyiseg FLOAT, egyseg VARCHAR2(20));
anyagadat anyagdatatype;
mycur refcursor;
BEGIN
OPEN c;
LOOP
FETCH c INTO nev, mycur;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('A ' || nev || ' összetevői:');
LOOP
FETCH mycur INTO anyagadat;
EXIT WHEN mycur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(
' ' || anyagadat.nev || ' ' || anyagadat.mennyiseg || ' ' || anyagadat.egyseg);
END LOOP;
END LOOP;
CLOSE c;
END;</pre>

-- [[PallosPeter|Peti]] - 2006.03.10.

[[Category:Valaszthato]]

AdatbServerProgJegyzet20060217

2013-03-31T19:03:01Z

2001:738:2001:2078:0:208:37:0: Átirányítás ide: Adatbázisok szerver oldali programozása Előadás, 2006.02.17.

#REDIRECT [[Adatbázisok_szerver_oldali_programozása_Előadás,_2006.02.17.]]

Adatbázisok szerver oldali programozása Előadás, 2006.02.17.

2013-03-31T19:03:00Z

2001:738:2001:2078:0:208:37:0: Új oldal, tartalma: „{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060217}}  <style> ol li { line-height: 18px; } </style> <br/> [[A…”

{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060217}}



<style> ol li { line-height: 18px; } </style>

<br/> [[AdatbServerProg|<<]] Előadás, 2006.02.17. [[AdatbServerProgJegyzet20060221|>>]]

__TOC__

==Kliens-szerver architektúra==
* Kliens: üzleti logika és megjelenítés
* Szerver: adatbázis

* Előny: kevés kommunikáció
* Hátrány: ha az üzleti logika megváltozik, minden klienst cserélni kell

==n-rétegű architektúra==
* Presentation Layer
* Business Logic Layer
* Data Access Layer
* Database -> mi csak ezzel foglalkozunk

===Előnyök===
* rugalmasabban módosítható
* DAL miatt a DB cserélhető
* skálázható
===Hátrányok===
* bonyolultabb
* több kommunikáció
* interfészeket előre definiálni kell

==Szerver oldali programozás==
* Tárolt eljárások és függvények
* Triggerek = eseményre automatikusan lefutó tárolt eljárások. Esemény alatt mást értünk Oracle és MS SQL alatt.

===Tárolt eljárások===

====Előnyök====
* Teljesítmény
** Szerveren lefordítva tárolódnak
** Több szál ugyanazt a memóriát használja
** Kisebb hálózati forgalom
** BLL-ben kevesebb funkció, jobban skálázható (rossz is lehet, mert a szervert terheljük)
* Kevésbé redundáns kódolás
* Biztonságos jogosultságkezelés a szerveren: felhasználó jogosultsága csak a tárolt eljárások futtatására korlátozható
* karbantarthatóság: ha csak a szervert módosítjuk, a klienst nem kell újrafordítani

====Hátrányok====
* Szervert terheljük
* Nem szabványos

==SQL szintaktika==

===Alap típusok===
* =int=
* =varchar(20)=: változó hosszú string
* =nvarchar(20)=: változó hosszú unicode string
* =char(20)=: fix hosszú string
* =nchar(20)=: fix hosszú unicode string
* =date= vagy =datetime=
* =float=
* =bit= (csak MS SQL-ben)

===Tábla létrehozása===

<pre>CREATE TABLE táblanév (
{ oszlopdef | tábla szintű megkötés }*
)
oszlopdef: oszlopnév típus [oszlopszintű megkötés]
oszlopszintű megkötés:
NULL | NOT NULL // default: NULL
[CONSTRAINT megkötésnév]
PRIMARY KEY
UNIQUE
REFERENCES tábla(oszlop)
CHECK(feltétel) // a feltételben csak a saját oszlopnév szerepelhet
táblaszintű megkötés:
CONSTRAINT név
PRIMARY KEY (oszlopnév1, oszlopnév2, ...)
UNIQUE (oszlopnév1, oszlopnév2, ...)
FOREIGN KEY (oszlopnév1, ... oszlopnévn) REFERENCES táblanév(oszlopnév1, ... oszlopnévn)
CHECK (több oszlopot tartalmazó logikai kifejezés</pre>

===Tábla módosítása===
<pre>ALTER TABLE
ADD { oszlopdef | tábla szintű megkötés }
DROP, stb.</pre>

===Lekérdezés===
<pre>SELECT [ALL|DISTINCT] oszloplista // default: all
FROM táblalista
[WHERE keresési feltétel]
[GROUP BY csoportosítási kifejezés
[HAVING csoport feltétel]]
[ORDER BY oszlop [ASC|DESC]] // default: ASC</pre>

====Táblák összekapcsolása====
# Descartes-szorzat
<br/> =SELECT * FROM t1,t2=
# =WHERE= mögött id-k alapján
<br/> =SELECT * FROM t1,t2 WHERE t1.id=t2.t1_id=
# =JOIN= kulcsszó
<br/> =SELECT * FROM t1 JOIN t2 ON t1.id=t2.t1_id=
<br/> Állítólag gyorsabb, de a jobb olvashatóság miatt a 2. változatot szokták használni.
# =NATURAL JOIN=
<br/> =SELECT * FROM t1 NATURAL JOIN t2=
<br/> A két tábla közötti referenciánál illeszt.
# Beágyazott =SELECT= a tábla helyén
<br/> =SELECT * FROM t1, (SELECT * FROM t2) a WHERE a.id=t1.a_id=
# Beágyazott =SELECT= a =WHERE= mögött
<br/> =SELECT * from t1 WHERE t1_id IN (SELECT id FROM t2)=

====GROUP BY====

Oszlopfüggvények: =MIN=, =MAX=, =SUM=, =COUNT=, =AVG=.

* Legjobban megfizetett alkalmazott:
<br/> =SELECT MAX(fizetés) FROM alkalmazott=
* Minden tanszékről a legjobban megfizetett alkalmazott:
<br/> =SELECT tanszék, MAX(fizetés) FROM alkalmazott GROUP BY tanszék=
* Azok a tanszékek, ahol a legjobb fizetés 200000 fölött van + a fizetés értéke:
<br/> =SELECT tanszék, MAX(fizetés) maxfizu FROM alkalmazott
<br/> GROUP BY tanszék HAVING MAX(fizetés)>200000=
<br/> (A =HAVING= után nem használható az oszlopok új neve (itt: maxfizu))
* Ha a =SELECT= oszlopfüggvényt tartalmaz, a =GROUP BY= után szerepelnie kell minden olyan oszlopnak, ami a <pre>SELECT</pre>-ben oszlopfüggvény kívül szerepelt.

====Halmazműveletek====

* =UNION=
* =UNION ALL=
* =INTERSECT= (Az SQL Server 2000 nem támogatja, csak a 2005)
* =MINUS= (Az SQL Server 2000 nem támogatja, csak a 2005)
-- [[PallosPeter|Peti]] - 2006.02.21.

===Beszúrás===

* =INSERT INTO tábla (oszlop1, oszlop2, ...) VALUES (érték1, érték2, ...)=
* =INSERT INTO tábla (oszlop1, oszlop2, ...) SELECT (oszlop1, oszlop2, ...) ...=

====Azonosító generálás====

* SQL Server
** Létrehozás: =CREATE TABLE a (id INT IDENTITY PRIMARY KEY, szoveg VARCHAR(20))=
** Beszúrás: =INSERT INTO a VALUES('abc')=
** Beszúrás nem automatikusan generált id-vel:
<br/> =SET IDENTITY_INSERT a ON=
<br/> =INSERT INTO a(id,szoveg) VALUES(3, 'cde')=
<br/> =SET IDENTITY_INSERT a OFF=
* Oracle
** Szekvencia létrehozása (különálló objektum, nem kapcsolódik táblához):
<br/> =CREATE SEQUENCE a_seq START WITH 1 INCREMENT BY 1=
** Beszúrás: =INSERT INTO a VALUES(a_seq.NEXTVAL, 'abc')=
** Beszúrás a szekvencia aktuális értékével:
<br/> =INSERT INTO a VALUES(a_seq.CURVAL, 'abc')=

===Módosítás===

* =UPDATE táblanév SET oszlop1=érték1, oszlop2=érték2, ... [WHERE feltétel]=
* Több táblás módosítás:
** SQL Server: =FROM= kulcsszóval
** Oracle: al-<pre>SELECT</pre> segítségével

===Törlés===

* =DELETE FROM táblanév [WHERE feltétel]=
<br/> Ha nincs feltétel megadva, az egész táblát törli.
* =TRUNCATE táblanév= == =DELETE FROM táblanév=. Az előbbi gyorsabb.

===Nézet létrehozása===

* =CREATE VIEW nézetnév(oszlop1, oszlop2, ...) AS lekérdezés=
* Egy táblás nézetbe be lehet szúrni sorokat, több tábla esetén csak triggerekkel lehet trükközni.

-- [[PallosPeter|Peti]] - 2006.02.21.

[[Category:Valaszthato]]

AdatbServerProgJegyzet20060303

2013-03-31T19:02:59Z

2001:738:2001:2078:0:208:37:0: Átirányítás ide: Adatbázisok szerver oldali programozása Előadás, 2006.03.03.

#REDIRECT [[Adatbázisok_szerver_oldali_programozása_Előadás,_2006.03.03.]]

Adatbázisok szerver oldali programozása Előadás, 2006.03.03.

2013-03-31T19:02:57Z

2001:738:2001:2078:0:208:37:0: Új oldal, tartalma: „{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060303}}  <style> code.pre { white-space: pre; display:block; me…”

{{GlobalTemplate|Valaszthato|AdatbServerProgJegyzet20060303}}



<style>
code.pre { white-space: pre; display:block; mergin-top: 2px; margin-bottom: 2px;}
li { line-height: 18px; }
</style>

<br/> [[AdatbServerProgJegyzet20060224|<<]] Előadás, 2006.03.03. [[AdatbServerProgJegyzet20060307|>>]]

__TOC__

==Adattípusok==

===Típusok osztályozása===
* változó / konstans / oszlop / összetett
* skalár / összetett
* beépitett / programozó által definiált

===Skalár típusok===
* =NUMBER(p,s)=: fixpontos szám. p=hossz, s=tizedesjegyek száma. Ha s=0, egészet tárol.
* =CHAR(hossz [CHAR|BYTE])=: fix hosszú karakterláncot tárol. A hossz alapérteke 1, legfeljebb 32767 lehet. Táblában maximum 2000 hosszú karakterlánc tárolható. Ha rövidebb karakterláncot szúrunk be, a maradék helyeket automatikusan feltölti szóközökkel, ha hosszabbat, =VALUE_ERROR= exceptiont kapunk. A =CHAR= módositó segítségével unicode karakterek tárolhatók, BYTE esetén 1 karakter 1 byte hosszú.
* =VARCHAR2(hossz [CHAR|BYTE])=: változo hosszú karakterlánc. Ugyanazok a korlátozások érvényesek rá, mint a <pre>CHAR</pre>-ra. Ha a hossz paraméter legfeljebb 2000, akkor a memóriában a maximális hosszt foglalja le, különben csak a string aktuális hosszát.<code class="pre">DECLARE
<br/> a VARCHAR2(1500);
<br/> b VARCHAR(3000);
<br/>BEGIN
<br/> a:='abcde'; -- 1500 byte
<br/> b:='abcde'; -- 5 byte
<br/>END;</code>
* =NCHAR2=, =NVARCHAR2=: ugyanazt tudja, mint az előző két típus, csak unicode karaktereket tárol.
* =LONG=: >2000 hosszú karaktersorozatot is tud tárolni
* =DATE=: dátumot és időt tárol
* =BOOLEAN=: értékkészlete: ={TRUE, FALSE, NULL}=. Nem lehet oszlop típusa.
* =BINARY INTEGER=: -2^31+1 és 2^31-1 közötti egész
* =PLS_INTEGER=: ugyanez, csak hardver aritmetikát használ
* =LOB=: Large Object, legfeljebb 4 GB méretű. Típusai:
** =BFILE=: bináris file, az operációs rendszer filerendszerében tárolódik. Az adatbázis csak hivatkozást tartalmaz. Nem backupolódik automatikusan az adatbázissal.
** =BLOB=: adatbázison belül tárolódik, backupolódik.
** =CLOB=: mint a =BLOB=, csak karaktereket tartalmaz.

===Altípusok===

Altípus = alaptípus + megszorítás

* =BINARY_INTEGER= altípusok
** =NATURAL= ∈ {0..2^31-1, NULL}
** =NATURALN= ∈ {0..2^31-1}
** =POSITIVE= ∈ {1..2^31-1, NULL}
** =POSITIVEN= ∈ {1..2^31-1}
** =SIGNTYPE= ∈ {-1, 0, 1}
* =NUMBER= altípusok
** =DEC=, =DECIMAL=, =NUMERIC=: fixpontos
** =FLOAT=, =DOUBLE PRECISION=, =REAL=: lebegőpontos
** =INTEGER=, =INT=, =SMALLINT=: egész
* =VARCHAR2= altípusok
** =STRING=, =VARCHAR= (csak alias)

===Programozó által definiált típusok===

Előre definiált típusok + megkötések.

=SUBTYPE típusnév IS alaptípus [(megkötés)] [NOT NULL]=
<br/>
Csak a hosszra adható megkötés, értékkészletre nem.

===Összetett típusok===

* Rekord
** Típus deklaráció: =TYPE típusnév IS RECORD (mezőnév típus, mezőnév típus, ...);=
<br/> =típus=: <pre>típusnév [NOT NULL] [{:= | DEFAULT} érték];</pre>
** Változó deklaráció: =rekord_változó típusnév=
** Hivatkozás a rekord mezőjére: =rekord_változó.mezőnév=
** Értékadás: <pre>:=</pre>, ha a két rekord azonos típusú,
<br/> =SELECT ... INTO rekord= vagy =FETCH ... INTO rekord=
* Rekord az adatbázis sémából
** =rekord_változó1 tábla%ROWTYPE;=
<br/> =rekord_változó2 tábla%ROWTYPE;=
<br/> A két változó nem lesz azonos típusú, még akkor sem, ha ugyanannak a táblának a sorát reprezentálják.
* Tömb:
** Az elemek azonos típusúak
** Az elemek sorban követik egymást, a tömb nem tartalmazhat lyukakat
** Az elemek indexelése 1-től kezdődik
** A minimális méret 1, az =EXTEND= metódussal növelhető, de nem haladhatja meg a deklarációkor megadott maximumot. Az új elemek a tömb végére szúrhatók be.
** Oszlop típusa is lehet
** Inicializálás előtt NULL értékű
** Deklaráció: =TYPE tömbtípus IS VARRAY(5) OF INT;=
** Konstruktor: <pre>tömb:=tömbtípus(elem, elem, ...);</pre>
** Új elem beszúrása: =tömb.EXTEND=; majd <pre>tömb(tömb.LAST):=érték;</pre>
** Címzés: =tömbnév(index)=
* Nested table — különbségek a sima tömbhöz képest
** Az elemszám nem korlátos
** Ritka, középről is törölhetők elemek
** Deklaráció: =TYPE típusnév IS TABLE OF elem_típus [NOT NULL];=
* Index-by table = hash tábla
** Nem lehet táblában tárolni
** Nem kell inicializálni, kezdetben üres
** Szerkezeténél fogva ritka
** Az index negatív is lehet, az Oracle 10g-től kezdve pedig =VARCHAR2= is.
** Deklaráció: =TYPE típusnév IS TABLE OF elem_típus INDEX BY index_típus;=

Példa tömb használatára:
<code class="pre">DECLARE
TYPE tombtipus IS VARRAY(5) OF INT;
tomb tombtipus;
BEGIN
tomb:=tombtipus(1,4,9);
tomb.EXTEND;
tomb(4):=16;
FOR i IN 1..tomb.LAST LOOP
DBMS_OUTPUT.PUT_LINE(tomb(i));
END LOOP;
END;</code>

====Metódusok====

=változónév.metódusnév[(paraméterek)]= formában hívhatók

* =EXISTS(n)=: n. indexen van-e elem. Ha túlcímzünk a tömbön, <pre>FALSE</pre>-szal tér vissza.
* =COUNT=: a kollekció elemszáma. Nested table-nél értéke különbözhet a <pre>LAST</pre>-tól.
* =LIMIT=: a kollekció maximális elemszáma. Nested table-nél NULL.
* =FIRST=, =LAST=: első illetve utolsó elem indexe. Üres kollekció esetén NULL.
* =PRIOR(n)=, =NEXT(n)=: n-et megelőző illetve rákövetkező kitöltött elem indexe. =VARRAY= esetén n-1 illetve n+1, index-by table-re nincs értelmezve. Ha nincs előző vagy következő elem, <pre>NULL</pre>-lal tér vissza.
* =EXTEND=, =ENTEND(n)=, =EXTEND(n,i)=: bővíti a <pre>VARRAY</pre>-t vagy a nested table-t 1 db <pre>NULL</pre>-lal, n db <pre>NULL</pre>-lal, illetve n példánnyal az i indexű elemből. Index-by table-re nem értelmezett.
* =TRIM=, =TRIM(n)=: törli a =VARRAY= vagy a nested table utolsó 1 illetve n elemét. Index-by table-re nem értelmezett.
* =DELETE=, =DELETE(n)=, =DELETE(n,m)=: törli az összes, az n. illetve a [n...m] intervallum összes elemét az index-by vagy nested table-ből.

-- [[PallosPeter|Peti]] - 2006.03.06.

[[Category:Valaszthato]]

Valaszthato AdatbServerProgJegyzet20060321

2013-03-31T18:51:56Z

Valaszthato AdatbServerProgJegyzet20060317

2013-03-31T18:50:54Z

Adatbázisok szerver oldali programozása

2013-03-31T18:50:29Z

2001:738:2001:2078:0:208:37:0:

{{GlobalTemplate|Valaszthato|AdatbServerProg}}

Tantárgy adatlap:
* http://www.vdk.bme.hu/targykov/valaszthato/viau9159.htm
Tanszéki oldal:
* https://avalon.aut.bme.hu/~reni/aszp/ (jelszavas)
* https://avalon.aut.bme.hu/~ivancsy/aszp.html
* http://www.aut.bme.hu/Portal/Targy.aspx?courseId=06d4f149-7397-4e93-903b-0d52483c49ed
Oktatók:
* Iváncsy Renáta (IB.153, 1668, reni@aut.bme.hu)
* Dr. Iváncsy Szabolcs (csak a Neptun szerint tanít minket, a félév során kétszer láttam)

==Órai jegyzet==

===Bevezetés===
* [[AdatbServerProgJegyzet20060217|Előadás, 2006.02.17.]] — 2 és n-rétegű architektúra, szerver oldali programozás, SQL szintaktika

===Oracle===
* [[AdatbServerProgJegyzet20060221|Labor, 2006.02.21.]] — PL/SQL Developer alapok, Forma1 adatbázis létrehozása és feltöltése
* [[AdatbServerProgJegyzet20060224|Előadás, 2006.02.24.]] — Oracle és Sql Server összehasonlítása, Oracle vezérlési szerkezetek
* Labor, 2006.03.07. — Az ipari nyílt nap miatt elmaradt.
* [[AdatbServerProgJegyzet20060303|Előadás, 2006.03.03.]] — adattípusok
* [[AdatbServerProgJegyzet20060307|Labor, 2006.03.07.]] — <pre>SELECT</pre>-ek írása a cukrász adatbázishoz
* [[AdatbServerProgJegyzet20060310|Előadás, 2006.03.10.]] — kurzorok
* [[AdatbServerProgJegyzet20060314|Labor, 2006.03.14.]] — elágazások, ciklusok, kurzorok használata
* [[AdatbServerProgJegyzet20060317|Előadás, 2006.03.17.]] — hibakezelés, tárolt eljárások és függvények
* [[AdatbServerProgJegyzet20060321|Labor, 2006.03.21.]] — tárolt eljárások és hibakezelés (Formula 1 DB)
* [[AdatbServerProgJegyzet20060324|Előadás, 2006.03.24.]] — triggerek
* [[AdatbServerProgJegyzet20060328|Labor, 2006.03.28.]] — tárolt alprogramok és triggerek
* [[AdatbServerProgJegyzet20060331|Előadás, 2006.03.31.]] — csomagok
* [[AdatbServerProgJegyzet20060404|Labor, 2006.04.04.]] — Forma1 csomag írása
* [[AdatbServerProgJegyzet20060407|Előadás, 2006.04.07.]] — tranzakciók
* [[AdatbServerProgJegyzet20060411|Labor, 2006.04.11.]] — tranzakciók
* [[AdatbServerProgJegyzet20060414|Előadás, 2006.04.14.]] — dinamikus SQL
* [[AdatbServerProgJegyzet20060418|Labor, 2006.04.18.]] — dinamikus SQL, infó kinyerése rendszer táblákból

===Microsoft SQL Server ===
* [[AdatbServerProgJegyzet20060421|Előadás, 2006.04.21.]] — MS SQL bevezetés, =IDENTITY=, típusok, operátorok, vezérlési szerkezetek
* [[AdatbServerProgJegyzet20060425|Labor, 2006.04.25.]] — SQL Management Studio, változók, dátumkezelés, =UPDATE OUTPUT=
* [[AdatbServerProgJegyzet20060428|Előadás, 2006.04.28.]] — SQL-92 és T-SQL kurzor, kurzor ciklus, tárolt eljárások és függvények
* [[AdatbServerProgJegyzet20060502|Labor, 2006.05.02.]] — kurzorok, tárolt eljárások, függvények, =CASE=
* [[AdatbServerProgJegyzet20060505|Előadás, 2006.05.05.]] — DML és DDL triggerek, hibakezelés
* [[AdatbServerProgJegyzet20060509|Labor, 2006.05.09.]] — triggerek, hibakezelés, nézetek, példa kliens progi kiegészítése
* [[AdatbServerProgJegyzet20060512|Előadás, 2006.05.12.]] — dinamikus SQL, tranzakciók, XML (vetítős óra, XML nem vizsgaanyag)
* [[AdatbServerProgJegyzet20060516|Labor, 2006.05.16.]] — egy 2003-as vizsgát végig oldottunk
A jegyzetek Iváncsy Renáta kérésére csak bejelentkezés után elérhetők.

==Minta adatbázisok==

* Cukrász séma: https://avalon.aut.bme.hu/~reni/aszp/script/uj/sema/cukrasz_sema.pdf
* Tanár-diák séma: https://avalon.aut.bme.hu/~reni/aszp/script/uj/sema/tanar_diak_sema.pdf
* Forma-1 séma: https://avalon.aut.bme.hu/~reni/aszp/script/uj/sema/forma1_sema.pdf
* Oracle és MS SQL scriptek (<pre>CREATE TABLE</pre> és <pre>INSERT</pre>): https://avalon.aut.bme.hu/~reni/aszp/script.html
* Csatlakozás a tanszéki adatbázis szerverhez
** Csanás: egyetemi VPN-nel tavaly (2005-ben) el lehetett érni
** Chris: Oracle? szerver címe: deathstar.aut.bme.hu

==Vizsga==
* 50 perc elmélet
** csak papírt használhatunk
** a feladat kidolgozásánál megválaszthatjuk a nyelvet
** példa elméleti kérdésre:
*** soroljunk fel 3 beépített csomagot
*** tranzakciók tulajdonságai (ACID)
** példa feladatra:
*** írjunk tárolt eljárást kivételkezeléssel
* 90 perc gyakorlat
** bármi használható (jegyzet, net, stb.)
** 4 feladat, 2 Oracle + 2 Microsoft SQL
** az egyik Oracle feladat biztosan egy csomag írása
** a megoldást emailben kell beküldeni
* Elővizsga a 2006. tavaszi félévben utolsó héten pénteken.
<br/> Mivel idén összesen 4 jelentkező volt, megállapodtunk a szóbeli vizsgában, ami szerintem lényegesen egyszerűbb volt, mint ha a gépek előtt kellett volna görnyedni másfél órán keresztül. Szóbelin 1 nagyobb témakört kaptunk (én pl. az SQL Server triggereket), és másba nem is kérdeztek bele.

* 2008. őszi félév: Egy komplexebb tárolt eljárás megírása papíron, segédeszköz nélkül és egy elméleti kérdés kidolgozása, majd szóbeli vizsga az előzőek alapján. Szóval változott kicsit a számonkérés. -- [[AndrasGelanyi|giga]] - 2009.01.20.

-- [[PallosPeter|Peti]] - 2006.05.18.

* {{InLineFileLink|Valaszthato|AdatbServerProg|000_elmeleti_kerdesek.rtf|000_elmeleti_kerdesek.rtf}}: Elméleti kérdések kidolgozása

%META:FORM{name="ValaszthatoForm"}%
%META:FIELD{name="Trgy" title="Tárgy" value="Adatbázisok szerver oldali programozása"}%
%META:FIELD{name="Trgykd" title="Tárgykód" value="VIAU9159"}%
%META:FIELD{name="Tanszk" title="Tanszék" value="AUT"}%
%META:FIELD{name="Elad" title="Előadó" value="Iváncsy Renáta, Dr. Iváncsy Szabolcs"}%
%META:FIELD{name="Kreditszm" title="Kreditszám" value="5"}%
%META:FIELD{name="raszm" title="Óraszám" value="4"}%
%META:FIELD{name="Flv" title="Félév" value=""}%
%META:FIELD{name="Terlet" title="Terület" value="Programozás"}%
%META:FIELD{name="raijelenlt" title="Órai jelenlét" value="nem kötelező"}%
%META:FIELD{name="Jegy" title="Jegy" value="vizsga "}%
%META:FIELD{name="Elvrtmin.munka" title="Elvárt min. munka" value="bejárás"}%
%META:FIELD{name="Minimumrajrjegy" title="Minimumra járó jegy" value=""}%
%META:FIELD{name="Elvrtmax.munka" title="Elvárt max. munka" value="kis utánaolvasás - kis munka"}%
%META:FIELD{name="Munkrajrjegy" title="Munkára járó jegy" value="5"}%

[[Category:Valaszthato]]

AdatbServerProgJegyzet20060317

2013-03-31T18:48:53Z

2001:738:2001:2078:0:208:37:0: Átirányítás ide: Adatbázisok szerver oldali programozása

#REDIRECT [[Adatbázisok_szerver_oldali_programozása]]