assignment operator in oracle sql

Home » PL/SQL Tutorial » PL/SQL Variables

PL/SQL Variables

Summary : in this tutorial, you will learn about PL/SQL variables and how to use them effectively.

In PL/SQL, a variable is named storage location that stores a value of a particular data type . The value of the variable changes through the program. Before using a variable, you must declare it in the declaration section of a block .

Declaring variables

The syntax for a variable declaration is as follows:

In this syntax:

• First, specify the name of the variable. The name of the variable should be as descriptive as possible, e.g., l_total_sales , l_credit_limit , and l_sales_revenue .
• Second, choose an appropriate data type for the variable, depending on the kind of value that you want to store, for example, number, character, Boolean, and datetime.

By convention, local variable names should start with l_ and global variable names should have a prefix of g_ .

The following example declares three variables l_total_sales , l_credit_limit , and l_contact_name :

Default values

PL/SQL allows you to set a default value for a variable at the declaration time. To assign a default value to a variable, you use the assignment operator ( := ) or the DEFAULT keyword.

The following example declares a variable named l_product_name with an initial value 'Laptop' :

It is equivalent to the following block:

In this example, instead of using the assignment operator := , we used the DEFAULT keyword to initialize a variable.

NOT NULL constraint

If you impose the NOT NULL constraint on a value, then the variable cannot accept a NULL value. Besides, a variable declared with the NOT NULL must be initialized with a non-null value. Note that PL/SQL treats a zero-length string as a NULL value.

The following example first declares a variable named l_shipping_status with the NOT NULL constraint. Then, it assigns the variable a zero-length string.

PL/SQL issued the following error:

Because the variable l_shipping_status declared with the NOT NULL constraint, it could not accept a NULL value or zero-length string in this case.

Variable assignments

To assign a value to a variable, you use the assignment operator ( := ), for example:

You can assign a value of a variable to another as shown in the following example:

Anchored declarations

Typically, you declare a variable and select a value from a table column for this variable. If the data type of the table column changes, you must adjust the program to make it work with the new type.

PL/SQL allows you to declare a variable whose data type anchor to a table column or another variable. Consider the following example:

In this example:

• First,  declare two variables l_customer_name and l_credit_limit whose data type anchors to the name and credit_limit columns respectively, in the declaration section of the block.
• Second, query the customer name and credit limit of the customer id 38 and assign these column values to the l_customer_name and l_credit_limit variables in the execution block.
• Third, display the customer name and credit limit.

PL/SQL returned the following output:

This example illustrates how to declare variables that anchor to another variable:

Here is the output:

Now, you should know how to use PL/SQL variables in your block and manipulate them efficiently.

Oracle PL/SQL Assignment Operator

Oracle pl/sql concatenation operator, oracle pl/sql like operator, oracle pl/sql regular expressions, oracle pl/sql metacharacters, oracle pl/sql contains, oracle pl/sql operator precedence, oracle pl/sql operator precedence demo, oracle pl/sql logical operators, oracle pl/sql null in unequal compare, oracle pl/sql null in equal compare, oracle pl/sql logical operator precedence, oracle pl/sql short-circuit evaluation, oracle pl/sql arithmetic comparisons, oracle pl/sql between operator, oracle pl/sql in, oracle pl/sql boolean expressions.

The assignment operator is specific to PL/SQL.

Its primary function is to set a variable equal to the value or expression on the other side of the operator.

If we wanted to set the variable v_price equal to 5.5 * .90, we would write it as follows:

An assignment can take place in the declaration section when a variable is first declared, or in the execution or exception sections at any time.

For example, to declare the variable v_price, we would write.

The variable is not yet initialized, and its value is NULL.

To initialize the variable to zero, we can use the assignment operator in the declaration section as follows:

In the execution section, the assignment operator is used for assigning constants, literals, other variables, or expressions to a variable.

Another use is to assign a function call to a variable where the return value of the function is ultimately stored.

• User's Guide and Reference
• Using SQL*Plus
• Using Substitution Variables

6 Using Substitution Variables

This chapter explains how SQL*Plus substitution variables work and where they can be used. It shows the relationship between the three types of variables (substitution, bind, and system) used in SQL*Plus.

This topics covered are:

Defining Substitution Variables

About Using Predefined Variables

Referencing Substitution Variables

System Variables Influencing Substitution Variables

Passing Parameters through the START Command

About Communicating with the User

About Using Bind Variables

Using REFCURSOR Bind Variables

Fetching Iterative Results from a SELECT inside a PL/SQL Block

6.1 Defining Substitution Variables

You can define variables, called substitution variables, for repeated use in a single script by using the SQL*Plus DEFINE command . Note that you can also define substitution variables to use in titles and to save your keystrokes (by defining a long string as the value for a variable with a short name).

To list all substitution variable definitions, enter DEFINE by itself. Note that any substitution variable you define explicitly through DEFINE takes only CHAR values (that is, the value you assign to the variable is always treated as a CHAR datatype). You can define a substitution variable of datatype NUMBER implicitly through the ACCEPT command. You will learn more about the ACCEPT command.

To delete a substitution variable , use the SQL*Plus command UNDEFINE followed by the variable name.

Example 6-1 Defining a Substitution Variable

To define a substitution variable L_NAME and give it the value "SMITH", enter the following command:

To confirm the variable definition, enter DEFINE followed by the variable name:

6.2 About Using Predefined Variables

There are nine variables containing SQL*Plus information that are defined during SQL*Plus installation. These variables can be redefined, referenced or removed the same as any other variable. They are always available from session to session unless you explicitly remove or redefine them.

Predefined Variables for a list of the predefined variables and examples of their use.

6.3 Referencing Substitution Variables

Suppose you want to write a query like the one in SALES to list the employees with various jobs, not just those whose job is SA_MAN. You could do that by editing a different value into the WHERE clause each time you run the command, but there is an easier way.

By using a substitution variable in place of the text, SA_MAN, in the WHERE clause, you can get the same results you would get if you had written the values into the command itself.

A substitution variable is preceded by one or two ampersands (&). When SQL*Plus encounters a substitution variable in a command, SQL*Plus executes the command as though it contained the value of the substitution variable, rather than the variable itself.

For example, if the variable SORTCOL has the value JOB_ID and the variable MYTABLE has the value EMP_DETAILS_VIEW, SQL*Plus executes the commands

as if they were

6.3.1 Where and How to Use Substitution Variables

You can use substitution variables anywhere in SQL and SQL*Plus commands, except as the first word entered. When SQL*Plus encounters an undefined substitution variable in a command, SQL*Plus prompts you for the value.

You can enter any string at the prompt, even one containing blanks and punctuation. If the SQL command containing the reference should have quote marks around the variable and you do not include them there, the user must include the quotes when prompted.

SQL*Plus reads your response from the keyboard or standard input.

After you enter a value at the prompt, SQL*Plus lists the line containing the substitution variable twice: once before substituting the value you enter and once after substitution. You can suppress this listing by setting the SET command variable VERIFY to OFF.

Now run the script STATS:

And respond to the prompts for values as shown:

SQL*Plus displays the following output:

A more practical use of substitution variables is to prompt for a value before referencing the variable:

If these two commands are stored in a SQL*Plus script, a different last name can be entered each time the script is run.

If you wish to append characters immediately after a substitution variable , use a period to separate the variable from the character. For example:

is interpreted as

If you want to append a period immediately after a substitution variable name, then use two periods together. For example, if "myfile" is defined as "reports" then the command:

is the same as:

Text in ANSI "/* */" or "--" comments that looks like a substitution variable may be treated as one. For example:

Here the text "& loc" in the comment is interpreted as a variable reference. SQL*Plus prompts you for a value for the variable "loc".

Example 6-2 Using Substitution Variables

Create a script named STATS, to be used to calculate a subgroup statistic (the maximum value) on a numeric column:

6.3.2 Difference Between "&" and "&&" Prefixes

Both single ampersand (&) and double ampersand (&&) can prefix a substitution variable name in a statement. SQL*Plus pre-processes the statement and substitutes the variable's value. The statement is then executed. If the variable was not previously defined then SQL*Plus prompts you for a value before doing the substitution.

If a single ampersand prefix is used with an undefined variable, the value you enter at the prompt is not stored. Immediately after the value is substituted in the statement the variable is discarded and remains undefined. If the variable is referenced twice, even in the same statement, then you are prompted twice. Different values can be entered at each prompt:

If a double ampersand reference causes SQL*Plus to prompt you for a value, then SQL*Plus defines the variable as that value (that is, the value is stored until you exit). Any subsequent reference to the variable (even in the same command) using either "&" or "&&" substitutes the newly defined value. SQL*Plus will not prompt you again:

6.3.3 Storing a Query Column Value in a Substitution Variable

Data stored in the database can be put into substitution variables:

The NEW_VALUE option in the COLUMN command implicitly creates a substitution variable called mynv . The variable is not physically created until a query references the column LAST_NAME . When the query finishes, the variable mynv holds the last retrieved value from the column LAST_NAME :

6.3.4 Restrictions

You cannot use substitution variables in the buffer editing commands, APPEND, CHANGE, DEL, and INPUT, nor in other commands where substitution would be meaningless. The buffer editing commands, APPEND, CHANGE, and INPUT, treat text beginning with "&" or "&&" literally, like any other text string.

6.3.5 How Substitution Variables are Handled in SQL*Plus

Substitution variable references are pre-processed and substituted before the command is parsed and executed. For each statement, SQL*Plus will do the following:

Step 1 happens inside the SQL*Plus client tool. SQL*Plus then sends the final statement to the database engine where step 2 occurs.

It is not possible to repeatedly prompt in a PL/SQL loop. This example prompts once and the entered value is substituted in the script text. The resulting script is then sent to the database engine for execution. The same entered value is stored five times in the table:

Substitution variables are not recursively expanded. If the value of a referenced variable contains an ampersand, then the ampersand is used literally and is not treated as a second variable prefix:

You cannot use a substitution variable as the first token of a command. Each command name must be hard-coded text else an error is displayed. For example:

6.3.6 Substitution Variable Commands

Substitution variables can be used to replace options and values in almost all SQL*Plus commands. Several of the commands have special significance for substitution variables.

See SQL*Plus Command Summary for more information about these substitution variable commands.

6.3.6.1 Using "&" Prefixes With Title Variables

The title commands (TTITLE, BTITLE, REPHEADER and REPFOOTER) substitute variables differently to most other commands. (The exceptions are the EXIT and SET SQLPROMPT commands, which are similar to the title commands).

If you want the same value for a variable to be printed on every page then use an "&" prefix and put the variable inside a quoted string:

If you want each title to have data from the query that is unique to each report page then do not use an "&" prefix for the variable and do not put the variable inside quotes.

SQL*Plus substitution variables are expanded before each command is executed. After this happens in a title command, the resulting string is stored as the title text. What makes variables in titles special is that they need to be re-substituted for each page of query results. This is so the current COLUMN NEW_VALUE and OLD_VALUE substitution variable values are displayed on each page, customizing each title for the results displayed on its page. If "&" is used inadvertently or incorrectly to prefix title variables, it is possible to get double substitution. This is dependent on the variable's value and is easily overlooked when you write scripts.

Any non-quoted, non-keyword in a title is checked when the page is printed to see if it is a variable. If it is, its value is printed. If not, then the word is printed verbatim. This means that if you use "&myvar" in a title command, and the text substituted for it can itself be interpreted as another variable name then you get double variable substitution. For example, the script:

causes the text "left scottsvar" to be stored as the title. When the title is printed on each page of the query this string is re-evaluated. The word "scottsvar" in the title is itself treated as a variable reference and substituted. The query output is:

Using "&" in titles most commonly causes a problem with the numeric variable names of the SQL*Plus script parameters. If the value of an arbitrary "&"-prefixed title variable is the same as a script parameter variable name, then double substitution will occur.

To display an "&" in a title, prefix it with the SET ESCAPE character. The ampersand (&) is stored as the title text and is not substituted when page titles are printed.

6.3.6.2 Variables and Text Spacing in Titles

Unquoted whitespace in titles is removed. Use whitespace instead of the SET CONCAT character to separate variables from text that should appear immediately adjacent. Use whitespace inside quotes to display a space. For example, the script:

gives a title of:

6.3.7 Substitution Variable Namespace, Types, Formats and Limits

Substitution Variable Namespace

In a SQL*Plus session there is just one global name space for substitution variables. If you reconnect using CONNECT, or run subscripts using "@", all variables ever defined are available for use and may be overridden or undefined.

When a child script finishes, all substitution variables it defined or changed are visible to the calling script. This is particularly noticeable when a subscript executed with "@" or START is given script parameters. The parameters "&1" etc. get redefined and the parent script sees the new values.

The call to myscript.sql changes the value of "&1" to "King". By saving the original value of "&1" in "myuser" and using "&myuser" instead of "&1" in the SELECT, the query executes correctly.

Substitution Variable Types

The substitution variable types stored by SQL*Plus are:

• BINARY_FLOAT
• BINARY_DOUBLE

The CHAR type is a generic text format similar to the database table VARCHAR2 column type. All variables created from the following are of type CHAR:

• with DEFINE
• from prompts for "&" variables
• from script parameters

This ensures that values entered are substituted verbatim with no conversion loss.

Variables created by COLUMN NEW_VALUE or OLD_VALUE for the columns in Oracle number format will have the type NUMBER. These substitution variables are stored in Oracle's internal number representation as they are in the database. This allows display formats to be altered without any internal value loss. Substitution variables of BINARY_FLOAT and BINARY_DOUBLE types are similarly created for Oracle BINARY_FLOAT and BINARY_DOUBLE columns. These variables are stored in native machine representation. The CHAR type is used for NEW_VALUE and OLD_VALUE variables with all other column types.

There is no explicit DATE type. The DATE keyword in the ACCEPT command is used solely to allow correct format validation against a date format. Substitution variables created by ACCEPT ... DATE, or by COLUMN NEW_VALUE on a date column, are stored as type CHAR. For example:

If a variable already exists and is redefined, its old type is discarded and the new type used.

The type of a substitution variable is generally transparent. Substitution variables are weakly typed. For example, a COLUMN NEW_VALUE variable takes on the particular type of the named column in each new query. It may also change type during a query. For example, the type of a substitution variable used on a NUMBER column changes from NUMBER to CHAR when a NULL value is fetched. It changes back to NUMBER when the next numeric value is fetched.

No type comparison semantics are defined for any type since there is no direct comparison of variables. All variables are textually substituted before any SQL or PL/SQL statement that could do a comparison is executed.

Substitution Variable Formats

When a variable is substituted, or its value is shown by a DEFINE command, it is formatted as text before the command referencing the variable is finally executed.

CHAR variables are substituted verbatim.

NUMBER variables are formatted according to SET NUMWIDTH (by default) or SET NUMFORMAT (if you have explicitly set one):

The display format of a number can be changed even after the variable is created. To show this, first create a NUMBER variable. You cannot use DEFINE to do this because it makes the type of all new variables CHAR. Instead use a COLUMN NEW_VALUE command which inherits the NUMBER type from a NUMBER column:

Changing the format affects the display of the number but not the stored value:

Substitution Variable Limits

The maximum number of substitution variables allowed is 2048. SQL*Plus gives an error an attempt is made to create more. The limit includes the predefined variables, however these can be undefined if necessary. Leaving a large number of unnecessarily defined variables can reduce the performance of SQL*Plus because variable lookups are slower.

A character substitution variable can be up to 240 bytes long.

A numeric substitution variable holds the full range of Oracle numbers.

When a command line undergoes variable substitution, the resulting line length can be no more than:

• 3000 bytes if it is a line of SQL (like SELECT or INSERT) or PL/SQL text (like BEGIN or CREATE PROCEDURE)
• 2499 bytes if it a line of a SQL*Plus command (like TTITLE or COLUMN)

Otherwise an error is displayed.

These limits may be lower in old versions of SQL*Plus.

6.3.8 Assigning Substitution Variables to Bind Variables

You can assign a substitution variable to a bind variable:

SQL*Plus executes the PL/SQL assignment statement after it substitutes the value of "mysubv". If "mysubv" was not already defined, you would be prompted for a value.

The bind variable can be used in subsequent SQL or PL/SQL commands.

6.3.9 Assigning Bind Variables to Substitution Variables

Sometimes it is useful to make the value of a bind variable available to SQL*Plus commands like TTITLE or SPOOL. For example, you might want to call a PL/SQL function that returns a string and use the value for a SQL*Plus spool file name. The SPOOL command does not understand bind variable syntax so the bind variable value needs to be assigned to a substitution variable first.

This is done using COLUMN NEW_VALUE and SELECT commands. For example, declare a bind variable in SQL*Plus and instantiate it in a PL/SQL block. Its value can be returned from a PL/SQL function, or like here, set by a direct assignment:

Pass the bind variable's value to a new substitution variable "nv" by using a query:

Now you can use the substitution variable in a SPOOL command:

The SPOOL command executes as if you had typed

6.3.10 Substitution Variable Examples

The following examples demonstrate how to use substitution variables.

Setting a Substitution Variable's Value

Using a Substitution Variable

Finding All Defined Substitution Variables

Inserting Data Containing "&" Without Being Prompted

Putting the Current Date in a Spool File Name

Appending Alphanumeric Characters Immediately After a Substitution Variable

Putting a Period After a Substitution Variable

Using a Fixed Value Variable in a TTITLE, BTITLE, REPHEADER or REPFOOTER

Using a Changing Value Variable in a TTITLE, BTITLE, REPHEADER or REPFOOTER

Using the Value of a Bind Variable in a SQL*Plus Command Like SPOOL

Passing Parameters to SQL*Plus Substitution Variables

Passing Operating System Variables to SQL*Plus

Passing a Value to a PL/SQL Procedure From the Command Line

Allowing Script Parameters to be Optional and Have a Default Value

Using a Variable for the SQL*Plus Return Status

Putting the Username and Database in the Prompt

6.3.10.1 Setting a Substitution Variable's Value

A substitution variable can be set in several ways. The common ways are as follows:

The DEFINE command sets an explicit value:

The ACCEPT command:

prompts you for a value and creates a character variable "myv" set to the text you enter.

Using "&&" before an undefined variable prompts you for a value and uses that value in the statement:

If the substitution variable "myuser" is not already defined, then this statement creates "myuser" and sets it to the value you enter.

Using COLUMN NEW_VALUE to set a substitution variable to a value stored in the database:

This creates a substitution variable "mynv" set to the value in the "last_name" column.

6.3.10.2 Using a Substitution Variable

Once a substitution variable has a value, it can be referenced by prefixing the variable name with an ampersand (&).

If the variable "myv" is already defined, it can be used as:

6.3.10.3 Finding All Defined Substitution Variables

might give:

6.3.10.4 Inserting Data Containing "&" Without Being Prompted

The INSERT statement stores the text "thick & thin" in the table.

The second method is useful for ignoring individual occurrences of "&" while allowing others to prefix substitution variables:

The first INSERT statement in this method stores the text "thick & thin" in the table. The second INSERT causes SQL*Plus to prompt you for a value, which is then stored.

6.3.10.5 Putting the Current Date in a Spool File Name

Using SYSDATE you can query the current date and put it in a substitution variable. The substitution variable can then be used in a SPOOL command:

In this example, the first query puts the date in the substitution variable "mydate". There is no visible output from this query because of the NOPRINT option in the COLUMN command. In the SPOOL command, the first period (.) indicates the end of the variable name and is not included in the resulting string. If "mydate" contained "20030120" from the first query, then the spool file name would be "20030120report.txt".

You can use this technique to build up any string for the file name.

The period is the default value of SET CONCAT. If you have assigned another character, use it instead of a period to end the substitution variable name.

6.3.10.6 Appending Alphanumeric Characters Immediately After a Substitution Variable

creates a file with the name "MelbourneAustralia.txt".

6.3.10.7 Putting a Period After a Substitution Variable

6.3.10.8 using a fixed value variable in a ttitle, btitle, repheader or repfooter, 6.3.10.9 using a changing value variable in a ttitle, btitle, repheader or repfooter.

In a BTITLE or REPFOOTER command, use a COLUMN OLD_VALUE variable instead of a COLUMN NEW_VALUE variable.

6.3.10.10 Using the Value of a Bind Variable in a SQL*Plus Command Like SPOOL

If you want to use the value of a bind variable in a SQL*Plus command, it must first be copied to a substitution variable.

SQL*Plus commands such as SPOOL, SET and TTITLE are executed in the SQL*Plus program and are not passed to the database for execution. Because of this, these commands do not understand bind variables.

To use a bind variable's value as the name of a spool file:

6.3.10.11 Passing Parameters to SQL*Plus Substitution Variables

You can pass parameters on the command line to a SQL*Plus script:

They can be referenced in the script using "&1" and "&2". For example, myscript.sql could be:

Here the "SET VERIFY OFF" command stops SQL*Plus from echoing the SQL statement before and after the variables are substituted. The query returns the employee identifier for the employee "De Haan" from the "employees" table.

Parameters can also be passed to scripts called within SQL*Plus:

6.3.10.12 Passing Operating System Variables to SQL*Plus

or in a Windows command window:

The script myscript.sql could reference the substitution variable "&1" to see the passed name.

6.3.10.13 Passing a Value to a PL/SQL Procedure From the Command Line

and myscript.sql contains:

then calling:

executes the script as if it is:

This method does not work if the parameter "p1" to "myproc" is "IN OUT". The variable reference is pre-processed and is effectively a hardcoded value which cannot contain an OUT value. To get around this, you can assign the substitution variable to a bind variable. The script myscript.sql becomes:

6.3.10.14 Allowing Script Parameters to be Optional and Have a Default Value

you can call the script with or without a parameter. If you enter "12" at the prompt your screen looks like:

or if you call it with a parameter "8":

Note when you pass a parameter, the PROMPT text is still displayed, but you do not enter a value. The PROMPT command is the SQL*Plus "echo" or "print" statement. (It does not read input). The only occurrence of "&1" should be where "mypar" is defined. All other references to the parameter should use "&mypar" or "&&mypar".

6.3.10.15 Using a Variable for the SQL*Plus Return Status

No ampersand (&) prefix is required before the substitution variable name.

A numeric bind variable requires a colon (:) prefix:

6.3.10.16 Putting the Username and Database in the Prompt

For customized prompts that query the database, ensure that you explicitly DEFINE any referenced substitution variables. Glogin.sql and login.sql can get run when there is no database connection. Defining variables prevents the user being prompted for values when the query fails and the variables do not get defined by it:

SQL*Plus 9.2 and earlier do not re-execute glogin.sql and login.sql after CONNECT commands. Also, variables in the SQLPROMPT are not dynamically substituted. It is possible to use the query script given above, but note that the prompt will only be valid for the original connection.

6.4 System Variables Influencing Substitution Variables

The following system variables, specified with the SQL*Plus SET command, affect substitution variables :

See SET for more information about system variables.

6.4.1 System Variables in Titles and EXIT

There is a special syntax to reference system variables in TTITLE, BTITLE, REPHEADER, REPFOOTER, and EXIT commands. The name of each special variable is the same as the SHOW option prefixed with "SQL.".

The special variables that can be referenced include:

SQL.PNO - page number

SQL.LNO - line number

SQL.USER - current username

SQL.RELEASE - SQL*Plus version

SQL.SQLCODE - last Oracle "ORA" error number

For example:

System variables of numeric type, such as SQL.SQLCODE, are formatted using the same rules as numeric substitution variables.

The variables cannot be prefixed with an "&".

These variables are not substitution variables. The DEFINE command does not show them. They cannot be referenced in general commands. The system variables are not affected if you create substitution variables with the same name. For example, SQL.USER is not affected if you create a substitution variable called USER. The system variable SQL.RELEASE is not affected if the predefined substitution variable _O_RELEASE is changed.

6.5 Passing Parameters through the START Command

You can bypass the prompts for values associated with substitution variables by passing values to parameters in a script through the START command.

You do this by placing an ampersand (&) followed by a numeral in the script in place of a substitution variable. Each time you run this script, START replaces each &1 in the file with the first value (called an argument ) after START filename, then replaces each &2 with the second value, and so forth.

For example, you could include the following commands in a script called MYFILE:

In the following START command, SQL*Plus would substitute PU_CLERK for &1 and 3100 for &2 in the script MYFILE:

When you use arguments with the START command, SQL*Plus DEFINEs each parameter in the script with the value of the appropriate argument.

Now run the command with the parameter SA_MAN:

SQL*Plus lists the line of the SQL command that contains the parameter, before and after replacing the parameter with its value, and then displays the output:

You can use many parameters in a script. Within a script, you can refer to each parameter many times, and you can include the parameters in any order.

While you cannot use parameters when you run a command with RUN or slash (/), you could use substitution variables instead.

Before continuing, return the columns to their original heading by entering the following command:

Example 6-3 Passing Parameters through START

To create a new script based on SALES that takes a parameter specifying the job to be displayed, enter

6.5.1 Script Parameters

Parameters can be passed to SQL*Plus scripts. For example, from the command line:

You can also pass parameters when calling a SQL*Plus script from within a SQL*Plus session, for example:

Script parameters become defined substitution variables. The variable name for the first parameter is "1", the second is "2", etc. The effect is the same as starting SQL*Plus and typing:

Commands in myscript.sql can reference "&1" to get the value "King". A DEFINE command shows the parameter variable:

Script parameter variables have type CHAR, similar to variables explicitly created with DEFINE.

Quoting parameters with single or double quotes is allowed. This lets whitespace be used within parameters. Operating systems and scripting languages that call SQL*Plus handle quotes in different ways. They may or may not pass quotes to the SQL*Plus executable. For example, in a standard Bourne shell on UNIX, quotes around parameters are stripped before the parameters are passed to SQL*Plus, and SQL*Plus never sees the quotes.

It is recommended to check how quoted parameters are handled on your operating system with your patch level of SQL*Plus. For portability between UNIX and Windows environments use double quotes around parameters containing whitespace.

SQL*Plus Releases 8.1.7, 9.2.0.3 (and other 9.x versions patched for bug 2471872) and 10.1 onwards remove an outer set of single or double quotes from parameters passed on the SQL*Plus command line. This makes SQL*Plus behave the same way on operating systems that do not themselves strip quotes as it does when the operating system strips the quotes before calling SQL*Plus.

As an example of passing parameters, when SQL*Plus 10.1 is called in the UNIX shell script:

only one program parameter is defined. References in myscript.sql to "&1" are replaced with "Jack and Jill" (without quotes - because the shell script does not pass quotes to SQL*Plus).

6.6 About Communicating with the User

Three SQL*Plus commands—PROMPT, ACCEPT, and PAUSE—help you communicate with the end user. These commands enable you to send messages to the screen and receive input from the user, including a simple Return. You can also use PROMPT and ACCEPT to customize the prompts for values SQL*Plus automatically generates for substitution variables.

6.6.1 Receiving a Substitution Variable Value

Through PROMPT and ACCEPT , you can send messages to the end user and receive values from end-user input . PROMPT displays a message you specify on-screen to give directions or information to the user. ACCEPT prompts the user for a value and stores it in the substitution variabl e you specify. Use PROMPT in conjunction with ACCEPT when a prompt spans more than one line.

The TTITLE command sets the top title for your report. See About Defining Page and Report Titles and Dimensions for more information about the TTITILE command.

Finally, run the script, responding to the prompt for the title as shown:

Before continuing, turn the TTITLE command off:

Example 6-4 Prompting for and Accepting Input

To direct the user to supply a report title and to store the input in the variable MYTITLE for use in a subsequent query, first clear the buffer:

Next, set up a script as shown and save this file as PROMPT1:

6.6.2 Customizing Prompts for Substitution Variable

If you want to customize the prompt for a substitution variable value , use PROMPT and ACCEPT in conjunction with the substitution variable, as shown in the following example.

What would happen if you typed characters instead of numbers? Since you specified NUMBER after the variable name in the ACCEPT command, SQL*Plus will not accept a non-numeric value:

Try entering characters instead of numbers to the prompt for "Employee ID.", SQL*Plus will respond with an error message and prompt you again to re-enter the correct number:

When SQL*Plus prompts you to enter an Employee ID, enter the word "one" instead of a number:

Example 6-5 Using PROMPT and ACCEPT in Conjunction with Substitution Variables

As you have seen in Example 6-4 , SQL*Plus automatically generates a prompt for a value when you use a substitution variable. You can replace this prompt by including PROMPT and ACCEPT in the script with the query that references the substitution variable. First clear the buffer with:

To create such a file, enter the following:

Save this file as PROMPT2. Next, run this script. SQL*Plus prompts for the value of ENUMBER using the text you specified with PROMPT and ACCEPT:

SQL*Plus prompts you to enter an Employee ID:

6.6.3 Sending a Message and Accepting Return as Input

If you want to display a message on the user's screen and then have the user press Return after reading the message, use the SQL*Plus command PAUSE . For example, you might include the following lines in a script:

6.6.4 Clearing the Screen

If you want to clear the screen before displaying a report (or at any other time), include the SQL*Plus CLEAR command with its SCREEN clause at the appropriate point in your script, using the following format:

Before continuing to the next section, reset all columns to their original formats and headings by entering the following command:

6.7 About Using Bind Variables

Bind variables are variables you create in SQL*Plus and then reference in PL/SQL or SQL. If you create a bind variable in SQL*Plus, you can use the variable as you would a declared variable in your PL/SQL subprogram and then access the variable from SQL*Plus. You can use a bind variable as an input bind variable to hold data which can then be used in PL/SQL or SQL statements to insert data into the database. You can assign a value to a newly defined variable. The value assigned in this variable can then be used in a statement.

Because bind variables are recognized by SQL*Plus, you can display their values in SQL*Plus or reference them in PL/SQL subprograms that you run in SQL*Plus.

6.7.1 Creating Bind Variables

You create bind variables in SQL*Plus with the VARIABLE command. For example

This command creates a bind variable named ret_val with a datatype of NUMBER. See the VARIABLE command for more information. (To list all bind variables created in a session, type VARIABLE without any arguments.)

6.7.2 Referencing Bind Variables

You reference bind variables in PL/SQL by typing a colon (:) followed immediately by the name of the variable. For example

To change this bind variable in SQL*Plus, you must enter a PL/SQL block. For example:

This command assigns a value to the bind variable named ret_val .

6.7.3 Displaying Bind Variables

To display the value of a bind variable in SQL*Plus, you use the SQL*Plus PRINT command. For example:

This command displays a bind variable named ret_val. See PRINT for more information about displaying bind variables.

6.7.4 Executing an Input Bind

You can assign a value to a variable for input binding.

See the VARIABLE command for more information.

6.8 Using REFCURSOR Bind Variables

SQL*Plus REFCURSOR bind variables allow SQL*Plus to fetch and format the results of a SELECT statement contained in a PL/SQL block.

REFCURSOR bind variables can also be used to reference PL/SQL cursor variables in stored procedures. This enables you to store SELECT statements in the database and reference them from SQL*Plus.

A REFCURSOR bind variable can also be returned from a stored function.

The results from the SELECT statement can now be displayed in SQL*Plus with the PRINT command.

The PRINT statement also closes the cursor. To reprint the results, the PL/SQL block must be executed again before using PRINT.

Next, create the stored procedure containing an OPEN... FOR SELECT statement.

Execute the procedure with a SQL*Plus bind variable as the parameter.

Now print the bind variable.

The procedure can be executed multiple times using the same or a different REFCURSOR bind variable.

Execute the function.

The function can be executed multiple times using the same or a different REFCURSOR bind variable.

Example 6-6 Creating, Referencing, and Displaying REFCURSOR Bind Variables

To create, reference and display a REFCURSOR bind variable, first declare a local bind variable of the REFCURSOR datatype

Next, enter a PL/SQL block that uses the bind variable in an OPEN... FOR SELECT statement. This statement opens a cursor variable and executes a query. See OPEN Statement for information on the OPEN command and cursor variables.

In this example we are binding the SQL*Plus employee_info bind variable to the cursor variable.

Example 6-7 Using REFCURSOR Variables in Stored Procedures

A REFCURSOR bind variable is passed as a parameter to a procedure. The parameter has a REF CURSOR type. First, define the type.

Example 6-8 Using REFCURSOR Variables in Stored Functions

Create a stored function containing an OPEN... FOR SELECT statement:

6.9 Fetching Iterative Results from a SELECT inside a PL/SQL Block

SQL*Plus can iteratively fetch and format the results of a SELECT statement contained in a PL/SQL block or stored procedure. You do not need to define local REFCURSOR variables.

The results from the SELECT statements are displayed.

Example 6-9 Creating a PL/SQL Procedure

Create a PL/SQL procedure P4 which calls two statements.

Next, run the procedure to retrieve results iteratively from the SELECT statements in the procedure.

Oracle PL/SQL Tutorial

Learn Oracle SQL

• PL/SQL Operators

Oracle PL/SQL offers a variety of operators that can be used in various situations. These include:

Arithmetic operators are used to perform mathematical operations such as addition (+), subtraction (-), multiplication (*), division (/), and modulus (MOD).

Logical operators are used to evaluate Boolean expressions and return either TRUE or FALSE. The logical operators in PL/SQL include AND, OR, and NOT.

SET operators combine the results of two or more SELECT statements into a single result. The set operators in PL/SQL include UNION, UNION ALL, INTERSECT, and MINUS.

Comparison operators are used to compare two expressions and return a Boolean value. The comparison operators in PL/SQL include =, <>, >, <, >=, <=, IS NULL, IS NOT NULL, BETWEEN, IN, and LIKE.

It’s important to note that the order of precedence of these operators follows the standard arithmetic order of operations: Parentheses, Exponentiation, Multiplication and Division, Addition and Subtraction, Comparison, Logical Negation, Logical Conjunction, Logical Disjunction.

• PL/SQL Tutorial
• PL/SQL Functions
• PL/SQL Select Query
• PL/SQL Table Joins
• PL/SQL Cursors
• PL/SQL Collections and Records
• PL/SQL Triggers
• PL/SQL Views
• PL/SQL Exception Handling
• PL/SQL Sequential Control
• PL/SQL Iterative Control
• PL/SQL Control Structures
• PL/SQL Procedure
• PL/SQL Function
• PL/SQL Data Types
• PL/SQL Indexes
• PL/SQL Constraints
• PL/SQL %ROWTYPE Attribute
• PL/SQL %TYPE Attribute
• PL/SQL Variables
• PL/SQL Anonymous block
• PL/SQL Delete
• PL/SQL Update
• PL/SQL Insert
• PL/SQL Select
• PL/SQL Create Table
• PL/SQL Alter Table
• PL/SQL Add Column
• PL/SQL Modify Column
• PL/SQL IDENTITY Column
• PL/SQL Sequence
• PL/SQL Dynamic SQL
• PL/SQL REGEXP
• PL/SQL JSON
• BULK COLLECT

Learn PL/SQL

• PL/SQL DBMS_XMLGEN.SETROWTAG
• PL/SQL DBMS_XMLGEN.SETROWSETTAG
• PL/SQL DBMS_XMLGEN.GETXMLTYPE
• PL/SQL DBMS_XMLGEN.GETXML
• PL/SQL DBMS_XMLGEN.CLOSECONTEXT
• PL/SQL DBMS_XMLGEN.NEWCONTEXT
• PL/SQL DBMS_XMLGEN
• PL/SQL DBMS_XMLQUERY.setRowSetTag
• PL/SQL DBMS_XMLQUERY.setRowTag
• PL/SQL DBMS_XMLQUERY.SETMAXROWS
• PL/SQL DBMS_XMLQUERY.CLOSECONTEXT
• PL/SQL DBMS_XMLQUERY.GETXML
• PL/SQL DBMS_XMLQUERY.SETSQLQUERY
• PL/SQL DBMS_XMLQUERY.NEWCONTEXT
• PL/SQL DBMS_XMLQUERY
• PL/SQL DBMS_XMLDOM
• PL/SQL DBMS_XMLPARSER.FREEPARSER
• PL/SQL DBMS_XMLPARSER.NEWPARSER

SQL Tutorial

Sql database, sql references, sql examples, sql operators, sql arithmetic operators, sql bitwise operators, sql comparison operators.

Advertisement

SQL Compound Operators

Sql logical operators.

COLOR PICKER

Report Error

If you want to report an error, or if you want to make a suggestion, do not hesitate to send us an e-mail:

[email protected]

Top Tutorials

Top references, top examples, get certified.

Database.Guide

Sql operators.

This article provides a list of SQL operators, and can therefore be used as cheat sheet for SQL operators.

Not all of these operators are supported across all DBMS s. If in doubt, check the documentation for your specific DBMS.

SQL Arithmetic Operators

Sql bitwise operators, sql comparsion operators, sql compound operators, sql logical operators, sql set operators, sql string operators, sql unary operators, other sql operators, what are sql operators.

SQL operators are symbols that specify an action that is performed on one or more expressions.

SQL operators manipulate individual data items and return a result. The data items are called  operands  or  arguments . SQL operators are represented by special characters or by keywords. 

Here’s a simple example:

There are three operators in this SQL SELECT statement.

• The Equals operator ( = ) compares the equality of two expressions (the value of the PetName column and the string Fluffy )
• The Greater Than operator ( > ) compares two expressions and returns TRUE if the left operand has a value higher than the right operand; otherwise, the result is FALSE . In this case, it’s comparing the value of the DOB column and the date 2020-01-01 to see if that date is greater than the value of the DOB column.
• The AND operator combines two Boolean expressions and returns TRUE when both expressions are TRUE . In this case, it returns TRUE when PetName = 'Fluffy' and DOB > '2020-01-01' .

The operators listed on this page can be used to construct complex SQL queries that can help you return the data you need.

For more examples, see 12 Commonly Used SQL Operators .

Assignment Operator in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

Assignment Operator in SQL Server with Examples

In this article, I am going to discuss Assignment Operator in SQL Server with Examples. Please read our previous article, where we discussed Clauses in SQL Server . Before understanding Assignment Operator in SQL Server, let us first understand what are operators and why we need operators, and what are the different types of operators available in SQL Server.

What is an Operator in SQL Server?

A n operator is a symbol that performs some specific operation on operands or expressions. These operators are classified as follows in SQL Server.

• Assignment operator
• Arithmetic operator
• Comparison operator
• Logical operator
• Set operator

Note: In this article, I am going to discuss Assignment Operator,  rest of all other operators will discuss one by one from our upcoming articles.

Understanding the Assignment Operator in SQL Server:

Let us understand how to use the Assignment Operator in SQL Server with an example. We are going to use the following Employee table to understand the Assignment Operator.

Please use the below script to create and populate the Employee table with the required data.

Assignment operator:.

The assignment operator (=) in SQL Server is used to assign the values to a variable. The equal sign (=) is the only Transact-SQL assignment operator. In the following example, we create the @MyCounter variable, and then the assignment operator sets the @MyCounter variable to a value i.e. 1 .

DECLARE @MyCounter INT; SET @MyCounter = 1;

The assignment operator can also be used to establish the relationship between a column heading and the expression that defines the values for that column. The following example displays the column headings as FirstColumn and SecondColumn. The string ‘ abcd ‘ is displayed for all the rows in the FirstColumn column heading. Then, each Employee ID from the Employee table is listed in the SecondColumn column heading.

SELECT FirstColumn = ‘abcd’,  SecondColumn = ID  FROM Employee;

Compound Assignment Operators in SQL Server:

SQL SERVER 2008 has introduced a new concept of Compound Assignment Operators. The Compound Assignment Operators are available in many other programming languages for quite some time. Compound Assignment Operators are operated where variables are operated upon and assigned in the same line. Compound-assignment operators provide a shorter syntax for assigning the result of an arithmetic or bitwise operator. They perform the operation on the two operands before assigning the result to the first operand.

Example without using Compound Assignment Operators

The following example is without using Compound Assignment Operators.

Example using Compound Assignment Operators

The above example can be rewritten using Compound Assignment Operators as follows.

Following are the list of available compound operators in SQL Server

+= Adds some amount to the original value and sets the original value to the result. -= Subtracts some amount from the original value and sets the original value to the result. *= Multiplies by an amount and sets the original value to the result. /= Divides by an amount and sets the original value to the result. %= Divides by an amount and sets the original value to the modulo.

In the next article, I am going to discuss Arithmetic Operators in SQL Server. Here, in this article, I try to explain the Assignment Operator in SQL Server with Examples. I hope this article will help you with your needs. I would like to have your feedback. Please post your feedback, question, or comments about this article.

About the Author: Pranaya Rout

Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.

1 thought on “Assignment Operator in SQL Server”

Operators in SQL Server covers almost all the important areas of SQL. This tutorial is very good.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *