This topic describes the basic elements of an SPL program.

Character set

You can use the following set of characters to write SPL programs:
  • Uppercase letters A to Z and lowercase letters a to z
  • Digits 0 to 9
  • Symbols ( ) + - * / < > = ! ~ ^ ; : . ' @ % , " # $ & _ | { } ? [ ]
  • White space characters tabs, spaces, and carriage returns
These characters can be used to write identifiers, expressions, statements, and control structures that comprise the SPL language.
Note The data that can be manipulated by an SPL program is determined by the character set supported by the database encoding.

Case sensitivity

Keywords and user-defined identifiers that are used in an SPL program are not case-sensitive.

For example, the statement
DBMS_OUTPUT.PUT_LINE('Hello World');
is equivalent to the following statements:
dbms_output.put_line('Hello World');
Dbms_Output.Put_Line('Hello World');
DBMS_output.Put_line('Hello World');
However, character constants, string constants, and other data obtained from the POLARDB compatible with Oracle database or external data sources, are case sensitive. The statement DBMS_OUTPUT.PUT_LINE('Hello World!'); produces the following output:
Hello World!
However, the statement DBMS_OUTPUT.PUT_LINE('HELLO WORLD!'); produces the following output:
HELLO WORLD!

Identifiers

Identifiers are user-defined names that are used to identify various elements of an SPL program including variables, cursors, labels, programs, and parameters.

The syntax rules for valid identifiers in the SPL language are the same as for identifiers in the SQL language.

An identifier must be different from SPL or SQL keywords. The following are some examples of valid identifiers:
x
last    name
a_$_Sign
Many$$$$$$$$signs    
THIS_IS_AN_EXTREMELY_LONG_NAME A1                

Qualifiers

A qualifier is a name that specifies the owner or context of an entity that is the object of the qualification. A qualified object is specified as the qualifier name. The qualified object is followed by a period (.) and the name of the object being qualified. Note that the qualifier name and the period (.) has no white space in between. This syntax is called dot notation.

The following is an example of the syntax that is used for a qualified object.
qualifier. [ qualifier. ]... object

qualifier is the name of the object owner. object is the name of the entity that belongs to qualifier. It is possible to have a chain of qualifications where the preceding qualifier owns the entity identified by the subsequent qualifier(s) and object.

Almost any identifier can be qualified. What an identifier is qualified by depends upon what the identifier represents and the context of its usage.

Some examples of qualification are described as follows:
  • Procedure and function names qualified by the schema to which they belong, such as schema_name.procedure_name(...)
  • Trigger names qualified by the schema to which they belong, such as schema_name.trigger_name
  • Column names qualified by the table to which they belong, such as emp.empno
  • Table names qualified by the schema to which they belong, such as public.emp
  • Column names qualified by table and schema, such as public.emp.empno

As a general rule, wherever a name appears in the syntax of an SPL statement, its qualified name can also be used.

A qualified name is used if two procedures that have the same name but belong to two different schemas are invoked from within a program. A qualified name is also used if the same name is used for a table column and SPL variable within the same program.

We do not recommend that you use qualified names. In this chapter, the following conventions are adopted to avoid naming conflicts:
  • All variables declared in the declaration section of an SPL program are prefixed by v_, such as v_empno.
  • All formal parameters declared in a procedure or function definition are prefixed by p_, such as p_empno.
  • Column names and table names do not have any special prefix conventions, such as column empno in table emp.

Constants

In SPL programs, constants or literals are fixed values that can be used to represent values of various types, such as numbers, strings, and dates. Constants can be of the following types:
  • Numeric (integer and real number)
  • Character and string
  • Date/time