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 Dynamic Server 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

This section lists the extended data types and UDRs created for the superstores_demo database. For information about creating and using UDRs and extended data types, see IBM Informix: 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)