This topic describes the basic elements of a structured process language (SPL) program.

Character set

You can use the following characters for SPL programs:
  • Uppercase letters A to Z and lowercase letters a to z
  • Digits 0 to 9
  • Symbols:

    ( ) + - * / < > = ! ~ ^ ; : . ' @ % , " # $ & _ | { } ? [ ]

  • Tabs, space characters, and carriage returns
These characters can be used to write identifiers, expressions, statements, and control structures that are in the SPL language.
Note The data that can be manipulated by an SPL program is determined by the character set that is supported by the database encoding.

Case sensitivity

The following example shows that the keywords and the user-defined identifiers that are used in an SPL program are not case-sensitive.

Example:
DBMS_OUTPUT.PUT_LINE('Hello World');
The preceding statement 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, characters, string constants, and data that is obtained from PolarDB databases 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 each of the following elements of an SPL program: variables, cursors, labels, programs, and parameters.

The syntax rules for valid identifiers in the SPL language are the same as those in the Structured Query Language (SQL).

An identifier cannot be defined as a name that is the same as the name of a keyword in SPL and SQL languages. The following examples show some valid defined identifiers:
x
last    name
a_$_Sign
Many$$$$$$$$signs    
THIS_IS_AN_EXTREMELY_LONG_NAME A1                

Qualifiers

A qualifier is the owner of a specified object, or the name of the context in which the object is used. An object is the name of a qualifier entity. Generally, the entity of a qualifier consists of a qualifier, a period (.) that follows the qualifier, and the name of the object for the qualifier. Take note of the following item: Spaces do not exist before and after the period (.). This syntax is called dot notation.

The following syntax of a qualifier is available:
qualifier. [ qualifier. ]... object

qualifier is the object owner. object is the name of the entity for the qualifier. In a case, the entity that the preceding qualifier owns is identified as a qualifier by the qualifier and the object that follows this entity.

Almost all the identifiers can be qualified. Whether an identifier can be qualified depends on the identifier meaning and the context in which the identifier is used.

The following list provides some qualifier examples.
  • Names that are qualified by the schemas of stored procedures and functions, such as schema_name.procedure_name.
  • Names that are qualified by the schemas of triggers, such as schema_name.trigger_name.
  • Column names that are qualified by the data tables of the columns, such as emp.empno.
  • Table names that are qualified by the schemas of the data tables, such as public.emp.
  • Column names that are qualified by data tables and schemas, such as public.emp.empno.

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

Generally, if two stored procedures that have the same name but belong to two different schemas are invoked from a program, a qualified name is used. A qualified name is also used if identifier naming conflicts occur. For example, if the same name is used for a data table column and an SPL variable in the same program, a qualified name is used.

We recommend that you avoid using qualifiers in actual application scenarios if possible. In this chapter, you can use the following conventions to avoid naming conflicts:
  • All variables declared in the SPL program are prefixed with v_, such as v_empno.
  • All the parameters defined in a stored procedure or a function are prefixed with p_, such as p_empno.
  • Column names and table names do not have specific prefixes, such as column empno in the data table emp.

Constants

In SPL programs, constants are fixed values that can be used to represent values of different data types, such as numbers, strings, and dates. Constants can generally be of the following types:
  • Numbers (integers and real numbers)
  • Characters and strings
  • Dates and time