如何在PL/SQL中查看变量的类型? [英] How to view the type of a variable in PL/SQL?
本文介绍了如何在PL/SQL中查看变量的类型?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
PL/SQL中是否有一个函数可以显示变量的确切类型,例如SQL中的DUMP函数?
Is there a function in PL/SQL to show a variable's exact type, like the DUMP function in SQL?
我尝试了以下
DECLARE
l_variable INTEGER := 1;
BEGIN
DBMS_OUTPUT.PUT_LINE (DUMP (l_variable));
END;
但是会出现以下错误:
PLS-00204:函数或伪列"DUMP"可在SQL内部使用 仅声明
PLS-00204: function or pseudo-column 'DUMP' may be used inside a SQL statement only
推荐答案
You can create this function using PL/Scope. But it won't work with anonymous blocks, and you'll need to reference the variable as a string.
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;
/
NUMBER
INTEGER
MY_WEIRD_TYPE
MY_SUBTYPE
这篇关于如何在PL/SQL中查看变量的类型?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文