Блог

Главная - Вопросы по программированию - Как передать имя столбца в качестве параметра в хранимой процедуре в PL/SQL?

Как передать имя столбца в качестве параметра в хранимой процедуре в PL/SQL?

#sql #oracle #stored-procedures #jdbc #plsql

Вопрос:

Я хочу передать что-то вроде p.lastname или p.first_name в query_type параметре, чтобы я мог упорядочить по тому, что я хочу; и, соответственно, query_value содержал бы что-то вроде UPPER('%Smith%') или UPPER('%Henry%') в зависимости от того, что я ввел query_type .

Но когда я передаю их в виде строк, курсор возвращается пустым.

Какие-нибудь советы? В идеале я бы не хотел избавляться от курсора.

ps. search_cursor был объявлен как КУРСОР ССЫЛКИ в заголовке пакета.

 PROCEDURE test_proc(
          company_id IN NUMBER,
          query_type IN VARCHAR2,
          query_value IN VARCHAR2,
          result_limit IN NUMBER,
          cur OUT search_cursor) AS
BEGIN

OPEN cur FOR
select *
from (SELECT p.first_name as "first_name",
             p.surname as "surname",
             row_number()
      OVER (ORDER BY query_type asc) rn
      FROM person p, company c
      WHERE c.employee_id = p.person_id
            AND c.id = company_id
            AND query_type LIKE query_value
      )

where rn BETWEEN 1 AND result_limit;

END test_proc;

Ответ №1:

Динамический SQL. Остерегайтесь SQL-инъекции.

Поскольку у меня нет ваших таблиц, я использовал примерную схему Скотта.

Функция, которая возвращает курсор ссылки (это то, что вы тоже делаете, только в качестве параметра OUT процедуры):

 SQL> create or replace function f_test
  2    (query_type  in varchar2,
  3     query_value in varchar2
  4    )
  5    return sys_refcursor
  6  is
  7    l_str varchar2(500);
  8    rc    sys_refcursor;
  9  begin
 10    l_str := 'select e.ename, e.job, ' ||
 11             '  row_number() over (order by ' || query_type || ' asc) rn ' ||
 12             'from emp e join dept d on e.deptno = d.deptno ' ||
 13             'where ' || query_type || ' like ' || query_value;
 14    open rc for l_str;
 15    return rc;
 16  end;
 17  /

Function created.

Тестирование:

 SQL> select f_test('e.ename', q'[upper('%King%')]') from dual;

F_TEST('E.ENAME',Q'[
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

ENAME      JOB               RN
---------- --------- ----------
KING       PRESIDENT          1


SQL>

Комментарии:

1. эй, спасибо за ответ! я пытаюсь понять, как вы выполнили функцию — что dual означает ваш запрос? Это посылка или столик?

2. Пожалуйста. DUAL, вероятно, самая известная таблица во всем мире Oracle. Чтобы извлечь из функции в SQL, вы должны ВЫБРАТЬ значение функции ИЗ какой-либо таблицы; DUAL-хороший выбор, так как он содержит только 1 строку. Если бы вы выбрали из таблицы, например, с миллионом строк, вы получили бы миллион повторяющихся результатов.

3. большое спасибо. Мне удалось запустить хранимую процедуру, но без использования переменной varchar2 l_str . Я только что открыл курсор OPEN cur FOR 'select * from (SELECT p.first_name as "first_name", p.surname as "surname", row_number() OVER (ORDER BY ' || query_type || ' asc) rn FROM person p, company c WHERE c.employee_id = p.person_id AND c.id = company_id AND ' || query_type || ' LIKE ' || query_value || ') where rn BETWEEN 1 AND ' || result_limit;

4. Пожалуйста. Я предпочитаю хранить инструкцию SELECT в переменной, чтобы я мог отобразить ее на экране(с помощью DBMS_OUTPUT. PUT_LINE), потому что — если это сложно и зная, что динамический SQL трудно отлаживать — полезно на самом деле увидеть , что вы пытаетесь запустить. Если вы сразу же переведете его в режим НЕМЕДЛЕННОГО ВЫПОЛНЕНИЯ, он может сработать — а может и не сработать (и тогда вам все равно придется его отлаживать и делать то, что я уже сделал — сохранять оператор в переменной и отображать его.

Метки: