DEFINE and UNDEFINE Commands with SQL

Using Ampersand Substitution to Restrict and Sort Output at Run Time: DEFINE and UNDEFINE Commands

Introduction to DEFINE and UNDEFINE Commands

In Oracle SQL*Plus:

• DEFINE is used to create a substitution variable with a default value.
• UNDEFINE is used to remove a substitution variable, so it no longer has a value.

These commands are useful for setting up variables that can be reused across multiple queries or scripts, making your SQL execution more interactive and flexible.

Using the DEFINE Command

The DEFINE command assigns a default value to a substitution variable. This value is used in SQL queries unless overridden by a user input at runtime.

Syntax: 

DEFINE variable_name = value;

Example: Define Default Values for Department and Sort Order 

DEFINE dept_name = 'Sales';
DEFINE sort_order = 'ASC';
SELECT name, salary
FROM employees
WHERE department = '&dept_name'
ORDER BY salary &sort_order;

In this example:

• &dept_name is predefined as ‘Sales’.
• &sort_order is predefined as ‘ASC’.

When you run this query, it will use ‘Sales’ as the default department and ‘ASC’ for sorting unless you provide different values at runtime.

Using the UNDEFINE Command

The UNDEFINE command removes a substitution variable. After using this command, the variable no longer has a value, and if used in a query, the system will prompt for a new value or may result in an error if not provided.

Syntax: 

UNDEFINE variable_name;

Example: Undefine a Variable 

UNDEFINE dept_name;
SELECT name, salary
FROM employees
WHERE department = '&dept_name'
ORDER BY salary DESC;

In this example, dept_name is undefined, so when running the query, SQL*Plus will prompt for a value for &dept_name. If no value is provided, the query may fail or return no results.

Combining DEFINE and UNDEFINE in a Script

You can combine DEFINE and UNDEFINE to create flexible scripts that set default values, use them in queries, and then remove them when no longer needed.

Example: A Script with Defined Variables 

-- Define variables
DEFINE dept_name = 'Marketing';
DEFINE min_salary = 30000;
DEFINE max_salary = 70000;
-- Query using defined variables
SELECT name, salary
FROM employees
WHERE department = '&dept_name'
  AND salary BETWEEN &min_salary AND &max_salary
ORDER BY salary DESC;
-- Undefine variables after use
UNDEFINE dept_name;
UNDEFINE min_salary;
UNDEFINE max_salary;

In this script:

• Variables are defined with default values.
• The query uses these values for filtering and sorting.
• Variables are undefined after the query execution to clean up the session environment.

Interactive Query Execution

When you define variables with DEFINE, users can still override these values at runtime by entering new values when prompted.

Example: Allow Runtime Overrides 

-- Define default values
DEFINE dept_name = 'HR';
DEFINE min_salary = 25000;
-- Prompt for runtime values
ACCEPT dept_name PROMPT 'Enter Department Name: ';
ACCEPT min_salary PROMPT 'Enter Minimum Salary: ';
-- Query using defined variables
SELECT name, salary
FROM employees
WHERE department = '&dept_name'
  AND salary > &min_salary
ORDER BY salary DESC;

In this example:

• ACCEPT is used to prompt for user input.
• Users can enter new values for &dept_name and &min_salary when executing the query.

Best Practices

• Default Values: Use DEFINE to set default values for commonly used variables. This can streamline script execution and reduce the number of prompts.
• Variable Cleanup: Use UNDEFINE to remove variables that are no longer needed to avoid potential conflicts or unintended results.
• Testing: Thoroughly test scripts with both default and user-specified values to ensure they behave as expected.

Handling SQL*Plus Environment

DEFINE and UNDEFINE are specific to SQL*Plus and may not be supported in all SQL environments or database tools. Ensure compatibility with your SQL environment and understand the tool’s features and limitations.

Conclusion

The DEFINE and UNDEFINE commands in Oracle SQL*Plus allow you to manage substitution variables effectively, making your SQL scripts more dynamic and interactive. By setting default values, prompting for runtime input, and cleaning up variables, you can create flexible and user-friendly queries that adapt to different scenarios and user inputs.