(3) Language, Identifiers, Declarations

Basic Aspects of Oracle PL/SQL

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).