如何忽略存储过程预言机中的空参数

Question

我创建了一个存储过程，用户可以在其中插入参数中的 1 个或多个值

create or replace procedure  MyProcerdure
(
title Film.Title%Type,
country Film.country%Type,
language  Film.language%Type,
category  Film.category%Type,
refCursor OUT SYS_REFCURSOR ) 
AS 
begin 
 OPEN refCursor FOR
select Film.Title as FilmTitle,
      Film.language as language
       Film.category
FROM Film 
Where Film.language=language
AND Film.category=category
AND Film.Country=country
//etc...

但是我想允许这样一个事实，即用户不必将它们全部填写并在参数中传递，这意味着如果用户只输入语言而没有其他任何东西，则返回正确的语言，假设他输入了国家和语言，因此结果应该得到 WHERE 语言和国家等于他插入的内容

是否可以使用 oracle 在存储过程中建立这样的机制？

谢谢

Answer 1

只需在查询中添加一些逻辑来处理参数可以为 null 的事实：

CREATE OR REPLACE PROCEDURE MyProcerdure(
                                         p_title             Film.Title%TYPE,
                                         p_country           Film.country%TYPE,
                                         p_language          Film.language%TYPE,
                                         p_category          Film.category%TYPE,
                                         po_refCursor    OUT SYS_REFCURSOR
                                        ) AS
BEGIN
    OPEN po_refCursor FOR
        SELECT Film.Title AS FilmTitle, Film.language AS language, Film.category
        FROM Film
        Where ( p_title    is null or Film.title    = p_title   )
          AND ( p_country  is null or Film.country  = p_country )
          AND ( p_language is null or Film.language = p_language )
          AND ( p_category is null or Film.category = p_category );
END;

Answer 2

实现此目的的最佳方法是使用动态 SQL。您可以有条件地连接正确的筛选器。您还需要在未提供值时连接备用版本。

例如，以下内容允许您过滤或放入编译器将忽略但在动态 SQL 中仍具有正确数量的绑定变量的语句。

CREATE OR REPLACE PROCEDURE MyProcerdure
(
   title     file.title%TYPE,
   country   file.country%TYPE,
   language  file.language%TYPE,
   category  file.category%TYPE,
   refCursor OUT SYS_REFCURSOR
) IS
   l_stmt VARCHAR2(4000);
BEGIN
   l_stmt := 'SELECT f.title AS filetitle,'||
             '       f.language AS language,'||
             '       f.category'||
             '  FROM file f'||
             ' WHERE 1 = 1';
   IF title IS NOT NULL THEN
      l_stmt := l_stmt || ' AND f.title = :title';
   ELSE
      l_stmt := l_stmt || ' AND (1=1 OR :title IS NULL)';
   END IF;
   -- The others would be done similarly
   OPEN refCursor FOR l_stmt USING title, -- The others would go the same order as above
END;
/