Re: Passing a 'where in' to stored procedure
Date: 1995/07/11
Message-ID: <3turvc$a6r_at_palm.phcs.com>#1/1
In article <sarek.25.002A3A5B_at_cais.com>, sarek_at_cais.com (Scott T. Johnson) writes:
|> Is the following legal? After executing, no data is changed in the
|> database but SQL reports the PL/SQL procedure completed
|> successfully.
|>
|> create or replace procedure upd_t1(name char, cc3 char, chg char)
|> is
|> begin
|> update t1 set c1 = name, c2 = cc3
|> where c2 in (chg);
|> end;
|> /
|>
|> SQL> execute upd_t1('picard','riker','''A1'',''A2'',''A3''')
Your procedure call is legal, but it probably doesn't do what you want. In your example, the parameter CHG has the value
'A1','A2','A3'
i.e. it's a character string that happens to include six singlequotes and two commas. Your WHERE ... IN clause gets a single-item list following the IN , and you'll only update your table if C2 equals CHG. SQL won't parse CHG into a multi-item list.
I see two alternatives:
(a) If you know that you'll never have more than <n> possible values
for column C2 to match, redefine your procedure to have <n> trailing CHG<i> parameters, with CHG2 through CHG<n> defaulting to NULL. Then your procedure's WHERE clause would be
where c2 in (chg1, chg2, chg3, ... , chg<n>) and you could execute
upd_t1('picard','riker','A1')
upd_t1('picard','riker','A1','A2','A3','A4')
etc.
(b) If you don't know how many CHG<i> parameters you might need,
or if you want to keep your procedure expandable for other kinds of parameters, then you could format parameter CHG as a delimited list and change your procedure's WHERE clause to
where CHG like '%,'||C2||',%'
or
where instr( CHG, ','||C2||',' ) > 0 These examples assume that parameter CHG looks like
,A1,
,A1,A2,A3,A4,
etc.
If you can't be sure that a comma will always be a safe delimiter, your procedure could compute the delimiter as
substr( CHG, 1, 1 )
Then you could issue calls with CHG looking like
|A1|
^Doe, Jane^Smith, John, Jr.^Madonna^
etc., changing the delimiter on the fly as convenient.
This method has the disadvantage of disabling any index on column C2.
Good luck. Received on Tue Jul 11 1995 - 00:00:00 CEST