Pl-Sql: Null Values in Comparisons and Conditional Statements

Lot of time we got this type of senerio When working with nulls, you can avoid some common mistakes by keeping in mind the following rules:

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

If the expression in a simple CASE statement or CASE expression yields NULL, it cannot be matched by using WHEN NULL. In this case, you would need to use the searched case syntax and test WHEN expression IS NULL.

In the example below, you might expect the sequence of statements to execute because x and y seem unequal. But, nulls are indeterminate. Whether or not x is equal to y is unknown. Therefore, the IF condition yields NULL and the sequence of statements is bypassed.

DECLARE
x NUMBER := 5;
y NUMBER := NULL;
BEGIN
IF x != y THEN — yields NULL, not TRUE
dbms_output.put_line(‘x != y’); — not executed
ELSIF x = y THEN — also yields NULL
dbms_output.put_line(‘x = y’);
ELSE
dbms_output.put_line(‘Can”t tell if x and y are equal or not…’);
END IF;
END;
/
In the next example, you might expect the sequence of statements to execute because a and b seem equal. But, again, that is unknown, so the IF condition yields NULL and the sequence of statements is bypassed.

DECLARE
a NUMBER := NULL;
b NUMBER := NULL;
BEGIN
IF a = b THEN — yields NULL, not TRUE
dbms_output.put_line(‘a = b’); — not executed
ELSIF a != b THEN — yields NULL, not TRUE
dbms_output.put_line(‘a != b’); — not executed
ELSE
dbms_output.put_line(‘Can”t tell if two NULLs are equal’);
END IF;
END;
/
NULLs and the NOT Operator
Recall that applying the logical operator NOT to a null yields NULL. Thus, the following two statements are not always equivalent:

IF x > y THEN | IF NOT x > y THEN
high := x; | high := y;
ELSE | ELSE
high := y; | high := x;
END IF; | END IF;

The sequence of statements in the ELSE clause is executed when the IF condition yields FALSE or NULL. If neither x nor y is null, both IF statements assign the same value to high. However, if either x or y is null, the first IF statement assigns the value of y to high, but the second IF statement assigns the value of x to high.

NULLs and Zero-Length Strings

PL/SQL treats any zero-length string like a null. This includes values returned by character functions and Boolean expressions. For example, the following statements assign nulls to the target variables:

DECLARE
null_string VARCHAR2(80) := TO_CHAR(”);
address VARCHAR2(80);
zip_code VARCHAR2(80) := SUBSTR(address, 25, 0);
name VARCHAR2(80);
valid BOOLEAN := (name != ”);
BEGIN
NULL;
END;
/
Use the IS NULL operator to test for null strings, as follows:

IF my_string IS NULL THEN …

NULLs and the Concatenation Operator

The concatenation operator ignores null operands. For example, the expression

‘apple’ || NULL || NULL || ‘sauce’

returns the following value:

‘applesauce’

NULLs as Arguments to Built-In Functions

If a null argument is passed to a built-in function, a null is returned except in the following cases.

The function DECODE compares its first argument to one or more search expressions, which are paired with result expressions. Any search or result expression can be null. If a search is successful, the corresponding result is returned. In the following example, if the column rating is null, DECODE returns the value 1000:

DECLARE
the_manager VARCHAR2(40);
name employees.last_name%TYPE;
BEGIN
— NULL is a valid argument to DECODE. In this case, manager_id is null
— and the DECODE function returns ‘nobody’.
SELECT DECODE(manager_id, NULL, ‘nobody’, ‘somebody’), last_name
INTO the_manager, name FROM employees WHERE employee_id = 100;
dbms_output.put_line(name || ‘ is managed by ‘ || the_manager);
END;
/
The function NVL returns the value of its second argument if its first argument is null. In the following example, if the column specified in the query is null, the function returns the value -1 to signify a non-existent employee in the output:

DECLARE
the_manager employees.manager_id%TYPE;
name employees.last_name%TYPE;
BEGIN
— NULL is a valid argument to NVL. In this case, manager_id is null
— and the NVL function returns -1.
SELECT NVL(manager_id, -1), last_name
INTO the_manager, name FROM employees WHERE employee_id = 100;
dbms_output.put_line(name || ‘ is managed by employee #’ || the_manager);
END;
/
The function REPLACE returns the value of its first argument if its second argument is null, whether the optional third argument is present or not. For example, the following call to REPLACE does not make any change to the value of OLD_STRING:

DECLARE
string_type VARCHAR2(60);
old_string string_type%TYPE := ‘Apples and oranges’;
my_string string_type%TYPE := ‘more apples’;
— NULL is a valid argument to REPLACE, but does not match
— anything so no replacement is done.
new_string string_type%TYPE := REPLACE(old_string, NULL, my_string);
BEGIN
dbms_output.put_line(‘Old string = ‘ || old_string);
dbms_output.put_line(‘New string = ‘ || new_string);
END;
/
If its third argument is null, REPLACE returns its first argument with every occurrence of its second argument removed. For example, the following call to REPLACE removes all the dashes from DASHED_STRING, instead of changing them to another character:

DECLARE
string_type VARCHAR2(60);
dashed string_type%TYPE := ‘Gold-i-locks’;
— When the substitution text for REPLACE is NULL,
— the text being replaced is deleted.
name string_type%TYPE := REPLACE(dashed, ‘-‘, NULL);
BEGIN
dbms_output.put_line(‘Dashed name = ‘ || dashed);
dbms_output.put_line(‘Dashes removed = ‘ || name);
END;
/
If its second and third arguments are null, REPLACE just returns its first argument.
Posted by Sairamgoud at 3:05 PM 0 comments
PL/SQL -Advantages
Advantages of PL/SQL

PL/SQL is a completely portable, high-performance transaction processing language that offers the following advantages:

Support for SQL
Support for object-oriented programming
Better performance
Higher productivity
Full portability
Tight integration with Oracle
Tight security
Tight Integration with SQL

The PL/SQL language is tightly integrated with SQL. You do not have to translate between SQL and PL/SQL datatypes: a NUMBER or VARCHAR2 column in the database is stored in a NUMBER or VARCHAR2 variable in PL/SQL. This integration saves you both learning time and processing time. Special PL/SQL language features let you work with table columns and rows without specifying the datatypes, saving on maintenance work when the table definitions change.

Running a SQL query and processing the result set is as easy in PL/SQL as opening a text file and processing each line in popular scripting languages.

Using PL/SQL to access metadata about database objects and handle database error conditions, you can write utility programs for database administration that are reliable and produce readable output about the success of each operation.

Many database features, such as triggers and object types, make use of PL/SQL. You can write the bodies of triggers and methods for object types in PL/SQL.

Support for SQL
SQL has become the standard database language because it is flexible, powerful, and easy to learn. A few English-like commands such as SELECT, INSERT, UPDATE, and DELETE make it easy to manipulate the data stored in a relational database.

PL/SQL lets you use all the SQL data manipulation, cursor control, and transaction control commands, as well as all the SQL functions, operators, and pseudocolumns. This extensive SQL support lets you manipulate Oracle data flexibly and safely. Also, PL/SQL fully supports SQL datatypes, reducing the need to convert data passed between your applications and the database.

PL/SQL also supports dynamic SQL, a programming technique that makes your applications more flexible and versatile. Your programs can build and process SQL data definition, data control, and session control statements at run time, without knowing details such as table names and WHERE clauses in advance.

Better Performance
Without PL/SQL, Oracle must process SQL statements one at a time. Programs that issue many SQL statements require multiple calls to the database, resulting in significant network and performance overhead.

With PL/SQL, an entire block of statements can be sent to Oracle at one time. This can drastically reduce network traffic between the database and an application. PL/SQL even has language features to further speed up SQL statements that are issued inside a loop.

PL/SQL stored procedures are compiled once and stored in executable form, so procedure calls are efficient. Because stored procedures execute in the database server, a single call over the network can start a large job. This division of work reduces network traffic and improves response times. Stored procedures are cached and shared among users, which lowers memory requirements and invocation overhead.

Higher Productivity
PL/SQL extends tools such as Oracle Forms and Oracle Reports. With PL/SQL in these tools, you can use familiar language constructs to build applications. For example, you can use an entire PL/SQL block in an Oracle Forms trigger, instead of multiple trigger steps, macros, or user exits.

PL/SQL is the same in all environments. Once you learn PL/SQL with one Oracle tool, you can transfer your knowledge to other tools.

Full Portability
Applications written in PL/SQL can run on any operating system and platform where the Oracle database runs. With PL/SQL, you can write portable program libraries and reuse them in different environments.

Tight Security
PL/SQL stored procedures move application code from the client to the server, where you can protect it from tampering, hide the internal details, and restrict who has access. For example, you can grant users access to a procedure that updates a table, but not grant them access to the table itself or to the text of the UPDATE statement.

Triggers written in PL/SQL can control or record changes to data, making sure that all changes obey your business rules.

Support for Object-Oriented Programming
Object types are an ideal object-oriented modeling tool, which you can use to reduce the cost and time required to build complex applications. Besides allowing you to create software components that are modular, maintainable, and reusable, object types allow different teams of programmers to develop software components concurrently.

By encapsulating operations with data, object types let you move data-maintenance code out of SQL scripts and PL/SQL blocks into methods. Also, object types hide implementation details, so that you can change the details without affecting client programs.

In addition, object types allow for realistic data modeling. Complex real-world entities and relationships map directly into object types. This direct mapping helps your programs better reflect the world they are trying to simulate.