Home » SQL & PL/SQL » SQL & PL/SQL » How to build a function in order to be called in SQL (Oracle 10g)
How to build a function in order to be called in SQL [message #359326] Fri, 14 November 2008 16:27 Go to next message
Bonita
Messages: 32
Registered: June 2008
Member
I'm newbie in PL/SQL. One question need your helps. I'd like to how to pass values from function in order to be call in SQL.

Look at this simple example

CREATE TABLE TESTING (ID NUMBER,LV VARCHAR2(10));

Insert into TESTING (ID,LV) values (76000,'HIGH');
Insert into TESTING (ID,LV) values (76009,'LOW');
Insert into TESTING (ID,LV) values (76011,'HIGH');


Create a function

CREATE OR REPLACE FUNCTION testing_1 (i_id_in VARCHAR2 )
RETURN VARCHAR2
IS
v_id_out VARCHAR2(10);
BEGIN
v_id_out :=
CASE
WHEN i_id_in = 'H' THEN
'HIGH'
WHEN I_ID_IN = 'HL' THEN
'('||chr(39)||'HIGH'||chr(39)||chr(44)||chr(39)||'LOW'||chr(39)||')'
END;
RETURN v_id_out;
END;


If i_id_in is H, the function output value is 'HIGH', so following SQL proceeds succssfully.

select * FROM TESTING
WHERE LV IN TESTING_1('H');

Output is
Id LV
76000 HIGH
76011 HIGH


So far so good. The problem is that How to deal with this case when HL is being entered and show HIGH and LOW records ?

select * FROM TESTING
WHERE LV IN TESTING_1('HL');

The output should be
Id LV
76000 HIGH
76009 LOW
76011 HIGH

I build WHEN I_ID_IN = 'HL' THEN
'('||chr(39)||'HIGH'||chr(39)||chr(44)||chr(39)||'LOW'||chr(39)||')' whch is equal to ('HIGH','LOW'), but it doesn't work. Try a couple of combination, all failed.

Can someone help me out on how to modify this function ?

Thanks for your time and reply.
Re: How to build a function in order to be called in SQL [message #359328 is a reply to message #359326] Fri, 14 November 2008 16:35 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
What you are looking for is commonly called a "varying in list", and basically gets asked and answered almost every day.

Just this morning again, where a link to the usual Ask Tom answer was posted.
Previous Topic: How to get top X record
Next Topic: Create files from Oracle Procedure
Goto Forum:
  


Current Time: Fri Dec 02 18:38:27 CST 2016

Total time taken to generate the page: 0.27724 seconds