Comparison operators cannot be used to place restrictions on character-based attributes.

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 outer. Therefore, after the sub-block redeclares the global variable birthdate, it can reference that global variable by qualifying its name with the block label. The sub-block can also reference its local variable birthdate, by its simple name.

Example 2-18 Qualifying Redeclared Global Identifier with Block Label

  -- 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 check_credit declares a variable, rating, and a function, check_rating. The function redeclares the variable. Then the function references the global variable by qualifying it with the procedure name.

Example 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  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 SELECT INTO statement is:

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

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

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

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

DECLARE
  bonus   NUMBER[8,2];
BEGIN
  SELECT salary * 0.10 INTO bonus
  FROM employees
  WHERE 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 OUT or IN OUT parameter, and the subprogram assigns a value to the parameter, the variable retains that value after the subprogram finishes running. For more information, see "Subprogram Parameters".

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

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 BOOLEAN variable are TRUE, FALSE, and NULL.

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

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 BOOLEAN 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, a]

2. A unary operator and its single operand [for example, -a]

3. A binary operator and its two operands [for example, a+b]

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

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

Topics

• Concatenation Operator

• Operator Precedence

• Logical Operators

• Short-Circuit Evaluation

• Comparison Operators

• BOOLEAN Expressions

• CASE Expressions

• SQL Functions in PL/SQL Expressions

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

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, OR, and NOT follow the tri-state logic shown in Table 2-4. AND and OR are binary operators; NOT is a unary operator.

Table 2-4 Logical Truth Table

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

Example 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, AND returns TRUE if and only if both operands are TRUE.

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 = TRUE
y = TRUE
x 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, OR returns TRUE if either operand is TRUE. [Example 2-36 invokes the print_boolean 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 = TRUE
y = FALSE
x OR y = TRUE
x = FALSE
y = TRUE
x OR y = TRUE
x = TRUE
y = TRUE
x OR y = TRUE
x = TRUE
y = NULL
x OR y = TRUE
x = FALSE
y = NULL
x OR y = NULL
x = NULL
y = TRUE
x OR y = TRUE
x = NULL
y = FALSE
x OR y = NULL

As Table 2-4 and Example 2-37 show, NOT returns the opposite of its operand, unless the operand is NULL. NOT NULL returns NULL, because NULL is an indeterminate value. [Example 2-37 invokes the print_boolean 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 x and y seem unequal. But, NULL values are indeterminate. Whether x equals y is unknown. Therefore, the IF condition yields NULL and the sequence of statements is bypassed.

Example 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 run
  ELSIF x = y THEN -- also yields NULL
    DBMS_OUTPUT.PUT_LINE['x = y'];
  ELSE
    DBMS_OUTPUT.PUT_LINE
      ['Can''t tell if x and y are equal or not.'];
  END IF;
END;
/
 

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 a and b seem equal. But, again, that is unknown, so the IF condition yields NULL 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 run
  ELSIF 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 IF statements appear to be equivalent. However, if either x or y is NULL, then the first IF statement assigns the value of y to high and the second IF statement assigns the value of x to high.

Example 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 print_boolean 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 OR expression from causing a divide-by-zero error. When the value of on_hand is zero, the value of the left operand is TRUE, so PL/SQL does not evaluate the right operand. If PL/SQL evaluated both operands before applying the OR operator, the right operand would cause a division by zero error.

Example 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 TRUE, FALSE, or NULL. If the value of one expression is NULL, then the result of the comparison is also NULL.

The comparison operators are:

• IS [NOT] NULL Operator

• Relational Operators

• LIKE Operator

• BETWEEN Operator

• IN Operator

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 CLOB 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 IS NULL operator returns the BOOLEAN value TRUE if its operand is NULL or FALSE if it is not NULL. The IS NOT NULL operator does the opposite. Comparisons involving NULL values always yield NULL.

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

• Example 2-12

• Example 2-34

• Example 2-53

Relational Operators

Table 2-5 summarizes the relational operators.

Table 2-5 Relational Operators

Topics

• Arithmetic Comparisons

• BOOLEAN Comparisons

• Character Comparisons

• Date Comparisons

Arithmetic Comparisons

One number is greater than another if it represents a larger quantity. Real numbers are stored as approximate values, so Oracle recommends comparing them for equality or inequality.

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

Example 2-43 Relational Operators in Expressions

BEGIN
  print_boolean ['[2 + 2 =  4]', 2 + 2 = 4];
  
  print_boolean ['[2 + 2  4]', 2 + 2  4];
  print_boolean ['[2 + 2 != 4]', 2 + 2 != 4];
  print_boolean ['[2 + 2 ~= 4]', 2 + 2 ~= 4];
  print_boolean ['[2 + 2 ^= 4]', 2 + 2 ^= 4];
  
  print_boolean ['[1 < 2]', 1  2];
 
  print_boolean ['[1 = 1]', 1 >= 1];
END;
/
 

Result:

[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 = 1] = TRUE

BOOLEAN Comparisons

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

Character Comparisons

By default, one character is greater than another if its binary value is larger. For example, this expression is true:

'y' > 'r'

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

'Kathy' > 'Kathryn'

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

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

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

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

'a' = 'A'
'Alpha' = 'ALPHA'

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

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

'Cooperate' = 'Co-Operate'
'Co-Operate' = 'coöperate'

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

Semantic differences between the CHAR and VARCHAR2 data types affect character comparisons. For more information, see "Value Comparisons".

Date Comparisons

One date is greater than another if it is more recent. For example, this expression is true:

'01-JAN-91' > '31-DEC-90'

LIKE Operator

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

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 'Johnson' matches the pattern 'J%s_n' but not 'J%S_N', as Example 2-44 shows.

Example 2-44 LIKE Operator in Expression

DECLARE
  PROCEDURE compare [
    value   VARCHAR2,
    pattern VARCHAR2
  ] IS
  BEGIN
    IF value LIKE pattern THEN
      DBMS_OUTPUT.PUT_LINE ['TRUE'];
    ELSE
      DBMS_OUTPUT.PUT_LINE ['FALSE'];
    END IF;
  END;
BEGIN
  compare['Johnson', 'J%s_n'];
  compare['Johnson', 'J%S_N'];
END;
/
 

Result:

TRUE
FALSE

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

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

Example 2-45 Escape Character in Pattern

DECLARE
  PROCEDURE half_off [sale_sign VARCHAR2] IS
  BEGIN
    IF sale_sign LIKE '50\% off!' ESCAPE '\' THEN
      DBMS_OUTPUT.PUT_LINE ['TRUE'];
    ELSE
      DBMS_OUTPUT.PUT_LINE ['FALSE'];
    END IF;
  END;
BEGIN
  half_off['Going out of business!'];
  half_off['50% off!'];
END;
/
 

Result:

FALSE
TRUE

See Also:

• Oracle Database SQL Language Reference for more information about LIKE

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

BETWEEN Operator

The BETWEEN operator tests whether a value lies in a specified range. x BETWEEN a AND b returns the same value as [x>=a] AND [x CREATE OR REPLACE PROCEDURE p 2 IS 3 i PLS_INTEGER; 4 BEGIN 5 DBMS_OUTPUT.PUT_LINE['Inside p']; 6 i := $$PLSQL_LINE; 7 DBMS_OUTPUT.PUT_LINE['i = ' || i]; 8 DBMS_OUTPUT.PUT_LINE['$$PLSQL_LINE = ' || $$PLSQL_LINE]; 9 DBMS_OUTPUT.PUT_LINE['$$PLSQL_UNIT = ' || $$PLSQL_UNIT]; 10 END; 11 / Procedure created. SQL> BEGIN 2 p; 3 DBMS_OUTPUT.PUT_LINE['Outside p']; 4 DBMS_OUTPUT.PUT_LINE['$$PLSQL_UNIT = ' || $$PLSQL_UNIT]; 5 END; 6 /

Result:

Inside p
i = 6
$$PLSQL_LINE = 8
$$PLSQL_UNIT = P
Outside p
$$PLSQL_UNIT =
 
PL/SQL procedure successfully completed.

Example 2-55 displays the current values of PL/SQL the compilation parameters.

Example 2-55 Displaying Values of PL/SQL Compilation Parameters

BEGIN
  DBMS_OUTPUT.PUT_LINE['$$PLSCOPE_SETTINGS = '     || $$PLSCOPE_SETTINGS];
  DBMS_OUTPUT.PUT_LINE['$$PLSQL_CCFLAGS = '        || $$PLSQL_CCFLAGS];
  DBMS_OUTPUT.PUT_LINE['$$PLSQL_CODE_TYPE = '      || $$PLSQL_CODE_TYPE];
  DBMS_OUTPUT.PUT_LINE['$$PLSQL_OPTIMIZE_LEVEL = ' || $$PLSQL_OPTIMIZE_LEVEL];
  DBMS_OUTPUT.PUT_LINE['$$PLSQL_WARNINGS = '       || $$PLSQL_WARNINGS];
  DBMS_OUTPUT.PUT_LINE['$$NLS_LENGTH_SEMANTICS = ' || $$NLS_LENGTH_SEMANTICS];
END;
/

Result:

$$PLSCOPE_SETTINGS =
$$PLSQL_CCFLAGS = 99
$$PLSQL_CODE_TYPE = INTERPRETED
$$PLSQL_OPTIMIZE_LEVEL = 2
$$PLSQL_WARNINGS = ENABLE:ALL
$$NLS_LENGTH_SEMANTICS = BYTE

Note:

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

Assigning Values to Inquiry Directives

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

ALTER SESSION SET PLSQL_CCFLAGS = 
  'name1:value1, name2:value2, ... namen:valuen'

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

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

ALTER SESSION SET PLSQL_CCFLAGS = 'flag:TRUE, flag:5'

Oracle recommends against using PLSQL_CCFLAGS to assign values to predefined inquiry directives, including compilation parameters. To assign values to compilation parameters, Oracle recommends using the ALTER SESSION statement. For more information about the ALTER SESSION statement, see Oracle Database SQL Language Reference.

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

Example 2-56 PLSQL_CCFLAGS Assigns Value to Itself

ALTER SESSION SET
PLSQL_CCFlags = 'Some_Flag:1, Some_Flag:2, PLSQL_CCFlags:99'
/
BEGIN
  DBMS_OUTPUT.PUT_LINE[$$Some_Flag];
  DBMS_OUTPUT.PUT_LINE[$$PLSQL_CCFlags];
END;
/

Result:

2
99

Note:

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

• BOOLEAN literals [TRUE, FALSE, or NULL]

• BOOLEAN static constants

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

• Where x and y are PLS_INTEGER static expressions:

  • x > y

  • x = y

  • x y

  • x >= y

  • x = y

  • x