如何动态查看PL/SQL中变量的类型?

问题描述

此链接显示了如何在 Oracle 中获取过程/函数变量的类型:查看变量类型.

This link shows how to get a procedure/function variable's type in Oracle: View Type of a variable.

它通过函数get_plsql_type_name"来实现:

It does so through the function "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;
/

上述方法的问题在于它是静态的，我需要验证一个变量的类型，该变量可以是过程/函数作用域中声明的变量的子类型.

The problem with the above method is that it is static and I need to verify the type of a variable that can be a subtype of the one declared in the procedure/function scope.

使用上述方法我得到以下结果.

Using the above method I get the following.

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 参数，有时我需要使用它的子类型):

Function creation (it has two my_weird_type parameters, and sometimes I am going need to use its subtypes):

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"需要改进.有办法吗?

The function needs to recognize which subtype is being used, therefore thefunction "get_plsql_type_name" needs to be improved. Is there a way to do it?

推荐答案

您不能根据函数规范测试类型，但可以使用 ISOF( type ) 运算符或 SYS_TYPEID 函数:

You can't test the type based on the function specification but you can test the type of the passed in objects using the IS OF( type ) operator or the SYS_TYPEID function:

SQL 小提琴

Oracle 11g R2 架构设置:

CREATE type my_weird_type IS OBJECT
(
  a NUMBER
) NOT FINAL
/

CREATE TYPE my_weird_subtype UNDER my_weird_type
(
   b NUMBER
)
/

CREATE FUNCTION getType(
  i_type my_weird_type
) RETURN VARCHAR2
IS
BEGIN
  IF i_type IS OF( my_weird_subtype ) THEN
    RETURN 'subtype';
  ELSIF i_type IS OF( my_weird_type ) THEN
    RETURN 'type';
  ELSE
    RETURN 'other';
  END IF;
END;
/

CREATE FUNCTION getType2(
  i_type my_weird_type
) RETURN VARCHAR2
IS
  o_type USER_TYPES.TYPE_NAME%TYPE;
BEGIN
  SELECT type_name
  INTO   o_type
  FROM   user_types
  WHERE  typeid = SYS_TYPEID( i_type );

  RETURN o_type;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;
/

create table test_my_weird_type(
  value my_weird_type
)
/

INSERT INTO test_my_weird_type (value)
SELECT my_weird_type(1)      FROM DUAL UNION ALL
SELECT my_weird_subtype(2,3) FROM DUAL UNION ALL
SELECT NULL                  FROM DUAL
/

查询 1:

SELECT t.value.a AS a,
       TREAT( t.value AS my_weird_subtype ).b AS b,
       getType( value ),
       getType2( value )
FROM   test_my_weird_type t

结果:

|      A |      B | GETTYPE(VALUE) |  GETTYPE2(VALUE) |
|--------|--------|----------------|------------------|
|      1 | (null) |           type |    MY_WEIRD_TYPE |
|      2 |      3 |        subtype | MY_WEIRD_SUBTYPE |
| (null) | (null) |          other |           (null) |

这篇关于如何动态查看PL/SQL中变量的类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！