Home -> Usenet -> comp.databases.oracle -> help with procedure

help with procedure

From: ben <benc_at_email.arizona.edu>
Date: 3 May 2003 00:15:53 -0700
Message-ID: <71915a6e.0305022315.5bded2ca_at_posting.google.com> 






I am writing a procedure in pl/sql (just learning it actually).  



I had an algorithm all worked out in a T-SQL mindset, I figured "how
different can oracle be?" Whoa!



here is the algorithim in T-SQL



curdate = getdate()


create #temp 


get a cursor select name, time from a table
while cursor has rows


    while curdate < targetdate

         insert into #temp values(curdate,cursor.name,cursor.time)
         curdate++




    end


end



select * from #temp



Its actually a bit more complex than that, but that is the essence.
      


After a couple of days of hunting, this is what I figure I have to do
in oracle.


The prodedure will look something like this: (psuedocode)



--declare TABLE varibles




TYPE tdate is TABLE of DATE


TYPE ttime is TABLE of DATE


TYPE tname is TABLE of VARCHAR2(20)



--make the prodedure





PROCEDURE returnValues (mydate OUT tdate, mytime OUT ttime, 

                        myname OUT tname)





AS



DECLARE



cursor, tempname, temptime


--make cursor that selects name, time from a table





WHILE cursor has rows


   tempname := cursor.name


   temptime := cursor.time



   WHILE curdate < target date

      mydate = curdate
      mytime = temptime
      myname = tempname
      increment date




   END



END


As you can see, I can't just simply return a REF CURSOR via a
function, because I am basically creating rows in the second while
loop.



so if the table looked like this:



name       time



bob        3:00
jill       4:00





The result from the procedure might look like this:



date    name      time



4/27    bob       3:00
5/27    bob       3:00
4/31    jill      4:00
5/02    jill      4:00
5/04    jill      4:00





I am also having a problem with displaying the output values so that I
can check them.  In sqlplus, how can I print out the result of a
procedure that returns multiple parameters of type table?  I confess,
I can't even get something like this to print out:



prodecure mysp (myout OUT NUMBER)



AS



BEGIN 



myout := 5;


END;



/



BEGIN



output.putline(mysp);


END



/



Thank you for any help,



Ben
Received on Sat May 03 2003 - 09:15:53 CEST












Original text of this message