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
Special cases of projection This section describes the identity projection, r {ALL BUT}, and the projection on no attributes, r { }, which yields TABLE_DUM when r is empty, ot

V ariables and Constants in PL/SQL The PL/SQL permits you to declare constants and variables, and then use them in SQL and procedural statements anywhere in the expression. Th

CHECK Constraints in SQL A CHECK constraint is a table constraint defined using the key word CHECK, as already illustrated in several examples in this chapter. In particular,

%FOUND Subsequent to a cursor or cursor variable is opened but before the first fetch, the %FOUND yields NULL. Afterward, it yields TRUE when the last fetch returned a row, or

Usefulness of Data Type In SQL, as in most computer languages, a type can be used for constraining the values that are permitted to be used for some purpose. In particular, i

Set Operators The Set operators combine the results of the two queries into one result. The INTERSECT returns all the distinct rows selected by both queries. The MINUS returns

a. Write an anonymous block that contains a PL/SQL function. Given an order number orderNo, the function will calculate the total number of the parts in the order. Then the anonym

Negation (NOT, ¬) - SQL There are three rows instead of just two. As you can see, ¬ p is defined as in two-valued logic (2VL) when p is either true or false, but ¬ (unknown) i

Difference between 9i & 10G When Oracle releases any new databases then it are having some discrepancy with them. But 10G is having much difference than oracle 9i has. Oracle

Aggregate Assignment The %ROWTYPE declaration cannot include an initialization clause. Though, there are two ways to assign values to all fields in a record at once. At First, t