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 has the following format:
NULL statement is a
NULL keyword followed by a semicolon (
NULL statement does nothing except that it passes control to the next statement.
NULL statement is useful to:
- Improve code readability
- Provide a target for a
- Create placeholders for subprograms
Improving code readability
The following code sends an email to employees whose job titles are
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
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
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.