java - 通过传递表名和列名从 Oracle 表中删除行

Question

我有一个 jsp 页面，用户可以在其中选择表名、列名和列值，在这三个条件下，我想从数据库中删除所有匹配的行。有没有办法在oracle中通过表名，列名和列值来删除表中的某些行？任何例子都会帮助我..谢谢

Answer 1

当您提供表名和列名时，我会担心 SQL 注入(inject)攻击。您可以创建一个 Oracle 函数来删除所需的记录，并在删除行之前测试是否满足某些条件:

CREATE OR REPLACE
FUNCTION delete_record (
   p_table  IN VARCHAR2,
   p_column IN VARCHAR2,
   p_value  IN VARCHAR2
)
RETURN NUMBER
AS
   v_table   user_tables.table_name%TYPE;
   v_columns user_tab_cols.column_name%TYPE;
BEGIN
   -- Check table exists in DB
   SELECT table_name
     INTO v_table
     FROM user_tables
    WHERE table_name = UPPER(p_table);

   -- Check column exists in DB table
   SELECT column_name
     INTO v_colums
     FROM user_tab_cols
    WHERE table_name = UPPER(p_table)
      AND column_name = UPPER(p_column);

   EXECUTE IMMEDIATE 
      'DELETE FROM '||DBMS_ASSERT.SIMPLE_SQL_NAME(p_table)||
      ' WHERE '||DBMS_ASSERT.SIMPLE_SQL_NAME(p_column)||' = :col_value'
     USING p_value;

   RETURN SQL%ROWCOUNT;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
        -- Either return -1 (error) or log an error etc.
        RETURN -1;
   WHEN others
   THEN
        <Your exception handling here>
END delete_record;
/

这(或类似的东西)将检查提供的表和列变量是否存在于数据库中，然后删除记录并返回删除的记录数。如果删除的号码有问题，您可以发出回滚语句，如果没问题，那么您可以发出提交。

当然，如果您想提供完全限定的表名(推荐)，那么您可以使用 DBMS_ASSERT.QUALIFIED_SQL_NAME 函数而不是 DBMS_ASSERT.SIMPLE_SQL_NAME 函数。

希望对你有帮助

编辑:回应 jack 关于添加日期从和日期到的问题。

如果您添加两个传递给函数的新条件:

CREATE OR REPLACE
FUNCTION delete_record (
   p_table     IN VARCHAR2,
   p_column    IN VARCHAR2,
   p_value     IN VARCHAR2,
   p_date_from IN DATE,
   p_date_to   IN DATE
)

然后你需要扩展 EXECUTE IMMEDIATE :

EXECUTE IMMEDIATE 
  'DELETE FROM '||DBMS_ASSERT.SIMPLE_SQL_NAME(p_table)||
  ' WHERE '||DBMS_ASSERT.SIMPLE_SQL_NAME(p_column)||' = :col_value'||
  ' AND date BETWEEN :date_from AND :date_to'
 USING p_value,
       p_date_from,
       p_date_to;

注意这假定您在表中的日期列称为“日期”。目前我面前没有 SQL 界面，但这应该足够接近您需要的界面才能让它正常工作。

如果您将 p_date_XXXX 参数作为 VARCHAR2 而不是 DATE 类型传递，那么在将它们传递到动态 SQL 之前，您需要“TO_DATE”这些值。

例如

EXECUTE IMMEDIATE 
  'DELETE FROM '||DBMS_ASSERT.SIMPLE_SQL_NAME(p_table)||
  ' WHERE '||DBMS_ASSERT.SIMPLE_SQL_NAME(p_column)||' = :col_value'||
  ' AND date BETWEEN :date_from AND :date_to'
 USING p_value,
       TO_DATE(p_date_from, <date_format>),
       TO_DATE(p_date_to, <date_format>);