需要那么复杂吗?
for example:
16:31:52 SQL> Create or replace function sGetday(iDays in int) return date is
16:31:52 2 dDate date;
16:31:52 3 begin
16:31:52 4 select to_date(rq,'yyyymmdd') into ddate from
16:31:52 5 (select rownum rn,to_char(rq,'yyyymmdd') rq from (select sysdat
e-rownum rq from dba_objects) where to_char(rq,'d')<>1)
16:31:52 6 where rn=idays;
16:31:52 7 return ddate;
16:31:52 8 end sGetday;
16:31:52 9 /函数已创建。已用时间: 00: 00: 00.81
16:31:54 SQL> select sysdate from dual;SYSDATE
----------
16-12月-04已用时间: 00: 00: 00.00
16:32:01 SQL> select sgetday(8) from dual;SGETDAY(8)
----------
07-12月-04已用时间: 00: 00: 00.47
for example:
16:31:52 SQL> Create or replace function sGetday(iDays in int) return date is
16:31:52 2 dDate date;
16:31:52 3 begin
16:31:52 4 select to_date(rq,'yyyymmdd') into ddate from
16:31:52 5 (select rownum rn,to_char(rq,'yyyymmdd') rq from (select sysdat
e-rownum rq from dba_objects) where to_char(rq,'d')<>1)
16:31:52 6 where rn=idays;
16:31:52 7 return ddate;
16:31:52 8 end sGetday;
16:31:52 9 /函数已创建。已用时间: 00: 00: 00.81
16:31:54 SQL> select sysdate from dual;SYSDATE
----------
16-12月-04已用时间: 00: 00: 00.00
16:32:01 SQL> select sgetday(8) from dual;SGETDAY(8)
----------
07-12月-04已用时间: 00: 00: 00.47
select sgetday(4) from dual;得到了下面的结果,
to_date( "2004-12-15", "yyyy-mm-dd") , to_date( "2004-12-14", "yyyy-mm-dd") , to_date( "2004-12-13", "yyyy-mm-dd") , to_date( "2004-12-11", "yyyy-mm-dd")
现在是如何把双引号变成单引号?
sReturn := sReturn || ' to_date( "' || to_char(dDate,'yyyy-mm-dd') || '", "yyyy-mm-dd") ';
请问怎么写???
谢谢!!!
Create or replace function sGetday(aiDays in integer) return varchar2 is
---获得当前天以前几天非星期天日期
---返回字符串,例如 to_date('2004.12.16','yyyy-mm-dd'),to_date('2004.12.15','yyyy-mm-dd'')
dDate date;
sReturn varchar2(2000);
idays integer ;
idaynum integer ;begin idays := aiDays ;
sReturn := ' ';
select to_date(sysdate) - 1 into dDate from dual where rownum = 1;
Loop
select decode(to_char(dDate,'d')-1,0,7,to_char(dDate,'d')-1) into idaynum from dual where rownum = 1;
if idaynum = 7 then
select dDate - 1 into dDate from dual where rownum = 1;
else
if sReturn > ' ' then
sReturn := sReturn || ',';
end if;
sReturn := sReturn || ' to_date( ''' || to_char(dDate,'yyyy-mm-dd') || ''', ''yyyy-mm-dd'') ';
select dDate - 1 into dDate from dual where rownum = 1;
idays := idays - 1;
end if;
exit when idays <= 0;
end loop;
return sReturn ;
end sGetday;
你的判断条件好象有问题吧?
-- idays := idays - 1;
end if; idays := idays - 1; --按你的意思,应该放在这里exit when idays <= 0;