动态SQL或非动态SQL

Question

在执行更新时，我们希望在ORA_ROWSCN (Oracle db BTW)上执行并发检查。所以我在考虑创建一个函数，在这里你可以传入你拥有的ora_rowscn，表名，列名和ID。这个函数会根据你传入的表，列和id执行一条select语句，如果返回的ora_rowscn与你传入的不同，返回true或false是相同的。

我对传入的table_name和column_name进行有效性检查，以确保它们首先存在。

FUNCTION ConcurrencyCheck (
  pi_orarowscn_in             IN NUMBER,
  pi_table_name               IN VARCHAR2,
  pi_column_name              IN VARCHAR2,
  pi_id                       IN NUMBER


)
  RETURN BOOLEAN IS

r_data_out_of_date BOOLEAN := false;

ln_orarowscn_current NUMBER := 0;

lv_sql VARCHAR2(300) := '';
lv_column VARCHAR(20) := '';
lv_table VARCHAR(20) := '';

BEGIN

SELECT table_name INTO lv_table from ALL_TABLES WHERE TABLE_NAME = pi_table_name;

IF lv_table = '' THEN
  RAISE NO_DATA_FOUND;
ELSE
  SELECT column_name INTO lv_column from USER_TAB_COLUMNS WHERE TABLE_NAME = pi_table_name AND COLUMN_NAME = pi_column_name;

  IF lv_column = '' THEN
    RAISE NO_DATA_FOUND;

  ELSE
    lv_sql := 'select ORA_ROWSCN from ' || pi_table_name || ' where ' || pi_column_name || ' = ' || pi_id || '';
    EXECUTE IMMEDIATE lv_sql INTO ln_orarowscn_current;

    IF ln_orarowscn_current <> pi_orarowscn_in THEN
      r_data_out_of_date := true;
    END IF;

  END IF;      
END IF;

RETURN r_data_out_of_date;



EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE;
    WHEN OTHERS THEN
      RAISE;

  END ConcurrencyCheck;

我不喜欢我在那里使用的动态SQL。我更希望(不是动态SQL，而是...)如下所示：

lv_sql := 'select ORA_ROWSCN from :table_name where :column_name = :id';
EXECUTE IMMEDIATE lv_sql INTO ln_orarowscn_current USING pi_table_name, pi_column_name, pi_id;

但是我一直收到一个SQL错误，说表是错的

另一种解决方案是为所有的表创建子函数，这些子函数将返回每个表的ORA_ROWSCN，并在主函数中返回一个IF ELSE来调用每个表。

我在这里寻找最佳实践。现在是可以接受动态SQL的时候了吗？或者我应该走“长”路线，为每个表创建大量的函数/过程？

谢谢!

Answer 1

至于最佳实践，在这些情况下，建议使用sys.dbms_assert包。

FUNCTION ConcurrencyCheck(
    pi_orarowscn_in             IN NUMBER,
    pi_table_name               IN VARCHAR2,
    pi_column_name              IN VARCHAR2,
    pi_id                       IN NUMBER
)
RETURN BOOLEAN IS
    ln_orarowscn_current NUMBER := 0;
    lv_sql VARCHAR2(300) := '';
BEGIN
    lv_sql := '
        select X.ora_rowscn
        from '||sys.dbms_assert.sql_object_name(pi_table_name)||' X
        where X.'||sys.dbms_assert.simple_sql_name(pi_column_name)||' = :pi_id
    ';
    execute immediate lv_sql
        into ln_orarowscn_current
        using in pi_id;

    return ln_orarowscn_current <> pi_orarowscn_in;
END ConcurrencyCheck;

注意：如果我处在您的位置上，我会给自己一段艰难的时间来实现一个元数据驱动的代码生成器，用于为每个表一次检查的函数包实现一个代码生成器，正如您可能已经猜到的那样，它将是静态PL/SQL代码-为您提供所有那些良好的编译时语法/语义检查。