Oracle SQL haladó minták

– ORACLE tömeges tábla kiajánlás:

— előzetes lekérdetés
select
‘GRANT SELECT ON klajosw.’||table_name||’ TO vendeg, uj_hasznalo GRANT OPTION;’
from user_tables
;
=>
set serveroutput on
spool ..\result\&1
—
GRANT SELECT ON klajosw.tbl_elso TO vendeg, uj_hasznalo WITH GRANT OPTION;
…
spool off
exit
–//Eredmény sql filebe irányitása és megfuttatás.

———————

– ORACLE saját tábla használó/elérő userek listázása:

select * from USER_TAB_PRIVS where owner=’KECSKEMETIL’;
=>
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY
vendeg klajosw INPUT_V1 klajosw DEBUG NO NO
———————-
/*=======================
Adatbázis linkek eldobása/törlése
=======================*/
declare v_script varchar2(32767);
begin
for i in(select db_link
from user_db_links
)
loop
v_script := ‘drop database link ‘||i.db_link;
execute immediate v_script;
end loop;
end
;
/
disconnect
;
exit
————————-

/*========================================================
Komlex minta
========================================================*/
drop table tt_0002743_sp purge
;
drop sequence tt_0002743_sp_seq
;
create table tt_0002743_sp
(tt_0002743_sp_id number(14)
,div2_termek_rk varchar2(30)
,div2_termek_id varchar2(30)
,div2_termek_nm varchar2(255)
,crt_usr varchar2(30)
,crt_dt date
,lstmd_usr varchar2(30)
,lstmd_dt date
) compress
;
create unique index indx_tt_0002743_sp_t_id
on tt_0002743_sp(div2_termek_id asc
)
;
grant all on tt_0002743_sp to public
;
create sequence tt_0002743_sp_seq
start with 1 –select nvl(max(tt_0002743_sp_id),1) from tt_0002743_sp
increment by 1
nocache
nocycle
;
— drop trigger t_0002743_sp_chngs
create or replace trigger t_0002743_sp_chngs
before insert or update
on mkr_0002743_sp
for each row
declare
begin
:new.lstmd_dt := sysdate;
:new.lstmd_usr := user;
if :old.crt_dt is null
then :new.crt_dt := sysdate;
:new.crt_usr := user;
end if;
end;
/
exit


Szöveg maniluláció:

select 'a' as TIP, SUBSTR('KL_12345678901',4,100) as aa from dual

union all

select 'b' as TIP, SUBSTR('BR_12345678902',LENGTH('BR_12345678902')-10,11) as aa from dual

union all

select 'c' as TIP, REPLACE( 'KL_12345678903','KL_') as aa from dual

union all

select 'd' as TIP, REGEXP_SUBSTR('KL_12345678904', '(\d){1,}' )as aa from dual

union all

select 'e' as TIP, REGEXP_REPLACE('KL_12345678905','\D', '') as aa from dual

union all

select 'f' as TIP, TRANSLATE('KL_12345678906', '1234567890ABCKL_', '1234567890') as aa from dual

union all

select 'h' as TIP, LTRIM('KL_12345678907','ABCKL_')as aa from dual

=>

TIP         AA

a             12345678901

b             12345678902

c              12345678903

d             12345678904

e             12345678905

f              12345678906

h             12345678907

select c.* from (

select b.*,

  RANK() OVER ( ORDER BY az_nev_az, az_cim_az, az_szul_hely_az ) AS MIN_1_az

, (row_number() OVER ( partition by az_nev_az, az_cim_az, az_szul_hely_az order by az_nev_az, az_cim_az, az_szul_hely_az)) AS MIN_01_az

, RANK() OVER ( ORDER BY az_szsz_az, az_ado_az ) AS MIN_2_az

, (row_number() OVER ( partition by az_szsz_az, az_ado_az order by az_szsz_az, az_ado_az)) AS MIN_02_az

from(

select uf.*

, RANK() OVER ( ORDER BY nev) AS az_nev_az

, RANK() OVER ( ORDER BY cime) AS az_cim_az

--, DENSE_RANK() OVER ( ORDER BY cime) AS az_cim_az2

, RANK() OVER ( ORDER BY szul_hely) AS az_szul_hely_az

, RANK() OVER ( ORDER BY szil_ido) AS az_szul_ido_az

, RANK() OVER ( ORDER BY szsz) AS az_szsz_az

, RANK() OVER ( ORDER BY ado) AS az_ado_az

from ugyfel uf

) b ) c

-- where c.MIN_02_az > 1

CREATE GLOBAL TEMPORARY TABLE today_sales(order_id NUMBER) ON COMMIT PRESERVE ROWS;

CREATE GLOBAL TEMPORARY TABLE Table_name ()  ON COMMIT DELETE ROWS;

----

Global Temporary Table transaction-specific

CREATE GLOBAL TEMPORARY TABLE table_name

( column_name column_data_type

  ...

    ...

) ON COMMIT DELETE ROWS;

Global Temporary Table session-specific

CREATE GLOBAL TEMPORARY TABLE table_name

( column_name column_data_type

  ...

    ...

) ON COMMIT PRESERVE ROWS;

----

CREATE GLOBAL TEMPORARY TABLE temp_users (

   id number(5)  NOT NULL,

   name varchar2(50)  NOT NULL,

   surname varchar2(50)  NOT NULL ) ON COMMIT DELETE ROWS

INSERT INTO temp_users VALUES (1, 'John', 'Smith');

INSERT INTO temp_users VALUES (2, 'Anne', 'Parker');

INSERT INTO temp_users VALUES (3, 'Kate', 'Doe');

SELECT COUNT(*) FROM temp_users;

-- RESULT: 3

COMMIT;

SELECT COUNT(*) FROM temp_users;

-- RESULT: 0

----

create global temporary table gtt_dept

on commit preserve rows                       --> first

as

select * from dept;  

---

select * from (

select

 itemid,

itemtype,

ctime,

mtime,

l_owner,

l_date,

XML

--, utl_raw.cast_to_varchar2(dbms_lob.substr(xml))

--, UTL_RAW.CAST_TO_VARCHAR2( xml )

-- UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(xml, 3200,1))

--,substr(XMLTYPE (UTL_RAW.cast_to_varchar2 (xml)).EXTRACT('xml_tag/xml_tag/../text()'),200,200) as "XML DATA"

--,utl_raw.cast_to_varchar2( dbms_lob.substr( xml, 32000, 1 ) )

from ebh_ele.ele_eis_log_nv a

) a,

   xmltable('/EVENTS/EVENT' passing xmltype(a.xml, nls_charset_id('UTF8'))

        COLUMNS

        dt varchar(20) path 'DATETIME', userid varchar(20) path 'USERID',

        code varchar(20) path 'CODE', args varchar(200) path 'ARGS') b

where a.itemtype = 'EVENTS'

    and code in ('LGS_RLGINIP', 'LGS_RLGIN')

    and l_owner like '______'