ORACLE SQL 语句

1. 字符串拼接， 用于组装SQL语句，批量update 数据，更新表结构， 双引号 ''的为查询内容 ,|| 为动态参数和动态语句，实际查询语句忽略''即可

select  'alter table'  || table_name from user_tab_columns where column_name = upper('MOLD_NO') || 'modify MOLD_NO varchar(255)';

select 'update abc set name='|| CS.SECTION_NAME||''  from comm_section cs ;

select 'update MF_TOOLING_RECORD_INFO m set m.BEGIN_TIME = ' || to_char(a.ci, 'yyyy/MM/dd hh24:mi:ss') || ', m.COMPLETE_TIME =' ||to_char(a.di, 'yyyy/MM/dd hh24:mi:ss') || ' where  m.INFO_ID = '  ||a.INFO_ID  from  
(select MTRI.INFO_ID as  INFO_ID, MTR.TOOLING_RECORD_ID as bi , MTR.BEGIN_TIME ci, MTR.COMPLETE_TIME di from MF_TOOLING_RECORD_INFO mtri , MF_TOOLING_RECORD mtr where mtri.TOOLING_RECORD_ID = MTR.TOOLING_RECORD_ID
) a 

 select  ' update WHMS_MATERIAL_ENTITY wme  set wme.STATUS = ' ||  ta.status2  || ' where wme.WHMS_MATERIAL_ENTITY_ID =  ' ||    ta.toolEntityId || ';'  from 
    (select WMEI.TOOL_ENTITY_ID toolEntityId, WMEI.BACK_STATUS2 status2 from whms_material_entity_inout wmei where wmei.entity_inout_id in (select max(t.entity_inout_id)
 from whms_material_entity_inout t where t.back_status >=300 and T.BACK_STATUS2 is not null group by t.tool_entity_id) 
 ) ta

2. 字符串  substr ，length ，instr ，Replace 用法

update PM_MOLD_INFO b set B.YEAR_QUARTER =  Replace(B.YEAR_QUARTER,'q','Q') where instr( B.YEAR_QUARTER,'q')>0 ;

update BOM_PARTS b set B.STOCK_SIZE = 'Φ'||substr(B.STOCK_SIZE , 2, length(B.STOCK_SIZE) ) where instr( B.STOCK_SIZE,'?')>0 ;

3. --  Oracle中两个Date相减，返回值单位为天
SELECT ROUND((sysdate- S.REAL_START)*24*3600,0) ,sysdate, S.REAL_START FROM SCH_SCHEDULE s WHERE s.SECTION_ID  = '9' and S.SCH_ID =397205  // sysdate 数据库服务器的时间
 -- 如果想要秒可以：(date1-date2)*24*3600，时分同理

4. ORACLE 创建索引

CREATE INDEX PM_MOLD_INFO_IDX_4 ON PM_MOLD_INFO
(MOLD_SORT)
LOGGING
STORAGE    (
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

5. 批量插入

--1. 拷贝线径为0.25的数据为 0.2
Insert into WE_TECHNICS_INFO w1
   (WE_TECHNICS_INFO_ID, WE_CUT_MODE_ID, CUT_NUM, WE_TECHNICS_CD_ID, WE_TECHNICS_MATERIAL_ID, WE_TECHNICS_LINE_ID, WE_TECHNICS_GEOMETRY_ID, WE_TECHNICS_THICKNESS_ID, MACHINE_TYPE_ID)
select we_seq.NEXTVAL,   WE_CUT_MODE_ID, CUT_NUM, WE_TECHNICS_CD_ID, WE_TECHNICS_MATERIAL_ID, 1, WE_TECHNICS_GEOMETRY_ID, WE_TECHNICS_THICKNESS_ID, MACHINE_TYPE_ID
from WE_TECHNICS_INFO w2 where W2.WE_TECHNICS_LINE_ID = 2   ;

--2. 处理新增加的WE_TECHNICS_INFO 下的 WE_TECHNICS_DETAIL
Insert into WE_TECHNICS_DETAIL w1 (WE_TECHNICS_DETAIL_ID, WE_TECHNICS_INFO_ID, CUT_NO, E_NUM, OFFSET)
select we_seq.NEXTVAL, B.WE_TECHNICS_INFO_ID  , c.CUT_NO,   c. E_NUM, c.OFFSET
from  WE_TECHNICS_INFO a, WE_TECHNICS_INFO b ,WE_TECHNICS_DETAIL c
where a.WE_CUT_MODE_ID = b.WE_CUT_MODE_ID
and  a.CUT_NUM = b.CUT_NUM
and a.WE_TECHNICS_CD_ID = b.WE_TECHNICS_CD_ID
and a.WE_TECHNICS_MATERIAL_ID = b.WE_TECHNICS_MATERIAL_ID
and a.WE_TECHNICS_GEOMETRY_ID = b.WE_TECHNICS_GEOMETRY_ID
and a.WE_TECHNICS_THICKNESS_ID = b.WE_TECHNICS_THICKNESS_ID
and a.MACHINE_TYPE_ID = b.MACHINE_TYPE_ID
and A.WE_TECHNICS_LINE_ID = 2 and b.WE_TECHNICS_LINE_ID=1
and A.WE_TECHNICS_INFO_ID = C.WE_TECHNICS_INFO_ID;

-- 1. 先设置数据，多表之间的关系确定好，然后取数据从多表里面取
SELECT we_seq.NEXTVAL FROM DUAL; --169660
利用唯一键找出对应的ID

6. 批量更新SQL

1>  字符串拼接，获取update 脚本

2> merge

merge into BI_SCHT_COST bsc using (
select SPH.SCH_ID schId ,   ROUND((nvl(sum(SPH.TOTAL_TIME),0)/sum(SPH.QUANTITY)),2)  realTime  from SCHT_PROCESS_HIS SPH  group by SPH.SCH_ID  having sum(SPH.QUANTITY) > 0
) ta
on (ta.schId =BSC.SCH_ID )
 when matched then
 update set BSC.REAL_TIME = ta.realTime