Tutorial Contoh Stored Procedure PostgreSQL

Pendahuluan

Stored Procedure di PostgreSQL memegang peran penting dan menawarkan sejumlah manfaat untuk pengelolaan database. Dalam tutorial ini, kita akan membahas cara menggunakan stored procedure di PostgreSQL untuk menangani operasi SQL dengan lebih efisien dan aman. Artikel ini ditujukan bagi software engineer dan administrator database yang ingin meningkatkan keterampilan pengelolaan database mereka dengan memanfaatkan fitur PostgreSQL yang lebih canggih.

Apa itu Stored Procedure?

Sebelum masuk ke contoh, penting untuk memahami konsep Stored Procedure. Di PostgreSQL, Stored Procedure adalah objek yang dapat dieksekusi dan sudah dikompilasi sebelumnya, berisi serangkaian perintah SQL. Tugas utamanya adalah menjalankan operasi pada database seperti insert, update, delete, dan create. Stored procedure sangat berguna untuk operasi yang melibatkan banyak langkah atau logika kompleks, sehingga manajemen transaksi lebih baik dan kerentanan database berkurang.

Catatan: Contoh dan perintah dalam tutorial ini kompatibel dengan PostgreSQL 11 dan versi yang lebih baru.

Prasyarat

Untuk tutorial ini, Anda perlu memasang dan mengonfigurasi hal-hal berikut:

1. PostgreSQL yang terpasang di mesin lokal atau server Anda.
2. Tool command line atau tool GUI seperti Beekeeper Studio untuk menjalankan perintah SQL.

Membuat Stored Procedure di PostgreSQL

Sejak dulu, PostgreSQL menggunakan keyword FUNCTION untuk mendefinisikan rutin yang dapat mengembalikan nilai. Function di PostgreSQL dapat menjalankan operasi dan wajib mengembalikan sebuah nilai atau set, yang bisa berupa nilai skalar maupun tabel. Function juga dapat digunakan di dalam query SQL.

PROCEDURE, yang diperkenalkan di PostgreSQL 11, digunakan untuk mendefinisikan rutin yang tidak mengembalikan nilai. Procedure dapat digunakan untuk menjalankan aksi seperti mengubah state database tanpa mengembalikan data secara langsung. Procedure dipanggil menggunakan statement CALL, berbeda dengan Function yang biasanya dipanggil sebagai bagian dari statement SELECT.

Menggunakan PROCEDURE untuk Memperbarui Tabel

Mari mulai dengan membuat Stored Procedure sederhana di PostgreSQL. Kita akan membuat procedure untuk memperbarui gaji seorang karyawan.

CREATE OR REPLACE PROCEDURE update_employee_salary(p_id INT, p_salary NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
   UPDATE employees SET salary = p_salary WHERE employee_id = p_id;
END;
$$;

Pada kode di atas, update_employee_salary adalah nama procedure yang menerima dua parameter - ID karyawan (p_id) dan gaji baru (p_salary).

Memanggil Stored Procedure di PostgreSQL

Untuk memanggil procedure yang baru dibuat, kita menggunakan perintah CALL seperti di bawah ini:

CALL update_employee_salary(1, 50000);

Perintah di atas akan memperbarui gaji karyawan dengan ID 1 menjadi 50000.

Penggunaan Lanjutan: Procedure dengan Logika Kondisional

Procedure juga dapat menggunakan logika yang lebih kompleks, seperti statement kondisional dan penanganan error, untuk menjalankan operasi yang lebih canggih.

Procedure ini memperbarui departemen karyawan berdasarkan kondisi yang diberikan, sekaligus menunjukkan penggunaan struktur kontrol di dalam procedure.

CREATE OR REPLACE PROCEDURE update_employee_department(employee_id INT, new_department_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
    IF new_department_id IS NOT NULL THEN
        UPDATE employees
        SET department_id = new_department_id
        WHERE id = employee_id;
    ELSE
        RAISE EXCEPTION 'Department ID cannot be null.';
    END IF;
END;
$$;

Output yang Diharapkan:

CREATE PROCEDURE

Menggunakan FUNCTION untuk Mengambil Data

Berbeda dengan PROCEDURE, FUNCTION di PostgreSQL lebih cocok untuk operasi yang mengharapkan nilai kembalian. Ini sangat berguna untuk pengambilan data dan komputasi yang perlu menjadi bagian dari query SQL. Di sini, kita akan membuat contoh function yang mengambil informasi berdasarkan kondisi tertentu.

Function ini akan mengambil detail seorang karyawan berdasarkan ID-nya. Contoh ini menunjukkan bagaimana function dapat digunakan untuk mengembalikan data yang kemudian bisa langsung dipakai di query SQL.

CREATE OR REPLACE FUNCTION get_employee_details(employee_id INT)
RETURNS TABLE(first_name VARCHAR, last_name VARCHAR, department_id INT) AS $$
BEGIN
    RETURN QUERY 
    SELECT first_name, last_name, department_id
    FROM employees
    WHERE id = employee_id;
END;
$$ LANGUAGE plpgsql;

Output yang Diharapkan:

CREATE FUNCTION

Memanggil Function

Untuk mengambil data menggunakan FUNCTION ini, Anda menggunakan statement SELECT, sama seperti melakukan query pada tabel.

SELECT * FROM get_employee_details(1);

Output yang Diharapkan:

Di sini, output-nya adalah detail karyawan dengan ID 1. Jika karyawan tersebut ada, nama depan, nama belakang, dan ID departemennya akan ditampilkan. Jika tidak ada, hasilnya akan kosong.

 first_name | last_name | department_id 
------------+-----------+---------------
 John       | Doe       | 1
(1 row)

Kesimpulan

Dalam tutorial ini, kita telah mempelajari cara menulis Stored Procedure di PostgreSQL menggunakan PROCEDURE dan FUNCTION serta cara memanggilnya. Memahami cara menggunakan Stored Procedure bermanfaat untuk organisasi kode, keamanan, dan kecepatan eksekusi yang lebih baik. Contoh-contoh ini memang sederhana, tetapi konsepnya tetap sama untuk operasi yang lebih kompleks.

Stored procedure di PostgreSQL menawarkan cara yang andal untuk membungkus logika, memastikan operasi berjalan secara efisien dan aman. Dengan mengintegrasikan stored procedure ke dalam desain database, Anda dapat membangun aplikasi database yang lebih mudah dipelihara dan andal. Gunakan panduan ini sebagai titik awal untuk menjelajahi luasnya kemungkinan yang ditawarkan kemampuan prosedural PostgreSQL.