oracle 如何在plsql中动态定义类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5973313/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to define types dynamically in plsql
提问by Navin
I have a procedure like this:
我有一个这样的程序：
create or replace procedure pname (tn varchar2) is
-- here i want to declare the variable
col1 tn%COL_ID
begin
end;
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
而且我知道我作为参数传递的每个表都将包含名为 COL_ID 的列。但我无法做到这一点，我收到了这样的错误
PLS-00487: Invalid reference to variable 'tn' PL/SQL: Item ignored PLS-00487: Invalid reference to variable 'tn' PL/SQL: Item ignored
PLS-00487：对变量“tn”的无效引用 PL/SQL：项目被忽略 PLS-00487：对变量“tn”的无效引用 PL/SQL：项目被忽略
Please help me in how to declare variables like that.
请帮助我如何声明这样的变量。
回答by tbone
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.
有趣的问题。正如 APC 所指出的，Oracle 不像其他语言那样具有反射，因此传入名称 (varchar2) 对 Oracle 没有太大帮助（尤其是在编译时）。我相信您在这里说的是您有一组表，它们都具有某些特征（以相同方式使用的 1 个或多个相同类型的列），因此您想要创建一个适用于任何一个的通用函数他们。
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;
And you'd call it like:
你会这样称呼它：
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;
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，然后强制调用 procs 以发送正确的游标（由于潜在的运行时异常而存在风险并且不太清楚）。如果编码“通用”函数，我更喜欢上述方法。
EDIT To be complete, I'll throw in the sys_refcursor example I mentioned above:
编辑为了完整起见，我将加入我上面提到的 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;
And call it like:
并称之为：
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;
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.
注意这似乎过于简单，只有 2 列（为什么不只是将参数设置为 varchar2 和数字），但是您可能要在函数中处理数十列，并且输入对象可以填充任意数量的列。
Cheers
干杯
回答by APC
The syntax is this:
语法是这样的：
declare
col1 t23.col1%type;
begin
null;
end;
/
The Oracle documentation set is online. Find it here. The information about %TYPE is in the PL/SQL User's Guide.
Oracle 文档集已联机。 在这里找到它。有关 %TYPE 的信息在PL/SQL User's Guide 中。
" In your example t23 is a table. Not a parameter with a table name."
“在您的示例中 t23 是一个表。不是带有表名的参数。”
Fair point. I was assuming that every declaration of COL1 was the same datatype, hence declaring it using one fixed table name would work for all tables. If this is not the case then I agree my solution won't work.
有道理。我假设 COL1 的每个声明都是相同的数据类型，因此使用一个固定的表名声明它适用于所有表。如果情况并非如此，那么我同意我的解决方案将不起作用。
The issue with passing a table name is the usual one: PL/SQL doesn't support it. It has not anything like reflection in Java.
传递表名的问题很常见：PL/SQL 不支持它。它不像 Java 中的反射那样。
There is always dynamic SQL but that may well be overkill. I think the OP needs to provide more details about what they are trying to achieve.
总是有动态 SQL，但这很可能是矫枉过正。我认为 OP 需要提供有关他们试图实现的目标的更多详细信息。
回答by Jeffrey Kemp
Have a look at all the data types used by COL_IDin all the tables you might pass to the procedure. Chances are they all could fit in one of the basic data types - i.e. NUMBER, VARCHAR2(4000).
查看COL_ID您可能传递给过程的所有表中使用的所有数据类型。机会是他们都可以适合基本数据类型中的一种-即NUMBER，VARCHAR2(4000)。
Create a separate procedure for each data type, call the appropriate version of the procedure (or, work out which variable to use with logic within the procedure) and don't worry about anchoring the type.
为每种数据类型创建一个单独的过程，调用该过程的适当版本（或者，确定在该过程中使用哪个变量与逻辑一起使用）并且不用担心锚定类型。
