Lesson 8

This module discussed how to write your first PL/SQL block, nested blocks, identifiers, and literals that help you build the program logic.
In this module, you also discovered the various datatype conversion functions as well as the use of operators in PL/SQL. Remember, it is extremely important to indent and comment your code.
In this module, you learned how to:

1. Describe the basic structure of a PL/SQL block
2. Define the scope of variables within nested blocks
3. Place identifiers and use literals within a PL/SQL block
4. Add comments to a PL/SQL block
5. Identify implicit and explicit datatype conversions within PL/SQL
6. Use operators within PL/SQL

In this module, you were introduced to the following glossary terms:

1. Block:The basic unit of a PL/SQL source program is the block, which groups related declarations and statements. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handling part. Only the executable part is required. A block can have a label.
2. Datatype: The PL/SQL data types include the SQL data types.
3. Identifier: An identifier is a name for a PL/SQL object such as constant, variable, exception, package, procedure, function.
4. Literal: Aliteral is a value that is neither represented by an identifier nor calculated from other values. For example, 123 is an integer literal and 'abc' is a character literal, but 1+2 is not a literal.
5. Operator: A symbol used as a function, with infix syntax if it has two arguments (e.g. "+") or prefix syntax if it has only one (e.g. Boolean NOT).
6. Reserved word: You should not use them to name program objects such as constants, variables, cursors, schema objects such as columns, tables, or indexes.
7. Subprogram:A PL/SQL subprogram is a named PL/SQL block that can be invoked repeatedly. If the subprogram has parameters, their values can differ for each invocation. A subprogram is either a procedure or a function. Typically, you use a procedure to perform an action and a function to compute and return a value.
8. Truth table:A table listing all possible combinations of inputs and the corresponding output of a Boolean function such as AND, OR, NOT, IMPLIES, XOR, NAND, NOR. Truth tables can be used as a means of representing a function.

In the next module, you will learn to add control statements to a PL/SQL block.

The regular expression SQL functions REGEXP_INSTR and REGEXP_SUBSTR have increased functionality. A new regular expression SQL function, REGEXP_COUNT, returns the number of times a pattern appears in a string. These functions act the same in SQL and PL/SQL.

The

1. SIMPLE_INTEGER,
2. SIMPLE_FLOAT, and
3. SIMPLE_DOUBLE

data types are predefined subtypes of PLS_INTEGER, BINARY_FLOAT, and BINARY_DOUBLE, respectively. Each subtype has the same range as its base type and has a NOT NULL constraint. SIMPLE_INTEGER differs significantly from PLS_INTEGER in its overflow semantics, but SIMPLE_FLOAT and SIMPLE_DOUBLE are identical to their base types, except for their NOT NULL constraint.
You can use SIMPLE_INTEGER when the value will never be NULL and overflow checking is unnecessary. You can use SIMPLE_FLOAT and SIMPLE_DOUBLE when the value will never be NULL. Without the overhead of checking for nullness and overflow, these subtypes provide significantly better performance than their base types when PLSQL_CODE_TYPE='NATIVE', because arithmetic operations on SIMPLE_INTEGER values are done directly in the hardware. When PLSQL_CODE_TYPE='INTERPRETED', the performance improvement is smaller.

It is better to use regular expressions, or any other WHERE clause, in SQL because the database server has more power than the client. One should let the database server manage the planning, filtering, and sorting for your client.
The database server handles SQL "WHERE clauses" using query plans to filter the valid rows. A regular expression comparison is similar to a SQL "WHERE clause". In practice, there is one centralized database server and many clients. The cost of transporting more rows and computing the regular expression locally means the work is multiplied by (1 to n) clients.

Click the Quiz link below to take a multiple-choice quiz about the material we covered in this module.
PL/ SQL DataTypes - Quiz