Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    10

    Unanswered: How can i write this store procedure

    sql date range problem! Help me
    sql result:

    startdate enddate price
    2004-1-2 2004-1-4 677
    2004-1-5 2004-1-8 900
    2004-1-9 2004-1-12 2000


    i want display in page this format:
    2004-1-2 2004-1-3 2004-1-4 2004-1-5 ... 2004-1-12
    677 677 677 900 2000

    how can i write store procedure

  2. #2
    Join Date
    Nov 2003
    Posts
    87
    this will do for u?



    1 declare
    2 sdate aa.startdate%type;
    3 edate aa.enddate%type;
    4 prc aa.price%type;
    5 diff integer;
    6 cursor c1 is
    7 select startdate, enddate, price
    8 from aa;
    9 begin
    10 open c1;
    11 loop
    12 fetch c1 into sdate,edate,prc;
    13 exit when c1%notfound;
    14 diff := edate - sdate;
    15 dbms_output.put_line(sdate || ' '|| prc);
    16 for i in 2..diff loop
    17 dbms_output.put_line(sdate +1 || ' '||prc);
    18 end loop;
    19 end loop;
    20 close c1;
    21* end;
    SQL> /
    02-JAN-04 677
    03-JAN-04 677
    05-JAN-04 900
    06-JAN-04 900
    06-JAN-04 900
    09-JAN-04 2000
    10-JAN-04 2000
    10-JAN-04 2000

    PL/SQL procedure successfully completed.

    SQL>

  3. #3
    Join Date
    Nov 2003
    Posts
    87
    sorry 1 currection..


    1 declare
    2 sdate aa.startdate%type;
    3 edate aa.enddate%type;
    4 prc aa.price%type;
    5 diff integer;
    6 cursor c1 is
    7 select startdate, enddate, price
    8 from aa;
    9 begin
    10 open c1;
    11 loop
    12 fetch c1 into sdate,edate,prc;
    13 exit when c1%notfound;
    14 diff := edate - sdate;
    15 dbms_output.put_line(sdate || ' '|| prc);
    16 for i in 1..diff loop
    17 dbms_output.put_line(sdate +i || ' '||prc);
    18 end loop;
    19 end loop;
    20 close c1;
    21* end;
    SQL> /
    02-JAN-04 677
    03-JAN-04 677
    04-JAN-04 677
    05-JAN-04 900
    06-JAN-04 900
    07-JAN-04 900
    08-JAN-04 900
    09-JAN-04 2000
    10-JAN-04 2000
    11-JAN-04 2000
    12-JAN-04 2000

    PL/SQL procedure successfully completed.

    SQL>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •