Oracle 常用写法分享
-- 创建临时表空间create temporary tablespace test_temptempfile 'E:\app\tablespace\test_temp.dbf'size 32mautoextend onnext 32m maxsize 2048mextent management local;--创建数据表空间create tablespace testloggingdatafile 'E:\app\tablespace\test.dbf'size 32mautoextend onnext 32m maxsize 2048mextent management local;-- 创建用户create user chentao identified by chentao default tablespace test temporary tablespace test_temp;--给用户援权grant resource,connect,dba to test;--给表添加字段,可批量alter table tab_name add(col_name1 varchar2(20),col_name2 varchar2(20));-- 删除字段,可批量alter table tab_name drop column col_name1,col_name2;-- 添加字段描述comment on column tab_name.username IS '用户名';-- 更改字段默认值alter table tab_name modify username default 'chentao'-- 删除约束alter table tab_name drop constraint foreign_key_name-- 查询用户下所有表select 'Drop table '||table_name||';' from all_tables where owner='数据库用户名';select * from all_tables where table_name='sales_card_body'-- 查询某个时段的数据select * from ( select * from tbl_name as of timestamp to_timestamp('2011-7-22 13:00:00','yyyy-mm-dd hh24:mi:ss')) where head_id is not null;-- 更改某表字段类型,考虑到字段本身有数据所以采用此方法更新ALTER TABLE LOG_ACTIVITY_MAIN ADD(CARD_ID_TEMP NUMBER);alter table tab_name add(col_name_temp number);-- Add/modify columnsupdate tab_name set col_name_temp=col_name,colname=null;commit;alter table tab_name modify col_name varchar2(15);update tab_name set col_name=col_name_temp,col_name_temp=null;commit;alter table tab_name drop column col_name_temp;alter table tab_name modify col_name not null;-- 创建序列create sequence seq_tab_name_idminvalue 1maxvalue 999999999999999999999999999start with 1increment by 1cache 20;Oracle毫秒日期转换--毫秒转日期select 1197902428593/1000/60/60/24+to_date('1970-01-01 08:00:00', 'yyyy-MM-dd hh:mi:ss') from dual; --1197900835625--日期转毫秒select (to_date('2006-1-1 12:30:25', 'yyyy-mm-dd hh:mi:ss') - TO_DATE('1970-1-1', 'yyyy-mm-dd')) * 24 * 60 * 60 * 1000 resulttime from dual;-- 日期相减,减一年select sysdate - 365 from dual;-- for 的应用set serveroutput on;begindbms_output.put_line('打印倒三角形');for i in 1..9 loop for j in reverse i..9 loop --reverse对值进行从大到小的循环 dbms_output.put('*'); end loop for_j; dbms_output.new_line;--用于在缓冲区中添加换行符 end loop for_i;end;-- 判断一个表是否存在declare nums number;begin select count(1) into nums from user_tables where table_name= 'TMP_CAT'; if nums>0 then execute immediate 'drop table TMP_CAT'; end if;end;?-- 修改tablename1表中的 column1字段值为tablename2表的column1,如果column1为空,则默认值为1update tablename1 tn set tn.column1 = nvl((select max(tn2.column1) from tablename2 tn2 where tn.id = tn2.id),1)