Return 1 In Pl/Sql What Is The Assignment Operator

Assignment Statement

An assignment statement sets the current value of a variable, field, parameter, or element. The statement consists of an assignment target followed by the assignment operator and an expression. When the statement is executed, the expression is evaluated and the resulting value is stored in the target. For more information, see "Assigning Values to Variables".


assignment statement ::=

Description of the illustration assignment_statement.gif

Keyword and Parameter Description


An attribute of an object type. The name must be unique within the object type (but can be reused in other object types). You cannot initialize an attribute in its declaration using the assignment operator or clause. Also, you cannot impose the constraint on an attribute.


A nested table, index-by table, or varray previously declared within the current scope.


A PL/SQL cursor variable previously declared within the current scope. Only the value of another cursor variable can be assigned to a cursor variable.


A combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. For the syntax of , see "Expression Definition". When the assignment statement is executed, the expression is evaluated and the resulting value is stored in the assignment target. The value and target must have compatible datatypes.


A field in a user-defined or record.


A cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. The datatype of the host cursor variable is compatible with the return type of any PL/SQL cursor variable. Host variables must be prefixed with a colon.


A variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. Host variables must be prefixed with a colon.


A numeric expression that must return a value of type , , or a value implicitly convertible to that datatype.


An indicator variable declared in a PL/SQL host environment and passed to PL/SQL. Indicator variables must be prefixed with a colon. An indicator variable indicates the value or condition of its associated host variable. For example, in the Oracle Precompiler environment, indicator variables let you detect nulls or truncated values in output host variables.


An instance of an object type previously declared within the current scope.


A formal or parameter of the subprogram in which the assignment statement appears.


A user-defined or record previously declared within the current scope.


A PL/SQL variable previously declared within the current scope.

Usage Notes

By default, unless a variable is initialized in its declaration, it is initialized to every time a block or subprogram is entered. Always assign a value to a variable before using that variable in an expression.

You cannot assign nulls to a variable defined as . If you try, PL/SQL raises the predefined exception . Only the values , , and can be assigned to a Boolean variable. You can assign the result of a comparison or other test to a Boolean variable.

You can assign the value of an expression to a specific field in a record. You can assign values to all fields in a record at once. PL/SQL allows aggregate assignment between entire records if their declarations refer to the same cursor or table. Example 1-2, "Assigning Values to Variables With the Assignment Operator" shows how to copy values from all the fields of one record to another:

You can assign the value of an expression to a specific element in a collection, by subscripting the collection name.


Example 13-1 illustrates various ways to declare and then assign values to variables.

Example 13-1 Declaring and Assigning Values to Variables

DECLARE wages NUMBER; hours_worked NUMBER := 40; hourly_salary CONSTANT NUMBER := 17.50; -- constant value does not change country VARCHAR2(64) := 'UNKNOWN'; unknown BOOLEAN; TYPE comm_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER; commissions comm_tab; TYPE jobs_var IS VARRAY(10) OF employees.job_id%TYPE; jobids jobs_var; CURSOR c1 IS SELECT department_id FROM departments; -- cursor declaration deptid departments.department_id%TYPE; emp_rec employees%ROWTYPE; -- do not need TYPE declaration in this case BEGIN /* the following are examples of assignment statements */ wages := hours_worked * hourly_salary; -- compute wages country := UPPER('italy'); unknown := (country = 'UNKNOWN'); commissions(5) := 20000 * 0.15; commissions(8) := 20000 * 0.18; jobids := jobs_var('ST_CLERK'); jobids.EXTEND(1); jobids(2) := 'SH_CLERK'; OPEN c1; FETCH c1 INTO deptid; CLOSE c1; emp_rec.department_id := deptid; emp_rec.job_id := jobids(2); END; /

For examples, see the following:

Example 1-2, "Assigning Values to Variables With the Assignment Operator"
Example 1-3, "Assigning Values to Variables by SELECTing INTO"
Example 1-4, "Assigning Values to Variables as Parameters of a Subprogram"
Example 2-10, "Assigning Values to a Record With a %ROWTYPE Declaration"

Related Topics

"Assigning Values to Variables"
"Constant and Variable Declaration"
"Expression Definition"
"SELECT INTO Statement"

2Fundamentals of the PL/SQL Language

The previous chapter provided an overview of PL/SQL. This chapter focuses on the detailed aspects of the language. Like other programming languages, PL/SQL has a character set, reserved words, punctuation, datatypes, and fixed syntax rules.

This chapter contains these topics:

Character Sets and Lexical Units

PL/SQL programs are written as lines of text using a specific set of characters:

  • Upper- and lower-case letters .. and ..

  • Numerals ..

  • Symbols

  • Tabs, spaces, and carriage returns

PL/SQL keywords are not case-sensitive, so lower-case letters are equivalent to corresponding upper-case letters except within string and character literals.

A line of PL/SQL text contains groups of characters known as lexical units:

  • Delimiters (simple and compound symbols)

  • Identifiers, which include reserved words

  • Literals

  • Comments

To improve readability, you can separate lexical units by spaces. In fact, you must separate adjacent identifiers by a space or punctuation. The following line is not allowed because the reserved words and are joined:

-- not allowed, must be

You cannot embed spaces inside lexical units except for string literals and comments. For example, the following line is not allowed because the compound symbol for assignment () is split:

-- not allowed, must be

To show structure, you can split lines using carriage returns, and indent lines using spaces or tabs. This formatting makes the first statement more readable.

The following is easier to read:


A delimiter is a simple or compound symbol that has a special meaning to PL/SQL. For example, you use delimiters to represent arithmetic operations such as addition and subtraction. Table 2-1 contains a list of PL/SQL delimiters.

Table 2-1 PL/SQL Delimiters


addition operator

attribute indicator
character string delimiter
component selector

division operator
expression or list delimiter
expression or list delimiter
host variable indicator
item separator

multiplication operator
quoted identifier delimiter

relational operator

relational operator
relational operator

remote access indicator
statement terminator
subtraction/negation operator
assignment operator
association operator

concatenation operator

exponentiation operator

label delimiter (begin)
label delimiter (end)

multi-line comment delimiter (begin)

multi-line comment delimiter (end)
range operator
relational operator
relational operator
relational operator
relational operator

relational operator
relational operator
single-line comment indicator


You use identifiers to name PL/SQL program items and units, which include constants, variables, exceptions, cursors, cursor variables, subprograms, and packages. Some examples of identifiers follow:

An identifier consists of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs. Other characters such as hyphens, slashes, and spaces are not allowed, as the following examples show:

is not allowed because of the ampersand
is not allowed because of the hyphen
is not allowed because of the slash
is not allowed because of the space

Adjoining and trailing dollar signs, underscores, and number signs are allowed:

You can use upper, lower, or mixed case to write identifiers. PL/SQL is not case sensitive except within string and character literals. If the only difference between identifiers is the case of corresponding letters, PL/SQL considers them the same:

-- same as lastname
-- same as lastname and LastName

The size of an identifier cannot exceed 30 characters. Every character, including dollar signs, underscores, and number signs, is significant. For example, PL/SQL considers the following identifiers to be different:

Identifiers should be descriptive. Avoid obscure names such as . Instead, use meaningful names such as .

Reserved Words

Some identifiers, called reserved words, have a special syntactic meaning to PL/SQL. For example, the words and are reserved. Often, reserved words are written in upper case for readability.

Trying to redefine a reserved word causes a compilation error. Instead, you can embed reserved words as part of a longer identifier. For example:

DECLARE -- end BOOLEAN; the use of "end" is not allowed; causes compilation error end_of_game BOOLEAN; -- allowed

In addition to reserved words, there are keywords that have special meaning in PL/SQL. PL/SQL keywords can be used for identifiers, but this is not recommended. For a list of PL/SQL reserved words and keywords, see Table D-1, "PL/SQL Reserved Words" and Table D-2, "PL/SQL Keywords".

Predefined Identifiers

Identifiers globally declared in package , such as the exception , can be redeclared. However, redeclaring predefined identifiers is error prone because your local declaration overrides the global declaration.

Quoted Identifiers

For flexibility, PL/SQL lets you enclose identifiers within double quotes. Quoted identifiers are seldom needed, but occasionally they can be useful. They can contain any sequence of printable characters including spaces but excluding double quotes. Thus, the following identifiers are valid:

The maximum size of a quoted identifier is 30 characters not counting the double quotes. Though allowed, using PL/SQL reserved words as quoted identifiers is a poor programming practice.


A literal is an explicit numeric, character, string, or value not represented by an identifier. The numeric literal and the literal are examples. For information on the PL/SQL datatypes, see "Overview of Predefined PL/SQL Datatypes".

Numeric Literals

Two kinds of numeric literals can be used in arithmetic expressions: integers and reals. An integer literal is an optionally signed whole number without a decimal point. Some examples follow:

A real literal is an optionally signed whole or fractional number with a decimal point. Several examples follow:

PL/SQL considers numbers such as and to be reals even though they have integral values.

A numeric literal value that is composed only of digits and falls in the range -2147483648 to 2147483647 has a datatype; otherwise this literal has the datatype. You can add the of suffix to a literal value that is composed only of digits to specify the or respectively. For the properties of the datatypes, see "PL/SQL Number Types".

Numeric literals cannot contain dollar signs or commas, but can be written using scientific notation. Simply suffix the number with an (or ) followed by an optionally signed integer. A few examples follow:

stands for times ten to the power of. As the next example shows, the number after is the power of ten by which the number before is multiplied (the double asterisk () is the exponentiation operator):

The number after also corresponds to the number of places the decimal point shifts. In the last example, the implicit decimal point shifted three places to the right. In this example, it shifts three places to the left:

The absolute value of a literal can be in the range up to (but not including) . The literal can also be . See Example 2-1. For information on results outside the valid range, see "NUMBER Datatype".

Example 2-1 NUMBER Literals

DECLARE n NUMBER; -- declare n of NUMBER datatype BEGIN n := -9.999999E-130; -- valid n := 9.999E125; -- valid -- n := 10.0E125; -- invalid, "numeric overflow or underflow" END; /

Real literals can also use the trailing letters and to specify the types and , as shown in Example 2-2.


DECLARE x BINARY_FLOAT := sqrt(2.0f); -- single-precision floating-point number y BINARY_DOUBLE := sqrt(2.0d); -- double-precision floating-point number BEGIN NULL; END; /

Character Literals

A character literal is an individual character enclosed by single quotes (apostrophes). Character literals include all the printable characters in the PL/SQL character set: letters, numerals, spaces, and special symbols. Some examples follow:

PL/SQL is case sensitive within character literals. For example, PL/SQL considers the literals and to be different. Also, the character literals .. are not equivalent to integer literals but can be used in arithmetic expressions because they are implicitly convertible to integers.

String Literals

A character value can be represented by an identifier or explicitly written as a string literal, which is a sequence of zero or more characters enclosed by single quotes. All string literals except the null string ('') have datatype .

The following are examples of string literals:

PL/SQL is case sensitive within string literals. For example, PL/SQL considers the following literals to be different:

To represent an apostrophe within a string, you can write two single quotes, which is not the same as writing a double quote:

Doubling the quotation marks within a complicated literal, particularly one that represents a SQL statement, can be tricky. You can also use the following notation to define your own delimiter characters for the literal. You choose a character that is not present in the string, and then do not need to escape other single quotation marks inside the literal:

You can use delimiters [, {, <, and (, pair them with ], }, >, and ), pass a string literal representing a SQL statement to a subprogram, without doubling the quotation marks around 'INVALID' as follows:

For and literals, use the prefix instead of :

For more information about the datatype and unicode strings, see Oracle Database Globalization Support Guide.

BOOLEAN Literals

literals are the predefined values , , and . stands for a missing, unknown, or inapplicable value. Remember, literals are values, not strings. For example, is no less a value than the number .

Datetime Literals

Datetime literals have various formats depending on the datatype. For example:

Example 2-3 Using DateTime Literals

DECLARE d1 DATE := DATE '1998-12-25'; t1 TIMESTAMP := TIMESTAMP '1997-10-22 13:01:01'; t2 TIMESTAMP WITH TIME ZONE := TIMESTAMP '1997-01-31 09:26:56.66 +02:00'; -- Three years and two months -- For greater precision, we would use the day-to-second interval i1 INTERVAL YEAR TO MONTH := INTERVAL '3-2' YEAR TO MONTH; -- Five days, four hours, three minutes, two and 1/100 seconds i2 INTERVAL DAY TO SECOND := INTERVAL '5 04:03:02.01' DAY TO SECOND;

You can also specify whether a given interval value is or . For example, produces a value of type by default. You can specify the type of the interval using the formats:

For details on the syntax for the date and time types, see the Oracle Database SQL Reference. For examples of performing date and time arithmetic, see Oracle Database Application Developer's Guide - Fundamentals.


The PL/SQL compiler ignores comments, but you should not. Adding comments to your program promotes readability and aids understanding. Generally, you use comments to describe the purpose and use of each code segment. PL/SQL supports two comment styles: single-line and multi-line.

Single-Line Comments

Single-line comments begin with a double hyphen () anywhere on a line and extend to the end of the line. A few examples follow:

Example 2-4 Using Single-Line Comments

DECLARE howmany NUMBER; num_tables NUMBER; BEGIN -- begin processing SELECT COUNT(*) INTO howmany FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE'; -- Check number of tables num_tables := howmany; -- Compute some other value END; /

Notice that comments can appear within a statement at the end of a line.

While testing or debugging a program, you might want to disable a line of code. The following example shows how you can disable a line by making it a comment:

Multi-line Comments

Multi-line comments begin with a slash-asterisk (), end with an asterisk-slash (), and can span multiple lines, as shown in Example 2-5. You can use multi-line comment delimiters to comment-out whole sections of code.

Example 2-5 Using Multi-Line Comments

DECLARE some_condition BOOLEAN; pi NUMBER := 3.1415926; radius NUMBER := 15; area NUMBER; BEGIN /* Perform some simple tests and assignments */ IF 2 + 2 = 4 THEN some_condition := TRUE; /* We expect this THEN to always be performed */ END IF; /* The following line computes the area of a circle using pi, which is the ratio between the circumference and diameter. After the area is computed, the result is displayed. */ area := pi * radius**2; DBMS_OUTPUT.PUT_LINE('The area is: ' || TO_CHAR(area)); END; /

Restrictions on Comments

You cannot nest comments. You cannot use single-line comments in a PL/SQL block that will be processed by an Oracle Precompiler program because end-of-line characters are ignored. As a result, single-line comments extend to the end of the block, not just to the end of a line. In this case, use the notation instead.


Your program stores values in variables and constants. As the program executes, the values of variables can change, but the values of constants cannot.

You can declare variables and constants in the declarative part of any PL/SQL block, subprogram, or package. Declarations allocate storage space for a value, specify its datatype, and name the storage location so that you can reference it.

Some examples follow:

DECLARE birthday DATE; emp_count SMALLINT := 0;

The first declaration names a variable of type . The second declaration names a variable of type and uses the assignment operator to assign an initial value of zero to the variable.

The next examples show that the expression following the assignment operator can be arbitrarily complex and can refer to previously initialized variables:

DECLARE pi REAL := 3.14159; radius REAL := 1; area REAL := pi * radius**2;

By default, variables are initialized to , so it is redundant to include " in a variable declaration.


To declare a constant, put the keyword before the type specifier. The following declaration names a constant of type and assigns an unchangeable value of 5000 to the constant. A constant must be initialized in its declaration. Otherwise, a compilation error occurs.

DECLARE credit_limit CONSTANT REAL := 5000.00; max_days_in_year CONSTANT INTEGER := 366; urban_legend CONSTANT BOOLEAN := FALSE;


You can use the keyword instead of the assignment operator to initialize variables. For example, the declaration

can be rewritten as follows:

Use for variables that have a typical value. Use the assignment operator for variables (such as counters and accumulators) that have no typical value. For example:

You can also use to initialize subprogram parameters, cursor parameters, and fields in a user-defined record.


Besides assigning an initial value, declarations can impose the constraint:

You cannot assign nulls to a variable defined as . If you try, PL/SQL raises the predefined exception .

The constraint must be followed by an initialization clause.

PL/SQL provide subtypes and that are predefined as . You can omit the constraint when declaring variables of these types, and you must include an initialization clause.

Using the %TYPE Attribute

The attribute provides the datatype of a variable or database column. As shown in Example 2-6, variables declared with inherit the datatype of a variable, plus default values and constraints.

Example 2-6 Using %TYPE With the Datatype of a Variable

DECLARE credit PLS_INTEGER RANGE 1000..25000; debit credit%TYPE; v_name VARCHAR2(20); name VARCHAR2(20) NOT NULL := 'JoHn SmItH'; -- If we increase the length of NAME, the other variables become longer also upper_name name%TYPE := UPPER(name); lower_name name%TYPE := LOWER(name); init_name name%TYPE := INITCAP(name); BEGIN -- display inherited default values DBMS_OUTPUT.PUT_LINE('name: ' || name || ' upper_name: ' || upper_name || ' lower_name: ' || lower_name || ' init_name: ' || init_name); -- lower_name := 'jonathan henry smithson'; invalid, character string is too long -- lower_name := NULL; invalid, NOT NULL CONSTRAINT -- debit := 50000; invalid, value out of range END; /

Note that variables declared using are treated like those declared using a datatype specifier. For example, given the previous declarations, PL/SQL treats like a variable. A declaration can also include an initialization clause.

The attribute is particularly useful when declaring variables that refer to database columns. You can reference a table and column, or you can reference an owner, table, and column, as in:

DECLARE -- If the length of the column ever changes, this code -- will use the new length automatically. the_trigger user_triggers.trigger_name%TYPE;

When you use to declare a variable, you do not need to know the actual datatype, and attributes such as precision, scale, and length. If the database definition of the column changes, the datatype of the variable changes accordingly at run time. However, variables do not inherit column constraints, such as the or check constraint, or default values. For example, even though the database column is defined as in Example 2-7, you can assign a to the variable .

Example 2-7 Using %TYPE With Table Columns

CREATE TABLE employees_temp (empid NUMBER(6) NOT NULL PRIMARY KEY, deptid NUMBER(6) CONSTRAINT check_deptid CHECK (deptid BETWEEN 100 AND 200), deptname VARCHAR2(30) DEFAULT 'Sales'); DECLARE v_empid employees_temp.empid%TYPE; v_deptid employees_temp.deptid%TYPE; v_deptname employees_temp.deptname%TYPE; BEGIN v_empid := NULL; -- this works, null constraint is not inherited -- v_empid := 10000002; -- invalid, number precision too large v_deptid := 50; -- this works, check constraint is not inherited -- the default value is not inherited in the following DBMS_OUTPUT.PUT_LINE('v_deptname: ' || v_deptname); END; /

See "Constraints and Default Values With Subtypes" for information on column constraints that are inherited by subtypes declared using .

Using the %ROWTYPE Attribute

The attribute provides a record type that represents a row in a table or view. Columns in a row and corresponding fields in a record have the same names and datatypes. However, fields in a record do not inherit constraints, such as the or check constraint, or default values, as shown in Example 2-8. See also Example 3-11.

Example 2-8 Using %ROWTYPE With Table Rows

DECLARE emprec employees_temp%ROWTYPE; BEGIN emprec.empid := NULL; -- this works, null constraint is not inherited -- emprec.empid := 10000002; -- invalid, number precision too large emprec.deptid := 50; -- this works, check constraint is not inherited -- the default value is not inherited in the following DBMS_OUTPUT.PUT_LINE('emprec.deptname: ' || emprec.deptname); END; /

The record can store an entire row of data selected from the table, or fetched from a cursor or strongly typed cursor variable as shown in Example 2-9.

Example 2-9 Using the %ROWTYPE Attribute

DECLARE -- %ROWTYPE can include all the columns in a table... emp_rec employees%ROWTYPE; -- ...or a subset of the columns, based on a cursor. CURSOR c1 IS SELECT department_id, department_name FROM departments; dept_rec c1%ROWTYPE; -- Could even make a %ROWTYPE with columns from multiple tables. CURSOR c2 IS SELECT employee_id, email, employees.manager_id, location_id FROM employees, departments WHERE employees.department_id = departments.department_id; join_rec c2%ROWTYPE; BEGIN -- We know EMP_REC can hold a row from the EMPLOYEES table. SELECT * INTO emp_rec FROM employees WHERE ROWNUM < 2; -- We can refer to the fields of EMP_REC using column names -- from the EMPLOYEES table. IF emp_rec.department_id = 20 AND emp_rec.last_name = 'JOHNSON' THEN emp_rec.salary := emp_rec.salary * 1.15; END IF; END; /

Aggregate Assignment

Although a declaration cannot include an initialization clause, there are ways to assign values to all fields in a record at once. You can assign one record to another if their declarations refer to the same table or cursor. Example 2-10 shows record assignments that are allowed.

Example 2-10 Assigning Values to a Record With a %ROWTYPE Declaration

DECLARE dept_rec1 departments%ROWTYPE; dept_rec2 departments%ROWTYPE; CURSOR c1 IS SELECT department_id, location_id FROM departments; dept_rec3 c1%ROWTYPE; BEGIN dept_rec1 := dept_rec2; -- allowed -- dept_rec2 refers to a table, dept_rec3 refers to a cursor -- dept_rec2 := dept_rec3; -- not allowed END; /

You can assign a list of column values to a record by using the or statement, as the following example shows. The column names must appear in the order in which they were defined by the or statement.

DECLARE dept_rec departments%ROWTYPE; BEGIN SELECT * INTO dept_rec FROM departments WHERE department_id = 30 and ROWNUM < 2; END; /

However, there is no constructor for a record type, so you cannot assign a list of column values to a record by using an assignment statement.

Using Aliases

Select-list items fetched from a cursor associated with must have simple names or, if they are expressions, must have aliases. Example 2-11 uses an alias called to represent the concatenation of two columns:

Example 2-11 Using an Alias for Column Names

BEGIN -- We assign an alias (complete_name) to the expression value, because -- it has no column name. FOR item IN ( SELECT first_name || ' ' || last_name complete_name FROM employees WHERE ROWNUM < 11 ) LOOP -- Now we can refer to the field in the record using this alias. DBMS_OUTPUT.PUT_LINE('Employee name: ' || item.complete_name); END LOOP; END; /

Restrictions on Declarations

PL/SQL does not allow forward references. You must declare a variable or constant before referencing it in other statements, including other declarative statements.

PL/SQL does allow the forward declaration of subprograms. For more information, see "Declaring Nested PL/SQL Subprograms".

Some languages allow you to declare a list of variables that have the same datatype. PL/SQL does not allow this. You must declare each variable separately:

DECLARE -- Multiple declarations not allowed. -- i, j, k, l SMALLINT; -- Instead, declare each separately. i SMALLINT; j SMALLINT; -- To save space, you can declare more than one on a line. k SMALLINT; l SMALLINT;

PL/SQL Naming Conventions

The same naming conventions apply to all PL/SQL program items and units including constants, variables, cursors, cursor variables, exceptions, procedures, functions, and packages. Names can be simple, qualified, remote, or both qualified and remote. For example, you might use the procedure name in any of the following ways:

-- simple
-- qualified
-- remote
-- qualified and remote

In the first case, you simply use the procedure name. In the second case, you must qualify the name using dot notation because the procedure is stored in a package called . In the third case, using the remote access indicator (), you reference the database link because the procedure is stored in a remote database. In the fourth case, you qualify the procedure name and reference a database link.


You can create synonyms to provide location transparency for remote schema objects such as tables, sequences, views, standalone subprograms, packages, and object types. However, you cannot create synonyms for items declared within subprograms or packages. That includes constants, variables, cursors, cursor variables, exceptions, and packaged subprograms.


Within the same scope, all declared identifiers must be unique; even if their datatypes differ, variables and parameters cannot share the same name. In Example 2-12, the second declaration is not allowed.

Example 2-12 Errors With Duplicate Identifiers in Same Scope

DECLARE valid_id BOOLEAN; valid_id VARCHAR2(5); -- not allowed, duplicate identifier BEGIN -- The error occurs when the identifier is referenced, -- not in the declaration part. valid_id := FALSE; -- raises an error here END; /

For the scoping rules that apply to identifiers, see "Scope and Visibility of PL/SQL Identifiers".

Case Sensitivity

Like all identifiers, the names of constants, variables, and parameters are not case sensitive. For instance, PL/SQL considers the following names to be the same:

Example 2-13 Case Sensitivity of Identifiers

DECLARE zip_code INTEGER; Zip_Code INTEGER; -- duplicate identifier, despite Z/z case difference BEGIN zip_code := 90120; -- raises error here because of duplicate identifiers END; /

Name Resolution

In potentially ambiguous SQL statements, the names of database columns take precedence over the names of local variables and formal parameters. For example, if a variable and a column with the same name are both used in a clause, SQL considers that both cases refer to the column.

To avoid ambiguity, add a prefix to the names of local variables and formal parameters, or use a block label to qualify references as shown in Example 2-14.

Example 2-14 Using a Block Label for Name Resolution

CREATE TABLE employees2 AS SELECT last_name FROM employees; <<main>> DECLARE last_name VARCHAR2(10) := 'King'; v_last_name VARCHAR2(10) := 'King'; BEGIN -- deletes everyone, because both LAST_NAMEs refer to the column DELETE FROM employees2 WHERE last_name = last_name; DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows.'); ROLLBACK; -- OK, column and variable have different names DELETE FROM employees2 WHERE last_name = v_last_name; DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows.'); ROLLBACK; -- OK, block name specifies that 2nd last_name is a variable DELETE FROM employees2 WHERE last_name = main.last_name; DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows.'); ROLLBACK; END; /

Example 2-15 shows that you can use a subprogram name to qualify references to local variables and formal parameters.

Example 2-15 Using a Subprogram Name for Name Resolution

DECLARE FUNCTION dept_name (department_id IN NUMBER) RETURN departments.department_name%TYPE IS department_name departments.department_name%TYPE; BEGIN -- DEPT_NAME.department_name specifies the local variable -- instead of the table column SELECT department_name INTO dept_name.department_name FROM departments WHERE department_id = dept_name.department_id; RETURN department_name; END; BEGIN FOR item IN (SELECT department_id FROM departments) LOOP DBMS_OUTPUT.PUT_LINE('Department: ' || dept_name(item.department_id)); END LOOP; END; /

For a full discussion of name resolution, see Appendix B, "How PL/SQL Resolves Identifier Names".

Scope and Visibility of PL/SQL Identifiers

References to an identifier are resolved according to its scope and visibility. The scope of an identifier is that region of a program unit (block, subprogram, or package) from which you can reference the identifier. An identifier is visible only in the regions from which you can reference the identifier using an unqualified name. Figure 2-1 shows the scope and visibility of a variable named , which is declared in an enclosing block, then redeclared in a sub-block.

Identifiers declared in a PL/SQL block are considered local to that block and global to all its sub-blocks. If a global identifier is redeclared in a sub-block, both identifiers remain in scope. Within the sub-block, however, only the local identifier is visible because you must use a qualified name to reference the global identifier.

Although you cannot declare an identifier twice in the same block, you can declare the same identifier in two different blocks. The two items represented by the identifier are distinct, and any change in one does not affect the other. However, a block cannot reference identifiers declared in other blocks at the same level because those identifiers are neither local nor global to the block.

Example 2-16 illustrates the scope rules. Notice that the identifiers declared in one sub-block cannot be referenced in the other sub-block. That is because a block cannot reference identifiers declared in other blocks nested at the same level.

Example 2-16 Scope Rules

DECLARE a CHAR; b REAL; BEGIN -- identifiers available here: a (CHAR), b DECLARE a INTEGER; c REAL; BEGIN NULL; -- identifiers available here: a (INTEGER), b, c END; DECLARE d REAL; BEGIN NULL; -- identifiers available here: a (CHAR), b, d END; -- identifiers available here: a (CHAR), b END; /

Recall that global identifiers can be redeclared in a sub-block, in which case the local declaration prevails and the sub-block cannot reference the global identifier unless you use a qualified name. The qualifier can be the label of an enclosing block as shown in Example 2-17.

Example 2-17 Using a Label Qualifier With Identifiers

<<outer>> DECLARE birthdate DATE := '09-AUG-70'; BEGIN DECLARE birthdate DATE; BEGIN birthdate := '29-SEP-70'; IF birthdate = outer.birthdate THEN DBMS_OUTPUT.PUT_LINE ('Same Birthday'); ELSE DBMS_OUTPUT.PUT_LINE ('Different Birthday'); END IF; END; END; /

As Example 2-18 shows, the qualifier can also be the name of an enclosing subprogram:

Example 2-18 Using Subprogram Qualifier With Identifiers

CREATE OR REPLACE PROCEDURE check_credit(limit NUMBER) AS rating NUMBER := 3; FUNCTION check_rating RETURN BOOLEAN IS rating NUMBER := 1; over_limit BOOLEAN; BEGIN IF check_credit.rating <= limit THEN over_limit := FALSE; ELSE rating := limit; over_limit := TRUE; END IF; RETURN over_limit; END check_rating; BEGIN IF check_rating THEN DBMS_OUTPUT.PUT_LINE( 'Credit rating over limit (' || TO_CHAR(limit) || ').' || ' Rating: ' || TO_CHAR(rating)); ELSE DBMS_OUTPUT.PUT_LINE( 'Credit rating OK. ' || 'Rating: ' || TO_CHAR(rating) ); END IF; END; / CALL check_credit(1);

However, within the same scope, a label and a subprogram cannot have the same name. The use of duplicate labels, illustrated in Example 2-19, should be avoided.

Example 2-19 PL/SQL Block Using Multiple and Duplicate Labels

<<compute_ratio>> <<another_label>> DECLARE numerator NUMBER := 22; denominator NUMBER := 7; the_ratio NUMBER; BEGIN <<inner_label>> <<another_label>> DECLARE denominator NUMBER := 0; BEGIN -- first use the denominator value = 7 from global DECLARE -- to compute a rough value of pi the_ratio := numerator/compute_ratio.denominator; DBMS_OUTPUT.PUT_LINE('Ratio = ' || the_ratio); -- now use the local denominator value = 0 to raise an exception -- inner_label is not needed but used for clarification the_ratio := numerator/inner_label.denominator; DBMS_OUTPUT.PUT_LINE('Ratio = ' || the_ratio); -- if you use a duplicate label, you might get errors -- or unpredictable results the_ratio := numerator/another_label.denominator; DBMS_OUTPUT.PUT_LINE('Ratio = ' || the_ratio); EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('Divide-by-zero error: can''t divide ' || numerator || ' by ' || denominator); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Unexpected error.'); END inner_label; END compute_ratio; /

Assigning Values to Variables

You can use assignment statements to assign values to variables. For example, the following statement assigns a new value to the variable , overwriting its old value:

Variables and constants are initialized every time a block or subprogram is entered. By default, variables are initialized to . Unless you expressly initialize a variable, its value is undefined () as shown in Example 2-20.

Example 2-20 Initialization of Variables and Constants

DECLARE counter INTEGER; BEGIN -- COUNTER is initially NULL, so 'COUNTER + 1' is also null. counter := counter + 1; IF counter IS NULL THEN DBMS_OUTPUT.PUT_LINE('COUNTER is NULL not 1.'); END IF; END; /

To avoid unexpected results, never reference a variable before you assign it a value. The expression following the assignment operator can be arbitrarily complex, but it must yield a datatype that is the same as or convertible to the datatype of the variable.

Assigning BOOLEAN Values

Only the values , , and can be assigned to a variable as shown in Example 2-21. You can assign these literal values, or expressions such as comparisons using relational operators.

Example 2-21 Assigning BOOLEAN Values

DECLARE done BOOLEAN; -- DONE is initially NULL counter NUMBER := 0; BEGIN done := FALSE; -- Assign a literal value WHILE done != TRUE -- Compare to a literal value LOOP counter := counter + 1; done := (counter > 500); -- If counter > 500, DONE = TRUE END LOOP; END; /

Assigning a SQL Query Result to a PL/SQL Variable

You can use the statement to have Oracle assign values to a variable. For each item in the select list, there must be a corresponding, type-compatible variable in the list as shown in Example 2-22.

Example 2-22 Assigning a Query Result to a Variable

DECLARE emp_id employees.employee_id%TYPE := 100; emp_name employees.last_name%TYPE; wages NUMBER(7,2); BEGIN SELECT last_name, salary + (salary * nvl(commission_pct,0)) INTO emp_name, wages FROM employees WHERE employee_id = emp_id; DBMS_OUTPUT.PUT_LINE('Employee ' || emp_name || ' might make ' || wages); END; /

Because SQL does not have a type, you cannot select column values into a variable. For additional information on assigning variables with the DML statements, including situations when the value of a variable is undefined, see "Data Manipulation".

PL/SQL Expressions and Comparisons

Expressions are constructed using operands and operators. An operand is a variable, constant, literal, or function call that contributes a value to an expression. An example of a simple arithmetic expression follows:

Unary operators such as the negation operator () operate on one operand; binary operators such as the division operator () operate on two operands. PL/SQL has no ternary operators.

The simplest expressions consist of a single variable, which yields a value directly. PL/SQL evaluates an expression by combining the values of the operands in ways specified by the operators. An expression always returns a single value. PL/SQL determines the datatype of this value by examining the expression and the context in which it appears.

Operator Precedence

The operations within an expression are done in a particular order depending on their precedence (priority). Table 2-2 shows the default order of operations from first to last (top to bottom).

Table 2-2 Order of Operations


, identity, negation
, multiplication, division
, , addition, subtraction, concatenation
, , , , , , , , , , , , comparison
logical negation

Operators with higher precedence are applied first. In the following example, both expressions yield 8 because division has a higher precedence than addition. Operators with the same precedence are applied in no particular order.

You can use parentheses to control the order of evaluation. For example, the following expression yields 7, not 11, because parentheses override the default operator precedence:

In the next example, the subtraction is done before the division because the most deeply nested subexpression is always evaluated first:

The following example shows that you can always use parentheses to improve readability, even when they are not needed:

Logical Operators

The logical operators , , and follow the tri-state logic shown in Table 2-3. and are binary operators; is a unary operator.

Table 2-3 Logic Truth Table

xyx AND yx OR yNOT x

As the truth table shows, returns only if both its operands are true. On the other hand, returns if either of its operands is true. returns the opposite value (logical negation) of its operand. For example, returns .

returns , because nulls are indeterminate. Be careful to avoid unexpected results in expressions involving nulls; see "Handling Null Values in Comparisons and Conditional Statements".

Order of Evaluation

When you do not use parentheses to specify the order of evaluation, operator precedence determines the order. Compare the following expressions:

If the variables and have the value , the first expression yields . However, the second expression yields because has a higher precedence than . Therefore, the second expression is equivalent to:

In the following example, notice that when has the value , the whole expression yields regardless of the value of :

Likewise, in the next example, when has the value , the whole expression yields regardless of the value of :

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 the result can be determined. This lets you write expressions that might otherwise cause an error. Consider the expression in Example 2-23.

Example 2-23 Short-Circuit Evaluation

DECLARE on_hand INTEGER := 0; on_order INTEGER := 100; BEGIN -- Does not cause divide-by-zero error; evaluation stops after first expression IF (on_hand = 0) OR ((on_order / on_hand) < 5) THEN DBMS_OUTPUT.PUT_LINE('On hand quantity is zero.'); END IF; END; /

When the value of is zero, the left operand yields , so PL/SQL does not evaluate the right operand. If PL/SQL evaluated both operands before applying the operator, the right operand would cause a division by zero error.

Short-circuit evaluation applies to statements, statements, and expressions in PL/SQL.

Comparison Operators

Comparison operators compare one expression to another. The result is always true, false, or null. Typically, you use comparison operators in conditional control statements and in the clause of SQL data manipulation statements. Example 2-24 provides some examples of comparisons for different types.

Example 2-24 Using Comparison Operators

DECLARE PROCEDURE assert(assertion VARCHAR2, truth BOOLEAN) IS BEGIN IF truth IS NULL THEN DBMS_OUTPUT.PUT_LINE('Assertion ' || assertion || ' is unknown (NULL)'); ELSIF truth = TRUE THEN DBMS_OUTPUT.PUT_LINE('Assertion ' || assertion || ' is TRUE'); ELSE DBMS_OUTPUT.PUT_LINE('Assertion ' || assertion || ' is FALSE'); END IF; END; BEGIN assert('2 + 2 = 4', 2 + 2 = 4); assert('10 > 1', 10 > 1); assert('10 <= 1', 10 <= 1); assert('5 BETWEEN 1 AND 10', 5 BETWEEN 1 AND 10); assert('NULL != 0', NULL != 0); assert('3 IN (1,3,5)', 3 IN (1,3,5)); assert('''A'' < ''Z''', 'A' < 'Z'); assert('''baseball'' LIKE ''%all%''', 'baseball' LIKE '%all%'); assert('''suit'' || ''case'' = ''suitcase''', 'suit' || 'case' = 'suitcase'); END; /

Relational Operators

The following table lists the relational operators with their meanings.


equal to
, , , not equal to

less than
greater than

less than or equal to
greater than or equal to

IS NULL Operator

The operator returns the value if its operand is null or if it is not null. Comparisons involving nulls always yield . Test whether a value is null as follows:

LIKE Operator

You use the operator to compare a character, string, or value to a pattern. Case is significant. returns the value if the patterns match or if they do not match.

The patterns matched by can include two special-purpose characters called wildcards. An underscore () matches exactly one character; a percent sign () matches zero or more characters. For example, if the value of is , the following expression is true:

To search for the percent sign and underscore characters, you define an escape character and put that character before the percent sign or underscore. The following example uses the backslash as the escape character, so that the percent sign in the string does not act as a wildcard:

BETWEEN Operator

The operator tests whether a value lies in a specified range. It means "greater than or equal to low value and less than or equal to high value." For example, the following expression is false:

IN Operator

The operator tests set membership. It means "equal to any member of." The set can contain nulls, but they are ignored. For example, the following expression tests whether a value is part of a set of values:

Be careful when inverting this condition. Expressions of the form:

yield if the set contains a null.

Concatenation Operator

Double vertical bars () serve as the concatenation operator, which appends one string (, , , or the equivalent Unicode-enabled type) to another. For example, the expression

returns the following value:

If both operands have datatype , the concatenation operator returns a value. If either operand is a value, the operator returns a temporary CLOB. Otherwise, it returns a value.

BOOLEAN Expressions

PL/SQL lets you compare variables and constants in both SQL and procedural statements. These comparisons, called expressions, consist of simple or complex expressions separated by relational operators. Often, expressions are connected by the logical operators , , and . A expression always yields , , or .

In a SQL statement, expressions let you specify the rows in a table that are affected by the statement. In a procedural statement, expressions are the basis for conditional control. There are three kinds of expressions: arithmetic, character, and date.

BOOLEAN Arithmetic Expressions

You can use the relational operators to compare numbers for equality or inequality. Comparisons are quantitative; that is, one number is greater than another if it represents a larger quantity. For example, given the assignments

the following expression is true:

BOOLEAN Character Expressions

You can compare character values for equality or inequality. By default, comparisons are based on the binary values of each byte in the string. For example, given the assignments

the following expression is true:

By setting the initialization parameter , you can make comparisons use the collating sequence identified by the 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.

Depending on the value of the parameter, you can perform comparisons that are case-insensitive and even accent-insensitive. A case-insensitive comparison still returns true if the letters of the operands are different in terms of uppercase and lowercase. An accent-insensitive comparison is case-insensitive, and also returns true if the operands differ in accents or punctuation characters. For example, the character values and are considered identical by a case-insensitive comparison; the character values , , and are all considered the same. To make comparisons case-insensitive, add to the end of your usual value for the parameter. To make comparisons accent-insensitive, add to the end of the value.

There are semantic differences between the and base types that come into play when you compare character values. For more information, see "Differences between the CHAR and VARCHAR2 Datatypes".

Many types can be converted to character types. For example, you can compare, assign, and do other character operations using variables. For details on the possible conversions, see "PL/SQL Character and String Types".

BOOLEAN Date Expressions

You can also compare dates. Comparisons are chronological; that is, one date is greater than another if it is more recent. For example, given the assignments

the following expression is true:

Guidelines for PL/SQL BOOLEAN Expressions

In general, do not compare real numbers for exact equality or inequality. Real numbers are stored as approximate values. For example, the following condition might not yield :

DECLARE fraction BINARY_FLOAT := 1/3; BEGIN IF fraction = 11/33 THEN DBMS_OUTPUT.PUT_LINE('Fractions are equal (luckily!)'); END IF; END; /

It is a good idea to use parentheses when doing comparisons. For example, the following expression is not allowed because yields a value, which cannot be compared with the number 500:

-- not allowed

The debugged version follows:

A variable is itself either true or false. You can just use the variable in a conditional test, rather than comparing it to the literal values and . In Example 2-25 the loops are all equivalent.

Example 2-25 Using BOOLEAN Variables in Conditional Tests

DECLARE done BOOLEAN ; BEGIN -- Each WHILE loop is equivalent done := FALSE; WHILE done = FALSE LOOP done := TRUE; END LOOP; done := FALSE; WHILE NOT (done = TRUE) LOOP done := TRUE; END LOOP; done := FALSE; WHILE NOT done LOOP done := TRUE; END LOOP; END; /

Using values with comparison operators, or functions such as and , can create temporary LOBs. You might need to make sure your temporary tablespace is large enough to handle these temporary LOBs.

CASE Expressions

There are two types of expressions used in CASE statements: simple and searched. These expressions correspond to the type of CASE statement in which they are used. See "Using CASE Statements".

Simple CASE expression

A simple expression selects a result from one or more alternatives, and returns the result. Although it contains a block that might stretch over several lines, it really is an expression that forms part of a larger statement, such as an assignment or a procedure call. The expression uses a selector, an expression whose value determines which alternative to return.

A expression has the form illustrated in Example 2-26. The selector () is followed by one or more clauses, which are checked sequentially. The value of the selector determines which clause is evaluated. The first clause that matches the value of the selector determines the result value, and subsequent clauses are not evaluated. If there are no matches, then the optional clause is performed.

Example 2-26 Using the WHEN Clause With a CASE Statement

DECLARE grade CHAR(1) := 'B'; appraisal VARCHAR2(20); BEGIN appraisal := CASE grade WHEN 'A' THEN 'Excellent' WHEN 'B' THEN 'Very Good' WHEN 'C' THEN 'Good' WHEN 'D' THEN 'Fair' WHEN 'F' THEN 'Poor' ELSE 'No such grade' END; DBMS_OUTPUT.PUT_LINE('Grade ' || grade || ' is ' || appraisal); END; /

The optional clause works similarly to the clause in an statement. If the value of the selector is not one of the choices covered by a clause, the clause is executed. If no clause is provided and none of the clauses are matched, the expression returns .

Searched CASE Expression

A searched expression lets you test different conditions instead of comparing a single expression to various values. It has the form shown in Example 2-27.

A searched expression has no selector. Each clause contains a search condition that yields a value, so you can test different variables or multiple conditions in a single clause.

Example 2-27 Using a Search Condition With a CASE Statement

DECLARE grade CHAR(1) := 'B'; appraisal VARCHAR2(120); id NUMBER := 8429862; attendance NUMBER := 150; min_days CONSTANT NUMBER := 200; FUNCTION attends_this_school(id NUMBER) RETURN BOOLEAN IS BEGIN RETURN TRUE; END; BEGIN appraisal := CASE WHEN attends_this_school(id) = FALSE THEN 'N/A - Student not enrolled' -- Have to test this condition early to detect good students with bad attendance WHEN grade = 'F' OR attendance < min_days THEN 'Poor (poor performance or bad attendance)' WHEN grade = 'A' THEN 'Excellent' WHEN grade = 'B' THEN 'Very Good' WHEN grade = 'C' THEN 'Good' WHEN grade = 'D' THEN 'Fair' ELSE 'No such grade' END; DBMS_OUTPUT.PUT_LINE('Result for student ' || id || ' is ' || appraisal); END; /

The search conditions are evaluated sequentially. The value of each search condition determines which clause is executed. If a search condition yields , its clause is executed. After any clause is executed, subsequent search conditions are not evaluated. If none of the search conditions yields , the optional clause is executed. If no clause is executed and no clause is supplied, the value of the expression is .

Handling Null Values in Comparisons and Conditional Statements

When working with nulls, you can avoid some common mistakes by keeping in mind the following rules:

  • Comparisons involving nulls always yield

  • Applying the logical operator to a null yields

  • In conditional control statements, if the condition yields , its associated sequence of statements is not executed

  • If the expression in a simple statement or expression yields , it cannot be matched by using . In this case, you would need to use the searched case syntax and test .

In Example 2-28, you might expect the sequence of statements to execute because and seem unequal. But, nulls are indeterminate. Whether or not is equal to is unknown. Therefore, the condition yields and the sequence of statements is bypassed.

Example 2-28 Using NULLs in Comparisons

DECLARE x NUMBER := 5; y NUMBER := NULL; BEGIN IF x != y THEN -- yields NULL, not TRUE DBMS_OUTPUT.PUT_LINE('x != y'); -- not executed ELSIF x = y THEN -- also yields NULL DBMS_OUTPUT.PUT_LINE('x = y'); ELSE DBMS_OUTPUT.PUT_LINE('Can''t tell if x and y are equal or not.'); END IF; END; /

In the following example, you might expect the sequence of statements to execute because and seem equal. But, again, that is unknown, so the condition yields and the sequence of statements is bypassed.

DECLARE a NUMBER := NULL; b NUMBER := NULL; BEGIN IF a = b THEN -- yields NULL, not TRUE DBMS_OUTPUT.PUT_LINE('a = b'); -- not executed ELSIF a != b THEN -- yields NULL, not TRUE DBMS_OUTPUT.PUT_LINE('a != b'); -- not executed ELSE DBMS_OUTPUT.PUT_LINE('Can''t tell if two NULLs are equal'); END IF; END; /

NULLs and the NOT Operator

Recall that applying the logical operator to a null yields . Thus, the following two statements are not always equivalent:

The sequence of statements in the clause is executed when the condition yields or . If neither nor is null, both statements assign the same value to . However, if either or is null, the first statement assigns the value of to , but the second statement assigns the value of to .

NULLs and Zero-Length Strings

PL/SQL treats any zero-length string like a null. This includes values returned by character functions and expressions. For example, the following statements assign nulls to the target variables:

DECLARE null_string VARCHAR2(80) := TO_CHAR(''); address VARCHAR2(80); zip_code VARCHAR2(80) := SUBSTR(address, 25, 0); name VARCHAR2(80); valid BOOLEAN := (name != '');

Use the operator to test for null strings, as follows:

NULLs and the Concatenation Operator

The concatenation operator ignores null operands. For example, the expression

returns the following value:

NULLs as Arguments to Built-In Functions

If a null argument is passed to a built-in function, a null is returned except in the following cases.

The function

One thought on “Return 1 In Pl/Sql What Is The Assignment Operator

Leave a Reply

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