Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / PL/SQL Tutorial / PL/SQL NULL Statement

PL/SQL NULL Statement

Summary: in this tutorial, you will learn about PL/SQL NULL statement and how to apply it in some programming contexts.

Introduction to PL/SQL NULL statement

The PL/SQL NULL statement has the following format:

NULL;

The NULL statement is a NULL keyword followed by a semicolon ( ;). The NULL statement does nothing except that it passes control to the next statement.

The NULL statement is useful to:

  • Improve code readability
  • Provide a target for a GOTO statement
  • Create placeholders for subprograms

Improving code readability

The following code sends an email to employees whose job titles are Sales Representative.

IF job_title = 'Sales Representative' THEN send_email; END IF;

What should the program do for employees whose job title are not Sales Representative? You might assume that it should do nothing. Because this logic is not explicitly mentioned in the code, you may wonder if it misses something else.

To make it more clear, you can add a comment. For example:

-- Send email to only Sales Representative, -- for other employees, do nothing IF job_title = 'Sales Representative' THEN send_email; END IF;

Or you can add an ELSE clause that consists of a NULL statement to clearly state that no action is needed for other employees.

IF job_title = 'Sales Representative' THEN send_email; ELSE NULL; END IF;

Similarly, you can use a NULL statement in the ELSE clause of a simple CASE statement as shown in the following example:

DECLARE n_credit_status VARCHAR2( 50 ); BEGIN n_credit_status := 'GOOD'; CASE n_credit_status WHEN 'BLOCK' THEN request_for_aproval; WHEN 'WARNING' THEN send_email_to_accountant; ELSE NULL; END CASE; END;

In this example, if the credit status is not blocked or warning, the program does nothing.

Providing a target for a GOTO statement

When using a GOTO statement, you need to specify a label followed by at least one executable statement.

The following example uses a GOTO statement to quickly move to the end of the program if no further processing is required:

DECLARE b_status BOOLEAN BEGIN IF b_status THEN GOTO end_of_program; END IF; -- further processing here -- ... <<end_of_program>> NULL; END;

Note that an error will occur if you don’t have the NULL statement after the end_of_program label.

Creating placeholders for subprograms

The following example creates a procedure named request_for_approval that doesn’t have the code in the body yet. PL/SQL requires at least one executable statement in the body of the procedure in order to compile successfully, therefore, we add a NULL statement to the body as a placeholder. Later you can fill the real code.

CREATE PROCEDURE request_for_aproval( customer_id NUMBER ) AS BEGIN NULL; END;

Now, you should have a good understanding of the PL/SQL NULL statement and how to apply it in your daily programming tasks.

  • Was this tutorial helpful?
  • YesNo
Previous PL/SQL GOTO Statement
Next PL/SQL LOOP

PL/SQL Getting Started

  • What is PL/SQL
  • PL/SQL Anonymous Block
  • PL/SQL Data Types
  • PL/SQL Variables
  • PL/SQL Comments
  • PL/SQL Constants

PL/SQL Conditional Control

  • PL/SQL IF THEN
  • PL/SQL CASE
  • PL/SQL GOTO
  • PL/SQL NULL Statement

PL/SQL Loops

  • PL/SQL LOOP
  • PL/SQL FOR LOOP
  • PL/SQL WHILE Loop
  • PL/SQL CONTINUE

PL/SQL Select Into

  • PL/SQL SELECT INTO

PL/SQL Exception Handlers

  • PL/SQL Exception
  • PL/SQL Exception Propagation
  • PL/SQL RAISE Exceptions
  • RAISE_APPLICATION_ERROR

PL/SQL Records

  • PL/SQL Record

PL/SQL Cursors

  • PL/SQL Cursor
  • PL/SQL Cursor FOR LOOP
  • PL/SQL Cursor with Parameters
  • PL/SQL Updatable Cursor

PL/SQL Procedures & Functions

  • PL/SQL Procedure
  • PL/SQL Function
  • PL/SQL Cursor Variables

PL/SQL Packages

  • PL/SQL Package
  • PL/SQL Package Specification
  • PL/SQL Package Body

PL/SQL Triggers

  • PL/SQL Triggers
  • PL/SQL Statement-level Triggers
  • PL/SQL Row-level Triggers
  • PL/SQL INSTEAD OF Triggers
  • PL/SQL Disable Triggers
  • PL/SQL Enable Triggers
  • PL/SQL Drop Triggers
  • Oracle Mutating Table Error

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.