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

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.
Posted Date: 1/18/2013 1:52:51 AM | Location : United States







Related Discussions:- Example of null operator - nino rule, Assignment Help, Ask Question on Example of null operator - nino rule, Get Answer, Expert's Help, Example of null operator - nino rule Discussions

Write discussion on Example of null operator - nino rule
Your posts are moderated
Related Questions
Initializing Objects: Till you initialize an object by calling the constructor for its object type, the object is automatically null. That is, the object itself is null, not me

FETCH Statement The FETCH statement retrieve rows of data one at a time from the result set of the multi-row query. The data is stored in fields or variables which correspond t

Assignment of Variable - Updating a Variable Syntax: SET SN = SID ('S2'); This can obviously be read as "set the variable SN to be equal in value to SID ( 'S2' )".

Cursor Variables As Parameters You can declare the cursor variables as the formal parameters of the functions and procedures. In the illustration below, you define the REF CUR

Wrapping and unwrapping in SQL Operators WRAP and UNWRAP in connection with attributes whose declared types are tuple types. Example shows how extension and projection can be

Need for Dynamic SQL: You need dynamic SQL in the situations as follows: 1) You would like to execute a SQL data definition statement (like CREATE), a data control statemen

Data Types in SQL - Decimal DECIMAL, NUMERIC, REAL, FLOAT and various other terms for various sets of rational numbers. When these key words are specified for the declared typ

Initializing Records The illustration below shows that you can initialize a record in its type definition. Whenever you declare a record of the type TimeRec, its 3 fields supp

At times, customers make mistakes in submitting their orders and call to cancel the order. Brewbean’s wants to create a trigger that automatically updates the stock level of all pr