User-defined routines and extended data types

The superstores_demo database uses user-defined routines (UDRs) and extended data types.

A UDR is a routine that you define that can be invoked within an SQL statement or another UDR. A UDR can either return values or not.

The data type system of HCL OneDB™ is an extensible and flexible system that supports the creation of following kinds of data types:
  • Extensions of existing data types by, redefining some of the behavior for data types that the database server provides
  • Definitions of customized data types by a user

For information about creating and using UDRs and extended data types, see HCL OneDB User-Defined Routines and Data Types Developer's Guide.

The superstores_demo database creates the distinct data type, percent, in a UDR, as follows:
CREATE DISTINCT TYPE percent AS DECIMAL(5,5);
DROP CAST (DECIMAL(5,5) AS percent);
CREATE IMPLICIT CAST (DECIMAL(5,5) AS percent);
The superstores_demo database creates the following named row types:
  • location hierarchy:
    • location_t
    • loc_us_t
    • loc_non_us_t
  • customer hierarchy:
    • name_t
    • customer_t
    • retail_t
    • whlsale_t
  • orders table
    • ship_t

location_t definition

location_id               SERIAL
loc_type                  CHAR(2)
company                   VARCHAR(20)
street_addr               LIST(VARCHAR(25) NOT NULL)
city                      VARCHAR(25)
country                   VARCHAR(25)

loc_us_t definition

state_code               CHAR(2)
zip                      ROW(code INTEGER, suffix SMALLINT)
phone                    CHAR(18)

loc_non_us_t definition

province_code            CHAR(2)
zipcode                  CHAR(9)
phone                    CHAR(15)

name_t definition

first               VARCHAR(15)
last                VARCHAR(15)

customer_t definition

customer_num                   SERIAL
customer_type                  CHAR(1)
customer_name                  name_t
customer_loc                   INTEGER
contact_dates                  LIST(DATETIME YEAR TO DAY NOT NULL)
cust_discount                  percent
credit_status                  CHAR(1)

retail_t definition

credit_num                  CHAR(19)
expiration                  DATE

whlsale_t definition

resale_license              CHAR(15)
terms_net                   SMALLINT

ship_t definition

date                  DATE
weight                DECIMAL(8,2)
charge                MONEY(6,2)
instruct              VARCHAR(40)