Oracle Stored Procedure
684479Mar 3 2009 — edited Mar 3 2009I am new to Oracle with MS SQL Server background. It seems that MS SQL and Oracle handle data set operations differently in creating stored procedures; with a stored proc I am trying to create a denormalized table with a few large normalized Oracle tables which act as data dumps and store all kinds of historical information.
Instead of going deep with 40 million rows and very difficult to navigate, I am thinking about going horizontal with data denormalized for easy user access. With only 1 million unique customer records and their historical data in each row, business users can run queries more efficiently. However, in Oracle, I am not sure how ref cursors can be used and if the output can be selected into a permanent table. Here is the simplified code :
{color:#0000ff}Create or Replace PROCEDURE PROCEDURE1 AS
{color}
{color:#0000ff}Type rc IS REF CURSOR RETURN user.Table1%rowtype;
Cursor1 rc;
Cursor2 rc;
Cursor3 rc;
BEGIN
OPEN Cursor1 FOR
SELECT * FROM user.Table1
{color}
{color:#0000ff}END;
End PROCEDURE1;
{color}
While the above code compiles, I do not knwo how to output the whole dataset into a table. Can I open multiple cursors and hold all the rows from the historical tables and perform joins and other set based operations and return the result set from a cursor to a table? I thought about creating views but it seemed there would be too many intermidiate ones to be created. Will a stored proc or package be the best way to go or there are better solutions? Any help would be apprciated.