Блог

в Oracle DB (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           |
 ----------- ----------- --------------- 
  

все id-столбцы имеют тип 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, который может запрашивать одну из 2 таблиц каждый раз на основе некоторого условия, поэтому я в конечном итоге напишу одну функцию, которая использует один запрос вместо 2 функций, что-то вроде:

   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;
  

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

 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 запросы из всех возможных таблиц; каждая таблица вносит свой вклад или нет, в зависимости от значения некоторого параметра, так что вы получаете только строки из нужной вам таблицы на основе значения параметра.

Одним из вариантов может быть использование динамического 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;
/
  

Demo

где

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

Вы также можете использовать динамический 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;