assignment statement in plsql

• Getting started with plsql
• Assignments model and language
• Assignments model in PL/SQL
• Bulk collect
• Collections and Records
• Exception Handling
• IF-THEN-ELSE Statement
• Object Types
• PLSQL procedure

plsql Assignments model and language Assignments model in PL/SQL

Fastest entity framework extensions.

All programming languages allow us to assign values to variables. Usually, a value is assigned to variable, standing on left side. The prototype of the overall assignment operations in any contemporary programming language looks like this:

This will assign right operand to the left operand. In PL/SQL this operation looks like this:

Left operand must be always a variable . Right operand can be value, variable or function:

When the code block is executed in SQL*Plus the following output is printed in console:

There is a feature in PL/SQL that allow us to assign "from right to the left". It's possible to do in SELECT INTO statement. Prototype of this instrunction you will find below:

This code will assign character literal to a local variable:

Asignment "from right to the left" is not a standard , but it's valuable feature for programmers and users. Generally it's used when programmer is using cursors in PL/SQL - this technique is used, when we want to return a single scalar value or set of columns in the one line of cursor from SQL cursor.

Further Reading:

• Assigning Values to Variables

Got any plsql Question?

• Advertise with us
• Cookie Policy
• Privacy Policy

Get monthly updates about new articles, cheatsheets, and tricks.

  Oracle Training   Oracle Tips   Oracle Forum   Class Catalog   Remote DBA   Oracle Tuning   Emergency 911   RAC Support   Apps Support   Analysis   Design   Implementation   Oracle Support

Question:  I want some examples of the PL/SQL assignment statement.

Answer:  PL/SQL allow for assignment statements as follows:

Text assignment statement:

myvar := 'hello'

Numeric assignment statement in PL/SQL:

  v_line := 'Area of a sphere of radius '||'&radius'||' is '||to_char(v_out);

Arra y assignment statement on PL/SQL:

objects_tab t_tab := t_tab();

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

• Interview Questions

PL/SQL Language Elements

Through this section of the PL/SQL tutorial you will learn the PL/SQL language elements, how to declare a block, definition of a collection, various types of statements and more.

Assignment Statement

CASE Statement The CASE statement chooses from a sequence of conditions and executes a corresponding statement. The CASE statement evaluates a single expression and compares it against several potential values, or evaluates multiple Boolean expressions and chooses the first one that is TRUE. Syntax:-

Cursor Declaration To execute a multi-row query, Oracle opens an unnamed work area that stores processing information. Syntax:-

EXECUTE IMMEDIATE Statement The EXECUTE IMMEDIATE statement executes a dynamic SQL statement or an anonymous PL/SQL block. You can use it to issue SQL statements that cannot be represented directly in PL/SQL, or to build up statements where you do not know all the table names, WHERE clauses, and so on in advance. Syntax:-

FORALL Statement The FORALL statement issues a series of static or dynamic DML statements, usually much faster than an equivalent FOR loop. It requires some setup code because each iteration of the loop must use values from one or more collections in its VALUES or WHERE clauses.

Learn from our SQL Server Interview Questions to crack top companies Interview 

GOTO Statement The GOTO statement branches unconditionally to a statement label or block label. The label must be unique within its scope and must precede an executable statement or a PL/SQL block. The GOTO statement transfers control to the labeled statement or block.

IF Statement The IF statement executes or skips a sequence of statements, depending on the value of a Boolean expression.

INSERT Statement The INSERT statement adds one or more new rows of data to a database table.

MERGE Statement The MERGE statement inserts some rows and updates others in a single operation. The decision about whether to update or insert into the target table is based upon a join condition: rows already in the target table that match the join condition are updated; otherwise, a row is inserted using values from a separate subquery.

NULL Statement The NULL statement is a no-op (no operation); it passes control to the next statement without doing anything. In the body of an IF-THEN clause, a loop, or a procedure, the NULL statement serves as a placeholder.

Object Type Declaration An object type is a user-defined composite datatype that encapsulates a data structure along with the functions and procedures needed to manipulate the data. The variables that form the data structure are called attributes. The functions and procedures that characterize the behavior of the object type are called methods. A special kind of method called the constructor creates a new instance of the object type and fills in its attributes. Object types must be created through SQL and stored in an Oracle database, where they can be shared by many programs. When you define an object type using the CREATE TYPE statement, you create an abstract template for some real-world objects. The template specifies the attributes and behaviors the object needs in the application environment.

OPEN-FOR Statement The OPEN-FOR statement executes the query associated with a cursor variable. It allocates database resources to process the query and identifies the result set – the rows that meet the query conditions. The cursor variable is positioned before the first row in the result set.

Package Declaration A package is a schema object that groups logically related PL/SQL types, items, and subprograms. Use packages when writing a set of related subprograms that form an application programming interface (API) that you or others might reuse. Packages have two parts: a specification and a body.

Procedure Declaration A procedure is a subprogram that can take parameters and be called. Generally, you use a procedure to perform an action. A procedure has two parts: the specification and the body. The specification (spec for short) begins with the keyword PROCEDURE and ends with the procedure name or a parameter list. Parameter declarations are optional. Procedures that take no parameters are written without parentheses. The procedure body begins with the keyword IS (or AS) and ends with the keyword END followed by an optional procedure name. The procedure body has three parts: an optional declarative part, an executable part, and an optional exception-handling part. The declarative part contains declarations of types, cursors, constants, variables, exceptions, and subprograms. These items are local and cease to exist when you exit the procedure. The executable part contains statements that assign values, control execution, and manipulate Oracle data. The exception-handling part contains handlers that deal with exceptions raised during execution.

ROLLBACK Statement The ROLLBACK statement is the inverse of the COMMIT statement. It undoes some or all database changes made during the current transaction.

%ROWTYPE Attribute The %ROWTYPE attribute provides a record type that represents a row in a database table. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable. Variables declared using %ROWTYPE are treated like those declared using a datatype name. You can use the %ROWTYPE attribute in variable declarations as a data type specifier.

SAVEPOINT Statement The SAVEPOINT statement names and marks the current point in the processing of a transaction. With the ROLLBACK TO statement, savepoints undo parts of a transaction instead of the whole transaction.

SERIALLY_REUSABLE Pragma The pragma SERIALLY_REUSABLE indicates that the package state is needed only for the duration of one call to the server. An example could be an OCI call to the database or a stored procedure call through a database link. After this call, the storage for the package variables can be reused, reducing the memory overhead for long-running sessions.

SET TRANSACTION Statement The SET TRANSACTION statement begins a read-only or read-write transaction, establishes an isolation level, or assigns the current transaction to a specified rollback segment. Read-only transactions are useful for running multiple queries against one or more tables while other users update the same tables.

SQL Cursor Oracle implicitly opens a cursor to process each SQL statement not associated with an explicit cursor. In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always has the attributes %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. They provide information about the execution of data manipulation statements. The SQL cursor has additional attributes, %BULK_ROWCOUNT, and %BULK_EXCEPTIONS, designed for use with the FORALL statement.

SQLCODE Function The function SQLCODE returns the number code of the most recent exception. For internal exceptions, SQLCODE returns the number of the associated Oracle error. The number that SQLCODE returns is negative unless the Oracle error is no data found, in which case SQLCODE returns +100. For user-defined exceptions, SQLCODE returns +1, or a value you assign if the exception is associated with an Oracle error number through pragma EXCEPTION_INIT.

Wish to crack PL/SQL interviews? Intellipaat’s Top PL/SQL Interview Questions are meant only for you!

%TYPE Attribute The %TYPE attribute lets use the datatype of a field, record, nested table, database column, or variable in your own declarations, rather than hardcoding the type names. You can use the %TYPE attribute as a datatype specifier when declaring constants, variables, fields, and parameters. If the types that you reference change, your declarations are automatically updated. This technique saves you from making code changes when, for example, the length of a VARCHAR2 column is increased.

Course Schedule

Find SQL Certification Training in Other Regions

Bangalore   Chennai   Virginia   Kansas City   Texas   Pune   Hyderabad Dublin London Chicago San Francisco Denver Seattle Mumbai Delhi Kolkata Malaysia San Diego San Jose New Jersey Adelaide Atlanta Boston Johannesburg Noida Phoenix Orange County Philippines Singapore Sydney Toronto Melbourne Minneapolis New York Perth Charlotte Cincinnati Columbus Manchester Detroit Velachery Manila Maryland Miami Michigan Minnesota Nashville New Zeal South Africa St Louis Dallas Tampa Los Angeles Houston Brisbane Calgary Baltimore Bay Area Bellevue Birmingham Northern Virginia Washington Edinburgh USA Bristol

Course Preview

Expert-Led No.1

Home » PL/SQL Variables

PL/SQL Variables

Summary : in this tutorial, you will learn about PL/SQL variables that help you manipulate data in PL/SQL programs.

In PL/SQL, a variable is a meaningful name of a temporary storage location that supports a particular data type in a program. Before using a variable, you need to declare it first in the declaration section of a  PL/SQL block .

PL/SQL variables naming rules

Like other programming languages, a variable in PL/SQL must follow the naming rules as follows:

• The variable name must be less than 31 characters. Try to make it as meaningful as possible within 31 characters.
• The variable name must begin with an ASCII letter. It can be either lowercase or uppercase. Notice that PL/SQL is case-insensitive, which means  v_data  and V_DATA refer to the same variable.
• Followed by the first character are any number, underscore ( _ ), and dollar sign ( $ ) characters. Once again, do not make your variables hard to read and difficult to understand.

PL/SQL variables naming convention

It is highly recommended that you should follow the naming conventions listed in the following table to make the variables obvious in PL/SQL programs:

Each organization has its own development naming convention guidelines. Make sure that you comply with your organization’s naming convention guidelines.

For example, if you want to declare a variable that holds the first name of the employee with the VARCHAR2 data type, the variable name should be v_first_name .

PL/SQL Variables Declaration

To declare a variable, you use a variable name followed by the data type and terminated by a semicolon ( ; ). You can also explicitly add a length constraint to the data type within parentheses. The following illustrates some examples of declaring variables in a PL/SQL  anonymous block :

PL/SQL variable anchors

In PL/SQL program, one of the most common tasks is to select values from columns in a table into a set of variables. In case the data types of columns of the table changes, you have to change the PL/SQL program to make the types of the variables compatible with the new changes.

PL/SQL provides you with a very useful feature called variable anchors . It refers to the use of the  %TYPE   keyword to declare a variable with the data type is associated with a column’s data type of a particular column in a table.

Let’s take a look at the employees table in HR sample database provided by Oracle:

The v_first_name  variable has a data type that is the same as the data type of the first_name column in the  emloyees    table .  In case the data type of the first_name column changes, the type of the v_first_name variable automatically inherits the new data type of the column.

PL/SQL variable assignment

In PL/SQL, to assign a value or a variable to another, you use the assignment operator ( :=  ) which is a colon( : ) followed by the equal sign( = ).

Please see the code listing below to get a better understanding:

In the example above, we assigned Mary  to v_first_name variable, Jane to v_last_name variable, and result of the to_date function to d_hire_date variable.

You can use INTO of the SELECT  statement to assign a value to a variable. The INTO clause moves the values from the SELECT query’s column list into corresponding PL/SQL variables.

Initializing variables

When you declare a variable, its value is uninitialized and hence is NULL . You can initialize variable a value in declaration section by using variable assignment.

See the following example:

In PL/SQL, NULL means an unknown value so it has some special characteristics as follows:

• NULL is not equal to anything, even itself NULL .
• NULL is not greater than or less than anything else, even NULL .
• You cannot use logical operator equal ( = ) or ( <> ) with NULL . You must use the SQL  IS NULL  or IS NOT NULL  to test the NULL values.

In this tutorial, we have shown you how to declare, assign and initialize PL/SQL variables. We also walked you through how to declare PL/SQL variables using variable anchors to make your code more flexible and adaptable to the changes in columns of the database tables.

• Database PL/SQL Language Reference
• PL/SQL Language Fundamentals

3 PL/SQL Language Fundamentals

The PL/SQL language fundamental components are explained.

Character Sets

Lexical Units

Declarations

References to Identifiers

Scope and Visibility of Identifiers

Assigning Values to Variables

Expressions

Error-Reporting Functions

Conditional Compilation

3.1 Character Sets

Any character data to be processed by PL/SQL or stored in a database must be represented as a sequence of bytes. The byte representation of a single character is called a character code . A set of character codes is called a character set .

Every Oracle database supports a database character set and a national character set. PL/SQL also supports these character sets. This document explains how PL/SQL uses the database character set and national character set.

Database Character Set

National Character Set

About Data-Bound Collation

Oracle Database Globalization Support Guide for general information about character sets

3.1.1 Database Character Set

PL/SQL uses the database character set to represent:

Stored source text of PL/SQL units

For information about PL/SQL units, see " PL/SQL Units and Compilation Parameters " .

Character values of data types CHAR , VARCHAR2 , CLOB , and LONG

For information about these data types, see " SQL Data Types " .

The database character set can be either single-byte, mapping each supported character to one particular byte, or multibyte-varying-width, mapping each supported character to a sequence of one, two, three, or four bytes. The maximum number of bytes in a character code depends on the particular character set.

Every database character set includes these basic characters:

Latin letters: A through Z and a through z

Decimal digits: 0 through 9

Punctuation characters in Table 3-1

Whitespace characters: space , tab , new line , and carriage return

PL/SQL source text that uses only the basic characters can be stored and compiled in any database. PL/SQL source text that uses nonbasic characters can be stored and compiled only in databases whose database character sets support those nonbasic characters.

Table 3-1 Punctuation Characters in Every Database Character Set

Oracle Database Globalization Support Guide for more information about the database character set

3.1.2 National Character Set

PL/SQL uses the national character set to represent character values of data types NCHAR , NVARCHAR2 and NCLOB .

" SQL Data Types " for information about these data types

Oracle Database Globalization Support Guide for more information about the national character set

3.1.3 About Data-Bound Collation

Collation (also called sort ordering) is a set of rules that determines if a character string equals, precedes, or follows another string when the two strings are compared and sorted.

Different collations correspond to rules of different spoken languages. Collation-sensitive operations are operations that compare text and need a collation to control the comparison rules. The equality operator and the built-in function INSTR are examples of collation-sensitive operations.

Starting with Oracle Database 12c release 2 (12.2) , a new architecture provides control of the collation to be applied to operations on character data. In the new architecture, collation becomes an attribute of character data, analogous to a data type. You can now declare collation for a column and this collation is automatically applied by all collation-sensitive SQL operations referencing the column. The data-bound collation feature uses syntax and semantics compatible with the ISO/IEC SQL standard.

The PL/SQL language has limited support for the data-bound collation architecture. All data processed in PL/SQL expressions is assumed to have the compatibility collation USING_NLS_COMP . This pseudo-collation instructs collation-sensitive operators to behave in the same way as in previous Oracle Database releases. That is, the values of the session parameters NLS_COMP and NLS_SORT determine the collation to use. However, all SQL statements embedded or constructed dynamically in PL/SQL fully support the new architecture.

A new property called default collation has been added to tables, views, materialized views, packages, stored procedures, stored functions, triggers, and types. The default collation of a unit determines the collation for data containers, such as columns, variables, parameters, literals, and return values, that do not have their own explicit collation declaration in that unit. The default collation for packages, stored procedures, stored functions, triggers, and types must be USING_NLS_COMP .

For syntax and semantics, see the DEFAULT COLLATION Clause .

CREATE FUNCTION Statement

CREATE PACKAGE Statement

CREATE PROCEDURE Statement

CREATE TRIGGER Statement

CREATE TYPE Statement

Oracle Database Globalization Support Guide for more information about specifying data-bound collation for PL/SQL units

Oracle Database Globalization Support Guide for more information about effective schema default collation

3.2 Lexical Units

The lexical units of PL/SQL are its smallest individual components—delimiters, identifiers, literals, pragmas, and comments.

Identifiers

Whitespace Characters Between Lexical Units

3.2.1 Delimiters

A delimiter is a character, or character combination, that has a special meaning in PL/SQL.

Do not embed any others characters (including whitespace characters) inside a delimiter.

Table 3-2 summarizes the PL/SQL delimiters.

Table 3-2 PL/SQL Delimiters

3.2.2 Identifiers

Identifiers name PL/SQL elements, which include:

Reserved words

Subprograms

Every character in an identifier, alphabetic or not, is significant. For example, the identifiers lastname and last_name are different.

You must separate adjacent identifiers by one or more whitespace characters or a punctuation character.

Except as explained in " Quoted User-Defined Identifiers " , PL/SQL is case-insensitive for identifiers. For example, the identifiers lastname , LastName , and LASTNAME are the same.

Reserved Words and Keywords

Predefined Identifiers

User-Defined Identifiers

3.2.2.1 Reserved Words and Keywords

Reserved words and keywords are identifiers that have special meaning in PL/SQL.

You cannot use reserved words as ordinary user-defined identifiers. You can use them as quoted user-defined identifiers, but it is not recommended. For more information, see " Quoted User-Defined Identifiers " .

You can use keywords as ordinary user-defined identifiers, but it is not recommended.

For lists of PL/SQL reserved words and keywords, see Table D-1 and Table D-2 , respectively.

3.2.2.2 Predefined Identifiers

Predefined identifiers are declared in the predefined package STANDARD .

An example of a predefined identifier is the exception INVALID_NUMBER .

For a list of predefined identifiers, connect to Oracle Database as a user who has the DBA role and use this query:

You can use predefined identifiers as user-defined identifiers, but it is not recommended. Your local declaration overrides the global declaration (see " Scope and Visibility of Identifiers " ).

3.2.2.3 User-Defined Identifiers

A user-defined identifier is:

Composed of characters from the database character set

Either ordinary or quoted

Make user-defined identifiers meaningful. For example, the meaning of cost_per_thousand is obvious, but the meaning of cpt is not.

Avoid using the same user-defined identifier for both a schema and a schema object. This decreases code readability and maintainability and can lead to coding mistakes. Note that local objects have name resolution precedence over schema qualification.

For more information about database object naming rules, see Oracle Database SQL Language Reference .

For more information about PL/SQL-specific name resolution rules, see " Differences Between PL/SQL and SQL Name Resolution Rules ".

3.2.2.3.1 Ordinary User-Defined Identifiers

An ordinary user-defined identifier:

Begins with a letter

Can include letters, digits, and these symbols:

Dollar sign ($)

Number sign (#)

Underscore (_)

Is not a reserved word (listed in Table D-1 ).

The database character set defines which characters are classified as letters and digits. If COMPATIBLE is set to a value of 12.2 or higher, the representation of the identifier in the database character set cannot exceed 128 bytes. If COMPATIBLE is set to a value of 12.1 or lower, the limit is 30 bytes.

Examples of acceptable ordinary user-defined identifiers:

Examples of unacceptable ordinary user-defined identifiers:

3.2.2.3.2 Quoted User-Defined Identifiers

A quoted user-defined identifier is enclosed in double quotation marks.

Between the double quotation marks, any characters from the database character set are allowed except double quotation marks, new line characters, and null characters. For example, these identifiers are acceptable:

If COMPATIBLE is set to a value of 12.2 or higher, the representation of the quoted identifier in the database character set cannot exceed 128 bytes (excluding the double quotation marks). If COMPATIBLE is set to a value of 12.1 or lower, the limit is 30 bytes.

A quoted user-defined identifier is case-sensitive, with one exception: If a quoted user-defined identifier, without its enclosing double quotation marks, is a valid ordinary user-defined identifier, then the double quotation marks are optional in references to the identifier, and if you omit them, then the identifier is case-insensitive.

It is not recommended, but you can use a reserved word as a quoted user-defined identifier. Because a reserved word is not a valid ordinary user-defined identifier, you must always enclose the identifier in double quotation marks, and it is always case-sensitive.

Example 3-1 Valid Case-Insensitive Reference to Quoted User-Defined Identifier

In this example, the quoted user-defined identifier "HELLO" , without its enclosing double quotation marks, is a valid ordinary user-defined identifier. Therefore, the reference Hello is valid.

Example 3-2 Invalid Case-Insensitive Reference to Quoted User-Defined Identifier

In this example, the reference "Hello" is invalid, because the double quotation marks make the identifier case-sensitive.

Example 3-3 Reserved Word as Quoted User-Defined Identifier

This example declares quoted user-defined identifiers "BEGIN" , "Begin" , and "begin" . Although BEGIN , Begin , and begin represent the same reserved word, "BEGIN" , "Begin" , and "begin" represent different identifiers.

Example 3-4 Neglecting Double Quotation Marks

This example references a quoted user-defined identifier that is a reserved word, neglecting to enclose it in double quotation marks.

Example 3-5 Neglecting Case-Sensitivity

This example references a quoted user-defined identifier that is a reserved word, neglecting its case-sensitivity.

3.2.3 Literals

A literal is a value that is neither represented by an identifier nor calculated from other values.

For example, 123 is an integer literal and 'abc' is a character literal, but 1+2 is not a literal.

PL/SQL literals include all SQL literals (described in Oracle Database SQL Language Reference ) and BOOLEAN literals (which SQL does not have). A BOOLEAN literal is the predefined logical value TRUE , FALSE , or NULL . NULL represents an unknown value.

Like Oracle Database SQL Language Reference , this document uses the terms character literal and string interchangeably.

When using character literals in PL/SQL, remember:

Character literals are case-sensitive.

For example, 'Z' and 'z' are different.

Whitespace characters are significant.

For example, these literals are different:

PL/SQL has no line-continuation character that means "this string continues on the next source line." If you continue a string on the next source line, then the string includes a line-break character.

For example, this PL/SQL code:

Prints this:

If your string does not fit on a source line and you do not want it to include a line-break character, then construct the string with the concatenation operator ( || ).

For more information about the concatenation operator, see " Concatenation Operator " .

'0' through '9' are not equivalent to the integer literals 0 through 9.

However, because PL/SQL converts them to integers, you can use them in arithmetic expressions.

A character literal with zero characters has the value NULL and is called a null string .

However, this NULL value is not the BOOLEAN value NULL .

An ordinary character literal is composed of characters in the database character set .

For information about the database character set, see Oracle Database Globalization Support Guide .

A national character literal is composed of characters in the national character set .

For information about the national character set, see Oracle Database Globalization Support Guide .

You can use Q or q as part of the character literal syntax to indicate that an alternative quoting mechanism will be used. This mechanism allows a wide range of delimiters for a string as opposed to simply single quotation marks.

For more information about the alternative quoting mechanism, see Oracle Database SQL Language Reference .

You can view and run examples of the Q mechanism at Alternative Quoting Mechanism (''Q'') for String Literals

3.2.4 Pragmas

A pragma is an instruction to the compiler that it processes at compile time.

A pragma begins with the reserved word PRAGMA followed by the name of the pragma. Some pragmas have arguments. A pragma may appear before a declaration or a statement. Additional restrictions may apply for specific pragmas. The extent of a pragma’s effect depends on the pragma. A pragma whose name or argument is not recognized by the compiler has no effect.

For information about pragmas syntax and semantics, see :

" AUTONOMOUS_TRANSACTION Pragma "

" COVERAGE Pragma "

" DEPRECATE Pragma "

" EXCEPTION_INIT Pragma "

" INLINE Pragma "

" RESTRICT_REFERENCES Pragma "

" SERIALLY_REUSABLE Pragma "

" SUPPRESSES_WARNING_6009 Pragma "

" UDF Pragma "

3.2.5 Comments

The PL/SQL compiler ignores comments. Their purpose is to help other application developers understand your source text.

Typically, you use comments to describe the purpose and use of each code segment. You can also disable obsolete or unfinished pieces of code by turning them into comments.

Single-Line Comments

Multiline Comments

" Comment "

3.2.5.1 Single-Line Comments

A single-line comment begins with -- and extends to the end of the line.

Do not put a single-line comment in a PL/SQL block to be processed dynamically by an Oracle Precompiler program. The Oracle Precompiler program ignores end-of-line characters, which means that a single-line comment ends when the block ends.

While testing or debugging a program, you can disable a line of code by making it a comment. For example:

Example 3-6 Single-Line Comments

This example has three single-line comments.

3.2.5.2 Multiline Comments

A multiline comment begins with /* , ends with */ , and can span multiple lines.

You can use multiline comment delimiters to "comment out" sections of code. When doing so, be careful not to cause nested multiline comments. One multiline comment cannot contain another multiline comment. However, a multiline comment can contain a single-line comment. For example, this causes a syntax error:

This does not cause a syntax error:

Example 3-7 Multiline Comments

This example has two multiline comments. (The SQL function TO_CHAR returns the character equivalent of its argument. For more information about TO_CHAR , see Oracle Database SQL Language Reference .)

3.2.6 Whitespace Characters Between Lexical Units

You can put whitespace characters between lexical units, which often makes your source text easier to read.

Example 3-8 Whitespace Characters Improving Source Text Readability

3.3 Declarations

A declaration allocates storage space for a value of a specified data type, and names the storage location so that you can reference it.

You must declare objects before you can reference them. Declarations can appear in the declarative part of any block, subprogram, or package.

Declaring Variables

Declaring Constants

Initial Values of Variables and Constants

NOT NULL Constraint

Declaring Items using the %TYPE Attribute

For information about declaring objects other than variables and constants, see the syntax of declare_section in " Block " .

3.3.1 NOT NULL Constraint

You can impose the NOT NULL constraint on a scalar variable or constant (or scalar component of a composite variable or constant).

The NOT NULL constraint prevents assigning a null value to the item. The item can acquire this constraint either implicitly (from its data type) or explicitly.

A scalar variable declaration that specifies NOT NULL , either implicitly or explicitly, must assign an initial value to the variable (because the default initial value for a scalar variable is NULL ).

PL/SQL treats any zero-length string as a NULL value. This includes values returned by character functions and BOOLEAN expressions.

To test for a NULL value, use the " IS [NOT] NULL Operator " .

Example 3-9 Variable Declaration with NOT NULL Constraint

In this example, the variable acct_id acquires the NOT NULL constraint explicitly, and the variables a , b , and c acquire it from their data types.

Example 3-10 Variables Initialized to NULL Values

In this example, all variables are initialized to NULL .

3.3.2 Declaring Variables

A variable declaration always specifies the name and data type of the variable.

For most data types, a variable declaration can also specify an initial value.

The variable name must be a valid user-defined identifier .

The data type can be any PL/SQL data type. The PL/SQL data types include the SQL data types. A data type is either scalar (without internal components) or composite (with internal components).

Example 3-11 Scalar Variable Declarations

This example declares several variables with scalar data types.

Related Topics

" User-Defined Identifiers "

" Scalar Variable Declaration " for scalar variable declaration syntax

PL/SQL Data Types for information about scalar data types

PL/SQL Collections and Records , for information about composite data types and variables

3.3.3 Declaring Constants

A constant holds a value that does not change.

The information in " Declaring Variables " also applies to constant declarations, but a constant declaration has two more requirements: the keyword CONSTANT and the initial value of the constant. (The initial value of a constant is its permanent value.)

Example 3-12 Constant Declarations

This example declares three constants with scalar data types.

Related Topic

" Constant Declaration " for constant declaration syntax

3.3.4 Initial Values of Variables and Constants

In a variable declaration, the initial value is optional unless you specify the NOT NULL constraint . In a constant declaration, the initial value is required.

If the declaration is in a block or subprogram, the initial value is assigned to the variable or constant every time control passes to the block or subprogram. If the declaration is in a package specification, the initial value is assigned to the variable or constant for each session (whether the variable or constant is public or private).

To specify the initial value, use either the assignment operator ( := ) or the keyword DEFAULT , followed by an expression. The expression can include previously declared constants and previously initialized variables.

If you do not specify an initial value for a variable, assign a value to it before using it in any other context.

Example 3-13 Variable and Constant Declarations with Initial Values

This example assigns initial values to the constant and variables that it declares. The initial value of area depends on the previously declared constant pi and the previously initialized variable radius .

Example 3-14 Variable Initialized to NULL by Default

In this example, the variable counter has the initial value NULL , by default. The example uses the " IS [NOT] NULL Operator " to show that NULL is different from zero.

" Declaring Associative Array Constants " for information about declaring constant associative arrays

" Declaring Record Constants " for information about declaring constant records

" NOT NULL Constraint "

3.3.5 Declaring Items using the %TYPE Attribute

The %TYPE attribute lets you declare a data item of the same data type as a previously declared variable or column (without knowing what that type is). If the declaration of the referenced item changes, then the declaration of the referencing item changes accordingly.

The syntax of the declaration is:

For the kinds of items that can be referencing and referenced items, see " %TYPE Attribute " .

The referencing item inherits the following from the referenced item:

Data type and size

Constraints (unless the referenced item is a column)

The referencing item does not inherit the initial value of the referenced item. Therefore, if the referencing item specifies or inherits the NOT NULL constraint, you must specify an initial value for it.

The %TYPE attribute is particularly useful when declaring variables to hold database values. The syntax for declaring a variable of the same type as a column is:

" Declaring Items using the %ROWTYPE Attribute " , which lets you declare a record variable that represents either a full or partial row of a database table or view

Example 3-15 Declaring Variable of Same Type as Column

In this example, the variable surname inherits the data type and size of the column employees . last_name , which has a NOT NULL constraint. Because surname does not inherit the NOT NULL constraint, its declaration does not need an initial value.

Example 3-16 Declaring Variable of Same Type as Another Variable

In this example, the variable surname inherits the data type, size, and NOT NULL constraint of the variable name . Because surname does not inherit the initial value of name , its declaration needs an initial value (which cannot exceed 25 characters).

3.4 References to Identifiers

When referencing an identifier, you use a name that is either simple, qualified, remote, or both qualified and remote.

The simple name of an identifier is the name in its declaration. For example:

If an identifier is declared in a named PL/SQL unit, you can (and sometimes must) reference it with its qualified name . The syntax (called dot notation ) is:

For example, if package p declares identifier a , you can reference the identifier with the qualified name p . a . The unit name also can (and sometimes must) be qualified. You must qualify an identifier when it is not visible (see " Scope and Visibility of Identifiers " ).

If the identifier names an object on a remote database, you must reference it with its remote name . The syntax is:

If the identifier is declared in a PL/SQL unit on a remote database, you must reference it with its qualified remote name . The syntax is:

You can create synonyms for remote schema objects, but you cannot create synonyms for objects declared in PL/SQL subprograms or packages. To create a synonym, use the SQL statement CREATE SYNONYM , explained in Oracle Database SQL Language Reference .

For information about how PL/SQL resolves ambiguous names, see PL/SQL Name Resolution .

You can reference identifiers declared in the packages STANDARD and DBMS_STANDARD without qualifying them with the package names, unless you have declared a local identifier with the same name (see " Scope and Visibility of Identifiers " ).

3.5 Scope and Visibility of Identifiers

The scope of an identifier is the region of a PL/SQL unit from which you can reference the identifier. The visibility of an identifier is the region of a PL/SQL unit from which you can reference the identifier without qualifying it. An identifier is local to the PL/SQL unit that declares it. If that unit has subunits, the identifier is global to them.

If a subunit redeclares a global identifier, then inside the subunit, both identifiers are in scope, but only the local identifier is visible. To reference the global identifier, the subunit must qualify it with the name of the unit that declared it. If that unit has no name, then the subunit cannot reference the global identifier.

A PL/SQL unit cannot reference identifiers declared in other units at the same level, because those identifiers are neither local nor global to the block.

You cannot declare the same identifier twice in the same PL/SQL unit. If you do, an error occurs when you reference the duplicate identifier.

You can declare the same identifier in two different units. The two objects represented by the identifier are distinct. Changing one does not affect the other.

In the same scope, give labels and subprograms unique names to avoid confusion and unexpected results.

Example 3-17 Scope and Visibility of Identifiers

This example shows the scope and visibility of several identifiers. The first sub-block redeclares the global identifier a . To reference the global variable a , the first sub-block would have to qualify it with the name of the outer block—but the outer block has no name. Therefore, the first sub-block cannot reference the global variable a ; it can reference only its local variable a . Because the sub-blocks are at the same level, the first sub-block cannot reference d , and the second sub-block cannot reference c .

Example 3-18 Qualifying Redeclared Global Identifier with Block Label

This example labels the outer block with the name outer . Therefore, after the sub-block redeclares the global variable birthdate , it can reference that global variable by qualifying its name with the block label. The sub-block can also reference its local variable birthdate , by its simple name.

Example 3-19 Qualifying Identifier with Subprogram Name

In this example, the procedure check_credit declares a variable, rating , and a function, check_rating . The function redeclares the variable. Then the function references the global variable by qualifying it with the procedure name.

Example 3-20 Duplicate Identifiers in Same Scope

You cannot declare the same identifier twice in the same PL/SQL unit. If you do, an error occurs when you reference the duplicate identifier, as this example shows.

Example 3-21 Declaring Same Identifier in Different Units

You can declare the same identifier in two different units. The two objects represented by the identifier are distinct. Changing one does not affect the other, as this example shows. In the same scope, give labels and subprograms unique names to avoid confusion and unexpected results.

Example 3-22 Label and Subprogram with Same Name in Same Scope

In this example, echo is the name of both a block and a subprogram. Both the block and the subprogram declare a variable named x . In the subprogram, echo . x refers to the local variable x , not to the global variable x .

Example 3-23 Block with Multiple and Duplicate Labels

This example has two labels for the outer block, compute_ratio and another_label . The second label appears again in the inner block. In the inner block, another_label . denominator refers to the local variable denominator , not to the global variable denominator , which results in the error ZERO_DIVIDE .

3.6 Assigning Values to Variables

After declaring a variable, you can assign a value to it in these ways:

Use the assignment statement to assign it the value of an expression.

Use the SELECT INTO or FETCH statement to assign it a value from a table.

Pass it to a subprogram as an OUT or IN OUT parameter, and then assign the value inside the subprogram.

The variable and the value must have compatible data types. One data type is compatible with another data type if it can be implicitly converted to that type. For information about implicit data conversion, see Oracle Database SQL Language Reference .

Assigning Values to Variables with the Assignment Statement

Assigning Values to Variables with the SELECT INTO Statement

Assigning Values to Variables as Parameters of a Subprogram

Assigning Values to BOOLEAN Variables

" Assigning Values to Collection Variables "

" Assigning Values to Record Variables "

" FETCH Statement "

3.6.1 Assigning Values to Variables with the Assignment Statement

To assign the value of an expression to a variable, use this form of the assignment statement:

For the complete syntax of the assignment statement, see " Assignment Statement " .

For the syntax of an expression, see " Expression " .

Example 3-24 Assigning Values to Variables with Assignment Statement

This example declares several variables (specifying initial values for some) and then uses assignment statements to assign the values of expressions to them.

3.6.2 Assigning Values to Variables with the SELECT INTO Statement

A simple form of the SELECT INTO statement is:

For each select_item , there must be a corresponding, type-compatible variable_name . Because SQL does not have a BOOLEAN type, variable_name cannot be a BOOLEAN variable.

For the complete syntax of the SELECT INTO statement, see " SELECT INTO Statement " .

Example 3-25 Assigning Value to Variable with SELECT INTO Statement

This example uses a SELECT INTO statement to assign to the variable bonus the value that is 10% of the salary of the employee whose employee_id is 100.

3.6.3 Assigning Values to Variables as Parameters of a Subprogram

If you pass a variable to a subprogram as an OUT or IN OUT parameter, and the subprogram assigns a value to the parameter, the variable retains that value after the subprogram finishes running. For more information, see " Subprogram Parameters " .

Example 3-26 Assigning Value to Variable as IN OUT Subprogram Parameter

This example passes the variable new_sal to the procedure adjust_salary . The procedure assigns a value to the corresponding formal parameter, sal . Because sal is an IN OUT parameter, the variable new_sal retains the assigned value after the procedure finishes running.

3.6.4 Assigning Values to BOOLEAN Variables

The only values that you can assign to a BOOLEAN variable are TRUE , FALSE , and NULL .

For more information about the BOOLEAN data type, see " BOOLEAN Data Type " .

Example 3-27 Assigning Value to BOOLEAN Variable

This example initializes the BOOLEAN variable done to NULL by default, assigns it the literal value FALSE , compares it to the literal value TRUE , and assigns it the value of a BOOLEAN expression.

3.7 Expressions

An expression is a combination of one or more values, operators, and SQL functions that evaluates to a value.

An expression always returns a single value. The simplest expressions, in order of increasing complexity, are:

A single constant or variable (for example, a )

A unary operator and its single operand (for example, -a )

A binary operator and its two operands (for example, a+b )

An operand can be a variable, constant, literal, operator, function invocation, or placeholder—or another expression. Therefore, expressions can be arbitrarily complex. For expression syntax, see Expression .

The data types of the operands determine the data type of the expression. Every time the expression is evaluated, a single value of that data type results. The data type of that result is the data type of the expression.

Concatenation Operator

Operator Precedence

Logical Operators

Short-Circuit Evaluation

Comparison Operators

BOOLEAN Expressions

CASE Expressions

SQL Functions in PL/SQL Expressions

3.7.1 Concatenation Operator

The concatenation operator ( || ) appends one string operand to another.

The concatenation operator ignores null operands.

For more information about the syntax of the concatenation operator, see " character_expression ::= " .

Example 3-28 Concatenation Operator

Example 3-29 Concatenation Operator with NULL Operands

The concatenation operator ignores null operands, as this example shows.

3.7.2 Operator Precedence

An operation is either a unary operator and its single operand or a binary operator and its two operands. The operations in an expression are evaluated in order of operator precedence.

Table 3-3 shows operator precedence from highest to lowest. Operators with equal precedence are evaluated in no particular order.

Table 3-3 Operator Precedence

To control the order of evaluation, enclose operations in parentheses, as in Example 3-30 .

When parentheses are nested, the most deeply nested operations are evaluated first.

You can also use parentheses to improve readability where the parentheses do not affect evaluation order.

Example 3-30 Controlling Evaluation Order with Parentheses

Example 3-31 Expression with Nested Parentheses

In this example, the operations (1+2) and (3+4) are evaluated first, producing the values 3 and 7, respectively. Next, the operation 3*7 is evaluated, producing the result 21. Finally, the operation 21/7 is evaluated, producing the final value 3.

Example 3-32 Improving Readability with Parentheses

In this example, the parentheses do not affect the evaluation order. They only improve readability.

Example 3-33 Operator Precedence

This example shows the effect of operator precedence and parentheses in several more complex expressions.

3.7.3 Logical Operators

The logical operators AND , OR , and NOT follow a tri-state logic.

AND and OR are binary operators; NOT is a unary operator.

Table 3-4 Logical Truth Table

AND returns TRUE if and only if both operands are TRUE .

OR returns TRUE if either operand is TRUE .

NOT returns the opposite of its operand, unless the operand is NULL . NOT NULL returns NULL , because NULL is an indeterminate value.

Example 3-34 Procedure Prints BOOLEAN Variable

This example creates a procedure, print_boolean , that prints the value of a BOOLEAN variable. The procedure uses the " IS [NOT] NULL Operator " . Several examples in this chapter invoke print_boolean .

Example 3-35 AND Operator

As Table 3-4 and this example show, AND returns TRUE if and only if both operands are TRUE .

Example 3-36 OR Operator

As Table 3-4 and this example show, OR returns TRUE if either operand is TRUE . (This example invokes the print_boolean procedure from Example 3-34 .)

Example 3-37 NOT Operator

As Table 3-4 and this example show, NOT returns the opposite of its operand, unless the operand is NULL . NOT NULL returns NULL , because NULL is an indeterminate value. (This example invokes the print_boolean procedure from Example 3-34 .)

Example 3-38 NULL Value in Unequal Comparison

In this example, you might expect the sequence of statements to run because x and y seem unequal. But, NULL values are indeterminate. Whether x equals y is unknown. Therefore, the IF condition yields NULL and the sequence of statements is bypassed.

Example 3-39 NULL Value in Equal Comparison

In this example, you might expect the sequence of statements to run because a and b seem equal. But, again, that is unknown, so the IF condition yields NULL and the sequence of statements is bypassed.

Example 3-40 NOT NULL Equals NULL

In this example, the two IF statements appear to be equivalent. However, if either x or y is NULL , then the first IF statement assigns the value of y to high and the second IF statement assigns the value of x to high .

Example 3-41 Changing Evaluation Order of Logical Operators

This example invokes the print_boolean procedure from Example 3-34 three times. The third and first invocation are logically equivalent—the parentheses in the third invocation only improve readability. The parentheses in the second invocation change the order of operation.

3.7.4 Short-Circuit Evaluation

When evaluating a logical expression, PL/SQL uses short-circuit evaluation . That is, PL/SQL stops evaluating the expression as soon as it can determine the result.

Therefore, you can write expressions that might otherwise cause errors.

In Example 3-42 , short-circuit evaluation prevents the OR expression from causing a divide-by-zero error. When the value of on_hand is zero, the value of the left operand is TRUE , so PL/SQL does not evaluate the right operand. If PL/SQL evaluated both operands before applying the OR operator, the right operand would cause a division by zero error.

Example 3-42 Short-Circuit Evaluation

3.7.5 Comparison Operators

Comparison operators compare one expression to another. The result is always either TRUE , FALSE , or NULL .

If the value of one expression is NULL , then the result of the comparison is also NULL .

The comparison operators are:

IS [NOT] NULL Operator

Relational Operators

LIKE Operator

BETWEEN Operator

IN Operator

Character comparisons are affected by NLS parameter settings, which can change at runtime. Therefore, character comparisons are evaluated at runtime, and the same character comparison can have different values at different times. For information about NLS parameters that affect character comparisons, see Oracle Database Globalization Support Guide .

Using CLOB values with comparison operators can create temporary LOB values. Ensure that your temporary tablespace is large enough to handle them.

3.7.5.1 IS [NOT] NULL Operator

The IS NULL operator returns the BOOLEAN value TRUE if its operand is NULL or FALSE if it is not NULL . The IS NOT NULL operator does the opposite.

Comparisons involving NULL values always yield NULL .

To test whether a value is NULL , use IF value IS NULL , as in these examples:

Example 3-14 , "Variable Initialized to NULL by Default"

Example 3-34 , "Procedure Prints BOOLEAN Variable"

Example 3-53 , "Searched CASE Expression with WHEN ... IS NULL"

3.7.5.2 Relational Operators

This table summarizes the relational operators.

Table 3-5 Relational Operators

Arithmetic Comparisons

BOOLEAN Comparisons

Character Comparisons

Date Comparisons

3.7.5.2.1 Arithmetic Comparisons

One number is greater than another if it represents a larger quantity.

Real numbers are stored as approximate values, so Oracle recommends comparing them for equality or inequality.

Example 3-43 Relational Operators in Expressions

This example invokes the print_boolean procedure from Example 3-35 to print the values of expressions that use relational operators to compare arithmetic values.

3.7.5.2.2 BOOLEAN Comparisons

By definition, TRUE is greater than FALSE . Any comparison with NULL returns NULL .

3.7.5.2.3 Character Comparisons

By default, one character is greater than another if its binary value is larger.

For example, this expression is true:

Strings are compared character by character. For example, this expression is true:

If you set the initialization parameter NLS_COMP=ANSI , string comparisons use the collating sequence identified by the NLS_SORT initialization parameter.

A collating sequence is an internal ordering of the character set in which a range of numeric codes represents the individual characters. One character value is greater than another if its internal numeric value is larger. Each language might have different rules about where such characters occur in the collating sequence. For example, an accented letter might be sorted differently depending on the database character set, even though the binary value is the same in each case.

By changing the value of the NLS_SORT parameter, you can perform comparisons that are case-insensitive and accent-insensitive.

A case-insensitive comparison treats corresponding uppercase and lowercase letters as the same letter. For example, these expressions are true:

To make comparisons case-insensitive, append _CI to the value of the NLS_SORT parameter (for example, BINARY_CI or XGERMAN_CI ).

An accent-insensitive comparison is case-insensitive, and also treats letters that differ only in accents or punctuation characters as the same letter. For example, these expressions are true:

To make comparisons both case-insensitive and accent-insensitive, append _AI to the value of the NLS_SORT parameter (for example, BINARY_AI or FRENCH_M_AI ).

Semantic differences between the CHAR and VARCHAR2 data types affect character comparisons.

For more information, see " Value Comparisons " .

3.7.5.2.4 Date Comparisons

One date is greater than another if it is more recent.

3.7.5.3 LIKE Operator

The LIKE operator compares a character, string, or CLOB value to a pattern and returns TRUE if the value matches the pattern and FALSE if it does not.

Case is significant.

The pattern can include the two wildcard characters underscore ( _ ) and percent sign (%).

Underscore matches exactly one character.

Percent sign ( % ) matches zero or more characters.

To search for the percent sign or underscore, define an escape character and put it before the percent sign or underscore.

Oracle Database SQL Language Reference for more information about LIKE

Oracle Database SQL Language Reference for information about REGEXP_LIKE , which is similar to LIKE

Example 3-44 LIKE Operator in Expression

The string 'Johnson' matches the pattern 'J%s_n' but not 'J%S_N' , as this example shows.

Example 3-45 Escape Character in Pattern

This example uses the backslash as the escape character, so that the percent sign in the string does not act as a wildcard.

3.7.5.4 BETWEEN Operator

The BETWEEN operator tests whether a value lies in a specified range.

The value of the expression x BETWEEN a AND b is defined to be the same as the value of the expression (x>=a) AND (x<=b)  . The expression x will only be evaluated once.

Oracle Database SQL Language Reference for more information about BETWEEN

Example 3-46 BETWEEN Operator in Expressions

This example invokes the print_boolean procedure from Example 3-34 to print the values of expressions that include the BETWEEN operator.

3.7.5.5 IN Operator

The IN operator tests set membership.

x IN ( set ) returns TRUE only if x equals a member of set .

Oracle Database SQL Language Reference for more information about IN

Example 3-47 IN Operator in Expressions

This example invokes the print_boolean procedure from Example 3-34 to print the values of expressions that include the IN operator.

Example 3-48 IN Operator with Sets with NULL Values

This example shows what happens when set includes a NULL value. This invokes the print_boolean procedure from Example 3-34 .

3.7.6 BOOLEAN Expressions

A BOOLEAN expression is an expression that returns a BOOLEAN value— TRUE , FALSE , or NULL .

The simplest BOOLEAN expression is a BOOLEAN literal, constant, or variable. The following are also BOOLEAN expressions:

For a list of relational operators, see Table 3-5 . For the complete syntax of a BOOLEAN expression, see " boolean_expression ::= " .

Typically, you use BOOLEAN expressions as conditions in control statements (explained in PL/SQL Control Statements ) and in WHERE clauses of DML statements.

You can use a BOOLEAN variable itself as a condition; you need not compare it to the value TRUE or FALSE .

Example 3-49 Equivalent BOOLEAN Expressions

In this example, the conditions in the loops are equivalent.

3.7.7 CASE Expressions

Simple CASE Expression

Searched CASE Expression

3.7.7.1 Simple CASE Expression

For this explanation, assume that a simple CASE expression has this syntax:

The selector is an expression (typically a single variable). Each selector_value and each result can be either a literal or an expression. At least one result must not be the literal NULL .

The simple CASE expression returns the first result for which selector_value matches selector . Remaining expressions are not evaluated. If no selector_value matches selector , the CASE expression returns else_result if it exists and NULL otherwise.

" simple_case_expression ::= " for the complete syntax

Example 3-50 Simple CASE Expression

This example assigns the value of a simple CASE expression to the variable appraisal . The selector is grade .

Example 3-51 Simple CASE Expression with WHEN NULL

If selector has the value NULL , it cannot be matched by WHEN NULL , as this example shows.

Instead, use a searched CASE expression with WHEN boolean_expression IS NULL , as in Example 3-53 .

3.7.7.2 Searched CASE Expression

For this explanation, assume that a searched CASE expression has this syntax:

The searched CASE expression returns the first result for which boolean_expression is TRUE . Remaining expressions are not evaluated. If no boolean_expression is TRUE , the CASE expression returns else_result if it exists and NULL otherwise.

" searched_case_expression ::= " for the complete syntax

Example 3-52 Searched CASE Expression

This example assigns the value of a searched CASE expression to the variable appraisal .

Example 3-53 Searched CASE Expression with WHEN ... IS NULL

This example uses a searched CASE expression to solve the problem in Example 3-51 .

3.7.8 SQL Functions in PL/SQL Expressions

In PL/SQL expressions, you can use all SQL functions except:

Aggregate functions (such as AVG and COUNT )

Aggregate function JSON_ARRAYAGG

Aggregate function JSON_DATAGUIDE

Aggregate function JSON_MERGEPATCH

Aggregate function JSON_OBJECTAGG

JSON_TRANSFORM

JSON condition JSON_TEXTCONTAINS

Analytic functions (such as LAG and RATIO_TO_REPORT )

Conversion function BIN_TO_NUM

Data mining functions (such as CLUSTER_ID and FEATURE_VALUE )

Encoding and decoding functions (such as DECODE and DUMP )

Model functions (such as ITERATION_NUMBER and PREVIOUS )

Object reference functions (such as REF and VALUE )

XML functions

These collation SQL operators and functions:

COLLATE operator

COLLATION function

NLS_COLLATION_ID function

NLS_COLLATION_NAME function

These miscellaneous functions:

DATAOBJ_TO_PARTITION

SYS_CONNECT_BY_PATH

WIDTH_BUCKET

PL/SQL supports an overload of BITAND for which the arguments and result are BINARY_INTEGER .

When used in a PL/SQL expression, the RAWTOHEX function accepts an argument of data type RAW and returns a VARCHAR2 value with the hexadecimal representation of bytes that comprise the value of the argument. Arguments of types other than RAW can be specified only if they can be implicitly converted to RAW . This conversion is possible for CHAR , VARCHAR2 , and LONG values that are valid arguments of the HEXTORAW function, and for LONG RAW and BLOB values of up to 16380 bytes.

3.7.9 Static Expressions

A static expression is an expression whose value can be determined at compile time—that is, it does not include character comparisons, variables, or function invocations. Static expressions are the only expressions that can appear in conditional compilation directives.

Definition of Static Expression

An expression is static if it is the NULL literal.

An expression is static if it is a character, numeric, or boolean literal.

An expression is static if it is a reference to a static constant.

An expression is static if it is a reference to a conditional compilation variable begun with $$ .

An expression is static if it is an operator is allowed in static expressions, if all of its operands are static, and if the operator does not raise an exception when it is evaluated on those operands.

Table 3-6 Operators Allowed in Static Expressions

This list shows functions allowed in static expressions.

IS [NOT] INFINITE

IS [NOT] NAN

TO_BINARY_DOUBLE

TO_BINARY_FLOAT

Static expressions can be used in the following subtype declarations:

Length of string types ( VARCHAR2, NCHAR, CHAR, NVARCHAR2, RAW , and the ANSI equivalents)

Scale and precision of NUMBER types and subtypes such as FLOAT

Interval type precision (year, month ,second)

Time and Timestamp precision

VARRAY bounds

Bounds of ranges in type declarations

In each case, the resulting type of the static expression must be the same as the declared item subtype and must be in the correct range for the context.

PLS_INTEGER Static Expressions

BOOLEAN Static Expressions

VARCHAR2 Static Expressions

Static Constants

" Expressions " for general information about expressions

3.7.9.1 PLS_INTEGER Static Expressions

PLS_INTEGER static expressions are:

PLS_INTEGER literals

For information about literals, see " Literals " .

PLS_INTEGER static constants

For information about static constants, see " Static Constants " .

" PLS_INTEGER and BINARY_INTEGER Data Types " for information about the PLS_INTEGER data type

3.7.9.2 BOOLEAN Static Expressions

BOOLEAN static expressions are:

BOOLEAN literals ( TRUE , FALSE , or NULL )

BOOLEAN static constants

Where x and y are PLS_INTEGER static expressions:

x <> y

For information about PLS_INTEGER static expressions, see " PLS_INTEGER Static Expressions " .

Where x and y are BOOLEAN expressions:

For information about BOOLEAN expressions, see " BOOLEAN Expressions " .

Where x is a static expression:

x IS NOT NULL

For information about static expressions, see " Static Expressions " .

" BOOLEAN Data Type " for information about the BOOLEAN data type

3.7.9.3 VARCHAR2 Static Expressions

VARCHAR2 static expressions are:

String literal with maximum size of 32,767 bytes

TO_CHAR(x) , where x is a PLS_INTEGER static expression

For information about the TO_CHAR function, see Oracle Database SQL Language Reference .

TO_CHAR(x , f , n) where x is a PLS_INTEGER static expression and f and n are VARCHAR2 static expressions

x || y where x and y are VARCHAR2 or PLS_INTEGER static expressions

" CHAR and VARCHAR2 Variables " for information about the VARCHAR2 data type

3.7.9.4 Static Constants

A static constant is declared in a package specification with this syntax:

The type of static_expression must be the same as data_type (either BOOLEAN or PLS_INTEGER ).

The static constant must always be referenced as package_name . constant_name , even in the body of the package_name package.

If you use constant_name in the BOOLEAN expression in a conditional compilation directive in a PL/SQL unit, then the PL/SQL unit depends on the package package_name . If you alter the package specification, the dependent PL/SQL unit might become invalid and need recompilation (for information about the invalidation of dependent objects, see Oracle Database Development Guide ).

If you use a package with static constants to control conditional compilation in multiple PL/SQL units, Oracle recommends that you create only the package specification, and dedicate it exclusively to controlling conditional compilation. This practice minimizes invalidations caused by altering the package specification.

To control conditional compilation in a single PL/SQL unit, you can set flags in the PLSQL_CCFLAGS compilation parameter. For information about this parameter, see " Assigning Values to Inquiry Directives " and Oracle Database Reference .

" Declaring Constants " for general information about declaring constants

PL/SQL Packages for more information about packages

Oracle Database Development Guide for more information about schema object dependencies

Example 3-54 Static Constants

In this example, the package my_debug defines the static constants debug and trace to control debugging and tracing in multiple PL/SQL units. The procedure my_proc1 uses only debug , and the procedure my_proc2 uses only trace , but both procedures depend on the package. However, the recompiled code might not be different. For example, if you only change the value of debug to FALSE and then recompile the two procedures, the compiled code for my_proc1 changes, but the compiled code for my_proc2 does not.

3.8 Error-Reporting Functions

PL/SQL has two error-reporting functions, SQLCODE and SQLERRM , for use in PL/SQL exception-handling code.

For their descriptions, see " SQLCODE Function " and " SQLERRM Function " .

You cannot use the SQLCODE and SQLERRM functions in SQL statements.

3.9 Conditional Compilation

Conditional compilation lets you customize the functionality of a PL/SQL application without removing source text.

For example, you can:

Use new features with the latest database release and disable them when running the application in an older database release.

Activate debugging or tracing statements in the development environment and hide them when running the application at a production site.

How Conditional Compilation Works

Conditional Compilation Examples

Retrieving and Printing Post-Processed Source Text

Conditional Compilation Directive Restrictions

3.9.1 How Conditional Compilation Works

Conditional compilation uses selection directives, which are similar to IF statements, to select source text for compilation.

The condition in a selection directive usually includes an inquiry directive. Error directives raise user-defined errors. All conditional compilation directives are built from preprocessor control tokens and PL/SQL text.

Preprocessor Control Tokens

Selection Directives

Error Directives

Inquiry Directives

DBMS_DB_VERSION Package

3.9.1.1 Preprocessor Control Tokens

A preprocessor control token identifies code that is processed before the PL/SQL unit is compiled.

There cannot be space between $ and plsql_identifier .

The character $ can also appear inside plsql_identifier , but it has no special meaning there.

These preprocessor control tokens are reserved:

For information about plsql_identifier , see " Identifiers " .

3.9.1.2 Selection Directives

A selection directive selects source text to compile.

For the syntax of boolean_static_expression , see " BOOLEAN Static Expressions " . The text can be anything, but typically, it is either a statement (see " statement ::= " ) or an error directive (explained in " Error Directives " ).

The selection directive evaluates the BOOLEAN static expressions in the order that they appear until either one expression has the value TRUE or the list of expressions is exhausted. If one expression has the value TRUE , its text is compiled, the remaining expressions are not evaluated, and their text is not analyzed. If no expression has the value TRUE , then if $ELSE is present, its text is compiled; otherwise, no text is compiled.

For examples of selection directives, see " Conditional Compilation Examples " .

" Conditional Selection Statements " for information about the IF statement, which has the same logic as the selection directive

3.9.1.3 Error Directives

An error directive produces a user-defined error message during compilation.

It produces this compile-time error message, where string is the value of varchar2_static_expression :

For the syntax of varchar2_static_expression , see " VARCHAR2 Static Expressions " .

For an example of an error directive, see Example 3-58 .

3.9.1.4 Inquiry Directives

An inquiry directive provides information about the compilation environment.

For information about name , which is an unquoted PL/SQL identifier, see " Identifiers " .

An inquiry directive typically appears in the boolean_static_expression of a selection directive, but it can appear anywhere that a variable or literal of its type can appear. Moreover, it can appear where regular PL/SQL allows only a literal (not a variable)—for example, to specify the size of a VARCHAR2 variable.

Predefined Inquiry Directives

Assigning Values to Inquiry Directives

Unresolvable Inquiry Directives

3.9.1.4.1 Predefined Inquiry Directives

The predefined inquiry directives are:

$$PLSQL_LINE

A PLS_INTEGER literal whose value is the number of the source line on which the directive appears in the current PL/SQL unit. An example of $$PLSQL_LINE in a selection directive is:

$$PLSQL_UNIT

A VARCHAR2 literal that contains the name of the current PL/SQL unit. If the current PL/SQL unit is an anonymous block, then $$PLSQL_UNIT contains a NULL value.

$$PLSQL_UNIT_OWNER

A VARCHAR2 literal that contains the name of the owner of the current PL/SQL unit. If the current PL/SQL unit is an anonymous block, then $$PLSQL_UNIT_OWNER contains a NULL value.

$$PLSQL_UNIT_TYPE

A VARCHAR2 literal that contains the type of the current PL/SQL unit— ANONYMOUS BLOCK , FUNCTION , PACKAGE , PACKAGE BODY , PROCEDURE , TRIGGER , TYPE , or TYPE BODY . Inside an anonymous block or non-DML trigger, $$PLSQL_UNIT_TYPE has the value ANONYMOUS BLOCK .

$$ plsql_compilation_parameter

The name plsql_compilation_parameter is a PL/SQL compilation parameter (for example, PLSCOPE_SETTINGS ). For descriptions of these parameters, see Table 2-2 .

Because a selection directive needs a BOOLEAN static expression, you cannot use $$PLSQL_UNIT , $$PLSQL_UNIT_OWNER , or $$PLSQL_UNIT_TYPE in a VARCHAR2 comparison such as:

However, you can compare the preceding directives to NULL . For example:

Example 3-55 Predefined Inquiry Directives

In this example, a SQL*Plus script, uses several predefined inquiry directives as PLS_INTEGER and VARCHAR2 literals to show how their values are assigned.

Example 3-56 Displaying Values of PL/SQL Compilation Parameters

This example displays the current values of PL/SQL the compilation parameters.

In the SQL*Plus environment, you can display the current values of initialization parameters, including the PL/SQL compilation parameters, with the command SHOW PARAMETERS . For more information about the SHOW command and its PARAMETERS option, see SQL*Plus User's Guide and Reference .

3.9.1.4.2 Assigning Values to Inquiry Directives

You can assign values to inquiry directives with the PLSQL_CCFLAGS compilation parameter.

For example:

Each value must be either a BOOLEAN literal ( TRUE , FALSE , or NULL ) or PLS_INTEGER literal. The data type of value determines the data type of name .

The same name can appear multiple times, with values of the same or different data types. Later assignments override earlier assignments. For example, this command sets the value of $$flag to 5 and its data type to PLS_INTEGER :

Oracle recommends against using PLSQL_CCFLAGS to assign values to predefined inquiry directives, including compilation parameters. To assign values to compilation parameters, Oracle recommends using the ALTER SESSION statement.

For more information about the ALTER SESSION statement, see Oracle Database SQL Language Reference .

The compile-time value of PLSQL_CCFLAGS is stored with the metadata of stored PL/SQL units, which means that you can reuse the value when you explicitly recompile the units. For more information, see " PL/SQL Units and Compilation Parameters " .

For more information about PLSQL_CCFLAGS , see Oracle Database Reference .

Example 3-57 PLSQL_CCFLAGS Assigns Value to Itself

This example uses PLSQL_CCFLAGS to assign a value to the user-defined inquiry directive $$Some_Flag and (though not recommended) to itself. Because later assignments override earlier assignments, the resulting value of $$Some_Flag is 2 and the resulting value of PLSQL_CCFLAGS is the value that it assigns to itself (99), not the value that the ALTER SESSION statement assigns to it ( 'Some_Flag:1, Some_Flag:2, PLSQL_CCFlags:99' ).

3.9.1.4.3 Unresolvable Inquiry Directives

If the source text is not wrapped, PL/SQL issues a warning if the value of an inquiry directive cannot be determined.

If an inquiry directive ( $$ name ) cannot be resolved, and the source text is not wrapped, then PL/SQL issues the warning PLW-6003 and substitutes NULL for the value of the unresolved inquiry directive. If the source text is wrapped, the warning message is disabled, so that the unresolved inquiry directive is not revealed.

For information about wrapping PL/SQL source text, see PL/SQL Source Text Wrapping .

3.9.1.5 DBMS_DB_VERSION Package

The DBMS_DB_VERSION package specifies the Oracle version numbers and other information useful for simple conditional compilation selections based on Oracle versions.

The DBMS_DB_VERSION package provides these static constants:

The PLS_INTEGER constant VERSION identifies the current Oracle Database version.

The PLS_INTEGER constant RELEASE identifies the current Oracle Database release number.

Each BOOLEAN constant of the form VER_LE_ v has the value TRUE if the database version is less than or equal to v ; otherwise, it has the value FALSE .

Each BOOLEAN constant of the form VER_LE_ v_r has the value TRUE if the database version is less than or equal to v and release is less than or equal to r ; otherwise, it has the value FALSE .

For more information about the DBMS_DB_VERSION package, see Oracle Database PL/SQL Packages and Types Reference .

3.9.2 Conditional Compilation Examples

Examples of conditional compilation using selection and user-defined inquiry directives.

Example 3-58 Code for Checking Database Version

This example generates an error message if the database version and release is less than Oracle Database 10g release 2; otherwise, it displays a message saying that the version and release are supported and uses a COMMIT statement that became available at Oracle Database 10g release 2.

Example 3-59 Compiling Different Code for Different Database Versions

This example sets the values of the user-defined inquiry directives $$my_debug and $$my_tracing and then uses conditional compilation:

In the specification of package my_pkg , to determine the base type of the subtype my_real ( BINARY_DOUBLE is available only for Oracle Database versions 10g and later.)

In the body of package my_pkg , to compute the values of my_pi and my_e differently for different database versions

In the procedure circle_area , to compile some code only if the inquiry directive $$my_debug has the value TRUE .

3.9.3 Retrieving and Printing Post-Processed Source Text

The DBMS_PREPROCESSOR package provides subprograms that retrieve and print the source text of a PL/SQL unit in its post-processed form.

For information about the DBMS_PREPROCESSOR package, see Oracle Database PL/SQL Packages and Types Reference .

Example 3-60 Displaying Post-Processed Source Textsource text

This example invokes the procedure DBMS_PREPROCESSOR . PRINT_POST_PROCESSED_SOURCE to print the post-processed form of my_pkg (from " Example 3-59 " ). Lines of code in " Example 3-59 " that are not included in the post-processed text appear as blank lines.

3.9.4 Conditional Compilation Directive Restrictions

Conditional compilation directives are subject to these semantic restrictions.

A conditional compilation directive cannot appear in the specification of a schema-level user-defined type (created with the " CREATE TYPE Statement " ). This type specification specifies the attribute structure of the type, which determines the attribute structure of dependent types and the column structure of dependent tables.

Using a conditional compilation directive to change the attribute structure of a type can cause dependent objects to "go out of sync" or dependent tables to become inaccessible. Oracle recommends that you change the attribute structure of a type only with the " ALTER TYPE Statement " . The ALTER TYPE statement propagates changes to dependent objects.

If a conditional compilation directive is used in a schema-level type specification, the compiler raises the error PLS-00180: preprocessor directives are not supported in this context.

As all conditional compiler constructs are processed by the PL/SQL preprocessor, the SQL Parser imposes the following restrictions on the location of the first conditional compilation directive in a stored PL/SQL unit or anonymous block:

In a package specification, a package body, a type body, a schema-level function and in a schema-level procedure, at least one nonwhitespace PL/SQL token must appear after the identifier of the unit name before a conditional compilation directive is valid.

The PL/SQL comments, "--" or "/*", are counted as whitespace tokens.

If the token is invalid in PL/SQL, then a PLS-00103 error is issued. But if a conditional compilation directive is used in violation of this rule, then an ORA error is produced.

Example 3-61 and Example 3-62 , show that the first conditional compilation directive appears after the first PL/SQL token that follows the identifier of the unit being defined.

In a trigger or an anonymous block, the first conditional compilation directive cannot appear before the keyword DECLARE or BEGIN , whichever comes first.

The SQL parser also imposes this restriction: If an anonymous block uses a placeholder, the placeholder cannot appear in a conditional compilation directive. For example:

Example 3-61 Using Conditional Compilation Directive in the Definition of a Package Specification

This example shows the placement of the first conditional compilation directive after an AUTHID clause, but before the keyword IS , in the definition of the package specification.

Example 3-62 Using Conditional Compilation Directive in the Formal Parameter List of a Subprogram

This example shows the placement of the first conditional compilation directive after the left parenthesis, in the formal parameter list of a PL/SQL procedure definition.

Home » PL/SQL Tutorial » PL/SQL IF Statement

PL/SQL IF Statement

Summary : in this tutorial, you will learn how to use the PL/SQL IF statement to either execute or skip a sequence of statements based on a specified condition.

The IF statement allows you to either execute or skip a sequence of statements, depending on a condition. The IF statement has three forms:

IF THEN IF THEN ELSE IF THEN ELSIF

PL/SQL IF THEN statement

The following illustrates the structure of the IF THEN statement:

The condition is a Boolean expression that always evaluates to TRUE, FALSE, or NULL.

If the condition evaluates to TRUE, the statements after the THEN execute. Otherwise, the IF statement does nothing.

PL/SQL IF THEN statement example

In the following example, the statements between THEN and END IF execute because the sales revenue is greater than 100,000.

Tip # 1: Avoid clumsy IF statement

Consider the following example:

In this example, the IF statement determines whether the sales revenue is higher than the cost and updates the b_profitable variable accordingly.

This IF statement called a clumsy IF statement because you can assign the result of a Boolean expression directly to a Boolean variable as follows:

Tip #2: Avoid evaluating Boolean variables

A Boolean variable is always TRUE, FALSE, or NULL. Therefore the following comparison is unnecessary:

Instead, use:

PL/SQL IF THEN ELSE statement

The IF THEN ELSE statement has the following structure:

If the condition evaluates to TRUE, then the statements between THEN and ELSE execute. In case the condition evaluates to FALSE or NULL, the else_statements between ELSE and END IF executes.

IF THEN ELSE statement example

The following example sets the sales commission to 10% if the sales revenue is greater than 200,000. Otherwise, the sales commission is set to 5%.

PL/SQL IF THEN ELSIF statement

The following illustrates the structure of the IF THEN ELSIF statement:

In this structure, the condition between IF and THEN , which is the first condition, is always evaluated. Each other condition between ELSEIF and THEN is evaluated only if the preceding condition is FALSE. For example, the condition_2 is evaluated only if the condition_1 is false, the condition_3 is evaluated only if the condition_2 is false, and so on.

If a condition is true, other subsequent conditions are not evaluated. If no condition is true, the else_statements between the ELSE and ENDIF execute. In case you skip the ELSE clause and no condition is TRUE, then the IF THEN ELSIF does nothing

IF THEN ELSIF statement example

The following example uses the IF THEN ELSIF statement to set the sales commission based on the sales revenue.

Nested IF statement

You can nest an IF statement within another IF statement as shown below:

However, if you have too many levels of nesting, the code will be hard to read and maintain, so you should avoid nesting the IF statements.

In this tutorial, you have learned how to use the PL/SQL IF statement to either execute or skip a sequence of statements depending on a specified condition.

Search code, repositories, users, issues, pull requests...

Provide feedback.

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly.

To see all available qualifiers, see our documentation .

• Notifications

Merey1508/PLSQL-assignments

Folders and files, repository files navigation, pl-sql-assignments, assignment 1: introduction to pl/sql programming language.

• Download SQL Developer
• Create a database connection
• Test the new connection

Assignment 2: Declaring PL/SQL Variables

• PL/SQL variables:

– Reference

– Large object (LOB)

– Composite

• Non-PL/SQL variables: Bind variables

Assignment 3: Writing Executable Statements

• Lexical Units in a PL/SQL Block
• Data Type Conversion
• Nested Blocks

Assignment 4: Interacting with the Oracle Server

• SELECT Statements in PL/SQL
• Inserting Data
• Updating Data
• Deleting Data
• Merging Rows
• Implicit/Explicit cursors

Assignment 5: Writing Control Structures

• IF Statement, NULL Value in IF Statement
• IF THEN ELSE Statement
• IF ELSIF ELSE Clause
• CASE Expressions
• LOOP Statements: Basic Loop, FOR loop, WHILE loop
• Nested loops
• CONTINUE Statement

Assignment 6: Working with Composite Data Types

• PL/SQL Records
• PL/SQL Collections
• PL/SQL Records Attributes
• Associative Arrays (INDEX BY Tables)

Assignment 7: Using Explicit Cursors

• Declaring the Cursor
• Opening the Cursor
• Fetching Data from the Cursor
• Closing the Cursor
• Cursor FOR Loops
• Explicit Cursor Attributes
• Cursors with Parameters

Assignment 8: Handling Exceptions

• Predefined Exceptions
• Standard Oracle Server Exceptions

Assignment statement (PL/SQL)

The assignment statement sets a previously-declared variable or formal OUT or IN OUT parameter to the value of an expression.

Description