PLSQL 中的反射?

2021-12-24 00:00:00 oracle plsql user-defined-types

我正在编写一个程序来处理存储在 ANYDATA 中的用户定义对象.对象类型和属性名称只能在运行时知道，因此我无法在声明部分为其定义 Viarable.在Java中，我可以使用反射来处理它，我可以知道类名和字段名.然后我可以通过反射访问这些字段.有没有办法在 PLSQL 中做到这一点?我现在的想法是在程序中动态创建一个 sql 字符串并执行它.但这不是我想要的.

I am writing a procedure to deal with user defined object which is stored in ANYDATA. The object type and the attribute name can only be known at run time, so I can't define the viarable for it in declare section. In Java, I can use reflection to deal with it, I can know the class name and fields name. Then I can access the fields through reflection. Is there any way to do it in PLSQL like that? What in my head right now is creating an sql string in the procedure dynamically and execute it. But it is not what I want exactly.

假设，用户 A 定义了一个 ADT 类型为 create or replace type Person_type as object (fname varchar2(10), lname varchar2(10)); 并创建一个对象实例并将其插入任何数据.

Let's say, user A defined a ADT type as create or replace type Person_type as object (fname varchar2(10), lname varchar2(10)); and create an object instance and insert it into ANYDATA.

在我的程序中，我知道我需要处理这个对象的第一个属性，即 fname.因此，如果首先知道 adt 类型，我的代码将类似于:

In my procedure, somehow I know I need to deal with the first attribute of this object, which is fname. So if know the adt type at the first place, my code will be like:

declare
  adobject A.Person_type; -- HERE! I don't know the type yet, so I can't define adobject!
  tempAnydata anydata;
  rt number;
  vbuffer varchar2(10);
  ...
begin
   select somecolumn 
   into tempAnydata 
   from sometable 
   where something='something' for update;

   rt := tempAnydata.GetObject(adobject);

   vbuffer := adobject.fname; -- HERE! I don't know the attribute name is fname!
   -- deal with vbuffer here
end;

那么我应该怎么做才能使其动态化?提前致谢.

So what should I do to make it dynamically? Thanks in advance.

推荐答案

您需要使用ANYTYPE来描述ANYDATA并确保类型正确.然后您可以使用 piecewise 和 getVarchar2 访问该属性.

You need to use ANYTYPE to describe the ANYDATA and ensure the type is correct. Then you can access the attribute using piecewise and getVarchar2.

以下大部分代码用于检查类型，如果您不关心类型安全，则不需要.

Most of the code below is for checking the type, which you don't need if you're not concerned about type safety.

返回值的函数:

create or replace function get_first_attribute(
    p_anydata in out anydata --note the "out" - this is required for the "piecewise"
) return varchar2 is
    v_typecode pls_integer;
    v_anytype anytype;
begin
    --Get the typecode, and the ANYTYPE
    v_typecode := p_anydata.getType(v_anytype);

    --Check that it's really an object
    if v_typecode = dbms_types.typecode_object then
        --If it is an object, find the first item
        declare
            v_first_attribute_typecode pls_integer;
            v_aname          varchar2(32767);
            v_result         pls_integer;
            v_varchar        varchar2(32767);
            --Variables we don't really care about, but need for function output
            v_prec           pls_integer; 
            v_scale          pls_integer;
            v_len            pls_integer;
            v_csid           pls_integer;
            v_csfrm          pls_integer;
            v_attr_elt_type  anytype;
        begin
            v_first_attribute_typecode := v_anytype.getAttrElemInfo(
                pos            => 1, --First attribute
                prec           => v_prec,
                scale          => v_scale,
                len            => v_len,
                csid           => v_csid,
                csfrm          => v_csfrm,
                attr_elt_type  => v_attr_elt_type,
                aname          => v_aname);

            --Check typecode of attribute
            if v_first_attribute_typecode = dbms_types.typecode_varchar2 then
                --Now that we've verified the type, get the actual value.
                p_anydata.piecewise;
                v_result := p_anydata.getVarchar2(c => v_varchar);

                --DEBUG: Print the attribute name, in case you're curious
                --dbms_output.put_line('v_aname: '||v_aname);

                return v_varchar;
            else
                raise_application_error(-20000, 'Unexpected 1st Attribute Typecode: '||
                    v_first_attribute_typecode);
            end if;
        end;
    else
        raise_application_error(-20000, 'Unexpected Typecode: '||v_typecode);
    end if;
end;
/

类型:

create or replace type Person_type as object (fname varchar2(10), lname varchar2(10));

create or replace type other_type as object (first_name varchar2(10), poetry clob);

试运行:

declare
    --Create records
    v_type1 person_type := person_type('Ford', 'Prefect');
    v_type2 other_type := other_type('Paula', 'blah blah...');
    v_anydata anydata;
begin
    --Convert to ANYDATA.
    --Works as long as ANYDATA is an object with a varchar2 as the first attribute.
    v_anydata := anydata.convertObject(v_type1);
    dbms_output.put_line(get_first_attribute(v_anydata));

    v_anydata := anydata.convertObject(v_type2);
    dbms_output.put_line(get_first_attribute(v_anydata));
end;
/

输出:

Ford
Paula

相关文章