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

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.14. >>

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:

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, ...] 

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

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;

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:

  COMMENT ON anyag IS '...';

ALL_COL_COMMENTS, USER_COL_COMMENTS

•  COMMENT ON COLUMN anyag.anyag_id IS '...';

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_ERRORS, ALL_ERRORS

-- Matyi - 2006.06.09.