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

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

Maintain an audit trail of product table changes, The accuracy of product t...

The accuracy of product table data is critical and the Brwebean's. owner wants to have an audit file that contains information regarding all DML activity on the BB_PRODUCT table. T

Variable declaration - sql, Variable Declaration - SQL SQL's support f...

Variable Declaration - SQL SQL's support for variables is very similar to Tutorial D's, except that the syntax for creating persistent  variables-base tables-is quite differen

Naming conventions-pl/sql, Naming Conventions The similar naming conventi...

Naming Conventions The similar naming conventions apply to all PL/SQL program items and units including the variables, cursors, constants, cursor variables, procedures, exception

Package body, The Package Body The package specification is implemented...

The 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 p

Database Management, Due to an increase in overhead costs, the buying price...

Due to an increase in overhead costs, the buying price of all items needs to be increased. Management wants to see a report before deciding how much each product will go up. Add to

Table represents an extension - sql, Table Represents an Extension - SQL ...

Table Represents an Extension - SQL It describes how each tuple in a relation represents a true instantiation of some predicate and each true instantiation is represented by s

Using a host variable, Using a Host Variable You can declare the curso...

Using a Host Variable You can declare the cursor variable in the PL/SQL host environment like an OCI or Pro C program. To use the cursor variable, you should pass it as a host

Sql queries-oracle , 1- You can check attribute names from each table in D...

1- You can check attribute names from each table in DBF11 by running for example:  desc dbf11.Member;  desc dbf11.Agent;  desc dbf11.Producer; Because some attribute names in

Data types in sql - timestamp, Data Types in SQL - Timestamp TIMESTAMP...

Data Types in SQL - Timestamp TIMESTAMP for values representing points in time on a specified uniform scale. DATE is used for timestamps on a scale of one day, such as DATE '2

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