(3) Language, Identifiers, Declarations

Character Sets
Lexical Units
Data Types
Declarations
References to identifiers
Scope and visibility of identifiers
Assigning Values to variables
Expressions
Error-reporting functions
SQL Functions in PL/SQL Expressions
Pragmas
Conditional Compilation



Character Sets
Single-byte vs Multi-byte representation

(a) Database CS
  • Stored source text of PL/SQL units
  • Character values of data types CHAR, VARCHAR2, CLOB, and LONG
(a) National CS
  • Character values of data types NCHAR, NVARCHAR2 and NCLOB

Lexical Units
Delimiters
Identifiers
Literals
Comments
Whitespace

Identifiers name PL/SQL Elements. Reserved words and Keywords should not be used as identifiers.
  • Predefined identifiers are declared in the predefined package STANDARD.
  • User-defined (ordinary or Quoted)
To list all predefined identifiers:
select type_name from all_types where predefined='YES';




Data Types

(a) Scalar data types
Can have subtypes. A data type and its subtypes comprise a data type family.


PL/SQL scalar data types are:
  • The SQL data types
  • BOOLEAN
  • PLS_INTEGER
  • BINARY_INTEGER
  • REF CURSOR
  • User-defined subtypes



(b) Composite data types
  • These are structured data types. They have internal components (scalar or composite) that can be individually accessed. Composite variables can be passed as parameter.
  • Two types: Collections and Records.

Collections
  • Internal components (elements) are always of the same data type.
  • To create a collection variable: either (a) define a collection type and then create a variable of that type OR use %TYPE attribute.
  • Lists and arrays are classic examples of collections.
Records
  • Here the internal components (fields) can be of different data types.
  • You access each field by its name.
  • A record variable can hold a table row, or some columns from a table row.
  • To create a record variable: either (a) define a RECORD type and then create a variable of that type, OR use %ROWTYPE or %TYPE.
  • Struct (in C); RECORD (in Pascal)

Click here for more information on Oracle and SQL Data types...


Declarations
Variables
Constants
Initial Values
NOT NULL Constraint
%TYPE attribute


//declaring scalar Variable and Constant
DECLARE
  part_number NUMBER(6); -- SQL data type
  part_name VARCHAR2(20); -- SQL data type
  in_stock BOOLEAN := FALSE; -- PL/SQL-only data type

  max_days CONSTANT INTEGER := 366; -- SQL data type
  ispaid CONSTANT BOOLEAN := FALSE; -- PL/SQL-only
BEGIN
  ...
[EXCEPTION
  ...]
END;




If the declaration is in a block or subprogram, the initial value is assigned to the variable or constant every time control passes to the block or subprogram.
If the declaration is in a package specification, the initial value is assigned to the variable or constant for each session (whether the variable or constant is public or private).