如何在plsql中动态定义类型 [英] how to define types dynamically in plsql

问题描述

我有一个这样的程序:

create or replace procedure pname (tn varchar2) is
-- here i want to declare the variable

  col1 tn%COL_ID

begin

end;

，我知道我作为参数传递的每个表都将包含名为COL_ID的列.但是我无法做到这一点，我收到这样的错误

and I know that every table i am passing as an argument will contain the column named as COL_ID. But i am not able to do this, i am getting an error like this

PLS-00487:对变量'tn'的无效引用 PL/SQL:项目被忽略 PLS-00487:对变量'tn'的无效引用 PL/SQL:项目被忽略

PLS-00487: Invalid reference to variable 'tn' PL/SQL: Item ignored PLS-00487: Invalid reference to variable 'tn' PL/SQL: Item ignored

请帮助我如何声明这样的变量.

Please help me in how to declare variables like that.

推荐答案

有趣的问题.正如APC所指出的那样，Oracle的话语表达没有其他语言那么好，因此传入名称(varchar2)并不能对Oracle产生太大的帮助(特别是在编译时).我相信您在这里所说的是，您有一组表，这些表都共享某些特征(1个或多个以相同方式使用的同一类型的列)，因此您想创建一个通用函数，该函数可用于任何他们.

Interesting question. As APC noted, Oracle doesn't have reflection per say like other languages, so passing in the name (varchar2) doesn't help Oracle much (esp at compile time). What I believe you are saying here is that you have a set of tables that all share certain characteristics (1 or more columns of the same type used in the same way), so you want to make a generic function that will work for any of them.

您可以执行此操作，但是必须定义一个对象类型，该对象类型定义各种表共享的公用列和类型.说这是以下两列:

You can do this, but you'll have to define an object type that defines the common columns and types that your various tables share. Say this is the following 2 columns:

create or replace type my_obj as object (name varchar2(100), num number);

现在，您的函数(或过程)将接受此类型作为参数:

Now your function (or procedure) would accept this type as a param:

create or replace function my_fn(obj my_obj) return varchar2 is
  begin
    -- do something with object
    return obj.name || ',' || obj.num;
  end;

您会这样称呼它:

declare
    obj my_obj;
    rv varchar2(1000);
  begin
    for rec in (select * from sometable)
    loop
      obj := my_obj(rec.some_varchar_col, rec.some_number_col);
      select my_fn(obj) into rv from dual;
      dbms_output.put_line(rv);
    end loop;
  end;

我能想到的另一种方法是接受弱类型的sys_refcursor，然后强制调用proc发送正确的游标(由于潜在的运行时异常而导致的危险，并且不太清楚).如果编码泛型"函数，我更喜欢上面的方法.

The only other way that I can think of is to accept a weakly typed sys_refcursor and then force calling procs to send in a correct cursor (risky due to potential runtime exceptions and not very clear). I prefer the above approach if coding a "generic" function.

编辑 为了完整起见，我将输入上面提到的sys_refcursor示例:

EDIT To be complete, I'll throw in the sys_refcursor example I mentioned above:

create or replace procedure my_proc(cur sys_refcursor) is
  v_name varchar2(100);
  v_num number;
begin
  loop
    fetch cur into v_name, v_num;
    exit when cur%notfound;

    -- do something with our common fields
    dbms_output.put_line(v_name || ',' || v_num);
  end loop;
end;

并这样称呼它:

declare
  v_cur sys_refcursor;
begin
  open v_cur for select my_name, my_num from some_table;
  my_proc(v_cur);
  close v_cur;
end;

注意 仅2列似乎太琐碎了(为什么不将params设置为varchar2和number)，但是您可能要在函数中处理数十个列，并且输入对象可以填充任意数量的列.

NOTE This seems overly trivial with just 2 columns (why not just set params as varchar2 and number), but you may have dozens of columns that you want to work on in the function, and the input object can have any number of them populated.

欢呼

这篇关于如何在plsql中动态定义类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！