Re: PL/SQL Stored Procedure Question
Date: Thu, 14 May 1998 22:10:43 GMT
Message-ID: <355f6bdb.29843232_at_192.86.155.100>
A copy of this was sent to "Stan Zieg" <stanley.zieg_at_sciatl.com> (if that email address didn't require changing) On 14 May 1998 18:51:14 GMT, you wrote:
>I would expect this to be a simple question, but have been unable to work
>through it myself....
>
>I'd like to create a parameterized stored procedure that uses input
>parameters to set up conditions for a query, and returns a recordset. I
>would expect that the implementation would be something like:
>
> (Using SQL PLUS as front end)
>
> SQL> CREATE OR REPLACE PROCEDURE myTEST(p_InValue IN VARCHAR2) AS
> 2 BEGIN
> 3 SELECT * FROM myTable WHERE MyTable.Value = p_InValue;
> 4 END myTEST;
> 5 /
>
>Then, by executing with a command like:
>
> EXECUTE MyTest('123456')
>
>The result would be the recordset that 'would' have resulted from the
>following query:
>
> SELECT * FROM myTable WHERE MyTable.Value = '123456';
>
In short, it'll look like this:
create or replace function sp_ListEmp return types.cursortype as
l_cursor types.cursorType;
begin
open l_cursor for select ename, empno from emp order by ename;
return l_cursor;
end;
/
With 7.2 on up of the database you have cursor variables. Cursor variables are cursors opened by a pl/sql routine and fetched from by another application or pl/sql routine (in 7.3 pl/sql routines can fetch from cursor variables as well as open them). The cursor variables are opened with the privelegs of the owner of the procedure and behave just like they were completely contained within the pl/sql routine. It uses the inputs to decide what database it will run a query on.
Here is an example:
create or replace package types
as
type cursorType is ref cursor;
end;
/
create or replace function sp_ListEmp return types.cursortype as
l_cursor types.cursorType;
begin
open l_cursor for select ename, empno from emp order by ename;
return l_cursor;
end;
/
REM SQL*Plus commands to use a cursor variable
variable c refcursor
exec :c := sp_ListEmp
print c
and the Pro*c to use this would look like:
static void process()
{
EXEC SQL BEGIN DECLARE SECTION;
SQL_CURSOR my_cursor;
VARCHAR ename[40];
int empno;
EXEC SQL END DECLARE SECTION; EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL ALLOCATE :my_cursor;
EXEC SQL EXECUTE BEGIN
:my_cursor := sp_listEmp;
END; END-EXEC;
for( ;; )
{
EXEC SQL WHENEVER NOTFOUND DO break;
EXEC SQL FETCH :my_cursor INTO :ename, empno;
printf( "'%.*s', %d\n", ename.len, ename.arr, empno );
}
EXEC SQL CLOSE :my_cursor;
}
>When I try to create the procedure I get the error message:
>
> Warning: Procedure created with compilation errors.
>
>Upon examination of error message:
>
> SQL> show errors
> Errors for PROCEDURE myTEST:
>
> LINE/COL ERROR
> -------- -----------------------------------------------------------------
> 3/1 PLS-00428: an INTO clause is expected in this SELECT statement
> 3/1 PL/SQL: SQL Statement ignored
> SQL>
>
>I would expect this capability to be a standard part of Oracle, since this
>is
>accomplished easily in SQL Server using stored procedures. It would appear
>from the error description that the result set must be moved into a table
>before it is retrieved. This is pretty inefficient, since the results of
>the query are transitory, and I only wish to send the result back to the
>requestor, not to save the result. The actual query I need to run is
>pretty complex and involves several subqueries. I have been unable to
>define a view that I select against with WHERE conditions, because the
>conditions cannot be
>passed to the subquery and the subquery size becomes huge without the
>limiting conditions in place.
>
>Can anyone help?
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri May 15 1998 - 00:10:43 CEST