oracle中if/else功能的实现的3种写法

oracle中if/else功能的实现的3种写法
https://www.cnblogs.com/yangzhilong/archive/2013/04/03/2998282.html

1、标准sql规范

一、单个IF
1、if a=… then
. …
end if;

2、

if a=… then
…
else
…
end if;

二、多个IF

if a=… then
…
elsif a=… then
…
end if;
这里中间是“ELSIF”，而不是ELSE IF 。这里需要特别注意

2、decode函数

DECODE的语法：

DECODE(value,if1,then1,if2,then2,if3,then3,…,else)
表示如果value等于if1时，DECODE函数的结果返回then1,…,如果不等于任何一个if值，则返回else。

3、case when

case when a='1’then ‘xxxx’
when a=‘2’ then ‘ssss’
else
　　‘zzzzz’
end as
注意点：

1、以CASE开头，以END结尾
2、分支中WHEN 后跟条件，THEN为显示结果
3、ELSE 为除此之外的默认情况，类似于高级语言程序中switch case的default，可以不加
4、END 后跟别名

例子：

create or replace procedure MW_WMS_HANDLE_BILL_OUT(userCode       varchar2,
                                                   billType       VARCHAR2,
                                                   billCode       varchar2,
                                                   warehouseGroup varchar2,
                                                   theRetStr      out varchar2) is

  /**********************************************
  /*根据发料单生成出库单
  /*根据type处理不同类型事务
  **********************************************/

  tempBillCode  varchar2(50);
  queryBillCode varchar2(50);
  queryBillCodes varchar2(50);

begin
  --先判断临时表中是否存在该单据
   select distinct t.bill_code into queryBillCode from data_inv_bill_info t where t.map_bill_code=billCode and t.map_bill_code not in(select distinct a.map_bill_code from data_inv_temp_bill a where a.map_bill_code=billCode);
   select distinct t.bill_code into queryBillCodes from data_inv_temp_bill t where t.map_bill_code=billCode;
   
   if queryBillCode = '' then 
   if queryBillCodes = '' then
   
   if billType = 'O001' THEN
    --生产发料单生成出库单
    --DATA_PLAN_ISSUE_STOCK到temp
    tempBillCode := to_char(SEQ_CHECKDETAIL.NEXTVAL);

    insert into DATA_INV_TEMP_BILL
      (BILL_CODE,
       MAP_BILL_CODE,
       BILL_DATE,
       IO_FLAG,
       --DIRECTION_FLAG,
       BILL_TYPE,
       SAP_MOVE_TYPE,
       SAP_BILL_CODE,
       SAP_BILL_TYPE,
       RELATION_CODE,
       RELATION_TYPE,
       RELATION_USER,
       BILL_REMARK,
       LIST_CODE,
       LIST_STATUS,
       MAP_LIST_CODE,
       ITEM_CODE,
       ITEM_QTY,
       
       QUALITY_STATUS,
       UNIT_CODE,
       WAREHOUSE_GROUP,
       WAREHOUSE_CODE,
       SRC_SAP_STOCK,
       SRC_SAP_WERKS,
       DETAIL_USER,
       DETAIL_REMARK,
       CREATE_USER,
       CREATE_TIME,
       CREATE_FLAG)
    
      select tempBillCode,
             a.issue_code,
             sysdate,
             'O',
             '生产发料',
             '261',
             a.issue_code,
             a.issue_type,
             'PCB车间',
             '成本中心',
             '车间收料员',
             '生产发料',
             tempBillCode,
             'N',
             a.rmes_ID,
             a.item_Code,
             a.item_Qty,
             'Y',
             b.unit_code,
             warehouseGroup,
             b.default_warehouse_id, --改为warehouse_code
             '', --SRC_SAP_STOCK,
             '', --SRC_SAP_WERKS,
             a.receive_userid,
             a.remark,
             userCode,
             sysdate,
             'S'
        from DATA_PLAN_ISSUE_STOCK a
        left join code_item b
          on a.item_code = b.item_code
       where a.issue_code = billCode;
  
    theRetStr := 'Y1,' || tempBillCode;
  end if;
  
  if billType = 'O005' THEN
    --交货单生成出库单
    --SAP_DELIVERY_ORDER到temp  
    tempBillCode := to_char(SEQ_CHECKDETAIL.NEXTVAL);
  
    insert into DATA_INV_TEMP_BILL
      (BILL_CODE,
       MAP_BILL_CODE,
       BILL_DATE,
       IO_FLAG,
       --DIRECTION_FLAG,
       BILL_TYPE,
       SAP_MOVE_TYPE,
       SAP_BILL_CODE,
       SAP_BILL_TYPE,
       RELATION_CODE,
       RELATION_TYPE,
       RELATION_USER,
       BILL_REMARK,
       LIST_CODE,
       LIST_STATUS,
       MAP_LIST_CODE,
       ITEM_CODE,
       ITEM_QTY,
       
       QUALITY_STATUS,
       UNIT_CODE,
       WAREHOUSE_GROUP,
       WAREHOUSE_CODE,
       SRC_SAP_STOCK,
       SRC_SAP_WERKS,
       DETAIL_USER,
       DETAIL_REMARK,
       CREATE_USER,
       CREATE_TIME,
       CREATE_FLAG)
    
      select tempBillCode,
             b.vbeln,
             sysdate,
             'O',
             '成品发货',
             '601',
             b.vbeln,--sap-bill-code
             '发货单',--sap-bill-type
             a.name1,--关联方
             '客户',--关联方类型
             a.extension1,--接收方
             '',--单据备注
             tempBillCode,--清单号
             'N',--清单号状态
             b.rmes_ID,
             b.matnr,
             b.lfimg,
             'Y',
             c.unit_code,
             warehouseGroup,
             b.lgort, --改为warehouse_code
             '', --SRC_SAP_STOCK,
             '', --SRC_SAP_WERKS,
             a.extension1,--detail_user
             '',
             userCode,
             sysdate,
             'S'--单据标识
        from SAP_DELIVERY_ORDER a 
        left join SAP_DELIVERY_DETAIL b 
         on a.VBELN=b.vbeln
        left join code_item c
          on b.matnr = c.item_code
       where a.vbeln = billCode;
  
    theRetStr := 'Y1,' || tempBillCode;
  end if;
  
    
  if billtype = 'O002' then
    --从TMP生成到BILL INFO 
    tempBillCode := func_get_bill_code('BILL_OUT');
    insert into DATA_INV_BILL_INFO
      (BILL_CODE,
       MAP_BILL_CODE,
       BILL_DATE,
       IO_FLAG,
       BILL_TYPE,
       SAP_MOVE_TYPE,
       SAP_BILL_CODE,
       SAP_BILL_TYPE,
       RELATION_CODE,
       RELATION_TYPE,
       BILL_REMARK,
       LIST_CODE,
       ITEM_CODE,
       ITEM_QTY,
       QUALITY_STATUS,
       UNIT_CODE,
       WAREHOUSE_GROUP,
       SRC_SAP_STOCK,
       SRC_SAP_WERKS,
       DETAIL_USER,
       DETAIL_REMARK,
       CREATE_FLAG,
       CREATE_USER,
       CREATE_TIME)
      select tempBillCode,
             T.MAP_BILL_CODE,
             T.BILL_DATE,
             T.IO_FLAG,
             T.BILL_TYPE,
             T.SAP_MOVE_TYPE,
             T.SAP_BILL_CODE,
             'B',
             T.RELATION_CODE,
             T.RELATION_TYPE,
             T.BILL_REMARK,
             T.LIST_CODE,
             T.ITEM_CODE,
             T.ITEM_QTY,
             T.QUALITY_STATUS,
             T.UNIT_CODE,
             T.WAREHOUSE_GROUP,
             T.SRC_SAP_STOCK,
             T.SRC_SAP_WERKS,
             T.DETAIL_USER,
             T.DETAIL_REMARK,
             T.CREATE_FLAG,
             usercode,
             sysdate
        from data_inv_temp_bill t
       where t.bill_code = billcode
         and t.item_code in (select a.rmes_id
                               from TMP_CREATE_BILL_OUT a
                              where a.bill_seq = billcode);
    theRetStr := 'Y2,' || tempBillCode;
  
    DELETE FROM data_inv_temp_bill t
     WHERE t.bill_code = billcode
       and item_code in (select a.rmes_id
                           from TMP_CREATE_BILL_OUT a
                          where a.bill_seq = billCode);
  
    DELETE FROM TMP_CREATE_BILL_OUT T WHERE T.BILL_SEQ = billCode;
  
  end if;
  end if;

  else 
    theRetStr:='e,'||'该单据已生成出库单，请重写填写单据！';
  end if;
  commit;

exception
  when others then
    ---异常提示
    rollback;
    TheRetStr := 'N,' || '存储过程运行出错，请联系管理人员，反馈给北自所解决!';
end MW_WMS_HANDLE_BILL_OUT;