Oracle语法整理

ORACLE函数：decode()

decode(sib.input_mode,2,2,1) AS SOATRANSACTIONTYPEID,--SOA事务处理类型ID   1 订单接收 2 直发现场

decode(字段或字段的运算，值1，值2，值3） 这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3
当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多

Oracle 语句中“||”代表什么啊？

Oracle 语句中“||”代表什么啊？跟ServerSQL中的字符串的连接符“+”是一个概念么？

1. 恩是的 是一个含义。。。select ‘1’||‘2’ from dual 代表12

2.跟ServerSQL中的字符串的连接符“+”是一个概念

3.–连接字段

SQL> select ‘ABC’||‘EFG’ from dual;

拼接：‘ABC’||‘EFG’

该字段拼接后的结果 ABCEFG

也可以使用concat()函数
select concat(‘A’,‘B’) from dual;

4.是的，是和java的 +号一样的。相当于 串联

5. '||'在oracle中用于拼接关联字段

6.连接字符串，相当于sql server中的+，也可以用concat（）函数实现连接

7.连接符，用于列与列，列与算术表达式或列与常量间创建一个字符表达式

比如
SQL>SELECT ename||’ is a '||job AS “Employee Details” FROM emp;
则查询结果为:
字段：Employee Details
值：SMITH is a ClERK
ALLEN is a SALESMAN
WARD is a MANAGER

to_char()

to_char(loc.inventory_location_id) AS LOCATORID,–货位ID

转换字段类型为varchar

oracle的nvl函数

Oracle中函数以前介绍的字符串处理，日期函数，数学函数,以及转换函数等等，还有一类函数是通用函数。主要有：NVL,NVL2,NULLIF,COALESCE，这几个函数用在各个类型上都可以。

下面简单介绍一下几个函数的用法。
在介绍这个之前你必须明白什么是oracle中的空值null
1.NVL函数
NVL函数的格式如下：NVL(expr1,expr2)
含义是：如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。
例如：
SQL> select ename,NVL(comm, -1) from emp;

oracle函数length

length(123)=3,是计算123的长度, length(‘abc’)=3,是计算’abc’的长度

length(123asd)和length(asd)中,函数将其当做是变量,因为它既不是有效的数字,也不是有效的字符串(应该，如’aaaaaa’,带引号的),所以就会出现"标识符无效"等错误

Oracle中的substr()函数

1、substr函数格式 (俗称：字符截取函数)

格式1： substr(string string, int a, int b);

格式2：substr(string string, int a) ;

解析：

格式1：
    1、string 需要截取的字符串
    2、a 截取字符串的开始位置（注：当a等于0或1时，都是从第一位开始截取）
    3、b 要截取的字符串的长度

格式2：
    1、string 需要截取的字符串
    2、a 可以理解为从第a个字符开始截取后面所有的字符串。

多表连接时是指多个条件 and

left join erp_i_inquiry_locator loc on loc.organization_id=wa.mis_warehouse_org_id and
loc.secondary_inventory_name=subwa.entity_code and
          substr(loc.locator_name,6,length(sp.entity_code)) = sp.entity_code --货位

sql语句查出的字段设置为固定值或为空

select
     sibd.id as id,
      'Y' AS DOFLAG,--导入/验证
      sibd.id AS PRIKEY,--记录唯一关键字
      sib.id as BILLID,--主单id
      sibd.id AS SOATRANSACTIONID,--SCM接收事务处理
      sib.entity_code AS SOARCVNUM,--SCM接收号
      1 AS SOATRANSACTIONTYPEID,--SOA事务处理类型ID   1 订单接收 2 直发现场
      '' AS RECEIPTSOURCECODE,--接收来源代码
      '' AS SOURCEDOCUMENTCODE,--文档类型
      so.mis_order_code AS ERPPONUM,--ERP采购订单编码
      sod.po_line_num AS POLINENUM,--ERP订单行号 ，由【OSB_ERP_PO_HQ_00001  导入采购订单信息服务】返回的【ERP订单号：ERP_PO_NUM】到【OSB_ERP_PO_HQ_00002  查询ERP采购订单信息服务（分页）】中去查询得到
      null AS POLINELOCATIONNUM,

新建表字段和描述的语法

--主表
CREATE TABLE "IF_PICKING_LINE_60" (
"ID" NUMBER NOT NULL ,
"PRI_KEY" VARCHAR2(60) NOT NULL,
"PROVINCE_CODE" VARCHAR2(30) NULL,
"INVEST_SUBJECT" VARCHAR2(20) NULL,
"PMS_PICKINGORDERHEAD_ID" NUMBER NOT NULL,
"PMS_PICKINGORDER_NO" VARCHAR2(50) NOT NULL,
"PMS_PICKINGORDER_NAME" VARCHAR2(240) NULL,
"CONSTRUCTION_UNIT_CODE" VARCHAR2(50) NOT NULL,
"CONSTRUCTION_UNIT_NAME" VARCHAR2(100) NOT NULL,
"PICK_WAREHOUSES_TYPE" VARCHAR2(10) NOT NULL,
"NEED_TIME" DATE NOT NULL,
"ORDER_DESC" VARCHAR2(240) NULL,
"COMPANY_CODE" VARCHAR2(50) NOT NULL,
"COMPANY_NAME" VARCHAR2(100) NOT NULL,
"SECOND_ORG_CODE" VARCHAR2(50) NOT NULL,
"SECOND_ORG_ID_OU" VARCHAR2(50) NULL,
"SECOND_ORG_NAME" VARCHAR2(100) NOT NULL,
"SECOND_ORG_NAME_OU" VARCHAR2(100) NULL,
"ORG_CODE" VARCHAR2(50) NULL,
"ORG_NAME" VARCHAR2(100) NULL,
"CREATED_ORG_TYPE" VARCHAR2(10) NOT NULL,
"CREATED_NUM" VARCHAR2(50) NOT NULL,
"CREATED_NAME" VARCHAR2(50) NOT NULL,
"CREATION_DATE" DATE NOT NULL,
"UPDATED_NUM" VARCHAR2(50) NOT NULL,
"UPDATED_NAME" VARCHAR2(50) NOT NULL,
"UPDATE_DATE" DATE NOT NULL,
"INPUT_EXT" VARCHAR2(2000) NULL,
"APPROVE_STATUS" VARCHAR2(10 BYTE) NULL ,
"PASS_STATE" VARCHAR2(10 BYTE) NULL ,
"ERP_RESULT" VARCHAR2(10 BYTE) NULL ,
"ENTITY_STATUS" VARCHAR2(10 BYTE) NULL ,
"IS_CANCELLATION" VARCHAR2(10 BYTE) NULL
)
;
COMMENT ON COLUMN "IF_PICKING_LINE_60"."ID" IS '主键';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."PRI_KEY" IS '记录唯一关键字';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."PROVINCE_CODE" IS '省公司代码';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."INVEST_SUBJECT" IS '投资主体';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."PMS_PICKINGORDERHEAD_ID" IS 'PMS领料申请单头ID';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."PMS_PICKINGORDER_NO" IS 'PMS领料申请单号';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."PMS_PICKINGORDER_NAME" IS 'PMS领料申请单名称';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."CONSTRUCTION_UNIT_CODE" IS '施工单位编码';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."CONSTRUCTION_UNIT_NAME" IS '施工单位名称';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."PICK_WAREHOUSES_TYPE" IS '申领仓库类型';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."NEED_TIME" IS '需求日期';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."ORDER_DESC" IS '单据说明';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."COMPANY_CODE" IS '单位编码';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."COMPANY_NAME" IS '单位简称';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."SECOND_ORG_CODE" IS '二级组织编码';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."SECOND_ORG_ID_OU" IS 'OU组织ID';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."SECOND_ORG_NAME" IS '二级组织名称';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."SECOND_ORG_NAME_OU" IS 'OU组织名称';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."ORG_CODE" IS '组织编码';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."ORG_NAME" IS '组织名称';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."CREATED_ORG_TYPE" IS '创建人组织类型';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."CREATED_NUM" IS '创建人编号';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."CREATED_NAME" IS '创建人名称';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."CREATION_DATE" IS '创建时间';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."UPDATED_NUM" IS '更新人编号';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."UPDATED_NAME" IS '更新人名称';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."UPDATE_DATE" IS '更新时间';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."INPUT_EXT" IS '输入扩展';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."APPROVE_STATUS" IS '审批状态';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."PASS_STATE" IS 'ERP导入状态';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."ERP_RESULT" IS 'ERP同步状态';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."ENTITY_STATUS" IS '状态';
COMMENT ON COLUMN "IF_PICKING_LINE_60"."IS_CANCELLATION" IS '作废';

给某一个字段加索引，目的字段不重复

格式：

CREATE INDEX 索引名(自定义) ON 表名(需要不重复的字段名)
alter table 表名
add constraint 索引名(自定义) unique(需要不重复的字段名) ENABLE NOVALIDATE;

CREATE INDEX IDX_IF_PICKING_LINE_60_PRI_KEY ON IF_PICKING_LINE_60(PRI_KEY);						
alter table IF_PICKING_LINE_60
add constraint PRI_KEY_ID unique(PRI_KEY) ENABLE NOVALIDATE;

删除索引

修改已有数据表的字段类型

group by 分组实战

查询近两年的BOM单子，根据is_model_product分组，后的数量。

select 
 count(*) bom总数,
 sum(case when  d.po_line_num is null then 1 else 0 end) 行id为空,
 sum(case when  d.po_line_num is not null then 1 else 0 end) 行id不为空,
 d.is_main_product as 是否同步erp  --  1同步2不同步
from scm_input_bill_detail t 
inner join scm_order_detail d on t.order_detail_id = d.id 
inner join scm_input_bill ib on t.input_bill_id = ib.id
where
t.parent_input_detail_id is not null
and t.parent_input_detail_id != 0 
and t.is_model_product in ('4','5') 
and ib.Entity_Create_Date >= to_Date('2021-05-01','yyyy-mm-dd')
and ib.entity_status !='terminated'
and ib.bill_type_id = 201
group by d.is_main_product

触发器和检查约束

触发器：
对装箱明细表如图所示，进行插入、更新、删除时就会触发这个触发器
。当触发这个触发器时就会更新订单明细的值。

检查约束：
当操作订单明细数据时，检查约束会使amount数量始终应该大于等于已发货数量。否则抛错。

oracle中字段加索引

–查看索引

select * from all_indexes where table_name = ‘表名（大写）’;

–查看索引和列名

select* from all_ind_columns where table_name = ‘表名（大写）’;

–增加索引

create index I_IC_HANDRESERVE_26 ON IC_HANDRESERVE(CBIZID);

create index 索引名 ON 表名(字段名);

备份表

create table [备份名] as select * from [表名];

navicate导入sql文件中文乱码

把sql文件重新另存为UTF-8格式，再导入数据库，中文显示正常了

查询表中重复的数据

select distinct ssss.entity_code
from erp_i_po_line ss
left join ERP_I_PO_ORDER_EXECUTION sss  on ss.INQUIRY_PO_ORDER_EXECUTION_ID = sss.id
inner join scm_order ssss on sss.erp_po_num = ssss.entity_code
where ssss.entity_create_date >= to_date('2022-05-01','yyyy-mm-dd')
and nvl(ss.DELETED_FLAG,0)=0 and ssss.deleted_flag = '0' and ssss.entity_status = 'completed'
group by ss.po_line_pri_key, ssss.entity_code
having count(ss.po_line_pri_key) > 1

行转列

参考文章 https://blog.csdn.net/weixin_40543389/article/details/127384313

我的逻辑：查询库存流水时，是以套数（组合物料编码一样的库存明细就是一套）的形式返回结果。 也就是说一套物料有两个库存明细，那我们返回的结果就是一条数据，而不是两条。
我的使用场景：在我的逻辑上，需要把这一套中的两个库存流水id拿到，那么只能以逗号合并成一个字段。就是两条数据的同一个字段，合并到一条数据中。也就是用到行转列。

SELECT 
WM_CONCAT(DISTINCT WAID),
MATERIALNAME, MATERIALCODE, INPUTUNITNAME, INPUTUNITID,BUYCOPERATECODE, PROVIDERNAME, MESSURENAME, MATERIALBELONG,
WAREHOUSENAME, LOCATIONNAME,  SUBWAREHOUSEORGNAME,PROVIDERPRODUCTNAME, FIRSTINPUTDATE, STOCKBALANCEAMOUT, 
ACCOUNTCATEID, BOMNUMBER, PROJECTNAME, PROJECTCODE, SITENAME, SITECODE, ISMODELPRODUCT, INPUTDETAILID, ATTRIBUTE10 
FROM 

( 
--当内部的select语句中。如果配比不一样，那么在计算套数时用到的字段就不相同，不能把多条流水明细行转列合并分组。但是计算后每个流水明细的套数就一致了，所以需要外侧再嵌套select进行合并分组
SELECT 
WM_CONCAT(DISTINCT IB.ID) AS WAID, --入库流水ID按英文逗号分割 
IB.MATERIAL_SKU_DESC AS MATERIALNAME, --组合物料名称
IB.MATERIAL_SKU_CODE AS MATERIALCODE , --组合物料编码 
IB.INPUT_UNIT_NAME AS INPUTUNITNAME ,  
IB.INPUT_UNIT_ID AS INPUTUNITID ,  
'' AS BUYCOPERATECODE , --当前字段为空 
IB.PROVIDER_NAME AS PROVIDERNAME ,  
'套' AS MESSURENAME, --当前字段为‘套’ 
IB.MATERIAL_BELONG AS MATERIALBELONG ,  
W.ENTITY_NAME AS    WAREHOUSENAME,  
IB.LOCATION_NAME AS LOCATIONNAME,  
WO.ENTITY_NAME AS   SUBWAREHOUSEORGNAME, 
'' AS PROVIDERPRODUCTNAME, --当前字段为空
REGEXP_SUBSTR(WM_CONCAT(DISTINCT IB.FIRST_INPUT_DATE),'[^,]+',1,1) AS FIRSTINPUTDATE,  --如果该字段一套流水明细中不一样拿每个明细的值都可以，那么合并就用WM_CONCAT()函数且不用group by分组该字段，再通过按逗号分割拿随意一条
(NVL(IB.AMOUNT, 0) - NVL(IB.PRE_AMOUNT, 0) - NVL(IB.BEYOND_PRE_AMOUNT, 0)) / IB.GROUP_RELATION AS STOCKBALANCEAMOUT, --返回实际剩余组合物料的套数 
IB.ACCOUNT_CATE_ID AS ACCOUNTCATEID,  
'' AS BOMNUMBER, --当前字段为空 
IB.PROJECT_NAME AS PROJECTNAME,  
IB.PROJECT_CODE AS PROJECTCODE,  
IB.SITE_CODE AS SITENAME,  
IB.SITE_NAME AS SITECODE,  
IB.IS_MODEL_PRODUCT AS ISMODELPRODUCT,  
'' AS INPUTDETAILID, --当前字段为空
'1' AS ATTRIBUTE10 --是否组合物料 组合物料查询时，应为1
FROM SCM_INPUT_WASTE_BOOK IB 
LEFT JOIN SCM_INPUT_BILL SIB ON IB.INPUT_BILL_ID = SIB.ID 
LEFT JOIN SCM_WAREHOUSE W ON W.ID = IB.WAREHOUSE_ID   
LEFT JOIN SCM_WAREHOUSE_ORG WO ON WO.ID = IB.SUB_WAREHOUSE_ORG_ID 
WHERE IB.FEE_TYPE = 0 --是否费用物资   
AND (IB.IS_MODEL_PRODUCT = 0 
  OR ( IB.IS_MODEL_PRODUCT > 0 AND IB.PARENT_INPUT_DETAIL_ID = 0 )   
  OR ( IB.IS_MODEL_PRODUCT > 0 AND IB.IS_SPLIT_BOM = 1 AND IB.PARENT_INPUT_DETAIL_ID > 0 ))   
AND W.IS_ERP_WAREHOUSE = 0   
AND NVL(IB.IS_DELETED, 0) = 0   
AND NVL(IB.AMOUNT, 0) - NVL(IB.PRE_AMOUNT, 0) - NVL(IB.BEYOND_PRE_AMOUNT, 0) > 0   
AND IB.FUND_TYPE = '1' --资金类型（费用类型） 
AND IB.ACCOUNT_CATE_ID = '1' --物资账务属性：正常物资
AND NVL( W.WAREHOUSE_LEVEL, 3 ) < 5   
AND IB.SURPLUS_TYPE <> 219   
AND IB.ISERP_HIS =0 
AND IB.MATERIAL_SKU_CODE IS NOT NULL 
AND IB.IS_CONCENTER =1 
GROUP BY IB.INPUT_BILL_ID,
IB.MATERIAL_SKU_CODE,
IB.MATERIAL_SKU_DESC, 
IB.INPUT_UNIT_NAME,  
IB.INPUT_UNIT_ID,  
IB.PROVIDER_NAME, 
IB.MATERIAL_BELONG,  
W.ENTITY_NAME,  
IB.LOCATION_NAME,  
WO.ENTITY_NAME,
IB.FIRST_INPUT_DATE,  
IB.AMOUNT,
IB.PRE_AMOUNT,
IB.BEYOND_PRE_AMOUNT,
IB.GROUP_RELATION, 
IB.ACCOUNT_CATE_ID, 
IB.PROJECT_NAME,  
IB.PROJECT_CODE,  
IB.SITE_CODE,  
IB.SITE_NAME,  
IB.IS_MODEL_PRODUCT 
) 

GROUP BY 
MATERIALNAME, MATERIALCODE, INPUTUNITNAME, INPUTUNITID,BUYCOPERATECODE, PROVIDERNAME, MESSURENAME,
MATERIALBELONG,WAREHOUSENAME,LOCATIONNAME, SUBWAREHOUSEORGNAME,PROVIDERPRODUCTNAME, FIRSTINPUTDATE, STOCKBALANCEAMOUT, 
ACCOUNTCATEID, BOMNUMBER, PROJECTNAME, PROJECTCODE, SITENAME, SITECODE, ISMODELPRODUCT, INPUTDETAILID, ATTRIBUTE10 

批量update

https://blog.csdn.net/qq_36993080/article/details/107388188

update scm_deliver_notice sdn
set sdn.BUYER_ID = (
 select spu.OA_ACCOUNT
 from S_PRIVILEGE_USER spu 
 where sdn.BUYER_ID = spu.PARTY_CODE and sdn.BUYER_ID is not null and sdn.SEND_NUM is not null
) 
WHERE EXISTS (
 select 1
 from S_PRIVILEGE_USER spu 
 where sdn.BUYER_ID = spu.PARTY_CODE and sdn.BUYER_ID is not null and sdn.SEND_NUM is not null
);