This chapter first describes the basic elements of an EDB-SPL program. The chapter then provides an overview of the organization of an EDB-SPL program and how it is used to create a procedure or a function. Triggers, while still utilizing EDB-SPL, are sufficiently different to warrant a separate discussion. See Chapter 14.
The remaining sections of the chapter delve into the details of the EDB-SPL language and provide examples of its application.
This section discusses the basic programming elements of an EDB-SPL program.
EDB-SPL programs are written using the following set of characters:
Uppercase letters A thru Z and lowercase letters a thru z
Digits 0 thru 9
Symbols ( ) + - * / < > = ! ~ ^ ; : . ' @ % , " # $ & _ | { } ? [ ]
White space characters tabs, spaces, and carriage returns
Identifiers, expressions, statements, control structures, etc. that comprise the EDB-SPL language are written using these characters.
Note: The data that can be manipulated by an EDB-SPL program is determined by the character set supported by the database encoding.
Keywords and user-defined identifiers that are used in an EDB-SPL program are case insensitive. So for example, the statement DBMS_OUTPUT.PUT_LINE('Hello World'); is interpreted to mean the same thing as dbms_output.put_line('Hello World'); or Dbms_Output.Put_Line('Hello World'); or DBMS_output.Put_line('Hello World');.
Character and string constants, however, are case sensitive as well as any data retrieved from the EnterpriseDB database or data obtained from other external sources. 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 output:
HELLO WORLD!
Identifiers are user-defined names that are used to identify various elements of an EDB-SPL program including variables, cursors, labels, programs, and parameters.
The syntax rules for valid identifiers are the same as for identifiers in the SQL language. See Section 3.1.1 for a discussion of SQL identifiers.
An identifier must not be the same as an EDB-SPL keyword or a keyword of the SQL language. The following are some examples of valid identifiers:
x last___name a_$_Sign Many$$$$$$$$signs_____ THIS_IS_AN_EXTREMELY_LONG_NAME A1
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 followed by a dot with no intervening white space, followed by the name of the object being qualified with no intervening white space. This syntax is called dot notation.
The following is the syntax of a qualified object.
qualifier. [ qualifier. ]... objectqualifier is the name of the owner of the object. object is the name of the entity belonging to qualifier. It is possible to have a chain of qualifications, where the preceeding 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 follow:
Procedure and function names qualified by the schema to which they belong - e.g., schema_name.procedure_name(...)
Trigger names qualified by the schema to which they belong - e.g., schema_name.trigger_name
Column names qualified by the table to which they belong - e.g., emp.empno
Table names qualified by the schema to which they belong - e.g., public.emp
Column names qualified by table and schema - e.g., public.emp.empno
As a general rule, wherever a name appears in the syntax of an EDB-SPL statement, its qualified name in dot notation can be used as well.
Typically a qualified name would only be used if there is some ambiguity associated with the name. For example, if two procedures with the same name belonging to two different schemas are invoked from within a program or if the same name is used for a table column and EDB-SPL variable within the same program.
It is suggested that qualified names be avoided if at all possible. In this chapter, the following conventions are adopted to avoid such naming conflicts:
All variables declared in the declaration section of an EDB-SPL program are prefixed by v_. E.g., v_empno
All formal parameters declared in a procedure or function definition are prefixed by p_. E.g., p_empno
Column names and table names do not have any special prefix conventions. E.g., column empno in table emp
Constants or literals are fixed values that can be used in EDB-SPL programs to represent values of various types - e.g., numbers, strings, dates, etc. Constants come in the following types:
Numeric (Integer and Real) - see Section 3.1.2.2 for information on numeric constants.
Character and String - see Section 3.1.2.1 for information on character and string constants.
Boolean - see Section 7.5 for information on the BOOLEAN data type and constants.
Date/time - see Section 7.4 for information on date/time data types and constants.