反映在PLSQL中? [英] Reflection in PLSQL?

问题描述

我正在编写一个程序来处理存储在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));，并创建一个对象实例并将其插入ANYDATA.

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

这篇关于反映在PLSQL中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！