动态更新数据库列的数据库过程

Question

要求是只更新从前端编辑的那些列。我使用的逻辑是将 2 个数组从 java 代码发送到数据库过程。

第一个数组是：Column_array其中包含必须更新的列名。

&

第二个数组：value_array包含对column_array负责的列的值。

从爪哇：

Array value_array = ((OracleConnection) dbConnection).createOracleArray("STRING_ARRAY",
                valueList.toArray());
        Array param_array = ((OracleConnection) dbConnection).createOracleArray("STRING_ARRAY",
                            paramList.toArray());
 stmt = dbConnection.prepareCall(SqlConstants.UPDATE_SUBSCRIBER_CONFIG_IN_BOLTES);//
            stmt.setLong(1, 3628);
            stmt.setLong(2, 3629);
            stmt.setLong(3, 3632);
            stmt.setArray(4, param_array);
            stmt.setArray(5, value_array);
            int count = stmt.executeUpdate();

现在在数据库端：

如何遍历此列表以在 SET 子句中更新和设置它???

 PROCEDURE update_subscriber_config (
    p_app_id        VARCHAR2,
    p_service_id     VARCHAR2,
    p_pubsub_id       VARCHAR2,
    column_list     string_array,
    value_list      string_array
)
    AS
BEGIN
FOR a IN 1..column_list.count LOOP
        update  bolt_oracle_pubsub_config set 
            column_list(a)=value_list(a),
             ...how to do iteration here???
      where  APP_ID = p_app_id AND SERVICE_ID =  p_service_id AND PUBSUB_ID = p_pubsub_id;
       END LOOP;
END update_subscriber_config;

请帮忙。

Answer 1

我找到了比数组迭代更简单的解决方案。

解决方案是使用COALESCE方法。

 PROCEDURE TEST (
      p_app_id       NUMBER,
    p_service_id   NUMBER,
    p_pubsub_id    NUMBER,
    p_pubsub_name       VARCHAR2,
    p_host              VARCHAR2,
    p_user_name           VARCHAR2,
    p_auth_key              VARCHAR2
)
    AS
BEGIN
     update bolt_elastic_pubsub_config set 
      PUBSUB_NAME = coalesce(p_pubsub_name, PUBSUB_NAME),
       HOST = coalesce(p_host, HOST),
        USER_NAME = coalesce(p_user_name, USER_NAME),
        AUTH_KEY = coalesce(p_auth_key, AUTH_KEY)
     where  APP_ID = p_app_id AND SERVICE_ID =  p_service_id AND PUBSUB_ID = p_pubsub_id;
END TEST;

Answer 2

您必须使用动态 SQL 来组装定制的更新语句。它将看起来像这样：

PROCEDURE update_subscriber_config (
    p_app_id        VARCHAR2,
    p_service_id     VARCHAR2,
    p_pubsub_id       VARCHAR2,
    column_list     string_array,
    value_list      string_array
)
AS
   stmt varchar2(32767);
BEGIN
    stmt := 'update  bolt_oracle_pubsub_config set ';
    FOR a IN 1..column_list.count LOOP
        if a != 1 then
           stmt := stmt ||', ';
        end if;
        stmt := stmt || column_list(a) ||'=''' ||value_list(a)||'''';
       END LOOP;
       stmt := stmt ||
             ' where  APP_ID = :p1 AND SERVICE_ID = :p2 AND PUBSUB_ID = :p3';
      execute immediate stmt using p_app_id , p_service_id , p_pubsub_id;
END update_subscriber_config;

这是一个非常粗略的暗示，因为它假设所有传递的列都可以被视为字符串。如果您的表包含数字或日期列，则应考虑处理数据转换，因为这可能会成为问题。

动态 SQL 很难，因为它将编译错误转换为运行时错误。在您的情况下，您有一段代码，每次调用它时都可能执行不同的更新语句。因此，测试此过程完全是一场噩梦。这意味着您严重依赖前端传递具有有效内容的数组。

Answer 3

也许您可以尝试避免使用其中一个数组，这些数组是列数组，因为如果值为空，Oracle SQL将容忍该数组。

下面是伪代码。

PROCEDURE update_subscriber_config (p_app_id             VARCHAR2,
                                    p_service_id         VARCHAR2,
                                    p_pubsub_id          VARCHAR2,
                                    p_first_column       VARCHAR2,
                                    p_second_column      VARCHAR2,
                                    --column_list     string_array,
                                    i_array           IN my_array_type)
AS
BEGIN
   FORALL i IN 1 .. i_array.COUNT
      UPDATE bolt_oracle_pubsub_config
         SET your_first_column =
                TREAT (i_array (i) AS my_array_type).column_array_name,
             your_second_column =
                TREAT (i_array (i) AS my_array_type).second_column_array_name
       WHERE     APP_ID = p_app_id
             AND SERVICE_ID = p_service_id
             AND PUBSUB_ID = p_pubsub_id;
END update_subscriber_config;

如果更新 SQL 中声明的任何列为空，则在执行更新后，它在表中将为空或空。话虽如此，请确保填充所有NOT NULL或必填列。

我认为由于容易出错而避免动态SQL更新。如果您在不使用动态SQL的情况下获得结果，那么为什么要使用动态SQL更新的麻烦。