前言

社区版产品中配备了星环极具竞争力、成熟的关系型分析引擎Inceptor，具备完整的传统关系型数据库语法兼容能力，包括数据库方言Oracle/DB2/Teradata，同时也具备PL/SQL存储过程能力。Inceptor完整支持CRUD，具备完整关系型数据库的操作功能，比如增删改查Merge；

下面的示例将以orc事务表为例，数据内容仅用作演示，具体建表过程中需要注意的实现以及其他细节请参考Inceptor开发者指南。

SQL兼容性能力演示

• 能力演示示例视频

-- !set plsqlUseSlash true

DROP PROCEDURE IF EXISTS merge_from_increment;

set transaction.type = inceptor;

CREATE OR REPLACE PROCEDURE merge_from_increment(var_in in number, var_out out number) IS
BEGIN
BEGIN
TRANSACTION
DBMS_OUTPUT.PUT_LINE('启动 merge_from_increment:' || var_in)
var_out := var_in + 10
-- 变量赋值方式
INSERT INTO ce_demo.employee
VALUES(1, 'Tom', 2000, 1, '2014-3-20', '公司创始人')
INSERT INTO ce_demo.employee
VALUES(2, 'Jack', 6000, 0, '2015-6-1', '一个好人')
INSERT INTO ce_demo.employee
VALUES(3, 'Lucy', 1500, 0, '2017-6-1', '麻烦制造者')
INSERT INTO ce_demo.employee
VALUES(4, '离职', 1500, 0, '2017-6-2', '待删除')
INSERT INTO ce_demo.employee(id, name, salary, bouns, enroll_time, note)
VALUES(5, '', 10000, 0, '2017-8-28', '空ID')
UPDATE ce_demo.employee
SET note = '干得好!'
WHERE id = 2
DELETE ce_demo.employee
WHERE id = 4
INSERT INTO ce_demo.employee_increment
VALUES(2, NULL, 6500, 1)
INSERT INTO ce_demo.employee_increment
VALUES(3, '离职', 0, 0)
INSERT INTO ce_demo.employee_increment
VALUES(4, 'Rob', 12000, 0)
INSERT INTO ce_demo.employee_increment(id, salary, bouns)
VALUES('', 10000, 0)
MERGE INTO ce_demo.employee dest USING ce_demo.employee_increment src ON (dest.id = src.id)
WHEN MATCHED THEN
UPDATE
SET name = CASE
WHEN src.name IS NULL THEN dest.name
ELSE src.name
END,
salary = CASE
WHEN src.salary IS NULL THEN dest.salary
ELSE src.salary
END,
bouns = CASE
WHEN src.bouns IS NULL THEN dest.bouns
ELSE src.bouns
END
WHEN NOT MATCHED THEN
INSERT (id, name, salary, bouns, enroll_time, note)
VALUES (
src.id,
src.name,
src.salary,
src.bouns,
SYSDATE,
'欢迎新员工!'
)
DELETE ce_demo.employee
WHERE name = '离职'
COMMIT
put_line('update_from_increment 完成.')
END merge_from_increment;

-- 调用涨薪存储过程
set transaction.type = inceptor;

declare
 v_out number
BEGIN
 merge_from_increment(1, V_OUT)
 DBMS_OUTPUT.PUT_LINE('out merge_from_increment:' || V_OUT)
END;

-- 验证数据，rob成为新员工，jack薪水更新并显示涨薪
SELECT * FROM ce_demo.employee;

-- 再次调用请先清空表
DELETE FROM ce_demo.employee_increment WHERE 1 = 1;
DELETE FROM ce_demo.employee WHERE 1 = 1;

友情链接

• 数据库安全（库、表、行、列级权限管控能力演示）
• 社区版基础能力演示--计算隔离（离线跑批、OLAP分析业务演示）
• 【汇总】Inceptor相关使用教程及介绍
• 社区版相关资源指路