总结一下oracle中用到的操作,语句等

近期感觉数据库用的比较多，所以总结一下：
1.table

常用sql写法:
	select * from (select ...);
	select * from A where A.a = (select ...);
	select A.a from A group by A.a having count(...)>1;
	select * from A left join B on A.a=B.a;
常用关键字: distinct;in;like;between...and;is null;is not null
case when:
	count(case when at.position is null then at.id end)   ...  case when wsflag='线上' then '1' else '0' end
常用函数:sum;count;max;to_number;sysdate;
to_char;to_date:
	to_char(a.create_at,'yyyy-MM-dd')  > '2019-01-29'  ...  to_date('2018-12-26 09:25','yyyy-MM-dd HH24:mi')
coalesce:
	coalesce(b.summary, 0.0)  ...  coalesce(c.clock_time,a.create_at)
instr:
	instr(a.name, str) > 0  (数据量大的时候)可用于替换  a.name like '%str%'
nvl:
	nvl(t.wsflag,0) = 0 可用于替换 t.wsflag is null
decode：
	decode(a1,a2,a3,a4)  --a1是条件,a2,a3是值,a4为缺省值;如果 a1 = a2,则为a3,否则为a4;计算除法时用到过
extract：
	extract(year from a.profit_date) as yy,extract(month from a.profit_date) as mm --截取年月

create table ABC_CHECK
(
  id                NUMBER(19) PRIMARY KEY,
  data_systemdate   DATE default sysdate,
  abs               VARCHAR2(255 CHAR),
  amount            FLOAT not null,
  cust_flag         VARCHAR2(8) default 0
);
comment on table ABC_CHECK is '华夏数据';
comment on column ABC_CHECK.id is '自增列';

alter table t_linker add (abc number(10) default 0);
alter table t_linker drop column abc;
alter table t_linker modify (abc varchar2(40));
alter table t_linker drop constraint uk_abc;
comment on column t_linker.abc is 'abc';--备注

insert into t_linker (a,b)  select a,b from ...;
update t_customer set (a, b) = (select a,b from ...);
delete from t_linker;
drop table t_linker;

2.procedure

create or replace procedure p_abc
as
begin
   --存储过程：从abclk库中的t_cba表中导入数据到t_abc表

   --清除旧的数据
   delete from t_abc where ...;

   --同步新的数据
   insert into t_abc 
   select * from t_cba@abclk a where to_char(a.update_at,'yyyy-MM-dd') >= to_char(sysdate-1,'yyyy-MM-dd');
   commit;
   
   --异常处理：抛出
   EXCEPTION
         WHEN OTHERS THEN 
             RAISE_APPLICATION_ERROR(-20001, '系统异常');
             Rollback;
             return;

end p_abc;

call p_abc();--见5
drop procedure p_abc;

3.view

create or replace view v_abc_list as
select * from ... where ...;

drop view v_abc_list;

4.db link

create database link abclk
  connect to username identified by password  --username用户需要授权（见8）才能创建
  using 'ip';
 
drop database link abclk;

5.job

--每天6点跑一下存储过程
declare job1 number;
begin
  sys.dbms_job.submit(job1,'p_abc;',sysdate,'TRUNC(sysdate+ 1)  +6/ (24)');
  commit;
end;
--查看所有job
select * from user_jobs;
--查看下一次执行时间
select trunc(sysdate+ 1)  +6/ (24) from dual;
--启动定时任务
begin
   dbms_job.RUN(23); /*job的id*/
end;
---暂停定时任务
begin   
  dbms_job.broken(23,true,sysdate); /*表示立即（sysdate）停止id为23的job */
end;
---删除定时任务
begin
  dbms_job.remove(23);  /*删除自动执行的job*/
end;

6.sequence

create sequence ABC_SEQ
minvalue 1
maxvalue 9999999999999999999999999999
start with 206741
increment by 1
cache 10;

drop sequence ABC_SEQ;

重新设置seq的起始值：

alter sequence ABC_SEQ increment by 700000;--现值上增加700000
select ABC_SEQ.nextval from dual;
alter sequence ABC_SEQ increment by 1;

7.index

create index idx_abc on t_abc(a);
create index idx_abc on t_abc(a,b,c);--组合索引可能影响其他索引导致性能反而下降
drop index idx_abc;
查询表索引：
select a.*, b.index_type
  from user_ind_columns a, user_indexes b
 where a.index_name = b.index_name
   and a.table_name = b.table_name
   and a.table_name = upper('basic_dept');

8.other
查询表主键和唯一键：

select a.column_name, b.table_name, b.constraint_name 
from user_cons_columns a, user_constraints b
where (b.constraint_type = 'U' or b.constraint_type = 'P')
and a.constraint_name = b.constraint_name
and a.table_name = upper('t_account');

查询表信息：

select * from all_tables a where a.TABLE_NAME like upper('%T_BEHAV%');

查询表字段：

SELECT t.table_name,
       t.colUMN_NAME,
       t1.COMMENTS,
       t.DATA_TYPE || '(' || t.DATA_LENGTH || ')'
  FROM User_Tab_Cols t, User_Col_Comments t1
WHERE t.table_name = t1.table_name
    AND t.column_name = t1.column_name and t.TABLE_NAME =upper('t_menu');

db link授权（sys用户登录）：

grant connect,resource,dba to username;