Oracle——问题小书

Rehoni / 2021-04-22

问题1、日期格式重复

ORA-01810 format code appears twice

报次错误的原因很简单，原因是重复了

比如：日期格式：

正确：TO_DATE(‘2012-07-03 13:04:58’, ‘yyyy-MM-dd hh24:mi:ss’)

错误：TO_DATE(‘2012-07-03 13:04:58’, ‘yyyy-MM-dd hh24:mi:dd’) dd是重复的

错误：TO_DATE(‘2012-07-03 13:04:58’, ‘yyyy-MM-dd hh24:mm:ss’) mm是重复的

错误：TO_DATE(‘2012-07-03 13:04:58’, ‘yyyy-MM-dd hh24:mm:dd’) dd，mm是重复的

问题2、ORA-01000: 超出打开游标的最大数

一般是Java代码在执行conn.createStatement()和 conn.prepareStatement()的时候，实际上都是相当与在数据库中打开了一个cursor。尤其是，如果你的 createStatement和prepareStatement是在一个循环里面的话，就会非常容易出现这个问题。因为游标一直在不停的打开，而且没有关闭。

问题3、Oracle取两列中值最大和最小的列

Greatest和least两个函数；或者用case when

-- a，b，c为table中的三列
select (case when a>b then a else b end),(case when b>c then b else c end) from table;

问题4、Oracle的update set必须是子查询

ORA-01767: UPDATE ...SET 表达式必须是子查询

问题5、Oracle查找和删除约束

select * from user_constraints; -- 在sys账户下查找
alter table standard.EMS_RATIO drop constraint SYS_C0036049; -- 注意带standard模式名

问题6、in改为exist提高效率

# 当in的数量很大的时候，使用in的效率会很慢
where tb.fl_type = 3 and tb.classify_id in (246,7943,6669);
# 改成exist提升效率
where tb.fl_type = 3
and exists(
select *
        from (select 246 as id
              from dual
              union
              select 7943
              from dual
              union
              select 6669
              from dual
              ) temp
        where temp.id = tb.classify_id
        );

问题7、Oracle的merge into 和update set的写法

merge into table_target a
using table_source t
on (a.key1 = t.key1) # 可以多字段匹配
when matched then
	update
	set a.col1 = t.col1,a.col2 = t.col2 # 需要更新的行
when not matched then
	insert (a.col1,a.col2) values (b.col1,b.col2); # 全插入
#merge into写法
merge into IMP_CABLE_SHEATH_CIRCULATION_LATEST a
using (select *
       from (
                select row_number() over (partition by URI order by MONITOR_TIME desc) as rn, a.* from IMP_CABLE_SHEATH_CIRCULATION a)
       where rn = 1) t
on (a.URI = t.URI)
when matched then
    update
    set a.NAME                = t.NAME,
        a.REGION              = t.REGION,
        a.VOLTAGELEVEL        = t.VOLTAGELEVEL,
        a.SUBSTATION          = t.SUBSTATION,
        a.LOCALNAME           = t.LOCALNAME,
        a.PHASE               = t.PHASE,
        a.COMMUNICATIONMODE   = t.COMMUNICATIONMODE,
        a.OPERATIONSTATE      = t.OPERATIONSTATE,
        a.MONITORINGTYPEURI   = t.MONITORINGTYPEURI,
        a.MONITORINGTYPENAME  = t.MONITORINGTYPENAME,
        a.PSRURI              = t.PSRURI,
        a.PSRTYPE             = t.PSRTYPE,
        a.CONTAINEDGRDIPHASES = t.CONTAINEDGRDIPHASES,
        a.CONTAINEDIPHASES    = t.CONTAINEDIPHASES,
        a.JUNCTION= t.JUNCTION,
        a.IA= t.IA,
        a.IB= t.IB,
        a.IC= t.IC,
        a.GRDIA= t.GRDIA,
        a.GRDIB= t.GRDIB,
        a.GRDIC= t.GRDIC,
        a.GRDIN               = t.GRDIN,
        a.IA_TIME             = t.IA_TIME,
        a.IB_TIME= t.IB_TIME,
        a.IC_TIME= t.IC_TIME,
        a.GRDIA_TIME= t.GRDIA_TIME,
        a.GRDIB_TIME= t.GRDIB_TIME,
        a.GRDIC_TIME= t.GRDIC_TIME,
        a.GRDIN_TIME= t.GRDIN_TIME,
        a.MONITOR_TIME        = t.MONITOR_TIME
when not matched then
    INSERT (a.URI, a.NAME, a.REGION, a.VOLTAGELEVEL, a.SUBSTATION, a.LOCALNAME, a.PHASE, a.COMMUNICATIONMODE, a.OPERATIONSTATE,
            a.MONITORINGTYPEURI, a.MONITORINGTYPENAME, a.PSRURI, a.PSRTYPE, a.CONTAINEDGRDIPHASES, a.CONTAINEDIPHASES, a.JUNCTION, a.IA,
            a.IB, a.IC, a.GRDIA, a.GRDIB, a.GRDIC, a.GRDIN, a.IA_TIME, a.IB_TIME, a.IC_TIME, a.GRDIA_TIME, a.GRDIB_TIME, a.GRDIC_TIME,
            a.GRDIN_TIME, a.MONITOR_TIME)
    VALUES (t.URI, t.NAME, t.REGION, t.VOLTAGELEVEL, t.SUBSTATION, t.LOCALNAME, t.PHASE, t.COMMUNICATIONMODE, t.OPERATIONSTATE,
            t.MONITORINGTYPEURI, t.MONITORINGTYPENAME, t.PSRURI, t.PSRTYPE, t.CONTAINEDGRDIPHASES, t.CONTAINEDIPHASES, t.JUNCTION, t.IA,
            t.IB, t.IC, t.GRDIA, t.GRDIB, t.GRDIC, t.GRDIN, t.IA_TIME, t.IB_TIME, t.IC_TIME, t.GRDIA_TIME, t.GRDIB_TIME, t.GRDIC_TIME,
            t.GRDIN_TIME, t.MONITOR_TIME);

问题7、Oracle按月自动创建分区

具体描述

建分区表自动按月分区

解决方案和原理

 -- 自动按月分区
 create table EMS_RATIO
 (
     FUNCTION_ID VARCHAR2(64),
     FUNCTION_NAME VARCHAR2(256),
     FUNCTION_TYPE NUMBER(4),
     RESOLVE_TIME TIMESTAMP(0),
     MEASURE_TIME TIMESTAMP(0) not null,
     VOLTAGE VARCHAR2(32),
     START_STATION VARCHAR2(64),
     END_STATION VARCHAR2(64),
     RATED_CAPACITY FLOAT(24),
     LIMIT_I FLOAT(24),
     I FLOAT(24),
     P FLOAT(24),
     Q FLOAT(24),
     LOAD FLOAT(24),
     LOAD_TYPE NUMBER(4),
     ID VARCHAR2(32) not null,
     NAME VARCHAR2(64),
     constraint EMS_RATIO_PK
         primary key (ID, MEASURE_TIME)
 ) # 创建表结束
 tablespace EMS_RATIO_TB
 partition by RANGE (MEASURE_TIME) interval (numtoyminterval(1,'month'))
 store in (EMS_RATIO_TB)(
     partition ems_ratio_partition values less than (to_date('2020-06-01','yyyy-MM-dd'))
 )