| How to return cursor from a stored procedure [message #4578] |
Mon, 23 December 2002 22:43  |
Evgeny Stepanov
Messages: 2
Registered: December 2002
|
Junior Member |
|
|
Hello everybody!
I'm totally new in Oracle stored procs and having a problem. I don't know how to return cursor from a stored proc. i want to make stored proc as simple as
select * from my_table
i can happily get result from procedure with dbms_oputpu.put_line('something') but this is not what i really want. Actually, if you know how interbase works, i have SP like this (IB syntax)
-- IB stored proc
CREATE PROCEDURE GET_ALL_SUMS
RETURNS (
NA SMALLINT,
AREANAME VARCHAR(100),
PGEN DOUBLE PRECISION,
PDEM DOUBLE PRECISION,
QGEN DOUBLE PRECISION,
QDEM DOUBLE PRECISION,
POP DOUBLE PRECISION,
DELTAP DOUBLE PRECISION,
DELTAQ DOUBLE PRECISION)
AS
DECLARE VARIABLE DP DOUBLE PRECISION;
DECLARE VARIABLE DQ DOUBLE PRECISION;
begin
pgen = 0;
pdem = 0;
qgen = 0;
qdem = 0;
pop = 0;
for select distinct a.na, b.areaname from nodes a
inner join areas b on a.na = b.na
into :na, :areaname do
begin
select deltap from get_dp(:na, 0) into :dp ;
select deltaq from get_dq(:na, 0) into :dq;
select a.*, a.pdem + :dp, :dp, :dq from get_area_sums(:na) a
into :na, :pgen, :pdem, :qgen, :qdem, :pop, :deltap, :deltaq;
suspend;
end
end
-- end of IB stored proc
So i simply make select * from get_all_sums in IB and getting the resultset. How can i achieve similar behaviour in Oracle?
Thanks in advance!
Evgeny Stepanov
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
