#oracle #plsql #oracle-ords #oracle-rest-data-services
Вопрос:
Я пишу обработчик REST в ПОРЯДКЕ. Конечная точка URL-адреса должна позволять выполнять запросы с рядом необязательных параметров. Одним из способов написания кода PL/SQL может быть:
DECLARE cur SYS_REFCURSOR BEGIN OPEN cur FOR SELECT * FROM MYTABLE WHERE (:param1 IS NULL OR column1 = :param1) AND (:param2 IS NULL OR column2 = :param2); :resultSetOut := cur; END; Другой способ, который, как я думал, может быть немного более эффективным, — это создать строку SQL
DECLARE cur SYS_REFCURSOR sqlString VARCHAR2(200) BEGIN sqlString := 'SELCT * FROM MYTABLE WHERE 1=1'; IF (:param1 IS NOT NULL) THEN sqlString := sqlString || ' AND COLUMN1=:param1'; END IF; IF (:param2 IS NOT NULL) THEN sqlString := sqlString || ' AND COLUMN1=:param2'; END IF; OPEN cur FOR sqlString USING :param1, :param2; :resultSetOut := cur; END; Однако эта конструкция строки в конце должна быть статически привязана к переменным, что, по сути, делает все переменные обязательными и необязательными в запросе URL.
Для блока PL/SQL, который допускает динамические предложения WHERE, можно ли использовать первый способ, показанный здесь? Есть ли способ построить строку и привязать, аналогичный второму способу, показанному здесь?
Комментарии:
1. В чем проблема с первым кодом? Это довольно распространенный способ выражения необязательных параметров в SQL, и его производительность будет такой же хорошей, как и в обычном запросе. Здесь нет необходимости вводить динамический SQL.
2. Следует иметь в виду, что динамический sql генерирует гораздо больше различных операторов sql, каждый из которых должен быть проанализирован механизмом sql и создан план выполнения. С точки зрения базы данных первый вариант имеет больше смысла: требуется только один запрос и один план выполнения
Ответ №1:
Ты на правильном пути:
IF (:param1 IS NOT NULL) THEN sqlString := sqlString || ' AND COLUMN1=:param1'; Else sqlString := sqlString || ' and (1=1 or :param1 is null) '; END IF; Теперь вам нужно одинаковое количество переменных привязки, несмотря ни на что, и оптимизатор Oracle будет знать, что 1 всегда равно 1, поэтому ему не нужно учитывать другой предикат.
Комментарии:
1. В этом случае, конечно, это не более эффективно или даже менее эффективно, чем
(:param1 IS NULL OR column1 = :param1)оценка?2. Это более эффективно. Oracle должна создать план, который действителен для SQL, независимо от того, какое значение используется в переменной привязки. В вашем случае он не будет оптимизировать запрос с использованием индекса, потому что возможно, что привязка равна нулю. В моем случае он знает, что он фильтрует по равенству или вообще не фильтрует.
Ответ №2:
Вы можете сделать это эквивалентным способом без динамического SQL:
BEGIN IF :param1 IS NOT NULL AND :param1 IS NOT NULL THEN OPEN :resultSetOut FOR SELECT * FROM MYTABLE WHERE COLUMN1=:param1 AND COLUMN2=:param2; ELSIF :param1 IS NOT NULL THEN OPEN :resultSetOut FOR SELECT * FROM MYTABLE WHERE COLUMN1=:param1; ELSIF :param2 IS NOT NULL THEN OPEN :resultSetOut FOR SELECT * FROM MYTABLE WHERE COLUMN2=:param2; ELSE OPEN :resultSetOut FOR SELECT * FROM MYTABLE; END IF; END; Комментарии:
1. Пример кода, который я показал, упрощен. Это, если структура будет невыполнимой, если есть дополнительные необязательные параметры. Количество комбинаций слишком велико.
2. @DavidMin Тогда вы, вероятно, захотите использовать
(:param1 IS NULL OR column1 = :param1), а не пытаться использовать динамический SQL. Однако вы спрашиваете: «Есть ли способ построить строку и привязать ее аналогично второму способу [без привязки всех параметров], показанному здесь?» Это делает это без привязки всех параметров, и, да, это усложняется с большим количеством параметров, но вы либо включаете все параметры (ваш первый вариант), либо вам нужно индивидуально обрабатывать, какие параметры включены и опущены, а затем вы получаете множество разветвленныхIFоператоров. Я не вижу середины.