Much ado about nothing
This presentation is the property of its rightful owner.
Sponsored Links
1 / 40

MUCH ADO ABOUT NOTHING PowerPoint PPT Presentation


  • 117 Views
  • Uploaded on
  • Presentation posted in: General

MUCH ADO ABOUT NOTHING. Walter Schenk SoluTech Consulting Services Inc. AGENDA. What it is and what is not NULL in functions, expressions, comparisons and conditional control NULL in Indexes NULL in programming languages. WHAT A NULL IS NOT!. A NULL is NOT 0! A NULL is NOT Nothing!

Download Presentation

MUCH ADO ABOUT NOTHING

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Much ado about nothing

MUCH ADO ABOUT NOTHING

  • Walter Schenk

  • SoluTech Consulting Services Inc


Agenda

AGENDA

  • What it is and what is not

  • NULL in functions, expressions, comparisons and conditional control

  • NULL in Indexes

  • NULL in programming languages


What a null is not

WHAT A NULL IS NOT!

  • A NULL is NOT 0!

  • A NULL is NOT Nothing!

  • A NULL is NOT an empty string (although Oracle does treat it as such but that may change in the future!)


Then what is a null

THEN WHAT IS A NULL?

  • When a column in a row has no value

  • The value is not known or meaningful


Why do we need null values

WHY DO WE NEED NULL VALUES?

  • Often real-world information is incomplete

  • It is a way of handling this unknown


Null in functions

NULL IN FUNCTIONS

  • “Normal” scalar functions will return NULL when given a NULL argument

  • All aggregate functions except COUNT(*) and GROUPING ignore nulls.

  • You can use the NVL in the argument to an aggregate function to substitute a value for a null.

  • If a query with an aggregate function returns no rows or only rows with nulls for the argument to the aggregate function, the aggregate function returns null.


Functions that handle nulls

FUNCTIONS THAT HANDLE NULLS

  • NVL

  • CONCAT

  • REPLACE

  • NULLIF (9i)

  • COALESCE (9i)

  • DECODE


Much ado about nothing

NVL

NVL ( expr1 , expr2 )

  • If expr1 is null, returns expr2; if expr1 is not null, returns expr1.

  • Can be any datatype


Concat

CONCAT

CONCAT ( char1 , char2 )

  • Returns char1 concatenated with char2.

SELECT CONCAT(‘job’,NULL) "Job"

FROM DUAL;

Job

-----------------

job


Replace

REPLACE

REPLACE ( char , search_string, replacement_string)

  • Returns char with every occurrence of search_string replaced with replacement_string.

  • If replacement_string is omitted or null, all occurrences of search_string are removed.

  • If search_string is null, char is returned.


Replace1

REPLACE

SELECT REPLACE(‘Hello there’,’l’,NULL) "Changes"

FROM DUAL;

Changes

--------------

Heo there

SELECT REPLACE(‘Hello there’,NULL,’xxx’) "Changes"

FROM DUAL;

Changes

--------------

Hello there


Nullif

NULLIF

  • If the values match, then the result is NULL

A := NULLIF(B,C);


Coalesce

COALESCE

  • COALESCE returns the first non-null expr in the expression list.

  • At least one expr must not be the literal NULL. If all occurrences of expr evaluate to null, then the function returns null.

COALESCE (expr1, expr2, ..., expr n)


Decode

DECODE

  • NULL = NULL !!!!

DECODE (deptno, 10, ’ACCOUNTING’,

20, ’RESEARCH’,

30, ’SALES’,

NULL, ’OPERATION’,

’NONE’)


Nulls and comparisons

NULLS AND COMPARISONS

  • Only compare NULLs with IS NULL or IS NOT NULL

  • Use of any other operator and the result will be NULL!

  • NULL <> NULL (except in the DECODE expression and compound keys)


Nulls in conditions

NULLS IN CONDITIONS

  • Always use: variable IS NULL

  • Never user: variable = NULL


Nulls in logical expressions

NULLS IN LOGICAL EXPRESSIONS


Handling nulls

HANDLING NULLS

  • Avoid common mistakes by keeping the following in mind:

    • Comparisons involving NULLs always yield NULL

    • Applying the logical operator NOT to a NULL yields NULL

    • In conditional control statements, if the condition yields NULL, its associated sequence of statements is NOT executed!


Null and conditional control

NULL AND CONDITIONAL CONTROL

MOST COMMON MISTAKE:

In conditional control statements, if the condition yields NULL, its associated sequence of statements is NOT executed!


Null and conditional control1

NULL AND CONDITIONAL CONTROL

IF x > y THEN

high := x;

ELSE

high := y;

END IF;

IF NOT x > y THEN

high := Y;

ELSE

high := x;

END IF;


Programming guideline

PROGRAMMING GUIDELINE

Always account for NULL in applications even if the underlying database objects are defined as NOT NULL.


Null and index entries

NULL AND INDEX ENTRIES

  • Oracle does NOT enter an index value if the ENTIRE key is NULL

  • Consequences:

    • An index can NOT be used in a search criteria for NULL values

    • A UNIQUE constraint on a column that can be NULL will allow multiple NULL values


Nulls in programming languages

NULLS IN PROGRAMMING LANGUAGES

  • PL/SQL

  • C/C++

  • VB.NET and C#.NET


Pl sql

PL/SQL

  • Full support of NULL


Much ado about nothing

C/C++

  • C/C++ does NOT support NULL

  • Variables are passed on to a C/C++ application through host variables for both input as well as output

  • Host variables are prefixed with a colon (“:”) to set them apart from Oracle objects


Much ado about nothing

C/C++

  • Any host variable can be associated with an indicator variable

  • An indicator variable is a short integer variable that indicates the condition of its host variable


C c host variable on input

C/C++ host variable on Input

  • If indicator variable = –1 then the variable is a NULL and Oracle ignores the value of the host variable

  • If indicator variable >=0 Oracle will assign the value of the host variable to the column


C c host variable on output

C/C++ host variable on Output

  • If indicator variable = -1 then the column is NULL and the value of the host variable is indeterminate

  • If indicator variable = 0 then value of the host variable is assigned


Much ado about nothing

C/C++

EXEC SQL SELECT SAL, COMM

INTO :salary,:commission:ind_com

FROM EMP

WHERE EMPNO = :emp_number;

if (ind_comm == -1)

pay = salary

else

pay=salary + commission;


Much ado about nothing

C/C++

Set ind_comm = -1;

EXEC SQL INSERT INTO emp (empno,comm)

VALUES (:emp_number,:commision:ind_comm);


Much ado about nothing

VB

  • In VB6 only Variant data types could support NULL

  • The NULL keyword indicated that a variable contained the NULL value

  • The IsNull function was used to test for NULL


Vb net

VB.NET

  • During a migration from VB6 to VB.NET:

    • Null is converted to DBNull

    • IsNull is converted to IsDBNull

    • The Variant data type is converted to Object

  • In VB6 Null could be used in functions and assignments; DBNull cannot!

  • Consider using the Nothing keyword in .NET instead of Null.


Vb net isdbnull function

VB.NET IsDBNull function

  • Returns TRUE if the expression evaluates to the DBNull type; otherwise returns FALSE

  • The System.DBNull value indicates that the object represents missing or nonexistent data

  • It is NOT the same as Nothing which indicates that a variable has not yet been initialized


Vb net dbnull class

VB.NET DBNull class

  • The DBNull class is used to indicate the absence of a known value

  • The class differentiates between a null value and an uninitialized value


Programming guideline1

PROGRAMMING GUIDELINE

  • Do not circumvent the use of NULLs by assigning “meaningless” or “out-of-range” values

  • Example: a column “EndDate” is often assigned a far fetched date in the future to avoid use of NULL


Sql standards and nulls

SQL STANDARDS AND NULLS

FIPS 127-2 (1993)

The following features have "preliminary" syntax and semantics available in Working Draft form as part of an on-going ANSI and ISO/IEC standardization effort for further development of the SQL language. Features specified in preliminary form include:

17. Multiple null states. A facility that allows user definitions for an arbitrary number of application specific Null values, such as "Unknown", "Missing", "Not Applicable", "Pending", etc. Each such Null value would have a different representation in the database so that they could be distinguished during retrieval or update.


Sql standards and nulls1

SQL STANDARDS AND NULLS

FIPS 193-7 (1995)

If an SQL/ERI Server implementation at the Minimal SDL level or below chooses not to provide support for null values (see item 4 of Section 4.1), then it may raise an implementation-defined exception in any SQL statement that attempts to process null values.

If an SQL/ERI Server implementation at the Minimal SDL level or below chooses not to provide support for null values (see item 4 of Section 4.1), then it shall provide an implementation-defined conversion of would-be null values in Information Schema tables to an appropriate non-null value.

If an SQL/ERI Server implementation at the Minimal SDL level or below chooses not to provide support for null values (see item 4 of Section 4.1), then it may raise an implementation- defined exception in any SQL statement that attempts to process null values.


Sql standards and nulls2

SQL STANDARDS AND NULLS

  • The concept of NULL is subject to change!

  • Various implementations may vary.


Departing words

DEPARTING WORDS

  • Never ignore NULL

  • Use NULL properly


Questions

QUESTIONS?

  • NoCOUG, February 20, 2003


  • Login