SQL nyelv és Oracle sajátosságai

Az SQL (Structured Query Language) az adatbázisok kezelésére és lekérdezésére szolgáló szabványos nyelv.

Az SQL alábbi fő kategóriára osztható:

DDL (Data Definition Language) – adatbázis szerkezetének kezelése (pl. táblák létrehozása, módosítása, törlése)
- CREATE, ALTER, DROP, TRUNCATE
DML (Data Manipulation Language) – adatok kezelése (beszúrás, módosítás, törlés)
- SELECT, INSERT, UPDATE, DELETE, MERGE
DCL (Data Control Language) – jogosultságok kezelése
- GRANT, REVOKE
TCL (Transaction Control Language) – tranzakciókezelés

COMMIT, ROLLBACK, SAVEPOINT

Oracle adatbázis egyediségei

Az Oracle adatbázis több szempontból is eltér más relációs adatbázis-kezelő rendszerektől, például MySQL, PostgreSQL vagy SQL Server rendszerektől.

PL/SQL – Oracle Procedural Language Extension to SQL

Az Oracle PL/SQL egy procedurális kiterjesztése az SQL-nek, amely lehetővé teszi ciklusok, elágazások, kivételek és kurzorok használatát.

Anonim blokkok

DECLARE
    v_emp_name VARCHAR2(100);
BEGIN
    SELECT ename INTO v_emp_name FROM emp WHERE empno = 7369;
    DBMS_OUTPUT.PUT_LINE(v_emp_name);
END;

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


CREATE OR REPLACE PROCEDURE get_employee_name (p_empno IN NUMBER) IS
    v_emp_name VARCHAR2(100);
BEGIN
    SELECT ename INTO v_emp_name FROM emp WHERE empno = p_empno;
    DBMS_OUTPUT.PUT_LINE(v_emp_name);
END;

Csomagok és trigger-ek

Az Oracle adatbázis egyik nagy előnye a PL/SQL csomagok és triggerek használata, amelyek segítenek az üzleti logika elkülönítésében, újrafelhasználásában és teljesítményoptimalizálásában.

Csomagok (Packages) az Oracle PL/SQL-ben

A csomagok (packages) olyan PL/SQL objektumok, amelyek elnevezett függvényeket, eljárásokat, típusokat, kurzorokat és változókat foglalnak össze egyetlen egységben.

Előnyök:

Kód modularizálása – az üzleti logika jól elkülöníthető egységekben tárolható.
Teljesítménynövekedés – ha egy csomag betöltődik a memóriába, akkor annak minden eleme elérhető marad a munkamenet során.
Adatbiztonság és elrejtés – a csomagokban privát és publikus komponensek is lehetnek, így az adatbázis-kezelő elrejtheti a belső implementációt.
Újrafelhasználhatóság – a csomagok többször felhasználható kódrészleteket tartalmazhatnak.

Csomag fejléce (Package Specification)

A csomag fejléce deklarálja azokat a függvényeket, eljárásokat és változókat, amelyeket más objektumok is elérhetnek.


CREATE OR REPLACE PACKAGE emp_package AS 
    FUNCTION get_employee_name (p_empno NUMBER) RETURN VARCHAR2;
    PROCEDURE update_salary (p_empno NUMBER, p_salary NUMBER);
END emp_package;
/

Csomag törzse (Package Body)

A csomag törzse tartalmazza a csomagban deklarált függvények és eljárások konkrét implementációját.


CREATE OR REPLACE PACKAGE BODY emp_package AS 

    FUNCTION get_employee_name (p_empno NUMBER) RETURN VARCHAR2 IS
        v_emp_name VARCHAR2(100);
    BEGIN
        SELECT ename INTO v_emp_name FROM emp WHERE empno = p_empno;
        RETURN v_emp_name;
    END get_employee_name;

    PROCEDURE update_salary (p_empno NUMBER, p_salary NUMBER) IS
    BEGIN
        UPDATE emp SET sal = p_salary WHERE empno = p_empno;
        COMMIT;
    END update_salary;

END emp_package;
/

Csomag használata

A csomagban lévő függvényeket és eljárásokat így lehet meghívni:

--1
SELECT emp_package.get_employee_name(7369) FROM dual;



--2
BEGIN
    emp_package.update_salary(7369, 5000);
END;
/

Triggerek az oracle adatbázisban

A triggerek olyan adatbázis-objektumok, amelyek automatikusan végrehajtanak egy előre definiált műveletet egy INSERT, UPDATE vagy DELETE esemény hatására.

Felhasználási területek:

Adatintegritás biztosítása (pl. nem engedi nullára csökkenteni egy raktárkészlet mennyiségét).
Auditálás és naplózás (pl. ki és mikor módosított egy adott táblát).
Üzleti szabályok érvényesítése (pl. egy dolgozó fizetése nem csökkenhet egy adott határ alá).

Sor szintű trigger (Row-Level Trigger)

Ez a trigger minden egyes sor esetén külön fut le egy INSERT, UPDATE vagy DELETE művelet során.

Példa: naplózó trigger, amely menti a fizetésváltozásokat

CREATE OR REPLACE TRIGGER salary_audit_trigger 
AFTER UPDATE OF sal ON emp
FOR EACH ROW
BEGIN
    INSERT INTO salary_audit (empno, old_salary, new_salary, change_date)
    VALUES (:OLD.empno, :OLD.sal, :NEW.sal, SYSDATE);
END;
/

:OLD – a módosítás előtti értékeket tartalmazza.
:NEW – a módosítás utáni értékeket tartalmazza.
AFTER UPDATE OF sal ON emp – csak a fizetés (sal) mező változásakor fut le.

Tábla szintű trigger (Statement-Level Trigger)

Ez a trigger egyszer fut le egy SQL művelet hatására, függetlenül attól, hogy hány sort érint a művelet.


CREATE OR REPLACE TRIGGER update_employee_count 
AFTER INSERT OR DELETE ON emp
BEGIN
    UPDATE company_stats SET employee_count = (SELECT COUNT(*) FROM emp);
END;
/

BEFORE és AFTER triggerek

BEFORE trigger: A művelet előtt fut le (még azelőtt, hogy az adatok ténylegesen módosulnának).
AFTER trigger: A művelet után fut le (amikor az adatok már módosultak).

Példa: Minimum fizetés ellenőrzése BEFORE triggerrel


CREATE OR REPLACE TRIGGER enforce_min_salary 
BEFORE INSERT OR UPDATE OF sal ON emp
FOR EACH ROW
BEGIN
    IF :NEW.sal < 3000 THEN
        RAISE_APPLICATION_ERROR(-20001, 'A fizetés nem lehet 3000-nél kevesebb!');
    END IF;
END;
/

Ha valaki megpróbálna egy dolgozónak 3000 alatti fizetést adni, akkor a trigger hibát dob.

Összegzés

- PL/SQL csomagok segítenek az üzleti logika modularizálásában, újrafelhasználásában és optimalizálásában.
- Triggerekkel automatikusan végrehajthatunk adatbázis-műveleteket, például auditálást, integritás-ellenőrzést és üzleti szabályok betartását.
- A row-level triggerek minden egyes sorhoz lefutnak, míg a statement-level triggerek csak egyszer futnak egy SQL művelethez.
- BEFORE triggerek a művelet előtt, AFTER triggerek a művelet után futnak le.

Flashback technológia

Oracle egyedülálló Flashback funkciója lehetővé teszi az adatok visszaállítását egy korábbi időpontra anélkül, hogy teljes adatbázis-helyreállításra lenne szükség.

Flashback Query (korábbi állapot megtekintése)


SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE);

Flashback Table (tábla visszaállítása egy korábbi időpontra)


FLASHBACK TABLE employees TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE);

Oracle Partitioning (Adattábla particionálás)

Oracle lehetőséget biztosít az adattáblák particionálására, amely segíti a nagy adatmennyiségek kezelését.

Range partitioning


CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION sales_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
    PARTITION sales_2025 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD'))
);

4. Advanced Queuing (AQ) – Üzenetsorok kezelése

Az Oracle Advanced Queuing (AQ) lehetővé teszi a tranzakciós üzenetkezelést az adatbázison belül.

Üzenetsor létrehozása


BEGIN
    DBMS_AQADM.CREATE_QUEUE_TABLE(
        queue_table => 'my_queue_table',
        queue_payload_type => 'RAW'
    );

    DBMS_AQADM.CREATE_QUEUE(
        queue_name => 'my_queue',
        queue_table => 'my_queue_table'
    );

    DBMS_AQADM.START_QUEUE(
        queue_name => 'my_queue'
    );
END;

Üzenet küldése és fogadása


DECLARE
    enqueue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
    message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
    message RAW(200);
    message_handle RAW(16);
BEGIN
    message := UTL_RAW.CAST_TO_RAW('Hello, Oracle Queue!');
    DBMS_AQ.ENQUEUE(
        queue_name => 'my_queue',
        enqueue_options => enqueue_options,
        message_properties => message_properties,
        payload => message,
        msgid => message_handle
    );
    COMMIT;
END;

JSON kezelés Oracle SQL-ben

Oracle támogatja a JSON adatok kezelését és tárolását.

JSON keresés


SELECT employee_data
FROM employees
WHERE JSON_EXISTS(employee_data, '$.skills[?(@.name == "SQL")]');

JSON_TABLE függvény használata


SELECT jt.name, jt.experience
FROM employees e,
     JSON_TABLE(e.employee_data, '$.skills[*]'
     COLUMNS (name VARCHAR2(100) PATH '$.name',
              experience NUMBER PATH '$.years')) jt;

Oracle Database In-Memory

Az In-Memory Column Store lehetővé teszi az adatok oszlop alapú tárolását és gyorsított lekérdezéseket.

Aktiválás
```
ALTER TABLE sales INMEMORY;
```

Lekérdezés optimalizáció

SELECT /*+ INMEMORY */ product_id, SUM(sales_amount)
FROM sales
GROUP BY product_id;

Oracle Autonomous Database

Az Oracle új generációs Autonomous Database megoldása mesterséges intelligenciával optimalizálja az adatbázis teljesítményét, automatikusan skálázódik és karbantartja magát.