Variables are named temporary storage locations that support a particular data type in your PL/SQL program. You must declare them in the declaration section of a PL/SQL block. By “named,” I mean that you give each of your variables a name. They are temporary because the values you assign to variables typically exist only in memory (or are accessible in memory) while the PL/SQL block in which they are declared is executing. They are storage locations in memory. And they are declared to store a particular data type so PL/SQL knows how to create, store, access, and destroy them.
So far, all the SQL statements were executed with predetermined columns, conditions and their values. Suppose that you want a query that lists the employees with various jobs and not just those whose job_ID is SA_REP. You can edit the WHERE clause to provide a different value each time you run the command, but there is also an easier way.
Variable Naming
Like a SQL or database data type, PL/SQL variables must follow the identifier naming rules:
- A variable name must be less than 31 characters in length.
- A variable name must start with an uppercase or lowercase ASCII letter: A–Z or a–z. PL/SQL is not case-sensitive.
- A variable name may be composed of 1 letter, followed by up to 29 letters, numbers, or the underscore (_) character. You can also use the number (#) and dollar sign ($) characters.
Another interesting aspect of using interactive SQL statements is the ability to employ parameters. Parameters are variables that are written in SQL and reside within an application. Parameters can be passed into a SQL statement during runtime, allowing more flexibility for the user executing the statement. Many of the major implementations allow use of these parameters. Following are examples of passing parameters for Oracle and SQL Server.
Parameters in Oracle can be passed into an otherwise static SQL statement, as the following code shows:
SELECT EMPLOYEEID, LASTNAME, FIRSTNAME FROM EMPLOYEES WHERE EMPLOYEEID = '&EMP_ID'
The preceding SQL statement returns the EMPLOYEEID, LASTNAME, and FIRSTNAME for whatever EMP_ID you enter at the prompt.
The next statement prompts you for the city and the state. The query returns all data for those employees living in the city and state that you entered.
SELECT * FROM EMPLOYEES WHERE CITY = '&CITY' AND STATE = '&STATE'
You can use single-ampersand (&) substitution variables to temporarily store values. You can also predefine variables by using the DEFINE command. DEFINE creates and assigns a value to a variable.
Restricted Ranges of Data: Examples
- Reporting figures only for the current quarter or specified date range.
- Reporting on data relevant only to the user requesting the report.
- Displaying personnel only within a given department
Other Interactive Effects
Interactive effects are not restricted to direct user interaction with the WHERE clause. The same principles can also be used to achieve other goals, such as:
- Obtaining input values from a file rather than from a person.
- Passing values from one SQL statement to another.
Using the Single-Ampersand Substitution Variable
When running a report, users often want to restrict the data that is returned dynamically. SQL*Plus or SQL Developer provides this flexibility with user variables. Use an ampersand (&) to identify each variable in your SQL statement. However, you do not need to define the value of each variable.
Use a variable prefixed with an ampersand (&) to prompt the user for a value:
SELECT employee_id, last_name, salary, department_id FROM employees WHERE employee_id = &employee_num ;
When SQL Developer detects that the SQL statement contains an ampersand, you are prompted to enter a value for the substitution variable that is named in the SQL statement. After you enter a value and click the OK button, the results are displayed in the Results tab of your SQL Developer session.
Character and Date Values with Substitution Variables
In a WHERE clause, date and character values must be enclosed with single quotation marks. The same rule applies to the substitution variables. Enclose the variable with single quotation marks within the SQL statement itself.
Use single quotation marks for date and character values:
SELECT last_name, department_id, salary*12 FROM employees WHERE job_id = '&job_title' ;
Specifying Column Names, Expressions, and Text
You can use the substitution variables not only in the WHERE clause of a SQL statement, but also as substitution for column names, expressions, or text.
Example:
The example displays the employee number, last name, job title, and any other column that is specified by the user at run time, from the EMPLOYEES table. For each substitution variable in the SELECT statement, you are prompted to enter a value, and then click OK to proceed. If you do not enter a value for the substitution variable, you get an error when you execute the preceding statement.
Note: A substitution variable can be used anywhere in the SELECT statement, except as the first word entered at the command prompt.
SELECT employee_id, last_name, job_id,&column_name FROM employees WHERE &condition ORDER BY &order_column ;
Use double ampersand (&&) if you want to reuse the variable value without prompting the user each time:
SELECT employee_id, last_name, job_id, &&column_name FROM employees ORDER BY &column_name ;
You can use the double-ampersand (&&) substitution variable if you want to reuse the variable value without prompting the user each time. The user sees the prompt for the value only once. In the example above, the user is asked to give the value for the variable, column_name, only once. The value that is supplied by the user (department_id) is used for both display and ordering of data. If you run the query again, you will not be prompted for the value of the variable.
SQL Developer stores the value that is supplied by using the DEFINE command; it uses it again whenever you reference the variable name. After a user variable is in place, you need to use the UNDEFINE command to delete it:
UNDEFINE column_name