Thanks for the question, mike.
Asked: August 17, 2000 - 3:10 pm UTC
Last updated: August 17, 2000 - 3:10 pm UTC
Version: 8.1.5 or 8i
Viewed 1000+ times
You Asked
Problem: To write a stored procedure to retrieve the most recent 5 news articles on a news table with 1 million rows.
Given: Table news has 1,000,000 news stories that are populated every hour.
Basically i would like to write a stored procedure that would give as output a result set which is the top 5 rows of
(
select * from news order by transmission_timestamp descending.
)
here's what i got so far
-----
create or replace procedure
get_news(ticker_value in varchar2, news_curs out types.cursorType)
is
begin
open news_curs for
select n.news_id, n.story_headline, n.filename, n.transmission_time, n.source_
code from news n, ticker_news_xref t where t.ticker=ticker_value and n.news_id=
t.news_id order by transmission_time desc;
END;
-----
But this would return 1,000,000 rows. I would like to take the first 5 rows. Now, ROWNUM does not work with an order clause.
ROWNUM simply returns to you the first 5 rows without the order by applied to it.
On a more general note, I like to know how stored procedures in Oracle implement the equivalent of temporary tables in other RDBMS languages like Sybase, Informix. Secondly, is there a way
to pass a subset from say cursor1 to cursor2? For example, in the above problem, i can have a loop of 5 iterations on news_curs and fetch into a row, but how could i store the 5 rows into another cursor which i can then give back to the front end web scripting language?
Thanks in advance.
Mike Onghai
and Tom said...
In Oracle8i as you have -- it would be for example:
ops$tkyte@8i> variable x refcursor
ops$tkyte@8i>
ops$tkyte@8i> begin
2 open :x for 'select *
3 from ( select *
4 from all_users
5 order by created desc )
6 where rownum <= 5';
7 end;
8 /
PL/SQL procedure successfully completed.
ops$tkyte@8i>
ops$tkyte@8i> print x
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
WEB$NKATARE 35703 17-AUG-00
WEB$DRORHER 35702 17-AUG-00
WEB$BJEFFRIE 35701 17-AUG-00
WEB$ABCAMPBE 35700 17-AUG-00
WEB$JMONCHER 35699 17-AUG-00
You have to use dynamic sql in 8.1.5 because plsql did not quite yet understand that you can order by a subquery ...
For temporary tables ... See
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:48812348054
For how to put one cursor into "another" cursor -- we can do that. See
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:139812348065 <code>
for the general mechanism. You would use a collection type variable and as you fetched them from the cursor -- put them into this variable (like an array of records). You can then "select * from <this plsql variable>" later to re-retrieve them (or sort them or where on them or use them in a subquery or whatever...)
Is this answer out of date? If it is, please let us know via a Comment