此链接显示如何在 Oracle 中获取过程/函数变量的类型:查看变量的类型 https://stackoverflow.com/questions/17134293/how-to-view-the-type-of-a-variable-in-pl-sql.
它通过函数“get_plsql_type_name”来实现:
create or replace function get_plsql_type_name
(
p_object_name varchar2,
p_name varchar2
) return varchar2 is
v_type_name varchar2(4000);
begin
select reference.name into v_type_name
from user_identifiers declaration
join user_identifiers reference
on declaration.usage_id = reference.usage_context_id
and declaration.object_name = reference.object_name
where
declaration.object_name = p_object_name
and declaration.usage = 'DECLARATION'
and reference.usage = 'REFERENCE'
and declaration.name = p_name;
return v_type_name;
end;
/
alter session set plscope_settings = 'IDENTIFIERS:ALL';
create or replace type my_weird_type is object
(
a number
);
create or replace procedure test_procedure is
var1 number;
var2 integer;
var3 my_weird_type;
subtype my_subtype is pls_integer range 42 .. 43;
var4 my_subtype;
begin
dbms_output.put_line(get_plsql_type_name('TEST_PROCEDURE', 'VAR1'));
dbms_output.put_line(get_plsql_type_name('TEST_PROCEDURE', 'VAR2'));
dbms_output.put_line(get_plsql_type_name('TEST_PROCEDURE', 'VAR3'));
dbms_output.put_line(get_plsql_type_name('TEST_PROCEDURE', 'VAR4'));
end;
/
begin
test_procedure;
end;
/
上述方法的问题在于它是静态的,我需要验证变量的类型,该变量可以是过程/函数范围中声明的变量的子类型。
使用上述方法我得到以下结果。
Create the type and its subtype:
create or replace type my_weird_type is object
(
a number
) NOT FINAL;
CREATE OR REPLACE TYPE my_weird_subtype UNDER my_weird_type(
b number
);
/
创建一个表并填充它:
create table test_my_weird_type(
x my_weird_type,
y my_weird_subtype
);
INSERT INTO test_my_weird_type (x,y) VALUES (my_weird_type(100),my_weird_subtype(100,200));
COMMIT;
函数创建(它有两个 my_weird_type 参数,有时我需要使用它的子类型):
create or replace function test_procedure (
inn_type my_weird_type,
out_subtype my_weird_type
) RETURN number is
var1 number;
var2 integer;
begin
dbms_output.put_line(get_plsql_type_name('TEST_PROCEDURE', 'VAR1'));
dbms_output.put_line(get_plsql_type_name('TEST_PROCEDURE', 'VAR2'));
dbms_output.put_line(get_plsql_type_name('TEST_PROCEDURE', 'INN_TYPE'));
dbms_output.put_line(get_plsql_type_name('TEST_PROCEDURE', 'OUT_SUBTYPE'));
return 1;
end;
/
以下查询:
select test_procedure(x,y) from test_my_weird_type;
给出以下输出:
NUMBER
INTEGER
MY_WEIRD_TYPE
MY_WEIRD_TYPE
然而,正确的输出是:
NUMBER
INTEGER
MY_WEIRD_TYPE
MY_WEIRD_SUBTYPE
该函数需要识别正在使用哪个子类型,因此
函数“get_plsql_type_name”需要改进。有办法做到吗?