create or replace package PKG_NETCOMMCONTRACT
is
type CURCONTRACT is ref Cursor;
procedure GETNETCOMMCONTRACT
(DEP in varchar,
DES in varchar,
DEPDATE in Date,
USERCurCONTRACT out PKG_NETCOMMCONTRACT.CURCONTRACT);
end PKG_NETCOMMCONTRACT;
create or replace package body PKG_NETCOMMCONTRACT
as
procedure GETNETCOMMCONTRACT
(DEP in varchar,
DES in varchar,
DEPDATE in Date,
USERCurCONTRACT out PKG_NETCOMMCONTRACT.CURCONTRACT)
is
begin open USERCurCONTRACT for
'SELECT * FROM VI_USEFULCONTRACT
WHERE V1 BETWEEN TravelFrom and TravelTo
AND (V2-SYSDATE <AdvancePurchase)
AND DEPUTURE=:V3 AND DESTINATION=:V4
' USING DEPDATE,DEPDATE,DEP, DES ;
end GETNETCOMMCONTRACT;
end PKG_NETCOMMCONTRACT;
我的V1,V2,V3,V4该如何写啊,程序中老是报错啊?谢谢!
is
type CURCONTRACT is ref Cursor;
procedure GETNETCOMMCONTRACT
(DEP in varchar,
DES in varchar,
DEPDATE in Date,
USERCurCONTRACT out PKG_NETCOMMCONTRACT.CURCONTRACT);
end PKG_NETCOMMCONTRACT;
create or replace package body PKG_NETCOMMCONTRACT
as
procedure GETNETCOMMCONTRACT
(DEP in varchar,
DES in varchar,
DEPDATE in Date,
USERCurCONTRACT out PKG_NETCOMMCONTRACT.CURCONTRACT)
is
begin open USERCurCONTRACT for
'SELECT * FROM VI_USEFULCONTRACT
WHERE V1 BETWEEN TravelFrom and TravelTo
AND (V2-SYSDATE <AdvancePurchase)
AND DEPUTURE=:V3 AND DESTINATION=:V4
' USING DEPDATE,DEPDATE,DEP, DES ;
end GETNETCOMMCONTRACT;
end PKG_NETCOMMCONTRACT;
我的V1,V2,V3,V4该如何写啊,程序中老是报错啊?谢谢!
.......
begin open USERCurCONTRACT for
'SELECT * FROM VI_USEFULCONTRACT
WHERE V1 BETWEEN TravelFrom and TravelTo
AND (V2-SYSDATE <AdvancePurchase)
AND DEPUTURE=:V3 AND DESTINATION=:V4
' USING DEPDATE,DEPDATE,DEP, DES ;
exception
when others then
--这里我应该返回什么啊
.................. end GETNETCOMMCONTRACT;
end PKG_NETCOMMCONTRACT;
open USERCurCONTRACT for
'SELECT * FROM VI_USEFULCONTRACT
WHERE :V1 BETWEEN TravelFrom AND TravelTo AND (:V2-SYSDATE)<AdvancePurchase
'USING DEPDATE,DEPDATE;
疑问,是不是所有的占位符号都要以:V(冒号)写法啊?
你提问的帖子里v1,v2没加,v3v4又加了。
异常里需要返回什么是根据你业务需要什么阿。如果不想返回什么,又要避免过程提示错误,就:
exception
when others then
null;
....
谢谢了,如果象这种游标类的,我的sql比较复杂,因为我的概念是sql server中的,用了游标速度会很慢的,这里在oracle中的速度执行效率会慢吗?因为在sql server中都是通过select的语句返回一个结果集,我的数据量又比较大,我担心效率会下降啊