更新日期：2024年09月11日
通过运行用于创建 sales_demo 数据库的脚本，可以为数据仓储应用程序实施维数据库。
sales_demo 数据库基于 stores_demo 模式和数据。
要创建 sales_demo 数据库：
1. 使用以下命令创建 stores_demo 数据库：
dbaccessdemo -log
2. 确保 createdw.sql 和 loaddw.sql 文件与 loaddw.sql 所使用的扩展名为 .unl 的
文件位于同一目录中。
3. 运行 createdw.sql 文件。
4. 运行 loaddw.sql 文件。
createdw.sql 文件
此文件创建包含记录的新 sales_demo 数据库，然后在该数据库内创建表。它包含以下语
句：
create database sales_demo with log;
create table product (
product_code integer,
product_name char(31),
vendor_code char(3),
vendor_name char(15),
product_line_code smallint,
product_line_name char(15));
GBase 8s DB-Access 用户指南
南大通用数据技术股份有限公司 - 57 -
create table customer (
customer_code integer,
customer_name char(31),
company_name char(20));
create table sales (
customer_code integer,
district_code smallint,
time_code integer,
product_code integer,
units_sold smallint,
revenue money (8,2),
cost money (8,2),
net_profit money(8,2));
create table time
(
time_code int,
order_date date,
month_code smallint,
month_name char(10),
quarter_code smallint,
quarter_name char(10),
year integer
);
create table geography (
GBase 8s DB-Access 用户指南
南大通用数据技术股份有限公司 - 58 -
district_code serial,
district_name char(15),
state_code char(2),
state_name char(18),
region smallint);
loaddw.sql 文件
此文件包含从两个源装入数据所必需的命令：
l
演示目录中带有扩展名 .unl 的文件
l
从 stores_demo 数据库中选择的数据
loaddw.sql 中的这些 SQL 语句完成以下操作：
connect to "stores_demo ";
load from "add_orders.unl"
insert into stores_demo :orders;
load from 'add_items.unl'
insert into stores_demo :items;
connect to "sales_demo";
load from 'costs.unl'
insert into cost;
load from 'time.unl'
insert into time;
insert into geography(district_name, state_code, state_name)
select distinct c.city, s.code, s.sname
from stores_demo :customer c, stores_demo :state s
where c.state = s.code;
update geography -- converts state_code values to region values
set region = 1
GBase 8s DB-Access 用户指南
南大通用数据技术股份有限公司 - 59 -
where state_code = "CA";
update geography
set region = 2
where state_code <> "CA";
insert into customer (customer_code, customer_name, company_name)
select c.customer_num, trim(c.fname) || " " || c.lname, c.company
from stores_demo :customer c;
insert into product (product_code, product_name, vendor_code,
vendor_name, product_line_code, product_line_name)
select a.catalog_num,
trim(m.manu_name) || " "|| s.description,
m.manu_code, m.manu_name, s.stock_num, s.description
from stores_demo :catalog a, stores_demo :manufact m,
stores_demo :stock s
where a.stock_num = s.stock_num and
a.manu_code = s.manu_code and
s.manu_code = m.manu_code;
insert into sales (customer_code, district_code,
time_code, product_code,
units_sold, revenue, cost, net_profit)
select c.customer_num, g.district_code, t.time_code, p.product_code,
SUM(i.quantity), SUM(i.total_price),
SUM(i.quantity * x.cost),
SUM(i.total_price) - SUM(i.quantity * x.cost)
from stores_demo :customer c, geography g, time t,
product p,
stores_demo :items i, stores_demo :orders o, cost x
GBase 8s DB-Access 用户指南
南大通用数据技术股份有限公司 - 60 -
where c.customer_num = o.customer_num and
o.order_num = i.order_num and
p.product_line_code = i.stock_num and
p.vendor_code = i.manu_code and
t.order_date = o.order_date and
p.product_code = x.product_code and
c.city = g.district_name
GROUP BY 1,2,3,4;
connect to "stores_demo ";
load from 'add_orders.unl'
insert into stores_demo :orders;
load from 'add_items.unl'
insert into stores_demo :items;
connect to "sales_demo";
load from 'costs.unl'
insert into cost;
load from 'time.unl'
insert into time;
insert into geography(district_name, state_code, state_name)
select distinct c.city, s.code, s.sname
from stores_demo :customer c, stores_demo :state s
where c.state = s.code;
update geography -- converts state_code values to region values
set region = 1
where state_code = "CA";
update geography
GBase 8s DB-Access 用户指南
南大通用数据技术股份有限公司 - 61 -
set region = 2
where state_code <> "CA";
insert into customer (customer_code, customer_name, company_name)
select c.customer_num, trim(c.fname) || " " || c.lname, c.company
from stores_demo :customer c;
insert into product (product_code, product_name, vendor_code,
vendor_name, product_line_code, product_line_name)
select a.catalog_num,
trim(m.manu_name) || " " || s.description,
m.manu_code, m.manu_name, s.stock_num, s.description
from stores_demo :catalog a, stores_demo :manufact m,
stores_demo :stock s
where a.stock_num = s.stock_num and
a.manu_code = s.manu_code and
s.manu_code = m.manu_code;
insert into sales (customer_code, district_code,
time_code, product_code,
units_sold, revenue, cost, net_profit)
select c.customer_num, g.district_code, t.time_code, p.product_code,
SUM(i.quantity), SUM(i.total_price),
SUM(i.quantity * x.cost),
SUM(i.total_price) - SUM(i.quantity * x.cost)
from stores_demo :customer c, geography g, time t, product p,
stores_demo :items i, stores_demo :orders o, cost x
where c.customer_num = o.customer_num and
o.order_num = i.order_num and
GBase 8s DB-Access 用户指南
南大通用数据技术股份有限公司 - 62 -
p.product_line_code = i.stock_num and
p.vendor_code = i.manu_code and
t.order_date = o.order_date and
p.product_code = x.product_code and
c.city = g.district_name
GROUP BY 1,2,3,4;
UNCOMPRESS(expr)
函数说明
解压缩由COMPRESS()函数压缩的字符串。
输入值类型：二进制字符串(COMPRESS 的输出)。
返回值类型：字符串类型。
示例：
gbase> select UNCOMPRESS(COMPRESS('GBAESE')) from dual ;
+--------------------------------+
| UNCOMPRESS(COMPRESS('GBAESE')) |
+--------------------------------+
| GBAESE
|
+--------------------------------+
1 row in set
+--------------------------------+
| UNCOMPRESS(COMPRESS('GBAESE')) |
+--------------------------------+
GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953（2022-04-10）
南大通用数据技术股份有限公司
860
| GBAESE
|
+--------------------------------+
1 row in set