Re: passing in list of values in stored procedure
- From: z1hou1 <z1hou1@xxxxxxxxx>
- Date: Tue, 02 Oct 2007 00:01:57 -0700
Hi tiffanythang,
It would be helpful if you can specify the database version. I have
modified the procedure here to use REF CURSOR - applicable in Oracle
8i and above.
This procedure is valid if you may call it as follows:
EXEC myproc1('1') or EXEC myproc1('1,2'). Both of these will retrieve
the rows corresponding to id=1 or id=1 or 2 from mytab as mentioned in
your very first post. The type REF CURSOR also allows you to pass the
CURSOR to other procedures/functions as parameters.
/** BEGIN of example **/
CREATE OR REPLACE PROCEDURE myproc1(p_idlist IN VARCHAR2) AS
v_id NUMBER;
v_name VARCHAR2(30);
v_sql_text VARCHAR2(1000);
TYPE c_ref_cursor IS REF CURSOR;
c_mytab_cur c_ref_cursor;
BEGIN
v_sql_text := 'SELECT id,name FROM mytab WHERE id IN (' || p_idlist
|| ')';
OPEN c_mytab_cur FOR v_sql_text;
LOOP
FETCH c_mytab_cur INTO v_id, v_name;
EXIT WHEN c_mytab_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('id = ' || v_id);
DBMS_OUTPUT.PUT_LINE('name = ' || v_name);
END LOOP;
CLOSE c_mytab_cur;
END;
/** END of example **/
While the example will work as long as the list is a numeric list,
slight modifications are neccessary if the parameters passed are going
to types other than numeric. If the parameters are say - alphanumeric,
then you will have to build in the single quotes as well into
v_sql_text - just as you had called the proc originally. Using REF
CURSOR you may construct complex SQLs with parameters representing
different datatypes, not just lists as is evident from this example.
Look up REF CURSOR against Oracle documentation all the way from
Oracle 8i to 10g. It has evolved and is very powerful. There is a
variation in SYS_REFCURSOR and OracleRefCursor as you move thru the
versions. It is extremely flexible and with 9i and 10g can be passed
back to .net programs and jdbc programs.
Hope this helps.
Regards,
z1hou1
.
- References:
- passing in list of values in stored procedure
- From: tiffanythang@xxxxxxxxx
- passing in list of values in stored procedure
- Prev by Date: Re: Unexplainable: "Beautified" code runs slower
- Next by Date: Re: recent drivel posted by Tony Rogerson on his blog
- Previous by thread: Re: passing in list of values in stored procedure
- Next by thread: Re: passing in list of values in stored procedure
- Index(es):
Relevant Pages
|
