Different Types Of Constraints In Pl/Sql What Is The Assignment Operator

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

Constant and Variable Declaration

You can declare constants and variables in the declarative part of any PL/SQL block, subprogram, or package. Declarations allocate storage for a value, specify its datatype, and specify a name that you can reference. Declarations can also assign an initial value and impose the constraint. For more information, see Declarations.

Syntax

variable declaration ::=


Description of the illustration variable_declaration.gif

datatype ::=


Description of the illustration variable_datatype.gif

constant ::=


Description of the illustration constant_declaration.gif

Keyword and Parameter Description

collection_name

A collection (associative array, nested table, or varray) previously declared within the current scope.

collection_type_name

A user-defined collection type defined using the datatype specifier or .

CONSTANT

Denotes the declaration of a constant. You must initialize a constant in its declaration. Once initialized, the value of a constant cannot be changed.

constant_name

A program constant. For naming conventions, see "Identifiers".

cursor_name

An explicit cursor previously declared within the current scope.

cursor_variable_name

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

db_table_name

A database table or view that must be accessible when the declaration is elaborated.

db_table_name.column_name

A database table and column that must be accessible when the declaration is elaborated.

expression

A combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. When the declaration is elaborated, the value of is assigned to the constant or variable. The value and the constant or variable must have compatible datatypes.

NOT NULL

A constraint that prevents the program from assigning a null value to a variable or constant. Assigning a null to a variable defined as raises the predefined exception . The constraint must be followed by an initialization clause.

object_name

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

record_name

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

record_name.field_name

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

record_type_name

A user-defined record type that is defined using the datatype specifier .

ref_cursor_type_name

A user-defined cursor variable type, defined using the datatype specifier .

%ROWTYPE

Represents a record that can hold a row from a database table or a cursor. Fields in the record have the same names and datatypes as columns in the row.

scalar_datatype_name

A predefined scalar datatype such as , , or . Includes any qualifiers for size, precision, or character versus byte semantics.

%TYPE

Represents the datatype of a previously declared collection, cursor variable, field, object, record, database column, or variable.

variable_name

A program variable.

Usage Notes

Constants and variables are initialized every time a block or subprogram is entered. By default, variables are initialized to . Whether public or private, constants and variables declared in a package spec are initialized only once for each session.

An initialization clause is required when declaring variables and when declaring constants. If you use to declare a variable, initialization is not allowed.

You can define constants of complex types that have no literal values or predefined constructors, by calling a function that returns a filled-in value. For example, you can make a constant associative array this way.

Examples

For examples, see the following:


Example 1-1, "Declaring Variables in PL/SQL"
Example 1-2, "Assigning Values to Variables With the Assignment Operator"
Example 1-3, "Assigning Values to Variables by SELECTing INTO"
Example 2-9, "Using the %ROWTYPE Attribute"
Example 13-1, "Declaring and Assigning Values to Variables"

Related Topics


"Declaring Variables"
"Declarations"
"Overview of Predefined PL/SQL Datatypes"
"Assignment Statement"
"Expression Definition"
"%ROWTYPE Attribute"
"%TYPE Attribute"

Comments

Leave a Reply

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