表名称中的％ROWTYPE变量 [英] %ROWTYPE variable from table name

问题描述

我有一个Oracle过程，我想以某种方式通用.我想:

I've got an Oracle procedure, that I want to be somehow generic. I would like to:

1. 将表名作为varchar参数传递
2. 使用EXECUTE IMMEDIATE动态选择数据
3. 将结果存储在传递类型的%ROWTYPE变量中

1. pass a table name as a varchar parameter
2. use EXECUTE IMMEDIATE to dynamically select data
3. 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屋！