#oracle #plsql
#Oracle #plsql
Вопрос:
Это мой первый SP в Oracle.
create or replace PROCEDURE SEARCH(R1 OUT SYS_REFCURSOR,
UserID IN VARCHAR2,
Name IN VARCHAR2,
FromDate IN VARCHAR2,
ToDate IN VARCHAR2
)
IS
BEGIN
OPEN R1 FOR
SELECT * FROM USER WHERE id = NVL( UserID, id )
and ((LASTNAME =NVL(Name,LASTNAME)) OR( FIRSTNAME =NVL(Name, FIRSTNAME)))
and ( to_char(releaseddate, 'mm/dd/rrrr') between FromDate and ToDate)
order by RELEASEDDATE desc
FETCH FIRST 100 ROWS ONLY;
END SEARCH;
В моей таблице у меня есть столбцы Id, FirstName, LastName и ReleasedDate. Эти столбцы не зависят друг от друга. Я использовал NVL для обработки столбцов таблицы, но столбец RleasedDate зависит от входных параметров FromDate и To date. Я пытался использовать NVL, но у меня возникли проблемы. Я получаю данные в формате mm / dd / rrrr. Есть ли какой-либо способ использовать NVL для Releaseddate?
В настоящее время я получаю все данные из таблицы, в которой releaseddate равно null, и я пытаюсь этого избежать.
Заранее спасибо за помощь.
and ( to_char(releaseddate, 'mm/dd/rrrr') between FromDate and ToDate)
Комментарии:
1. Чего вы пытаетесь достичь; если один из аргументов процедуры равен null, тогда не фильтруйте связанный столбец таблицы, используя это значение? Что делать, если FromDate имеет значение, но ToDate равно null, или наоборот? (Использование nvl для имен не будет совпадать, если и значение столбца, и параметр равны нулю, но для вас это может не быть проблемой.) У вас также есть конфликт имен с
id— сделайте имена параметров отличными от имен столбцов, например, с префиксом; и почему from/для параметров строк, а не дат?2. @AlexPoole все столбцы независимы друг от друга. Я просто извлекаю данные, по крайней мере, если есть один ненулевой входной параметр. если значение From date равно null, я использую значение To date. Если значения From date и To date равны нулю, я не извлекаю данные с помощью releaseddate .
3. @AlexPoole Я изменил имя входного параметра Id и обновил.
4. Помимо других ваших вопросов: если вы имеете в виду, что аргументы, которые вы получаете, FromDate и ToDate, являются строками (в любом формате), но столбец releaseddate имеет тип данных date (как и должно быть!), Тогда не преобразуйте releaseddate в строку. Вместо этого преобразуйте аргументы FromDate и ToDate в даты с помощью функции TO_DATE . (ЛУЧШЕ: напишите процедуру для приема ДАТ для этих двух параметров; пусть вызывающий объект преобразует строки в даты, это не входит в обязанности вашей процедуры делать это за них.)
5. Кроме преобразований между типами данных… Допустим, все уже преобразовано в даты. Вы хотите что-то вроде
(releaseddate >= FromDate or FromDate is null) AND (releaseddate <= ToDate or ToDate is null). Нет необходимости в NVL.
Ответ №1:
Я бы не стал использовать nvl для этого, отчасти потому, что это исключило бы значения столбцов, которые равны null. Я бы явно протестировал нулевые аргументы; что-то вроде:
OPEN R1 FOR
SELECT * FROM USER
WHERE (UserID IS NULL OR id = UserID)
AND (Name IS NULL OR LASTNAME = Name OR FIRSTNAME = Name)
AND (FromDate IS NULL OR releaseddate >= TO_DATE(FromDate, 'dd/mm/rrrr'))
AND (ToDate IS NULL OR releaseddate <= TO_DATE(ToDate, 'dd/mm/rrrr'))
ORDER BY RELEASEDDATE DESC
FETCH FIRST 100 ROWS ONLY;
При этом releasedate значения столбца сравниваются как дата, а не преобразуются в строку; вместо этого строковые аргументы преобразуются в даты, чтобы соответствовать типу данных столбца, что более эффективно. Было бы лучше, чтобы аргументы процедуры были объявлены как даты и чтобы вызывающий объект предоставлял допустимое значение.
Комментарии:
1. Спасибо за помощь. Я протестирую это изменение.
Ответ №2:
Не пытайтесь преобразовать даты в символьные строки, чтобы сравнить их — вы просто получите неожиданные результаты. Вместо этого преобразуйте символьные строки в даты.
Поскольку RELEASEDATE равно НУЛЮ, я бы просто использовал условие для проверки этого. Предполагая, что вы хотите включить строки, в которых RELEASEDATE равно NULL, я предлагаю:
create or replace PROCEDURE SEARCH(R1 OUT SYS_REFCURSOR,
UserID IN VARCHAR2,
Name IN VARCHAR2,
FromDate IN VARCHAR2,
ToDate IN VARCHAR2)
IS
BEGIN
OPEN R1 FOR
SELECT *
FROM USER
WHERE id = NVL( UserID, id ) and
( LASTNAME = NVL(Name, LASTNAME) OR
FIRSTNAME = NVL(Name, FIRSTNAME) ) and
( REALEASEDDATE IS NULL OR
REALEASEDDATE between TO_DATE(FromDate, 'mm/dd/rrrr')
and TO_DATE(ToDate, 'mm/dd/rrrr') )
order by REALEASEDDATE desc
FETCH FIRST 100 ROWS ONLY;
END SEARCH;