oracle 如何在PL/SQL中查看变量的类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17134293/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to view the type of a variable in PL/SQL?
提问by csadam
Is there a function in PL/SQL to show a variable's exact type, like the DUMP function in SQL?
PL/SQL 中是否有函数来显示变量的确切类型，如 SQL 中的 DUMP 函数？
I've tried the following
我试过以下
DECLARE
l_variable INTEGER := 1;
BEGIN
DBMS_OUTPUT.PUT_LINE (DUMP (l_variable));
END;
But it gives the following error:
但它给出了以下错误：
PLS-00204: function or pseudo-column 'DUMP' may be used inside a SQL statement only
PLS-00204：函数或伪列“DUMP”只能在 SQL 语句中使用
采纳答案by Jon Heller
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.
您可以使用PL/Scope创建此函数。但它不适用于匿名块，您需要将变量作为字符串引用。
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;
/
Example:
例子：
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
回答by Ali Avc?
as you should notice, DUMP is an overloaded function. it has 3 overloads.
您应该注意到，DUMP 是一个重载函数。它有 3 个重载。
So you can simulate the same thing within your code.
所以你可以在你的代码中模拟同样的事情。
function myDump (x Varchar2) return varchar2 is begin return('string') ; end ;
function myDump (x number) return varchar2 is begin return('integer') ; end ;
function myDump (x date) return varchar2 is begin return('date') ; end ;
the above code may not work properly but should give you the idea how to deal the problem.
上面的代码可能无法正常工作，但应该可以让您了解如何处理问题。
I hope that this will fulfil your requirements.
我希望这将满足您的要求。
Note; you can put these functions in a Package and use them accordingly.
笔记; 您可以将这些函数放在一个包中并相应地使用它们。
回答by Egor Skriptunoff
declare
a number(10,3);
type_info varchar2(400);
begin
a := 55.5;
select dump(a) into type_info from dual;
DBMS_OUTPUT.PUT_LINE(type_info);
end;
