Oracle: how to create a function that returns values for the "SELECT * FROM" tab WHERE name IN (function ()) "
I have a problem that I cannot solve. Maybe you have an idea on how to solve it.
I have a parameter table like this:
P_VALUE P_NAME
----------- ----------
X85 A_03
XH1 A_04
XH2 A_04
XH3 A_04
C84 A_05
As you can see, there are options with multiple entries. Currently, these parameters are used in this way:
SELECT * FROM tablex
WHERE code IN (SELECT p_value
FROM parameter_table
WHERE p_name LIKE 'A_04');
Since the query is very large, this subset of parameters is used very often. I was trying to implement a function in Oracle to get my parameters. This works very well as long as there is only one line per parameter. When I want to use it in an "IN-Statement" it doesn't work, because the functions just return one value.
--WORKS
SELECT * FROM tablex
WHERE code = (f_get_param('A_03'));
--DOES NOT WORK
SELECT * FROM tablex
WHERE code IN (f_get_param('A_04'));
Note that this is needed for simple SQL statements, so the procedures will not work as they are just fine for PL / SQL.
I would be very grateful for any good ideas or help!
source to share
Technically, you can achieve using this function in this way, but it will result in the index not being used on the code column in tablex and may have a performance impact. By using a functional index, you can degrade performance.
CREATE OR REPLACE FUNCTION f_get_param(p_value1 IN VARCHAR2,p_name1 in VARCHAR2) return NUMBER
DETERMINISTIC
IS
l_count NUMBER;
BEGIN
select count(1) into l_count from parameter_table where p_value =p_value1
and p_name=p_name1;
if l_count > 0
then
return 1;
else
return 0;
end if;
end f_get_param;
And use a select statement like this
SELECT * FROM tablex
WHERE f_get_param(code,'A_04')=1;
EDIT 1: - Also to reduce the performance impact on database 10.2 and up If the table_parameter is static, you can use the DETERMINISTIC clause in the function to tell the function to return the same value if called with the same parameters every time
Please find the link in the article about using functions in the SELECT statement
source to share
--DOES NOT WORK
SELECT * FROM tablex
WHERE code IN (f_get_param('A_04'));
-- Try this
SELECT * FROM tablex
WHERE code IN (select * from TABLE(f_get_param('A_04')));
You should compile "CAST" into SQL TABLE. Also when you are using molding you can also use the inner joint:
SELECT * FROM tablex join TABLE(f_get_param('A_04') using (code);
I think - in general - your problem is called "Dynamic where clause". Try to find some articles about this on AskTom.
source to share
I think the actual solution to your problem is to simply join the two tables and create the appropriate indexes and not reference PL / SQL at all:
SELECT x.* FROM tablex x, parameter_table p
WHERE x.code = p.p_value
AND p.p_name LIKE '%A_04%';
Note that you also have a semantic error in your LIKE clause. You are not using the% sign, so your LIKE 'A_04' will be the same as = 'A_04'
source to share