我想把sql语句当参数传递给存储过程,这个sql语句是用在游标里的。但是现在有问题,问题如下:大家帮忙看下。谢谢了!!create or replace procedure th_getPlanHour(
teamid in number,
pid in number,
fid in number,
sDate in date,
eDate in date,
sqlstr in varchar2,
oHour out number
)
as
i integer:=0;
cursor udcursor is select d.id ,c.startdate,c.enddate from ||sqlstr||/*这里出错,提示“PL/SQL: ORA-00903: invalid table name”,sqlstr是传递进来的参数*/and (
(
c.startdate>=to_date('2009-1-1','yyyy-mm-dd')
and c.ENDDATE<=to_date('2009-12-31','yyyy-mm-dd')
) or (
c.startdate<=to_date('2009-1-1','yyyy-mm-dd')
and c.enddate>=to_date('2009-12-31','yyyy-mm-dd')
) or (
c.startdate<=to_date('2009-1-1','yyyy-mm-dd') and
c.enddate>=to_date('2009-1-1','yyyy-mm-dd') and
c.enddate<=to_date('2009-1-1','yyyy-mm-dd')
) or (
c.startdate>=to_date('2009-1-1','yyyy-mm-dd') and
c.startdate<=to_date('2009-12-31','yyyy-mm-dd') and
c.enddate>=to_date('2009-12-31','yyyy-mm-dd')
)
) order by eid;eid number;
date1 date;
date2 date;eid1 number;
mdate1 date;
mdate2 date;
mday number;beginopen udcursor;while udcursor%found loop
fetch udcursor into eid,date1,date2;
if i>0 then
if eid=eid1 then /*if the same,then compare the date*/
if to_date(date1,'yyyy-mm-dd')<to_date(mdate1,'yyyy-mm-dd') and to_date(date2,'yyyy-mm-dd')>to_date(mdate2,'yyyy-mm-dd') then/*if second start date and end date between the first,do nothing*/
mdate1:=date1;
mdate2:=date2;
end if;
if to_date(date1,'yyyy-mm-dd')<to_date(mdate1,'yyyy-mm-dd') and to_date(date2,'yyyy-mm-dd')<to_date(mdate2,'yyyy-mm-dd') and to_date(date2,'yyyy-mm-dd')>to_date(mdate1,'yyyy-mm-dd') then
mdate1:=date1;
end if;
if to_date(date2,'yyyy-mm-dd')<to_date(mdate1,'yyyy-mm-dd') then
mdate1:=date1;
end if;
if to_date(mdate2,'yyyy-mm-dd')<to_date(date1,'yyyy-mm-dd') then
mdate2:=date2;
end if;
if to_date(mdate1,'yyyy-mm-dd')<to_date(date1,'yyyy-mm-dd') and to_date(mdate2,'yyyy-mm-dd')<to_date(date2,'yyyy-mm-dd') and to_date(date1,'yyyy-mm-dd')>to_date(mdate2,'yyyy-mm-dd') then
mdate2:=date2;
end if;
else
mday := th_getHour(mdate1,mdate2);/*取存储过程th_getHour的返回值,但出现错误提示“PLS-00306: wrong number or types of arguments in call to 'TH_GETHOUR'”*/
oHour :=oHour+mday;
end if;
end if;
if i=0 then
eid1:=eid;
mdate1:=date1;
mdate2:=date2;
end if;
i:=i+1;
end loop;
close udcursor;end;
teamid in number,
pid in number,
fid in number,
sDate in date,
eDate in date,
sqlstr in varchar2,
oHour out number
)
as
i integer:=0;
cursor udcursor is select d.id ,c.startdate,c.enddate from ||sqlstr||/*这里出错,提示“PL/SQL: ORA-00903: invalid table name”,sqlstr是传递进来的参数*/and (
(
c.startdate>=to_date('2009-1-1','yyyy-mm-dd')
and c.ENDDATE<=to_date('2009-12-31','yyyy-mm-dd')
) or (
c.startdate<=to_date('2009-1-1','yyyy-mm-dd')
and c.enddate>=to_date('2009-12-31','yyyy-mm-dd')
) or (
c.startdate<=to_date('2009-1-1','yyyy-mm-dd') and
c.enddate>=to_date('2009-1-1','yyyy-mm-dd') and
c.enddate<=to_date('2009-1-1','yyyy-mm-dd')
) or (
c.startdate>=to_date('2009-1-1','yyyy-mm-dd') and
c.startdate<=to_date('2009-12-31','yyyy-mm-dd') and
c.enddate>=to_date('2009-12-31','yyyy-mm-dd')
)
) order by eid;eid number;
date1 date;
date2 date;eid1 number;
mdate1 date;
mdate2 date;
mday number;beginopen udcursor;while udcursor%found loop
fetch udcursor into eid,date1,date2;
if i>0 then
if eid=eid1 then /*if the same,then compare the date*/
if to_date(date1,'yyyy-mm-dd')<to_date(mdate1,'yyyy-mm-dd') and to_date(date2,'yyyy-mm-dd')>to_date(mdate2,'yyyy-mm-dd') then/*if second start date and end date between the first,do nothing*/
mdate1:=date1;
mdate2:=date2;
end if;
if to_date(date1,'yyyy-mm-dd')<to_date(mdate1,'yyyy-mm-dd') and to_date(date2,'yyyy-mm-dd')<to_date(mdate2,'yyyy-mm-dd') and to_date(date2,'yyyy-mm-dd')>to_date(mdate1,'yyyy-mm-dd') then
mdate1:=date1;
end if;
if to_date(date2,'yyyy-mm-dd')<to_date(mdate1,'yyyy-mm-dd') then
mdate1:=date1;
end if;
if to_date(mdate2,'yyyy-mm-dd')<to_date(date1,'yyyy-mm-dd') then
mdate2:=date2;
end if;
if to_date(mdate1,'yyyy-mm-dd')<to_date(date1,'yyyy-mm-dd') and to_date(mdate2,'yyyy-mm-dd')<to_date(date2,'yyyy-mm-dd') and to_date(date1,'yyyy-mm-dd')>to_date(mdate2,'yyyy-mm-dd') then
mdate2:=date2;
end if;
else
mday := th_getHour(mdate1,mdate2);/*取存储过程th_getHour的返回值,但出现错误提示“PLS-00306: wrong number or types of arguments in call to 'TH_GETHOUR'”*/
oHour :=oHour+mday;
end if;
end if;
if i=0 then
eid1:=eid;
mdate1:=date1;
mdate2:=date2;
end if;
i:=i+1;
end loop;
close udcursor;end;
解决方案 »
- 几道关于oracle的题目?
- 请教V$SQL_WORKAREA_HISTOGRAM视图
- 帮忙写个sql语句.
- 大量数据
- 从A表查询数据往B表中插入时存在的问题,谁帮忙解决下?
- 在cent os6.3,64bit上安装32位的Oracle11gr2遇到一个问题,请大家帮忙解答一下
- 利用Sqlplus连接时的口令问题???
- 急问,如和在red hat linux8 下安装 oracle 8?
- 一个查询?
- 请看下面SQL语句,如何提高效率,畅所欲言,在线讨论!!!
- 4个表abcd,取出A表中有,BCD中都没有的数据【在线等】
- sqf%rowcount 到底可以使用几次,如下边的格式,oracle 知道是哪个sql%rowcount 吗 请高手指点
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/dynamic.htm#i13057
存储过程(in1,in2,...,out1,out2,...);