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
Using ROLLBACK The ROLLBACK statements end the present transaction and undo any change made during the transaction. The Rolling back is helpful for two reasons. Firstly, if yo

Packages The package is a schema object which groups logically associated to the PL/SQL items, types, and subprograms. The Packages have 2 sections: the specification & the bod

Using the student and faculty tables create a select query that outputs all students for a specific advisor. Generate the execution plan, select out the explain plan . Create an

Example of GROUPBY Operator Example: How many students sat each exam, using GROUP BY, NATURAL LEFT JOIN, and COALESCE SELECT CourseId, COALESCE (n, 0) AS n FROM COURS

Understanding Varrays The Items of type VARRAY are termed as the varrays. They permit you to relate a single identifier with the whole collection. This relationship lets you man

Name Resolution In potentially uncertain SQL statements, the names of the database columns take precedence over the names of the local variables and formal parameters. For e.g.

I need to write one function and one procedure to query a Oracle 10.1 DB using PL SQL. I have the schema and exact queries...along with work Ive started and a template to put the a

Fetching from a Cursor Variable The FETCH statement retrieve rows one at a time from the product set of a multi-row query. The syntax for the same is as shown: FETCH {curso

Package Body: The package specification is implemented by the package body. That is, the package body has the definition of every cursor and the subprogram declared in the pac

Delimiters A delimiter is a simple or compound symbol which has a special meaning to PL/SQL. For example, you use delimiters to symbolize an arithmetic operation like additio