当前位置:首页 > 数据库 > Oracle教程 > Oracle Single-Row Functions(单行函数) NULL-Related Functions

Oracle Single-Row Functions(单行函数) NULL-Related Functions

2017-10-10 07:49:03[Oracle教程]点击数:作者:未知 来源: 网络
随机为您推荐的文章:oracle sql实现mybatis半自动化工具

select t.*,        '@JsonProperty(value = "' || lower(t.column_name) || '")' || ' private ' ||         decode(t.data_type, 'NUMBER', 'Integer', 'St

本文将为您描述Oracle Single-Row Functions(单行函数) NULL-Related Functions,具体操作方法:

参考资料:http://file.qingyaoweb.com/d/file/shujuku/xfxskoeijwf.htm

Single-row functions return a single result row for every row of a queried table or view. These functions can appear in select lists,WHEREclauses,STARTWITHandCONNECTBYclauses, andHAVINGclauses.

单行函数返回查询表或视图的每一行的单一结果行。单行函数可以出现在SELECT列中,WHERE子句,START WITH 和CONNECT BY子句以及HAVING子句中。

NULL-Related Functions:

  The NULL-related functions facilitate null handling.

TheNULL-related functions are:

  NVL:

    语法:  NVL(expr1,expr2) 

    功能:  

        NVLlets you replace null (returned as a blank) with a string in the results of a query. Ifexpr1is null, thenNVLreturnsexpr2. Ifexpr1is not null, thenNVLreturnsexpr1.

    说明:  

        1.The argumentsexpr1andexpr2can have any data type.

        参数expr1和expr2可以是任何数据类型。

        2. If their data types are different, then Oracle Database implicitly converts one to the other.

        如果俩参数数据类型不同,oracle数据库将进行隐式转换。

        3.If they cannot be converted implicitly, then the database returns an error.

        如果俩参数不能进行隐式转换,数据库将返回一个error。

        4.The implicit conversion is implemented as follows:(隐式转换如下)

          1>.Ifexpr1is character data, then Oracle Database convertsexpr2to the data type ofexpr1before comparing them and returnsVARCHAR2in the character set ofexpr1.

          如果expr1是字符数据,则数据库会将expr2转换为expr1的数据类型,并返回expr1的字符集中的VARCHAR2。

          2>.Ifexpr1is numeric, then Oracle Database determines which argument has the highest numeric precedence, implicitly converts the other argument to that data type, and returns that data type.

          如果expr1是数字,则Oracle数据库确定哪个参数具有最高的数字优先级,将另一个参数隐式转换为该数据类型,并返回该数据类型。

    用途:

        最主要的是格式化数据,比如涉及到数字的,不想出现空数据时,可用nvl(num,0)来得到0。由于 null+[或-,*,/]数字 等于null,所以在表达式中对可能为空的值要使用nvl。

  NVL2:

    语法:  NVL2(expr1,expr2,expr3)

    功能:

        NVL2lets you determine the value returned by a query based on whether a specified expression is null or not null. Ifexpr1is not null, thenNVL2returnsexpr2. Ifexpr1is null, thenNVL2returnsexpr3.

    说明:

        1.The argumentexpr1can have any data type. The argumentsexpr2andexpr3can have any data types exceptLONG.

        参数expr1可以是任何数据类型,参数expr2和expr3可以是除了LONG类型外的任何数据类型。

        2.If the data types ofexpr2andexpr3are different, then Oracle Database implicitly converts one to the other.

        如果参数expr2和expr3是不同的数据类型,数据库将会隐式的将其中一个转换为另一个。

        3.If they cannot be converted implicitly, then the database returns an error.

        如果俩参数不能进行隐式转换,数据库将返回一个error。

        4.Ifexpr2is character or numeric data, then the implicit conversion is implemented as follows:

        (如果expr2是字符或是数字数据,则隐式转换如下实现)

          1>.Ifexpr2is character data, then Oracle Database convertsexpr3to the data type ofexpr2before returning a value unlessexpr3is a null constant. In that case, a data type conversion is not necessary, and the database returnsVARCHAR2in the character set ofexpr2.

          如果expr2是字符数据,数据库会将expr3(null除外)转换为expr2的数据类型。如果expr3为空常量,则不需要进行数据类型转换,数据库会返回expr2的字符集中的VARCHAR2。

          2>.Ifexpr2is numeric data, then Oracle Database determines which argument has the highest numeric precedence, implicitly converts the other argument to that data type, and returns that data type.

          如果expr2是数字数据,那么Oracle数据库将确定哪个参数具有最高的数字优先级,将另一个参数隐式转换为该数据类型,并返回该数据类型。

  NULLIF:

    语法:  NULLIF(expr1,expr2)

    功能:  NULLIFcomparesexpr1andexpr2. If they are equal, then the function returns null. If they are not equal, then the function returnsexpr1. You cannot specify the literalNULLforexpr1.

    说明:

        If both arguments are numeric data types, then Oracle Database determines the argument with the higher numeric precedence, implicitly converts the other argument to that data type, and returns that data type. If the arguments are not numeric, then they must be of the same data type, or Oracle returns an error.

        如果俩参数是数字数据类型,则Oracle数据库将确定具有较高数字优先级的参数,将另一个参数隐式转换为该数据类型,并返回该数据类型。 如果参数不是数字,则它们必须是相同的数据类型,否则Oracle返回一个error。

        TheNULLIFfunction is logically equivalent to the followingCASEexpression:

CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

  LNNVL:

    语法:  LNNVL(condition)

    功能:  LNNVLprovides a concise way to evaluate a condition when one or both operands of the condition may be null.

         The function can be used in theWHEREclause of a query, or as theWHENcondition in a searchedCASEexpression.

         It takes as an argument a condition and returnsTRUEif the condition isFALSEorUNKNOWNandFALSEif the condition isTRUE.

         LNNVLcan be used anywhere a scalar expression can appear, even in contexts where theIS[NOT]NULL,AND, orORconditions are not valid but would otherwise be required to account for potential nulls.

         Oracle Database sometimes uses theLNNVLfunction internally in this way to rewriteNOTINconditions asNOTEXISTSconditions.

         In such cases, output fromEXPLAINPLANshows this operation in the plan table output.

         Theconditioncan evaluate any scalar values but cannot be a compound condition containingAND,OR, orBETWEEN.

  NANVL:

    语法:  NANVL(n2,n1)

    功能:

      TheNANVLfunction is useful only for floating-point numbers of typeBINARY_FLOATorBINARY_DOUBLE.

      It instructs Oracle Database to return an alternative valuen1if the input valuen2isNaN(not a number). Ifn2isnotNaN, then Oracle returnsn2.

      This function takes as arguments any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type.

      Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type, and returns that data type.

这篇Oracle教程是否对您有用呢?

这些内容可能对你也有帮助

更多Oracle教程可查看Oracle教程列表页。