sql - 如何在plsql中动态定义类型

Question

我有这样的程序:

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

  col1 tn%COL_ID

begin

end;

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

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

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

Answer 1

有趣的问题。正如 APC 指出的那样，Oracle 不像其他语言那样具有反射功能，因此传入名称 (varchar2) 对 Oracle 帮助不大(尤其是在编译时)。我相信你在这里说的是你有一组表，它们都具有某些特征(一个或多个相同类型的列以相同的方式使用)，所以你想制作一个适用于任何一个的通用函数他们。

您可以这样做，但您必须定义一个对象类型，该对象类型定义您的各种表共享的公共(public)列和类型。假设这是以下 2 列:

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

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

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，然后强制调用 procs 发送正确的游标(由于潜在的运行时异常而存在风险，而且不是很清楚)。如果编写“通用”函数，我更喜欢上述方法。

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

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 列(为什么不将参数设置为 varchar2 和数字)这似乎过于微不足道了，但是您可能有几十个要在函数中处理的列，并且输入对象可以填充任意数量的列。

干杯