O'Reilly logo

SQL in a Nutshell, 3rd Edition by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Name

SET Statement

The SET statement assigns a value to a runtime variable. The variables may be platform-specific system variables or user-defined variables.

Platform

Command

MySQL

Supported

Oracle

Not supported

PostgreSQL

Supported

SQL Server

Supported

SQL2003 Syntax

SET variable = value

Keywords

variable

Denotes a system or user-defined variable.

value

Denotes a string or numeric value appropriate to the system or user-defined variable.

Rules at a Glance

Variable values are set for the duration of the session. The value assigned to a variable must match the datatype of the variable. For example, you cannot assign a string value to a variable that is declared with a numeric datatype. The actual command to create a variable varies from platform to platform. For example, Oracle and SQL Server use the DECLARE statement to declare a variable name and datatype, but other platforms may use other means.

The value assigned to a variable does not have to be a literal value. It may be a dynamic value that is derived from a subquery. For example, we assign the maximum employee ID to the emp_id_var variable in the following example:

DECLARE emp_id_var CHAR(5)
SET emp_id_var = (SELECT MAX(emp_id)
   FROM employees WHERE type = 'F')

In this example, an employee type of 'F' indicates that the employee is a full-time, salaried employee.

Programming Tips and Gotchas

The SET statement is easily transportable between the database platforms. Only Oracle uses a consistently different scheme for assigning values to variables. ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required