What are Lexical Units in a PL/SQL Block

Lexical Units in a PL/SQL Block

Lexical units include letters, numerals, special characters, tabs, spaces, returns, and symbols.

Identifiers

Identifiers are the names given to PL/SQL objects. You learned to identify valid and invalid identifiers. Recall that keywords cannot be used as identifiers.

Quoted identifiers:

• Make identifiers case-sensitive
• Include characters such as spaces
• Use reserved words

Examples:

begin date" DATE;
"end date" DATE;
"exception thrown" BOOLEAN DEFAULT TRUE;

All subsequent usage of these variables should have double quotation marks. However,use of quoted identifiers is not recommended.

Delimiters:

Delimiters are symbols that have special meaning. You already learned that the semicolon (;) is used to terminate a SQL or PL/SQL statement. Therefore, ; is an example of a delimiter.

Delimiters are simple or compound symbols that have special meaning in PL/SQL.

Simple symbols

Compound symbols

Literals

Any value that is assigned to a variable is a literal. Any character, numeral,Boolean, or date value that is not an identifier is a literal. Literals are classified as:

• Character literals: All string literals have the data type CHAR or VARCHAR2 and are, therefore, called character literals (for example, John, and 12c).
• Numeric literals: A numeric literal represents an integer or real value (for example, 428 and 1.276).
• Boolean literals: Values that are assigned to Boolean variables are Boolean literals. TRUE, FALSE, and NULL are Boolean literals or keywords.

Comments:

It is good programming practice to explain what a piece of code is trying to achieve. However, when you include the explanation in a PL/SQL block, the compiler cannot interpret these instructions. Therefore, there should be a way in which you can indicate that these instructions need not be compiled. Comments are mainly used for this purpose. Any instruction that is commented is not interpreted by the compiler.

• Two hyphens (–) are used to comment a single line.
• The beginning and ending comment delimiters (/* and */) are used to comment multiple lines.

PL/SQL Block Syntax and Guidelines

Using Literals

A literal is an explicit numeric, character string, date, or Boolean value that is not represented by an identifier.

• Character literals include all printable characters in the PL/SQL character set: letters,numerals, spaces, and special symbols.
• Numeric literals can be represented either by a simple value (for example, –32.5) or in scientific notation (for example, 2E5 means 2 * 105 = 200,000).

Formatting Code

In a PL/SQL block, a SQL statement can span several lines. You can format an unformatted SQL statement by using the SQL Worksheet shortcut menu. Right-click the active SQL Worksheet and, in the shortcut menu that appears, select the Format option.

Commenting Code

You should comment code to document each phase and to assist debugging. In PL/SQL code:

• A single-line comment is commonly prefixed with two hyphens (–)
• You can also enclose a comment between the symbols /* and */

Example:

DECLARE
...
v_annual_sal NUMBER (9,2);
BEGIN
/* Compute the annual salary based on the
    monthly salary input from the user */
v_annual_sal := monthly_sal * 12;
--The following line displays the annual salary
DBMS_OUTPUT.PUT_LINE(v_annual_sal);
END;
/

Comments are strictly informational and do not enforce any conditions or behavior on the logic or data. Well-placed comments are extremely valuable for code readability and future code maintenance.

SQL Functions in PL/SQL

SQL provides several predefined functions that can be used in SQL statements. Most of these functions (such as single-row number and character functions, data type conversion functions, and date and time-stamp functions) are valid in PL/SQL expressions.

The following functions are not available in procedural statements:

• DECODE
• Group functions: AVG, MIN, MAX, COUNT, SUM, STDDEV, and VARIANCE Group functions apply to groups of rows in a table and are, therefore, available only in SQL statements in a PL/SQL block. The functions mentioned here are only a subset of the complete list.

SQL Functions in PL/SQL: Examples

You can use SQL functions to manipulate data. These functions are grouped into the following categories:

• Number
• Character
• Conversion
• Date
• Miscellaneous

Examples

1. Get the length of a string:

v_desc_size INTEGER(5);
v_prod_description VARCHAR2(70):='You can use this
product with your radios for higher frequency';

-- get the length of the string in prod_description
v_desc_size:= LENGTH(v_prod_description);

2. Get the number of months an employee has worked:

v_tenure:= MONTHS_BETWEEN (CURRENT_DATE, v_hiredate);

Using Sequences in PL/SQL Expressions

In Oracle Database 11g and later, you can use the NEXTVAL and CURRVAL pseudocolumns in any PL/SQL context, where an expression of the NUMBER data type may legally appear.Although the old style of using a SELECT statement to query a sequence is still valid, it is recommended that you do not use it.

Before Oracle Database 11g, you were forced to write a SQL statement in order to use a sequence object value in a PL/SQL subroutine. Typically, you would write a SELECT statement to reference the pseudocolumns of NEXTVAL and CURRVAL to obtain a sequence number. This method created a usability problem.

In Oracle Database 11g and later, the limitation of forcing you to write a SQL statement to retrieve a sequence value is eliminated. With the sequence enhancement feature:

• Sequence usability is improved
• The developer has to type less
• The resulting code is clearer

Starting in 11g:

DECLARE
    v_new_id NUMBER;
BEGIN
    v_new_id := my_seq.NEXTVAL;
END;
/

Before 11g:

DECLARE
    v_new_id NUMBER;
BEGIN
    SELECT my_seq.NEXTVAL INTO v_new_id FROM Dual;
END;
/

Data Type Conversion

In any programming language, converting one data type to another is a common requirement.PL/SQL can handle such conversions with scalar data types. Data type conversions can be of two types:

Implicit conversions

PL/SQL attempts to convert data types dynamically if they are mixed in a statement. Consider the following example:

DECLARE
    v_salary NUMBER(6):=6000;
    v_sal_hike VARCHAR2(5):='1000';
    v_total_salary v_salary%TYPE;
BEGIN
    v_total_salary:=v_salary + v_sal_hike;
    DBMS_OUTPUT.PUT_LINE(v_total_salary);
END;
/

In this example, the sal_hike variable is of the VARCHAR2 type. When calculating the total salary, PL/SQL first converts sal_hike to NUMBER, and then performs the operation. The result is of the NUMBER type.

Implicit conversions can be between:

• Characters and numbers
• Characters and dates

Explicit conversions

To convert values from one data type to another, use built-in functions.For example, to convert a CHAR value to a DATE or NUMBER value, use TO_DATE or TO_NUMBER, respectively.

Examples

-- implicit data type conversion
v_date_of_joining DATE:= '02-Feb-2000';

-- error in data type conversion
v_date_of_joining DATE:= 'February 02,2000';

-- explicit data type conversion
v_date_of_joining DATE:= TO_DATE('February
02,2000','Month DD, YYYY');

Note the three examples of implicit and explicit conversions of the DATE data type in the examples above:
1. Because the string literal being assigned to date_of_joining is in the default format, this example performs implicit conversion and assigns the specified date to date_of_joining.
2. The PL/SQL returns an error because the date that is being assigned is not in the default format.
3. The TO_DATE function is used to explicitly convert the given date in a particular format and assign it to the DATE data type variable date_of_joining.