In an Oracle database schema, what is a more effecient way to collect data. Would it be better to use a cursor within a procedure, or a view. This question is based on the backdrop of a database which is quiet crunched. So which one would be less task intensive given the fact that it is going to run every 3 seconds.
Depends on what you mean by collect? Do you want to interact with the data ie update, delete, etc, do you want to return it to a front-end ie display it on a screen or web page, do you want to parse it?
You can return a ref cursor from a procedure without iterating it. Also most ref cursors are weak cursors which means Oracle can do better sharing on them (being weak it knows it can safely share the cursor between different sessions).
If we're talking view (as opposed to Materialised View), it and the cursor both require the underlying SQL to be executed for each select.
If the view is updateable, Oracle doesn't know if the view will be updated. If the cursor is weak Oracle knows it can't be updated, hence more efficient sharing of the cursor.