Stored Proc. Question 
Author Message
 Stored Proc. Question
Yes, you can.  But it will not as simple as in MS SQL Server or Sybase.
Quote:

> In MS SQL Server it is possible to write a stored procedure that consists of
> a SELECT statement that returns multiple rows.

> Is this possible in Oracle 7.3x?

> Thanks,
> Scott

> --

> (take out nospam if replying via email)



Mon, 12 Mar 2001 03:00:00 GMT
 Stored Proc. Question

In MS SQL Server it is possible to write a stored procedure that consists of
a SELECT statement that returns multiple rows.

Is this possible in Oracle 7.3x?

Thanks,
Scott

--

(take out nospam if replying via email)



Tue, 13 Mar 2001 03:00:00 GMT
 Stored Proc. Question
Scott

for oracle it is totally possible.  you can employ pl/sql table to do so.

thanks
uc



Quote:
> In MS SQL Server it is possible to write a stored procedure that consists
of
> a SELECT statement that returns multiple rows.

> Is this possible in Oracle 7.3x?

> Thanks,
> Scott

> --

> (take out nospam if replying via email)



Tue, 13 Mar 2001 03:00:00 GMT
 Stored Proc. Question
Yes, you can do it in Oracle 7.3.

Quote:

>In MS SQL Server it is possible to write a stored procedure that consists
of
>a SELECT statement that returns multiple rows.

>Is this possible in Oracle 7.3x?

>Thanks,
>Scott



Tue, 13 Mar 2001 03:00:00 GMT
 Stored Proc. Question
Okay, I got three yes answers but no clues as to how?

Can anyone offer additional help?

--

(take out nospam if replying via email)

Quote:

>Yes, you can.  But it will not as simple as in MS SQL Server or Sybase.


>> In MS SQL Server it is possible to write a stored procedure that consists
of
>> a SELECT statement that returns multiple rows.

>> Is this possible in Oracle 7.3x?

>> Thanks,
>> Scott

>> --

>> (take out nospam if replying via email)



Tue, 13 Mar 2001 03:00:00 GMT
 Stored Proc. Question

(if that email address didn't require changing)

Quote:

>In MS SQL Server it is possible to write a stored procedure that consists of
>a SELECT statement that returns multiple rows.

>Is this possible in Oracle 7.3x?

>Thanks,
>Scott

n 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;

Quote:
}

Thomas Kyte

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.



Tue, 13 Mar 2001 03:00:00 GMT
 Stored Proc. Question
Quote:
>Okay, I got three yes answers but no clues as to how?

>Can anyone offer additional help?

>--


Here is a sample of using cursors in a procedure. You must use a cursor
because there are no other ways of handling the situation of a SELECT clause
that returns multiple rows.
--------------------------------------------------------------------------
PROCEDURE Print_Customer_Info
--------------------------------------------------------------------------
(
  in_acc_no      IN NUMBER
)
IS
--this is the select clause that returns multiple rows
  CURSOR c1 IS
    SELECT c.cust_name_1
    FROM   customer c, add_hold d
    WHERE  d.acc_no = in_acc_no
    AND    c.cust_no = d.cust_no;
--you must declare a record that is type of the cursor
  rec1            c1%ROWTYPE;
  v_cust_name_1   VARCHAR2(1000) := NULL;

BEGIN
--and here is how to handle multiple rows of a cursor
--FOR-LOOP starts with the first row of the SELECT clause returns
--and loops until every row is handled
  FOR rec1 IN c1 LOOP
--you can get the valuesof the row by the record that is type of the cursor
    v_cust_name_1 := v_cust_name_1 || rec1.cust_name_1 || ' ' || rec1.cust_name_2 || '\n';
  END LOOP;

  DBMS_OUTPUT.PUTLINE('CUST_NAME_1 = ' || v_cust_name_1);
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END;

Quote:

>>> In MS SQL Server it is possible to write a stored procedure that consists
>of
>>> a SELECT statement that returns multiple rows.

>>> Is this possible in Oracle 7.3x?

>>> Thanks,
>>> Scott

>>> --

>>> (take out nospam if replying via email)



Tue, 13 Mar 2001 03:00:00 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. stored proc. question

2. stored proc. question

3. Another Sybase Stored Proc. Question

4. System stored proc. question...

5. System Stored Proc. Question....

6. Stored Proc Calling Another Stored Proc

7. How to get results from Stored Proc within a Stored Proc

8. How to call a stored Proc or Ext Stored proc /T-SQL UDF from VBScript

9. calling stored proc from stored proc via variable

10. Calling a stored proc within a stored proc

11. Calling Stored Proc from other Stored Proc

12. Exec store proc within a store proc


 
Powered by phpBB® Forum Software