常用sql备忘

/*
存储过程 test
*/
create or replace procedure my_insert(id in NUMBER, name in VARCHAR2)
as
BEGIN
  insert into all_name values(id,name);
  commit;
END;
/

execute my_insert(100,"hello");



/*
分区表 test
http://www.2cto.com/database/201402/281155.html
*/
*/drop table partition_test;
create table partition_test 
( pk_id number(30) not null, 
insert_time DATE, constraint 
PK_PARTITION_TEST primary key (pk_id))
PARTITION BY RANGE (insert_time)
( 
PARTITION t_test_2012 VALUES LESS THAN (TO_DATE('2013-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE MY_SPACE, 
PARTITION t_test_2013 VALUES LESS THAN (TO_DATE('2014-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE MY_SPACE, 
PARTITION t_test_2014 VALUES LESS THAN (TO_DATE('2015-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE MY_SPACE);

/*查看有哪些分区*/
select table_name,partition_name,subpartition_name,TABLESPACE_NAME 
from dba_tab_subpartitions 
where table_name='PARTITION_TEST'
order by table_name, partition_name;

/*查看是否分区*/
select * from dba_tables 
where table_name='PARTITION_TEST';

select * from partition_test t;
insert into partition_test values(1,TO_DATE('2012-01-01 11:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into partition_test values(2,TO_DATE('2013-01-01 11:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into partition_test values(3,TO_DATE('2014-01-01 11:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into partition_test values(4,TO_DATE('2015-01-01 11:00:00','yyyy-mm-dd hh24:mi:ss'));