I am trying to create a stored procedure in PL/SQL that can update values by taking 3 parameters

1. old_value
2. new_value
3. column_name

How can I achieve this without having to rewrite the set/update statement repeatedly?

This code does not work:

CREATE OR REPLACE PROCEDURE dp_replace_values (
    old_value varchar2, new_value varchar2, column_name varchar2
) IS
    
BEGIN
    UPDATE dp_mock_data
    SET
        first_name = case when column_name = 'first_name' then old_value end,
        last_name = case when column_name = 'last_name' then new_value end
    WHERE
        last_name = old_value;

END dp_replace_values;

The code below does work, but it keeps repeating the update and set statements.

CREATE OR REPLACE PROCEDURE dp_replace_values (
    old_value varchar2, new_value varchar2, column_name varchar2
) IS
    
BEGIN
    
    IF column_name = 'first_name' then

    UPDATE dp_mock_data
    SET
        first_name = new_value
    WHERE
        first_name = old_value;

    ELSIF column_name = 'last_name' then
    
     UPDATE dp_mock_data
    SET
        last_name = new_value
    WHERE
        last_name = old_value;
    end if;

END dp_replace_values;

CodePudding user response：

You'd have to use dynamic SQL.

CREATE OR REPLACE PROCEDURE dp_replace_values (
    old_value varchar2, 
    new_value varchar2, 
    column_name varchar2
) 
IS
BEGIN
  EXECUTE IMMEDIATE 'UPDATE dp_mock_data ' ||
                    '   SET ' || column_name || ' = :1 '
                    ' WHERE ' || column_name || ' = :2 '
    USING new_value, old_value;
END;

In reality, you'd need to add logic to prevent SQL injection. At a minimum, using dbms_assert to verify that column_name is a SQL identifier. But probably more-- I'd want to query all_tab_columns to confirm that column_name is actually the name of a column in the dp_mock_data table. You'd need to add code if your system happens to use any case-sensitive column names. You'd need to add code to log the statement that gets generated before executing it in order to make debugging possible.

If you go the dynamic SQL route, you make maintaining the code harder. You turn a lot of compilation errors into runtime errors. You make debugging harder. You eliminate things like the ability to look for dependencies between objects using the database's data dictionary. And the benefit tends not to be particularly large. It's generally easy to create an update procedure for each column or to create a procedure that lets you update one or more columns and leave the other columns unchanged. And you'd usually want to specify a key when you do that update rather than updating every row with the same old_value (if that could be multiple rows, I'd suspect you want to normalize your data model and just update the lookup table)