oracle 存储过程 PL SQL 如果字符串为空
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37388136/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Stored Procedure PL SQL If String Is blank
提问by smshahiran
I got a problem with Oracle Stored Procedure. The if elsestatement didnt check whether the string is blank or not. Or am I doing it wrong?
我在使用 Oracle 存储过程时遇到了问题。该if else语句没有检查字符串是否为空。还是我做错了？
create or replace PROCEDURE GET_ICECREAM
(
flavour IN VARCHAR2,
toppings IN VARCHAR2,
cursorIC OUT sys_refcursor
)
AS
dynaQuery VARCHAR2(8000);
BEGIN
dynaQuery := 'SELECT price FROM tblIceCream';
IF flavour <> '' THEN
dynaQuery := dynaQuery || ' WHERE flavour LIKE '''%''' '
ENDIF
OPEN cursorIC FOR dynaQuery;
END GET_ICECREAM;
DISCLAIMER: Above is not actual stored procedure. I'm using an example to understand concept of if else and native dynamic SQL in Oracle. So that its easier for you guys to understand ;)
免责声明：以上不是实际的存储过程。我正在使用一个示例来理解 Oracle 中 if else 和本机动态 SQL 的概念。这样你们更容易理解;)
回答by Avrajit Roy
Hope this below snippet will help you to understand how to handle with empty string and NULL values.
希望下面的代码片段能帮助您了解如何处理空字符串和 NULL 值。
SET serveroutput ON;
DECLARE
lv_var VARCHAR2(100):='';
BEGIN
IF lv_var IS NULL THEN
dbms_output.put_line('is null');
ELSE
dbms_output.put_line('av');
END IF;
END;
------------------------------------OUTPUT--------------------------------------
PL/SQL procedure successfully completed.
is null
--------------------------------------------------------------------------------
SET serveroutput ON;
DECLARE
lv_var VARCHAR2(100):='';
BEGIN
IF lv_var = '' THEN
dbms_output.put_line('is null');
ELSE
dbms_output.put_line('av');
END IF;
END;
--------------------------------------output-----------------------------------
PL/SQL procedure successfully completed.
av
--------------------------------------------------------------------------------
回答by MT0
In Oracle, the empty string is equivalent to NULL.
在 Oracle 中，空字符串相当于NULL.
So you want to do:
所以你想做：
IF flavour IS NOT NULL THEN
However, a better solution is not to use dynamic SQL but to re-write the WHEREfilter:
但是，更好的解决方案不是使用动态 SQL，而是重新编写WHERE过滤器：
create or replace PROCEDURE GET_ICECREAM
(
flavour IN VARCHAR2,
topping IN VARCHAR2,
cursorIC OUT sys_refcursor
)
AS
BEGIN
OPEN cursorIC FOR
SELECT price
FROM tblIceCream
WHERE ( flavour IS NULL OR your_flavour_column LIKE '%' || flavour || '%' )
AND ( topping IS NULL or your_topping_column LIKE '%' || topping || '%' );
END GET_ICECREAM;
/
回答by Erkan Haspulat
In PL/SQL, a zero length string that is assigned to a varchar2variable is treated as a NULL.
在 PL/SQL 中，分配给varchar2变量的零长度字符串被视为NULL.
In your case, if argument flavouris assigned a zero length string, the line below is actually comparing a NULLto something, which is always false.
在你的情况下，如果参数flavour被分配了一个零长度的字符串，下面的行实际上是将 aNULL与something进行比较，这总是错误的。
IF flavour <> '' then
Fix that line according to your business logic to take care of null values for flavour, and you'll be fine. An example fix would be:
根据您的业务逻辑修复该行以处理 的空值flavour，您会没事的。一个示例修复是：
if flavour is not null then
回答by Rahul Tripathi
You can try like this:
你可以这样试试：
IF NVL(flavour, 'NULL') <> 'NULL'
