免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 1559 | 回复: 0

调用存储过程问题很着急!在线等!谢谢了! [复制链接]

论坛徽章:
0
发表于 2006-08-23 11:04 |显示全部楼层
  应用在调用SP_Atpop_VesvoyMonitor这个存储过程的时候,当时带了这个3个参数值: V_MDEPT := '1070';  V_MDATE := '2006-08-23';  V_MTYPE := '0';oracle报:ORA-06502: PL/SQL: 数字或值错误 ORA-06512: 在line 1,但是过一会用同样的参数又自动好了这个是怎么回事请哪位大哥可以帮忙谢谢!过程如下:

CREATE OR REPLACE PROCEDURE SP_Atpop_VesvoyMonitor
(v_mdept  in Varchar2,v_mdate  in Varchar2,v_mtype  in Varchar2,
pRecCur out P_ReturnDataSet1.ReturnDataSet1)
as
  mdept Varchar2(20);
BEGIN
if v_mdept = '0' then
  mdept := '%';
else
   mdept:=v_mdept;
end if;
  open pRecCur for
  select total.vesselid, tbattemper.DEPARTMENTCODE ,tbattemper.BERTHTIME, atpop_vesvoy.VESSELENAME "船名", atpop_vesvoy.VOYAGE "航次", atpop_vesvoy.IMPORTSEXPORTSFLAG "进出口",
  round(nw *0.001,3)"预计吨位",  round(rw *0.001,0) "实际吨位",round(restw *0.001,0) "剩余吨位",nc "预计箱数/件数",rc "实际箱数/件数",restc "剩余箱数/件数",
  --atpop_vesvoy.LOCALETALLYEND || atpop_vesvoy.VESSELTALLYEND vstate
  decode(atpop_vesdock.STATE,0,0,NULL,0,1) vstate
  --total.nw ,total,rw,total.restw
from
(
select N.vesselid,Nw,decode(Rw,null,0,rw) rw, (nw-rw) restw, nc,rc,(nc-rc) restc
from
(SELECT   ctnidf_ctncheck.vesselid, SUM (ctnidf_ctncheck.grossweight) nw,COUNT (DISTINCT ctnidf_ctncheck.ctnno) nc
     FROM ctnidf_ctncheck, atpop_vesvoy,tbAttemper
    WHERE (    (atpop_vesvoy.vesselid = ctnidf_ctncheck.vesselid)
          and (tbAttemper.vesselid = ctnidf_ctncheck.vesselid)
    and (tbAttemper.DEPARTMENTCODE like v_mdept) and (to_char(tbAttemper.BERTHTIME,'yyyy-mm-dd') = v_mdate)
           AND (((atpop_vesvoy.importsexportsflag = 'E')AND (ctnidf_ctncheck.loadport = 'CNSHA'))
         or ((atpop_vesvoy.importsexportsflag = 'I')AND (ctnidf_ctncheck.DISCHARGEPORT = 'CNSHA')))
          )
GROUP BY ctnidf_ctncheck.vesselid) N,

(SELECT   ctnop_ctntal.vesselid, SUM (ctnop_ctntal.grossweight) rw,COUNT (DISTINCT ctnop_ctntal.ctnno) rc
     FROM ctnop_ctntal,tbAttemper
where (tbAttemper.vesselid = ctnop_ctntal.vesselid)
and (tbAttemper.DEPARTMENTCODE like mdept) and (to_char(tbAttemper.BERTHTIME,'yyyy-mm-dd') = v_mdate)
GROUP BY ctnop_ctntal.vesselid) r

where n.vesselid = r.vesselid(+)
) total,tbattemper,atpop_vesvoy,atpop_vesdock
where (tbattemper.vesselid = total.vesselid and atpop_vesvoy.vesselid = total.vesselid)
and (to_char(tbAttemper.BERTHTIME,'yyyy-mm-dd') = v_mdate)
and atpop_vesvoy.TRADETYPE = v_mtype
and (tbattemper.VESSELID = atpop_vesdock.VESSELID(+) and tbattemper.ATTEMPERTIME = atpop_vesdock.PLANDOCKTIME(+) and tbattemper.PORTSECTIONCODE = atpop_vesdock.PORTSECTIONCODE(+))
order by departmentcode, total.vesselid;
END sp_Atpop_VesvoyMonitor;
/

[ 本帖最后由 seanyu 于 2006-8-23 11:16 编辑 ]
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们:huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP