表名称中的%ROWTYPE变量 [英] %ROWTYPE variable from table name
问题描述
我有一个Oracle过程,我想以某种方式通用.我想:
I've got an Oracle procedure, that I want to be somehow generic. I would like to:
- 将表名作为
varchar
参数传递 - 使用
EXECUTE IMMEDIATE
动态选择数据 - 将结果存储在传递类型的
%ROWTYPE
变量中
- pass a table name as a
varchar
parameter - use
EXECUTE IMMEDIATE
to dynamically select data - store the result in the
%ROWTYPE
variable of passed type
第三点似乎是一个问题.我不确定是否可以在过程主体内部动态创建类型.拥有这样的东西将是很棒的:
The third point seems to be a problem. I'm not sure if i can create a type dynamically inside the procedure body. It would be great to have something like this:
procedure CHANGE_GENERIC(tableName in VARCHAR2, someOldVal in integer,
someNewVal in integer) is
v_sql varchar2(200);
begin
v_sql := 'select * from ' || tableName || 'where ID = ' || someOldVal;
EXECUTE IMMEDIATE v_sql1 into **myDynamicRowThatIDontHave**;
-- some other code
end;
推荐答案
您可能无法做到这一点(至少没有用).
You probably can't do this (at least not usefully).
您可以构造一个完整的匿名PL/SQL块
You could construct an entire anonymous PL/SQL block
v_plsql := 'DECLARE ' ||
' l_row ' || p_table_name || '%rowtype; ' ||
'BEGIN ' ||
' SELECT * ' ||
' INTO l_row ' ||
' FROM ' || p_table_name ||
' WHERE id = ' || p_some_old_value || ';' ||
...
EXECUTE IMMEDIATE v_plsql;
但是,通常,在开始在运行时开始求助于动态PL/SQL之前,您真的很想退后一步,评估是否有更简单的解决方案来解决您遇到的任何问题.例如,有许多框架可以为每个表动态生成CRUD程序包.那是使用动态PL/SQL,但它只是构建的一部分而已,而不是每次您要更新数据时都这样做.
In general, though, long before you start resorting to dynamic PL/SQL at runtime, you really want to take a step back and assess whether there isn't an easier solution to whatever problem you have. There are any number of frameworks, for example, that dynamically generate CRUD packages for each of your tables. That's using dynamic PL/SQL but it's only doing it once as part of a build rather than doing it every time you want to update data.
这篇关于表名称中的%ROWTYPE变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!