prompt SF_XT_GETZLXMBH 获取某类别下的最新诊疗项目编号
create or replace function SF_XT_GETZLXMBH
(
  as_SJXMBH  varchar2   --上级项目编号
)
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- jlg         2019.09.27  created 获取某类别下的最新诊疗项目编号   ZYSF9-XMGL9-20190925-001-001
--             2022.06.30  开放项目到6位
return varchar2
as
  uRetVal     varchar2(200);
  i           number;                   --计数器
  xmcount     number;                   --子层项目记录数
  VZLXMJC     BM_ZLZD00.ZLXMJC%type;
  tempbh      number;                   --子层编号
  E_xmgd      exception;
  LS_ERR      char(100);
  maxcount    number;                   --最大记录数
  maxlength   number;                   --要截取的位数
  placeholder_line varchar2(10);        --下划线占位符；
  placeholder_zero varchar2(10);        --0占位符；
begin
  uRetVal:=null;


  --maxcount := 999;
  --maxlength := 3;
  --placeholder_line := '___';
  --placeholder_zero := '000';

  --if as_SJXMBH = 'Z008283' then
    maxcount  := 999999;
    maxlength := 6;
    placeholder_line := '______';
    placeholder_zero := '000000';
  --end if;

  select count(*) into xmcount from BM_ZLZD00 where ZLXMBH like as_SJXMBH||placeholder_line;


  if xmcount = maxcount then--子项目已满
    raise E_xmgd; --(-20000,'该项目含有的子项目已超过999个');
  elsif xmcount = 0 then
    uRetVal:=as_SJXMBH||placeholder_zero;
  else --取最大编号值
    LS_ERR:='select max(ZLXMBH) into uRetVal from BM_ZLZD00 where ZLXMBH like '||as_SJXMBH||placeholder_line;
    select max(ZLXMBH) into uRetVal from BM_ZLZD00 where ZLXMBH like as_SJXMBH||placeholder_line;
  end if;

  tempbh:=to_number(substr(uRetVal,length(as_SJXMBH)+1,maxlength));--取末尾编码(数值)
  if tempbh < maxcount then  --编码小于999
    uRetVal:= as_SJXMBH||lpad(to_char(tempbh+1),maxlength,'0');  --编码加1
  else --编码=maxcount，倒求值
    while tempbh>0 loop
      tempbh:= tempbh-1;
      select count(*) into i from BM_ZLZD00 where ZLXMBH=as_SJXMBH||lpad(to_char(tempbh),maxlength,'0');
      exit when i=0;
    end loop;
    if tempbh=0 then--子项目已满
      raise E_xmgd; --_APPLICATION_ERROR(-20000,'该项目含有的子项目已超过限制'); --
    else--tempbh<>0
      uRetVal:=as_SJXMBH||lpad(to_char(tempbh),maxlength,'0');  --编码加1
    end if;
  end if;

  return uRetVal;

exception
    when E_xmgd then
      raise_application_error(-20000,'该项目含有的子项目已超过限制！*');
    when no_data_found then
      raise_application_error(-20001, '数据没有找到！*'||LS_ERR);
    when others then
      raise_application_error(-20002, nvl(SQLERRM, '原因不明出错')||'！*');
end;