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)