UPSERT功能的存储过程 [英] Stored Procedure for UPSERT functionality

问题描述

下面是我的表中的ID column is Primary Key.另外两列是字符串.

Below is my table in which ID column is Primary Key. And other two columns are string.

我正在使用一个名为XpressMP的新数据库.

I am using a new Database named XpressMP.

Column Name                
-------
ID             PrimaryKey                 
SEARCHES       String
ACCOUNT        String

我正在尝试在这里实现UPSERT的功能-

I am trying to achieve functionality of UPSERT here-

If ID doesn't exists here then insert a new record.
And ID exists then update the record.

我知道如果我正在使用Oracle，那么我可以使用MERGE sql命令，但是该数据库中不支持MERGE，并且到目前为止还没有其他命令.但是我相信我可以使用Stored Procedure做同样的事情.

I know If I am working with Oracle then I can use MERGE sql command but MERGE is not supported in that database and there is no other command for that as of now. But I believe I can do the same thing with Stored Procedure.

谁能提供一些建议，说明我如何使用存储过程来做同样的事情?因为存储过程将在那里工作.

Can anyone provide some suggestions how can I do the same thing with Stored Procedure? As stored procedure will work there.

已更新:-

public final static String INSERT = "BEGIN"
+" INSERT INTO TABLE (ID, SEARCHES, ACCOUNT) VALUES (?, ?, ?)"
+" EXCEPTION"
+" WHEN DUP_VAL_ON_INDEX THEN"
+" UPDATE TABLE"
+" SET SEARCHES = ?, ACCOUNT = ?"
+" WHERE ID = ?"
+" END";

每当我尝试执行上述存储过程

Whenever I try to execute the above stored procedure like this

preparedStatement = dbConnection.prepareStatement(INSERT);

preparedStatement.setString(1, String.valueOf(userId));
preparedStatement.setString(2, Constants.getaAccount(userId));
preparedStatement.setString(3, Constants.getaAdvertising(userId));
preparedStatement.executeUpdate();

我有例外吗?这是执行它的正确方法吗?

I get exception? Is this the right way to execute it?

推荐答案

您可以使用DUP_VAL_ON_INDEX异常来处理它.
您可以在您的过程中添加以下代码，该代码应符合您的要求.

You can handle it using the DUP_VAL_ON_INDEX exception.
You can add the below code in your procedure, which should meet your requirement.

CREATE OR REPLACE PROCEDURE TABLE_UPSERT (v_id IN NUMBER,
                                          v_searches IN VARCHAR2(20),
                                          v_account IN VARCHAR2(20)) AS
BEGIN
  INSERT INTO table (id, searches, account) VALUES (v_id, v_searches, v_account) ;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    UPDATE TABLE
    SET searches = v_searches, account = v_account
    WHERE id = v_id;
END;
/

您可以阅读有关DUP_VAL_ON_INDEX异常的更多信息此处和此处.

You can read more about DUP_VAL_ON_INDEX Exception here and here.

另一种选择是在检查计数后插入或更新数据.

Another option would be to insert or update the data after checking the count.

DECLARE
  l_count number;
BEGIN
  SELECT count(*) 
  INTO l_count
  FROM table 
  WHERE id = (value);

  IF l_count = 0 THEN
    INSERT into table VALUES ....;
  ELSE
    UPDATE table SET searches = .., account = ..;
  END IF;
END;

这篇关于UPSERT功能的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！