Как преобразовать Oracle SQL в хранимую процедуру, которая должна перебирать некоторые таблицы, извлекая определенное поле данных?
Мне нужно преобразовать оператор Oracle SQL в хранимую процедуру, поэтому пользователи с меньшими правами могут получить доступ к определенному полю данных:
SELECT
info_field, data_field
FROM
table_one
WHERE
some_id = '<id>' -- I need this <id> to be the procedure's parameter
UNION ALL
SELECT
info_field, data_field
FROM
table_two
WHERE
some_id = '<id>'
UNION ALL
SELECT
info_field, data_field
FROM
table_three
WHERE
some_id = '<id>'
UNION ALL
...
Учитывая, что я не специалист по SP, мне не удалось найти хорошее решение для циклического перебора всех задействованных таблиц (примерно 12).
Любые идеи были бы полезны. Спасибо большое!
Ответы 3
Ожидается ли, что среди всех этих таблиц только одна будет соответствовать данному идентификатору?
Если нет: вам нужно объяснить, что вы хотите сделать, когда есть несколько совпадений.
Если да: вы просто выполняете тот же SQL-запрос, выбирая результат в переменную, которую затем возвращаете.
Это выглядело бы примерно так:
PROCEDURE get_fields( the_id NUMBER,
info_field_out OUT table_one.info_field%TYPE,
data_field_out OUT table_one.data_field%TYPE
)
IS
BEGIN
SELECT info_field, data_field
INTO info_field_out, data_field_out
FROM (
... put your full SQL query here, using 'the_id' as the value to match against ..
);
EXCEPTION
WHEN no_data_found THEN
-- What do you want to do here? Set the outputs to NULL? Raise an error?
WHEN too_many_rows THEN
-- Is this an invalid condition?
END;
Если представление выполнит то, что вам нужно, это более простое решение. Если нет, вы можете настроить технику, которую я показал, для получения и возврата нескольких строк.
Если вы просто хотите ограничить доступ пользователей, вы можете создать представление и предоставить им выбор в представлении, но не в таблицах:
CREATE VIEW info_and_data AS
SELECT info_field, data_field
FROM table_one
UNION ALL
SELECT info_field, data_field
FROM table_two
UNION ALL
SELECT info_field, data_field
FROM table_three
...
Затем пользователи могли ввести:
SELECT info_field, data_field
FROM info_and_data
WHERE some_id = <id>
Есть и другие способы достижения вашей цели, помимо моих предложений ниже, но я бы предостерегал от разделения данных, которые действительно принадлежат одной таблице, только для реализации политики доступа к данным, которая может измениться в будущем.
Самое простое решение для ограничения количества столбцов таблицы, которые видит пользователь, - это представления этих таблиц. Используйте разные представления, которые показывают или скрывают определенные столбцы и предоставляют доступ к этим представлениям разным пользователям / ролям.
Если вы не знаете заранее, какая комбинация столбцов может быть разрешена пользователю для просмотра, вы можете использовать динамический sql: вы собираете SQL-статус в хранимой процедуре на основе прав доступа вашего пользователя (посмотрите из другого таблица, которую вы создаете для хранения этой информации), что означает, что вы включаете только правильные столбцы в часть SELECT вашего оператора. См. Это документ от Orace для получения дополнительной информации.
Если вы используете Oracle 10g, вам может показаться интересным этот Статья Oracle. Он вводит тему виртуальной частной базы данных или сокращенно VPD, где вы можете скрыть определенные строки или столбцы или даже отдельные значения столбца в зависимости от того, кто обращается к таблице.
Большое спасибо! На самом деле вы, ребята, убедили меня, что представления - лучший способ решить эту проблему.
Большое спасибо за ответ! Я думаю, мне действительно нужно получить несколько результатов, поэтому в этом случае лучшим решением будет View. Что вы думаете?