Oracle笔记

统计求和：

1. 分组求和：

   sum(字段) over(patition by 字段)

2. 连续求和：

   sum(字段) over(order by 字段)

3. 分组排序编号：

   row_number(字段) over(partition by 字段, order by 字段)

4. 分组合计：

   group by ROLLUP(字段)

递归查询：

--root==》leaf
select * from table
where a='1'
start with b='1'
connect by prior id=pid
 
--leaf==》root
select * from table
where a='1'
start with b='1'
connect by prior pid=id

有条件插入语句：

INSERT all
    WHEN 'a' is not null
    THEN INTO WRMS.KH_MX_SHWR (A, B, C) VALUES ('a', 'b', 'c')
select * from dual

导入导出：

exp username/password@host:port/sid file=d:daochu.dmp full=y
imp username/password@host:port/sid file=d:daochu.dmp full=y

yyyy-mm-dd转yyyy年mm月dd日：

SELECT TO_CHAR(SYSDATE, 'YYYY"年"MM"月"DD"日"') A FROM dual
SELECT 
    TO_CHAR(SYSDATE, 'YYYY') || '年' || 
    TO_CHAR(SYSDATE, 'mm') || '月' ||
    TO_CHAR(SYSDATE, 'dd') || '日' 
    A FROM dual

获取年份段和月份段：

SELECT 
  TO_CHAR(add_months(to_date('2015', 'yyyy'), -(ROWNUM - 1)*12), 'yyyy') YEAR
FROM dual
  CONNECT BY ROWNUM <= 5

SELECT 
  TO_CHAR(add_months(to_date('2015-12-01', 'yyyy-mm-dd'), -(ROWNUM - 1)), 'yyyy-mm-dd') dt
FROM dual
  CONNECT BY ROWNUM <= 5

dblink同步：

--创建目标库dblink
create database link 目标库
connect to 账号 identified by "密码"
using 'ip地址:端口/目标库'; 
 
--查询创建的dblink
select owner,object_name from dba_objects where object_type='DATABASE LINK';
 
--验证dblink
select * from  T_EXCH_SEND_WR_INT_B@目标库;
 
同步数据
merge INTO 目标库中的表@目标库b USING 源库表 a ON (b.id=a.id)
when matched then 
update set b.name=a.name, ...
when not matched then 
insert values (a.name, ...)
WHERE a.xx= 'xx';
  
提交
commit;

注：如果想在value里通过子查询插入值，在10g里可以，但是11g只能通过using来实现。

timestamp转date：

--第一种
select dt+0 from table
 
--第二种
select cast(dt as date) from table

创建新用户及其操作：

#以linux环境为例
 
#切换到oracle用户
su - oracle
 
#进入以管理员身份进入sqlplus
sqlplus / as sysdba
 
#创建用户
create user 用户名 identified by 密码;
  
#授权
grant connect, resource,dba to 用户名;
  
#创建表空间
create tablespace 表空间名称
datafile '表空间名称.dbf'   
size 1000M
autoextend on next 5M maxsize 20480M;  
extent management local
 
#导入
imp 用户名/密码@主机地址:端口实例名称 file=数据库文件.dmp full=y
 
 
#注意点：
#1.更改表的表空间 alter table XXX move tablespace XXX
#2.如果以管理员身份进入sqlplus
#遇到 idle instance，则需要startup命令，
#startup遇到could not open ...initXXX.ora，则需要拷贝 
cp $ORACLE_BASE/admin/$ORACLE_SID/pfile/init.ora.xxx $ORACLE_BOME/dbs/init实例名.ora