Stored Procedure

Setiap akhir bulan Toko Maju Jaya mengisi ulang stok untuk puluhan produk sekaligus. Pekerjaan seperti ini beda wujud dari diskon_member() yang kita panggil per baris: pekerjaan ini adalah aksi — mengubah data baris demi baris, kadang berjam-jam — bukan komputasi yang mengembalikan nilai. Function PL/pgSQL punya satu batasan mendasar untuk pekerjaan macam ini: function selalu berjalan di dalam transaksi pemanggilnya dan tidak boleh COMMIT di tengah jalan, jadi kemajuan tidak bisa disimpan per batch. PostgreSQL 11 ke atas menyediakan objek khusus untuk celah ini: stored procedure, dibuat dengan CREATE PROCEDURE dan dijalankan dengan CALL — satu-satunya tempat logika server boleh mengelola transaksinya sendiri. Chapter ini mempraktikkan prosedur pertama kita, membedakannya dari function, lalu membuktikan fitur andalannya: COMMIT di dalam prosedur.

CREATE PROCEDURE dan CALL #

Secara tampilan, CREATE PROCEDURE nyaris sama dengan CREATE FUNCTION: nama, parameter, LANGUAGE plpgsql, body $$ ... $$. Bedanya di cara panggil dan di tidak adanya klausa RETURNS:

sql
prosedur-dasar.sql
-- CREATE PROCEDURE dan CALL: prosedur pertama Toko Maju Jaya
CREATE TABLE produk (
    id    integer PRIMARY KEY,
    nama  text NOT NULL,
    harga numeric(12,2) NOT NULL,
    stok  integer NOT NULL DEFAULT 0
);

INSERT INTO produk (id, nama, harga, stok) VALUES
    (1, 'Kopi Gayo 250g',        85000.00,  40),
    (2, 'Teh Melati 100g',       15000.00, 120),
    (3, 'Gula Kristal 1kg',      16000.00,  75),
    (4, 'Beras Premium 5kg',     68000.00,  25),
    (5, 'Susu Kental 375g',      12000.00,  90);

CREATE PROCEDURE tambah_stok(v_id integer, v_jumlah integer DEFAULT 10)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE produk SET stok = stok + v_jumlah WHERE id = v_id;
END
$$;

CALL tambah_stok(1);        -- tanpa argumen kedua: nilai DEFAULT dipakai
CALL tambah_stok(2, 25);    -- eksplisit
CALL tambah_stok(4, -5);    -- nilai negatif: mengurangi stok

SELECT id, nama, stok FROM produk ORDER BY id;

-- Parameter INOUT: nilainya masuk sebagai argumen, keluar sebagai hasil CALL
CREATE PROCEDURE naikkan_poin(INOUT poin integer, v_naik integer)
LANGUAGE plpgsql
AS $$
BEGIN
    poin := poin + v_naik;
END
$$;

CALL naikkan_poin(1200, 150);

Output yang Dihasilkan:

text
CREATE TABLE
INSERT 0 5
CREATE PROCEDURE
CALL
CALL
CALL
 id |       nama        | stok 
----+-------------------+------
  1 | Kopi Gayo 250g    |   50
  2 | Teh Melati 100g   |  145
  3 | Gula Kristal 1kg  |   75
  4 | Beras Premium 5kg |   20
  5 | Susu Kental 375g  |   90
(5 rows)

CREATE PROCEDURE
 poin 
------
 1350
(1 row)

Bedah baris pentingnya:

CALL tambah_stok(1); — prosedur dipanggil dengan perintah CALL, bukan SELECT. Parameter kedua tidak dikirim, sehingga nilai DEFAULT 10 terpakai: stok Kopi Gayo naik dari 40 ke 50.
CALL tambah_stok(4, -5); — nilai negatif sah saja sebagai argumen; Beras turun dari 25 ke 20. Prosedur tidak memvalidasi apa pun kecuali yang Anda tulis sendiri — pola RAISE EXCEPTION dari chapter Exception Handling siap dipakai di sini.
CREATE PROCEDURE naikkan_poin(INOUT poin integer, ...) — mode INOUT dari chapter Parameter Function tetap berlaku: nilai masuk, diubah, dan dikembalikan sebagai hasil CALL. Pemanggilan CALL naikkan_poin(1200, 150) menampilkan satu baris hasil 1350. Inilah “nilai keluar”-nya prosedur — pengganti RETURNS yang tidak dimilikinya.

Procedure Bukan Function #

Kesalahan paling umum pemula: memanggil prosedur lewat SELECT. PostgreSQL menolaknya dengan pesan eksplisit:

sql
select-prosedur.sql
-- Procedure tidak bisa dipanggil lewat SELECT
SELECT tambah_stok(1);

Output yang Dihasilkan:

text
psql:select-prosedur.sql:2: ERROR:  tambah_stok(integer) is a procedure
LINE 1: SELECT tambah_stok(1);
               ^
HINT:  To call a procedure, use CALL.

Error tambah_stok(integer) is a procedure beserta HINT: To call a procedure, use CALL. memastikan tidak ada ruang salah paham. Pemisahan ini disengaja, karena dua objek itu memang hidup di dunia berbeda:

Aspek	Function	Procedure
Cara memanggil	`SELECT nama_fn(...)`	`CALL nama_prosedur(...)`
Klausa hasil	`RETURNS` — skalar, `TABLE`, `SETOF`	tidak ada; keluaran hanya lewat `INOUT`
Bisa dipakai di	`WHERE`, `FROM`, `SELECT` list, index expression	hanya `CALL` — tidak bisa di query
Kelola transaksi sendiri	tidak — error `invalid transaction termination`	ya — `COMMIT`/`ROLLBACK` di badan
Khasiat alami	komputasi dan pembentukan data	aksi dan pekerjaan maintenance

Batasan function pada baris “kelola transaksi” bukan teori: CREATE FUNCTION yang berisi COMMIT akan lolos saat dibuat, lalu gagal dengan ERROR: invalid transaction termination begitu dieksekusi. Function hidup di dalam transaksi pemanggil — hanya procedure yang boleh memutuskan sendiri kapan menyimpan.

Fitur Andalan: COMMIT di Dalam Procedure #

Prosedur restok_massal berikut memproses seluruh produk, menambah stok, mencatat ke restok_log, dan menyimpan kemajuan setiap dua produk dengan COMMIT. Parameter v_gagal_di sengaja disediakan untuk uji di bagian berikutnya:

sql
transaksi-dalam-prosedur.sql
-- COMMIT di dalam procedure: kemajuan tersimpan per batch
CREATE TABLE restok_log (
    produk  text NOT NULL,
    jumlah  integer NOT NULL
);

CREATE PROCEDURE restok_massal(v_tambah integer, v_gagal_di integer)
LANGUAGE plpgsql
AS $$
DECLARE
    rec record;
    v_no integer := 0;
BEGIN
    FOR rec IN SELECT id, nama FROM produk ORDER BY id LOOP
        v_no := v_no + 1;

        IF v_no = v_gagal_di THEN
            RAISE EXCEPTION 'gagal sengaja pada produk ke-%', v_no;
        END IF;

        UPDATE produk SET stok = stok + v_tambah WHERE id = rec.id;
        INSERT INTO restok_log VALUES (rec.nama, v_tambah);

        IF v_no % 2 = 0 THEN
            COMMIT;
        END IF;
    END LOOP;

    COMMIT;
END
$$;

CALL restok_massal(5, 0);

SELECT id, nama, stok FROM produk ORDER BY id;
SELECT * FROM restok_log ORDER BY produk;

Output yang Dihasilkan:

text
CREATE TABLE
CREATE PROCEDURE
CALL
 id |       nama        | stok 
----+-------------------+------
  1 | Kopi Gayo 250g    |   55
  2 | Teh Melati 100g   |  150
  3 | Gula Kristal 1kg  |   80
  4 | Beras Premium 5kg |   25
  5 | Susu Kental 375g  |   95
(5 rows)

      produk       | jumlah 
-------------------+--------
 Beras Premium 5kg |      5
 Gula Kristal 1kg  |      5
 Kopi Gayo 250g    |      5
 Susu Kental 375g  |      5
 Teh Melati 100g   |      5
(5 rows)

FOR rec IN SELECT id, nama FROM produk ORDER BY id LOOP — pola loop record dari chapter Record dan Row Type, kali ini di dalam prosedur: satu putaran memperbarui satu produk dan mencatat log-nya.
IF v_no % 2 = 0 THEN COMMIT; END IF; — setiap dua produk, seluruh perubahan sejauh itu disimpan permanen dan transaksi baru dibuka untuk sisanya. COMMIT terakhir setelah loop menutup sisa baris ganjil.
CALL restok_massal(5, 0); — dengan v_gagal_di = 0, tidak ada kegagalan; seluruh stok naik 5 dan lima baris log tercatat. Bandingkan dengan function: pekerjaan yang sama dalam satu function hanya bisa “semua jadi atau semua batal”.

Gagal di Tengah Jalan, Kemajuan Tetap Aman #

Sekarang ujian sesungguhnya. Pemanggilan berikut memerintahkan prosedur yang sama untuk gagal sengaja di produk ketiga — setelah dua batch pertama (produk 1 dan 2) sudah COMMIT:

sql
gagal-paruh-jalan.sql
-- Gagal sengaja di produk ke-3: dua batch pertama sudah tersimpan
CALL restok_massal(20, 3);

Output yang Dihasilkan:

text

psql:gagal-paruh-jalan.sql:2: ERROR:  gagal sengaja pada produk ke-3
CONTEXT:  PL/pgSQL function restok_massal(integer,integer) line 10 at RAISE

CALL berakhir dengan error gagal sengaja pada produk ke-3. Pertanyaannya: apa nasib dua batch yang sudah tersimpan sebelum kegagalan? File berikut membuktikannya:

sql
bukti-paruh-jalan.sql
-- Bukti kemajuan yang sudah COMMIT bertahan melewati kegagalan prosedur
SELECT id, nama, stok FROM produk ORDER BY id;
SELECT * FROM restok_log ORDER BY produk;

Output yang Dihasilkan:

text
 id |       nama        | stok 
----+-------------------+------
  1 | Kopi Gayo 250g    |   75
  2 | Teh Melati 100g   |  170
  3 | Gula Kristal 1kg  |   80
  4 | Beras Premium 5kg |   25
  5 | Susu Kental 375g  |   95
(5 rows)

      produk       | jumlah 
-------------------+--------
 Beras Premium 5kg |      5
 Gula Kristal 1kg  |      5
 Kopi Gayo 250g    |      5
 Kopi Gayo 250g    |     20
 Susu Kental 375g  |      5
 Teh Melati 100g   |      5
 Teh Melati 100g   |     20
(7 rows)

Datanya bicara: Kopi Gayo dan Teh Melati naik 20 (55 ke 75, 150 ke 170) dengan dua baris log jumlah = 20 — keduanya tetap ada meskipun prosedur gagal. Adapun Gula, Beras, dan Susu tidak tersentuh sama sekali (masih 80, 25, 95) karena kegagalan terjadi sebelum giliran mereka dan transaksi yang sedang berjalan dibatalkan. Inilah perilaku yang tidak mungkin dicapai function: kemajuan batch yang sudah disimpan tidak ikut tergulung oleh kegagalan di batch berikutnya — pas untuk pekerjaan restok besar, migrasi data, atau pengarsipan bertahap di mana mengulang dari awal jauh lebih mahal daripada melanjutkan dari titik gagal.

Batasan: CALL dalam Transaksi Luar #

Kekuatan mengelola transaksi sendiri datang dengan satu aturan keras: CALL yang badannya berisi COMMIT tidak boleh dibungkus transaksi dari luar. Kalau aplikasi Anda sudah membuka BEGIN lalu memanggil prosedur:

sql
call-dalam-transaksi.sql
-- CALL yang berisi COMMIT tidak boleh dibungkus transaksi luar
BEGIN;
CALL restok_massal(1, 0);
COMMIT;

Output yang Dihasilkan:

text

psql:call-dalam-transaksi.sql:3: ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function restok_massal(integer,integer) line 17 at COMMIT

PostgreSQL menghentikan pemanggilan dengan ERROR: invalid transaction termination — prosedur tidak boleh menutup transaksi yang bukan miliknya. Prosedur dengan COMMIT internal harus dipanggil dalam mode autocommit (kondisi default psql dan sebagian besar driver), atau lewat blok DO yang dijalankan di luar transaksi eksplisit.

Function menghitung, prosedur bertindak — dan keduanya kini lengkap di tangan kita. Ada satu pola terakhir yang melengkapi perulangan data di PL/pgSQL: bagaimana berjalan di atas hasil query baris demi baris sambil memegang kendali penuh — membuka, melangkah, mengubah baris yang sedang dipijak, lalu menutup. Itu pekerjaan cursor, topik chapter berikutnya.

Referensi #

Berkas Sumber

Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.

sql
-- Bukti kemajuan yang sudah COMMIT bertahan melewati kegagalan prosedur
SELECT id, nama, stok FROM produk ORDER BY id;
SELECT * FROM restok_log ORDER BY produk;

sql
-- CALL yang berisi COMMIT tidak boleh dibungkus transaksi luar
BEGIN;
CALL restok_massal(1, 0);
COMMIT;

sql
-- Gagal sengaja di produk ke-3: dua batch pertama sudah tersimpan
CALL restok_massal(20, 3);

sql
-- CREATE PROCEDURE dan CALL: prosedur pertama Toko Maju Jaya
CREATE TABLE produk (
    id    integer PRIMARY KEY,
    nama  text NOT NULL,
    harga numeric(12,2) NOT NULL,
    stok  integer NOT NULL DEFAULT 0
);

INSERT INTO produk (id, nama, harga, stok) VALUES
    (1, 'Kopi Gayo 250g',        85000.00,  40),
    (2, 'Teh Melati 100g',       15000.00, 120),
    (3, 'Gula Kristal 1kg',      16000.00,  75),
    (4, 'Beras Premium 5kg',     68000.00,  25),
    (5, 'Susu Kental 375g',      12000.00,  90);

CREATE PROCEDURE tambah_stok(v_id integer, v_jumlah integer DEFAULT 10)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE produk SET stok = stok + v_jumlah WHERE id = v_id;
END
$$;

CALL tambah_stok(1);        -- tanpa argumen kedua: nilai DEFAULT dipakai
CALL tambah_stok(2, 25);    -- eksplisit
CALL tambah_stok(4, -5);    -- nilai negatif: mengurangi stok

SELECT id, nama, stok FROM produk ORDER BY id;

-- Parameter INOUT: nilainya masuk sebagai argumen, keluar sebagai hasil CALL
CREATE PROCEDURE naikkan_poin(INOUT poin integer, v_naik integer)
LANGUAGE plpgsql
AS $$
BEGIN
    poin := poin + v_naik;
END
$$;

CALL naikkan_poin(1200, 150);

sql
-- Procedure tidak bisa dipanggil lewat SELECT
SELECT tambah_stok(1);

sql
-- COMMIT di dalam procedure: kemajuan tersimpan per batch
CREATE TABLE restok_log (
    produk  text NOT NULL,
    jumlah  integer NOT NULL
);

CREATE PROCEDURE restok_massal(v_tambah integer, v_gagal_di integer)
LANGUAGE plpgsql
AS $$
DECLARE
    rec record;
    v_no integer := 0;
BEGIN
    FOR rec IN SELECT id, nama FROM produk ORDER BY id LOOP
        v_no := v_no + 1;

        IF v_no = v_gagal_di THEN
            RAISE EXCEPTION 'gagal sengaja pada produk ke-%', v_no;
        END IF;

        UPDATE produk SET stok = stok + v_tambah WHERE id = rec.id;
        INSERT INTO restok_log VALUES (rec.nama, v_tambah);

        IF v_no % 2 = 0 THEN
            COMMIT;
        END IF;
    END LOOP;

    COMMIT;
END
$$;

CALL restok_massal(5, 0);

SELECT id, nama, stok FROM produk ORDER BY id;
SELECT * FROM restok_log ORDER BY produk;