Skip to content

Pragmas In Pl/Sql What Is The Assignment Operator

9/89

2PL/SQL Language Fundamentals

This chapter explains the following aspects of the PL/SQL language:

Character Sets and Lexical Units

PL/SQL supports two character sets: the database character set, which is used for identifiers and source code, and the national character set, which is used for national language data. This topic applies only to the database character set. For information about the national character set, see NCHAR and NVARCHAR2 Data Types.

PL/SQL programs are written as lines of text using the following 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

You must separate adjacent identifiers by a space or punctuation. For example:

SQL> BEGIN 2 IF x > y THEN high := x; END IF; -- correct 3 IF x > y THEN high := x; ENDIF; -- incorrect 4 END; 5 / END;*ERROR at line 4:ORA-06550: line 4, column 4:PLS-00103: Encountered the symbol ";" when expecting one of the following:if SQL>

You cannot embed spaces inside lexical units (except string literals and comments). For example:

SQL> BEGIN 2 count := count + 1; -- correct 3 count : = count + 1; -- incorrect 4 END; 5 / count : = count + 1; -- incorrect*ERROR at line 3:ORA-06550: line 3, column 9:PLS-00103: Encountered the symbol ":" when expecting one of the following: := . ( @ % ; SQL>

To show structure, you can split lines using carriage returns, and indent lines using spaces or tabs. For example:

SQL> DECLARE 2 x NUMBER := 10; 3 y NUMBER := 5; 4 max NUMBER; 5 BEGIN 6 IF x>y THEN max:=x;ELSE max:=y;END IF; -- correct but hard to read 7 8 -- Easier to read: 9 10 IF x > y THEN 11 max:=x; 12 ELSE 13 max:=y; 14 END IF; 15 END; 16 / PL/SQL procedure successfully completed. SQL>

Topics:

Delimiters

A delimiter is a simple or compound symbol that has a special meaning to PL/SQL. Table 2-1 lists the PL/SQL delimiters.

Table 2-1 PL/SQL Delimiters

SymbolMeaning

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


Identifiers

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

The minimum length of an identifier is one character; the maximum length is 30 characters. The first character must be a letter, but each later character can be either a letter, numeral, dollar sign ($), underscore (_), or number sign (#). For example, the following are acceptable identifiers:

X t2 phone# credit_limit LastName oracle$number money$$$tree SN## try_again_

Characters other than the aforementioned are not allowed in identifiers. For example, the following are not acceptable identifiers:

mine&yours -- ampersand (&) is not allowed debit-amount -- hyphen (-) is not allowed on/off -- slash (/) is not allowed user id -- space is not allowed

PL/SQL is not case-sensitive with respect to identifiers. For example, PL/SQL considers the following to be the same:

lastname LastName LASTNAME

Every character, alphabetic or not, is significant. For example, PL/SQL considers the following to be different:

lastname last_name

Make your identifiers meaningful rather than obscure. For example, the meaning of is obvious, while the meaning of is not.

Topics:

Reserved Words and Keywords

Both reserved words and keywords have special meaning in PL/SQL. The difference between reserved words and keywords is that you cannot use reserved words as identifiers. You can use keywords as as identifiers, but it is not recommended.

Trying to redefine a reserved word causes a compilation error. For example:

SQL> DECLARE 2 end BOOLEAN; 3 BEGIN 4 NULL; 5 END; 6 / end BOOLEAN;*ERROR at line 2:ORA-06550: line 2, column 3:PLS-00103: Encountered the symbol "END" when expecting one of the following: begin function pragma procedure subtype type <an identifier> <a double-quoted delimited-identifier> current cursor delete exists prior The symbol "begin was inserted before "END" to continue. ORA-06550: line 5, column 4: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted SQL>

The PL/SQL reserved words are listed in Table D-1.

Keywords also have special meaning in PL/SQL, but you can redefine them (this is not recommended). The PL/SQL keywords are listed inTable D-2.

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:

"X+Y" "last name" "on/off switch" "employee(s)" "*** header info ***"

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.

Literals

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 about the PL/SQL data types, see Predefined PL/SQL Scalar Data Types and Subtypes.

Topics:

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. For example:

030 6 -14 0 +32767

A real literal is an optionally signed whole or fractional number with a decimal point. For example:

6.6667 0.0 -12.0 3.14159 +8300.00 .5 25.

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 data type; otherwise this literal has the data type. 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 data types, see Predefined PL/SQL Numeric Data Types and Subtypes.

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. For example:

2E5 1.0E-7 3.14159e0 -1E38 -9.5e-3

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):

5E3 = 5 * 10**3 = 5 * 1000 = 5000

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

5E-3 = 5 * 10**-3 = 5 * 0.001 = 0.005

The absolute value of a literal can be in the range . up to (but not including) .. The literal can also be 0. For information about results outside the valid range, see NUMBER Data Type.

Example 2-1 NUMBER Literals

SQL> DECLARE 2 n NUMBER; 3 BEGIN 4 n := -9.999999E-130; 5 n := 9.999E125; 6 n := 10.0E125; 7 END; 8 / n := 10.0E125;*ERROR at line 6:ORA-06550: line 6, column 8:PLS-00569: numeric overflow or underflow ORA-06550: line 6, column 3: PL/SQL: Statement ignored SQL>

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

Example 2-2 Using BINARY_FLOAT and BINARY_DOUBLE

SQL> DECLARE 2 x BINARY_FLOAT := sqrt(2.0f); 3 -- single-precision floating-point number 4 y BINARY_DOUBLE := sqrt(2.0d); 5 -- double-precision floating-point number 6 BEGIN 7 NULL; 8 END; 9 / PL/SQL procedure successfully completed. SQL>

Character Literals

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

'Z' '%' '7' ' ' 'z' '('

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 data type . For example:

'Hello, world!' 'XYZ Corporation' '10-NOV-91' 'He said "Life is like licking honey from a thorn."' '$1,000,000'

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

'baker' 'Baker'

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

'I''m a string, you''re a string.'

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 need not escape other single quotation marks inside the literal:

-- q'!...!' notation allows use of single quotes inside literal string_var := q'!I'm a string, you're a string.!';

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:

func_call(q'[SELECT index_name FROM user_indexes WHERE status ='INVALID']');

For and literals, use the prefix instead of , as in the following example, where 00E0 represents the character é:

where_clause := nq'#WHERE COL_VALUE LIKE '%\00E9'#';

For more information about the data type 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 25.

Date and Time Literals

Datetime literals have various formats depending on the data type, as in Example 2-3.

Example 2-3 Using DateTime Literals

SQL> DECLARE 2 d1 DATE := DATE '1998-12-25'; 3 t1 TIMESTAMP := TIMESTAMP '1997-10-22 13:01:01'; 4 5 t2 TIMESTAMP WITH TIME ZONE := 6 TIMESTAMP '1997-01-31 09:26:56.66 +02:00'; 7 8 -- Three years and two months 9 -- For greater precision, use the day-to-second interval 10 11 i1 INTERVAL YEAR TO MONTH := INTERVAL '3-2' YEAR TO MONTH; 12 13 -- Five days, four hours, three minutes, two and 1/100 seconds 14 15 i2 INTERVAL DAY TO SECOND := 16 INTERVAL '5 04:03:02.01' DAY TO SECOND; 17 18 BEGIN 19 NULL; 20 END; 21 / PL/SQL procedure successfully completed. SQL>

Comments

The PL/SQL compiler ignores comments. Adding comments to your program promotes readability and aids understanding. 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.

Topics:

Single-Line Comments

A single-line comment begins with . It can appear anywhere on a line, and it extends to the end of the line, as in Example 2-4.

Example 2-4 Single-Line Comments

SQL> DECLARE 2 howmany NUMBER; 3 num_tables NUMBER; 4 BEGIN 5 -- Begin processing 6 SELECT COUNT(*) INTO howmany 7 FROM USER_OBJECTS 8 WHERE OBJECT_TYPE = 'TABLE'; -- Check number of tables 9 num_tables := howmany; -- Compute some other value 10 END; 11 / PL/SQL procedure successfully completed. SQL>

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

-- DELETE FROM employees WHERE comm_pct IS NULL

Multiline Comments

A multiline comments begins with , ends with , and can span multiple lines, as in Example 2-5. You can use multiline comment delimiters to "comment out" sections of code.

Example 2-5 Multiline Comment

SQL> DECLARE 2 some_condition BOOLEAN; 3 pi NUMBER := 3.1415926; 4 radius NUMBER := 15; 5 area NUMBER; 6 BEGIN 7 /* Perform some simple tests and assignments */ 8 IF 2 + 2 = 4 THEN 9 some_condition := TRUE; 10 /* We expect this THEN to always be performed */ 11 END IF; 12 /* The following line computes the area of a circle using pi, 13 which is the ratio between the circumference and diameter. 14 After the area is computed, the result is displayed. */ 15 area := pi * radius**2; 16 DBMS_OUTPUT.PUT_LINE('The area is: ' || TO_CHAR(area)); 17 END; 18 / The area is: 706.858335 PL/SQL procedure successfully completed. SQL>

Declarations

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 data type, and name the storage location so that you can reference it.

Topics:

Variables

Example 2-6 declares a variable of type , a variable of type (to which it assigns the initial value zero), and three variables of type . The expression following the assignment operator can be arbitrarily complex, and can refer to previously initialized variables, as in the declaration of the variable .

Variables are initialized every time a block or subprogram is entered. By default, variables are initialized to .

Example 2-6 Declaring Variables

SQL> DECLARE 2 birthday DATE; 3 emp_count SMALLINT := 0; 4 pi REAL := 3.14159; 5 radius REAL := 1; 6 area REAL := pi * radius**2; 7 BEGIN 8 NULL; 9 END; 10 / PL/SQL procedure successfully completed. SQL>

Constants

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. Constants are initialized every time a block or subprogram is entered.

Example 2-7 Declaring Constants

SQL> DECLARE 2 credit_limit CONSTANT REAL := 5000.00; 3 max_days_in_year CONSTANT INTEGER := 366; 4 urban_legend CONSTANT BOOLEAN := FALSE; 5 BEGIN 6 NULL; 7 END; 8 / PL/SQL procedure successfully completed. SQL>

Using DEFAULT

You can use the keyword instead of the assignment operator to initialize variables. You can also use to initialize subprogram parameters, cursor parameters, and fields in a user-defined record.

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

Example 2-8 Assigning Default Values to Variables with DEFAULT Keyword

SQL> DECLARE 2 blood_type CHAR DEFAULT 'O'; -- Same as blood_type CHAR := 'O'; 3 4 hours_worked INTEGER DEFAULT 40; -- Typical value 5 employee_count INTEGER := 0; -- No typical value 6 7 BEGIN 8 NULL; 9 END; 10 / PL/SQL procedure successfully completed. SQL>

Using NOT NULL

A declaration can impose the constraint, which prevents you from assigning a null value to the variable. Because variables are initialized to by default, a declaration that specifies must also specify a default value.

PL/SQL subtypes , , and are predefined as . When declaring a variable of one of these subtypes, you can omit the constraint, and you must specify a default value.

Example 2-9 Declaring Variables with NOT NULL Constraint

SQL> DECLARE 2 acct_id INTEGER(4) NOT NULL := 9999; 3 a NATURALN := 9999; 4 b POSITIVEN := 9999; 5 c SIMPLE_INTEGER := 9999; 6 BEGIN 7 NULL; 8 END; 9 / PL/SQL procedure successfully completed. SQL>

Using the %TYPE Attribute

The attribute lets you declare a constant, variable, field, or parameter to be of the same data type a previously declared variable, field, record, nested table, or database column. If the referenced item changes, your declaration is automatically updated. You need not change your code when, for example, the length of a column increases.

An item declared with (the referencing item) always inherits the data type of the referenced item. The referencing item inherits the constraints only if the referenced item is not a database column. The referencing item inherits the default value only if the referencing item is not a database column and does not have the constraint.

In Example 2-10, the variable inherits the data type of the variable . The variables , , and inherit the data type and default value of the variable .

Example 2-10 Using %TYPE to Declare Variables of the Types of Other Variables

SQL> DECLARE 2 credit PLS_INTEGER RANGE 1000..25000; 3 debit credit%TYPE; -- inherits data type 4 5 name VARCHAR2(20) := 'JoHn SmItH'; 6 upper_name name%TYPE; -- inherits data type and default value 7 lower_name name%TYPE; -- inherits data type and default value 8 init_name name%TYPE; -- inherits data type and default value 9 BEGIN 10 DBMS_OUTPUT.PUT_LINE ('name: ' || name); 11 DBMS_OUTPUT.PUT_LINE ('upper_name: ' || UPPER(name)); 12 DBMS_OUTPUT.PUT_LINE ('lower_name: ' || LOWER(name)); 13 DBMS_OUTPUT.PUT_LINE ('init_name: ' || INITCAP(name)); 14 END; 15 / name: JoHn SmItH upper_name: JOHN SMITH lower_name: john smith init_name: John Smith PL/SQL procedure successfully completed. SQL>

If you add a constraint to the variable in Example 2-10, and declare another variable that references it, you must specify a default value for the new item, as Example 2-11 shows.

Example 2-11 Using %TYPE Incorrectly with NOT NULL Referenced Type

SQL> DECLARE 2 name VARCHAR2(20) NOT NULL := 'JoHn SmItH'; 3 same_name name%TYPE; 4 BEGIN 5 NULL; 6 END; 7 / same_name name%TYPE;*ERROR at line 3:ORA-06550: line 3, column 15:PLS-00218: a variable declared NOT NULL must have an initialization assignment SQL>

In Example 2-12, the variables , , and inherit the data type and constraint of the variable , but not its default value. To avoid the error shown in Example 2-11, they are assigned their own default values.

Example 2-12 Using %TYPE Correctly with NOT NULL Referenced Type

SQL> DECLARE 2 name VARCHAR2(20) NOT NULL := 'JoHn SmItH'; 3 upper_name name%TYPE := UPPER(name); 4 lower_name name%TYPE := LOWER(name); 5 init_name name%TYPE := INITCAP(name); 6 BEGIN 7 DBMS_OUTPUT.PUT_LINE('name: ' || name); 8 DBMS_OUTPUT.PUT_LINE('upper_name: ' || upper_name); 9 DBMS_OUTPUT.PUT_LINE('lower_name: ' || lower_name); 10 DBMS_OUTPUT.PUT_LINE('init_name: ' || init_name); 11 END; 12 / name: JoHn SmItH upper_name: JOHN SMITH lower_name: john smith init_name: John Smith PL/SQL procedure successfully completed. SQL>

The attribute is particularly useful when declaring variables that refer to database columns. When you use .. to declare a data item, you need not know the referenced data type or its attributes (such as precision, scale, and length), and if they change, you need not update your code.

Example 2-13 shows that referencing items do not inherit column constraints or default values from database columns.

Example 2-13 Using %TYPE to Declare Variables of the Types of Table Columns

SQL> CREATE TABLE employees_temp ( 2 empid NUMBER(6) NOT NULL PRIMARY KEY, 3 deptid NUMBER(6) CONSTRAINT c_employees_temp_deptid 4 CHECK (deptid BETWEEN 100 AND 200), 5 deptname VARCHAR2(30) DEFAULT 'Sales' 6 ); Table created. SQL> SQL> DECLARE 2 v_empid employees_temp.empid%TYPE; 3 v_deptid employees_temp.deptid%TYPE; 4 v_deptname employees_temp.deptname%TYPE; 5 BEGIN 6 v_empid := NULL; -- Null constraint not inherited 7 v_deptid := 50; -- Check constraint not inherited 8 DBMS_OUTPUT.PUT_LINE 9 ('v_deptname: ' || v_deptname); -- Default value not inherited 10 END; 11 / v_deptname: PL/SQL procedure successfully completed. SQL>

Using the %ROWTYPE Attribute

The attribute lets you declare a record that represents a row in a table or view. For each column in the referenced table or view, the record has a field with the same name and data type. To reference a field in the record, use .. The record fields do not inherit the constraints or default values of the corresponding columns, as Example 2-14 shows.

If the referenced item table or view changes, your declaration is automatically updated. You need not change your code when, for example, columns are added or dropped from the table or view.

Example 2-14 Using %ROWTYPE to Declare a Record that Represents a Table Row

SQL> CREATE TABLE employees_temp ( 2 empid NUMBER(6) NOT NULL PRIMARY KEY, 3 deptid NUMBER(6) CONSTRAINT c_employees_temp_deptid 4 CHECK (deptid BETWEEN 100 AND 200), 5 deptname VARCHAR2(30) DEFAULT 'Sales' 6 ); Table created. SQL> SQL> DECLARE 2 emprec employees_temp%ROWTYPE; 3 BEGIN 4 emprec.empid := NULL; -- Null constraint not inherited 5 emprec.deptid := 50; -- Check constraint not inherited 6 DBMS_OUTPUT.PUT_LINE 7 ('emprec.deptname: ' || emprec.deptname); 8 -- Default value not inherited 9 END; 10 / emprec.deptname: PL/SQL procedure successfully completed. SQL>

The record in Example 2-14 has a field for every column in the table . The record in Example 2-15 has columns for a subset of columns in the table.

Example 2-15 Declaring a Record that Represents a Subset of Table Columns

SQL> DECLARE 2 CURSOR c1 IS 3 SELECT department_id, department_name 4 FROM departments; 5 6 dept_rec c1%ROWTYPE; -- includes subset of columns in table 7 8 BEGIN 9 NULL; 10 END; 11 / PL/SQL procedure successfully completed. SQL>

The record in Example 2-15 has columns from two tables, and .

Example 2-16 Declaring a Record that Represents a Row from a Join

SQL> DECLARE 2 CURSOR c2 IS 3 SELECT employee_id, email, employees.manager_id, location_id 4 FROM employees, departments 5 WHERE employees.department_id = departments.department_id; 6 7 join_rec c2%ROWTYPE; -- includes columns from two tables 8 9 BEGIN 10 NULL; 11 END; 12 / PL/SQL procedure successfully completed. SQL>

Topics:

Aggregate Assignment

A declaration cannot include an initialization clause, but there are two ways to assign values to all fields of a record at once:

  • If their declarations refer to the same table or cursor, you can assign one record to another, as in Example 2-17.

  • Use the or statement to assign a list of column values to a record.

    The column names must appear in the order in which they were defined in the or statement that created the referenced table or view. 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.

Example 2-17 Assigning One Record to Another, Correctly and Incorrectly

SQL> DECLARE 2 dept_rec1 departments%ROWTYPE; 3 dept_rec2 departments%ROWTYPE; 4 5 CURSOR c1 IS SELECT department_id, location_id 6 FROM departments; 7 8 dept_rec3 c1%ROWTYPE; 9 dept_rec4 c1%ROWTYPE; 10 11 BEGIN 12 dept_rec1 := dept_rec2; -- declarations refer to same table 13 dept_rec3 := dept_rec4; -- declarations refer to same cursor 14 dept_rec2 := dept_rec3; 15 END; 16 / dept_rec2 := dept_rec3;*ERROR at line 14:ORA-06550: line 14, column 16:PLS-00382: expression is of wrong type ORA-06550: line 14, column 3: PL/SQL: Statement ignored SQL>

Example 2-18 uses the statement to assign a list of column values to a record.

Example 2-18 Using SELECT INTO for Aggregate Assignment

SQL> DECLARE 2 dept_rec departments%ROWTYPE; 3 BEGIN 4 SELECT * INTO dept_rec 5 FROM departments 6 WHERE department_id = 30 7 AND ROWNUM < 2; 8 END; 9 / PL/SQL procedure successfully completed. SQL>

Using Aliases

Select-list items fetched from a cursor associated with must have simple names or, if they are expressions, must have aliases, such as in Example 2-19.

Example 2-19 Using an Alias for an Expression Associated with %ROWTYPE

SQL> BEGIN 2 FOR item IN 3 (SELECT (first_name || ' ' || last_name) complete_name 4 FROM employees 5 WHERE ROWNUM < 11 6 ) LOOP 7 DBMS_OUTPUT.PUT_LINE 8 ('Employee name: ' || item.complete_name); 9 END LOOP; 10 END; 11 / Employee name: Ellen Abel Employee name: Sundar Ande Employee name: Mozhe Atkinson Employee name: David Austin Employee name: Hermann Baer Employee name: Shelli Baida Employee name: Amit Banda Employee name: Elizabeth Bates Employee name: Sarah Bell Employee name: David Bernstein PL/SQL procedure successfully completed. SQL>

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 Creating Nested Subprograms that Invoke Each Other.

Some languages enable you to declare a list of variables that have the same data type. PL/SQL does not allow this. You must declare each variable separately. To save space, you can put more than one declaration on a line. For example:

SQL> DECLARE 2 i, j, k, l SMALLINT; 3 BEGIN 4 NULL; 5 END; 6 / i, j, k, l SMALLINT;*ERROR at line 2:ORA-06550: line 2, column 4:PLS-00103: Encountered the symbol "," when expecting one of the following: constant exception <an identifier> <a double-quoted delimited-identifier> table long double ref char time timestamp interval date binary national character nchar ORA-06550: line 2, column 14: PLS-00103: Encountered the symbol "SMALLINT" when expecting one of the following: . ( ) , * @ % & = - + < / > at in is mod remainder not rem => <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec between || ORA-06550: line 5, column 4: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted SQL> DECLARE 2 i SMALLINT; j SMALLINT; k SMALLINT; l SMALLINT; 3 BEGIN 4 NULL; 5 END; 6 / PL/SQL procedure successfully completed. SQL>

Naming Conventions

The same naming conventions apply to PL/SQL constants, variables, cursors, cursor variables, exceptions, procedures, functions, and packages. Names can be simple, qualified, remote, or both qualified and remote. For example:

  • Simple—procedure name only:

    raise_salary(employee_id, amount);
  • Qualified—procedure name preceded by the name of the package that contains it (this is called dot notation because a dot separates the package name from the procedure name):

    emp_actions.raise_salary(employee_id, amount);
  • Remote—procedure name followed by the remote access indicator () and a link to the database on which the procedure is stored:

    raise_salary@newyork(employee_id, amount);
  • Qualified and remote:

    emp_actions.raise_salary@newyork(employee_id, amount);

Topics:

Scope

Within the same scope, all declared identifiers must be unique. Even if their data types differ, variables and parameters cannot share the same name. An error occurs when the duplicate identifier is referenced, as in Example 2-20.

Example 2-20 Duplicate Identifiers in Same Scope

SQL> DECLARE 2 id BOOLEAN; 3 id VARCHAR2(5); -- duplicate identifier 4 BEGIN 5 id := FALSE; 6 END; 7 / id := FALSE;*ERROR at line 5:ORA-06550: line 5, column 3:PLS-00371: at most one declaration for 'ID' is permitted ORA-06550: line 5, column 3: PL/SQL: Statement ignored SQL>

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, as Example 2-21 shows.

Example 2-21 Case Insensitivity of Identifiers

SQL> DECLARE 2 zip_code INTEGER; 3 Zip_Code INTEGER; 4 BEGIN 5 zip_code := 90120; 6 END; 7 / zip_code := 90120;*ERROR at line 5:ORA-06550: line 5, column 3:PLS-00371: at most one declaration for 'ZIP_CODE' is permitted ORA-06550: line 5, column 3: PL/SQL: Statement ignored SQL>

Name Resolution

In 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 used in a clause, SQL considers both names to refer to the column.

Caution:

When a variable name is interpreted as a column name, data can be deleted unintentionally, as Example 2-22 shows. Example 2-22 also shows two ways to avoid this error.

Example 2-22 Using a Block Label for Name Resolution

SQL> CREATE TABLE employees2 AS 2 SELECT last_name FROM employees; Table created. SQL> SQL> -- Deletes everyone, because both LAST_NAMEs refer to the column: SQL> SQL> BEGIN 2 DELETE FROM employees2 3 WHERE last_name = last_name; 4 DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows.'); 5 END; 6 / Deleted 107 rows. PL/SQL procedure successfully completed. SQL> ROLLBACK; Rollback complete. SQL> SQL> -- Avoid error by giving column and variable different names: SQL> SQL> DECLARE 2 last_name VARCHAR2(10) := 'King'; 3 v_last_name VARCHAR2(10) := 'King'; 4 BEGIN 5 DELETE FROM employees2 6 WHERE last_name = v_last_name; 7 DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows.'); 8 END; 9 / Deleted 2 rows. PL/SQL procedure successfully completed. SQL> ROLLBACK; Rollback complete. SQL> SQL> -- Avoid error by qualifying variable with block name: SQL> SQL> <<main>> -- Label block for future reference 2 DECLARE 3 last_name VARCHAR2(10) := 'King'; 4 v_last_name VARCHAR2(10) := 'King'; 5 BEGIN 6 DELETE FROM employees2 7 WHERE last_name = main.last_name; 8 DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows.'); 9 END; 10 / Deleted 2 rows. PL/SQL procedure successfully completed. SQL> ROLLBACK; Rollback complete. SQL>

You can use a subprogram name to qualify references to local variables and formal parameters, as in Example 2-23.

Example 2-23 Using a Subprogram Name for Name Resolution

SQL> DECLARE 2 FUNCTION dept_name (department_id IN NUMBER) 3 RETURN departments.department_name%TYPE 4 IS 5 department_name departments.department_name%TYPE; 6 BEGIN 7 SELECT department_name INTO dept_name.department_name 8 -- ^column^local variable 9 FROM departments 10 WHERE department_id = dept_name.department_id; 11 -- ^column^formal parameter 12 RETURN department_name; 13 END; 14 BEGIN 15 FOR item IN (SELECT department_id FROM departments) 16 LOOP 17 DBMS_OUTPUT.PUT_LINE 18 ('Department: ' || dept_name(item.department_id)); 19 END LOOP; 20 END; 21 / Department: Administration Department: Marketing Department: Purchasing Department: Human Resources Department: Shipping Department: IT Department: Public Relations Department: Sales Department: Executive Department: Finance Department: Accounting Department: Treasury Department: Corporate Tax Department: Control And Credit Department: Shareholder Services Department: Benefits Department: Manufacturing Department: Construction Department: Contracting Department: Operations Department: IT Support Department: NOC Department: IT Helpdesk Department: Government Sales Department: Retail Sales Department: Recruiting Department: Payroll PL/SQL procedure successfully completed. SQL>

Synonyms

You can use the SQL statement to create synonyms to provide location transparency for remote schema objects. You cannot create synonyms for items declared within PL/SQL subprograms or packages.

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 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 declared in a PL/SQL unit is local to that unit and global to its subunits. 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.

You cannot declare an identifier twice in the same PL/SQL unit, but you can declare the same identifier in two different units. The two items represented by the identifier are distinct, and changing one does not affect the other.

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.

Example 2-24 shows the scope and visibility of several global and local identifiers. The global identifier is redeclared in the first sub-block.

Example 2-24 Scope and Visibility of Identifiers

SQL> DECLARE 2 a CHAR; -- Scope of a (CHAR) begins 3 b REAL; -- Scope of b begins 4 BEGIN 5 -- Visible: a (CHAR), b 6 7 DECLARE 8 a INTEGER; -- Scope of a (INTEGER) begins 9 c REAL; -- Scope of c begins 10 BEGIN 11 -- Visible: a (INTEGER), b, c 12 NULL; 13 END; -- Scopes of a (INTEGER) and c end 14 15 DECLARE 16 d REAL; -- Scope of d begins 17 BEGIN 18 -- Visible: a (CHAR), b, d 19 NULL; 20 END; -- Scope of d ends 21 22 -- Visible: a (CHAR), b 23 END; -- Scopes of a (CHAR) and b end 24 / PL/SQL procedure successfully completed. SQL>

Example 2-25 declares the variable in a labeled block, , redeclares it in a sub-block, and then references it in the sub-block by qualifying its name with the block label.

Example 2-25 Qualifying a Redeclared Global Identifier with a Block Label

SQL> <<outer>> 2 DECLARE 3 birthdate DATE := '09-AUG-70'; 4 BEGIN 5 DECLARE 6 birthdate DATE; 7 BEGIN 8 birthdate := '29-SEP-70'; 9 10 IF birthdate = outer.birthdate THEN 11 DBMS_OUTPUT.PUT_LINE ('Same Birthday'); 12 ELSE 13 DBMS_OUTPUT.PUT_LINE ('Different Birthday'); 14 END IF; 15 END; 16 END; 17 / Different Birthday PL/SQL procedure successfully completed. SQL>

Example 2-26 declares the variable in a procedure, , redeclares it in a function within the procedure, and then references it in the function by qualifying its name with the procedure name. (The built-in SQL function returns the character equivalent of its argument. For more information about , see Oracle Database SQL Language Reference.)

Example 2-26 Qualifying an Identifier with a Subprogram Name

SQL> CREATE OR REPLACE PROCEDURE check_credit (limit NUMBER) AS 2 rating NUMBER := 3; 3 4 FUNCTION check_rating RETURN BOOLEAN IS 5 rating NUMBER := 1; 6 over_limit BOOLEAN; 7 BEGIN 8 IF check_credit.rating <= limit THEN 9 over_limit := FALSE; 10 ELSE 11 over_limit := TRUE; 12 rating := limit; 13 END IF; 14 RETURN over_limit; 15 END check_rating; 16 BEGIN 17 IF check_rating THEN 18 DBMS_OUTPUT.PUT_LINE 19 ('Credit rating over limit (' || TO_CHAR(limit) || '). ' 20 || 'Rating: ' || TO_CHAR(rating)); 21 ELSE 22 DBMS_OUTPUT.PUT_LINE 23 ('Credit rating OK. ' || 'Rating: ' || TO_CHAR(rating)); 24 END IF; 25 END; 26 / Procedure created. SQL> BEGIN 2 check_credit(1); 3 END; 4 / Credit rating over limit (1). Rating: 3 PL/SQL procedure successfully completed. SQL>

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

Example 2-27 has both a block and a subprogram named . Both the block and the subprogram declare a variable named . Within the subprogram, . refers to the local variable , not to the global variable .

Example 2-27 Label and Subprogram with Same Name in Same Scope

SQL> <<echo>> 2 DECLARE 3 x NUMBER := 5; 4 5 PROCEDURE echo AS 6 x NUMBER := 0; 7 BEGIN 8 DBMS_OUTPUT.PUT_LINE('x = ' || x); 9 DBMS_OUTPUT.PUT_LINE('echo.x = ' || echo.x); 10 END; 11 12 BEGIN 13 echo; 14 END; 15 / x = 0 echo.x = 0 PL/SQL procedure successfully completed. SQL>

Example 2-28 has both a block and a subprogram named . Both the block and the subprogram declare a variable named . Within the subprogram, . refers to the local variable , not to the global variable .

Example 2-28 has two labels for the outer block, and . The second label is reused in the inner block. Within the inner block, . refers to the local variable , not to the global variable , which results in the error .

Example 2-28 Block with Multiple and Duplicate Labels

SQL> <<compute_ratio>> 2 <<another_label>> 3 DECLARE 4 numerator NUMBER := 22; 5 denominator NUMBER := 7; 6 BEGIN 7 <<another_label>> 8 DECLARE 9 denominator NUMBER := 0; 10 BEGIN 11 DBMS_OUTPUT.PUT_LINE('Ratio with compute_ratio.denominator = '); 12 DBMS_OUTPUT.PUT_LINE(numerator/compute_ratio.denominator); 13 14 DBMS_OUTPUT.PUT_LINE('Ratio with another_label.denominator = '); 15 DBMS_OUTPUT.PUT_LINE(numerator/another_label.denominator); 16 17 EXCEPTION 18 WHEN ZERO_DIVIDE THEN 19 DBMS_OUTPUT.PUT_LINE('Divide-by-zero error: can''t divide ' 20 || numerator || ' by ' || denominator); 21 WHEN OTHERS THEN 22 DBMS_OUTPUT.PUT_LINE('Unexpected error.'); 23 END inner_label; 24 END compute_ratio; 25 / Ratio with compute_ratio.denominator = 3.14285714285714285714285714285714285714 Ratio with another_label.denominator =Divide-by-zero error: cannot divide 22 by 0 PL/SQL procedure successfully completed. SQL>

Assigning Values to Variables

You can assign a default value to a variable when you declare it (as explained in Variables) or after you have declared it, with an assignment statement. For example, the following statement assigns a new value to the variable , overwriting its old value:

bonus := salary * 0.15;

The expression following the assignment operator () can be arbitrarily complex, but it must yield a data type that is the same as, or convertible to, the data type of the variable.

Variables are initialized every time a block or subprogram is entered. By default, variables are initialized to . Unless you explicitly initialize a variable, its value is , as Example 2-29 shows.

Example 2-29 Variable Initialized to NULL by Default

SQL> DECLARE 2 counter INTEGER; 3 BEGIN 4 counter := counter + 1; 5 6 IF counter IS NULL THEN 7 DBMS_OUTPUT.PUT_LINE('counter is NULL.'); 8 END IF; 9 END; 10 / counter is NULL. PL/SQL procedure successfully completed. SQL>

To avoid unexpected results, never reference a variable before assigning it a value.

Topics:

Assigning BOOLEAN Values

Only the values , , and can be assigned to a variable, either as literals or as the results of expressions.

In Example 2-30, the variable is initialized to by default, assigned the literal value , compared to a literal value, and assigned the value of a expression.

Example 2-30 Assigning BOOLEAN Values

SQL> DECLARE 2 done BOOLEAN; -- Initialize to NULL by default 3 counter NUMBER := 0; 4 BEGIN 5 done := FALSE; -- Assign literal value 6 WHILE done != TRUE -- Compare to literal value 7 LOOP 8 counter := counter + 1; 9 done := (counter > 500); -- Assign value of BOOLEAN expression 10 END LOOP; 11 END; 12 / PL/SQL procedure successfully completed. SQL>

Assigning SQL Query Results to PL/SQL Variables

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

Example 2-31 Assigning Query Results to Variables

SQL> DECLARE 2 emp_id employees.employee_id%TYPE := 100; 3 emp_name employees.last_name%TYPE; 4 wages NUMBER(7,2); 5 BEGIN 6 SELECT last_name, salary + (salary * nvl(commission_pct,0)) 7 INTO emp_name, wages 8 FROM employees 9 WHERE employee_id = emp_id; 10 11 DBMS_OUTPUT.PUT_LINE 12 ('Employee ' || emp_name || ' might make ' || wages); 13 END; 14 / Employee King might make 24000 PL/SQL procedure successfully completed. SQL>

Because SQL does not have a type, you cannot select column values into a variable. For more information about assigning variables with the DML statements, including situations when the value of a variable is undefined, see Data Manipulation Language (DML) Statements.

PL/SQL Expressions and Comparisons

The simplest PL/SQL expression consists of a single variable, which yields a value directly. You can build arbitrarily complex PL/SQL expressions from operands and operators. An operand is a variable, constant, literal, placeholder, or function call. An operator is either unary or binary, operating on either one operand or two operands, respectively. An example of a unary operator is negation (). An example of a binary operator is addition ().

An example of a simple arithmetic expression is:

-X / 2 + 3

PL/SQL evaluates an expression by combining the values of the operands as specified by the operators. An expression always returns a single value. PL/SQL determines the data type of this value by examining the expression and the context in which it appears.

Topics:

Concatenation Operator

The concatenation operator () appends one string operand to another. Each string can be , , , or the equivalent Unicode-enabled type. If either string is a , the result is a temporary ; otherwise, it is a value.

Example 2-32 and many other examples in this book use the concatenation operator.

Example 2-32 Concatenation Operator

SQL> DECLARE 2 x VARCHAR2(4) := 'suit'; 3 y VARCHAR2(4) := 'case'; 4 BEGIN 5 DBMS_OUTPUT.PUT_LINE (x || y); 6 END; 7 / suitcase PL/SQL procedure successfully completed. SQL>

Operator Precedence

The operations within an expression are evaluated in order of precedence. Table 2-2 shows operator precedence from highest to lowest. Operators with equal precedence are applied in no particular order.

Table 2-2 Operator Precedence

OperatorOperation

exponentiation

,

identity, negation

,

multiplication, division

, ,

addition, subtraction, concatenation

, , , , , , , , , , , ,

comparison

logical negation

conjunction

inclusion


You can use parentheses to control the order of evaluation. When parentheses are nested, the most deeply nested subexpression is evaluated first. You can use parentheses to improve readability, even when you do not need them to control the order of evaluation. (In Example 2-33, the built-in SQL function returns the character equivalent of its argument. For more information about , see Oracle Database SQL Language Reference.)

Example 2-33 Operator Precedence

SQL> DECLARE 2 salary NUMBER := 60000; 3 commission NUMBER := 0.10; 4 BEGIN 5 -- Division has higher precedence than addition: 6 7 DBMS_OUTPUT.PUT_LINE('5 + 12 / 4 = ' || TO_CHAR(5 + 12 / 4)); 8 DBMS_OUTPUT.PUT_LINE('12 / 4 + 5 = ' || TO_CHAR(12 / 4 + 5)); 9 10 -- Parentheses override default operator precedence: 11 12 DBMS_OUTPUT.PUT_LINE('8 + 6 / 2 = ' || TO_CHAR(8 + 6 / 2)); 13 DBMS_OUTPUT.PUT_LINE('(8 + 6) / 2 = ' || TO_CHAR((8 + 6) / 2)); 14 15 -- Most deeply nested subexpression is evaluated first: 16 17 DBMS_OUTPUT.PUT_LINE('100 + (20 / 5 + (7 - 3)) = ' 18 || TO_CHAR(100 + (20 / 5 + (7 - 3)))); 19 20 -- Parentheses, even when unnecessary, improve readability: 21 22 DBMS_OUTPUT.PUT_LINE('(salary * 0.05) + (commission * 0.25) = ' 23 || TO_CHAR((salary * 0.05) + (commission * 0.25)) 24 ); 25 26 DBMS_OUTPUT.PUT_LINE('salary * 0.05 + commission * 0.25 = ' 27 || TO_CHAR(salary * 0.05 + commission * 0.25) 28 ); 29 END; 30 / 5 + 12 / 4 = 8 12 / 4 + 5 = 8 8 + 6 / 2 = 11 (8 + 6) / 2 = 7 100 + (20 / 5 + (7 - 3)) = 108 (salary * 0.05) + (commission * 0.25) = 3000.025 salary * 0.05 + commission * 0.25 = 3000.025 PL/SQL procedure successfully completed. SQL>

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 Logical Truth Table

xyx AND yx OR yNOT x

Be careful to avoid unexpected results in expressions involving . For more information, see Handling NULL Values in Comparisons and Conditional Statements.

As Table 2-3 and Example 2-34 show, returns if and only if both operands are . (Several examples use the procedure that Example 2-34 creates.)

Example 2-34 AND Operator

SQL> CREATE OR REPLACE PROCEDURE print_boolean ( 2 name VARCHAR2, 3 value BOOLEAN 4 ) IS 5 BEGIN 6 IF value IS NULL THEN 7 DBMS_OUTPUT.PUT_LINE (name || ' = NULL'); 8 ELSIF value = TRUE THEN 9 DBMS_OUTPUT.PUT_LINE (name || ' = TRUE'); 10 ELSE 11 DBMS_OUTPUT.PUT_LINE (name || ' = FALSE'); 12 END IF; 13 END; 14 / Procedure created. SQL> DECLARE 2 3 PROCEDURE print_x_and_y ( 4 x BOOLEAN, 5 y BOOLEAN 6 ) IS 7 BEGIN 8 print_boolean ('x', x); 9 print_boolean ('y', y); 10 print_boolean ('x AND y', x AND y); 11 END; 12 13 BEGIN 14 print_x_and_y (FALSE, FALSE); 15 print_x_and_y (TRUE, FALSE); 16 print_x_and_y (FALSE, TRUE); 17 print_x_and_y (TRUE, TRUE); 18 19 print_x_and_y (TRUE, NULL); 20 print_x_and_y (FALSE, NULL); 21 print_x_and_y (NULL, TRUE); 22 print_x_and_y (NULL, FALSE); 23 END; 24 / x = FALSE y = FALSE x AND y = FALSE x = TRUE y = FALSE x AND y = FALSE x = FALSE y = TRUE x AND y = FALSE x = TRUEy = TRUEx AND y = TRUE x = TRUE y = NULL x AND y = NULL x = FALSE y = NULL x AND y = FALSE x = NULL y = TRUE x AND y = NULL x = NULL y = FALSE x AND y = FALSE PL/SQL procedure successfully completed. SQL>

As Table 2-3 and Example 2-35 show, returns if either operand is . (Example 2-35 invokes the procedure created in Example 2-34.)

Example 2-35 OR Operator

SQL> DECLARE 2 3 PROCEDURE print_x_or_y ( 4 x BOOLEAN, 5 y BOOLEAN 6 ) IS 7 BEGIN 8 print_boolean ('x', x); 9 print_boolean ('y', y); 10 print_boolean ('x OR y', x OR y); 11 END; 12 13 BEGIN 14 print_x_or_y (FALSE, FALSE); 15 print_x_or_y (TRUE, FALSE); 16 print_x_or_y (FALSE, TRUE); 17 print_x_or_y (TRUE, TRUE); 18 19 print_x_or_y (TRUE, NULL); 20 print_x_or_y (FALSE, NULL); 21 print_x_or_y (NULL, TRUE); 22 print_x_or_y (NULL, FALSE); 23 END; 24 / x = FALSE y = FALSE x OR y = FALSE x = TRUEy = FALSEx OR y = TRUEx = FALSEy = TRUEx OR y = TRUEx = TRUEy = TRUEx OR y = TRUEx = TRUEy = NULLx OR y = TRUE x = FALSE y = NULL x OR y = NULL x = NULLy = TRUEx OR y = TRUE x = NULL y = FALSE x OR y = NULL PL/SQL procedure successfully completed. SQL>

As Table 2-3 and Example 2-36 show, returns the opposite of its operand, unless the operand is . returns , because is an indeterminate value. (Example 2-36 invokes the procedure created in Example 2-34.)

Example 2-36 NOT Operator

SQL> DECLARE 2 3 PROCEDURE print_not_x ( 4 x BOOLEAN 5 ) IS 6 BEGIN 7 print_boolean ('x', x); 8 print_boolean ('NOT x', NOT x); 9 END; 10 11 BEGIN 12 print_not_x (TRUE); 13 print_not_x (FALSE); 14 print_not_x (NULL); 15 END; 16 / x = TRUENOT x = FALSEx = FALSENOT x = TRUEx = NULLNOT x = NULL PL/SQL procedure successfully completed. SQL>

Topics:

Order of Evaluation

As with all operators, the order of evaluation for logical operators is determined by the operator precedence shown in Table 2-2, and can be changed by parentheses, as in Example 2-37. (Example 2-37 invokes the procedure created in Example 2-34.)

Example 2-37 Changing Order of Evaluation of Logical Operators

SQL> DECLARE 2 x BOOLEAN := FALSE; 3 y BOOLEAN := FALSE; 4 5 BEGIN 6 print_boolean ('NOT x AND y', NOT x AND y); 7 print_boolean ('NOT (x AND y)', NOT (x AND y)); 8 print_boolean ('(NOT x) AND y', (NOT x) AND y); 9 END; 10 / NOT x AND y = FALSENOT (x AND y) = TRUE (NOT x) AND y = FALSE PL/SQL procedure successfully completed. SQL>

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 errors.

In Example 2-38, short-circuit evaluation prevents the expression in line 8 from causing an error.

Example 2-38 Short-Circuit Evaluation

SQL> DECLARE 2 on_hand INTEGER := 0; 3 on_order INTEGER := 100; 4 BEGIN 5 -- Does not cause divide-by-zero error; 6 -- evaluation stops after first expression 7 8 IF (on_hand = 0) OR ((on_order / on_hand) < 5) THEN 9 DBMS_OUTPUT.PUT_LINE('On hand quantity is zero.'); 10 END IF; 11 END; 12 / On hand quantity is zero. PL/SQL procedure successfully completed. SQL>

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 either , , OR . Typically, you use comparison operators in conditional control statements and in the clauses of SQL data manipulation statements.

The comparison operators are:

Note:

Using values with comparison operators can create temporary values. Be sure that your temporary tablespace is large enough to handle them.

Table 2-4 Relational Operators

OperatorMeaning

equal to

, , ,

not equal to

less than

greater than

less than or equal to

greater than or equal to


Example 2-39 invokes the procedure created in Example 2-34 to print values of some expressions that include relational operators.

Example 2-39 Relational Operators

SQL> BEGIN 2 print_boolean ('(2 + 2 = 4)', 2 + 2 = 4); 3 4 print_boolean ('(2 + 2 <> 4)', 2 + 2 <> 4); 5 print_boolean ('(2 + 2 != 4)', 2 + 2 != 4); 6 print_boolean ('(2 + 2 ~= 4)', 2 + 2 ~= 4); 7 print_boolean ('(2 + 2 ^= 4)', 2 + 2 ^= 4); 8 9 print_boolean ('(1 < 2)', 1 < 2); 10 11 print_boolean ('(1 > 2)', 1 > 2); 12 13 print_boolean ('(1 <= 2)', 1 <= 2); 14 15 print_boolean ('(1 >= 1)', 1 >= 1); 16 END; 17 / (2 + 2 = 4) = TRUE(2 + 2 <> 4) = FALSE(2 + 2 != 4) = FALSE(2 + 2 ~= 4) = FALSE(2 + 2 ^= 4) = FALSE(1 < 2) = TRUE(1 > 2) = FALSE(1 <= 2) = TRUE(1 >= 1) = TRUE PL/SQL procedure successfully completed. SQL>
IS NULL Operator

The operator returns the value if its operand is or if it is not . Comparisons involving values always yield .

To test whether a value is , use , as the procedure in Example 2-34 does at line 6.

LIKE Operator

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

The pattern can include the two "wildcard" characters underscore () and percent sign (%). Underscore matches exactly one character. Percent sign () matches zero or more characters.

Case is significant. The string matches the pattern but not , as Example 2-40 shows.

Example 2-40 LIKE Operator

SQL> DECLARE 2 3 PROCEDURE compare ( 4 value VARCHAR2, 5 pattern VARCHAR2 6 ) IS 7 BEGIN 8 IF value LIKE pattern THEN 9 DBMS_OUTPUT.PUT_LINE ('TRUE'); 10 ELSE 11 DBMS_OUTPUT.PUT_LINE ('FALSE'); 12 END IF; 13 END; 14 15 BEGIN 16 compare('Johnson', 'J%s_n'); 17 compare('Johnson', 'J%S_N'); 18 END; 19 / TRUE FALSE PL/SQL procedure successfully completed. SQL>

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

Example 2-41 uses the backslash as the escape character, so that the percent sign in the string does not act as a wildcard.

Example 2-41 Escape Character in Pattern

SQL> DECLARE 2 3 PROCEDURE half_off (sale_sign VARCHAR2) IS 4 BEGIN 5 IF sale_sign LIKE '50\% off!' ESCAPE '\' THEN 6 DBMS_OUTPUT.PUT_LINE ('TRUE'); 7 ELSE 8 DBMS_OUTPUT.PUT_LINE ('FALSE'); 9 END IF; 10 END; 11 12 BEGIN 13 half_off('Going out of business!'); 14 half_off('50% off!'); 15 END; 16 / FALSE TRUE PL/SQL procedure successfully completed. SQL>
BETWEEN Operator

The operator tests whether a value lies in a specified range. means that

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.

Topics

Database Character Set

PL/SQL uses the database character set to represent:

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 2-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 2-1 Punctuation Characters in Every Database Character Set

SymbolName

Left parenthesis

Right parenthesis

Left angle bracket

Right angle bracket

Plus sign

Hyphen or minus sign

Asterisk

Slash

Equal sign

Comma

Semicolon

Colon

Period

Exclamation point

Question mark

Apostrophe orsingle quotation mark

"

Quotation mark ordouble quotation mark

At sign

Percent sign

Number sign

Dollar sign

Underscore

Vertical bar


National Character Set

PL/SQL uses the national character set to represent character values of data types , and . For information about these data types, see "SQL Data Types".

Lexical Units

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

Topics

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 2-2 summarizes the PL/SQL delimiters.

Table 2-2 PL/SQL Delimiters

DelimiterMeaning

Addition operator

Assignment operator

Association operator

Attribute indicator

Character string delimiter

Component indicator

Concatenation operator

Division operator

Exponentiation operator

Expression or list delimiter (begin)

Expression or list delimiter (end)

Host variable indicator

Item separator

Label delimiter (begin)

Label delimiter (end)

Multiline comment delimiter (begin)

Multiline comment delimiter (end)

Multiplication operator

Quoted identifier delimiter

Range operator

Relational operator (equal)

Relational operator (not equal)

Relational operator (not equal)

Relational operator (not equal)

Relational operator (not equal)

Relational operator (less than)

Relational operator (greater than)

Relational operator (less than or equal)

Relational operator (greater than or equal)

Remote access indicator

Single-line comment indicator

Statement terminator

Subtraction or negation operator


Identifiers

Identifiers name PL/SQL elements, which include:

  • Constants

  • Cursors

  • Exceptions

  • Keywords

  • Labels

  • Packages

  • Reserved words

  • Subprograms

  • Types

  • Variables

Every character in an identifier, alphabetic or not, is significant. For example, the identifiers and 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 , , and are the same.

Topics

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.

Predefined Identifiers

Predefined identifiers are declared in the predefined package . An example of a predefined identifier is the exception .

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

SELECT TYPE_NAME FROM ALL_TYPES WHERE PREDEFINED='YES';

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").

User-Defined Identifiers

A user-defined identifier is:

  • Composed of characters from the database character set

  • Either ordinary or quoted

Tip:

Make user-defined identifiers meaningful. For example, the meaning of is obvious, but the meaning of is not.
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. The representation of the identifier in the database character set cannot exceed 30 bytes.

Examples of acceptable ordinary user-defined identifiers:

X t2 phone# credit_limit LastName oracle$number money$$$tree SN## try_again_

Examples of unacceptable ordinary user-defined identifiers:

mine&yours debit-amount on/off user id
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:

"X+Y" "last name" "on/off switch" "employee(s)" "*** header info ***"

The representation of the quoted identifier in the database character set cannot exceed 30 bytes (excluding the double quotation marks).

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.

In Example 2-1, the quoted user-defined identifier , without its enclosing double quotation marks, is a valid ordinary user-defined identifier. Therefore, the reference is valid.

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

DECLARE "HELLO" varchar2(10) := 'hello'; BEGIN DBMS_Output.Put_Line(Hello); END; /

Result:

hello

In Example 2-2, the reference is invalid, because the double quotation marks make the identifier case-sensitive.

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

DECLARE "HELLO" varchar2(10) := 'hello'; BEGIN DBMS_Output.Put_Line("Hello"); END; /

Result:

DBMS_Output.Put_Line("Hello"); * ERROR at line 4: ORA-06550: line 4, column 25: PLS-00201: identifier 'Hello' must be declared ORA-06550: line 4, column 3: PL/SQL: Statement ignored

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 2-3 declares quoted user-defined identifiers , , and . Although , , and represent the same reserved word, , , and represent different identifiers.

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

DECLARE "BEGIN" varchar2(15) := 'UPPERCASE'; "Begin" varchar2(15) := 'Initial Capital'; "begin" varchar2(15) := 'lowercase'; BEGIN DBMS_Output.Put_Line("BEGIN"); DBMS_Output.Put_Line("Begin"); DBMS_Output.Put_Line("begin"); END; /

Result:

UPPERCASE Initial Capital lowercase PL/SQL procedure successfully completed.

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

Example 2-4 Neglecting Double Quotation Marks

DECLARE "HELLO" varchar2(10) := 'hello'; -- HELLO is not a reserved word "BEGIN" varchar2(10) := 'begin'; -- BEGIN is a reserved word BEGIN DBMS_Output.Put_Line(Hello); -- Double quotation marks are optional DBMS_Output.Put_Line(BEGIN); -- Double quotation marks are required end; /

Result:

DBMS_Output.Put_Line(BEGIN); -- Double quotation marks are required * ERROR at line 6: ORA-06550: line 6, column 24: PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following: ( ) - + case mod new not null <an identifier> <a double-quoted delimited-identifier> <a bind variable> table continue avg count current exists max min prior sql stddev sum variance execute multiset the both leading trailing forall merge year month day hour minute second timezone_hour timezone_minute timezone_region timezone_abbr time timestamp interval date <a string literal with character set specificat

Example 2-5 references a quoted user-defined identifier that is a reserved word, neglecting its case-sensitivity.

Example 2-5 Neglecting Case-Sensitivity

DECLARE "HELLO" varchar2(10) := 'hello'; -- HELLO is not a reserved word "BEGIN" varchar2(10) := 'begin'; -- BEGIN is a reserved word BEGIN DBMS_Output.Put_Line(Hello); -- Identifier is case-insensitive DBMS_Output.Put_Line("Begin"); -- Identifier is case-sensitive END; /

Result:

DBMS_Output.Put_Line("Begin"); -- Identifier is case-sensitive * ERROR at line 6: ORA-06550: line 6, column 25: PLS-00201: identifier 'Begin' must be declared ORA-06550: line 6, column 3: PL/SQL: Statement ignored

Literals

A literal is a value that is neither represented by an identifier nor calculated from other values. For example, is an integer literal and is a character literal, but is not a literal.

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

Note:

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, and are different.

  • Whitespace characters are significant.

    For example, these literals are different:

    'abc' ' abc' 'abc ' ' abc ' 'a b c'
  • 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:

    BEGIN DBMS_OUTPUT.PUT_LINE('This string breaks here.'); END; /

    Prints this:

    This string breaks here.

    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 example, this PL/SQL code:

    BEGIN DBMS_OUTPUT.PUT_LINE('This string ' || 'contains no line-break character.'); END; /

    Prints this:

    This string contains no line-break character.

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

  • through 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 and is called a null string.

    However, this value is not the value .

  • 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.

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.

Topics

Single-Line Comments

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

Caution:

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.

Example 2-6 has three single-line comments.

Example 2-6 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 another value END; /

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

-- DELETE FROM employees WHERE comm_pct IS NULL

Multiline Comments

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

Example 2-7 has two multiline comments. (The SQL function returns the character equivalent of its argument. For more information about , see Oracle Database SQL Language Reference.)

Example 2-7 Multiline 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; /* This 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; /

Result:

The area is: 706.858335

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:

/* IF 2 + 2 = 4 THEN some_condition := TRUE; /* We expect this THEN to always be performed */ END IF; */

This does not cause a syntax error:

/* IF 2 + 2 = 4 THEN some_condition := TRUE; -- We expect this THEN to always be performed END IF; */

Whitespace Characters Between Lexical Units

You can put whitespace characters between lexical units, which often makes your source text easier to read, as Example 2-8 shows.

Example 2-8 Whitespace Characters Improving Source Text Readability

DECLARE x NUMBER := 10; y NUMBER := 5; max NUMBER; BEGIN IF x>y THEN max:=x;ELSE max:=y;END IF; -- correct but hard to read-- Easier to read: IF x > y THEN max:=x; ELSE max:=y; END IF; END; /

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.

Topics

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

Variable Declarations

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 (see "User-Defined Identifiers").

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 2-9 declares several variables with scalar data types.

Example 2-9 Scalar Variable Declarations

DECLARE part_number NUMBER(6); -- SQL data type part_name VARCHAR2(20); -- SQL data type in_stock BOOLEAN; -- PL/SQL-only data type part_price NUMBER(6,2); -- SQL data type part_description VARCHAR2(50); -- SQL data type BEGIN NULL; END; /

Constant Declarations

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

Example 2-10 declares three constants with scalar data types.

Example 2-10 Constant Declarations

DECLARE credit_limit CONSTANT REAL := 5000.00; -- SQL data type max_days_in_year CONSTANT INTEGER := 366; -- SQL data type urban_legend CONSTANT BOOLEAN := FALSE; -- PL/SQL-only data type BEGIN NULL; END; /

Initial Values of Variables and Constants

In a variable declaration, the initial value is optional unless you specify the constraint (for details, see "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 , followed by an expression. The expression can include previously declared constants and previously initialized variables.

Example 2-11 assigns initial values to the constant and variables that it declares. The initial value of depends on the previously declared constant and the previously initialized variable .

Example 2-11 Variable and Constant Declarations with Initial Values

DECLARE hours_worked INTEGER := 40; employee_count INTEGER := 0; pi CONSTANT REAL := 3.14159; radius REAL := 1; area REAL := (pi * radius**2); BEGIN NULL; END; /

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

In Example 2-12, the variable has the initial value , by default. As the example shows (using the "IS [NOT] NULL Operator") is different from zero.

Example 2-12 Variable Initialized to NULL by Default

DECLARE counter INTEGER; -- initial value is NULL by default BEGIN counter := counter + 1; -- NULL + 1 is still NULL IF counter IS NULL THEN DBMS_OUTPUT.PUT_LINE('counter is NULL.'); END IF; END; /

Result:

counter is NULL.

NOT NULL Constraint

You can impose the constraint on a scalar variable or constant (or scalar component of a composite variable or constant). The 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 , either implicitly or explicitly, must assign an initial value to the variable (because the default initial value for a scalar variable is ).

In Example 2-13, the variable acquires the constraint explicitly, and the variables , , and acquire it from their data types.

Example 2-13 Variable Declaration with NOT NULL Constraint

DECLARE acct_id INTEGER(4) NOT NULL := 9999; a NATURALN := 9999; b POSITIVEN := 9999; c SIMPLE_INTEGER := 9999; BEGIN NULL; END; /

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

In Example 2-14, all variables are initialized to .

Example 2-14 Variables Initialized to NULL Values

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

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

%TYPE Attribute

The 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:

referencing_itemreferenced_item%TYPE;

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 constraint, you must specify an initial value for it.

The 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:

variable_nametable_name.column_name%TYPE;

In Example 2-15, the variable inherits the data type and size of the column ., which has a constraint. Because does not inherit the constraint, its declaration does not need an initial value.

Example 2-15 Declaring Variable of Same Type as Column

DECLARE surname employees.last_name%TYPE; BEGIN DBMS_OUTPUT.PUT_LINE('surname=' || surname); END; /

Result:

surname=

In Example 2-16, the variable inherits the data type, size, and constraint of the variable . Because does not inherit the initial value of , its declaration needs an initial value (which cannot exceed 25 characters).

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

DECLARE name VARCHAR(25) NOT NULL := 'Smith'; surname name%TYPE := 'Jones'; BEGIN DBMS_OUTPUT.PUT_LINE('name=' || name); DBMS_OUTPUT.PUT_LINE('surname=' || surname); END; /

Result:

name=Smith surname=Jones

See Also:

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

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:

DECLARE a INTEGER; -- Declaration BEGIN a := 1; -- Reference with simple name END; /

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:

unit_name.simple_identifier_name

For example, if package declares identifier , you can reference the identifier with the qualified name .. 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:

simple_identifier_name@link_to_remote_database

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:

unit_name.simple_identifier_name@link_to_remote_database

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 , explained in Oracle Database SQL Language Reference.

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

Note:

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

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.

Example 2-17 shows the scope and visibility of several identifiers. The first sub-block redeclares the global identifier . To reference the global variable , 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 ; it can reference only its local variable . Because the sub-blocks are at the same level, the first sub-block cannot reference , and the second sub-block cannot reference .

Example 2-17 Scope and Visibility of Identifiers

-- Outer block: DECLARE a CHAR; -- Scope of a (CHAR) begins b REAL; -- Scope of b begins BEGIN -- Visible: a (CHAR), b -- First sub-block: DECLARE a INTEGER; -- Scope of a (INTEGER) begins c REAL; -- Scope of c begins BEGIN -- Visible: a (INTEGER), b, c NULL; END; -- Scopes of a (INTEGER) and c end -- Second sub-block: DECLARE d REAL; -- Scope of d begins BEGIN -- Visible: a (CHAR), b, d NULL; END; -- Scope of d ends -- Visible: a (CHAR), b END; -- Scopes of a (CHAR) and b end /

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

Example 2-18 Qualifying Redeclared Global Identifier with Block Label

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

Result:

Different Birthday

In Example 2-19, the procedure declares a variable, , and a function, . The function redeclares the variable. Then the function references the global variable by qualifying it with the procedure name.

Example 2-19 Qualifying Identifier with Subprogram Name

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

Result:

Credit rating over limit (1). Rating: 3

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 Example 2-20 shows.

Example 2-20 Duplicate Identifiers in Same Scope

DECLARE id BOOLEAN; id VARCHAR2(5); -- duplicate identifier BEGIN id := FALSE; END; /

Result:

id := FALSE; * ERROR at line 5: ORA-06550: line 5, column 3: PLS-00371: at most one declaration for 'ID' is permitted ORA-06550: line 5, column 3: PL/SQL: Statement ignored

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 Example 2-21 shows.

Example 2-21 Declaring Same Identifier in Different Units

DECLARE PROCEDURE p IS x VARCHAR2(1); BEGIN x := 'a'; -- Assign the value 'a' to x DBMS_OUTPUT.PUT_LINE('In procedure p, x = ' || x); END; PROCEDURE q IS x VARCHAR2(1); BEGIN x := 'b'; -- Assign the value 'b' to x DBMS_OUTPUT.PUT_LINE('In procedure q, x = ' || x); END; BEGIN p; q; END; /

Result:

In procedure p, x = a In procedure q, x = b

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

In Example 2-22, is the name of both a block and a subprogram. Both the block and the subprogram declare a variable named . In the subprogram, . refers to the local variable , not to the global variable .

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

<<echo>> DECLARE x NUMBER := 5; PROCEDURE echo AS x NUMBER := 0; BEGIN DBMS_OUTPUT.PUT_LINE('x = ' || x); DBMS_OUTPUT.PUT_LINE('echo.x = ' || echo.x); END; BEGIN echo; END; /

Result:

x = 0 echo.x = 0

Example 2-23 has two labels for the outer block, and . The second label appears again in the inner block. In the inner block, . refers to the local variable , not to the global variable , which results in the error .

Example 2-23 Block with Multiple and Duplicate Labels

<<compute_ratio>><<another_label>> DECLARE numerator NUMBER := 22; denominator NUMBER := 7; BEGIN <<another_label>> DECLARE denominator NUMBER := 0; BEGIN DBMS_OUTPUT.PUT_LINE('Ratio with compute_ratio.denominator = '); DBMS_OUTPUT.PUT_LINE(numerator/compute_ratio.denominator); DBMS_OUTPUT.PUT_LINE('Ratio with another_label.denominator = '); DBMS_OUTPUT.PUT_LINE(numerator/another_label.denominator); 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 another_label; END compute_ratio; /

Result:

Ratio with compute_ratio.denominator = 3.14285714285714285714285714285714285714 Ratio with another_label.denominator =Divide-by-zero error: cannot divide 22 by 0

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 or statement to assign it a value from a table.

  • Pass it to a subprogram as an or 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.

Topics

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:

variable_name := expression;

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

Example 2-24 declares several variables (specifying initial values for some) and then uses assignment statements to assign the values of expressions to them.

Example 2-24 Assigning Values to Variables with Assignment Statement

DECLARE -- You can assign initial values here wages NUMBER; hours_worked NUMBER := 40; hourly_salary NUMBER := 22.50; bonus NUMBER := 150; country VARCHAR2(128); counter NUMBER := 0; done BOOLEAN; valid_id BOOLEAN; emp_rec1 employees%ROWTYPE; emp_rec2 employees%ROWTYPE; TYPE commissions IS TABLE OF NUMBER INDEX BY PLS_INTEGER; comm_tab commissions; BEGIN -- You can assign values here too wages := (hours_worked * hourly_salary) + bonus; country := 'France'; country := UPPER('Canada'); done := (counter > 100); valid_id := TRUE; emp_rec1.first_name := 'Antonio'; emp_rec1.last_name := 'Ortiz'; emp_rec1 := emp_rec2; comm_tab(5) := 20000 * 0.15; END; /

Assigning Values to Variables with the SELECT INTO Statement

A simple form of the statement is:

SELECT select_item [, select_item ]... INTO variable_name [, variable_name ]... FROM table_name;

For each , there must be a corresponding, type-compatible . Because SQL does not have a type, cannot be a variable. For the complete syntax of the statement, see "SELECT INTO Statement".

Example 2-25 uses a statement to assign to the variable the value that is 10% of the salary of the employee whose is 100.

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

DECLARE bonus NUMBER(8,2); BEGIN SELECT salary * 0.10 INTO bonusFROM employeesWHERE employee_id = 100; END; DBMS_OUTPUT.PUT_LINE('bonus = ' || TO_CHAR(bonus)); /

Result:

bonus = 2646

Assigning Values to Variables as Parameters of a Subprogram

If you pass a variable to a subprogram as an or 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 2-26 passes the variable to the procedure . The procedure assigns a value to the corresponding formal parameter, . Because is an parameter, the variable retains the assigned value after the procedure finishes running.

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

DECLARE emp_salary NUMBER(8,2); PROCEDURE adjust_salary ( emp NUMBER, sal IN OUT NUMBER, adjustment NUMBER ) IS BEGIN sal := sal + adjustment; END; BEGIN SELECT salary INTO emp_salary FROM employees WHERE employee_id = 100; DBMS_OUTPUT.PUT_LINE ('Before invoking procedure, emp_salary: ' || emp_salary); adjust_salary (100, emp_salary, 1000); DBMS_OUTPUT.PUT_LINE ('After invoking procedure, emp_salary: ' || emp_salary); END; /

Result:

Before invoking procedure, emp_salary: 24000 After invoking procedure, emp_salary: 25000

Assigning Values to BOOLEAN Variables

The only values that you can assign to a variable are , , and .

Example 2-27 initializes the variable to by default, assigns it the literal value , compares it to the literal value , and assigns it the value of a expression.

Example 2-27 Assigning Value to BOOLEAN Variable

DECLARE done BOOLEAN; -- Initial value is NULL by default counter NUMBER := 0; BEGIN done := FALSE; -- Assign literal value WHILE done != TRUE -- Compare to literal value LOOP counter := counter + 1; done := (counter > 500); -- Assign value of BOOLEAN expression END LOOP; END; /

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

Expressions

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

  1. A single constant or variable (for example, )

  2. A unary operator and its single operand (for example, )

  3. A binary operator and its two operands (for example, )

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.

Topics

Concatenation Operator

The concatenation operator () appends one string operand to another, as Example 2-28 shows.

Example 2-28 Concatenation Operator

DECLARE x VARCHAR2(4) := 'suit';y VARCHAR2(4) := 'case'; BEGIN DBMS_OUTPUT.PUT_LINE (x || y); END; /

Result:

suitcase

The concatenation operator ignores null operands, as Example 2-29 shows.

Example 2-29 Concatenation Operator with NULL Operands

BEGIN DBMS_OUTPUT.PUT_LINE ('apple' || NULL || NULL || 'sauce'); END; /

Result:

applesauce

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

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 2-3 shows operator precedence from highest to lowest. Operators with equal precedence are evaluated in no particular order.

Table 2-3 Operator Precedence

OperatorOperation

exponentiation

,

identity, negation

,

multiplication, division

, ,

addition, subtraction, concatenation

, , , , , , , , , , , ,

comparison

negation

conjunction

inclusion


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

Example 2-30 Controlling Evaluation Order with Parentheses

DECLARE a INTEGER := 1+2**2; b INTEGER := (1+2)**2; BEGIN DBMS_OUTPUT.PUT_LINE('a = ' || TO_CHAR(a)); DBMS_OUTPUT.PUT_LINE('b = ' || TO_CHAR(b)); END; /

Result:

a = 5 b = 9

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

In Example 2-31, 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 2-31 Expression with Nested Parentheses

DECLARE a INTEGER := ((1+2)*(3+4))/7; BEGIN DBMS_OUTPUT.PUT_LINE('a = ' || TO_CHAR(a)); END; /

Result:

a = 3

You can also use parentheses to improve readability, as in Example 2-32, where the parentheses do not affect evaluation order.

Example 2-32 Improving Readability with Parentheses

DECLARE a INTEGER := 2**2*3**2; b INTEGER := (2**2)*(3**2); BEGIN DBMS_OUTPUT.PUT_LINE('a = ' || TO_CHAR(a)); DBMS_OUTPUT.PUT_LINE('b = ' || TO_CHAR(b)); END; /

Result:

a = 36 b = 36

Example 2-33 shows the effect of operator precedence and parentheses in several more complex expressions.

Example 2-33 Operator Precedence

DECLARE salary NUMBER := 60000; commission NUMBER := 0.10; BEGIN -- Division has higher precedence than addition: DBMS_OUTPUT.PUT_LINE('5 + 12 / 4 = ' || TO_CHAR(5 + 12 / 4)); DBMS_OUTPUT.PUT_LINE('12 / 4 + 5 = ' || TO_CHAR(12 / 4 + 5)); -- Parentheses override default operator precedence: DBMS_OUTPUT.PUT_LINE('8 + 6 / 2 = ' || TO_CHAR(8 + 6 / 2)); DBMS_OUTPUT.PUT_LINE('(8 + 6) / 2 = ' || TO_CHAR((8 + 6) / 2)); -- Most deeply nested operation is evaluated first: DBMS_OUTPUT.PUT_LINE('100 + (20 / 5 + (7 - 3)) = ' || TO_CHAR(100 + (20 / 5 + (7 - 3)))); -- Parentheses, even when unnecessary, improve readability: DBMS_OUTPUT.PUT_LINE('(salary * 0.05) + (commission * 0.25) = ' || TO_CHAR((salary * 0.05) + (commission * 0.25)) ); DBMS_OUTPUT.PUT_LINE('salary * 0.05 + commission * 0.25 = ' || TO_CHAR(salary * 0.05 + commission * 0.25) ); END; /

Result:

5 + 12 / 4 = 8 12 / 4 + 5 = 8 8 + 6 / 2 = 11 (8 + 6) / 2 = 7 100 + (20 / 5 + (7 - 3)) = 108 (salary * 0.05) + (commission * 0.25) = 3000.025 salary * 0.05 + commission * 0.25 = 3000.025

Logical Operators

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

Table 2-4 Logical Truth Table

xyx AND yx OR yNOT x

Example 2-34 creates a procedure, , that prints the value of a variable. The procedure uses the "IS [NOT] NULL Operator". Several examples in this chapter invoke .

Example 2-34 Procedure Prints BOOLEAN Variable

CREATE OR REPLACE PROCEDURE print_boolean ( b_name VARCHAR2, b_value BOOLEAN ) IS BEGIN IF b_value IS NULL THEN DBMS_OUTPUT.PUT_LINE (b_name || ' = NULL'); ELSIF b_value = TRUE THEN DBMS_OUTPUT.PUT_LINE (b_name || ' = TRUE'); ELSE DBMS_OUTPUT.PUT_LINE (b_name || ' = FALSE'); END IF; END; /

As Table 2-4 and Example 2-35 show, returns if and only if both operands are .

Example 2-35 AND Operator

DECLARE PROCEDURE print_x_and_y ( x BOOLEAN, y BOOLEAN ) IS BEGIN print_boolean ('x', x); print_boolean ('y', y); print_boolean ('x AND y', x AND y); END print_x_and_y; BEGIN print_x_and_y (FALSE, FALSE); print_x_and_y (TRUE, FALSE); print_x_and_y (FALSE, TRUE); print_x_and_y (TRUE, TRUE); print_x_and_y (TRUE, NULL); print_x_and_y (FALSE, NULL); print_x_and_y (NULL, TRUE); print_x_and_y (NULL, FALSE); END; /

Result:

x = FALSE y = FALSE x AND y = FALSE x = TRUE y = FALSE x AND y = FALSE x = FALSE y = TRUE x AND y = FALSE x = TRUEy = TRUEx AND y = TRUE x = TRUE y = NULL x AND y = NULL x = FALSE y = NULL x AND y = FALSE x = NULL y = TRUE x AND y = NULL x = NULL y = FALSE x AND y = FALSE

As Table 2-4 and Example 2-36 show, returns if either operand is . (Example 2-36 invokes the procedure from Example 2-35.)

Example 2-36 OR Operator

DECLARE PROCEDURE print_x_or_y ( x BOOLEAN, y BOOLEAN ) IS BEGIN print_boolean ('x', x); print_boolean ('y', y); print_boolean ('x OR y', x OR y); END print_x_or_y; BEGIN print_x_or_y (FALSE, FALSE); print_x_or_y (TRUE, FALSE); print_x_or_y (FALSE, TRUE); print_x_or_y (TRUE, TRUE); print_x_or_y (TRUE, NULL); print_x_or_y (FALSE, NULL); print_x_or_y (NULL, TRUE); print_x_or_y (NULL, FALSE); END; /

Result:

x = FALSE y = FALSE x OR y = FALSE x = TRUEy = FALSEx OR y = TRUEx = FALSEy = TRUEx OR y = TRUEx = TRUEy = TRUEx OR y = TRUEx = TRUEy = NULLx OR y = TRUE x = FALSE y = NULL x OR y = NULL x = NULLy = TRUEx OR y = TRUE x = NULL y = FALSE x OR y = NULL

As Table 2-4 and Example 2-37 show, returns the opposite of its operand, unless the operand is . returns , because is an indeterminate value. (Example 2-37 invokes the procedure from Example 2-35.)

Example 2-37 NOT Operator

DECLARE PROCEDURE print_not_x ( x BOOLEAN ) IS BEGIN print_boolean ('x', x); print_boolean ('NOT x', NOT x); END print_not_x; BEGIN print_not_x (TRUE); print_not_x (FALSE); print_not_x (NULL); END; /

Result:

x = TRUE NOT x = FALSE x = FALSE NOT x = TRUE x = NULL NOT x = NULL

In Example 2-38, you might expect the sequence of statements to run because and seem unequal. But, values are indeterminate. Whether equals is unknown. Therefore, the condition yields and the sequence of statements is bypassed.

Example 2-38 NULL Value in Unequal Comparison

DECLARE x NUMBER := 5;y NUMBER := NULL; BEGIN IF x != y THEN -- yields NULL, not TRUE DBMS_OUTPUT.PUT_LINE('x != y'); -- not runELSIF 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; /

Result:

Can't tell if x and y are equal or not.

In Example 2-39, you might expect the sequence of statements to run because and seem equal. But, again, that is unknown, so the condition yields and the sequence of statements is bypassed.

Example 2-39 NULL Value in Equal Comparison

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

Result:

Can't tell if two NULLs are equal

In Example 2-40, the two statements appear to be equivalent. However, if either or is , then the first statement assigns the value of to and the second statement assigns the value of to .

Example 2-40 NOT NULL Equals NULL

DECLARE x INTEGER := 2; Y INTEGER := 5; high INTEGER; BEGIN IF (x > y) -- If x or y is NULL, then (x > y) is NULL THEN high := x; -- run if (x > y) is TRUE ELSE high := y; -- run if (x > y) is FALSE or NULL END IF; IF NOT (x > y) -- If x or y is NULL, then NOT (x > y) is NULL THEN high := y; -- run if NOT (x > y) is TRUE ELSE high := x; -- run if NOT (x > y) is FALSE or NULL END IF; END; /

Example 2-41 invokes the procedure from Example 2-35 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.

Example 2-41 Changing Evaluation Order of Logical Operators

DECLARE x BOOLEAN := FALSE; y BOOLEAN := FALSE; BEGIN print_boolean ('NOT x AND y', NOT x AND y); print_boolean ('NOT (x AND y)', NOT (x AND y)); print_boolean ('(NOT x) AND y', (NOT x) AND y); END; /

Result:

NOT x AND y = FALSE NOT (x AND y) = TRUE (NOT x) AND y = FALSE

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 2-42, short-circuit evaluation prevents the expression from causing a divide-by-zero error. When the value of is zero, the value of the left operand is , 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.

Example 2-42 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; /

Result:

On hand quantity is zero.

Comparison Operators

Comparison operators compare one expression to another. The result is always either , , or . If the value of one expression is , then the result of the comparison is also .

The comparison operators are:

Note:

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.

Note:

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

IS [NOT] NULL Operator

The operator returns the value