Use the UPDATE statement in an SPL program - PolarDB

The SQL UPDATE statement works in SPL programs the same way it does in SQL. The one SPL-specific extension is that SPL variables and parameters can appear anywhere an expression is allowed, so you can pass values directly from procedure parameters into the UPDATE statement without additional assignment steps.

Use UPDATE in a stored procedure

The following procedure updates an employee's salary and commission using SPL parameters:

CREATE OR REPLACE PROCEDURE emp_comp_update (
    p_empno         IN emp.empno%TYPE,
    p_sal           IN emp.sal%TYPE,
    p_comm          IN emp.comm%TYPE
)
IS
BEGIN
    UPDATE emp SET sal = p_sal, comm = p_comm WHERE empno = p_empno;

    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Updated Employee # : ' || p_empno);
        DBMS_OUTPUT.PUT_LINE('New Salary         : ' || p_sal);
        DBMS_OUTPUT.PUT_LINE('New Commission     : ' || p_comm);
    ELSE
        DBMS_OUTPUT.PUT_LINE('Employee # ' || p_empno || ' not found');
    END IF;
END;

After the UPDATE runs, SQL%FOUND indicates whether any row was affected:

Returns TRUE if at least one row was updated.
Returns FALSE if no matching row was found.

Run the procedure

Call the procedure and verify the result:

EXEC emp_comp_update(9503, 6540, 1200);

Expected output:

Updated Employee # : 9503
New Salary         : 6540
New Commission     : 1200

Query the updated row to confirm the change:

SELECT * FROM emp WHERE empno = 9503;

Expected output:

 empno |  ename   |   job   | mgr  |      hiredate      |   sal   |  comm   | deptno
-------+----------+---------+------+--------------------+---------+---------+--------
  9503 | PETERSON | ANALYST | 7902 | 31-MAR-05 00:00:00 | 6540.00 | 1200.00 |     40
(1 row)

What's next

Use a FORALL statement to update multiple rows from values in one or more collections with a single UPDATE call.