如何在Oracle存储过程中获取逗号分隔值

问题描述

我必须将 FlightDate ， FlightNumbers 作为参数传递到Oracle存储过程，如下所示。

CREATE OR REPLACE PROCEDURE GetPaxDetails（
FlyingDate IN PAX_DETAILS.FlightDate％TYPE，
FlightNumbers IN VARCHAR（300））
IS
BEGIN
- 假设我们已经收到** FlightNumbers **为'0620,0712,0154'
- 基本上我想选择所有乘客详情（PAX_DETAILS ）
- 对于作为参数传递的那些航班（** FlightNumbers **）
- 对于指定的日期
- 在此过程中，查询应生成如下：
SELECT *
FROM PAX_DETAILS
WHERE TO_DATE（FlightDate，'DDMONYY'）在TO_DATE（FlyingDate，'DDMONYY'）
和TO_DATE（FlyingDate，'DDMONYY'）+ 1
AND FlightNo IN（'0620,0712,0154'）

END


我不明白如何将 FlightNumbers 参数转换为和FlightNo IN（'0620,0712,0154'）

解决方案

作为一种方法，和可以使用正则表达式函数将源字符串拆分为表行，然后可以轻松包含该结果在 IN 子句中：

   - 拆分逗号分隔字符串
从双
中选择regexp_substr（FlightNumbers，'[^，] +'，1，level）as num 
 connect by level< = regexp_count（FlightNumbers，'[^，] +'） 
  

例如，如果你输入 FlightNumbers 作为'0620,0712,0154'，上述查询将产生以下结果：

  NUM 
  - ------- 
 0620 
 0712 
 0154 
  

现在，您的最终查询将是：

 与数字（num）as（
 select regexp_substr FlightNumbers，'[^，] +'，1，level）as num 
 from dual 
 connect by level< = regexp_count（FlightNumbers，'[^，] +'）
）
 SELECT *  - 不要忘记INTO子句
 FROM PAX_DETAILS 
 WHERE TO_DATE（FlightDate，'DDMONYY'）在TO_DATE（FlyingDate，'DDMONYY'）
和TO_DATE（FlyingDate， DDMONYY'）+ 1 
 AND FlightNo IN（从数字中选择num 
）; 
  

I have to pass FlightDate, FlightNumbers as parameters to Oracle Stored procedure like below.

CREATE OR REPLACE PROCEDURE GetPaxDetails(
   FlyingDate IN PAX_DETAILS.FlightDate%TYPE,
   FlightNumbers IN VARCHAR(300))
IS
BEGIN
    -- Assume that we've received **FlightNumbers** as '0620,0712,0154'
    -- Basically I am trying to select all passenger details (PAX_DETAILS)
    -- for those flights passed in as a parameter(**FlightNumbers**)
    -- for the specified dates
    -- In this procedure the query should be generated something like below:
    SELECT *
      FROM PAX_DETAILS
     WHERE TO_DATE(FlightDate,'DDMONYY') BETWEEN TO_DATE(FlyingDate,'DDMONYY')
                                             AND TO_DATE(FlyingDate,'DDMONYY') + 1
       AND FlightNo IN ('0620,0712,0154')

END

I am not understanding how to convert FlightNumbers parameter to something like AND FlightNo IN ('0620,0712,0154') inside the query.

解决方案

As one of the approaches, the regexp_substr() and regexp_count() regular expression functions can be used to split the source string into table rows and then you can easily include that result in your IN clause:

-- splitting a comma separated string
select regexp_substr(FlightNumbers, '[^,]+', 1, level) as num
  from dual
connect by level <= regexp_count(FlightNumbers, '[^,]+')

So, for instance, if you pass in FlightNumbers as '0620,0712,0154', the above query will produce the following result:

NUM
--------
0620
0712
0154

Having that at hand, your final query would be:

with numbers(num) as(
   select regexp_substr(FlightNumbers, '[^,]+', 1, level) as num
     from dual
  connect by level <= regexp_count(FlightNumbers , '[^,]+')
 )
SELECT *           -- do not forget INTO clause
  FROM PAX_DETAILS
 WHERE TO_DATE(FlightDate,'DDMONYY') BETWEEN TO_DATE(FlyingDate,'DDMONYY')
                                         AND TO_DATE(FlyingDate,'DDMONYY') + 1
   AND FlightNo IN (select num
                      from numbers);

这篇关于如何在Oracle存储过程中获取逗号分隔值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！