2 Fundamentals of the PL/SQL Language (2024)

Delimiters

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

Identifiers

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

Xt2phone#credit_limitLastNameoracle$number

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

mine&yours -- not allowed because of ampersanddebit-amount -- not allowed because of hyphenon/off -- not allowed because of slashuser id -- not allowed because of space

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

money$$$treeSN## try_again_

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

lastnameLastName -- same as lastnameLASTNAME -- same as lastname and LastName

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

lastnamelast_name

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

DECLARE -- end BOOLEAN; -- not allowed; causes compilation error end_of_game BOOLEAN; -- allowedBEGIN NULL;END;/

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

Literals

A literal is an explicit numeric, character, string, or Boolean value not represented by an identifier. The numeric literal 147 and the Boolean literal FALSE are examples.

030 6 -14 0 +32767

6.6667 0.0 -12.0 3.14159 +8300.00 .5 25.

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

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

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

DECLARE n NUMBER;BEGIN n := 10E127; -- causes a 'numeric overflow or underflow' errorEND;/

DECLARE x BINARY_FLOAT := sqrt(2.0f); -- Single-precision floating-point number y BINARY_DOUBLE := sqrt(2.0d); -- Double-precision floating-point numberBEGIN NULL;END;/

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

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

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

-- q'!...!' notation lets us use single quotes inside the literal.string_var := q'!I'm a string, you're a string.!';-- To use delimiters [, {, <, and (, pair them with ], }, >, and ).-- Here we pass a string literal representing a SQL statement-- to a subprogram, without doubling the quotation marks around-- 'INVALID'.func_call(q'[select index_name from user_indexes where status = 'INVALID']');-- For NCHAR and NVARCHAR2 literals, use the prefix nq instead of q.where_clause := nq'#where col_value like '%é'#';

'baker''Baker'

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

Comments

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

DECLARE howmany NUMBER;BEGIN-- begin processing SELECT count(*) INTO howmany FROM user_objects WHERE object_type = 'TABLE'; -- Check number of tables howmany := howmany * 2; -- Compute some other valueEND;/

-- DELETE FROM employees WHERE comm_pct IS NULL;

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 done */ END IF;/* The following line computes the area of a circle using pi, which is the ratio between the circumference and diameter.*/ area := pi * radius**2;END;/

/*LOOP FETCH c1 INTO emp_rec; EXIT WHEN c1%NOTFOUND; ...END LOOP;*/

Using DEFAULT

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

blood_type CHAR := 'O';

can be rewritten as follows:

blood_type CHAR DEFAULT 'O';

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

hours_worked INTEGER DEFAULT 40;employee_count INTEGER := 0;

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

Using NOT NULL

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

DECLARE acct_id INTEGER(4) NOT NULL := 9999;

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

The NOT NULL constraint must be followed by an initialization clause.

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

Using the %TYPE Attribute

The %TYPE attribute provides the datatype of a variable or database column. In the following example, %TYPE provides the datatype of a variable:

DECLARE credit NUMBER(7,2); debit credit%TYPE; name VARCHAR2(20) := 'JoHn SmItH';-- If we increase the length of NAME, the other variables-- become longer too. upper_name name%TYPE := UPPER(name); lower_name name%TYPE := LOWER(name); init_name name%TYPE := INITCAP(name);BEGIN NULL;END;/

Variables declared using %TYPE are treated like those declared using a datatype specifier. For example, given the previous declarations, PL/SQL treats debit like a REAL(7,2) variable. A %TYPE declaration can also include an initialization clause.

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

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

When you use table_name.column_name.TYPE to declare a variable, you do not need to know the actual datatype, and attributes such as precision, scale, and length. If the database definition of the column changes, the datatype of the variable changes accordingly at run time.

%TYPE variables do not inherit the NOT NULL column constraint. In the next example, even though the database column employee_id is defined as NOT NULL, you can assign a null to the variable my_empno:

DECLARE my_empno employees.employee_id%TYPE;BEGIN my_empno := NULL; -- this worksEND;/

Using the %ROWTYPE Attribute

The %ROWTYPE attribute provides a record type that represents a row in a table (or view). The record can store an entire row of data selected from the table, or fetched from a cursor or strongly typed cursor variable:

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

Columns in a row and corresponding fields in a record have the same names and datatypes. However, fields in a %ROWTYPE record do not inherit the NOT NULL column constraint.

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

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

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

Restrictions on Declarations

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

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

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

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

Figure 2-1 Scope and Visibility

Description of the illustration lnpls007.gif

Assigning Boolean Values

Only the values TRUE, FALSE, and NULL can be assigned to a Boolean variable. You can assign these literal values, or expressions such as comparisons using relational operators.

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

Assigning a SQL Query Result to a PL/SQL Variable

You can use the SELECT statement to have Oracle assign values to a variable. For each item in the select list, there must be a corresponding, type-compatible variable in the INTO list. For example:

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

Because SQL does not have a Boolean type, you cannot select column values into a Boolean variable.

Table 2-1 Order of Operations

Logical Operators

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

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

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

Order of Evaluation

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

NOT (valid AND done) | NOT valid AND done

If the Boolean variables valid and done have the value FALSE, the first expression yields TRUE. However, the second expression yields FALSE because NOT has a higher precedence than AND. Therefore, the second expression is equivalent to:

(NOT valid) AND done

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

valid AND done

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

valid OR done

Short-Circuit Evaluation

When evaluating a logical expression, PL/SQL uses short-circuit evaluation. That is, PL/SQL stops evaluating the expression as soon as the result can be determined. This lets you write expressions that might otherwise cause an error. Consider the following OR expression:

DECLARE on_hand INTEGER := 0; on_order INTEGER := 100;BEGIN-- Does not cause divide-by-zero error; evaluation stops after 1st expr. IF (on_hand = 0) OR ((on_order / on_hand) < 5) THEN dbms_output.put_line('There are no more widgets left!'); END IF;END;/

When the value of on_hand is zero, the left operand yields 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.

Comparison Operators

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

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

Relational Operators

IS NULL Operator

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

IF variable IS NULL THEN ...

LIKE Operator

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

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

ename LIKE 'J%S_N'

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

IF sale_sign LIKE '50\% off!' ESCAPE '\' THEN...

BETWEEN Operator

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

45 BETWEEN 38 AND 44

IN Operator

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

letter IN ('a','b','c')

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

value NOT IN set

yield FALSE if the set contains a null.

Concatenation Operator

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

'suit' || 'case'

returns the following value:

'suitcase'

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

Boolean Expressions

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

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

Boolean Arithmetic Expressions

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

number1 := 75;number2 := 70;

the following expression is true:

number1 > number2

Boolean Character Expressions

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

For example, given the assignments

string1 := 'Kathy';string2 := 'Kathleen';

the following expression is true:

string1 > string2

By setting the initialization parameter NLS_COMP=ANSI, you can make 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.

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

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

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

Boolean Date Expressions

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

date1 := '01-JAN-91';date2 := '31-DEC-90';

the following expression is true:

date1 > date2

Guidelines for PL/SQL Boolean Expressions

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

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

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

  100 < tax < 500 -- not allowed

  The debugged version follows:

  (100 < tax) AND (tax < 500)

• A Boolean variable is itself either true or false. You can just use the variable in a conditional test, rather than comparing it to the literal values TRUE and FALSE. For example, the following loops are all equivalent:

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

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

CASE Expressions

A CASE expression selects a result from one or more alternatives, and returns the result. Although it contains a block that might stretch over several lines, it really is an expression that forms part of a larger statement, such as an assignment or a procedure call.

The CASE expression uses a selector, an expression whose value determines which alternative to return. A CASE expression has the following form:

CASE selector WHEN expression1 THEN result1 WHEN expression2 THEN result2 ... WHEN expressionN THEN resultN [ELSE resultN+1]END

The selector is followed by one or more WHEN clauses, which are checked sequentially. The value of the selector determines which clause is evaluated. The first WHEN clause that matches the value of the selector determines the result value, and subsequent WHEN clauses are not evaluated. For example:

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

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

An alternative to the CASE expression is the CASE statement, where each WHEN clause can be an entire PL/SQL block. For details, see "Using the CASE Statement".

CASE WHEN search_condition1 THEN result1 WHEN search_condition2 THEN result2 ... WHEN search_conditionN THEN resultN [ELSE resultN+1]END;

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

Handling Null Values in Comparisons and Conditional Statements

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

• Comparisons involving nulls always yield NULL

• Applying the logical operator NOT to a null yields NULL

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

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

In the example below, you might expect the sequence of statements to execute because x and y seem unequal. But, nulls are indeterminate. Whether or not x is equal to y is unknown. Therefore, the IF condition yields NULL and the sequence of statements is bypassed.

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

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

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

IF x > y THEN | IF NOT x > y THEN high := x; | high := y;ELSE | ELSE high := y; | high := x;END IF; | END IF;

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;/

IF my_string IS NULL THEN ...

'apple' || NULL || NULL || 'sauce'

'applesauce'

DECLARE the_manager VARCHAR2(40); name employees.last_name%TYPE;BEGIN-- NULL is a valid argument to DECODE. In this case, manager_id is null-- and the DECODE function returns 'nobody'. SELECT DECODE(manager_id, NULL, 'nobody', 'somebody'), last_name INTO the_manager, name FROM employees WHERE employee_id = 100; dbms_output.put_line(name || ' is managed by ' || the_manager);END;/

DECLARE the_manager employees.manager_id%TYPE; name employees.last_name%TYPE;BEGIN-- NULL is a valid argument to NVL. In this case, manager_id is null-- and the NVL function returns -1. SELECT NVL(manager_id, -1), last_name INTO the_manager, name FROM employees WHERE employee_id = 100; dbms_output.put_line(name || ' is managed by employee #' || the_manager);END;/

DECLARE string_type VARCHAR2(60); old_string string_type%TYPE := 'Apples and oranges'; my_string string_type%TYPE := 'more apples';-- NULL is a valid argument to REPLACE, but does not match-- anything so no replacement is done. new_string string_type%TYPE := REPLACE(old_string, NULL, my_string);BEGIN dbms_output.put_line('Old string = ' || old_string); dbms_output.put_line('New string = ' || new_string);END;/

DECLARE string_type VARCHAR2(60); dashed string_type%TYPE := 'Gold-i-locks';-- When the substitution text for REPLACE is NULL,-- the text being replaced is deleted. name string_type%TYPE := REPLACE(dashed, '-', NULL);BEGIN dbms_output.put_line('Dashed name = ' || dashed); dbms_output.put_line('Dashes removed = ' || name);END;/

Table 2-3 Built-In Functions