----===============1.嵌套==============
CREATE OR REPLACE PACKAGE package_001 IS
PROCEDURE testpro1(var3 int);
END package_001;
/
create or replace package body package_001 is
procedure testpro1(var3 int)
is
begin
commit;
end;
end package_001;
/

create or replace procedure proc_test3() as
context_id number;
query text;
define_column_ret int;
v1 varchar2;
begin
query := 'call package_001.testpro1(o_ret);';
context_id := dbe_sql.register_context();
rollback;
dbe_sql.sql_set_sql(context_id, query, 1);
rollback;
dbe_sql.sql_bind_variable(context_id, 'o_ret',1,10);
define_column_ret := dbe_sql.sql_run(context_id);
dbe_sql.get_variable_result(context_id,'o_ret',v1);
dbe_sql.sql_unregister_context(context_id);

--输出结果
RAISE INFO 'v1: %' ,v1;
end;
/
call proc_test3();
INFO:  v1: 
 proc_test3 
------------
 
(1 row)

drop package package_001;
NOTICE:  drop cascades to function public.testpro1(integer)
---===============1.嵌套==============
CREATE OR REPLACE PACKAGE package_001 IS
PROCEDURE testpro1(var3 int);
END package_001;
/
create or replace package body package_001 is
procedure testpro1(var3 int)
is
begin
commit;
end;
end package_001;
/

create or replace procedure proc_test3() as
context_id number;
query text;
define_column_ret int;
v1 varchar2;
proc_name varchar2;
begin
proc_name:='package_001.testpro1';
query := 'call '||proc_name||'(o_ret);';
context_id := dbe_sql.register_context();
dbe_sql.sql_set_sql(context_id, query, 1);
dbe_sql.sql_bind_variable(context_id, 'o_ret',1,10);
define_column_ret := dbe_sql.sql_run(context_id);
dbe_sql.get_variable_result(context_id,'o_ret',v1);
dbe_sql.sql_unregister_context(context_id);

--输出结果
RAISE INFO 'v1: %' ,v1;
end;
/
call proc_test3();
INFO:  v1: 
 proc_test3 
------------
 
(1 row)

drop package package_001;
NOTICE:  drop cascades to function public.testpro1(integer)

---===============2.全局变量==============

CREATE OR REPLACE PACKAGE package_001 IS
a int;
b int;
PROCEDURE testpro1(var3 int);
END package_001;
/
create or replace package body package_001 is
procedure testpro1(var3 int)
is
begin
a = 10;
raise INFO 'a:%' ,a;
commit;
end;
end package_001;
/

create or replace procedure proc_test3() as
context_id number;
query text;
define_column_ret int;
v1 varchar2;
proc_name varchar2;
begin
proc_name:='package_001.testpro1';
query := 'call '||proc_name||'(o_ret);';
context_id := dbe_sql.register_context();
dbe_sql.sql_set_sql(context_id, query, 1);
dbe_sql.sql_bind_variable(context_id, 'o_ret',1,10);

define_column_ret := dbe_sql.sql_run(context_id);

dbe_sql.get_variable_result(context_id,'o_ret',v1);
dbe_sql.sql_unregister_context(context_id);

--输出结果
RAISE INFO 'v1: %' ,v1;
end;
/
call proc_test3();
INFO:  a:10
CONTEXT:  SQL statement "call package_001.testpro1(o_ret);"
PL/pgSQL function proc_test3() line 14 at assignment
INFO:  v1: 
 proc_test3 
------------
 
(1 row)

drop package package_001;
NOTICE:  drop cascades to function public.testpro1(integer)
----========================3.savepoint-----------
CREATE OR REPLACE PACKAGE package_001 IS
a int;
b int;
PROCEDURE testpro1(var3 int);
END package_001;
/
create or replace package body package_001 is
procedure testpro1(var3 int)
is
begin
a=11;
savepoint s1;
a = 10;
ROLLBACK TO SAVEPOINT s1;
raise INFO 'a:%' ,a;
commit;
end;
end package_001;
/

create or replace procedure proc_test3() as
context_id number;
query text;
define_column_ret int;
v1 varchar2;
proc_name varchar2;
begin
proc_name:='package_001.testpro1';
query := 'call '||proc_name||'(o_ret);';
context_id := dbe_sql.register_context();
dbe_sql.sql_set_sql(context_id, query, 1);
dbe_sql.sql_bind_variable(context_id, 'o_ret',1,10);

define_column_ret := dbe_sql.sql_run(context_id);

dbe_sql.get_variable_result(context_id,'o_ret',v1);
dbe_sql.sql_unregister_context(context_id);

--输出结果
RAISE INFO 'v1: %' ,v1;
end;
/
call proc_test3();
INFO:  a:10
CONTEXT:  SQL statement "call package_001.testpro1(o_ret);"
PL/pgSQL function proc_test3() line 14 at assignment
INFO:  v1: 
 proc_test3 
------------
 
(1 row)

drop package package_001;
NOTICE:  drop cascades to function public.testpro1(integer)
----========================3.重载-----------
CREATE OR REPLACE PACKAGE package_001 IS
a int;
b int;
PROCEDURE testpro1(var3 int);
PROCEDURE testpro1(var3 int, var4 int);
END package_001;
/
create or replace package body package_001 is
procedure testpro1(var3 int)
is
begin
a = 10;
raise INFO 'a:%' ,a;
commit;
end;
PROCEDURE testpro1(var3 int, var4 int)
is
begin
a = 11;
raise INFO 'a:%' ,a;
rollback;
end;
end package_001;
/

create or replace procedure proc_test3() as
context_id number;
query text;
define_column_ret int;
v1 varchar2;
proc_name varchar2;
begin
proc_name:='package_001.testpro1';
query := 'call '||proc_name||'(o_ret,o_ret2);';
context_id := dbe_sql.register_context();
dbe_sql.sql_set_sql(context_id, query, 1);
dbe_sql.sql_bind_variable(context_id, 'o_ret',1,10);
dbe_sql.sql_bind_variable(context_id, 'o_ret2',1,10);

define_column_ret := dbe_sql.sql_run(context_id);

dbe_sql.get_variable_result(context_id,'o_ret',v1);
--dbe_sql.sql_unregister_context(context_id);

--输出结果
RAISE INFO 'v1: %' ,v1;
end;
/
call proc_test3();
INFO:  a:11
CONTEXT:  SQL statement "call package_001.testpro1(o_ret,o_ret2);"
PL/pgSQL function proc_test3() line 15 at assignment
INFO:  v1: 
 proc_test3 
------------
 
(1 row)

drop package package_001;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to function public.testpro1(integer)
drop cascades to function public.testpro1(integer,integer)

---========================3.重载-----------
CREATE OR REPLACE PACKAGE package_001 IS
a int;
b int;
PROCEDURE testpro1(var3 int);
PROCEDURE testpro1(var3 int, var4 int);
END package_001;
/
create or replace package body package_001 is
procedure testpro1(var3 int)
is
begin
a = 10;
raise INFO 'a:%' ,a;
commit;
end;
PROCEDURE testpro1(var3 int, var4 int)
is
begin
a = 11;
raise INFO 'a:%' ,a;
rollback;
end;
end package_001;
/

create or replace procedure proc_test3() as
context_id number;
query text;
define_column_ret int;
v1 varchar2;
proc_name varchar2;
begin
proc_name:='package_001.testpro1';
query := 'call '||proc_name||'(o_ret,o_ret2);';
context_id := dbe_sql.register_context();
dbe_sql.sql_set_sql(context_id, query, 1);
dbe_sql.sql_bind_variable(context_id, 'o_ret',1,10);
dbe_sql.sql_bind_variable(context_id, 'o_ret2',1,10);

define_column_ret := dbe_sql.sql_run(context_id);

dbe_sql.get_variable_result(context_id,'o_ret',v1);
dbe_sql.sql_unregister_context(context_id);

--输出结果
RAISE INFO 'v1: %' ,v1;
end;
/
CREATE OR REPLACE PACKAGE package_002 IS
PROCEDURE testpro1(var3 int);
END package_002;
/
create or replace package body package_002 is
procedure testpro1(var3 int)
is
begin
perform proc_test3(); 
commit;
end;
end package_002;
/
CREATE OR REPLACE PACKAGE package_003 IS
PROCEDURE testpro1(var3 int);
END package_003;
/
create or replace package body package_003 is
procedure testpro1(var3 int)
is
begin
perform package_002.testpro1(1);
commit;
end;
end package_003;
/
call package_003.testpro1(1);
INFO:  a:11
CONTEXT:  SQL statement "call package_001.testpro1(o_ret,o_ret2);"
PL/pgSQL function proc_test3() line 15 at assignment
referenced column: proc_test3
SQL statement "SELECT proc_test3()"
PL/pgSQL function testpro1(integer) line 3 at PERFORM
referenced column: testpro1
SQL statement "SELECT package_002.testpro1(1)"
PL/pgSQL function testpro1(integer) line 3 at PERFORM
INFO:  v1: 
CONTEXT:  referenced column: proc_test3
SQL statement "SELECT proc_test3()"
PL/pgSQL function testpro1(integer) line 3 at PERFORM
referenced column: testpro1
SQL statement "SELECT package_002.testpro1(1)"
PL/pgSQL function testpro1(integer) line 3 at PERFORM
 testpro1 
----------
 
(1 row)

drop package package_001;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to function public.testpro1(integer)
drop cascades to function public.testpro1(integer,integer)
drop package package_002;
NOTICE:  drop cascades to function public.testpro1(integer)
drop package package_003;
NOTICE:  drop cascades to function public.testpro1(integer)
-----------------------cursor---------------------------
create table t1(a int);
insert into t1 values (1);
insert into t1 values (2);
create or replace procedure p2 (c4 in int,c2 out int,c3 out int,c1 out sys_refcursor) as
va t1;
i int;
begin
open c1 for select * from t1;
begin
i = 1/0;
exception 
when others then
    c3=100;
    raise info '%', 'exception1';
end;
i=2/0;
exception 
when others then
    
	c4=100;
	c2=c4+10;
    raise info '%', 'exception2';
end;
/
select * from  p2(1);
INFO:  exception1
INFO:  exception2
 c2  | c3  | c1 
-----+-----+----
 110 | 100 | 
(1 row)

drop table t1;
drop procedure p2;