Oracle:如何为“SELECT * FROM tab WHERE name IN (function())"创建返回值的函数

2022-01-23 00:00:00 function subquery oracle

我有一个我无法解决的问题.也许你对如何解决它有一个想法.

I have a problem which I can't solve. Maybe you have an idea about how to solve it.

我确实有一个像这样的给定参数表:

I do have a given 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 parameters with multiple entries. At the moment this parameters are used in this way:

SELECT * FROM tablex
WHERE code IN (SELECT p_value 
               FROM parameter_table
               WHERE p_name LIKE 'A_04');

由于查询非常大，这些参数子选择经常使用.我试图在 Oracle 中实现一个函数来获取我的参数.只要每个参数只有 1 行，它就可以很好地工作.当我想在IN-Statements"中使用它时，它不起作用，因为函数只返回一个值.

As the query is very big these parameter sub-select are used very often. I was trying to implement a function in Oracle to get my parameters. This works very fine as long as there is just 1 row per parameter. When I want to use it in "IN-Statements", it won't work because functions just return a single 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'));

请注意，对于普通的 SQL 语句，我需要它，因此过程将无法工作，因为它们仅适用于 PL/SQL.

Please note that I need it for plain SQL statements, so procedures won't work as they are just good for PL/SQL.

如果有好的想法或帮助，我将不胜感激！

I would be really thankful for good ideas or help!

推荐答案

从技术上讲，你可以通过这种方式实现使用函数，但是这样做会导致 tablex 上的 code 列上没有使用索引，并且可能会影响性能.使用函数索引你可以降低性能影响

Technically you can achieve using the function this way but doing this will cause index not to be used on code column on tablex and may affect performance .Using function index you can reduce performance impact

 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;

并像这样使用 select 语句

AND use the select statement like this

SELECT * FROM tablex
WHERE f_get_param(code,'A_04')=1;

编辑 1:-还可以降低数据库 10.2 及更高版本中的性能影响如果 parameter_table 是静态的，您可以在函数中使用 DETERMINISTIC 子句来表示如果每次使用相同的参数调用该函数，则该函数返回相同的值

EDIT 1:- Also to reduce the performance impact in database 10.2 and greater If the parameter_table is static you can use the DETERMINISTIC clause in the Function to say that the function returns the same value if called with same parameters every time

请找到 链接关于在 SELECT 语句中使用函数的文章

相关文章