Запрос таблицы A или таблицы B на основе некоторого условия

в БД Oracle (12) у меня есть 2 таблицы:

таблица: STEP_DETAILS

+-----------+---------+---------------+
| record_id | step_id | material_type |
+===========+=========+===============+
| 1         | 1       | in            |
+-----------+---------+---------------+
| 2         | 1       | in            |
+-----------+---------+---------------+
| 3         | 1       | out           |
+-----------+---------+---------------+
| 4         | 2       | in            |
+-----------+---------+---------------+
| 5         | 2       | out           |
+-----------+---------+---------------+
| 6         | 2       | out           |
+-----------+---------+---------------+

таблица: ACTIONS_DETAILS

+-----------+-----------+---------------+
| record_id | action_id | material_type |
+===========+===========+===============+
| 1         | 11        | in            |
+-----------+-----------+---------------+
| 2         | 11        | out           |
+-----------+-----------+---------------+
| 3         | 12        | in            |
+-----------+-----------+---------------+
| 4         | 12        | out           |
+-----------+-----------+---------------+

все столбцы идентификаторов имеют тип INTEGER.

Мне нужно подсчитать входные материалы для обеих таблиц. в блоке PL/SQL у меня есть следующие функции, каждая из которых имеет «почти» один и тот же запрос:

--count from step_details:
FUNCTION get_step_input_count(p_step_id  step_details.step_id%TYPE)
  RETURN INTEGER
  IS
    l_count INTEGER := 0;
  BEGIN
    SELECT COUNT(1)
      INTO l_count
      FROM step_details
     WHERE step_id = p_step_id
       AND material_type = 'in';

    RETURN l_count;
  END get_step_input_count;

--count from action_details:
  FUNCTION get_action_input_count(p_action_id  action_details.action_id%TYPE)
  RETURN INTEGER
  IS
    l_count INTEGER := 0;
  BEGIN
    SELECT COUNT(1)
      INTO l_count
      FROM action_details
     WHERE action_id = p_action_id
       AND material_type = 'in';

    RETURN l_count;
  END get_action_input_count;

возможно ли написать один единственный оператор SELECT, который может каждый раз запрашивать одну из двух таблиц на основе некоторого условия, поэтому я в конечном итоге напишу одну функцию, которая использует один запрос вместо двух функций, что-то вроде:

  FUNCTION get_input_count(p_parent_id  integer,
                           p_from       varchar2)
  RETURN INTEGER
  IS
    l_count INTEGER := 0;
  BEGIN
    SELECT COUNT(1)
      INTO l_count
      FROM (when p_from = 'S' then 'step_details'
            when p_from = 'A' then 'action_details')
     WHERE (when p_from = 'S' then 'step_id   = p_parent_id'
            when p_from = 'A' then 'action_id = p_parent_id')
       AND material_type = 'in';

    RETURN l_count;
  END get_input_count;

sql oracle plsql stored-functions
person Ezzedeen Saghier    schedule 13.10.2020    source источник

Ответы (3)


arrow_upward
1
arrow_downward

Вы можете попробовать что-то вроде следующего:

select sum(num_rows)
from
(
    select count(*) as num_rows
    from tab1                        /* first table */
    where :param = 1
    union all
    select count(*) as num_rows
    from tab2                        /* second table */
    where :param = 2 
)

Здесь вы используете один select, который упаковывает UNION ALL запросов из всех возможных таблиц; каждая таблица дает свой вклад или нет, в зависимости от значения некоторого параметра, так что вы получаете только строки из таблицы, которую хотите, на основе значения параметра.

person Aleksej    schedule 13.10.2020
comment
что произойдет, если один из объединенных запросов вызовет исключение NO_DATA_FOUND? скажем, мы хотим считать с первой таблицы, поэтому первый запрос работает хорошо, но по какой-то причине второй запрос не удался из-за no_data_found. как это повлияет на весь функционал? - person Ezzedeen Saghier; 13.10.2020
comment
Независимо от таблиц, если один из двух запросов завершится ошибкой, вы получите нулевой результат, а не отсутствие данных; если одна таблица не дает результата, вы получаете количество записей другой. Если оба запроса дают ноль, в результате вы получите ноль - person Aleksej; 14.10.2020

arrow_upward
1
arrow_downward

Одним из вариантов может быть использование динамического SQL, состоящего из конкатенаций имен таблиц и столбцов и переменной связывания для значения, которое будет общим (p_parent_id) для запросов из каждой таблицы.

SQL> CREATE OR REPLACE FUNCTION get_input_count(p_parent_id INT, p_from VARCHAR2) RETURN INT IS
    l_count INT;
    crs     SYS_REFCURSOR;
    v_sql   VARCHAR2(32767);
    v_from  VARCHAR2(32);
    v_col   VARCHAR2(99);
BEGIN
    SELECT DECODE(p_from,'A','actions_details','S','step_details'),
           DECODE(p_from,'A','action_id','S','step_id')
      INTO v_from, v_col
      FROM dual;

     v_sql := 'SELECT COUNT(*) 
                 FROM '||v_from||' 
                WHERE material_type = ''in'' AND '||v_col||' = :prt_id';

     OPEN crs FOR v_sql USING p_parent_id; 
     LOOP       
        FETCH crs INTO l_count;
       EXIT WHEN crs%NOTFOUND;             
     END LOOP;

     CLOSE crs;

    RETURN l_count;
END;
/

Демо

куда

  • инициализация значения переменной l_count нулем является избыточной, поскольку запрос будет возвращать ноль без исключения всякий раз, когда совпадающие записи не найдены.
  • ключевое слово FUNCTION должно начинаться с CREATE [OR REPLACE]
  • окончание хранимой функции ее именем необязательно (можно пренебречь)
person Barbaros Özhan    schedule 13.10.2020

arrow_upward
0
arrow_downward

Вы также можете использовать динамический SQL, как показано ниже. В этом случае следует позаботиться о SQL-инъекциях.

sql_statement := 'select count(*)';
IF table = 'xxx'
THEN 
  sql_statement := sql_statement || ' from xxx where material_type = ''in''';
ELSIF table = 'yyy'
  sql_statement := sql_statement || ' from yyy where yyyy_type = ''in''';
END IF;
sql_statement := sql_statement blur blur;
EXECUTE IMMEDIATE sql_statement INTO l_count USING p_1;
person hoge    schedule 13.10.2020
comment
имена таблиц нельзя использовать в качестве переменных связывания в динамическом SQL. - person Barbaros Özhan; 13.10.2020