oracleSQl备忘

1、日期
//若planTime不为空则查询planTime的值，否则日期为realTime前3个月零7天
nvl(planTime,add_months(realTime,-3)+7)
To_Date('2010-12-23 00:40:58','yyyy-MM-dd HH24:mi:ss')
2、判断
//若type的值为'591994'则查询结果为time1的值，如果为'591995'则查询结果为time2的值
DECODE (type,'591994', time1,
'591995', time2)
3、oracle取整函数
　
1.取整(大) 　 select ceil(-1.001) value from dual 　　

2.取整（小） 　　select floor(-1.001) value from dual 　　

3.取整（截取）　 select trunc(-1.002) value from dual

4.取整(舍入) select round(-1.001) value from dual
4、Oracle 导入命令
imp user/pw@database file=expData.dmp log=2010-10-19.log commit=y full=y grants=y ignore=y statistics=none
5、Oracle导出命令
exp username/psw@host/severname file=D:\exp.dmp owner=username compress=y
6、创建表空间

create tablespace BILLTABLESPACE
datafile 'D:\DevTool\Oracle\oradata\orcl\BILLTABLESPACE.DBF' size 200M autoextend on next 50M
blocksize 8k
PERMANENT
7、创建用户
-- USER SQL
CREATE USER bill IDENTIFIED BY bill
DEFAULT TABLESPACE "BILLTABLESPACE"
TEMPORARY TABLESPACE "TEMP";

-- ROLES
GRANT "CONNECT" TO bill WITH ADMIN OPTION;
GRANT "DBA" TO bill WITH ADMIN OPTION;
ALTER USER bill DEFAULT ROLE "CONNECT","DBA";