| PL/SQL function returning table OR parameterized view [message #1570] |
Tue, 14 May 2002 00:14  |
Uwe Dahm
Messages: 2
Registered: May 2002
|
Junior Member |
|
|
Hi,
in MS SQL Server it is possible to make a construct like:
create function collaborator_inline (@au_id char(11))
returns table as
RETURN (
select l.au_id as "author",
l.title_id as "title",
r.au_id as "co-author"
from titleauthor l inner join titleauthor r
on l.title_id = r.title_id
AND l.au_id <> r.au_id
where l.au_id = COALESCE(@au_id, l.au_id)
-- order by l.title_id, l.au_id)
go
That is a stored procedure that can be referenced like a table as follows:
select * from dbo.collaborator_inline('724-80-9391')
select * from dbo.collaborator_inline(NULL)
Is there any way to do something similar with Oracle (8.1.7) ?
Thanks for your reply in advance!
|
|
|
|
|
|
| Re: PL/SQL function returning table OR parameterized view [message #1583 is a reply to message #1570] |
Tue, 14 May 2002 22:51  |
Uwe Dahm
Messages: 2
Registered: May 2002
|
Junior Member |
|
|
Dear Mahesh,
we have a web application for our financial department that should show some trades.
These trades must only be visible to the owner of the trade (this property is coded into a
4 character string e.g. '1234' of '0076' ). Each trade has a worth that changes during the lifetime
of the trade. The web application shows the parameters of the trade, the current worth and the worth
of the last ultimo. This should be done with a select that returns this row by row:
trade ,.. , current worth, ... last ultimos worth
But this is difficult to code within a single select statement because for some trades there are
no information yet for last ultimos worth.
The idea is now to compute these values within PL/SQL block and to access this PL/SQL code
as if this was a table like
select trade, currentworth,lastultimosworth from mysqlblock where tradeowner = '0076';
That should execute the necessary computiations within "mysqlblock" and return the rows needed.
Is something like this possible with PL/SQL in Oralce (8.1.7) ?
Mit freundlichen Grüßen / Best Regards
Uwe Dahm
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
