LightDB支持oracle pl/sql以及开源postgresql pl/pgsql两种过程性(增强)语言。本文主要讲解LightDB plorasql与KingBase和Oracle事务的典型用法及行为对比。
1.函数案例分析
LightDB commit/rollback在函数中的案例
create table test_rbk_var(id int primary key,ename varchar);
--创建函数
create or replace function func_rbk_var(a inout int,b inout text) return integer as
begin
a := 2;
b := 'bob';
insert into test_rbk_var(id,ename) values(a,b);
insert into test_rbk_var(id,ename) values(a,b); --会因主键冲突而触发EXCEPTION
commit;
return a;
exception when others then
a := 3;
b := 'tom';
rollback;
return a;
end;
/
--调用函数
declare
d int := 5;
e text := 'andy';
f int;
begin
f := func_rbk_var(d,e);
dbms_output.put_line('d:' || d || ',e:' || e || ',f:' || f);
end;
/
--运行结果展示
d:3,e:tom,f:3
--去掉函数中的一行INSERT语句走COMMIT逻辑的运行结果展示
d:2,e:bob,f:2
kingbase commit/rollback在函数中的案例
test=# \set SQLTERM /
test=# create table test_rbk_var(id int primary key,ename varchar);
test-# /
CREATE TABLE
test=# create or replace function func_rbk_var(a in out int,b in out text) return int as
test-# begin
test-# a := 2;
test-# b := 'bob';
test-# insert into test_rbk_var(id,ename) values(a,b);
test-# insert into test_rbk_var(id,ename) values(a,b); --会因主键冲突而触发EXCEPTION
test-# commit;
test-# return a;
test-# exception when others then
test-# a := 3;
test-# b := 'tom';
test-# rollback;
test-# return a;
test-# end;
test-# /
CREATE FUNCTION
test=# set serveroutput on;
--调用函数
test=# declare
test-# d int;
test-# e varchar(10);
test-# f int;
test-# begin
test-# f := func_rbk_var(d,e);
test-# dbms_output.put_line('d:' || d || ',e:' || e || ',f:' || f);
test-# end;
test-# /
ANONYMOUS BLOCK
d:3,e:tom,f:3
--去掉函数中的一行INSERT语句走COMMIT逻辑的运行结果展示
d:2,e:bob
Oracle commit/rollback在函数中的案例
create table test.test_rbk_var(id int primary key,ename varchar2(32));
--创建函数
create or replace function test.func_rbk_var(a in out int,b in out varchar2) return int
is
begin
a := 2;
b := 'bob';
insert into test_rbk_var(id,ename) values(a,b);
insert into test_rbk_var(id,ename) values(a,b); --会因主键冲突而触发EXCEPTION
commit;
return( a);
exception when others then
a := 3;
b := 'tom';
rollback;
return( a);
end;
/
--调用函数
set serveroutput on;
declare
d int;
e varchar(10);
f int;
begin
f := test.func_rbk_var(d,e);
dbms_output.put_line('d:' || d || ',e:' || e || ',f:' || f);
end;
--运行结果展示
d:3,e:tom,f:3
--去掉函数中的一行INSERT语句走COMMIT逻辑的运行结果展示
d:2,e:bob,f:2
从如上三个函数调用案例来看在函数内部COMMIT/ROLLBACK的行为以及INOUT参数的传递行为LightDB与KingBase和Oracle是完全一致的
2.存储过程案例分析
LightDB commit/rollback在存储过程中的案例
--创建存储过程
create or replace procedure proc_rbk_var(a inout int,b inout text) as
begin
a := 2;
b := 'bob';
insert into test_rbk_var(id,ename) values(a,b);
insert into test_rbk_var(id,ename) values(a,b);
commit;
exception when others then
a := 3;
b := 'tom';
rollback;
end;
/
--调用存储过程
declare
d int := 5;
e text := 'andy';
begin
proc_rbk_var(d,e);
dbms_output.put_line('d:' || d || ',e:' || e);
end;
/
--运行结果展示
d:3,e:tom
--去掉函数中的一行INSERT语句走COMMIT逻辑的运行结果展示
d:2,e:bob
kingbase commit/rollback在存储过程中的案例
test=# create or replace procedure proc_rbk_var(a in out int,b in out text) as
test-# begin
test-# a := 2;
test-# b := 'bob';
test-# insert into test_rbk_var(id,ename) values(a,b);
test-# insert into test_rbk_var(id,ename) values(a,b);
test-# commit;
test-# exception when others then
test-# a := 3;
test-# b := 'tom';
test-# rollback;
test-# end;
test-# /
CREATE PROCEDURE
--调用存储过程
test=# declare
test-# d int;
test-# e varchar2(10);
test-# begin
test-# proc_rbk_var(d,e);
test-# dbms_output.put_line('d:' || d || ',e:' || e);
test-# end;
test-# /
ANONYMOUS BLOCK
d:3,e:tom
--去掉函数中的一行INSERT语句走COMMIT逻辑的运行结果展示
d:2,e:bob
Oracle commit/rollback在存储过程中的案例
--创建存储过程
create or replace procedure test.proc_rbk_var(a in out int,b in out varchar2)
is
begin
a := 2;
b := 'bob';
insert into test_rbk_var(id,ename) values(a,b);
insert into test_rbk_var(id,ename) values(a,b);
exception when others then
a := 3;
b := 'tom';
rollback;
end;
/
--调用存储过程
declare
d int;
e varchar2(10);
begin
test.proc_rbk_var(d,e);
dbms_output.put_line('d:' || d || ',e:' || e);
end;
--运行结果展示
d:3,e:tom
--去掉函数中的一行INSERT语句走COMMIT逻辑的运行结果展示
d:2,e:bob
从如上三个存储过程调用案例来看在存储过程内部COMMIT/ROLLBACK的行为以及INOUT参数的传递行为LightDB与KingBase和Oracle是完全一致的
3.匿名块案例分析
LightDB commit/rollback在匿名块中的案例
--匿名块调用
declare
a int;
b text;
begin
a := 2;
b := 'bob';
insert into test_rbk_var(id,ename) values(a,b);
insert into test_rbk_var(id,ename) values(a,b);
commit;
dbms_output.put_line('a:' || a || ',b:' || b);
exception when others then
a := 3;
b := 'tom';
rollback;
dbms_output.put_line('a:' || a || ',b:' || b);
end;
/
--运行结果展示
a:3,b:tom
--去掉函数中的一行INSERT语句走COMMIT逻辑的运行结果展示
a:2,b:bob
kingbase commit/rollback在匿名块中的案例
test=# declare
test-# a int;
test-# b varchar2(10);
test-# begin
test-# a := 2;
test-# b := 'bob';
test-# insert into test_rbk_var(id,ename) values(a,b);
test-# insert into test_rbk_var(id,ename) values(a,b);
test-# commit;
test-# dbms_output.put_line('a:' || a || ',b:' || b);
test-# exception when others then
test-# a := 3;
test-# b := 'tom';
test-# rollback;
test-# dbms_output.put_line('a:' || a || ',b:' || b);
test-# end;
test-# /
ANONYMOUS BLOCK
a:3,b:tom
--去掉函数中的一行INSERT语句走COMMIT逻辑的运行结果展示
a:2,b:bob
Oracle commit/rollback在匿名块中的案例
--匿名块调用
declare
a int;
b varchar2(10);
begin
a := 2;
b := 'bob';
insert into test_rbk_var(id,ename) values(a,b);
insert into test_rbk_var(id,ename) values(a,b);
commit;
dbms_output.put_line('a:' || a || ',b:' || b);
exception when others then
a := 3;
b := 'tom';
rollback;
dbms_output.put_line('a:' || a || ',b:' || b);
end;
/
--运行结果展示
a:3,b:tom
--去掉函数中的一行INSERT语句走COMMIT逻辑的运行结果展示
a:2,b:bob
从如上三个匿名块调用案例来看在匿名块内部COMMIT/ROLLBACK的行为以及INOUT参数的传递行为LightDB与KingBase和Oracle是完全一致的
4.COMMIT/ROLLBACK函数外部执行案例分析
Oracle commit/rollback在外部执行的案例
--创建函数(不包含COMMIT/ROLLBACK)
create or replace function func_rbk_var01(a in out int,b in out varchar2) return integer as
begin
insert into test_rbk_var(id,ename) values(a,b);
return a;
end;
/
--会话A
SQL> declare
a int := 2;
b varchar2(10) := 'bob';
c int;
begin
c := func_rbk_var01(a,b);
--commit;
dbms_output.put_line('a:' || a || ',b:' || b || ',c:' || c);
end;
/
PL/SQL procedure successfully completed.
--会话B
SQL> select * from test.test_rbk_var;
no rows selected
--会话A
SQL> commit;
Commit complete.
--会话B
SQL> select * from test.test_rbk_var;
ID ENAME
---------- --------------------------------
2 bob
kingbase commit/rollback在外部执行的案例
test=# create or replace function func_rbk_var01(a in out int,b in out varchar2) return integer as
test-# begin
test-# insert into test_rbk_var(id,ename) values(a,b);
test-# return a;
test-# end;
test-# /
CREATE FUNCTION
--会话A
test=# declare
test-# a int := 2;
test-# b varchar2(10) := 'bob';
test-# c int;
test-# begin
test-# c := func_rbk_var01(a,b);
test-# --commit;
test-# dbms_output.put_line('a:' || a || ',b:' || b || ',c:' || c);
test-# end;
test-# /
ANONYMOUS BLOCK
a:2,b:bob,c:2
test=# select * from test_rbk_var;
test-# /
id | ename
----+-------
2 | bob
(1 row)
--会话B
test=# select * from test_rbk_var;
id | ename
----+-------
2 | bob
(1 row)
LightDB commit/rollback在外部执行的案例
--创建函数(不包含COMMIT/ROLLBACK)
create or replace function func_rbk_var01(a inout int,b inout text) return integer as
begin
insert into test_rbk_var(id,ename) values(a,b);
return a;
end;
/
--会话A
lightdb@postgres=# declare
lightdb@postgres$# a int := 2;
lightdb@postgres$# b text := 'bob';
lightdb@postgres$# c int;
lightdb@postgres$# begin
lightdb@postgres$# c := func_rbk_var01(a,b);
lightdb@postgres$# --commit;
lightdb@postgres$# end;
lightdb@postgres$# /
DO
--会话B
lightdb@postgres=# select * from test_rbk_var;
id | ename
----+-------
2 | bob
(1 row)
从如上三个外部控制事务案例来看LightDB与KingBase在被调函数中没有显式COMMIT遇到END会自动提交事务,而Oracle则需要手动COMMIT来提交事务
5.COMMIT/ROLLBACK存储过程外部执行案例分析
Oracle commit/rollback在外部执行的案例
--创建函存储过程(不包含COMMIT/ROLLBACK)
create or replace procedure proc_rbk_var01(a in out int,b in out varchar2) as
begin
insert into test_rbk_var(id,ename) values(a,b);
end;
/
--会话A
SQL> declare
a int := 2;
b varchar2(10) := 'bob';
begin
proc_rbk_var01(a,b);
--commit;
dbms_output.put_line('a:' || a || ',b:' || b);
end;
/
PL/SQL procedure successfully completed.
--会话B
SQL> select * from test.test_rbk_var;
no rows selected
--会话A
SQL> commit;
Commit complete.
--会话B
SQL> select * from test.test_rbk_var;
ID ENAME
---------- --------------------------------
2 bob
kingbase commit/rollback在外部执行的案例
test=# create or replace procedure proc_rbk_var01(a in out int,b in out text) as
test-# begin
test-# insert into test_rbk_var(id,ename) values(a,b);
test-# end;
test-# /
CREATE PROCEDURE
--会话A
test=# declare
test-# a int := 2;
test-# b varchar2(10) := 'bob';
test-# begin
test-# proc_rbk_var01(a,b);
test-# --commit;
test-# dbms_output.put_line('a:' || a || ',b:' || b);
test-# end;
test-# /
ANONYMOUS BLOCK
a:2,b:bob
test=# select * from test_rbk_var;
test-# /
id | ename
----+-------
2 | bob
(1 row)
--会话B
test=# select * from test_rbk_var;
id | ename
----+-------
2 | bob
(1 row)
LightDB commit/rollback在外部执行的案例
--创建存储过程(不包含COMMIT/ROLLBACK)
create or replace procedure proc_rbk_var01(a inout int,b inout text) as
begin
insert into test_rbk_var(id,ename) values(a,b);
end;
/
--会话A
lightdb@postgres=# declare
lightdb@postgres$# d int := 2;
lightdb@postgres$# e text := 'bob';
lightdb@postgres$# begin
lightdb@postgres$# proc_rbk_var01(d,e);
lightdb@postgres$# dbms_output.put_line('d:' || d || ',e:' || e);
lightdb@postgres$# end;
lightdb@postgres$# /
d:2,e:bob
DO
--会话B
lightdb@postgres=# select * from test_rbk_var;
id | ename
----+-------
2 | bob
(1 row)
从如上三个外部控制事务案例来看LightDB与KingBase在被调函数中没有显式COMMIT遇到END会自动提交事务,而Oracle则需要手动COMMIT来提交事务
综上所述
COMMIT/ROLLBACK与ORACLE行为对比 | 函数内部控制事务 | 存储过程内部控制事务 | 匿名块内部控制事务 | 函数外部控制事务 | 存储过程内部控制事务 |
---|---|---|---|---|---|
LightDB | 一致 | 一致 | 一致 | 不一致 | 不一致 |
KingBase | 一致 | 一致 | 一致 | 不一致 | 不一致 |