Cursor Intro.

[frankyberry]

I'm using Oracle 9i.
I'm creating a Db procedure that will retrieve data and I'm planning on using a cursor to loop through the returned data to insert into a table.

The only problem is that I'm a newbie to Cursors...my Db expertise is low.

So what I am asking is how to go about defining a cursor and so on...a better bet is to find a good FAQ on this topic.

Any help on cursors would be grateful...

 

[mrdenny]

The Oracle 9i forum forum759 will probably get you more assistance. This forum is more generic than that.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jrbarnett]

Franky

There's no need to use a cursor to retrieve the data. Its a while since I touched Oracle stored procs and have long forgotten the exact syntax, but try something like:


CREATE PROCEDURE procname
AS

INSERT INTO table2 (fielda, fieldb)
SELECT field1, field2
FROM sometable
WHERE field1 = 'X'

Be sure to change the query to your exact needs.
This stops you needing to use a cursor to insert each row individually.

John

 

[frankyberry]

Thanks for all the info.
I think I do need a cursor as I need to process the data depending on the returned data.

Thanks to all.

 

[SQLSister]

I don;t know Oracle but in SQL server I would never use a cursor for that. I would use the CASe statement. But truly your best bet for help that will specifically address Oracle performance issues is inthe Oracle forums. I know a cursor is horrible for performance and should be avoided in SQL Server but don't know if Oracle handles them differnently.

Questions about posting. See faq183-874

 

[mrdenny]

Oracle does handle them differently. Oracle is optomized for cursor based operations and processes them nicly.

Infact do to Oracle syntax differences cursors are ofter required where in SQL we would do a set based operation.

With Oracle if you do a SET based UPDATE in the back end the engine actualy does row by row updates as that's how they optomized the database engine.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com