UPDATE v15

Suggest edits

You can use the UPDATE command available in the SQL language in SPL programs.

You can use an expression in the SPL language wherever an expression is allowed in the SQL UPDATE command. Thus, you can use SPL variables and parameters to supply values to the update operation.

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;

The SQL%FOUND conditional expression returns TRUE if a row is updated, FALSE otherwise. See Obtaining the result status for a discussion of SQL%FOUND and other similar expressions.

The following shows the update on the employee using this procedure:

EXEC emp_comp_update(9503, 6540, 1200);

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

SELECT * FROM emp WHERE empno = 9503;
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)
Note

You can include the UPDATE command in a FORALL statement. A FORALL statement allows a single UPDATE command to update multiple rows from values supplied in one or more collections. See Using the FORALL statement for more information.

export const _frontmatter = {"title":"UPDATE"}

← Prev

SELECT INTO

Next →

Obtaining the result status