Parameterised queries

Why use parameters?

Parameter definition

A parameter is a special kind of variable that refers to a value that is provided as the input to a software routine (function, query, etc.)

As you can see from the coursework, SQL queries can be quite complex and it can take a long time to get them right. In a business organisation, a software developer will be required to write queries that a manager uses to produce reports. For example, a manager might use the information in the HR schema to work out how man employees have been in their current job for a particular length of time when considering promotions. The report might be required once or twice a year, and the manager may want to use a different time period on each occasion. It would be very inconvenient, however, to get the software developer to change the code each time. By using substitution variables in the WHERE clause, though, the manager can simply call the query and supply parameter values at run-time. This makes a query script much more flexible and useful.

Creating a report

As an example, we will write a report that allows a manager to list the jobs with salaries that fall within a particular range.

Whenever you are creating a parameterised report, it is always best to start with a non-parameterised example and introduce variables for the parameters later. Start by creating a new SQL script file using SQL Developer or a text editor and paste in the following code which lists jobs with salaries between 5000 and 10000. Run the query in SQL Developer and observe the results.

    select job_title
    from   jobs
    where  min_salary >= 5000
    and    max_salary <= 10000;

Before moving on, are the results what you would expect? The top salary for an accountant, for example, is 9000 which is exactly in the range requested. The problem is that the minimum salary for an accountant is outside the range. It is good practice to check the results against the data when writing a new query to ensure that the code correctly implements the requirement. In this case, it would make more sense to list all jobs that can have salaries in the range 5000 - 10000, not just those which fall completely within the range. In that case, it might also be a good idea to include the salary range for each job. We should therefore change the code to the following. Run this new version and observe the results.

    select job_title, min_salary, max_salary
    from   jobs
    where  min_salary between 5000 and 10000
    or     max_salary between 5000 and 10000;

The results are difficult to understand aren't they? Another improvement would be to show the results in salary order. Add an ORDER BY clause as shown below and run the report again:

    select job_title, min_salary, max_salary
    from   jobs
    where  min_salary between 5000 and 10000
    or     max_salary between 5000 and 10000
    order by min_salary, max_salary;

There may still be improvements to make - for example, we may wish to exclude jobs where the maximum salary is equal to the lower range bound (5000 in this case). This would need to be checked with the manager who will run the report. This version of the report is sufficient for the purposes of this exercise, though.

Next we will replace the numbers in the WHERE clause with substitution variables so that we can re-use the same piece of code for any salary range. The code example below illustrates the format of substitution variables in Oracle SQL. Note that other databases may use different conventions. Copy this code into your script and run it in SQL Developer.

    select job_title, min_salary, max_salary
    from   jobs
    where  min_salary between &low and &high
    or     max_salary between &low and &high
    order by min_salary, max_salary;

Nearly there - it is now clear how the same piece of code can be run for different salary ranges. It is annoying, however, to have to put the same values in twice. To avoid this problem, Oracle provides a way to define persistent substitution variables. Simply use a double ampersand the first time a variable is referenced. If the same variable name is found later, the earlier value will automatically be used again. Try this last version in SQL Developer.

    select job_title, min_salary, max_salary
    from   jobs
    where  min_salary between &&low and &&high
    or     max_salary between &low and &high
    order by min_salary, max_salary;

Problem

Write a parameterised report which shows the employees hired in a particular year. You will need to consider which column to use in the WHERE clause and how to extract the year as well as where to use a substitution variable. You may find it useful to consult the Oracle SQL language documentation for information about date functions.