返回首页

gbase数据、南大通用产品文档：GBase8a

更新日期：2024年09月11日

MERGE 示例
示例

示例1：对t1 表进行MERGE 操作。
示例中用到的表及数据如下：
DROP TABLE IF EXISTS t1;
CREATE
TABLE
t1(i
int,vc
varchar(20),d
date,dc
decimal(20,3))
DISTRIBUTED
BY
('i');
INSERT INTO t1 VALUES(1,'one','2013-02-03',11.21);
INSERT INTO t1 VALUES (2,'two','2013-04-03',12.21);
INSERT INTO t1 VALUES (3,'one2','2013-03-03',31.21);
INSERT INTO t1 VALUES (11,'one3','2013-08-03',41.21);
INSERT INTO t1 VALUES (14,'three','2013-07-22',161.218);
INSERT INTO t1 VALUES (33,'third','2013-09-04',11.216);
INSERT INTO t1 VALUES (5,'wto','2013-02-03',110.210);
INSERT INTO t1 VALUES (null,'first','2013-02-03',311.91);
INSERT INTO t1 VALUES (8,'five','2013-02-03',811.201);

DROP TABLE IF EXISTS t2;
CREATE
TABLE
t2(i
int,vc
varchar(20),d
date,dc
decimal(30,3))
DISTRIBUTED
BY
('i');
INSERT INTO t2 VALUES (1,'one','2013-02-03',11.20);
INSERT INTO t2 VALUES (2,'two','2013-08-03',12.81);
INSERT INTO t2 VALUES (13,'one2','2013-09-03',31.01);
INSERT INTO t2 VALUES (110,'one3','2013-08-03',41.21);

GBase UP 产品手册 5 数据库管理指南
文档版本04（2021-04-21） 南大通用数据技术股份有限公司 736
INSERT INTO t2 VALUES (14,'three','2013-06-22',161.218);
INSERT INTO t2 VALUES (30,'third','2013-09-04',11.216);
MERGE 操作前，分别查询t1 表和t2 表的原始数据。
gbase> SELECT * FROM t1 ORDER BY i;
+------+-------+------------+---------+
| i | vc | d | dc |
+------+-------+------------+---------+
| 1 | one | 2013-02-03 | 11.210 |
| 2 | two | 2013-04-03 | 12.210 |
| 3 | one2 | 2013-03-03 | 31.210 |
| 5 | wto | 2013-02-03 | 110.210 |
| 8 | five | 2013-02-03 | 811.201 |
| 11 | one3 | 2013-08-03 | 41.210 |
| 14 | three | 2013-07-22 | 161.218 |
| 33 | third | 2013-09-04 | 11.216 |
| NULL | first | 2013-02-03 | 311.910 |
+------+-------+------------+---------+
9 rows in set

gbase> SELECT * FROM t2 ORDER BY i;
+------+-------+------------+---------+
| i | vc | d | dc |
+------+-------+------------+---------+
| 1 | one | 2013-02-03 | 11.200 |
| 2 | two | 2013-08-03 | 12.810 |
| 13 | one2 | 2013-09-03 | 31.010 |
| 14 | three | 2013-06-22 | 161.218 |
| 30 | third | 2013-09-04 | 11.216 |
| 110 | one3 | 2013-08-03 | 41.210 |
+------+-------+------------+---------+
6 rows in set

gbase>
MERGE
INTO
t1
USING
t2
ON
t1.i=t2.i
WHEN
MATCHED
THEN
UPDATE
SET
t1.vc=t2.vc
WHEN NOT MATCHED THEN INSERT(t1.i,t1.vc) VALUES(t2.i,t2.vc);
Query OK, 6 rows affected
Rows matched: 6 Changed: 6 Warnings: 0

gbase> SELECT * FROM t1 ORDER BY i;
+------+-------+------------+---------+
| i | vc | d | dc |
+------+-------+------------+---------+
| 1 | one | 2013-02-03 | 11.210 |
| 2 | two | 2013-04-03 | 12.210 |
| 3 | one2 | 2013-03-03 | 31.210 |

GBase UP 产品手册 5 数据库管理指南
文档版本04（2021-04-21） 南大通用数据技术股份有限公司 737
| 5 | wto | 2013-02-03 | 110.210 |
| 8 | five | 2013-02-03 | 811.201 |
| 11 | one3 | 2013-08-03 | 41.210 |
| 13 | one2 | NULL | NULL |
| 14 | three | 2013-07-22 | 161.218 |
| 30 | third | NULL | NULL |
| 33 | third | 2013-09-04 | 11.216 |
| 110 | one3 | NULL | NULL |
| NULL | first | 2013-02-03 | 311.910 |
+------+-------+------------+---------+
12 rows in set

示例2：t1 表使用别名t，然后进行MERGE 操作。
示例中用到的表及数据如下：
DROP TABLE IF EXISTS t1;
CREATE
TABLE
t1(i
int,vc
varchar(20),d
date,dc
decimal(20,3))
DISTRIBUTED
BY
('i');
INSERT INTO t1 VALUES(1,'one','2013-02-03',11.21);
INSERT INTO t1 VALUES (2,'two','2013-04-03',12.21);
INSERT INTO t1 VALUES (3,'one2','2013-03-03',31.21);
INSERT INTO t1 VALUES (11,'one3','2013-08-03',41.21);
INSERT INTO t1 VALUES (14,'three','2013-07-22',161.218);
INSERT INTO t1 VALUES (33,'third','2013-09-04',11.216);
INSERT INTO t1 VALUES (5,'wto','2013-02-03',110.210);
INSERT INTO t1 VALUES (null,'first','2013-02-03',311.91);
INSERT INTO t1 VALUES (8,'five','2013-02-03',811.201);

DROP TABLE IF EXISTS t2;
CREATE
TABLE
t2(i
int,vc
varchar(20),d
date,dc
decimal(30,3))
DISTRIBUTED
BY
('i');
INSERT INTO t2 VALUES (1,'one','2013-02-03',11.20);
INSERT INTO t2 VALUES (2,'two','2013-08-03',12.81);
INSERT INTO t2 VALUES (13,'one2','2013-09-03',31.01);
INSERT INTO t2 VALUES (110,'one3','2013-08-03',41.21);
INSERT INTO t2 VALUES (14,'three','2013-06-22',161.218);
INSERT INTO t2 VALUES (30,'third','2013-09-04',11.216);
MERGE 操作前，分别查询t1 表和t2 表的原始数据。
gbase> SELECT * FROM t1 ORDER BY i;
+------+-------+------------+---------+
| i | vc | d | dc |
+------+-------+------------+---------+
| 1 | one | 2013-02-03 | 11.210 |
| 2 | two | 2013-04-03 | 12.210 |
| 3 | one2 | 2013-03-03 | 31.210 |
| 5 | wto | 2013-02-03 | 110.210 |
| 8 | five | 2013-02-03 | 811.201 |

GBase UP 产品手册 5 数据库管理指南
文档版本04（2021-04-21） 南大通用数据技术股份有限公司 738
| 11 | one3 | 2013-08-03 | 41.210 |
| 14 | three | 2013-07-22 | 161.218 |
| 33 | third | 2013-09-04 | 11.216 |
| NULL | first | 2013-02-03 | 311.910 |
+------+-------+------------+---------+
9 rows in set

gbase> SELECT * FROM t2 ORDER BY i;
+------+-------+------------+---------+
| i | vc | d | dc |
+------+-------+------------+---------+
| 1 | one | 2013-02-03 | 11.200 |
| 2 | two | 2013-08-03 | 12.810 |
| 13 | one2 | 2013-09-03 | 31.010 |
| 14 | three | 2013-06-22 | 161.218 |
| 30 | third | 2013-09-04 | 11.216 |
| 110 | one3 | 2013-08-03 | 41.210 |
+------+-------+------------+---------+
6 rows in set
t1 表使用别名t 后，继续执行MERGE 操作。
gbase>
MERGE
INTO
t1
t
USING
t2
ON
t.i=t2.i
WHEN
MATCHED
THEN
UPDATE
SET
t.vc=t2.vc
WHEN NOT MATCHED THEN INSERT (t.i,t.vc) VALUES (t2.i,t2.vc);
Query OK, 6 rows affected
Rows matched: 6 Changed: 6 Warnings: 0

gbase> SELECT * FROM t1 ORDER BY i;
+------+-------+------------+---------+
| i | vc | d | dc |
+------+-------+------------+---------+
| 1 | one | 2013-02-03 | 11.210 |
| 2 | two | 2013-04-03 | 12.210 |
| 3 | one2 | 2013-03-03 | 31.210 |
| 5 | wto | 2013-02-03 | 110.210 |
| 8 | five | 2013-02-03 | 811.201 |
| 11 | one3 | 2013-08-03 | 41.210 |
| 13 | one2 | NULL | NULL |
| 14 | three | 2013-07-22 | 161.218 |
| 30 | third | NULL | NULL |
| 33 | third | 2013-09-04 | 11.216 |
| 110 | one3 | NULL | NULL |
| NULL | first | 2013-02-03 | 311.910 |
+------+-------+------------+---------+
12 rows in set

示例3：MERGE 操作后，同步更新TIMESTAMP 列的值。

GBase UP 产品手册 5 数据库管理指南
文档版本04（2021-04-21） 南大通用数据技术股份有限公司 739
示例中用到的表及数据如下：
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a timestamp ,b int) DISTRIBUTED BY('b');
INSERT INTO t1(b) VALUES(1);
INSERT INTO t1(b) VALUES(2);
INSERT INTO t1(b) VALUES(6);
INSERT INTO t1(b) VALUES(8);
INSERT INTO t1(b) VALUES(107);
INSERT INTO t1(b) VALUES(105);
MERGE 操作前，分别查询t1 表和tt 表的原始数据。
gbase> SELECT * FROM t1 ORDER BY a;
+---------------------+------+
| a | b |
+---------------------+------+
| 2013-12-17 14:40:03 | 6 |
| 2013-12-17 14:40:03 | 8 |
| 2013-12-17 14:40:03 | 105 |
| 2013-12-17 14:40:03 | 1 |
| 2013-12-17 14:40:03 | 2 |
| 2013-12-17 14:40:03 | 107 |
+---------------------+------+
6 rows in set

gbase> SELECT * FROM tt ORDER BY a;
+---------------------+------+------+
| a | b | c |
+---------------------+------+------+
| 2013-12-17 14:40:44 | 105 | a |
+---------------------+------+------+
1 rows in set
进行MERGE 操作。
gbase>
MERGE
INTO
tt
USING
t1
ON
t1.b=tt.b
WHEN
MATCHED
THEN
UPDATE
SET
tt.c='b'
WHEN NOT MATCHED THEN INSERT (tt.b) VALUES(t1.b);
Query OK, 6 rows affected
Rows matched: 6 Changed: 6 Warnings: 0
查看t1 表中的数据，TIMESTAMP 列没有同步更新。
gbase> SELECT * FROM t1 ORDER BY a;
+---------------------+------+
| a | b |
+---------------------+------+
| 2013-12-17 14:40:03 | 6 |
| 2013-12-17 14:40:03 | 8 |

GBase UP 产品手册 5 数据库管理指南
文档版本04（2021-04-21） 南大通用数据技术股份有限公司 740
| 2013-12-17 14:40:03 | 105 |
| 2013-12-17 14:40:03 | 1 |
| 2013-12-17 14:40:03 | 2 |
| 2013-12-17 14:40:03 | 107 |
+---------------------+------+
6 rows in set
查看tt 表中的数据，TIMESTAMP 列同步更新。
gbase> SELECT * FROM tt ORDER BY a;
+---------------------+------+------+
| a | b | c |
+---------------------+------+------+
| 2013-12-17 14:40:20 | 8 | NULL |
| 2013-12-17 14:40:20 | 1 | NULL |
| 2013-12-17 14:40:20 | 105 | b |
| 2013-12-17 14:40:20 | 6 | NULL |
| 2013-12-17 14:40:20 | 2 | NULL |
| 2013-12-17 14:40:20 | 107 | NULL |
+---------------------+------+------+
6 rows in set
Oracle 引擎 DDL 语句



sword GCISessionBegin(
GCISvcCtx *svchp,
GCIError *errhp,
GCISession *usrhp,
ub4 credt,
ub4 mode
)；

使用登录信息在指定连接句柄上打开与数据库服务的连接。



GBase 8s GCI 接口使用指南

南大通用数据技术股份有限公司 26

参数
输入
/输出


svchp
输入
指定打开连接的上下文， 在此之前， 上下文必须已经被关联
到了连接句柄
errhp
输入
错误信息句柄， 接口调用失败时，错误码和错误信息会被写
入该句柄中
usrhp
输入
登录信息句柄， 执行登录之前， 该句柄上必须已经设置了登
录的用户名和口令
credt
输入
保留参数， 目前不使用(仅用于和Oracle保持兼容)
mode
输入
连接模式， 取值如下：
GCI_CRED_RDBMS：用户名/密码模式
GCI_CRED_EXT:外部整数模式(仅用于和Oracle保持兼容)

如果执行成功， 返回GCI_SUCCESS，否则返回GCI_ERROR。 注释：

调用该接口进行连接后， 须在适当的位置调用GCISessionEnd接口来结束该连接。

模式是一组数据库对象的集合，主要用于控制对数据库对象的访问。所涉及的SQL 语
句，请参考表11-4。
表11-4 模式定义相关SQL
功能
相关SQL
创建模式
CREATE SCHEMA
修改模式属性
ALTER SCHEMA
删除模式
DROP SCHEMA