create or replace function fun_DxcBillSn(tabType integer,tabName varchar2,fieldStr varchar2)return integer

is

 serialSn integer;

 maxVal integer;

 nowMonth varchar(20);

begin

   --获取当前时间的月份--

   select to_char(Sysdate,'MM') into nowMonth from dual;

   --检查是否存在单号最大值  表名+ 类型 + 月份+字段 ---

   select COUNT(*) into maxVal from DXC_SYSSN where TABLENAME=tabName and BILLTYPE=tabType and MONTH=nowMonth and FIELD=fieldStr;

   --如果大于0 ，证明存在序号记录--

   if maxVal >0 then

        --获取序号表的最大值 --

        select MAXVAL into maxVal from DXC_SYSSN where TABLENAME=tabName and BILLTYPE=tabType and MONTH=nowMonth and FIELD=fieldStr;

        --获取序号ID值 --

        serialSn:= maxVal+1;

        --更新序号表记录的值 --

        update DXC_SYSSN set MAXVAL=serialSn where TABLENAME=tabName and BILLTYPE=tabType and MONTH=nowMonth and FIELD=fieldStr;

   --如果小于等于0 ，证明不存在记录

   else

        --删除旧数据记录

        delete from DXC_SYSSN where TABLENAME=tabName and BILLTYPE=tabType and FIELD=fieldStr;

          -- 返回序号的ID --

          serialSn:=1;

        --添加新的数据记录

        insert into DXC_SYSSN(TABLENAME,BILLTYPE,MONTH,FIELD,MAXVAL) values(tabName,tabType,nowMonth,fieldStr,serialSn);

   end if;

   return serialSn;

end;

create or replace function fun_DxcBillSn(tabType integer,tabName varchar2,fieldStr varchar2)return integer

is

--开启自治事务（解决Oracle ORA-14551: 无法在查询中执行 DML 操作的问题）--

 pragma autonomous_transaction;

 serialSn integer;

 maxVal integer;

 nowMonth varchar(20);

begin

   --获取当前时间的月份--

   select to_char(Sysdate,'MM') into nowMonth from dual;

   --检查是否存在单号最大值  表名+ 类型 + 月份+字段 ---

   select COUNT(*) into maxVal from DXC_SYSSN where TABLENAME=tabName and BILLTYPE=tabType and MONTH=nowMonth and FIELD=fieldStr;

   --如果大于0 ，证明存在序号记录--

   if maxVal >0 then

        --获取序号表的最大值 --

        select MAXVAL into maxVal from DXC_SYSSN where TABLENAME=tabName and BILLTYPE=tabType and MONTH=nowMonth and FIELD=fieldStr;

        --获取序号ID值 --

        serialSn:= maxVal+1;

        --更新序号表记录的值 --

        update DXC_SYSSN set MAXVAL=serialSn where TABLENAME=tabName and BILLTYPE=tabType and MONTH=nowMonth and FIELD=fieldStr;

   --如果小于等于0 ，证明不存在记录

   else

        --删除旧数据记录

        delete from DXC_SYSSN where TABLENAME=tabName and BILLTYPE=tabType and FIELD=fieldStr;

          -- 返回序号的ID --

          serialSn:=1;

        --添加新的数据记录

        insert into DXC_SYSSN(TABLENAME,BILLTYPE,MONTH,FIELD,MAXVAL) values(tabName,tabType,nowMonth,fieldStr,serialSn);

   end if;

    commit;   -- 提交事务

   return serialSn;

end;