PL/SQL Oracle: write first stored procedure

Why use stored procedures

The stored procedure stores the logic in the database so that it can be recalled without sending the entire SQL statement each time. It also helps keep business rules consistent even when called upon from different apps.

Basic PL/SQL structure

CREATE OR REPLACE PROCEDURE kurangi_stok (
    p_produk_id IN NUMBER,
    p_jumlah IN NUMBER
) AS
    v_stok_sekarang NUMBER;
BEGIN
    SELECT stok INTO v_stok_sekarang
    FROM produk
    WHERE produk_id = p_produk_id
    FOR UPDATE;

    IF v_stok_sekarang < p_jumlah THEN
        RAISE_APPLICATION_ERROR(-20001, 'Stok tidak mencukupi');
    END IF;

    UPDATE produk
    SET stok = stok - p_jumlah
    WHERE produk_id = p_produk_id;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END kurangi_stok;
/

Calling procedure

BEGIN
    kurangi_stok(p_produk_id => 101, p_jumlah => 3);
END;
/

Handle errors clearly

RAISE_APPLICATION_ERROR lets you throw app-specific error messages with codes in the range -20000 to -20999. Blocks EXCEPTION ensure changes are undone by ROLLBACK if there is an error in the process.

FOR UPDATE lock related rows

Clause FOR UPDATE locks the rows read until the transaction is complete, preventing other transactions from converting the same stock simultaneously before checking is complete.

practice;

Add a new parameter to record the history of stock changes to the log table each time this procedure is called.