Oracle-受注番号サブ属性の関数

648 ワード
テキストリンク
--CartNo自動生成関数
create or replace function fn_create_cartno
  return varchar2
is
  v_res varchar2(20);
  v_cnt number:=0;
begin
  select count(*) into v_cnt
    from cart
   where cart_no like to_char(sysdate,'yyyymmdd')||'%';

  if v_cnt=0 then
     v_res:=to_char(sysdate,'yyyymmdd')||trim('001');
  else
     select max(cart_no)+1 into v_res
       from cart
      where cart_no like to_char(sysdate,'yyyymmdd')||'%';
  end if;
  return v_res;
end;  



select fn_create_cartno as cartno from dual;

insert into cart
  values(fn_create_cartno,?,sysdate,
20210703 TIL
[Redux] redux-saga effect