书接上回
这次增加的内容:
(1)包
(2)打开游标的方式
(2.1)open cursorname for sql_variable;
Oracle、lightdb都支持
(2.2)open cursorname for sql_variable using value.
Oracle、lightdb都支持
(3)lightdb 游标操作和事务操作(commit和rollback)结合使用的情况
其中包和打开游标的方式会在下面测试用例中用到,不做过多介绍。
lightdb 游标操作和事务操作(commit和rollback)结合使用的情况
主要介绍游标操作和事务操作(commit和rollback)结合使用的情况。具体分为以下5大类:
1、函数
函数中调函数:不支持
函数中调过程:不支持
函数中调包中函数、存储过程:
(1)调包函数:不支持
(2)调包过程:不支持
2、存储过程
存储过程中调函数:不支持
存储过程中调过程:支持
存储过程中调包中函数、存储过程:
(1)调包函数:不支持
(2)调包过程:支持
3、匿名块
匿名块中调函数:不支持
匿名块中调过程:支持
匿名块中调包中函数、存储过程:
(1)调包函数:不支持
(2)调包过程:支持
4、包中函数
包函数调函数:不支持
包函数调存储过程:不支持
包函数调包中函数、存储过程:
(1)调包函数:不支持
(2)调包过程:不支持
5、包中存储过程
包存储过程调函数:不支持
包存储过程调存储过程:支持
包存储过程调包中函数、存储过程
(1)调包函数:不支持
(2)调包过程:支持
测试案例
存储过程调用存储过程
--前提条件
create extension IF NOT EXISTS orafce;
set lightdb_syntax_compatible_type = oracle;
SET search_path TO public, oracle;
create table ltbench_accounts(id int, abalance int);
--insert
declare
irevert int := 9999;
icount int := 1000;
begin
loop
execute immediate 'insert into ltbench_accounts values(:er,:rt)' using irevert, icount;
icount := icount - 1;
irevert := irevert -1;
exit when icount < 1;
end loop;
end;
/
create or replace procedure tt_try_proc as
CURSOR c1 IS
SELECT * FROM ltbench_accounts;
CURSOR c2 IS
SELECT * FROM ltbench_accounts;
emp_rec ltbench_accounts%ROWTYPE;
BEGIN
OPEN c2;
OPEN c1;
UPDATE ltbench_accounts SET id = 25 WHERE abalance = 12;
rollback;
UPDATE ltbench_accounts SET id = 17 WHERE abalance = 13;
commit;
LOOP
FETCH c1 INTO emp_rec;
EXIT WHEN c1%NOTFOUND;
dbms_output.put_line ('emp_rec.id = ' || emp_rec.id || 'emp_rec.abalance = ' || emp_rec.abalance);
UPDATE ltbench_accounts SET id = 23 WHERE abalance = 12;
rollback;
END LOOP;
LOOP
FETCH c2 INTO emp_rec;
EXIT WHEN c2%NOTFOUND;
dbms_output.put_line ('emp_rec.id = ' || emp_rec.id || 'emp_rec.abalance = ' || emp_rec.abalance);
UPDATE ltbench_accounts SET id = 18 WHERE abalance = 13;
commit;
END LOOP;
commit;
rollback;
END;
/
create or replace procedure tt_try_dproc as
begin
tt_try_proc();
dbms_output.put_line ('This is just a test');
end;
/
call tt_try_dproc();
存储过程调包中的存储过程
前提条件
create table test(x int, y varchar(100));
insert into test values(1, 'One');
insert into test values(2, 'Two');
insert into test values(3, 'Three');
创建包
--create package
create or replace package pkg is
CURSOR c1 IS SELECT x,y FROM test;
function c_open return int;
function c_fetch return int;
procedure c_open_fetch;
end;
/
--create package boby
create or replace package body pkg IS
function c_open return int as
begin
OPEN c1;
return 0;
end;
/
--The fetch in the package, the cursor will not be cleared
function c_fetch return int AS
v_x test.x%TYPE;
v_y test.y%TYPE;
begin
LOOP
FETCH c1 INTO v_x, v_y;
EXIT WHEN c1%NOTFOUND;
dbms_output.put_line( v_x || '.' || v_y);
END LOOP;
--CLOSE c1;
return 0;
end;
procedure c_open_fetch as
v_x test.x%TYPE;
v_y test.y%TYPE;
BEGIN
OPEN c1;
COMMIT;
LOOP
FETCH c1 INTO v_x, v_y;
EXIT WHEN c1%NOTFOUND;
dbms_output.put_line( v_x || '.' || v_y);
IF c1%FOUND THEN
UPDATE test SET x = 4 WHERE y = 'Two';
ROLLBACK;
END IF;
END LOOP;
CLOSE c1;
end;
begin
dbms_output.put_line('initializer');
--same as oracle, package not support dml, commit and roolback.
--UPDATE test SET x = 5 WHERE y = 'Two';
--COMMIT;
end;
/
--procedure of package callled by procedure
create or replace procedure tt_try_proc as
begin
pkg.c_open_fetch();
dbms_output.put_line ('This is just a test');
end;
/
call tt_try_proc();
匿名块调存储过程
begin
tt_try_proc();
end;
/
匿名块调用包中的存储过程
--procedure of package called by anonymous block
begin
pkg.c_open_fetch();
dbms_output.put_line ('This is just a test');
end;
/
存储过程调包中的存储过程
--create package
create or replace package pkg_proc is
CURSOR c1 IS SELECT x,y FROM test;
procedure c_proc;
end;
/
--create package boby
create or replace package body pkg_proc IS
procedure c_proc as
v_x test.x%TYPE;
v_y test.y%TYPE;
BEGIN
OPEN c1;
COMMIT;
LOOP
tt_try_proc();
FETCH c1 INTO v_x, v_y;
EXIT WHEN c1%NOTFOUND;
dbms_output.put_line( v_x || '.' || v_y);
IF c1%FOUND THEN
UPDATE test SET x = 4 WHERE y = 'Two';
ROLLBACK;
END IF;
END LOOP;
CLOSE c1;
end;
begin
dbms_output.put_line('initializer');
--same as oracle, package not support dml, commit and roolback.
--UPDATE test SET x = 5 WHERE y = 'Two';
--COMMIT;
end;
/
call pkg_proc.c_proc();
包存储过程调包中存储过程
--procedure of package called by procedure of package
--create package of pkg_proc
create or replace package pkg_proc is
CURSOR c1 IS SELECT x,y FROM test;
procedure c_proc;
end;
/
--create package boby of pkg_proc
create or replace package body pkg_proc IS
procedure c_proc as
v_x test.x%TYPE;
v_y test.y%TYPE;
BEGIN
OPEN c1;
COMMIT;
LOOP
FETCH c1 INTO v_x, v_y;
EXIT WHEN c1%NOTFOUND;
dbms_output.put_line( v_x || '.' || v_y);
IF c1%FOUND THEN
UPDATE test SET x = 4 WHERE y = 'Two';
ROLLBACK;
END IF;
END LOOP;
CLOSE c1;
end;
begin
dbms_output.put_line('initializer');
--same as oracle, package not support dml, commit and roolback.
--UPDATE test SET x = 5 WHERE y = 'Two';
--COMMIT;
end;
/
--create package of pkg_call_proc
create or replace package pkg_call_proc is
CURSOR c1 IS SELECT x,y FROM test;
procedure c_call_proc;
end;
/
--create package boby of pkg_call_proc
create or replace package body pkg_call_proc IS
procedure c_call_proc as
v_x test.x%TYPE;
v_y test.y%TYPE;
BEGIN
OPEN c1;
COMMIT;
LOOP
pkg_proc.c_proc();
FETCH c1 INTO v_x, v_y;
EXIT WHEN c1%NOTFOUND;
dbms_output.put_line( v_x || '.' || v_y);
IF c1%FOUND THEN
UPDATE test SET x = 4 WHERE y = 'Two';
ROLLBACK;
END IF;
END LOOP;
CLOSE c1;
end;
begin
dbms_output.put_line('initializer');
--same as oracle, package not support dml, commit and roolback.
--UPDATE test SET x = 5 WHERE y = 'Two';
--COMMIT;
end;
/
call pkg_call_proc.c_call_proc();
在声明一个包时,不能在包中直接进行游标的声明
--Declare cursors in packages using sys_refcursor
--error
create or replace package pkg is
c1 sys_refcursor;
function c_open return int;
function c_fetch return int;
procedure c_open_fetch;
end;
/
在定义一个包体时,不能直接在包体的全局区定义一个游标变量
--Declare the cursor in the package body using sys_refcursor
create or replace package pkg is
function c_open return int;
function c_fetch return int;
procedure c_open_fetch;
end;
/
--error
create or replace package body pkg IS
c1 sys_refcursor;
function c_open return int as
begin
OPEN c1 for SELECT x,y FROM test;
return 0;
end;
function c_fetch return int AS
v_x test.x%TYPE;
v_y test.y%TYPE;
begin
LOOP
FETCH c1 INTO v_x, v_y;
EXIT WHEN c1%NOTFOUND;
dbms_output.put_line( v_x || '.' || v_y);
END LOOP;
--CLOSE c1;
return 0;
end;
procedure c_open_fetch as
v_x test.x%TYPE;
v_y test.y%TYPE;
BEGIN
OPEN c1 for SELECT x,y FROM test;
COMMIT;
LOOP
FETCH c1 INTO v_x, v_y;
EXIT WHEN c1%NOTFOUND;
dbms_output.put_line( v_x || '.' || v_y);
IF c1%FOUND THEN
UPDATE test SET x = 4 WHERE y = 'Two';
ROLLBACK;
END IF;
END LOOP;
CLOSE c1;
end;
begin
dbms_output.put_line('initializer');
--same as oracle, package not support dml, commit and roolback.
--UPDATE test SET x = 5 WHERE y = 'Two';
--COMMIT;
end;
/
注意
1、游标操作和事务操作(提交和回滚)结合使用,lightdb_syntax_compatible_type和search_path需要如下进行设置:
set lightdb_syntax_compatible_type = oracle;
SET search_path TO public, oracle;
2、匿名块不支持以调用存储过程相同的方式调用函数:
create or replace function func return int as
begin
dbms_output.put_line (‘This is just a test’);
end;
/
begin
func();
dbms_output.put_line (‘This is just a test’);
end;
/
3、包中游标声明和定义注意:
(1)在声明一个包时,不能在包中直接进行游标的声明;
(2)在定义一个包体时,不能直接在包体的全局区定义一个游标变量;
(3)游标可以声明在包中函数或者存储过程内,但范围只在包中函数或者存储过程内有效。