DESCRIBE dual SELECT dummy FROM dual; -- SQL számológép -- Nem is kell használni az attribútumát, a lényeg, hogy 'dual' egy egysoros -- tábla. Annyiszor jelenik meg az eredmény, ahány sora van. SELECT abs(-4-6)*(10+1)/50 "Eredmeny:" FROM dual; SELECT sysdate "A mai datum" FROM dual; -- Igaz-e egy logikai kifejezés -- 'X'-et ad, ha igaz, "no rows selected", ha hamis az állítás. SELECT * FROM dual WHERE trunc(abs(-1.9)) = length('ww') - cos(0); -- Sorfüggvények -- (Az összeshez nézd meg a dokumentációt. Nem mind van Oracle v8.0.5-ben.) ------------------------------------------------------------------------------ REM x+y x-y x*y x/y mod(x,y) sqrt(x) REM sign(x) abs(x) floor(x) ceil(x) REM exp(x) ln(x) power(a,x) log(a,x) REM round(x) -- egészekre kerekít REM round(x,d) -- d tizedesjegyre kerekít REM round(x,-d) -- tízesekre/százasokra/ezresekre/... kerekít REM trunc(x) -- egészekre vág REM trunc(x,d) -- d tizedesjegyre vág REM trunc(x,-d) -- tízesekre/százasokra/ezresekre/... vág REM cos(x) sin(x) tan(x) *ctg(x) -- REM aCos(x) *aSin(x) aTan(x) *aCtg(x) -- * : nincs Oracle-ben REM cosh(x) sinh(x) tanh(x) *cth(x) -- REM *aCosh(x) *aSinh(x) *aTanh(x) *aCth(x) -- REM s1 || s2 lower(s) upper(s) REM replace(s,mit,mire) -- 's'-ben 'mit' 'mire' cserél REM replace(s,mit) -- 's'-ben 'mit' töröl REM substr(s,hon,hany) -- 's' részsorozata: 'hany' karakter 'hon'-nan REM substr(s,-hon,hany) -- 's' részsorozata: 'hany' karakter a végétől REM substr(s,hon) -- 's' részsorozata: 'hon'-nan a végéig REM -- Üres stringek helyett NULL-t adnak vissza! REM -- Ebben az Oracle-ben '' = NULL. REM -- A stringek első karakterének indexe: 1 (ha 0-t adsz meg, 1-nek veszi.) REM s LIKE pattern -- igaz, ha a 'pattern' minta illeszkedik 's'-re. REM -- A minta elemei (nagyon egyszerű minta): REM -- _ : egy akármilyen karakterre illeszkedik (regexp .) REM -- % : akárhány akármilyen karakterre illeszkedik (regexp .*) REM -- REM -- Megj. REM -- A KSH adatbázis sorai valójában ékezetesek, csak nem jelenik meg. REM -- Az oszlopok nevei viszont nem ékezetesek (nem is lehetnek). REM ascii(c) -- Karakter ASCII kódja. REM length(s) -- string hossza REM instr(s1,s2,n,k) -- 's1' n-edik karakterétől 's2' 'k'-adik \ REM instr(s1,s2,n) k=1 -- előfordulását keresi, és visszaadja \ REM instr(s1,s2) n=1 k=1 -- 's2' első karakterének indexét. (1-től számoz) -- Ha bármelyik függvényt a NULL-ra alkalmazzuk, az eredmény NULL lesz -- (=ismeretlen), kivétel: ||. Az ''-et (üres stringet) ez az Oracle még -- NULL-nak tekinti, pl: length('') IS NULL. REM sysdate -- a mostani dátumot-időt adja (date típusú) REM l1 AND l2 -- logikai ÉS REM l1 OR l2 -- logikai VAGY REM NOT l -- logikai NEM REM ertek BETWEEN e1 AND e2 -- igaz, ha ertek "eleme" [e1..e2] REM ert NOT BETWEEN e1 AND e2 -- igaz, ha ert "nem eleme" [e1..e2] REM EXISTS( lekérdezés ) -- igaz, ha a 'lekérdezés' eredménye nem üres REM NOT EXISTS( lekérdezés ) -- igaz, ha a 'lekérdezés' eredménye üres REM oszlop IS NOT NULL -- igaz, ha az 'oszlop'-ban lévő érték nem NULL REM oszlop IS NULL -- igaz, ha az 'oszlop'-ban lévő érték NULL REM oszlop = NULL -- eldönthetetlen (unknown, 3-értékű logika). REM oszlop != NULL -- eldönthetetlen (unknown, 3-értékű logika). REM nvl(x,y) -- x IS NOT NULL => x, x IS NULL => y REM vsize(elem) -- hány bájtot használ 'elem' tárolásához REM uid -- Oracle user ID-d REM user -- Oracle login neved -- A 3-értékű logika műveleteinek táblázata: -- http://medusa.inf.elte.hu/oradoc8/server.815/a67779/operator.htm#998106 -- "eleme" operátor REM sor IN (Halmaz) -- igaz, ha sor "eleme" halmaz ( = ANY ) REM sor NOT IN (Halmaz) -- igaz, ha sor "nem eleme" halmaz ( != ALL ) -- A halmaz lehet elemek felsorolása, vagy egy SELECT. -- Például a következő lekérdezések mind "igazak": -- A halmazjelek a szokásos {} helyett (). SELECT * FROM dual WHERE 1 in (1); SELECT * FROM dual WHERE 1 in (1, 2, 3, 4); SELECT * FROM dual WHERE (1,1) in ((1,1)); SELECT * FROM dual WHERE (1,1) in ((1,1), (2,2), (3,3), (4,4)); SELECT * FROM dual WHERE 1 in (SELECT n FROM amok); SELECT * FROM dual WHERE (1,0) in (SELECT n,prim FROM amok); -- A számok helyén állhatnak egyéb konstansok és kifejezések is. SELECT * FROM dual WHERE 'a' in ('a','aa','aaa','aaaa'); SELECT * FROM dual WHERE 0+1 in (cos(0), 2, 3, 4); -- Ha NULL is van a halmazban, váratlan eredményeket kaphatunk! -- A WHERE csak "igaz" esetén teljesül. SELECT * FROM dual WHERE 1 in (1,null) /* igaz */; SELECT * FROM dual WHERE 1 in (0,null) /* Unknown */; SELECT * FROM dual WHERE 1 not in (1,null) /* hamis */; SELECT * FROM dual WHERE 1 not in (0,null) /* Unknown */; SELECT * FROM dual WHERE null in (0,1,2,null) /* Unknown */; -- A kiértékelés logikája: a NULL az ismeretlen érték, tehát "akármi lehet". -- Pl. a 2. sorban ha "NULL=1", akkor benne lenne, ha meg "NULL=más", -- akkor nem lenne benne, ezért az állítás eldönthetetlen (unknown). REM oszlop <= ANY (egyoszlopos lekérdezés) -- Valamelyikre teljesülnie kell REM oszlop != ANY (egyoszlopos lekérdezés) -- Valamelyikre teljesülnie kell REM oszlop > ALL (egyoszlopos lekérdezés) -- Mindegyikre teljesülnie kell REM oszlop < ( 1x1-es lekérdezés) -- 1x1-es: egy értéknek tekintendő REM ... -- Üres lekérdezés esetén ANY automatikusan hamis, ALL automatikusan igaz. -- Az egyoszlopos lekérdezés helyett fel is sorolhatjuk az elemeket, -- mint IN-nél. -- Az ANY és az ALL elhagyható, de ekkor elvárja, -- hogy az egyoszlopos lekérdezés egy soros is legyen. Ha ezt nem tartjuk be: -- ORA-01427: single-row subquery returns more than one row
---- DUPLIKÁCIÓ törlése
delete from kl.kl_tbl t
Where t.run_date = DATE '2017-05-12'
And rowid in (select rowid
from (select row_number() over(partition by account_id order by account_id) as rowno,
where rowno > 1);
----- PARTICIÓ létrehozása
CREATE TABLE KL_GENERAL
(MONTH NUMBER (4),
BATCH VARCHAR2(4),
JIB number
);
ALTER TABLE KL_GENERAL ADD PARTITION P_20180530 VALUES LESS THAN ( DATE'2018-06-01' ) ;
ALTER TABLE KL_GENERAL ADD PARTITION P_20180631 VALUES LESS THAN (TO_DATE(' 2018-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));
----- Jog osztás
grant select on KL_CTRL to DBLINK_KL_STAGE; -- DBlink
----- Szinonima létrehozása
create or replace synonym T_KL_EXP_01 for TBL_COLL_EXP@klajos.hu;
------ PARTICIÓ lekérdezés
select tablespace_name --into --v_old_tablespace
from user_tab_partitions
where table_name='table_name'
and partition_position=(
select max(partition_position) from user_tab_partitions
where table_name='table_name')
;
------- Könyvelés minta
LEFT JOIN ACCOUNTING_STRUCTURE_ITEM I ON 1=1
AND I.ACCOUNTING_STRUCTURE_ITEM_ID = X.ACCOUNTING_STRUCTURE_ITEM_ID
AND I.START_OF_VALIDITY <= TRUNC(&EFFECTIVE_LOAD_DATE +1,'MM')-1
AND I.END_OF_VALIDITY > TRUNC(&EFFECTIVE_LOAD_DATE +1,'MM')-1
WHERE
EFFECTIVE_LOAD_DATE >= ADD_MONTHS(TRUNC(&EFFECTIVE_
AND EFFECTIVE_LOAD_DATE <= NVL(
(SELECT LAST_ACCOUNTING_DAY
FROM EBH_DW_SRC.LAST_ACCOUNTING_DAY
WHERE PERIOD = TO_CHAR(TRUNC(&EFFECTIVE_LOAD_
AND START_OF_VALIDITY <= &EFFECTIVE_LOAD_DATE /*:GLOBAL.P_EFFECTIVE_LOAD_
AND END_OF_VALIDITY > &EFFECTIVE_LOAD_DATE /*:GLOBAL.P_EFFECTIVE_LOAD_
), &EFFECTIVE_LOAD_DATE /*:GLOBAL.P_EFFECTIVE_LOAD_
AND (
( /* Aktuális havi könyvelések */
AND SAP_VALUE_DATE >= ADD_MONTHS(TRUNC(&EFFECTIVE_
AND SAP_VALUE_DATE <= TRUNC(&EFFECTIVE_LOAD_DATE /*:GLOBAL.P_EFFECTIVE_LOAD_
) OR ( /* Előző hónapról átcsúszó könyvelések */
EFFECTIVE_LOAD_DATE > NVL(
(SELECT LAST_ACCOUNTING_DAY
FROM EBH_DW_SRC.LAST_ACCOUNTING_DAY
WHERE PERIOD = TO_CHAR(TRUNC(ADD_MONTHS(&
AND START_OF_VALIDITY <= &EFFECTIVE_LOAD_DATE
AND END_OF_VALIDITY > &EFFECTIVE_LOAD_DATE /*:GLOBAL.P_EFFECTIVE_LOAD_
), &EFFECTIVE_LOAD_DATE /*:GLOBAL.P_EFFECTIVE_LOAD_
AND SAP_VALUE_DATE >= ADD_MONTHS(TRUNC(&EFFECTIVE_
AND SAP_VALUE_DATE <= ADD_MONTHS(TRUNC(&EFFECTIVE_
)
)
----- Tárhely lekérdezése
SELECT tablespace_name,
SUM(max_bytes)/1024/1024 total_Mbyte,
SUM(bytes)/1024/1024 used_Mbyte
FROM USER_TS_QUOTAS
WHERE tablespace_name = 'USERS'
GROUP BY tablespace_name;
------- TÁBLA mezők megjegyzésekkel
select col.owner, col.table_name, col.column_id, col.column_name,
col.data_type, col.data_length, col.nullable, com.comments
from all_Tab_columns col, all_col_comments com
where col.owner=com.owner(+)
and col.table_name=com.table_name(
and col.column_name=com.column_
and col.table_name in ('TABLAIM')
order by col.owner, col.table_name, col.column_id
------ Tömeges mező bővítés
DECLARE
PROCEDURE add_column(
pv_table_name IN VARCHAR2,
pv_column_name IN VARCHAR2,
pv_data_type IN VARCHAR2,
pv_data_length IN VARCHAR2 DEFAULT NULL,
pv_default_value IN VARCHAR2 DEFAULT NULL
)
IS
column_exists EXCEPTION;
PRAGMA EXCEPTION_INIT(column_exists, -1430);
x VARCHAR2(512 CHAR);
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE ' || pv_table_name || ' ADD ' || pv_column_name
|| ' ' || pv_data_type || CASE pv_data_type WHEN 'VARCHAR2' THEN '(' || pv_data_length || ' CHAR)' END
|| CASE WHEN pv_default_value IS NOT NULL THEN ' DEFAULT ''' || pv_default_value || '''' END;
EXCEPTION
WHEN column_exists THEN
dbms_output.put_line(
END add_column;
BEGIN
add_column('TBL001', 'YN_TO_PURGE', 'VARCHAR2(1 CHAR)', NULL, 'N');
...
add_column('TBL100', 'YN_TO_PURGE', 'VARCHAR2(1 CHAR)', NULL, 'N');
END;
----- Saját session kilővésee
dbadm.kill_own_session
------ ODI sql kikérése
--session no: 7937
select s.sess_name, l.* from ODI_REPO.snp_sess_task_log l , ODI_REPO.snp_session s
where l.sess_no = s.sess_no
and sess_name like '%MAP_DMEXPO'
--and (task_status in ('R','E') or l.nb_row > 0)
and def_txt is not null
order by 2 desc,5 desc;
------
select
EBH_META.get_odi_task_code(
from
dual;
------
--------- ODI konstans
select def_v from ODI_REPO.snp_var v
where var_name = 'C_CONST_VARTERM'
-------- ODI tábla rövidneve
select
t.res_name,
th.full_text
from
odi_repo.snp_table t
join odi_repo.snp_model m on t.i_mod = m.i_mod
left join odi_repo.snp_txt_header th on th.i_txt = t.i_txt_desc
where
table_name = 'KL_TABLA_HOSSZU_NEVU'
and m.lagent_name = 'KL_TBL';
Megjegyzések
Megjegyzés küldése