Re: help with procedure
From: Jim Kennedy <kennedy-down_with_spammers_at_attbi.com>
Date: Sat, 03 May 2003 08:59:29 GMT
Message-ID: <RxLsa.714714$F1.93708_at_sccrnsc04>
Date: Sat, 03 May 2003 08:59:29 GMT
Message-ID: <RxLsa.714714$F1.93708_at_sccrnsc04>
You are making it too difficult, just issue a sql statement to get the results. You don't need the temp table garbage in Oracle. Jim
-- Replace part of the email address: kennedy-down_with_spammers_at_attbi.com with family. Remove the negative part, keep the minus sign. You can figure it out. "ben" <benc_at_email.arizona.edu> wrote in message news:71915a6e.0305022315.5bded2ca_at_posting.google.com...Received on Sat May 03 2003 - 10:59:29 CEST
> 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