PL/SQL Block Conclusion
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:
- Describe the basic structure of a PL/SQL block
- Define the scope of variables within nested blocks
Place identifiers and use literals within a PL/SQL block
Add comments to a PL/SQL block
Identify implicit and explicit datatype conversions within PL/SQL
Use operators within PL/SQL
PL/SQL Block Glossary
In this module, you were introduced to the following glossary terms:
- 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.
- Datatype: The PL/SQL data types include the SQL data types.
- 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.
- Reserved word: You should not use them to name program objects such as constants, variables, cursors, schema objects such as columns, tables, or indexes.
- 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.
- Truth table
In the next module, you will learn to add control statements to a PL/SQL block.
PL/SQL Features for Oracle Database 11g Release 1
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.
SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE Data Types
The SIMPLE_INTEGER, SIMPLE_FLOAT, and 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.
PL/ SQL DataTypes - Quiz
Click the Quiz link below to take a multiple-choice quiz about the material we covered in this module.
PL/ SQL DataTypes - Quiz