Ugrás a fő tartalomra

Hasznos SQL parancsok (oracle)

 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,
                                 account_id,
                                 rowid
                            from kl.kl_tbl t
                           where t.run_date = DATE '2017-05-12')
                  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_LOAD_DATE /*:GLOBAL.P_EFFECTIVE_LOAD_DATE*/+1,'MM'),-2)
  AND EFFECTIVE_LOAD_DATE <= NVL(
      (SELECT LAST_ACCOUNTING_DAY
         FROM EBH_DW_SRC.LAST_ACCOUNTING_DAY
        WHERE PERIOD = TO_CHAR(TRUNC(&EFFECTIVE_LOAD_DATE /*:GLOBAL.P_EFFECTIVE_LOAD_DATE*/+1,'MM')-1,'YYYYMM') -- Előzőt megelőző hónap
          AND START_OF_VALIDITY <= &EFFECTIVE_LOAD_DATE /*:GLOBAL.P_EFFECTIVE_LOAD_DATE*/
          AND END_OF_VALIDITY > &EFFECTIVE_LOAD_DATE /*:GLOBAL.P_EFFECTIVE_LOAD_DATE*/
      ), &EFFECTIVE_LOAD_DATE /*:GLOBAL.P_EFFECTIVE_LOAD_DATE*/)
  AND (
    ( /* Aktuális havi könyvelések */
      AND SAP_VALUE_DATE >= ADD_MONTHS(TRUNC(&EFFECTIVE_LOAD_DATE /*:GLOBAL.P_EFFECTIVE_LOAD_DATE*/+1,'MM'),-1) -- Előző hónap kezdete
      AND SAP_VALUE_DATE <= TRUNC(&EFFECTIVE_LOAD_DATE /*:GLOBAL.P_EFFECTIVE_LOAD_DATE*/+1,'MM')-1              -- Előző hónap vége
    ) 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(&EFFECTIVE_LOAD_DATE,-1) +1,'MM')-1,'YYYYMM') -- Előzőt megelőző hónap
            AND START_OF_VALIDITY <= &EFFECTIVE_LOAD_DATE
            AND END_OF_VALIDITY > &EFFECTIVE_LOAD_DATE /*:GLOBAL.P_EFFECTIVE_LOAD_DATE*/
        ), &EFFECTIVE_LOAD_DATE /*:GLOBAL.P_EFFECTIVE_LOAD_DATE*/)
      AND SAP_VALUE_DATE >= ADD_MONTHS(TRUNC(&EFFECTIVE_LOAD_DATE +1,'MM'),-2)     -- Előző hónapot megelőző hónap kezdete
      AND SAP_VALUE_DATE <= ADD_MONTHS(TRUNC(&EFFECTIVE_LOAD_DATE +1,'MM'),-1)-1   -- Előző hónapot megelőző hónap vége
    )
  )

-----  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_name(+)
  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('[INFO:] ' || pv_table_name || '.' || pv_column_name || ' már létezik.');
   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(7937)
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