I am writing an Oracle stored procedure for the first time in a long time, and I can't remember how this works. I want to write a procedure that takes a parameter and selects from a table based on that parameter, and displays the results to the user.
So I want something like:
create or replace procedure test(p in char)
select col1 from table where col=p;
There could be a variable number of results returned. How do I accomplish that?
You can also create table function that returns a result set.
CREATE TYPE result_row AS OBJECT (id number, full_name varchar(50))
CREATE TYPE result_row_table AS TABLE OF result_row
CREATE OR REPLACE FUNCTION get_records(p varchar)
return_row result_row := result_row(null, null);
FOR t_rec IN (SELECT id, first_name, last_name
WHERE last_name LIKE p||'%') LOOP
return_row.id := t_rec.id;
return_row.full_name := t_rec.first_name||' '||t_rec.last_name;
PIPE ROW (return_row);
And the function can then be used like this:
which might be easier to use than a ref_cursor depending on your environment.
This is something that drives me crazy about Oracle. In SQL Server, you can simply "select column from table" and it will display the results to the user. That seems so much simpler and straightforward to me.
If anybody knows why Oracle doesn't allow that, or can help me to understand (and therefore remember) their philosophy about this, I would definitely appreciate your comments. This really bugs me. It almost makes me want to abandon Oracle and go back to SQL Server.
That seems so much simpler and straightforward to me.
And a lot more limited as far as I can tell.
As far as I know you are limited to "straight" SELECTs in SQL Server. With Oracle's way, you can return anything. You could even read the data from a text file, or apply other procedural calculations on each row while you retrieve it (without the need to create a temporary table or to keep the whole result set in memory.