help with procedure
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
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
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