Oracle：如何创建一个为“SELECT * FROM”选项卡WHERE name IN（function（））返回值的函数。 [英] Oracle: How to create a function returning values for a "SELECT * FROM tab WHERE name IN (function())"

问题描述

我有一个我无法解决的问题。可能你有一个关于如何解决它的想法。

我有一个给定的参数表，像这样：

  P_VALUE P_NAME 
 ----------- ---------- 
 X85 A_03 
 XH1 A_04 
 XH2 A_04 
 XH3 A_04 
 C84 A_05 
  

看到有多个条目的参数。此时使用此参数：

  SELECT * FROM tablex 
 WHERE code IN（SELECT p_value 
 FROM parameter_table 
 WHERE p_name LIKE'A_04'）; 
  

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

  --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来说很好。

我会非常感谢好的想法或帮助！

解决方案从技术上讲，你可以通过这种方式来实现这个功能，但是这样做会导致索引不能在tablex上的代码列上使用，并且可能会影响性能。使用函数索引可以降低性能影响

 创建或替换函数f_get_param（p_value1 IN VARCHAR2，VARCHAR2中的p_name1）返回NUMBER 
 DETERMINISTIC 
 IS 
 l_count NUMBER ; 
 BEGIN 
从parameter_table中将count（1）转换为l_count，其中p_value = p_value1 
和p_name = p_name1; 
 if l_count> 0 
然后
返回1; 
 else 
 return 0; 
结束if; 
结束f_get_param; 
  

并使用像这样的select语句

  SELECT * FROM tablex 
 WHERE f_get_param（code，'A_04'）= 1; 
  

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

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

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');

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'));

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!

解决方案

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;

AND use the select statement like this

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

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

Please find the link on the article about using functions in SELECT statement

这篇关于Oracle：如何创建一个为“SELECT * FROM”选项卡WHERE name IN（function（））返回值的函数。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！