create or replace procedure test_without_commit()
as
begin
drop table if exists test_commit;
create table test_commit(a int);
insert into test_commit select 2;
end;
/
call test_without_commit();
select * from test_commit;
create or replace procedure test_empty_sp()
as
begin
insert into test_commit select 1;
insert into test_commit select 2;
insert into test_commit select 3;
end;
/
call test_empty_sp();
select * from test_commit;
drop table test_commit;
create or replace procedure test_commit()
as
begin
drop table if exists test_commit;
create table test_commit(a int, b int);
insert into test_commit select 1, 1;
commit;
end;
/
call test_commit();
select * from test_commit;
drop table test_commit;
create or replace procedure test_commit_insert_option()
shippable
as
begin
drop table if exists test_commit;
create table test_commit(a int, b int);
insert into test_commit select 1, 1;
commit;
end;
/
call test_commit_insert_option();
select * from test_commit;
drop table test_commit;
create or replace procedure test_commit_insert_delete()
as
begin
drop table if exists test_commit;
create table test_commit(a int, b int);
insert into test_commit select 1, 1;
insert into test_commit select 2, 2;
delete from test_commit where a = 1;
commit;
end;
/
call test_commit_insert_delete();
select * from test_commit;
drop table test_commit;
create or replace procedure test_commit_insert_update()
as
begin
drop table if exists test_commit;
create table test_commit(a int, b int);
insert into test_commit select 1, 1;
update test_commit set b = 3 where a = 1;
commit;
end;
/
call test_commit_insert_update();
select * from test_commit;
drop table test_commit;
create or replace procedure test_commit_insert_update_delete()
as
begin
drop table if exists test_commit;
create table test_commit(a int, b int);
insert into test_commit select 1, 1;
update test_commit set b = 3 where a = 1;
delete from test_commit where a = 1;
commit;
end;
/
call test_commit_insert_update_delete();
select * from test_commit;
drop table test_commit;
create or replace procedure test_commit_insert_delete_update()
as
begin
drop table if exists test_commit;
create table test_commit(a int, b int);
insert into test_commit select 1, 1;
insert into test_commit select 2, 2;
delete from test_commit where a = 1;
update test_commit set b = 3 where a = 2;
commit;
end;
/
call test_commit_insert_delete_update();
select * from test_commit;
drop table test_commit;
create or replace procedure test_commit_commit()
as
begin
drop table if exists test_commit;
create table test_commit(a int, b int);
insert into test_commit select 1, 1;
commit;
insert into test_commit select 2, 2;
commit;
end;
/
call test_commit_commit();
select * from test_commit;
drop table test_commit;
create or replace procedure test_commit_commit1()
as
begin
drop table if exists test_commit;
create table test_commit(a int, b int);
insert into test_commit select 1, 1;
insert into test_commit select 2, 2;
commit;
update test_commit set b = 3 where a = 2;
delete from test_commit where a = 1;
commit;
end;
/
call test_commit_commit1();
select * from test_commit;
drop table test_commit;
create or replace procedure test_commit_rollback()
as
begin
drop table if exists test_commit;
create table test_commit(a int, b int);
insert into test_commit select 1, 1;
commit;
insert into test_commit select 2, 2;
rollback;
end;
/
call test_commit_rollback();
select * from test_commit;
drop table test_commit;
create or replace procedure test_commit_rollback1()
as
begin
drop table if exists test_commit;
create table test_commit(a int, b int);
insert into test_commit select 1, 1;
insert into test_commit select 2, 2;
commit;
update test_commit set b = 3 where a = 2;
delete from test_commit where a = 1;
rollback;
end;
/
call test_commit_rollback1();
select * from test_commit;
drop table test_commit;
create or replace procedure test_rollback_commit()
as
begin
drop table if exists test_commit;
create table test_commit(a int, b int);
insert into test_commit select 1, 1;
insert into test_commit select 2, 2;
rollback;
drop table if exists test_commit;
create table test_commit(a int, b int);
insert into test_commit select 3, 3;
insert into test_commit select 4, 4;
insert into test_commit select 5, 5;
update test_commit set b = 6 where a = 5;
delete from test_commit where a = 3;
commit;
end;
/
call test_rollback_commit();
select * from test_commit;
drop table test_commit;
create or replace procedure test_commit_insert_exception_rollback()
as
begin
drop table if exists test_commit;
create table test_commit(a int, b int);
insert into test_commit select 1, 1;
commit;
raise exception 'raise exception after commit';
exception
when others then
insert into test_commit select 2, 2;
rollback;
end;
/
call test_commit_insert_exception_rollback();
select * from test_commit;
drop table test_commit;
create or replace procedure test_commit_insert_exception_commit_rollback()
as
begin
drop table if exists test_commit;
create table test_commit(a int, b int);
insert into test_commit select 1, 1;
commit;
raise exception 'raise exception after commit';
exception
when others then
insert into test_commit select 2, 2;
commit;
rollback;
end;
/
call test_commit_insert_exception_commit_rollback();
select * from test_commit;
drop table test_commit;
create or replace procedure test_commit_insert_raise_commit()
as
begin
drop table if exists test_commit;
create table test_commit(a int, b int);
insert into test_commit select 1, 1;
commit;
RAISE EXCEPTION 'After commit';
end;
/
call test_commit_insert_raise_commit();
select * from test_commit;
drop table test_commit;
create or replace procedure test_commit_insert_delete_raise_commit()
as
begin
drop table if exists test_commit;
create table test_commit(a int, b int);
insert into test_commit select 1, 1;
insert into test_commit select 2, 2;
delete from test_commit where a = 1;
commit;
RAISE EXCEPTION 'After commit';
end;
/
call test_commit_insert_delete_raise_commit();
select * from test_commit;
drop table test_commit;
create or replace procedure test_commit_insert_update_raise_commit()
as
begin
drop table if exists test_commit;
create table test_commit(a int, b int);
insert into test_commit select 1, 1;
update test_commit set b = 3 where a = 1;
commit;
RAISE EXCEPTION 'After commit';
end;
/
call test_commit_insert_update_raise_commit();
select * from test_commit;
drop table test_commit;
create or replace procedure test_commit_insert_update_delete_raise_commit()
as
begin
drop table if exists test_commit;
create table test_commit(a int, b int);
insert into test_commit select 1, 1;
update test_commit set b = 3 where a = 1;
delete from test_commit where a = 1;
commit;
RAISE EXCEPTION 'After commit';
end;
/
call test_commit_insert_update_delete_raise_commit();
select * from test_commit;
drop table test_commit;
create or replace procedure test_commit_insert_delete_update_raise_commit()
as
begin
drop table if exists test_commit;
create table test_commit(a int, b int);
insert into test_commit select 1, 1;
insert into test_commit select 2, 2;
delete from test_commit where a = 1;
update test_commit set b = 3 where a = 2;
commit;
RAISE EXCEPTION 'After commit';
end;
/
call test_commit_insert_delete_update_raise_commit();
select * from test_commit;
drop table test_commit_insert_delete_update_raise_commit;
create or replace procedure test_commit_insert_commit_raise()
as
begin
drop table if exists test_commit;
create table test_commit(a int, b int);
insert into test_commit select 1, 1;
RAISE EXCEPTION 'Before commit';
commit;
end;
/
call test_commit_insert_commit_raise();
select * from test_commit;
drop table test_commit;
create or replace procedure test_commit_insert_delete_commit_raise()
as
begin
drop table if exists test_commit;
create table test_commit(a int, b int);
insert into test_commit select 1, 1;
insert into test_commit select 2, 2;
delete from test_commit where a = 1;
RAISE EXCEPTION 'Before commit';
commit;
end;
/
call test_commit_insert_delete_commit_raise();
select * from test_commit;
drop table test_commit;
create or replace procedure test_commit_insert_update_commit_raise()
as
begin
drop table if exists test_commit;
create table test_commit(a int, b int);
insert into test_commit select 1, 1;
update test_commit set b = 3 where a = 1;
RAISE EXCEPTION 'Before commit';
commit;
end;
/
call test_commit_insert_update_commit_raise();
select * from test_commit;
drop table test_commit;
create or replace procedure test_commit_insert_update_delete_commit_raise()
as
begin
drop table if exists test_commit;
create table test_commit(a int, b int);
insert into test_commit select 1, 1;
update test_commit set b = 3 where a = 1;
delete from test_commit where a = 1;
RAISE EXCEPTION 'Before commit';
commit;
end;
/
call test_commit_insert_update_delete_commit_raise();
select * from test_commit;
drop table test_commit;
create or replace procedure test_commit_insert_delete_update_commit_raise()
as
begin
drop table if exists test_commit;
create table test_commit(a int, b int);
insert into test_commit select 1, 1;
insert into test_commit select 2, 2;
delete from test_commit where a = 1;
update test_commit set b = 3 where a = 2;
RAISE EXCEPTION 'Before commit';
commit;
end;
/
call test_commit_insert_delete_update_commit_raise();
select * from test_commit;
drop table test_commit;
create or replace procedure test_exception_commit()
as
begin
drop table if exists test_commit;
create table test_commit(a int, b int);
insert into test_commit select 1, 1;
insert into test_commit select 2, 2;
delete from test_commit where a = 1;
update test_commit set b = 3 where a = 2;
commit;
EXCEPTION
when raise_exception then
RAISE EXCEPTION '(%)', SQLERRM;
end;
/
call test_exception_commit();
select * from test_commit;
drop table test_commit;
create or replace procedure test_exception_commit_commit_raise()
as
begin
drop table if exists test_commit;
create table test_commit(a int, b int);
insert into test_commit select 1, 1;
insert into test_commit select 2, 2;
delete from test_commit where a = 1;
update test_commit set b = 3 where a = 2;
commit;
RAISE EXCEPTION 'After commit';
EXCEPTION
when raise_exception then
RAISE EXCEPTION '(%)', SQLERRM;
end;
/
call test_exception_commit_commit_raise();
select * from test_commit;
drop table test_commit;
create or replace procedure test_exception_commit_raise_commit()
as
begin
drop table if exists test_commit;
create table test_commit(a int, b int);
insert into test_commit select 1, 1;
insert into test_commit select 2, 2;
delete from test_commit where a = 1;
update test_commit set b = 3 where a = 2;
RAISE EXCEPTION 'After commit';
commit;
EXCEPTION
when raise_exception then
RAISE EXCEPTION '(%)', SQLERRM;
end;
/
call test_exception_commit_raise_commit();
select * from test_commit;
drop table test_commit;
create or replace procedure test_gg_1()
as
begin
drop table if exists test_commit;
create table test_commit(a int, b int);
insert into test_commit select 1, 1;
insert into test_commit select 2, 2;
delete from test_commit where a = 1;
update test_commit set b = 3 where a = 2;
commit;
insert into test_commit select 3, 3;
RAISE EXCEPTION 'After commit';
EXCEPTION
when raise_exception then
rollback;
insert into test_commit select 4, 4;
commit;
end;
/
call test_gg_1();
select * from test_commit;
drop table test_commit;
create or replace procedure test_commit_exception()
is
begin
drop table if exists test_commit;
create table test_commit(a int);
insert into test_commit select 1;
commit;
delete from test_commit;
commit;
update test_commit set a=3;
commit;
exception
WHEN OTHERS THEN
insert into test_commit select 2;
commit;
end;
/
call test_commit_exception();
select * from test_commit;
drop table test_commit;
create or replace procedure test_commit2()
is
begin
drop table if exists test_commit;
create table test_commit(a int);
FOR i IN REVERSE 3..0 LOOP
insert into test_commit select i;
commit;
END LOOP;
FOR i IN REVERSE 2..4 LOOP
update test_commit set a=i;
commit;
END LOOP;
exception
WHEN OTHERS THEN
-- FOR i IN REVERSE 200...101 LOOP
insert into test_commit select 4;
-- END LOOP;
commit;
end;
/
call test_commit2();
select * from test_commit;
drop table test_commit;
create or replace procedure test_commit3()
is
begin
drop table if exists test_commit;
create table test_commit(a int);
insert into test_commit select 1;
commit;
call test_commit2();
update test_commit set a=2;
commit;
exception
WHEN OTHERS THEN
insert into test_commit select 3;
commit;
end;
/
call test_commit3();
select * from test_commit;
drop table test_commit;
create or replace procedure test_rollback_with_exception()
as
begin
drop table if exists test_commit;
create table test_commit(a int);
insert into test_commit select 1;
rollback;
EXCEPTION
when raise_exception then
RAISE EXCEPTION '(%)', SQLERRM;
end;
/
call test_rollback_with_exception();
select * from test_commit;
drop table test_commit;
create or replace procedure test_nest_procedure_without_commit()
as
begin
drop table if exists test_commit;
create table test_commit(a int);
insert into test_commit select 3;
commit;
test_without_commit();
EXCEPTION
when raise_exception then
RAISE EXCEPTION '(%)', SQLERRM;
end;
/
call test_nest_procedure_without_commit();
select * from test_commit;
drop table test_commit;
create or replace procedure test_nest_procedure()
as
begin
drop table if exists test_commit;
create table test_commit(a int);
insert into test_commit select 3;
commit;
test_commit();
EXCEPTION
when raise_exception then
RAISE EXCEPTION '(%)', SQLERRM;
end;
/
call test_nest_procedure();
select * from test_commit;
drop table test_commit;
create or replace procedure test_nest_procedure1()
as
begin
test_commit();
EXCEPTION
when raise_exception then
RAISE EXCEPTION '(%)', SQLERRM;
end;
/
call test_nest_procedure1();
select * from test_commit;
drop table test_commit;
create or replace procedure test_nest_procedure2()
as
begin
test_commit();
end;
/
call test_nest_procedure2();
select * from test_commit;
drop table test_commit;
create or replace procedure test_nest_procedure_rollback()
as
begin
test_without_commit();
rollback;
end;
/
call test_nest_procedure_rollback();
select * from test_commit;
drop table test_commit;
create or replace procedure test_nest_procedure_select()
as
begin
insert into tx select 3;
commit;
select test_commit();
EXCEPTION
when raise_exception then
RAISE EXCEPTION '(%)', SQLERRM;
end;
/
call test_nest_procedure_select();
select * from test_commit;
drop table test_commit;
create or replace procedure test_nest_procedure_calll()
as
begin
insert into tx select 3;
commit;
call test_commit();
EXCEPTION
when raise_exception then
RAISE EXCEPTION '(%)', SQLERRM;
end;
/
call test_nest_procedure_calll();
select * from test_commit;
drop table test_commit;
create or replace procedure test_commit_exception_commit()
as
begin
drop table if exists test_commit;
create table test_commit(a int);
insert into test_commit select 1;
raise exception 'Exception rollback';
insert into test_commit select 2;
EXCEPTION
when raise_exception then
insert into test_commit select 3;
RAISE EXCEPTION '(%)', SQLERRM;
end;
/
call test_commit_exception_commit();
select * from test_commit;
drop table test_commit;
create or replace procedure test_commit_exception_commit_commit()
as
begin
drop table if exists test_commit;
create table test_commit(a int);
insert into test_commit select 1;
raise exception 'Exception rollback';
insert into test_commit select 2;
EXCEPTION
when raise_exception then
insert into test_commit select 3;
commit;
RAISE EXCEPTION '(%)', SQLERRM;
end;
/
call test_commit_exception_commit_commit();
select * from test_commit;
drop table test_commit;
create or replace procedure test_commit_exception_commit_rollback()
as
begin
drop table if exists test_commit;
create table test_commit(a int);
insert into test_commit select 1;
raise exception 'Exception rollback';
insert into test_commit select 2;
EXCEPTION
when raise_exception then
insert into test_commit select 3;
rollback;
RAISE EXCEPTION '(%)', SQLERRM;
end;
/
call test_commit_exception_commit_rollback();
select * from test_commit;
drop table test_commit;
create or replace procedure test_rollback
as
begin
drop table if exists test_commit;
create table test_commit(a int);
insert into test_commit select 1;
rollback;
insert into test_commit select 2;
end;
/
call test_rollback();
select * from test_commit;
drop table test_commit;
create or replace procedure test_commit_inout()
as
declare
p int;
begin
p = 3;
commit;
--DBE_OUTPUT.print_line('Cursor status:' + p);
end;
/
call test_commit_inout();
create or replace procedure test_rollback_inout()
as
declare
p int;
begin
p = 3;
rollback;
--DBE_OUTPUT.print_line('Cursor status:' + p);
end;
/
call test_rollback_inout();
create or replace procedure test_rollback_out()
as
declare
p int;
begin
p = 3;
rollback;
--DBE_OUTPUT.print_line('Cursor status:' + p);
end;
/
call test_rollback_out();
create or replace procedure test_rollback1()
as
declare
begin
create table test1(col1 int);
insert into test1 values(1);
rollback;
end;
/
call test_rollback1();
create type pro_type_04 as ( v_tablefield character varying, v_tablefield2 character varying, v_tablename character varying, v_cur refcursor);
create table test_cursor_table(c1 int,c2 varchar);
insert into test_cursor_table values(1,'Jack'),(2,'Rose');
CREATE or replace procedure pro_base13_03(v_tablefield character varying, v_tablefield2 character varying,v_tablename character varying, v_cur OUT refcursor)
AS
begin
open v_cur for
'select '||v_tablefield||' as tablecode, '||v_tablefield2||' as tablename from '||v_tablename|| ' order by 1,2;';
end;
/
CREATE or replace procedure pro_base13_04(v_tablefield character varying, v_tablefield2 character varying, v_tablename character varying)
AS
v_record pro_type_04;
v_cur refcursor;
num int;
begin
num := 0;
pro_base13_03(v_tablefield, v_tablefield2, v_tablename,v_cur);
loop
fetch v_cur into v_record;
num := num+1;
raise notice 'the num is %(%)', num,v_record;
EXIT WHEN v_cur%notfound;
end loop;
end;
/
call pro_base13_04('c1','c2','test_cursor_table');
CREATE or replace procedure pro_base13_05(v_tablefield character varying, v_tablefield2 character varying,v_tablename character varying, v_cur OUT refcursor)
AS
begin
open v_cur for
'select '||v_tablefield||' as tablecode, '||v_tablefield2||' as tablename from '||v_tablename|| ' order by 1,2;';
commit;
end;
/
CREATE or replace procedure pro_base13_06(v_tablefield character varying, v_tablefield2 character varying, v_tablename character varying)
AS
v_record pro_type_04;
v_cur refcursor;
begin
pro_base13_05(v_tablefield, v_tablefield2, v_tablename,v_cur);
loop
fetch v_cur into v_record;
raise notice '(%)', v_record;
EXIT WHEN v_cur%notfound;
end loop;
end;
/
call pro_base13_06('c1','c2','test_cursor_table');
CREATE or replace procedure pro_base13_07(v_tablefield character varying, v_tablefield2 character varying,v_tablename character varying, v_cur OUT refcursor)
AS
begin
open v_cur for
'select '||v_tablefield||' as tablecode, '||v_tablefield2||' as tablename from '||v_tablename|| ' order by 1,2;';
commit;
end;
/
CREATE or replace procedure pro_base13_08(v_tablefield character varying, v_tablefield2 character varying, v_tablename character varying)
AS
v_record pro_type_04;
v_cur refcursor;
begin
pro_base13_07(v_tablefield, v_tablefield2, v_tablename,v_cur);
loop
fetch v_cur into v_record;
raise notice 'before commit(%)', v_record;
commit;
raise notice 'after commit(%)', v_record;
EXIT WHEN v_cur%notfound;
end loop;
end;
/
call pro_base13_08('c1','c2','test_cursor_table');
select * from test_cursor_table;
drop table if exists test_cursor_table;
create table tb_tran_log(i_a int, i_b varchar(1000));
create or replace procedure proc_test_3(i_a in int, i_b in varchar2, o_retout out int)
as
begin
o_retout:=0;
if i_a >= 3 then
insert into tb_tran_log values(i_a, i_b);
if i_a >= 5 then
begin
commit;
end;
end if;
end if;
exception when others then
o_retout:=-1;
rollback;
end;
/
create or replace procedure proc_test_2(i_a in int, o_retout out int)
as
begin
insert into tb_t2 values(i_a+1,3,4,i_a);
commit;
o_retout:=0;
proc_test_3(3,'正常流程',o_retout);
exception when others then
rollback;
proc_test_3(5,SQLCODE||SQLERRM,o_retout);
o_retout:=-2;
end;
/
create table tb_t2(a int, b int, c int, d int);
create or replace procedure proc_test_1(i_a in int, o_cnt_before out int, o_cnt_after out int, o_retout out int)
as
begin
delete from tb_t2 where a=i_a+2 and d=i_a;
select count(*) into o_cnt_before from tb_t2;
insert into tb_t2 values(i_a+2,3,4,i_a);
commit;
raise notice '%', o_retout;
proc_test_2(i_a, o_retout);
raise notice '%', o_retout;
proc_test_3(4-1, '正常流程',o_retout);
select count(*) into o_cnt_after from tb_t2;
o_retout:=0;
commit;
exception when others then
rollback;
proc_test_3(6,SQLCODE||SQLERRM,o_retout);
o_retout:=1;
end;
/
call proc_test_1(1,1,1,2);
select * from tb_t2;
select * from tb_tran_log;
drop table if exists tb_t2;
drop table if exists tb_tran_log;
drop table if exists proc1_tb1;
create table proc1_tb1(id int, val text);
insert into proc1_tb1 select generate_series(1,10),'aaa';
analyze proc1_tb1;
CREATE OR REPLACE PROCEDURE proc1(c_int in int,var out int)
AS DECLARE
begin
insert into proc1_tb1 values(c_int,'b');
select count(*) into var from proc1_tb1;
raise info 'var1:%',var;
savepoint my_point;
insert into proc1_tb1 values(c_int,'a');
select count(*) into var from proc1_tb1;
raise info 'var2:%',var;
rollback to savepoint my_point;
select count(*) into var from proc1_tb1;
raise info 'var3:%',var;
insert into proc1_tb1 values(c_int,'c');
select count(*) into var from proc1_tb1;
raise info 'var4:%',var;
release savepoint my_point;
commit;
end;
/
select proc1(1);
drop procedure proc1;
drop table proc1_tb1;
drop table if exists t1;
create table t1(c1 int) with (storage_type=ustore, fillfactor=80);
insert into t1 values(1);
create or replace procedure p_t1_for_rollback as
begin
insert into t1 values(2);
begin
rollback;
exception when others then
raise info 'exception rollback 1';
rollback;
end;
exception when others then
raise info 'exception rollback 2s';
rollback;
end;
/
call p_t1_for_rollback();
insert into t1 values(3);
analyze t1;
select pg_sleep(1);
analyze t1;
select pg_sleep(1);
select * from t1;
drop table t1;
drop procedure test_without_commit;
drop procedure test_empty_sp;
drop procedure test_commit;
drop procedure test_commit_insert_option;
drop procedure test_commit_insert_delete;
drop procedure test_commit_insert_update;
drop procedure test_commit_insert_update_delete;
drop procedure test_commit_insert_delete_update;
drop procedure test_commit_commit;
drop procedure test_commit_commit1;
drop procedure test_commit_rollback;
drop procedure test_commit_rollback1;
drop procedure test_rollback_commit;
drop procedure test_commit_insert_exception_rollback;
drop procedure test_commit_insert_exception_commit_rollback;
drop procedure test_commit_insert_raise_commit;
drop procedure test_commit_insert_delete_raise_commit;
drop procedure test_commit_insert_update_raise_commit;
drop procedure test_commit_insert_update_delete_raise_commit;
drop procedure test_commit_insert_delete_update_raise_commit;
drop procedure test_commit_insert_commit_raise;
drop procedure test_commit_insert_delete_commit_raise;
drop procedure test_commit_insert_update_commit_raise;
drop procedure test_commit_insert_update_delete_commit_raise;
drop procedure test_commit_insert_delete_update_commit_raise;
drop procedure test_exception_commit;
drop procedure test_exception_commit_commit_raise;
drop procedure test_exception_commit_raise_commit;
drop procedure test_gg_1;
drop procedure test_commit_exception;
drop procedure test_commit2;
drop procedure test_commit3;
drop procedure test_rollback_with_exception;
drop procedure test_nest_procedure_without_commit;
drop procedure test_nest_procedure;
drop procedure test_nest_procedure1;
drop procedure test_nest_procedure2;
drop procedure test_nest_procedure_rollback;
drop procedure test_nest_procedure_select;
drop procedure test_nest_procedure_calll;
drop procedure test_commit_exception_commit;
drop procedure test_commit_exception_commit_commit;
drop procedure test_commit_exception_commit_rollback;
drop procedure test_rollback;
drop procedure test_commit_inout;
drop procedure test_rollback_inout;
drop procedure test_rollback_out;
drop procedure test_rollback1;
drop procedure pro_base13_03;
drop procedure pro_base13_04;
drop procedure pro_base13_05;
drop procedure pro_base13_06;
drop procedure pro_base13_07;
drop procedure pro_base13_08;
drop procedure proc_test_3;
drop procedure proc_test_2;
drop procedure proc_test_1;
drop procedure p_t1_for_rollback;
/*close AUTOCOMMIT cannot autocommit transaction*/
create table test(a int);
\set AUTOCOMMIT 'off'
BEGIN
insert into test values (1245);
END;
/
select * from test;
rollback;
select * from test;
\set AUTOCOMMIT 'on'
commit;
drop table if exists test;
/*
commit/rollback
Repeat the execution of 1/3/5/7 singular execution,
and the execution of the two times 2/4/6/8 doubles
*/
CREATE USER dams PASSWORD 'huawei@123' SYSADMIN;
--\c - dams
\! @abs_bindir@/gsql -d postgres -p @portstring@ -U dams -W huawei@123 -c "CREATE SCHEMA PCKG_DAMS_WEBSERVICE; CREATE SCHEMA dams;"
\! @abs_bindir@/gsql -d postgres -p @portstring@ -U dams -W huawei@123 -c "set behavior_compat_options='bind_procedure_searchpath';create or replace procedure dams.test() as begin raise notice '111';end;"
\! @abs_bindir@/gsql -d postgres -p @portstring@ -U dams -W huawei@123 -c "set behavior_compat_options='bind_procedure_searchpath';CREATE OR REPLACE PROCEDURE PCKG_DAMS_WEBSERVICE.create_temp_table(temptable VARCHAR2) AS DECLARE BEGIN EXECUTE IMMEDIATE 'drop table if exists '||temptable; CREATE TEMP TABLE TEST123(aa VARCHAR2); test(); rollback; END;"
\! @abs_bindir@/gsql -d postgres -p @portstring@ -U dams -W huawei@123 -c "set behavior_compat_options='bind_procedure_searchpath';CREATE OR REPLACE PROCEDURE PCKG_DAMS_WEBSERVICE.proc_senddata2dams_list ( ) PACKAGE IS BEGIN create_temp_table('TEST123'); create_temp_table('TEST123'); END;"
\! @abs_bindir@/gsql -d postgres -p @portstring@ -U dams -W huawei@123 -c "set behavior_compat_options='bind_procedure_searchpath';select PCKG_DAMS_WEBSERVICE.proc_senddata2dams_list();"
\! @abs_bindir@/gsql -d postgres -p @portstring@ -U dams -W huawei@123 -c "set behavior_compat_options='bind_procedure_searchpath';select PCKG_DAMS_WEBSERVICE.proc_senddata2dams_list();"
\! @abs_bindir@/gsql -d postgres -p @portstring@ -U dams -W huawei@123 -c "set behavior_compat_options='bind_procedure_searchpath';drop procedure PCKG_DAMS_WEBSERVICE.create_temp_table;"
\! @abs_bindir@/gsql -d postgres -p @portstring@ -U dams -W huawei@123 -c "set behavior_compat_options='bind_procedure_searchpath';drop procedure PCKG_DAMS_WEBSERVICE.proc_senddata2dams_list;"
\! @abs_bindir@/gsql -d postgres -p @portstring@ -U dams -W huawei@123 -c "set behavior_compat_options='bind_procedure_searchpath';drop procedure dams.test;"
\! @abs_bindir@/gsql -d postgres -p @portstring@ -U dams -W huawei@123 -c "set behavior_compat_options='bind_procedure_searchpath';drop table if exists TEST123;"
\! @abs_bindir@/gsql -d postgres -p @portstring@ -U dams -W huawei@123 -c "set behavior_compat_options='bind_procedure_searchpath';drop schema if exists PCKG_DAMS_WEBSERVICE;drop schema if exists dams;"
drop user if exists dams;
--end of file