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

Ez az oldal a korábbi SCH wiki-ről lett áthozva. Az eredeti változata itt érhető el.

Ha úgy érzed, hogy bármilyen formázási vagy tartalmi probléma van vele, akkor kérlek javíts rajta egy rövid szerkesztéssel.

Ha nem tudod, hogyan indulj el, olvasd el a migrálási útmutatót

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

<< Előadás, 2006.04.28. >>

SELECT @valt=anyag_id FROM anyag

• 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

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

• *INSENSITIVE*: kommitált törlések és módosítások láthatóak
• *SCROLL*: mindkét irányban lehet FETCH-elni
• *READONLY*: kurzoron keresztül nem 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ó:

DECLARE nev CURSOR [{LOCAL | GLOBAL}]
[{FORWARD ONLY | SCROLL}]
[{STATIC | KEYSET | DYNAMIC | FAST_FORWARD}]
[{READONLY | SCROLL_LOCKS | OPTIMISTIC}]
[TYPE_WARNING]
FOR lekerdezes
[FOR UPDATE [OF ...]]

• *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|KEYSET|DYNAMIC)
• *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

• @@CURSOR_ROWS: érintett sorok száma

[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 felszabadul

Kurzorváltozó

korzorváltozó = referencia egy létező kurzorra 

DECLARE @nev CURSOR

Ciklusszervezés

@@FETCH_STATUS

• 0: sikeres
• -1: nem sikerült
• -2: hiányzott a sor azért nem sikerült

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

Módosítás

• UPDATE, DELETE engedélyezett, INSERT nem
• WHERE CURRENT OF -t kell használni
• Ha a lekérdezés során a sorok sorrendjét megváltoztatjuk (ORDER BY, GROUP BY, stb.) akkor nem használhatunk 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

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

• 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

DROP PROC ...

nincs CREATE OR REPLACE

Pl.:

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

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

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.

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

• 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:

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

Hívás:

• =SELECT ap60.felkesz_ar(5)=
• =SELECT ap60.felkesz_ar(felkesz_id) FROM felkesz=

Inline table valued

CREATE FUNCTION név (@p típus(adat)) RETURNS TABLE AS
	RETURN lekérdezés
END

hívás: =SELECT * FROM ap60.név(paraméter)=

Tábla visszatérésű több soros

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

ALTER FUNCTION név

A három típus nem átjárható

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=

-- Matyi - 2006.08.12.