create or replace type SealData As object
(
   BE_ID  Char(36),
   
   JYZ_PIC nvarchar2(2000),

   GCJZ_PIC nvarchar2(2000),

   CMA_PIC nvarchar2(2000),

   CAL_PIC nvarchar2(2000),

   CNAS_PIC nvarchar2(2000)
);

create or replace type Seal_table As table of SealData;

create or replace function GetSeal(beid char(36))
  return Seal_table Pipelined
As 
   res SealData;
Begin
   For rw In ( 
          Select beid As BE_ID,
          (select b.Imageaddress from limsbusinessenregisterinfo c
          left join LIMSBEElectronicSealInfo a on c.id=a.enregisterid
          inner join LIMSCheckSealInfo b on a.electronicsealinfoid=b.electronicsealid 
          and c.checkqualificationid=b.checkqualificationid
          left join LIMSElectronicSealInfo d on b.electronicsealid=d.id
          Where d.Electronicname='检验专用章' And c.Id= beid
          ) As JYZ_PIC,
          (select b.Imageaddress from limsbusinessenregisterinfo c
          left join LIMSBEElectronicSealInfo a on c.id=a.enregisterid
          inner join LIMSCheckSealInfo b on a.electronicsealinfoid=b.electronicsealid 
          and c.checkqualificationid=b.checkqualificationid
          left join LIMSElectronicSealInfo d on b.electronicsealid=d.id
          Where d.Electronicname='工程见证' And c.Id= beid
          ) As GCJZ_PIC,
          (select b.Imageaddress from limsbusinessenregisterinfo c
          left join LIMSBEElectronicSealInfo a on c.id=a.enregisterid
          inner join LIMSCheckSealInfo b on a.electronicsealinfoid=b.electronicsealid 
          and c.checkqualificationid=b.checkqualificationid
          left join LIMSElectronicSealInfo d on b.electronicsealid=d.id
          Where d.Electronicname='CMA' And c.Id= beid
          ) As CMA_PIC,
          (select b.Imageaddress from limsbusinessenregisterinfo c
          left join LIMSBEElectronicSealInfo a on c.id=a.enregisterid
          inner join LIMSCheckSealInfo b on a.electronicsealinfoid=b.electronicsealid 
          and c.checkqualificationid=b.checkqualificationid
          left join LIMSElectronicSealInfo d on b.electronicsealid=d.id
          Where d.Electronicname='CAL' And c.Id= beid
          ) As CAL_PIC,
          (select b.Imageaddress from limsbusinessenregisterinfo c
          left join LIMSBEElectronicSealInfo a on c.id=a.enregisterid
          inner join LIMSCheckSealInfo b on a.electronicsealinfoid=b.electronicsealid 
          and c.checkqualificationid=b.checkqualificationid
          left join LIMSElectronicSealInfo d on b.electronicsealid=d.id
          Where d.Electronicname='CNAS' And c.Id= beid
          ) As CNAS_PIC
          From dual     
        )     
       Loop 
         res := SealData(rw.BE_ID,rw.JYZ_PIC,rw.GCJZ_PIC,rw.CMA_PIC,rw.CAL_PIC,rw.CNAS_PIC); 
         Pipe Row(res);
      End Loop;
      Return;     
End ;

create or replace function GetSeal(beid char(36))
  return Seal_table 
As 
   res Seal_table := Seal_table();
Begin
   For rw In ( 
          Select beid As BE_ID,
          (select b.Imageaddress from limsbusinessenregisterinfo c
          left join LIMSBEElectronicSealInfo a on c.id=a.enregisterid
          inner join LIMSCheckSealInfo b on a.electronicsealinfoid=b.electronicsealid 
          and c.checkqualificationid=b.checkqualificationid
          left join LIMSElectronicSealInfo d on b.electronicsealid=d.id
          Where d.Electronicname='检验专用章' And c.Id= beid
          ) As JYZ_PIC,
          (select b.Imageaddress from limsbusinessenregisterinfo c
          left join LIMSBEElectronicSealInfo a on c.id=a.enregisterid
          inner join LIMSCheckSealInfo b on a.electronicsealinfoid=b.electronicsealid 
          and c.checkqualificationid=b.checkqualificationid
          left join LIMSElectronicSealInfo d on b.electronicsealid=d.id
          Where d.Electronicname='工程见证' And c.Id= beid
          ) As GCJZ_PIC,
          (select b.Imageaddress from limsbusinessenregisterinfo c
          left join LIMSBEElectronicSealInfo a on c.id=a.enregisterid
          inner join LIMSCheckSealInfo b on a.electronicsealinfoid=b.electronicsealid 
          and c.checkqualificationid=b.checkqualificationid
          left join LIMSElectronicSealInfo d on b.electronicsealid=d.id
          Where d.Electronicname='CMA' And c.Id= beid
          ) As CMA_PIC,
          (select b.Imageaddress from limsbusinessenregisterinfo c
          left join LIMSBEElectronicSealInfo a on c.id=a.enregisterid
          inner join LIMSCheckSealInfo b on a.electronicsealinfoid=b.electronicsealid 
          and c.checkqualificationid=b.checkqualificationid
          left join LIMSElectronicSealInfo d on b.electronicsealid=d.id
          Where d.Electronicname='CAL' And c.Id= beid
          ) As CAL_PIC,
          (select b.Imageaddress from limsbusinessenregisterinfo c
          left join LIMSBEElectronicSealInfo a on c.id=a.enregisterid
          inner join LIMSCheckSealInfo b on a.electronicsealinfoid=b.electronicsealid 
          and c.checkqualificationid=b.checkqualificationid
          left join LIMSElectronicSealInfo d on b.electronicsealid=d.id
          Where d.Electronicname='CNAS' And c.Id= beid
          ) As CNAS_PIC
          From dual     
        )     
       Loop 
       res.extend;
       res(res.count):=SealData(rw.BE_ID,rw.JYZ_PIC,rw.GCJZ_PIC,rw.CMA_PIC,rw.CAL_PIC,rw.CNAS_PIC);        
      End Loop;
      Return res;     
End GetSeal;