ls |
%{
$name = [IO.Path]::GetFileNameWithoutExtension($_.Name)
$ext = [IO.Path]::GetExtension($_.Name)
$disable_name = $name + "_disable" + $ext
$over_name = $name + "_over" + $ext
convert $_.Name -modulate "100,0,100" $disable_name
convert $_.Name -modulate "150,100,100" $over_name
}
どっかで mogrify 使って 一括変換出来るって書いてあったけど、そのコードが動かないから結局ループ（；_；）
with
param as (
select
'SAKURAGI' as owner,
'EMP' as table_name
from
dual
),
seq as (
select
rownum as value
from
dual
connect by
rownum <= 5
),
data as (
select
s.value,
tc.table_name,
tc.column_id,
tc.column_name,
tc.data_type,
tc.nullable,
case
when tc.data_type = any('CHAR', 'VARCHAR2') then
tc.data_type || '(' || tc.char_length || ')'
when tc.data_type = 'NUMBER' then
tc.data_type || '(' || tc.data_length || ',' || nvl(tc.data_scale, 0) || ')'
else
tc.data_type
end as data_type_text,
case
when tc.data_type = any('CHAR', 'VARCHAR2') then
lpad(s.value, tc.char_length, '0')
when tc.data_type = 'DATE' then
'to_date(''' || to_char(sysdate+s.value, 'yyyy-mm-dd HH24:mi:ss') || ''', ''yyyy-mm-dd HH24:mi:ss'')'
else
rtrim(to_char(s.value) || '.' || lpad(s.value, tc.data_scale, '0'), '.')
end as sample_value
from
all_tab_cols tc,
seq s,
param
where
tc.owner = param.owner and
tc.table_name = param.table_name
order by
tc.column_id
),
formatted_data as (
select
d.*,
case
when d.data_type = any('CHAR', 'VARCHAR2') then
'''' || d.sample_value || ''''
else
d.sample_value
end as formatted_value
from
data d
order by
d.column_id
),
idx_info as (
select
col.column_id,
col.table_name,
col.column_name,
ind_col.index_name,
cons.constraint_name,
cons.constraint_type
from
user_tab_cols col
inner join user_ind_columns ind_col on
ind_col.table_name = col.table_name and
ind_col.column_name = col.column_name
inner join user_constraints cons on
cons.table_name = ind_col.table_name and
cons.index_name = ind_col.index_name,
param
where
col.table_name = param.table_name
),
create_parts as (
select
d.table_name,
listagg(
d.column_name || ' ' ||
d.data_type_text ||
(case when d.nullable = 'N' then ' not null' end),
', '
) within group (order by d.column_id) as column_list
from
data d
where
d.value = 1
group by
d.table_name
),
insert_parts as (
select
d.value,
d.table_name,
----- case 1 -----
--rtrim(regexp_replace(xmlagg(xmlelement(TAG,d.column_name||',') order by d.column_id), '', ''), ',') as column_list,
--replace(rtrim(regexp_replace(xmlagg(xmlelement(TAG,d.formatted_value||',') order by d.column_id), '', ''), ','), chr(38)||'apos;', '''') as value_list
----- case 2 -----
listagg(d.column_name, ', ') within group (order by d.column_id) as column_list,
listagg(d.formatted_value, ', ') within group (order by d.column_id) as value_list
from
formatted_data d
group by
d.value,
d.table_name
)
select
'create table ' || p.table_name || ' (' || p.column_list ||
(case when idx.column_list is not null then (', primary key(' || idx.column_list || ')') end) || ');' as query
from
create_parts p
left outer join (
select
idx.table_name,
listagg(idx.column_name, ', ') within group (order by idx.column_id) as column_list
from
idx_info idx
group by
idx.table_name
) idx on idx.table_name = p.table_name
union all
select
'insert into ' || p.table_name || ' (' || p.column_list || ')' || ' values (' || p.value_list || ');' as query
from
insert_parts p
;
create table EMP (EMPNO NUMBER(22,0) not null, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(22,0), HIREDATE DATE, SAL NUMBER(22,2), COMM NUMBER(22,2), DEPTNO NUMBER(22,0), primary key(EMPNO));
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (1, '0000000001', '000000001', 1, to_date('2012-08-26 16:07:22', 'yyyy-mm-dd HH24:mi:ss'), 1.01, 1.01, 1);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (2, '0000000002', '000000002', 2, to_date('2012-08-27 16:07:22', 'yyyy-mm-dd HH24:mi:ss'), 2.02, 2.02, 2);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (3, '0000000003', '000000003', 3, to_date('2012-08-28 16:07:22', 'yyyy-mm-dd HH24:mi:ss'), 3.03, 3.03, 3);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (4, '0000000004', '000000004', 4, to_date('2012-08-29 16:07:22', 'yyyy-mm-dd HH24:mi:ss'), 4.04, 4.04, 4);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (5, '0000000005', '000000005', 5, to_date('2012-08-30 16:07:22', 'yyyy-mm-dd HH24:mi:ss'), 5.05, 5.05, 5);
with
target as (
select
4294967295 value,
2 as prime
from
dual
),
prime_factorization (value, prime, success) as (
select
value,
prime,
null
from
target
union all
select
case
when mod(value, prime) = 0 then value/prime
else value
end,
case
when mod(value, prime) = 0 then prime
else prime + 1
end,
case
when mod(value, prime) = 0 then 1
else 0
end
from
prime_factorization
where
value > 1 or (value = prime)
)
select
((select value from target) ||
' = ' ||
listagg(prime, ' * ') within group (order by prime)
) as result
from
prime_factorization
where
success = 1
group by
null
with
data (input1, input2) as (
select 0, 0 from dual union all
select 0, 1 from dual union all
select 1, 0 from dual union all
select 1, 1 from dual
),
data2 as (
select
utl_raw.cast_from_binary_integer(input1) as input1,
utl_raw.cast_from_binary_integer(input2) as input2
from
data
),
-- 半加算器
half_adder as (
select
input1,
input2,
utl_raw.bit_or(
utl_raw.bit_and(
input1,
utl_raw.bit_complement(input2)
),
utl_raw.bit_and(
utl_raw.bit_complement(input1),
input2
)
) as output_sum,
utl_raw.bit_and(input1, input2) as output_carry
from
data2
),
-- 全加算器
full_adder as (
select
ha1.input1 as a,
ha1.input2 as b,
ha2.input2 as x,
utl_raw.bit_or(
ha1.output_carry,
ha2.output_carry
) as c,
ha2.output_sum as s
from
half_adder ha1
inner join half_adder ha2 on
ha1.output_sum = ha2.input1
)
select
to_number(fa.a) as a,
to_number(fa.b) as b,
to_number(fa.x) as x,
to_number(fa.c) as c,
to_number(fa.s) as s
from
full_adder fa
order by
fa.a || fa.b || fa.x
select
rownum as num,
nvl(
case when mod(rownum, 3) = 0 then 'Fizz' end ||
case when mod(rownum, 5) = 0 then 'Buzz' end,
rownum
) as val
from
dual
connect by
rownum <= 100
SQL でビットマップ画像の2値化は4年位前に挑戦した。 最近、それの Impala 版 を作ったときに閃いた。 「再帰CTEがあるなら、mp4 もいけるんじゃないか」と。 やってみた。 use ragingo drop table video go create...
