Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

IS NULL in Oracle SQL PLSQL

by

In Oracle SQL / PLSQL IS NULL is used to check whether the value of a literal is NULL or not.

Example 1:
Syntax to use IS NULL in IF statement is:
IF literal_name IS NULL THEN
<business_logic>
END IF;

Here if the literal_name has NULL value then the IF condition will evaluate to TRUE, then the “THEN” section of the code will get executed.

Example 2:

Using IS NULL is Oracle SQL / PLSQL SELECT statement:

Suppose we have a table named ‘employee’ as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support 5
105 Emp E 32000 Sales 10
106 Emp F 20000 Sales 5
107 Emp G 12000 Sales
108 Emp H 12000 Support

Suppose we want to see the records having commission as NULL, then we can achieve the same as:

SELECT *
FROM employee
WHERE commission IS NULL;

We will get the following results:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales
107 Emp G 12000 Sales
108 Emp H 12000 Support

Here we can see that we have successfully retrieved records having NULL commission using IS NULL in Oracle SQL PLSQL SELECT statement.

Declaring Variables in Oracle PLSQL

by

A variable, by definition, is a name given to a memory space.

In Oracle PLSQL a variable allows a programmer to store the needed data / values in variables while the program is being executed.

The Syntax for declaring a variable in Oracle PLSQL is:
variable_name [CONSTANT] data_type [NOT NULL] [:= DEFAULT initial value]

For example we can create a variable named ‘websitename’ in Oracle PLSQL as:

websitename VARCHAR2(100);

In the above statement we have just created a variable but the variable doesn’t have anything stored in it. We can assign a value to the above created ‘websitename’ variable as:

websitename := 'Website Name Is techhoney.com';

We can merge the two steps above and can create a variable along with assigning some value to it in a single statement as:

websitename VARCHAR2(100) := 'Website Name Is techhoney.com';

Later we can change the value of ‘websitename’ variable to something else e.g.

websitename := 'This is just a test';

Here we should understand that we have created a variable and not a constant; in case of constant the value assigned to it cannot be changed.

Below is an example is to create a constant in Oracle PLSQL:

grandTotal CONSTANT NUMERIC(10,1) := 123.456;

Note: Once assigned, the value of a constant cannot be changed.

Literals in Oracle PLSQL

by

Basically in Oracle SQL / PLSQL a literal is same as constant.

Let’s see few types of literals.

  1. Text Literals
  2. Integer Literals
  3. Number Literals

Text Literals:-
Text literals in oracle plsql are always surrounded by single quotes (‘) e.g.
‘tech honey’
‘oracle’
‘plsql’
‘January 1, 2012’

Integer Literals:
Integer Literals in oracle plsql can be up to 38 digits long, they can be either positive or negative numbers. If no sign is specified then oracle plsql assume the literal to be positive. Some examples of Integer literals are:
20
+20
-20

Number Literals:
Number Literals in oracle plsql can be up to 38 digits long, they can be either positive or negative numbers. If no sign is specified then oracle plsql assume the literal to be positive. Some examples of Number literals are:
20
+20
-20
20e-4
20.0020

LAST_VALUE Function with PARTITION BY Clause in Oracle SQL – PLSQL

by

The LAST_VALUE Function in Oracle SQL / PLSQL is an analytical function and is used to return the last value in an ordered set of values.

  1. If the last value in the ordered set is NULL, then LAST_VALUE function returns NULL unless we specify IGNORE NULLS.
  2. If we specify IGNORE NULLS, then LAST_VALUE function returns the LAST NON NULL value in the ordered list, or NULL if the list contains all the NULL values.
  3. LAST_VALUE function can be used with and without PARTITION BY clause.

Syntax for using the LAST_VALUE function in Oracle SQL / PLSQL is:

SELECT column(s)
,LAST_VALUE(column [IGNORE NULLS]) OVER ([PARTITION BY column] ORDER BY column ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM table_name;

Example 1:

Using LAST_VALUE Function with PARTITION BY and without IGNORE NULLS Clause.

Suppose we have a table named ‘employee’ as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support 5
105 Emp E 32000 Sales 10
106 Emp F 20000 Sales 5
107 Emp G 12000 Sales
108 Emp H 12000 Support

Suppose we wish to view department wise employee_id, employee_name, department and the LAST value of commission that employees get in an ordered set, we can achieve the same as:

SELECT employee_id
       ,employee_name
       ,department
       ,LAST_VALUE(commission) OVER (PARTITION BY department
                   ORDER BY employee_id DESC
                   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) Minimum_Commission
FROM employee;

We will get the following result:

Employee_ID Employee_Name Department Minimum_Commission
103 Emp C IT 20
102 Emp B IT 20
107 Emp G Sales
106 Emp F Sales
105 Emp E Sales
101 Emp A Sales
108 Emp H Support 5
104 Emp D Support 5

Here we can see that we have fetched LAST values for the commission column department wise in descending order of ‘employee_id’, for ‘IT’ department the ‘employee_id = 102’ has 20 commission and hence is fetched against ‘IT’ department.

For ‘Sales’ department the ‘employee_id = 101’ have NULL as commission, hence NULL is fetched as LAST value against ‘Sales’ department.

For ‘Support’ department the ‘employee_id = 104’ has 5 as commission, hence 5 is fetched as LAST value against ‘Support’ department.

Example 2:

Using LAST_VALUE Function with PARTITION BY and IGNORE NULLS Clause.

Suppose we have a table named ‘employee’ as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support 5
105 Emp E 32000 Sales 10
106 Emp F 20000 Sales 5
107 Emp G 12000 Sales
108 Emp H 12000 Support

Suppose we wish to view department wise employee_id, employee_name, department and the LAST value of commission that employees get in an ordered set, we can achieve the same as:

SELECT employee_id
       ,employee_name
       ,department
       ,LAST_VALUE(commission IGNORE NULLS) OVER (PARTITION BY department
                   ORDER BY employee_id DESC
                   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) Minimum_Commission
FROM employee;

We will get the following result:

Employee_ID Employee_Name Department Minimum_Commission
103 Emp C IT 20
102 Emp B IT 20
107 Emp G Sales 10
106 Emp F Sales 10
105 Emp E Sales 10
101 Emp A Sales 10
108 Emp H Support 5
104 Emp D Support 5

Here we can see that we have fetched LAST values for the commission column department wise in descending order of ‘employee_id’, for ‘IT’ department the ‘employee_id = 102’ has 20 commission and hence is fetched against ‘IT’ department.

Also observe that for ‘employee_id = 101’ in Sales department the LAST value encountered is NULL, but the next NON NULL value is 10 for ‘employee_id =105’ and hence 10 is fetched for ‘Sales’ department.

Similarly for ‘Support’ department, ‘employee_id = 104’ has commission of and hence 5 is fetched for other employees of ‘Support’ department.

LAST_VALUE Function without PARTITION BY Clause in Oracle SQL – PLSQL

by

The LAST_VALUE Function in Oracle SQL / PLSQL is an analytical function and is used to return the last value in an ordered set of values.

  1. If the last value in the ordered set is NULL then LAST_VALUE function returns NULL unless we specify IGNORE NULLS.
  2. If we specify IGNORE NULLS then LAST_VALUE function returns the first NON NULL value in the ordered list, or NULL if the list contains all the NULL values.
  3. LAST_VALUE function can be used with and without PARTITION BY clause.

Syntax for using the LAST_VALUE function in Oracle SQL / PLSQL is:
SELECT column(s)
,LAST_VALUE(column [IGNORE NULLS]) OVER ([PARTITION BY column] ORDER BY column ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM table_name;

Example 1:
Using LAST_VALUE Function Without IGNORE NULLS Clause.

Suppose we have a table named ‘employee’ as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support 5
105 Emp E 32000 Sales 10
106 Emp F 20000 Sales 5
107 Emp G 12000 Sales
108 Emp H 12000 Support

Suppose we wish to view the employee_id, employee_name, department and the last value of commission that employees get in an ordered set, we can achieve the same as:

SELECT employee_id
       ,employee_name
       ,department
       ,LAST_VALUE(commission) OVER (ORDER BY employee_id DESC
                   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) Minimum_Commission
FROM employee;

We will get the following result:

Employee_ID Employee_Name Department Minimum_Commission
108 Emp H Support
107 Emp G Sales
106 Emp F Sales
105 Emp E Sales
104 Emp D Support
103 Emp C IT
102 Emp B IT
101 Emp A Sales

Here we can see that we have fetched NULL values in the ‘Minimum_Commission’ column because we have sorted the list in descending order of ‘employee_id’, and for ‘employee_id = 101’ the ‘commission’ is NULL, hence NULL becomes the last value that is encountered and is therefore fetched.

Example 2:

Using LAST_VALUE Function With IGNORE NULLS Clause.

Suppose we have a table named ‘employee’ as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support 5
105 Emp E 32000 Sales 10
106 Emp F 20000 Sales 5
107 Emp G 12000 Sales
108 Emp H 12000 Support

Suppose we wish to view the employee_id, employee_name, department and the last value of commission that employees get in an ordered set, we can achieve the same as:

SELECT employee_id
       ,employee_name
       ,department
       ,LAST_VALUE(commission IGNORE NULLS) OVER (ORDER BY employee_id DESC
                   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) Minimum_Commission
FROM employee;

We will get the following result:

Employee_ID Employee_Name Department Minimum_Commission
108 Emp H Support 20
107 Emp G Sales 20
106 Emp F Sales 20
105 Emp E Sales 20
104 Emp D Support 20
103 Emp C IT 20
102 Emp B IT 20
101 Emp A Sales 20

Here we can see that we have fetched 20 as the commission value even though the last value encountered was NULL by using IGNORE NULLS clause in LAST_VALUE function.

20 has been returned because the LAST_VALUE function will return the FIRST NON NULL value if a NULL value is encountered at last while using IGNORE NULLS clause.