自分のテーブルデータを一括削除

とりあえず、単純なものを。メモっとかないと忘れてしまう・・・

use [DB]
go

declare cur_user_tables cursor for
select
  all_obj.name
from
  sys.all_objects as all_obj
  inner join sys.schemas s on
    s.name = 'dbo' and
    s.schema_id = all_obj.schema_id
where
  all_obj.type = 'U' and
  all_obj.is_ms_shipped = 0
;

declare @table_name varchar(max)
declare @sql varchar(max)

open cur_user_tables;

fetch next from cur_user_tables
  into @table_name;

while @@FETCH_STATUS = 0
begin
  set @sql = 'delete from ' + @table_name;
  print @sql
  execute(@sql)

  fetch next from cur_user_tables
    into @table_name;
end

close cur_user_tables;
deallocate cur_user_tables;

go

adbで一括pull

AndroidからWindowsへ、対象ファイルを一括コピーしたい時は超便利！
PowerShell使って楽してみた。
ls結果をforeachで回した時に、空文字列が交互に存在したからwhereで省いた。

adb shell "ls /mnt/sdcard/Pictures/*jpg" | ?{ $_.Length -gt 0 } | %{ adb pull $_ ("d:\tmp\" + [IO.Path]::GetFileName($_)) }

無効ボタン画像生成

アプリ作ってると画像のリソースが必要になる。ボタンだと、 enable, disable, mouse over, click などの状態別に必要・・・って結構めんどくさい。手を抜きたい。
enableを用意したら、他はPowerShell + ImageMagick で楽に作る。
色の事はよく分かんないから、輝度と彩度をいじっただけ。

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 使って 一括変換出来るって書いてあったけど、そのコードが動かないから結局ループ（；_；）

Windows 8 Release Preview に、Wiresharkをインストールしてみた。
WinPcapインストール時に、Windowsの互換性に問題あるとかで一旦失敗したけど、
「互換モード」を Windows XP SP3 に設定したらインストールできた。
（Windows 7 でも出来ると思う）

SQLでINSERT文を生成

SQLでinsert文を生成してみた！おまけでcreate文(中途半端)も。
テストデータはinsert文じゃないと嫌な人は、これに手を加えて再利用できるかも？

クエリ

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);

SQLで素因数分解

素因数分解やってみた。

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

SQLで全加算器

何か奇抜な事をしてみたくて思いついたのが、SQLで全加算器の真理値表を作る事。

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

SQLでFizzBuzz

SQLでFizzBuzzをやってみた。

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で正弦波

SQLで正弦波を描いてみました。

with
    data as (
        select
            rownum as num,
            trunc(amp * sin((rownum-1)/180 * 3.14 * freq) * 10) + 50 as pos
        from
            (select 1.5 as amp, 20 as freq from dual)
        connect by
            rownum <= 1000
    )
 
select
    lpad(' ', d.pos) || '*' || lpad(' ', d.pos) as plot
from
    data d

FC2からBloggerに移行

旧 sakuragi's blog からの完全移行がめんどい・・・