在块中查找游标或表列的数据类型 [英] Finding the datatype of a cursor or table column in a block

问题描述

是否可以使用系统表在无块中找到游标或变量列的数据类型?虽然我知道我可以使用系统表来查找此信息，但速度会慢很多.

Is is possible to find out the datatype of a column of a cursor or variable within block without using system tables? While I understand that I can use the system tables to find out this information it would be a lot slower.

类似

declare
   my_column_data_type varchar2(30);
begin
  my_column_data_type := all_tables.table_name%type;
  dbms_output.put_line(my_column_data_type);
end;

如果不求助于dbms_sql，我将找不到任何方法，这对于我最终的目标而言可能是过大的.

I can't find any way of doing it without resorting to dbms_sql, which would be overkill for my eventual purpose.

但是，Oracle已经掌握了所有信息.如果我尝试将varchar2分配给number，那么它将立即抱怨，因此它知道数据类型是什么.

But, Oracle already has all the information to hand. If I were to try to assign a varchar2 to a number then it would complain instantly so it knows what the datatypes are.

是的，是的，我知道Oracle的版本数量是荒谬的，但这就是我们目前所拥有的数量.9i不久就要死于11，但是如果我可以的话，该代码将立即在9i上运行找到答案！但是我包括了11个，因为如果需要我可以等待更好的解决方案，

And, yes I know the number of versions of Oracle are ridiculous but that's the amount we've got at the moment... 9i is dying shortly in favour of 11 but this code'll run on 9i immediately if I can find an answer! But I included 11 as I can wait for a better solution if needs be,

推荐答案

听起来好像您想要一个自我描述的对象.意味着可以通过编程方式找到变量的类型，而无需从某些元数据视图中进行选择.只是问对象，你是什么?

It sounds as if you want a self describing object. Meaning programmatically find the type of a variable without selecting from some metadata view. Just ask the object, what are you?

在大多数情况下似乎没有必要，因为在大多数情况下我们已经知道类型(强类型).例如，过程参数通常会指定类型(数字，varchar2等).局部变量通常会通过％type表示法指定类型或将自身与数据库对象类型联系起来.

It seems unnecessary for most situations as in most cases we already know the type (strongly typed). For example, a procedures parameters will typically specify the type (number, varchar2, whatever). Local variables will typically specify the type or tie themselves to a database object type via %type notation.

在某些情况下，需要或使用弱类型的对象，例如可用于任何查询的弱类型的游标变量.过于简单的示例:

There are some situations where weakly typed objects are needed or useful, such as a weakly typed cursor variable that can be used for any query. An overly simplistic example:

create or replace procedure get_data(o_cur OUT SYS_REFCURSOR) as
begin
  OPEN o_cur FOR
  -- without changing parameter, this could select from any table
  select * from emp;
end;

现在的问题是，如果有人编码要与另一个表一起使用的游标(我故意选择了一个可怕的过程名称)，您可能会在运行时出错.像这样:

Now the problem is that you may have errors (at runtime) if someone codes the cursor to be used with another table (I chose a terrible procedure name on purpose). Something like:

declare
  l_cur sys_refcursor;
  l_row dept%rowtype;
begin
  get_data(l_cur);
  -- oops, I thought this was dept data when I coded it, Oracle didn't complain at compile time
  LOOP
    fetch l_cur
    into l_row;
    exit when l_cur%notfound;
    -- do something here
  END LOOP;
  close l_cur;
end;

这也是为什么我更喜欢强类型的游标并避免这种情况的原因.

This is also why I prefer strongly typed cursors and avoid this situation.

无论如何，对于自描述对象，可以使用内置类型的SYS.ANYDATA(类似地，对于通用集合类型，请使用SYS.ANYDATASET).我相信这是在9i中引入的.例如，此过程需要一些数据并根据类型分支逻辑:

Anyway, in the case of a self-describing object, you can use SYS.ANYDATA built in type (similarly, SYS.ANYDATASET for generic collection types). This was introduced with 9i I believe. For example, this procedure takes some data and branches logic based on the type:

CREATE OR REPLACE procedure doStuffBasedOnType(i_data in sys.anydata) is
  l_type         SYS.ANYTYPE;
  l_typecode     PLS_INTEGER;
begin
  -- test type
  l_typecode := i_data.GetType (l_type);

  CASE l_typecode
  when Dbms_Types.Typecode_NUMBER then
    -- do something with number
    dbms_output.put_line('You gave me a number');

  when  Dbms_Types.TYPECODE_DATE then
    -- do something with date
    dbms_output.put_line('You gave me a date');

  when  Dbms_Types.TYPECODE_VARCHAR2 then
    -- do something with varchar2
    dbms_output.put_line('You gave me a varchar2');

  else
    -- didn't code for this type...
    dbms_output.put_line('wtf?');

  end case;
end;

在这里，您可以根据类型进行程序化分支.并使用它:

Here you have your programatic branching based on the type. And to use it:

declare
  l_data sys.anydata;
begin
  l_data := sys.anydata.convertvarchar2('Heres a string');
  doStuffBasedOnType(l_data);
end;

-- output: "You gave me a varchar2"

希望时间不长，引起了回应;)

Hope that wasn't too long winded a response ;)

这篇关于在块中查找游标或表列的数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！