无法在 where 子句中调用函数

Unable to call function in where clause

提问人：SKK 提问时间：10/20/2023 最后编辑：AbraSKK 更新时间：10/21/2023 访问量：120

问： 

CREATE OR REPLACE FUNCTION getFlightSeats (v_seatRange IN VARCHAR2)
 RETURN VARCHAR2
IS
  seat_number_template  seat_num_collection;
  seat_numbers          seat_num_collection;
  result_query          VARCHAR2(200);
  condition             VARCHAR2(200);
--create or replace type seat_num_collection AS table of VARCHAR2(60);
BEGIN
  IF v_seatRange LIKE '%-%' THEN
    SELECT comma_split
    BULK COLLECT INTO seat_number_template
    FROM (
           SELECT regexp_substr(v_seatRange, '[^,]+', 1, level) as comma_split
             FROM dual
             connect by regexp_substr(v_seatRange, '[^,]+', 1, level) is not null
         );
    FOR i IN 1..seat_number_template.COUNT
    LOOP
      DBMS_OUTPUT.PUT_LINE('seat_number_template inside loop::::: ' || seat_number_template(i));
      SELECT hiphen_split
      BULK COLLECT INTO seat_numbers
      FROM (
             SELECT regexp_substr(seat_number_template(i), '[^-]+', 1, level) as hiphen_split
             FROM dual
             CONNECT BY regexp_substr(seat_number_template(i), '[^-]+', 1, level) is not null
           );
      IF (i = 1) THEN
        condition := condition || '(seat_number BETWEEN ' || '''' || seat_numbers(1)|| '''' || ' AND ' || '''' || seat_numbers(2)|| '''' || ')';
        DBMS_OUTPUT.PUT_LINE('Condition::::: ' || condition);
      --FOR j IN 1..seat_numbers.COUNT LOOP
      --DBMS_OUTPUT.PUT_LINE('seat_numbers inside loop::::: '||seat_numbers(j));
      --END LOOP;
      ELSE
        condition := condition || ' OR (seat_number BETWEEN ' || '''' || seat_numbers(1)|| '''' || ' AND ' || '''' || seat_numbers(2)|| '''' || ')';
        DBMS_OUTPUT.PUT_LINE('Condition::::: ' || condition);
      END IF;
    END LOOP;
    condition := '(' || condition || ')';
  END IF;
--condition to check only comma and without hiphen
  IF REGEXP_LIKE(v_seatRange, '[[:alnum:]]') AND v_seatRange NOT LIKE '%-%' THEN
    condition := replace('''' || v_seatRange, ',', ''',''') || '''';
    DBMS_OUTPUT.PUT_LINE('Condition::::: ' || condition);
    condition := 'seat_number IN ' || '(' || condition || ')';
  END IF;
  result_query := condition;
  DBMS_OUTPUT.PUT_LINE('final  result_query::::: ' || result_query);
  return result_query;
END;

如果用户将函数称为

select getFlightSeats('3A,5A,7C')` from dual;

功能结果：seat_number IN （'3A'，'5A'，'7C'）

如果用户将函数称为

select getFlightSeats('3A-5B,6A-9B') from dual;

函数结果：（（seat_number 介于 '3A' 和 '5B' 之间）或 （seat_number 介于 '6A' 和 '9B' 之间））

我面临的问题是如何在where子句中调用此函数，如下所示：

SELECT seat_number
  FROM SEAT_TABLE
 WHERE getFlightSeats('3A,5B,6B')

它不起作用，因为它是预期的条件。如何在选择查询中实现此目的？

我得到了解决方案（从这个答案）：

您宁愿选择返回一个集合并将其与表函数一起使用，无论是作为联接还是在子查询中。

但是在这里我无法将字符串附加到集合中。

SQL Oracle 函数 PLSQL

评论

2赞 Alex Poole 10/20/2023
将列表转换为集合相当简单，如前面的答案所示。转换航程是一个更大的问题，除非你对飞机有所了解，或者至少有一排最大数量的座位 - 这仍然会将可能不相关的值放入集合中。是否包括3F？取决于飞机及其配置方式......（你现在尝试做的事情已经有问题了 - 会匹配等等 - 所以在动态 SQL 中使用它，比如说，无济于事。'3A,5A,7C'3A-5BBETWEEN '3A' AND '5B'30A
0赞 Erich Kitzmueller 10/20/2023
若要将生成的 String 用作 select 语句的 where 子句，需要使用动态 SQL。查看套餐dbms_sql了解更多详情：docs.oracle.com/en/database/oracle/oracle-database/19/arpls/...或者，您可以考虑更改您的函数，即给定座位号和所需座位的范围或集合，如果座位匹配，则返回 1，如果不匹配，则返回 0，因此您可以这样做select seat_number from seat_table where seatMatches(seat_number, '3A,5B,6B')=1
0赞 SKK 10/20/2023
感谢您的回答。但是我需要返回字符串。我怎样才能使用如下功能。从SEAT_TABLE中选择seat_number，其中 getFlightSeats（'3A，5B，6B'） 和 flight_number='87373' 按 seat_number ASC 排序;我在主程序中调用此查询
2赞 MT0 10/20/2023
你真的确定要以形式生成一些东西吗？因为 seat 将由该查询返回（在其他席位中） 小提琴WHERE seat_number BETWEEN '6D' AND '7A''71A'

答：

6赞 MT0 10/20/2023 #1

不要尝试生成动态 SQL。使用一个函数并生成范围之间的行（假设您知道每行中有多少个席位，您可能可以完全在 PL/SQL 中完成，这样您就可以避免任何上下文切换）：PIPELINED

CREATE TYPE string_list IS TABLE OF VARCHAR2(20);

CREATE FUNCTION getFlightSeats (
  v_seatRange IN VARCHAR2,
  v_maxseat   IN VARCHAR2 DEFAULT 'D'
) RETURN string_list PIPELINED
IS
  spos PLS_INTEGER := 1;
  dpos PLS_INTEGER;
  epos PLS_INTEGER;
  lseat VARCHAR2(20);
  useat VARCHAR2(20);
  lx PLS_INTEGER;
  ly PLS_INTEGER;
  ux PLS_INTEGER;
  uy PLS_INTEGER;
BEGIN
  IF v_seatRange IS NULL THEN
    RETURN;
  END IF;
  LOOP
    epos := INSTR(v_seatrange, ',', spos);
    dpos := INSTR(v_seatrange, '-', spos);
    IF epos > spos AND (dpos = 0 OR dpos > epos) THEN
      PIPE ROW (SUBSTR(v_seatrange, spos, epos - spos));
    ELSIF epos = 0 AND dpos = 0 THEN
      PIPE ROW (SUBSTR(v_seatrange, spos));
    ELSE
      lseat := SUBSTR(v_seatrange, spos, dpos - spos);
      lx := TO_NUMBER(SUBSTR(lseat, 1, LENGTH(lseat) - 1));
      ly := ASCII(SUBSTR(lseat, -1, 1));
      IF epos > 0 THEN
        useat := SUBSTR(v_seatrange, dpos + 1, epos - dpos - 1);
      ELSE
        useat := SUBSTR(v_seatrange, dpos + 1);
      END IF;
      ux := TO_NUMBER(SUBSTR(useat, 1, LENGTH(useat) - 1));
      uy := ASCII(SUBSTR(useat, -1, 1));


      IF lx < ux THEN
        FOR y IN ly .. ASCII(v_maxseat) LOOP
          PIPE ROW (lx || CHR(y));
        END LOOP;
        FOR x IN lx + 1 .. ux - 1 LOOP
          FOR y IN ASCII('A') .. ASCII(v_maxseat) LOOP
            PIPE ROW (x || CHR(y));
          END LOOP;
        END LOOP;
        FOR y IN ASCII('A') .. uy LOOP
          PIPE ROW (ux || CHR(y));
        END LOOP;
      ELSE
        FOR y IN ly .. uy LOOP
          PIPE ROW (lx || CHR(y));
        END LOOP;
      END IF;
    END IF;
    
    EXIT WHEN epos = 0;
    spos := epos + 1;
  END LOOP;
END;
/

然后，对于示例数据：

CREATE TABLE seat_table (seat_number) AS
SELECT x || y
FROM   (SELECT LEVEL AS x FROM DUAL CONNECT BY LEVEL <= 20)
       CROSS JOIN
       (SELECT CHR(64 + LEVEL) AS y FROM DUAL CONNECT BY LEVEL <= 4)

您可以使用以下命令调用该函数：

SELECT seat_number
FROM   SEAT_TABLE
WHERE  seat_number MEMBER OF getFlightSeats(
                               v_seatRange => '3A,5B,6B-6D,9C-11B',
                               v_maxseat   => 'D'
                             );

输出：

SEAT_NUMBER
3安培
5乙
6乙
6C的
6D模型
9摄氏度
9D模型
20"
20!
00b
10D（10D）
11安培
11乙

小提琴

评论

0赞 Erich Kitzmueller 10/20/2023
我认为这个答案实际上比我的要好，原因在描述中给出。很有道理。
1赞 p3consulting 10/21/2023 #2

为了好玩，在 SQL 中尝试一下：

with data(str) as (
    select '3A,5B,4B-6D,9C-11B' from dual
),
exploded(id, pos, seat_row, seat_pos) as (
    select id, level as pos, to_number(regexp_substr(regexp_substr(str,'[^-]+',1,level), '\d+')) as seat_row,
        ascii(regexp_substr(regexp_substr(str,'[^-]+',1,level), '[A-Z]+')) as seat_pos
    from (
        select level as id, regexp_substr(str,'[^,]+',1,level) as str from data
        connect by level <= regexp_count(str,'[^,]+')
    )
    connect by prior id = id and level <= regexp_count(str,'[^-]+') and prior sys_guid() is not null
),
ranges(id, pos, seat_row_from, seat_pos_from, seat_row_to, seat_pos_to) as
(
    select e.*, e1.seat_row as seat_row_to, e1.seat_pos as seat_pos_to 
    from exploded e
        left join exploded e1 on e1.id = e.id and e.pos = 1 and e1.pos = 2
),
flatranges(id, pos, seat_row_from, seat_pos_from, seat_row_to, seat_pos_to) as (
    select * from (
        select id, pos, seat_row_from + level - 1 as seat_row_from, 
            case when level = 1 then seat_pos_from else 
                case when seat_row_to is not null then 
                    ascii('A')
                else 
                    ascii('A') + level - 1 
                end
            end as seat_pos_from,
            case when seat_row_to is null then seat_row_from else 
                case when seat_row_from + level - 1 < seat_row_to 
                    then seat_row_from + level - 1 else seat_row_to 
                end 
            end as seat_row_to, 
            case when seat_pos_to is null then seat_pos_from 
            else
                case when seat_row_from + level - 1 < seat_row_to then ascii('D') else seat_pos_to end
            end as seat_pos_to
        from ranges 
        connect by prior id = id and prior pos = pos
            and seat_row_from + level - 1 <= seat_row_to and prior sys_guid() is not null
    )
)
select seat from (
    select distinct seat_row_from, seat_row_from || chr(seat_pos_from + level - 1) as seat
    from flatranges
    connect by seat_pos_from + level - 1 <= seat_pos_to
        and prior id = id and prior pos = pos and prior sys_guid() is not null
)
order by seat_row_from, seat
;

3A
4B
4C
4D
5A
5B
5C
5D
6A
6B
6C
6D
9C
9D
10A
10B
10C
10D
11A
11B

上一个：如何在多值属性上强加非 null？

下一个：使用 dbms_sql.parse 解析APEX_ITEM。TEXT_FROM_LOV