create or replace package demo
is
type curDemo is ref Cursor;
procedure getDemo
(DEP in varchar,DES in varchar,DEPDATE in DATE,userCurDemo out demo.curDemo);
end demo;create or replace package body demo
as
procedure getDemo
(DEP in varchar,DES in varchar,DEPDATE in DATE,userCurDemo out demo.curDemo);
is
begin
open userCurDemo for
select C.USERID,C.MARKUP from contractinfo C INNER JOIN
Markup M ON C.USERID=M.USERID
WHERE C.DEP=:V1 AND C.DES=:V2 AND DEPDATE >=:V2 USING DEP,DES,DEPDATE--请教的是这样的写法对吗?
UNION
select C.USERID,C.MARKUP from contractinfo C INNER JOIN
DISCOUNT D ON C.USERID=D.USERID
WHERE C.DEP=:V1 AND C.DES=:V2 AND DEPDATE >=:V2 USING DEP,DES,DEPDATE--请教的是这样的写法对吗?
end getDemo;
end demo;1,就是有多个输入变量的时候,我如何替换占位符号?
2, procedure getDemo
(DEP in varchar,DES in varchar,DEPDATE in DATE,userCurDemo out demo.curDemo);
这样的DEP in varchar,是否对啊,是不是要这样DEP in varchar(20)?谢谢!
is
type curDemo is ref Cursor;
procedure getDemo
(DEP in varchar,DES in varchar,DEPDATE in DATE,userCurDemo out demo.curDemo);
end demo;create or replace package body demo
as
procedure getDemo
(DEP in varchar,DES in varchar,DEPDATE in DATE,userCurDemo out demo.curDemo);
is
begin
open userCurDemo for
select C.USERID,C.MARKUP from contractinfo C INNER JOIN
Markup M ON C.USERID=M.USERID
WHERE C.DEP=:V1 AND C.DES=:V2 AND DEPDATE >=:V2 USING DEP,DES,DEPDATE--请教的是这样的写法对吗?
UNION
select C.USERID,C.MARKUP from contractinfo C INNER JOIN
DISCOUNT D ON C.USERID=D.USERID
WHERE C.DEP=:V1 AND C.DES=:V2 AND DEPDATE >=:V2 USING DEP,DES,DEPDATE--请教的是这样的写法对吗?
end getDemo;
end demo;1,就是有多个输入变量的时候,我如何替换占位符号?
2, procedure getDemo
(DEP in varchar,DES in varchar,DEPDATE in DATE,userCurDemo out demo.curDemo);
这样的DEP in varchar,是否对啊,是不是要这样DEP in varchar(20)?谢谢!
1.要这么用:
open userCurDemo for
'select C.USERID,C.MARKUP from contractinfo C INNER JOIN
Markup M ON C.USERID=M.USERID
WHERE C.DEP=:V1 AND C.DES=:V2 AND DEPDATE >=:V2
UNION
select C.USERID,C.MARKUP from contractinfo C INNER JOIN
DISCOUNT D ON C.USERID=D.USERID
WHERE C.DEP=:V1 AND C.DES=:V2 AND DEPDATE >=:V2' USING DEP,DES,DEPDATE,DEP,DES,DEPDATE;
2.不指定长度的用法:DEP in varchar
'select C.USERID,C.MARKUP from contractinfo C INNER JOIN
Markup M ON C.USERID=M.USERID
WHERE C.DEP=:V1 AND C.DES=:V2 AND DEPDATE >=:V2
UNION
select C.USERID,C.MARKUP from contractinfo C INNER JOIN
DISCOUNT D ON C.USERID=D.USERID
WHERE C.DEP=:V1 AND C.DES=:V2 AND DEPDATE >=:V2' USING DEP,DES,DEPDATE,DEP,DES,DEPDATE;
这个sql中有站位符,都是V1,V2,V3,而且是在不同的sql中语句中,难道不能识别吗:
例如:
open userCurDemo for
'select C.USERID,C.MARKUP from contractinfo C INNER JOIN
Markup M ON C.USERID=M.USERID
WHERE C.DEP=:V1 AND C.DES=:V2 AND DEPDATE >=:V2
UNION
select C.USERID,C.MARKUP from contractinfo C INNER JOIN
DISCOUNT D ON C.USERID=D.USERID
WHERE C.DEP=:V1 AND C.DES=:V2 AND DEPDATE >=:V2
UNION
select C.USERID,C.MARKUP from contractinfo C INNER JOIN
TEST1 T1 ON C.USERID=T1.USERID
WHERE C.DEP=:V1 AND C.DES=:V2 AND DEPDATE >=:V2
UNION
select C.USERID,C.MARKUP from contractinfo C INNER JOIN
TEST2 T2 ON C.USERID=T2.USERID
WHERE C.DEP=:V1 AND C.DES=:V2 AND DEPDATE >=:V2' USING DEP,DES,DEPDATE难道不可以,就写3个输入吗?非得写using n,n,n......个参数吗?