如何在存储过程 Oracle 中忽略空参数 [英] How to ignore null parameter in a Stored Procedure Oracle

问题描述

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

I have created a stored procedure where the user can inset 1 or multiple values in the parameter

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 语言和国家等于他插入的内容

But I want to allow the fact that the user doesn't have to fill them all and pass them in parameter , which means if the user only enter the language without anything else , return the proper language , and let's say he entered country and language so the result should get WHERE language and country is equal to what he inserted

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

Is it possible to make such a mechanism in a stored procedure using oracle ?

谢谢

推荐答案

您可以简单地在查询中添加一些逻辑来处理参数可以为 null 的事实:

You can simply add some logic in your query to handle the fact that parameters can be 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;

这篇关于如何在存储过程 Oracle 中忽略空参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！