Example of null operator - nino rule, PL-SQL Programming

Assignment Help:

Example of Null operator - NiNo Rule

If we wanted to make HIGHER_OF adhere to "NULL in, NULL out"-let's call it the NiNo rule-we would have to write something like what is shown in Example 2.1a.

Example: NiNo version of HIGHER_OF

CREATE FUNCTION HIGHER_OF ( A INTEGER, B INTEGER )

RETURNS INTEGER

CASE

WHEN A IS NULL OR B IS NULL

THEN RETURN CAST (NULL AS INTEGER);

WHEN A > B

THEN RETURN A;

ELSE RETURN B;

END CASE;

Explanation:

  • IS NULL is a monadic Boolean operator that evaluates to TRUE when its argument is "the null value", otherwise FALSE. Note, therefore, that it is our first exception to the NiNo rule, which would require it to evaluate to NULL (UNKNOWN) when its argument is "the null value".
  • CAST (NULL AS INTEGER) denotes "the null value" of type INTEGER. As in Tutorial D, the operand of RETURN must be an expression that has a declared type and NULL, on its own, does not have a declared type. The CAST operator takes an expression and a type name, separated by the "noise" word AS, and normally expresses a "type conversion"-a function that maps elements of one type to those of another that are considered to be in some defined sense equivalent. In the special case of NULL it is used to confer a type, so to speak, on something that doesn't otherwise have one.
  • AS, appearing where you might have expected just a comma, is explained by a matter of policy in SQL whereby invocations of user-defined functions, which always use commas between arguments, can be syntactically distinguished from invocations of system-defined functions.
  • OR is SQL's counterpart of the usual logical operator of that name. In A IS NULL OR B IS NULL its operands are clearly restricted to just TRUE and FALSE and that expression therefore results in TRUE if either of those operands does, otherwise FALSE.

Related Discussions:- Example of null operator - nino rule

%type attribute - syntax, %TYPE Attribute The %TYPE attribute gives the...

%TYPE Attribute The %TYPE attribute gives the datatype of a record, field, nested table, database column, or the variable. You can use the %TYPE attribute as the datatype speci

Long and long raw in pl/sql, LONG and LONG RAW You use the LONG datatyp...

LONG and LONG RAW You use the LONG datatype to store the variable-length character strings. The LONG datatype is such as the VARCHAR2 datatype, except that the maximum length o

Mechanistis theory-haeckel - origin of life, MECHANISTI S THEORY-HAECKEL (...

MECHANISTI S THEORY-HAECKEL (1866) - Haeckel stating that after each catalysm, some new organism suddenly forms as a chance event in one stride from inanimate matter and sub

Mutual recursion, Mutual Recursion The Subprograms are mutually recursi...

Mutual Recursion The Subprograms are mutually recursive if they directly or indirectly call each other. In the illustration below, the Boolean functions odd & even, that dete

Between and not between operator in sql, BETWEEN and NOT BETWEEN Operator i...

BETWEEN and NOT BETWEEN Operator in SQL Example: Restricting exam marks to between 0 and 100 CREATE ASSERTION Marks_between_0_and_100 CHECK (NOT EXISTS (SELECT * FROM

Implicit cursors, Implicit Cursors The Oracle implicitly opens a curso...

Implicit Cursors The Oracle implicitly opens a cursor to process each SQL statement not related with an explicitly declared cursor. The PL/SQL lets you refer to the most recen

Implement a new discount for return shoppers, Brewbean's is implementing a ...

Brewbean's is implementing a new discount for return shoppers - every fifth completed order receives a 10% discount. The count of orders for a shopper is placed in a packaged varia

Keyword & parameter description - exceptions, Keyword & Parameter Descripti...

Keyword & Parameter Description: WHEN: This keyword introduces the exception handler. You can have many exceptions execute the similar sequence of the statements by follo

Avoiding collection exceptions, Avoiding Collection Exceptions   In ma...

Avoiding Collection Exceptions   In many cases, if you reference a nonexistent collection element, then PL/SQL raises a predefined exception. Consider the illustration shown b

Database values-assignments in pl/sql, Database Values You can use the S...

Database Values You can use the SELECT statement to have the Oracle assign values to a variable. For Each and every item in the select list, there must be a matching, type-compa

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd