无法在WHERE子句调用getFlightSeats函数的技术问询

1. 创建自定义字符串集合类型 #

Oracle需要自定义集合类型来存储多个座位号，先创建一个表类型：

CREATE OR REPLACE TYPE seat_varchar2_tab AS TABLE OF VARCHAR2(10);
/

2. 重写getFlightSeats函数，返回座位号集合 #

修改原函数逻辑，解析输入的单个座位或范围格式，生成所有匹配的座位号并放入集合返回：

CREATE OR REPLACE FUNCTION getFlightSeats(p_seats IN VARCHAR2)
RETURN seat_varchar2_tab
IS
  v_seat_list seat_varchar2_tab := seat_varchar2_tab();
  v_seat_str VARCHAR2(1000);
  v_start_seat VARCHAR2(10);
  v_end_seat VARCHAR2(10);
  v_start_row NUMBER;
  v_end_row NUMBER;
  v_start_col CHAR(1);
  v_end_col CHAR(1);
  v_current_row NUMBER;
  v_current_col CHAR(1);
BEGIN
  -- 拆分逗号分隔的每个座位项
  FOR seat_item IN (SELECT REGEXP_SUBSTR(p_seats, '[^,]+', 1, LEVEL) AS item
                    FROM DUAL
                    CONNECT BY REGEXP_SUBSTR(p_seats, '[^,]+', 1, LEVEL) IS NOT NULL)
  LOOP
    v_seat_str := TRIM(seat_item.item);
    -- 判断是单个座位还是范围格式
    IF INSTR(v_seat_str, '-') > 0 THEN
      -- 拆分范围的起始和结束座位
      v_start_seat := TRIM(SUBSTR(v_seat_str, 1, INSTR(v_seat_str, '-') - 1));
      v_end_seat := TRIM(SUBSTR(v_seat_str, INSTR(v_seat_str, '-') + 1));
      
      -- 提取行号（数字部分）和列号（字母部分），假设座位格式为「数字+单个字母」
      v_start_row := TO_NUMBER(REGEXP_SUBSTR(v_start_seat, '\d+'));
      v_start_col := REGEXP_SUBSTR(v_start_seat, '[A-Z]');
      v_end_row := TO_NUMBER(REGEXP_SUBSTR(v_end_seat, '\d+'));
      v_end_col := REGEXP_SUBSTR(v_end_seat, '[A-Z]');
      
      -- 遍历行范围
      FOR v_current_row IN v_start_row .. v_end_row LOOP
        -- 遍历列范围
        v_current_col := v_start_col;
        WHILE v_current_col <= v_end_col LOOP
          v_seat_list.EXTEND;
          v_seat_list(v_seat_list.LAST) := v_current_row || v_current_col;
          v_current_col := CHR(ASCII(v_current_col) + 1);
        END LOOP;
      END LOOP;
    ELSE
      -- 单个座位直接加入集合
      v_seat_list.EXTEND;
      v_seat_list(v_seat_list.LAST) := v_seat_str;
    END IF;
  END LOOP;
  
  RETURN v_seat_list;
END;
/

3. 在查询中使用改造后的函数 #

通过TABLE()函数将集合转换为可查询的表，配合IN子句或JOIN实现筛选：

方式1：IN子句筛选 #

SELECT seat_number 
FROM SEAT_TABLE 
WHERE seat_number IN (SELECT COLUMN_VALUE FROM TABLE(getFlightSeats('3A,5B,6B')));

方式2：JOIN关联筛选 #

SELECT s.seat_number 
FROM SEAT_TABLE s
JOIN TABLE(getFlightSeats('3A-5B,6A-9B')) t ON s.seat_number = t.COLUMN_VALUE;

注意事项 #

• 如果你的座位列号是多字母格式（如AA、AB），需要调整函数中的正则表达式和列遍历逻辑，适配多字母列的递增规则。
• 可根据实际需求添加输入格式校验逻辑，避免非法格式导致函数报错。

内容的提问来源于stack exchange，提问作者SKK