Re: Stored Proc. Question

From: Rauno Seppanen <rauno.seppanen_at_icon.fi>
Date: Fri, 25 Sep 1998 22:36:45 +0300
Message-ID: <6ugmjn$4li$1@hiisi.inet.fi>

>Okay, I got three yes answers but no clues as to how?
>
>Can anyone offer additional help?
>
>--
>smetro_at_world.std.com

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;
>>S Metro wrote:
>>
>>> 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
>>>
>>> --
>>> smetro_at_world.std.com
>>> (take out nospam if replying via email)
>>
>
>
Received on Fri Sep 25 1998 - 14:36:45 CDT