Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
code1
declare v_c numeric(5); v_tab varchar; v_cn varchar; v_cl varchar; v_col varchar; v_ind varchar; v_s varchar; cursor c_ind is select distinct cc.table_name, cc.constraint_name from (select table_name, constraint_name from (select b.table_name, b.constraint_name, max(CASE position WHEN 1 THEN column_name ELSE Null END) cnamel, max(CASE position WHEN 2 THEN column_name ELSE Null END) cname2, max(CASE position WHEN 3 THEN column_name ELSE Null END) cname3, max(CASE position WHEN 4 THEN column_name ELSE Null END) cname4, max(CASE position WHEN 5 THEN column_name ELSE Null END) cname5, max(CASE position WHEN 6 THEN column_name ELSE Null END) cname6, max(CASE position WHEN 7 THEN column_name ELSE Null END) cname7, max(CASE position WHEN 8 THEN column_name ELSE Null END) cname8, count(*) col_cnt from (select table_name, constraint_name, column_name, position from user_cons_columns) a, user_constraints b where a.constraint_name = b.constraint_name and b.constraint_type = 'R' group by b.table_name, b.constraint_name ) cons where col_cnt > ALL (select count(*) from user_ind_columns i where i.table_name = cons.table_name and i.column_name in (cnamel, cname2, cname3, cname4, cname5, cname6, cname7, cname8) and i.column_position <= cons.col_cnt group by i.index_name )) cc; cursor c_col is select column_name from USER_CONS_COLUMNS where table_name = v_tab and constraint_name = v_cn order by position; begin select max(to_number(substr(index_name,8,8))) into v_c from user_ind_columns uc where upper(uc.index_name) like 'IDX_FK_0%'; v_c := nvl(v_c,0); open c_ind; loop fetch c_ind into v_tab, v_cn; exit when c_ind%notfound; v_c := v_c + 1; v_col := ''; v_s := ''; open c_col; loop fetch c_col into v_cl; exit when c_col%notfound; v_col := v_col||v_s||v_cl; v_s := ','; end loop; close c_col; v_ind := 'create index IDX_FK_'||lpad(to_char(v_c),8,'0')||' on '||v_tab||'('||v_col||') TABLESPACE SUFD_INDX_TS'; execute immediate v_ind; end loop; close c_ind; end;
run
|
edit
|
history
|
help
0
1
nested array to table
funkcia_transakcie
test jsonb_set of PostgreSQL
Pedido
enrj
pgsql trigger 01 - Ovocie
Query workaround in order to avoid IN Operator in WHERE Clause
E-Commerce SQL Analysis by Ran Li
FInale 38474297439