首页 新闻 赞助 找找看

大虾求救,关于Oracle的表函数问题

0
悬赏园豆:30 [已解决问题] 解决于 2016-06-17 08:52

照着 :

http://www.cnblogs.com/Godblessyou/archive/2010/07/17/1779633.html

http://www.cnblogs.com/liguiqing/archive/2009/05/26/1489243.html

这2个地址写了个表函数,使用pipelined 方式的和使用普通方式的,写好后,都是执行成功,编印出错!求助哪里出了问题。。。。

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;
View Code
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 ;
View Code
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;
View Code

看2个函数,不知道是不是因为 for in 后面跟的是 dual 方式的表?不清楚是不是这问题。。。。

 

TakeTry的主页 TakeTry | 菜鸟二级 | 园豆:238
提问于:2016-06-16 10:04
< >
分享
最佳答案
0
create or replace function GetSeal(beid char(36))  

函数的参数错误:
1.不能传 char(36) ,若使用char36 函数只能生成成功,不能编译通过。

2.不能只单独传 char ,若只传传char,虽然能生成成功,能编印通过,但是在使用的时候会报 “ ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小”。

正确写法:

根据 接收返回类型 必须传入可变类型,in varchar2 或者 in nvarchar2 。

TakeTry | 菜鸟二级 |园豆:238 | 2016-06-16 18:58
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册