环境准备
drop table if exists bills ;
create table bills
(
id serial not null,
goodsdesc text not null,
beginunit text not null,
begincity text not null,
pubtime timestamp not null,
amount float8 not null default 0,
primary key (id)
) distribute by shard(id) to group default_group;
COMMENT ON TABLE bills is '运单记录';
COMMENT ON COLUMN bills.id IS 'id号';
COMMENT ON COLUMN bills.goodsdesc IS '货物名称';
COMMENT ON COLUMN bills.beginunit IS '启运省份';
COMMENT ON COLUMN bills.begincity IS '启运城市';
COMMENT ON COLUMN bills.pubtime IS '发布时间';
COMMENT ON COLUMN bills.amount IS '运费';
INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount)
VALUES(default,'衣服','海南省','三亚市','2015-10-05 09:32:01',ROUND((random()*10000)::NUMERIC,2));
INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount)
VALUES(default,'建筑设备','福建省','三明市','2015-10-05 07:21:22',ROUND((random()*10000)::NUMERIC,2));
INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount)
VALUES(default,'设备','福建省','三明市','2015-10-05 11:21:54',ROUND((random()*10000)::NUMERIC,2));
INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount)
VALUES(default,'普货','福建省','三明市','2015-10-05 15:19:17',ROUND((random()*10000)::NUMERIC,2));
INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount)
VALUES(default,'5 0铲车，后八轮翻斗车','河南省','三门峡市','2015-10-05 07:53:13',ROUND((random()*10000)::NUMERIC,2));
INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount)
VALUES(default,'鲜香菇2000斤','河南省','三门峡市','2015-10-05 10:38:29',ROUND((random()*10000)::NUMERIC,2));
INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount)
VALUES(default,'旋挖附件38吨','河南省','三门峡市','2015-10-05 10:48:38',ROUND((random()*10000)::NUMERIC,2));
INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount)
VALUES(default,'旋挖附件35吨','河南省','三门峡市','2015-10-05 10:48:38',ROUND((random()*10000)::NUMERIC,2));
INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount)
VALUES(default,'旋挖附件39吨','河南省','三门峡市','2015-10-05 11:38:38',ROUND((random()*10000)::NUMERIC,2));
INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount)
VALUES(default,'设备','上海市','上海市','2015-10-05 07:59:35',ROUND((random()*10000)::NUMERIC,2));
INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount)
VALUES(default,'普货40吨需13米半挂一辆','上海市','上海市','2015-10-05 08:13:59',ROUND((random()*10000)::NUMERIC,2));
定义游标
注意游标需要放在一个事务中使用。
teledb=# begin;
BEGIN
teledb=# DECLARE teledb_cur SCROLL CURSOR FOR SELECT * from bills ORDER BY id;
DECLARE CURSOR
提取下一行数据
teledb=# DECLARE teledb_cur SCROLL CURSOR FOR SELECT * from bills ORDER BY id;
DECLARE CURSOR
teledb=# FETCH NEXT from teledb_cur;
id | goodsdesc | beginunit | begincity | pubtime | amount
----+-----------+-----------+-----------+---------------------+---------
1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 3714.15
(1 row)
teledb=# FETCH NEXT from teledb_cur;
id | goodsdesc | beginunit | begincity | pubtime | amount
----+-----------+-----------+-----------+---------------------+---------
2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 8195.98
(1 row)
提取前一行数据
teledb=# FETCH PRIOR from teledb_cur;
id | goodsdesc | beginunit | begincity | pubtime | amount
----+-----------+-----------+-----------+---------------------+---------
1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 3714.15
(1 row)
teledb=# FETCH PRIOR from teledb_cur;
id | goodsdesc | beginunit | begincity | pubtime | amount
----+-----------+-----------+-----------+---------+--------
(0 rows)
提取最后一行
teledb=# fetch last from teledb_cur;
id | goodsdesc | beginunit | begincity | pubtime | amount
----+------------------------+-----------+-----------+---------------------+---------
11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 1425.64
(1 row)
提取第一行
teledb=# fetch first from teledb_cur;
id | goodsdesc | beginunit | begincity | pubtime | amount
----+-----------+-----------+-----------+---------------------+---------
1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 3714.15
(1 row)
提取该查询的第x行
teledb=# fetch absolute 2 from teledb_cur;
id | goodsdesc | beginunit | begincity | pubtime | amount
----+-----------+-----------+-----------+---------------------+---------
2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 8195.98
(1 row)
teledb=# fetch absolute -2 from teledb_cur;
id | goodsdesc | beginunit | begincity | pubtime | amount
----+-----------+-----------+-----------+---------------------+---------
10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 1784.63
(1 row)
x为负数时从尾部向上提取。
提取当前位置后的第x行
teledb=# fetch first from teledb_cur;
id | goodsdesc | beginunit | begincity | pubtime | amount
----+-----------+-----------+-----------+---------------------+---------
1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 3714.15
(1 row)
teledb=# fetch relative 2 from teledb_cur;
id | goodsdesc | beginunit | begincity | pubtime | amount
----+-----------+-----------+-----------+---------------------+---------
3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6351.44
(1 row)
teledb=# fetch relative 2 from teledb_cur;
id | goodsdesc | beginunit | begincity | pubtime | amount
----+-----------------------+-----------+-----------+---------------------+---------
5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 6252.91
(1 row)
teledb=# fetch relative -2 from teledb_cur;
id | goodsdesc | beginunit | begincity | pubtime | amount
----+-----------+-----------+-----------+---------------------+---------
3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6351.44
(1 row)
每次提取游标都会移动到指定位置，下一个命令从当前位置出发，relative 2代表后面的第2条记录。
提取后x行数据
teledb=# fetch forward 2 from teledb_cur;
id | goodsdesc | beginunit | begincity | pubtime | amount
----+-----------------------+-----------+-----------+---------------------+---------
4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 7626.41
5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 6252.91
(2 rows)
teledb=# fetch forward 2 from teledb_cur;
id | goodsdesc | beginunit | begincity | pubtime | amount
----+--------------+-----------+-----------+---------------------+---------
6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 1828.83
7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9376.8
(2 rows)
提取剩下的所有数据
游标顺序往下遍历，提取所有数据。
teledb=# fetch forward all from teledb_cur;
id | goodsdesc | beginunit | begincity | pubtime | amount
----+------------------------+-----------+-----------+---------------------+---------
8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9885.95
9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 4971.79
10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 1784.63
11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 1425.64
(4 rows)
反向提取x行数据
teledb=# fetch backward 2 from teledb_cur;
id | goodsdesc | beginunit | begincity | pubtime | amount
----+------------------------+-----------+-----------+---------------------+---------
11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 1425.64
10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 1784.63
(2 rows)
teledb=# fetch backward 3 from teledb_cur;
id | goodsdesc | beginunit | begincity | pubtime | amount
----+--------------+-----------+-----------+---------------------+---------
9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 4971.79
8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9885.95
7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9376.8
(3 rows)
游标反向移动，提取x行数据。
反向提取所有数据
游标反向移动，提取直到第1条数据。
teledb=# fetch backward all from teledb_cur;
id | goodsdesc | beginunit | begincity | pubtime | amount
----+-----------------------+-----------+-----------+---------------------+---------
6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 1828.83
5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 6252.91
4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 7626.41
3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6351.44
2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 8195.98
1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 3714.15
(6 rows)
