oracle 无法在where子句中调用函数

rhfm7lfc 于 2023-11-17 发布在 Oracle

关注(0)|答案(2)|浏览(133)

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 BETWEEN '3A' AND '5B'）OR（seat_number BETWEEN '6A' AND '9B'））
我面临的问题是如何在 where 子句中调用这个函数，如下所示：

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

型
它不工作，因为它的预期条件。我如何在选择查询中实现这一点？
我得到了解决方案（来自this answer）：
您更愿意选择返回一个集合并将其与表函数一起使用，无论是作为连接还是在子查询中。
但是在这里我无法将字符串追加到集合中。

oracle

来源：https://stackoverflow.com/questions/77330145/unable-to-call-function-in-where-clause

2条答案

按热度按时间

3gtaxfhh1#

为了好玩，在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

字符串

赞(0）回复(0）举报 2023-11-17

iyr7buue2#

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

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

型
其输出：
| 座位号|
| --|
| 3A|
| 5B|
| 6B|
| 6C|
| 6D|
| 9C|
| 9D|
| 10A|
| 10B|
| 10C|
| 10D|
| 11A|
| 11B|
fiddle

赞(0）回复(0）举报 2023-11-17

我来回答

oracle 无法在where子句中调用函数

2条答案

相关问题

热门标签

最新问答