The language accepted by the pre-compiler to define the aggregate is also termed as SQL-AG. This section describes this language.
The following is a simple aggregate count defined in SQL-AG. This aggregate finds the number of records in a table.
Here Count is the name of the aggregate in the above example. User defines the prototype of Output function, which is used in procedures Single, Multi and Produce to output any information either on the screen or a user-defined table. In the above example since the user only wants to output a single field, the count of the records, Output is defined with a single argument v_result. InternalRecord is a record type that is used to store the state of the aggregate. It consists of all the variables used to keep the information about the state of the aggregate. In case of count user only wants to store the current count of records, hence defines internalrecord with only one field, counter.
Single, Multi and Produce defined here are similar to the single, multi and return function introduced in first section. Given the first tuple, Single computes the initial state of the aggregate. In count, tuple is a singleton and Single returns the initial state in its second argument, which is of type internalrecord. Given the old state and a new tuple, Multi computes the new state. In count the oldstate, new tuple and new state are in o, v_f1 and n respectively. Produce computes the result depending on the current state. Produce contains a set of actions to be performed depending on the value of the flag. It should contain actions for at least two values of the flag, 'normal_flag' and 'eof_flag' that , respectively, specify the action to be performed after each record (but not the last record) and at the end of the table. Other flags can be used by the user for defining other actions to be performed in Single and Multi, this might be useful for debugging purposes. For count where the user only wants to output the total number of records in a table , no value is to be produced at each record and only the counter value has to be returned at the end of the table. Hence output is used only for the flag value 'eof_flag'.
The types supported by SQL-AG are varchar2, chars, rawdata, number, date and bool. Of these types varchar2, chars and rawdata require sizes being variable size types.
The user could also use three pre-defined types t_stringtable,
t_numbertable and t_datetable for any variables declared in
SQL-AG. These types are pre-defined as the tables of varchar2
(string), number and date correspondingly and are useful for
aggregates that require the state to be a table. Examples such as maxname
described in subsection 4.3 use
one of these types.
The following is the main structure of a SQL-AG program used to define a new aggregate:
where parameter stands for the following syntax:
and local declarations stands for:
In the above syntax, Single, Multi and Produce are PL/SQL procedures. Also, the term "executable statements" denotes any PL/SQL code and "datatype" denotes any PL/SQL type. Detail description of the syntax of PL/SQL is given in Chapter 5.