Mi Érdekel?: Oracle SQL

Hierarchia lekérdezése

ORACLE-ben használt bővítése lehetővé teszi, hogy egy táblában kódolt hierarchikus adatokat a hierarchiának megfelelő sorrendben lekérdezzük.

CONNECT BY - specifikálja a kapcsolatot a szülő és a gyerek sorok között, amely alapján a tábla sorait hierarchiába lehet kapcsolni.
PRIOR: a PRIOR operátoros kifejezés reprezentálja a szülőt, először ez lesz kiértékelve a kiválasztott sorra, majd a másik oldali kifejezés a tábla összes sorára. Azon sorok lesznek a gyerek sorok, amelyekre a feltétel igaz. (A fabejárási irányt határozza meg ezáltal.)
START WITH : melyik csomópont(ok)on induljon.
LEVEL: pszeudó oszlop, amely lehetővé teszi annak a szintnek a lekérdezését, amelyen az egyes adatok a hierarchiában szerepelnek (pszeudó oszlop: nincs a táblában tárolva, de úgy viselkedik mint egy normál oszlop)

SELECT LEVEL, LPAD(' ', 2*LEVEL) || a_nev nev, beosztas, a_kod, fonok

FROM alkalmazott CONNECT BY PRIOR a_kod = fonok

START WITH beosztas = 'TELEPHELYVEZETO';

SELECT Nem
, LISTAGG(Nev,’,’) WITHIN GROUP (ORDER BY Nev) /*OVER(PARTITION BY Nem)*/ AS Elso_Nevek
, REGEXP_REPLACE(LISTAGG(Nev,’,’) WITHIN GROUP (ORDER BY Nev) /*OVER(PARTITION BY Nem)*/,'([^,]*)(,1)+($|,)’,’13’) AS Egyedi_nevek
FROM
( SELECT LEVEL AS ID
, DECODE(LEVEL,1,’Brigitta’,2,’László’,3,’László’,4,’Krisztina’,5,’Lajos’,6,’Péter’,7,’Béla’,8,’Péter’,9,’Gábor’,10,’Jenő’,11,’Norbert’) AS Nev
, DECODE(LEVEL,1,’Nő’,2,’Férfi’,3,’Férfi’,4,’Nő’,5,’Férfi’,6,’Férfi’,7,’Férfi’,8,’Férfi’,9,’Férfi’,10,’Férfi’,11,’Férfi’) AS Nem
FROM DUAL CONNECT BY LEVEL<=11
)
GROUP BY Nem
—-
SELECT Nem
, wm_concat(nev) as Elso_Nevek
FROM
( SELECT LEVEL AS ID
, DECODE(LEVEL,1,’Brigitta’,2,’László’,3,’László’,4,’Krisztina’,5,’Lajos’,6,’Péter’,7,’Béla’,8,’Péter’,9,’Gábor’,10,’Jenő’,11,’Norbert’) AS Nev
, DECODE(LEVEL,1,’Nő’,2,’Férfi’,3,’Férfi’,4,’Nő’,5,’Férfi’,6,’Férfi’,7,’Férfi’,8,’Férfi’,9,’Férfi’,10,’Férfi’,11,’Férfi’) AS Nem
FROM DUAL CONNECT BY LEVEL<=11
)
GROUP BY Nem

DML (Adatmanipulációs, adatkezelő nyelv )

Adatok lekérdezése:

SELECT adatok kiválasztása, megjelenítése

Adatok karbantartása: INSERT, UPDATE, DELETE
Az táblák adatokkal való feltöltésére, létező adatok módosítására és törlésére alkalmasak:

INSERT - Új sor(ok) felvitele
UPDATE - Meglévő sor(ok) módosítása
DELETE - Meglévő sor(ok) törlése

Szabványos adatbázis objektum fajták:

Tábla - TABLE: a felhasználói adatok tárolására szolgál, sorokból és oszlopokból áll.
Nézettábla -VIEW: más táblákból, nézettáblákból levezetett virtuális tábla.
Index - INDEX: lekérdezéseket gyorsítja, karbantartásuk időigényes.
Szinonima - SYNONYM: objektumok alternatív neve.
Szekvencia - SEQUENCE: egyedi, elsődleges kulcs értéket generáló objektum.
Materializált nézettábla (pillanatfelvétel): MATERIALIZED VIEW | SNAPSHOT: más táblákból, nézettáblákból származtatott adatok tárolására szolgáló tábla. Frissítése állítható.
Felhasználók és jogosultságok kezelése: USER, PROFILE, SCHEMA, ROLE, stb.
Programegységek:

- függvények (FUNCTION),
- eljárások (PROCEDURE),
- csomagok (PACKAGE),
- triggerek (TRIGGER), melyek PL/SQL vagy JAVA procedúrális nyelven készíthetők.

Adatbázis és a logikai szerkezet kialakítása: DATABASE, TABLESPACE, DATABASE LINK, stb.
Működési egységek paramétereinek kezelése: SYSTEM (instance), SESSION (felhasználó adatbázishoz való kapcsolódása).

CREATE TABLE tablam (
t_kod VARCHAR2(2) PRIMARY KEY,
t_nev VARCHAR2(15),
cim VARCHAR2(15));

Jogok és szerepkörök

Rendszer szintű jogok (privilégiumok):

a rendszer használatával kapcsolatos jogok, azaz milyen tevékenységeket (utasításokat) hajthat végre. DBA adhatja ezeket.

CREATE USER, CREATE SESSION, CREATE [ANY] TABLE, CREATE [ANY] VIEW, CREATE [ANY] SEQUENCE, ALTER [ANY] TABLE, DROP [ANY] TABLE, stb.

Objektum szintű jogok (hozzáférési jogok):

az objektumok használatával kapcsolatos jogok, azaz konkrét objektumokkal mit csinálhat. Minden felhasználó a saját objektumait korlátozás nélkül használhatja. Más felhasználók objektumaihoz, csak konkrét jogok birtokában férhet hozzá. Hozzáférési jogokat az objektum létrehozója (tulajdonosa) vagy a DBA adhat másoknak. A konkrétan megadható objektum jogok az objektum típusától függenek.

SELECT, INSERT, DELETE, UPDATE, REFERENCES, stb.

Rendszerjogok adása:

GRANT {rendszer_jog | szerepkör | ALL [PRIVILEGES]} , ... TO {felhasználó_név | szerepkör | PUBLIC}, … [IDENTIFIED BY jelszó] [WITH ADMIN OPTION];

Rendszerjogok visszavonása:

REVOKE {rendszer_jog | szerepkör | ALL [PRIVILEGES]},… FROM {felhasználó_név | szerepkör | PUBLIC}, …;
Ha egy felhasználónak minden privilégiumát megszüntetjük az objektumai még megmaradnak az adatbázisban előtte célszerű ezeket DROP-al törölni.

Objektumjogok adása:

GRANT {objektum_jog [(oszlop, …)] | ALL [PRIVILEGES]},… ON objektum TO {felhasználó_név | szerepkör | PUBLIC} [WITH GRANT OPTION];

Tábla kapcsolatok

UNION - Egyesítés eredménye: legalább az egyik táblában előforduló sorok, sor duplikáció nincs.
UNION ALL - Egyesítés eredménye: a táblákban előforduló sorok, sor duplikációt megenged.
INTERSECT - Metszet eredménye: mindkét táblában előforduló közös sorok.
MINUS - Kivonás eredménye: az első táblából elhagyjuk a második táblában előforduló sorokat.

Függvények

Matamatika fv:

POWER (3, 2) › 9
ROUND (45.923, 2) › 45.92
ROUND (45.923, 0) › 46
ROUND (45.923, -1) › 50
TRUNC (45.923, 2) › 45.92
TRUNC (45.923) › 45
TRUNC (45.923, -1) › 40
MOD (1600, 300) › 100

Szting fv:

INITCAP ('alma') › Alma
UPPER ('alma') › ALMA
LOWER('Alma') › alma
LENGTH ('alma') › 4
SUBSTR ('alma', 1, 3) › alm
INSTR ('xyalmaxyalmaxyxy', 'xy', 3, 2) › 13
LPAD ('alma', 6, '*') › **alma
RPAD ('alma', 6, '*') › alma**
LTRIM ('xyxXalmax', 'xy') › Xalmax
RTRIM ('xalmaXxyx', 'xy') › xalmaX

Dátum fv:

SYSDATE › aktuális dátum és idő
ADD_MONTHS ('21-JUN-74', 2) › 21-AUG-74
MONTHS_BETWEEN ('01-SEP-95', '11-JAN-94') › 19.6774194
LEAST ( '01-JUL-94', '13-JUL-94' ) › 01-JUL-94 a legkisebb
NEXT_DAY(SYSDATE, 'Friday') › a dátumhoz legközelebbi péntek dátuma
LAST_DAY(SYSDATE) › a dátum hónapjának utolsó napja

Konverzios:

TO_CHAR(dátum_kif, 'formátum'),
TO_CHAR(szám_kif, ' formátum '),
TO_DATE('dátum_kar', ' formátum '),
TO_NUMBER('szám_kar', ' formátum ')

Egyéb függvények:

NVL(mezo1,'lajos'),
GREATEST(Mezo1),
LEAST(Mezo1),
DECODE,

Triggerek:

A trigger két komponensből áll, egy feltétel és egy választevékenység részből.
A trigger működési elve igen egyszerű: ha a feltétel bekövetkezik, akkor
véghrehajtódik a választevékenység.
—
1. BEFORE művelet szintű trigger
2. ciklus az érintett rekordokra
a. BEFORE rekord szintű trigger a rekordra
b. rekord zárolása és módosítása, integritási feltételek ellenőrzése
c. AFTER rekord szintű trigger a rekordra
3. késeltett ellenőrzésű integritási feltételek ellenőrzése
4. AFTER műveleti szintű trigger
—
A rendszer kétféle rekordváltozót is tartalmaz, az egyik a rekord régi,
módosítás előtti, míg a másik a rekord új, módosítás utáni értékeit tartalmazza.

A két rekordváltozó alapértelmezés szerinti azonosítói:
OLD régi rekordérték
NEW új rekordérték
A PL/SQL blokkon belül e változók, mint külső, nem a PL/SQL blokkban
deklarált változók szerepelnek, ezért hivatkozáskor nevük elé egy kettőspontot
kell tenni, hasonlóan ahogy a beágyazott SQL-ben a gazdanyelvi változókat
használhatjuk. A rekordon belüli régi mezőkértékekre hívtkozni a
:OLD.mezőnév
:NEW.mezőnév

 --- pl:
 CREATE TRIGGER t1 AFTER DELETE ON auto
 BEGIN
 INSERT INTO naplo VALUES ('torles', SYSDATE);
 END;

Példa:

CREATE OR REPLACE TRIGGER Triggerem
 BEFORE INSERT ON R2
 FOR EACH ROW

DECLARE
 Hiba EXCEPTION;

BEGIN
 IF INSERTING
 then
 IF Tilt(:new.Tanar, :new.Diak)
 then
 -- Az alábbi INSERT parancsot végrehajtja
 -- az R2-n és az R22 táblákon
 insert into R22
 values (:new.tanar, :new.diak, :new.oraszam);
 else
 RAISE Hiba;
 end IF;
 end IF;

EXCEPTION
 WHEN Hiba
 then
 RAISE_APPLICATION_ERROR(-20001, 'Hiba a Trigi trigger futása során...!');
END;
/

Tárolt eljárás:

CREATE PROCEDURE elárásnév (paraméterlista) AS PL/SQL_blokk;
**** paraméternév jelleg adattipus
A jelleg lehetsléges értékei:
IN bementi paraméter
OUT kimeneti paraméter
IN OUT mindkét irányba mutató adatforgalmat lebonyolító
paraméter

Tárolt függvény:

CREATE FUNCTION atlag (tip IN CHAR(20)) RETURN NUMBER IS ertek NUMBER;
 BEGIN
 SELECT AVG(ar) INTO ertek FROM
 autok WHERE tipus LIKE tip;
 RETURN (ertek);
 END;

Egy szöveg rendező függvény:

create or replace function sort_string (v_string varchar2,v_delim varchar2,v_mode varchar2) return varchar2 is
 v_return varchar2(4000);
begin
 if lower(v_mode)='s' then
 for i in
 ( select regexp_substr(v_string||v_delim,'[^'||v_delim||']+',1,x.x) as elem
 from
 ( select level as x from dual connect by level<=length(regexp_replace(v_string,'[^'||v_delim||']'))+1) x
 order by elem
 )
 loop
 if v_return is null then
 v_return:=i.elem;
 else
 v_return:=v_return||v_delim||i.elem;
 end if;
 end loop;
 elsif lower(v_mode)='n' then
 for i in
 ( select regexp_substr(v_string||v_delim,'[^'||v_delim||']+',1,x.x) as elem
 , to_number(regexp_substr(v_string||v_delim,'[^'||v_delim||']+',1,x.x)) as elem_n
 from
 ( select level as x from dual connect by level<=length(regexp_replace(v_string,'[^'||v_delim||']'))+1) x
 order by elem_n
 )
 loop
 if v_return is null then
 v_return:=i.elem;
 else
 v_return:=v_return||v_delim||i.elem;
 end if;
 end loop;
 end if;
 return v_return;
end;
/

Névtelen blokk

DECLARE
 egyed ember%ROWTYPE;
 nev CHAR(20);
 BEGIN
 SELECT * INTO egyed FROM ember WHERE id = 2345;
 nev := egyed.nev;
 END;

Kurzorok használata:

– kurzor deklaráció
DECLARE
…
CURSOR kurzornév (paraméterlista) IS SELECT_utasítás;
a select végén álhat a : FOR UPDATE OF mezőlista // CURRENT OF kurzornév
– kurzor megnyitás
OPEN kurzornév (paraméterlista);
– rekord beolvasások ciklusa
FETCH kurzornév INTO változólista;
… IF (kurzornév%NOTFOUND) then … // EXIT WHEN kurzornév%NOTFOUND;
– kurzor lezárás
CLOSE kurzornév;
—

Hibakezelés : EXCEPTION

Hibakódok:
NO_DATA_FOUND A SELECT utaítás vagy a FETCH nem tud
eredményrerekordot visszaadni
ZERO_DIVIDE nullával való osztás
VALUE_ERROR adatkonverziós hiba
—
Hiba felhasználói kiváltása : RAISE hibakód;
—
DECLARE
sajathiba EXCEPTION;
BEGIN
…
IF x < 16 THEN
RAISE sajathiba;
END IF;
…
EXCEPTION
…
WHEN sajathiba THEN
ROLLBACK;
…
END;

Csomagba helyezett tárolt eljárás és függvény:

-- def (header) rész
 CREATE OR REPLACE PACKAGE my_pkg AS
 PROCEDURE my_proc(arg1 IN VARCHAR2);
 FUNCTION my_func(arg1 IN NUMBER) RETURN VARCHAR2;
 END my_pkg;
 -- body rész:
 CREATE OR REPLACE PACKAGE BODY my_pkg AS
 --
 FUNCTION my_private_func(arg1 IN NUMBER) RETURN VARCHAR2 IS
 return_val VARCHAR2(20);
 BEGIN
 SELECT col1 INTO return_val FROM tab2 WHERE col2 = arg1;
 RETURN return_val;
 EXCEPTION
 WHEN NO_DATA_FOUND THEN
 RETURN `NOT FOUND';
 END my_private_func;
 --2
 PROCEDURE my_proc(arg1 IN VARCHAR2) IS
 BEGIN
 UPDATE tab1 SET col1 = col1 + 1
 WHERE col2 = arg1;
 END my_proc;
 --3
 FUNCTION my_func(arg1 IN NUMBER) RETURN VARCHAR2 IS
 BEGIN
 RETURN my_private_func(arg1);
 END my_func;
END my_pkg;

CDV ellenőrző csomagba helyezve:

create or replace
package cdv as
 -- Vektor típus
 type vector is table of int(1);
 -- Inicializációs vektor
 iv constant vector := vector(9, 7, 3, 1, 9, 7, 3);
 -- Validációs függvény
 function validate(value in varchar2) return boolean;
end cdv;

create or replace
package body cdv as
 function validate(value in varchar2) return boolean as
 s int := 0;
 begin
 -- Ha az érték üres, vagy nem felel meg a formai követelményeknek, akkor a visszatérési érték hamis
 if value is null or length(value) <> 11 or not regexp_like(value, '^[1-9][0-9]{10}$') then
 return false;
 end if;

-- Iterálás az inicializációs vektoron
 for i in iv.first..iv.last
 loop
 -- Számjegyek súlyozott összeadása
 s := s + iv(i) * to_number(substr(value,i,1));
 end loop;

-- Ellenőrző számjegy vizsgálata
 if mod(10 - mod(s,10),10) = to_number(substr(value,8,1)) then
 -- Siker esetén a visszatérési érték igaz
 return true;
 end if;

-- Ha a vizsgálat sikertelen volt, akkor a visszatérési érték hamis
 return false;
 end validate;
end cdv;

create or replace
function cdv_validate
(
 value in varchar2
) return number is
begin
 if cdv.validate(value) then
 return 1;
 end if;
 return 0;
end;

Oracle stringek literálmegadási módok (quoting string literals) az idéző jelek feloldásánál:
‘ez egy ”idézet”’ — kettőzőtt idézőjelezett
q'{ ez egy ‘idézet’}’ — q jelzett

Oracle függvények:

LPAD (‘alma’, 6, ‘*’) => **alma | balról kiegészít/feltöllt
RPAD (‘alma’, 6, ‘*’) => alma** | jobbról kiegészít/feltöllt
LTRIM (‘xyxXalmax’, ‘xy’) => Xalmax
RTRIM (‘xalmaXxyx’, ‘xy’) => xalmaX

CONCAT({oszlop1 | kifejezés1}, {oszlop2 | kifejezés2}) – a két megadott karakterláncot összefűzi. Azonos a (||) operátorral.

INSTR(kifejezés, ‘keresendő sztring’) – az első előfordulás pozíciószámát adja vissza
INSTR (‘xyalmaxyalmaxyxy’, ‘xy’, 3, 2) => 13

SUBSTR(kifejezés, kezdő_pozíció, hossz) – A kezdő_pozíció-tól kezdődően a hossz hosszúságú sztringet adja vissza

RPAD({oszlop | kifejezés}, n, ’kitöltő’) – az oszlop értékeit n karakteren balra igazítva jeleníti meg úgy, hogy a jobb oldalon fennmaradó „üres” helyet a kitöltő karakterrel tölti fel.

LPAD({oszlop | kifejezés}, n, ’kitöltő’) – az oszlop értékeit n karakteren jobbra igazítva jeleníti meg úgy, hogy a bal oldalon fennmaradó „üres” helyet a kitöltő karakterrel tölti fel.

ABS({oszlop | kifejezés}) – Az oszlop, a kifejezés vagy a kifejezés által meghatározott oszlop soraiban lévő értékek abszolút értékét adja Vissza.

GREATEST(kifejezés, kifejezés,…) – A legnagyobb értéket adja vissza.

LEAST(kifejezés, kifejezés,…) – A legkisebbet adja vissza.
LEAST ( ’01-JUL-16′, ’13-JUL-16′ ) => 01-JUL-94 a legkisebb
SYSDATE => aktuális dátum és idő
ADD_MONTHS(dátum, hozzáadandó_hónapok_száma) – hozzáadás.
ADD_MONTHS (’21-JUN-16′, 2) => 21-AUG-16

MONTHS_BETWEEN(dátum1, dátum2) – a két dátum különbsége hónapokban. Eredménye egy szám.
MONTHS_BETWEEN (’01-SEP-16′, ’11-JAN-16′) => 19.6774194

NEX_DAY(dátum, ’nap’) – meghatározza a megadott dátum utáni ’nap’ nevű nap dátumát.
NEXT_DAY(SYSDATE, ‘Friday’) => a dátumhoz legközelebbi péntek dátuma

LAST_DAY(dátum) – meghatározza a megadott dátum által meghatározott hónap utolsó napjának dátumát.
LAST_DAY(SYSDATE) => a dátum hónapjának utolsó napja

Egy adott dátum (pl. a születésnapunk) napját írassuk ki betűvel:
SELECT TO_CHAR(TO_DATE(‘1982-01-31’, ‘YYYY-MM-DD’), ‘YYYY-MM Day’)
FROM DUAL;

Százalék karakterre keresés :

SELECT * FROM fiu WHERE MEZO1 LIKE ‘%%%’ ESCAPE ”

Hasonlóság keresések:

SELECT * FROM fiu WHERE MEZO1 LIKE 'W%ies' AND MEZO1 NOT LIKE '%L%'   -- normál likes forma
SELECT * FROM fiu WHERE REGEXP_LIKE(MEZO1, '^W([^L]*)ies$')                        -- reguláris kifejezéssel

——–
Apa nélküli fiuk is (apa adat üres) : SELECT f.MEZO1, f.MEZO2, a.MEZO3 FROM fiu f
LEFT OUTER JOIN apa a ON f.APA_KULCS = a.APA_KULCS
Fiu nélküli apa is (fiu adat üres) : SELECT f.MEZO1, f.MEZO2, a.MEZO3 FROM fiu f
RIGHT OUTER JOIN apa a ON f.APA_KULCS = a.APA_KULCS
Apa és fiu nélküliek is : SELECT f.MEZO1, f.MEZO2, a.MEZO3 FROM fiu f
FULL OUTER JOIN apa a ON f.APA_KULCS = a.APA_KULCS
Külső összekapcsolás:
SELECT a.MEZO1, f.MEZO2, f.MEZO3 FROM fiu f, apa a WHERE a.APA_KULCS = f.APA_KULCS (+);
———
Kétoldali outer join:
SELECT D.DEPT_ID, D.NAME, L.REGIONAL_GROUP FROM DEPARTMENT D, LOCATION L WHERE D.LOCATION_ID (+) = L.LOCATION_ID
UNION
SELECT D.DEPT_ID, D.NAME, L.REGIONAL_GROUP FROM DEPARTMENT D, LOCATION L WHERE D.LOCATION_ID = L.LOCATION_ID (+) ;
—
SELECT * FROM A CROSS JOIN B;
———
Gyorsabb, jobb:
SELECT f.* FROM fiu f JOIN apa a ON f.APA_KULCS = a.APA_KULCS WHERE a.APA_KULCS < 10;
SELECT f.* FROM fiu f WHERE EXISTS (SELECT * FROM apa a WHERE a.APA_KULCS < 10 AND f.APA_KULCS = a.APA_KULCS); — jobb
—
SELECT f.* FROM fiu f WHERE EXISTS (SELECT 1 FROM apa a WHERE a.APA_KULCS = f.APA_KULCS) // Cost = 320 — jobb
SELECT DISTINCT f.* FROM fiu f JOIN apa a ON a.APA_KULCS = f.APA_KULCS // Cost = 3056
——-
SELECT f.* FROM fiu f WHERE f.FIU_KULCS >100 AND f.APA_KULCS >= ALL
(SELECT a.APA_KULCS FROM apa a WHERE a.APA_KULCS > 30);
SELECT f.* FROM fiu f WHERE f.FIU_KULCS >100 AND f.APA_KULCS >= ANY
(SELECT a.APA_KULCS FROM apa a WHERE a.APA_KULCS < 300);
——-
A dinamikus nézet a select from jában szerepel egy másik szelect a két szelektet a fő select where-jében kapcsoljuk
SELECT d.dept_id, d.name, emp_cnt.tot FROM department d,
(SELECT dept_id, COUNT(*) tot FROM employee GROUP BY dept_id) emp_cnt
WHERE d.dept_id = emp_cnt.dept_id;
—
Allekérdezésné a WHERE sekcióban megadott másik select szerepel az al select where kapcsolást tartalmaz
Ha az allekérdezés több értéket add, akkor ALL, ANY, NOT ALL, NOT ANY kapcsolhat ill. IN (tartalmazás meghatározással) kapcsolhatunk.
SELECT p.part_nbr, p.name FROM supplier s, part p WHERE s.name = ‘Acme Industries’
AND s.supplier_id = p.supplier_id
AND 10 <= (SELECT COUNT(*) FROM cust_order co, line_item li WHERE li.part_nbr = p.part_nbr —itt kacsol a fo SELECThez
AND li.order_nbr = co.order_nbr AND co.order_dt >= TO_DATE(’01-DEC-2001′,’DD-MON-YYYY’)
);

Korrelációs együttható számítása, a CORR függvény

— A bevétel és a darabszám korrelációja termékenként
SELECT prod_id,CORR(quantity_sold,amount_sold) FROM sh.sales
GROUP BY prod_id
HAVING prod_id<200;
— A bevétel és a darabszám korrelációja vevőnként
SELECT cust_id,CORR(quantity_sold,amount_sold) FROM sh.sales
GROUP BY cust_id
HAVING cust_id<1000;
— Halmozott korrelációs együttható Az analitikus CORR függvény
SELECT t.calendar_month_desc,
CORR (SUM(s.amount_sold),SUM(s.quantity_sold))
OVER (ORDER BY t.calendar_month_desc) as CUM_CORR
FROM sh.sales s, sh.times t
WHERE s.time_id = t.time_id
GROUP BY t.calendar_month_desc
ORDER BY t.calendar_month_desc;

Oracle 8.-tól GROUP BY {o_kif, … | CUBE(o_kif,…) | ROLLUP(o_kif,…) }

SELECT DECODE(GROUPING(t_kod), 1, ‘Össz t_kód’, t_kod) AS t_kód,
DECODE(GROUPING(beosztas), 1, ‘Össz beosztás’, beosztas) AS beosztás,
COUNT(*) “Alk. szám”, AVG(fizetes) * 12 “Átlag fiz”
FROM alkalmazott
GROUP BY CUBE (t_kod, beosztas);
HAVING GROUPING(t_kod)=1 OR GROUPING(beosztas)=1; — ekkor csak az képzett összegzett sorok jelennek meg

Hierarchia lekérdezése ORACLE-ben

--- Három szintű fa kijelzés sima sql-el:
SELECT E_TOP.LNAME, E_2.LNAME, E_3.LNAME, E_4.LNAME
FROM EMPLOYEE E_TOP, EMPLOYEE E_2, EMPLOYEE E_3, EMPLOYEE E_4
WHERE E_TOP.MANAGER_EMP_ID IS NULL
AND E_TOP.EMP_ID = E_2.MANAGER_EMP_ID (+)
AND E_2.EMP_ID = E_3.MANAGER_EMP_ID (+)
AND E_3.EMP_ID = E_4.MANAGER_EMP_ID (+);

SELECT [LEVEL] …
FROM táblanév
…C
ONNECT BY {PRIOR o_kifejezés = o_kifejezés | o_kifejezés = PRIOR o_kifejezés}
[START WITH o_kifejezés = o_kifejezés]

-- Teljes hierachia megjelenítése:
SELECT LNAME, EMP_ID, MANAGER_EMP_ID FROM EMPLOYEE
START WITH MANAGER_EMP_ID IS NULL
CONNECT BY PRIOR EMP_ID = MANAGER_EMP_ID;

Nézet és matearizált nézet

CREATE OR REPLACE VIEW videk
AS SELECT * FROM telephely
WHERE cim <> ‘BUDAPEST’;
WITH CHECK OPTION;

—-

CREATE {SNAPSHOT | MATERIALIZED VIEW } nézettábla_név [(oszlopnév,…)]
[tárolási és egyéb előírások]
[REFRESH … frissítési paraméterek]
AS szelekciós_utasítás;

———–
CREATE SEQUENCE alk_seq
START WITH 2000 INCREMENT BY 1
MAXVALUE 9999 NOCYCLE;
Törlése:
DROP SEQUENCE alk_seq;
Módosítása:
ALTER SEQUENCE alk_seq …;

Oracle 10g-től kezdődően megjelent lomtár (recycle bin) a törölt adatbázis-okjektumok tárolási helye (amennyiben engedélyezett a szerverpéldány szintjén),
ahonnan azok szükség szerint visszaállíthatók.

Az objektumok szintje mellett lehetőség van az adatok szintjén is a visszaállításra az ún.
flashback technológiával, amely a 10g-ben SQL utasítások szintjére került (korábban egy PL/SQL csomag volt), és lehetőséget biztosít adatbázis és tábla szinten is a visszaállításra, illetőleg a korábbi állapot lekérdezésére.

Beállításhoz szűkséges privilégiumok: SELECT ANY DICTIONARY, FLASHBACK ANY TABLE vagy a SELECT_CATALOG_ROLE

FLASHBACK használat

— bekapcsolása egy táblára:
FLASHBACK TABLE kl_test
TO TIMESTAMP (SYSTIMESTAMP – INTERVAL ‘1’ minute);

— visszaállítási lehetőség beállítás
FLASHBACK TABLE kl_test TO BEFORE DROP RENAME TO kl_test_fb;

— szemetes használati működés listázása:
SELECT * FROM RECYCLEBIN;
SELECT * FROM USER_RECYCLEBIN;

— szemetes használat ki, be kapcsolás
ALTER SESSION SET recyclebin = ON;
ALTER SESSION SET recyclebin = OFF;

— Végleges törlés
PURGE TABLE kl_test;

ALTER TABLE table ENABLE ROW MOVEMENT;

CREATE TABLE t
 NOLOGGING
 ENABLE ROW MOVEMENT
 AS
 SELECT object_type AS x FROM all_objects;

 ----

create table emp3 as select * from empLOYEES;
 DROP TABLE EMP3;
SELECT * FROM RECYCLEBIN;
 FLASHBACK TABLE EMP3 TO BEFORE DROP;

DROP TABLE EMP3 PURGE;
 FLASHBACK TABLE EMP3 TO BEFORE DROP;
 PURGE RECYCLEBIN;

----
SELECT * FROM RECYCLEBIN;
SELECT * FROM USER_RECYCLEBIN;

FLASHBACK TABLE t TO TIMESTAMP TO_TIMESTAMP('2005-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')

FLASHBACK TABLE t TO TIMESTAMP SYSTIMESTAMP - INTERVAL '2' MINUTE;

A rendszer teljesítményanalíziséhez az AWR (Automatic Workload Repository, automatikus terhelés-repozitórium) rendszeres időközönként feljegyzi a fontosabb teljesítmény-paramétereket, amelyet az ADDM (Automatic Database Diagnostics Monitor, automatikus adatbázis-diagnosztikai monitor) komponens dolgoz fel és tesz elérhetővé.

2007-ben jelent meg az Oracle 11gR1, a Release 2 pedig 2009-ben. Számos apró újítása a hatékonyabb erőforrás-kihasználást célozza meg mind teljesítmény (pl. statisztika-gyűjtés, lekérdezéseredmény cache), mind tárhely (pl. tömörítés az egyedi DML műveletek eredmé-nyében is), mind DBA-erőforrások (pl. automatikus memória-tuning, terhelés-profilok rögzítése és visszajátszása: Real Application Testing) tekintetében.

A táblák a 11g-től kezdődően tartalmazhatnak ún. virtuális oszlopokat, amely a nézetekhez hasonlóan teszik lehetővé SQL kifejezésekkel definiált oszlopok megadását a rekord többi mezőjének értéke alapján. Ez a virtuális oszlop a tábla „teljes jogú” oszlopa lekérdezésekkor és indexek építésekor, ill. a tábla ún. particionálásakor.

2013. július 10., szerda

Oracle SQL