如何在存储过程 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屋!