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;
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

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