Programming & Customizing MariaDB

MariaDB bukan hanya sekadar alat penyimpanan dan pengambilan data, itu juga merupakan lingkungan yang kuat untuk mengembangkan aplikasi yang tangguh dan disesuaikan. Dengan memahami bagaimana menggunakan fitur-fitur pemrograman dan penyesuaian yang disediakan oleh MariaDB, Kamu dapat mengoptimalkan kinerja basis datamu, meningkatkan keamanan, dan menciptakan solusi yang sesuai dengan kebutuhan bisnismu.

Dalam modul ini, Kamu akan mempelajari konsep dasar pemrograman dengan menggunakan bahasa SQL, serta bagaimana memanfaatkan stored procedures, triggers, dan functions untuk meningkatkan fungsionalitas dan efisiensi MariaDB.

Tempat menjalankan query

Menggunakan console dari phpmyadmin

Mengguanakan Datagrip dari jetbrains

• https://www.jetbrains.com/datagrip/download/#section=windows

Menggunakan Command Prompt atu Powershell

• bukan command promt atau powershell
• pindahkan ke direktori bin pada mysql xampp\mysql\bin
• ketikkan mysql -u [username_phpmyadmin/database] -p (username tidak menggunakan kurung siku)
• example : mysql -u root -p
• kemudian masukkan password database kamu, jika tidak menggunakan password bisa langsung klik enter.

Programmatic & Compound Statements

Compound statements dalam SQL adalah sekelompok pernyataan SQL yang dieksekusi bersama sebagai satu unit. Pernyataan-pernyataan ini memungkinkan kita untuk mengendalikan alur eksekusi query, mempermudah penanganan error, dan melakukan transaksi dengan lebih terstruktur. Compound Statements biasanya digunakan dalam Stored Procedure, Function, atau Triggers.

Untuk menjalankan Compound Statements, kita harus membuat titik dimulai dan titik berakhirnya suatu Compound Statements dengan BEGIN dan END.

BEGIN
	[COMPUND STATEMENTS]
END

BEGIN dan END juga dapat dibuat dalam bentuk nested. Setiap blok BEGIN dan END dapat membuat variabel lokal yang hanya dapat diakses dalam blok tersebut.

Dapat dilihat pada gambar, pada outer block dideklarasi variabel bernama x dengan tipe TINYINT. Lalu, pada inner block dideklarasi kembali variabel bernama x dengan tipe CHAR serta dideklarasi variabel bernama y. Ketika dilakukan select dalam inner block maka yang diambil adalah nilai x yang baru (bertipe CHAR). Ketika dilakukan select dalam outer block maka x akan kembali bertipe TINYINT karena variabel x yang bertipe CHAR hanya dapat diakses pada inner block, begitu pula dengan variabel y yang hanya dapat diakses pada Inner block. Jika x tidak dideklarasi dalam inner block maka x tetap dapat diakses pada inner block. Jadi, inner block dapat menggunakan variabel dari outer block namun outer block tidak dapat menggunakan variabel dari inner block.

Untuk menjalankan Compound Statements di luar stored procedure, fungsi, atau trigger, kita perlu menggunakan DELIMITER serta BEGIN NOT ATOMIC.

DELIMITER |
BEGIN NOT ATOMIC
	[COMPUND STATEMENTS]
END |
DELIMITER ;

Hal penting yang perlu dilakukan dalam penulisan Compound Statements adalah ; (titik koma). Setiap statement harus diakhiri dengan titik koma, kecuali SELECT ... INTO.

1. Variabel

Deklarasi Variabel

DECLARE var_name [, var_name] [ROW] TYPE OF](/informatika-itts/modul-perancangan-basis-data/wiki/ROW]-TYPE-OF) type [DEFAULT value]

Misalkan jika ingin membuat variabel nama bertipe VARCHAR(255).

DECLARE nama VARCHAR(255);

Jika ingin menambahkan default value atau nilai awal dari suatu variabel, maka sintaksnya

DECLARE nama VARCHAR(255) DEFAULT 'ABI';

Selain itu, kita juga dapat menggunakan sintaks TYPE OF untuk mengambil tipe data dari suatu kolom seperti berikut.

DECLARE nama TYPE OF pembeli.nama_depan DEFAULT 'ABI';

Pada contoh di atas dilakukan deklarasi variabel nama yang tipe datanya diambil dari tabel pembeli, kolom nama_depan dan memiliki nilai default ‘ABI’.

Kita juga dapat menggunakan sintaks ROW TYPE OF untuk mengambil tipe data dari suatu baris seperti berikut.

DECLARE data_pembeli ROW TYPE OF pembeli;
SELECT data_pembeli.nama_depan, data_pembeli.alamat;

Pada contoh di atas terdapat variabel data_pembeli yang tipe data serta nama kolomnya semua diambil dari tabel pembeli sehingga kita dapat mengakses nama-nama kolom yang ada di tabel pembeli namun tidak dengan isinya.

Pengisian Nilai pada Variabel

SET var_name = value [, var_name = value]

Misalkan kita ingin mengisi variabel nama dengan nilai ‘Andi’, maka bisa menggunakan sintaks berikut.

DECLARE nama VARCHAR(255);
SET nama = 'Andi';
# menampilkan nilai dari variabel nama
SELECT nama;

Output:

atau jika kita ingin mengisi variabel dari nilai suatu kolom pada tabel, maka bisa menggunakan sintaks berikut.

DECLARE nama TYPE OF pembeli.nama_depan;
SET nama = (SELECT nama_depan FROM pembeli WHERE id_pembeli = 10);
# menampilkan nilai dari variabel nama
SELECT nama;

Pada contoh di atas, kita mengisi variabel nama dengan kolom nama_depan dari tabel pembeli yang memiliki id_pembeli 10. Kita dapat melakukan hal ini hanya ketika hasil select mengembalikan 1 baris dan 1 kolom.

Output:

Selain menggunakan SET kita juga dapat menggunakan SELECT INTO untuk memberi nilai pada suatu variabel.

DECLARE nama1, nama2 TYPE OF pembeli.nama_depan;
SELECT nama_depan, nama_belakang INTO nama1, nama2
FROM pembeli LIMIT 1;
SELECT nama1, nama2;

Pada contoh di atas terdapat variabel dengan nama nama1 dan nama2 , lalu kita memasukkan nilai nama_depan dan nama_belakang dari tabel pembeli ke dalam variabel nama1 dan nama2.

Output:

Mengubah Tipe Data Variabel

DECLARE nama TYPE OF pembeli.nama_depan;
SET nama = (SELECT nama_depan FROM pembeli WHERE id_pembeli = 10);
SELECT CAST(nama AS VARCHAR(2)) AS nama;

Kita ingin mengubah tipe data variabel nama yang tadinya sama dengan kolom nama_depan dari tabel pembeli menjadi VARCHAR dengan panjang 2 karakter.

Output:

2. Kondisi

Pada mariadb terdapat 2 cara untuk melakukan pengkondisian yaitu dengan menggunakan IF … ELSE atau CASE … WHEN.

IF ELSE

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF;

Contoh penggunaan IF ELSE untuk menentukan nilai apa yang didapat oleh seorang mahasiswa dengan nilai 70.

DECLARE TotalMark INT;
SET @nilai= 70;

IF @nilai >= 80 THEN
	SELECT 'A' AS Nilai;
ELSEIF @nilai >= 70 AND @nilai < 80 THEN
	SELECT 'AB' AS Nilai;
ELSEIF @nilai >= 65 AND @nilai < 70 THEN
	SELECT 'B' AS Nilai;
ELSEIF @nilai >= 60 AND @nilai < 65 THEN
	SELECT 'BC' AS Nilai;
ELSE
	SELECT 'C' AS Nilai;
END IF;

Output:

CASE WHEN

keyword ini mirip dengan switch, dimana kita bisa melakukan switch value sesuai dengan kondisi yang diberikan

CASE case_value
	WHEN when_value THEN statement_list
	[WHEN when_value THEN statement_list] ...
	[ELSE statement_list]
END CASE;

Berikut ini adalah contoh penggunaan CASE WHEN pada prosedur atau fungsi.

DECLARE jenis_kelamin TYPE OF pembeli.jenis_kelamin DEFAULT NULL;
  CASE jenis_kelamin
    WHEN 'L' THEN SELECT 'Laki-Laki';
    WHEN 'P' THEN SELECT 'Perempuan';
    ELSE BEGIN END;
  END CASE;

Dengan query di atas, program akan menampilkan ‘Laki-Laki’ ketika variabel jenis_kelamin bernilai L dan akan menampilkan Perempuan ketika variabel jenis_kelamin bernilai P.

Terdapat sintaks yang berbeda ketika kita menggunakan IF ELSE dan CASE WHEN pada statement SELECT.

SELECT CONCAT(nama_depan, ' ', nama_belakang) AS nama_lengkap,
	CASE jenis_kelamin
		WHEN 'L' THEN 'Laki-Laki'
		WHEN 'P' THEN 'Perempuan'
		ELSE NULL
	END AS jenis_kelamin
FROM pembeli;

Dapat dilihat pada contoh di atas bahwa blok CASE diakhiri dengan END AS jenis_kelamin yang mana baris tersebut digunakan untuk memberikan nama atau alias kepada hasil CASE yang telah dilakukan. Jika pada IF, ENDIF perlu ditambahkan dengan alias juga, misalkan END IF AS jenis_kelamin. Hal tersebut yang membedakan penggunaan IF dan CASE pada SELECT statement.

3. Iterasi

seperti bahasa pemrograman lainnya, mariadb mempunyai beberapa keyword untuk melakukan iterasi atau perulangan.

FOR

[begin_label:]
FOR var_name IN [ REVERSE ] lower_bound .. upper_bound
DO 
	statement_list
END FOR [ end_label ];

FOR Loop terdiri dari 2 bagian yaitu header dan body. Header digunakan untuk mendeklarasikan variabel yang digunakan iterasi, sedangkan Body adalah blok kode yang akan dieksekusi selama masih memenuhi syarat untuk melakukan iterasi. Terdapat 2 jenis FOR Loop pada mariadb yang pertama nilai variabel akan dilakukan increment dari lower_bound sampai upper_bound dan yang kedua nilai variabel akan dilakukan decrement dari upper_bound sampai lower_bound. Untuk lebih jelasnya perhatikan contoh FOR Loop di bawah ini.

CREATE TABLE t1 (a INT);

DELIMITER //

FOR i IN 1..3
DO
  INSERT INTO t1 VALUES (i);
END FOR;
//

DELIMITER ;

SELECT * FROM t1;

Pada FOR Loop di atas akan dilakukan perulangan dari nilai i = 1 hingga nilai i = 3. Setiap perulangan akan menambahkan nilai i sebanyak 1. Iterasi 1 i = 1, iterasi 2 i = 2 dan seterusnya.

CREATE OR REPLACE TABLE t1 (a INT);

DELIMITER //
FOR i IN REVERSE 4..12
    DO
    INSERT INTO t1 VALUES (i);
END FOR;
//

DELIMITER ;

SELECT * FROM t1;

Pada FOR Loop di atas akan dilakukan perulangan dari nilai i = 12 hingga nilai i = 4. Setiap perulangan akan mengurangi nilai i sebanyak 1. Iterasi 1 i = 12, iterasi 2 i = 11 dan seterusnya.

FOR Loop pada mariadb ini memiliki kekurangan yaitu kita dapat menentukan berapa nilai increment dan decrement sesuai dengan keinginan kita. Misal setiap perulangan kita ingin mengubah nilai i sebanyak 2, kita tidak dapat melakukan itu

WHILE

WHILE Loop akan melakukan perulangan selama search_condition masih bernilai TRUE.

[begin_label:] WHILE search_condition 
DO
	statement_list
END WHILE [end_label];

Untuk mengatasi masalah yang ada di FOR Loop tadi, kita dapat menggunakan WHILE Loop ini. Meskipun begitu, FOR dan WHILE memiliki kegunaan dan sintaks yang berbeda.

DECLARE v1 INT DEFAULT 5;
WHILE v1 > 0 DO
	SET v1 = v1 - 2;
END WHILE;

Kita dapat melakukan decrement pada variabel v1 sebanyak 2, yang mana hal tersebut tidak dapat dilakukan pada FOR Loop. Pada mariadb tidak mendukung penggunaan DO WHILE seperti bahasa pemrograman lain.

REPEAT

REPEAT dapat dikatakan sebagai kebalikan dari WHILE, jika WHILE akan melakukan perulangan selama search_condition bernilai TRUE, sedangkan REPEAT akan melakukan perulangan sampai search_condition bernilai TRUE.

[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label];

Berikut merupakan contoh penggunaan REPEAT.

DECLARE v1 INT;
SET @v1 = 0;
REPEAT 
	SET @v1 = @v1 + 1; UNTIL @v1 > 10 
END REPEAT;

Pada contoh diatas kita akan melakukan perulangan yang menambahkan nilai variabel v1 dengan 1 sampai nilai v1 > 10. Jadi perulangan akan berhenti jika v1 > 10 bernilai TRUE.

CURSOR

Pada mariadb keyword ini digunakan untuk mengiterasi tiap tiap row tanpa perlu menggunakan keyword looping seperti for dll. Jadi penggunaan keyword ini dapat mempermudah kita untuk melakukan pengeditan atau perubahan pada tiap-tiap baris/row jika dibutuhkan

CREATE TABLE c1(i INT);

CREATE TABLE c2(i INT);

CREATE TABLE c3(i INT);

DELIMITER //

CREATE PROCEDURE p1()
BEGIN
  -- variabel done digunakan untuk mengetahui apakah row sudah habis atau belum
  DECLARE done INT DEFAULT FALSE;
  -- variabel x dan y digunakan untuk operasi
  DECLARE x, y INT;
  -- variabel cur1 digunakan untuk memberitahu cursor kolom mana yang mau diiterasi
  DECLARE cur1 CURSOR FOR SELECT i FROM test.c1;
  -- variabel cur2 digunakan untuk memberitahu cursor kolom mana yang mau diiterasi
  DECLARE cur2 CURSOR FOR SELECT i FROM test.c2;
  -- menggunakan error handler dimana ketika row habis maka pengqueryan tetap berlanjut dan variabel done di set ke true
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  -- open cursor digunakan untuk menjalankan cursor yang telah dibuat
  OPEN cur1;
  OPEN cur2;

  -- menggunakan keyword loop untuk looping
  read_loop: LOOP
    -- data yang didapatkan dari cur1 atau cur2 akan dimasukkan kedalam variabel
    FETCH cur1 INTO x;
    FETCH cur2 INTO y;
    IF done THEN
      LEAVE read_loop;
    END IF;
    IF x < y THEN
      INSERT INTO test.c3 VALUES (x);
    ELSE
      INSERT INTO test.c3 VALUES (y);
    END IF;
  END LOOP;

  -- jangan lupa untuk menutup cursor yang telah kita buat
  CLOSE cur1;
  CLOSE cur2;
END; //

DELIMITER ;

INSERT INTO c1 VALUES(5),(50),(500);

INSERT INTO c2 VALUES(10),(20),(30);

CALL p1;

SELECT * FROM c3;
+------+
| i    |
+------+
|    5 |
|   20 |
|   30 |
+------+

4. Error Handler

key word ini mirip seperti try catch. Jadi keyword ini berfungsi untuk suatu kondisi error dan penangannya bagaimana. Fungsi yang disediakan oleh mariadb ada 3 yaitu

• CONTINUE, misalnya terdapat statement dengan urutan A, B,C jika menggunakan continue maka ketika terdapat error pada statement A maka statement B dan C tetap dijalankan
• EXIT, jika menggunakan exit maka jika terdapat error pada salah satu statement maka pada statement tersebutlah pengqueryan berhenti dan tidak menjalankan statement selanjutnya

DECLARE handler_type HANDLER
    FOR condition_value [, condition_value] ...
    statement

handler_type:
    CONTINUE
  | EXIT 

condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
  | mariadb_error_code

contoh penggunaan error handler adalah sebagai berikut :

EXIT

DROP PROCEDURE IF EXISTS handlerdemo;

DELIMITER //

CREATE PROCEDURE handlerdemo ()
BEGIN
  DECLARE EXIT HANDLER FOR SQLSTATE '42S01'
    -- Handle SQLSTATE '42S01' (table already exists) error
    SELECT 'Table pembeli already exists' AS error_message;

  CREATE TABLE IF NOT EXISTS `pembeli` (
    `id_pembeli` int(11) AUTO_INCREMENT PRIMARY KEY,
    `nama_depan` varchar(50) DEFAULT NULL,
    `nama_belakang` varchar(50) DEFAULT NULL,
    `alamat` varchar(50) DEFAULT NULL,
    `jenis_kelamin` enum('L','P') DEFAULT NULL,
    `tanggal_lahir` date DEFAULT NULL
  );

  -- Contoh insert data
  INSERT INTO pembeli (nama_depan, nama_belakang, alamat, jenis_kelamin, tanggal_lahir)
  VALUES ('John', 'Doe', 'Jalan ABC', 'L', '2000-01-01');

END;
//

DELIMITER ;

CALL handlerdemo();

pada kode diatas akan memberhentikan statement selanjutnya dari procedure yaitu insert data.

jika ingin tetap menginputkan data karena dalam kondisi ini tabel sudah ada maka bisa menggunakan keyword continue seperti ini

CONTINUE

DROP PROCEDURE IF EXISTS handlerdemo;

DELIMITER //

CREATE PROCEDURE handlerdemo ()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLSTATE '42S01'
    -- Handle SQLSTATE '42S01' (table already exists) error
    SELECT 'Table pembeli already exists' AS error_message;

  CREATE TABLE IF NOT EXISTS `pembeli` (
    `id_pembeli` int(11) AUTO_INCREMENT PRIMARY KEY,
    `nama_depan` varchar(50) DEFAULT NULL,
    `nama_belakang` varchar(50) DEFAULT NULL,
    `alamat` varchar(50) DEFAULT NULL,
    `jenis_kelamin` enum('L','P') DEFAULT NULL,
    `tanggal_lahir` date DEFAULT NULL
  );

  -- Contoh insert data
  INSERT INTO pembeli (nama_depan, nama_belakang, alamat, jenis_kelamin, tanggal_lahir)
  VALUES ('John', 'Doe', 'Jalan ABC', 'L', '2000-01-01');

END;
//

DELIMITER ;

CALL handlerdemo();

PASTIKAN KODE SQL STATE SESUAI DENGAN ERROR YANG KAMU DEKLARASIKAN

5. Compound Statements Outside of Stored programs

pada compound stetements kamu bebas untuk membuat suatu fungsi atau kumpulan statement tanpa menyimpannya pada database. berbeda dengan stored routines yang hasil dari kumpulan statement tersebut akan di simpan pada database seperti VIEW.

hal ini berguna jika kamu sedang melakukan pengembangan atau mengotak ngatik statement didalam fungsi. jadi ketika terdapat kesalahan kamu tidak perlu drop function atau procedure terlebih dahulu.

Stored Routines

1. Stored Procedures

Stored Procedure adalah kumpulan pernyataan SQL yang telah didefinisikan, disimpan, dan dapat dipanggil kembali dengan menggunakan nama tertentu. Stored Procedure digunakan untuk menjalankan serangkaian tugas atau operasi tertentu.

CREATE
    [OR REPLACE]
    [DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
    PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

type:
    Any valid MariaDB data type

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

routine_body:
    Valid SQL procedure statement

Terdapat 3 jenis parameter pada stored procedure, yang pertama adalah parameter IN yang berfungsi sebagai parameter input dari sebuah stored procedure, yang kedua adalah parameter OUT yang berfungsi sebagai parameter output dari sebuah stored procedure atau dapat dikatakan nilai yang dikembalikan oleh sebuah stored procedure, yang ketiga adalah parameter INOUT yang dapat berfungsi sebagai parameter input dan output.

Contoh stored procedure yang sederhana untuk menghitung jumlah produk yang telah dibeli oleh seorang pembeli tertentu dan berapa nilai rata-rata yang diberikan setelah beberapa kali melakukan pembelian.

DELIMITER |

CREATE PROCEDURE hitung_produk_dan_penilaian(
    IN pembeli_id_param INT, 
    IN produk_id_param INT, 
    OUT jumlah_produk INT, 
    OUT nilai_rata_rata DECIMAL(10, 2)
)
BEGIN
    -- Hitung jumlah produk yang telah dibeli oleh pembeli tertentu
    SELECT COUNT(*) INTO jumlah_produk
    FROM pembelian p
    WHERE p.pembeli_id = pembeli_id_param AND p.produk_id = produk_id_param;

    -- Hitung nilai rata-rata dari pembelian produk tertentu
    SELECT IFNULL(AVG(pe.nilai_produk), 0) INTO nilai_rata_rata
    FROM penilaian pe
    JOIN pembelian p ON pe.pembelian_id = p.id_pembelian
    WHERE p.pembeli_id = pembeli_id_param AND p.produk_id = produk_id_param;

		SELECT jumlah_produk AS JumlahProduk, nilai_rata_rata AS NilaiRataRata;
END;

|

DELIMITER ;

Dapat dilihat pada contoh di atas, untuk dapat mengembalikan suatu nilai, kita harus mengisi parameter dengan suatu nilai menggunakan SELECT INTO.

Cara memanggil suatu stored procedure:

CALL [nama_procedure] (param IN, @param OUT, @param INOUT);

Misalkan untuk memanggil procedure yang tadi telah dibuat:

CALL hitung_produk_dan_penilaian(1, 2, @jumlah_produk, @nilai_rata_rata);

Pada contoh di atas dilakukan pemanggilan prosedur hitung_produk_dan_penilaian dengan parameter input yaitu id_pembeli = 1 dan id_produk = 2 serta parameter output jumlah_produk dan nilai_rata_rata. Untuk pemanggilan parameter output dapat dituliskan bebas, misal @jumlah_produk diganti dengan @a tidak ada perbedaan untuk hasilnya.

2. Stored Functions

Stored Function dalam MariaDB adalah objek database yang menyimpan kumpulan pernyataan SQL dan mengembalikan nilai tunggal. Fungsi tersimpan digunakan untuk melakukan operasi kalkulasi atau pemrosesan data di dalam database MariaDB. Mereka dapat digunakan dalam pernyataan SQL, seperti bagian dari SELECT atau kondisi WHERE, dan memberikan fleksibilitas dalam merancang logika bisnis yang dapat digunakan secara berulang.

Berikut adalah beberapa karakteristik dan aspek penting dari Stored Function dalam MariaDB:

1. Definisi:
   • Fungsi tersimpan didefinisikan menggunakan pernyataan CREATE FUNCTION.
   • Mereka menyimpan logika bisnis dalam bentuk pernyataan SQL yang dapat dieksekusi.
2. Struktur:
   • Sebuah fungsi tersimpan dapat memiliki parameter yang memungkinkan nilai-nilai eksternal untuk digunakan dalam logikanya.
   • Fungsi dapat mengembalikan nilai tunggal ke pemanggilnya.
3. Penggunaan:
   • Fungsi tersimpan dapat digunakan dalam pernyataan SQL untuk menghasilkan nilai yang dapat digunakan dalam perhitungan atau evaluasi logika bisnis.
   • Contoh: SELECT my_function(param1, param2);
4. Scope:
   • Fungsi tersimpan biasanya terkait dengan database tertentu.
   • Mereka dapat diakses dari pernyataan SQL di dalam database tersebut.
5. Perubahan Data:
   • Fungsi tersimpan dapat melakukan perubahan pada data, seperti operasi INSERT, UPDATE, atau DELETE, tergantung pada izin dan kebijakan keamanan.

Stored Function dan Stored Procedure adalah dua konsep terkait dalam basis data yang menyimpan logika pemrograman di dalam server basis data. Meskipun keduanya serupa, ada beberapa perbedaan kunci antara keduanya:

1. Nilai Kembali:
   • Stored Function: Mengembalikan nilai tunggal. Fungsi ini dapat digunakan dalam pernyataan SQL, dan nilai yang dihasilkan dapat dimasukkan langsung ke dalam kueri atau perhitungan.
   • Stored Procedure: Tidak mengembalikan nilai atau mengembalikan nilai melalui parameter OUTPUT.
2. Penggunaan dalam Kueri:
   • Stored Function: Dapat digunakan dalam pernyataan SELECT atau dalam ekspresi SQL untuk mengembalikan nilai yang akan digunakan dalam perhitungan.
   • Stored Procedure: Digunakan sebagai prosedur yang dapat mengandung sejumlah pernyataan SQL dan tidak dapat digunakan dalam pernyataan SELECT langsung.
3. Deklarasi dalam Kueri:
   • Stored Function: Dapat digunakan dalam pernyataan SQL tanpa perlu menggunakan kata kunci CALL.
   • Stored Procedure: Biasanya dipanggil menggunakan kata kunci CALL dalam pernyataan SQL.
4. Keamanan:
   • Stored Function: Lebih sering digunakan untuk logika yang bersifat pembacaan (read-only) karena mengembalikan nilai.
   • Stored Procedure: Digunakan untuk logika yang dapat melakukan modifikasi pada data.
5. Pemanggilan:
   • Stored Function: Dapat digunakan dalam pernyataan SQL dan dalam ekspresi di dalam kueri.
   • Stored Procedure: Dipanggil secara terpisah dari pernyataan SQL menggunakan perintah CALL.

Contoh Stored Function MariaDB:

Stored Function:

CREATE [OR REPLACE]
    [DEFINER = {user | CURRENT_USER | role | CURRENT_ROLE }]
    [AGGREGATE] FUNCTION [IF NOT EXISTS] func_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...]
    RETURN func_body

func_parameter:
    [ IN | OUT | INOUT | IN OUT ]  param_name type

type:
    Any valid MariaDB data type

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

func_body:
    Valid SQL procedure statement

Berikut adalah contoh fungsi tersimpan (stored function) dalam MariaDB:

DELIMITER //

CREATE FUNCTION calculate_discount(price INT, discount_rate FLOAT)
RETURNS INT
BEGIN
    DECLARE discount_amount INT;
    SET discount_amount = price * discount_rate;
    RETURN discount_amount;
END //

DELIMITER ;

SELECT calculate_discount(100, 0.2) AS discount;

Pada contoh di atas, kita membuat fungsi tersimpan bernama calculate_discount yang menerima dua parameter: price dan discount_rate. Fungsi ini mengembalikan jumlah diskon yang dihitung dari harga dan tingkat diskon yang diberikan. Setelah kita membuat fungsi tersimpan, kita dapat menggunakannya dalam pernyataan SQL seperti contoh di atas, di mana kita memanggil fungsi calculate_discount dengan argumen 100 dan 0.2, dan hasilnya akan mengembalikan nilai diskon.

Triggers & Events

1. Triggers

Trigger adalah objek database yang memungkinkan kita untuk menjalankan serangkaian perintah SQL secara otomatis ketika suatu peristiwa tertentu terjadi pada tabel tertentu. Peristiwa tersebut bisa berupa operasi INSERT, UPDATE, DELETE, atau kombinasi dari operasi-operasi tersebut. Trigger sering digunakan untuk memberikan respon secara otomatis terhadap perubahan data dalam database.

Terdapat beberapa komponen utama dalam trigger di MySQL:

1. Event

Event adalah peristiwa yang memicu eksekusi trigger. Peristiwa ini dapat berupa INSERT, UPDATE, DELETE, atau kombinasi dari ketiganya. 2. Timing

Timing menentukan kapan trigger harus dijalankan. Dalam mariadb, ada dua timing yang dapat digunakan: - BEFORE: Trigger dijalankan sebelum operasi yang menyebabkan trigger diaktifkan. - AFTER: Trigger dijalankan setelah operasi yang menyebabkan trigger diaktifkan.

Contoh penggunaan BEFORE padaTrigger. Misalkan pada database modul7 terdapat tabel penilaian yang terdapat kolom nilai_produk. Nilai produk biasanya hanya memiliki rentang 1-5, kita dapat membuat sebuah trigger untuk mengatasi jika ada yang melakukan INSERT di luar rentang tersebut.

DELIMITER |
CREATE TRIGGER validate_penilaian
BEFORE INSERT ON penilaian
FOR EACH ROW
BEGIN
    IF NEW.nilai_produk > 5 THEN
        SIGNAL SQLSTATE '45000' #(unhandled user-defined exception)
        SET MESSAGE_TEXT = 'Penilaian Harus Antara 1-5';
    END IF;
END;
|
DELIMITER ;

Pada contoh di atas kita akan memvalidasi INSERT dengan menggunakan sebuah Trigger. Jadi, kita membuat suatu trigger yang akan dieksekusi sebelum proses INSERT berhasil dieksekusi. Serta terdapat kondisi ketika nilai produk > 5 akan membuat sebuah exception baru yang memiliki pesan kustom yaitu ‘Penilaian Harus Antara 1-5’.

Coba buat trigger tersebut lalu jalankan query di bawah ini.

INSERT INTO penilaian (nilai_produk) VALUES (10)

Maka akan tampil pesan error seperti berikut.

Contoh penggunaan AFTER pada TRIGGER. Misalkan terdapat tabel t1 dengan isi data sebagai berikut.

dan terdapat tabel t2 dengan isi data yang merepresentasikan jumlah data yang ada pada tabel t1.

Jika kita memasukkan data baru ke tabel t1 maka jumlah datanya akan menjadi 5 namun tabel t2 tetap berisikan 4. Bagaimana cara agar membuat tabel t2 dapat ter-update secara otomatis? Kita dapat menggunakan AFTER untuk mengatasi masalah ini.

DELIMITER |

CREATE TRIGGER checkt1
AFTER INSERT ON t1 FOR EACH ROW
BEGIN
    UPDATE t2
    SET countt1 = countt1 + 1;
END;
|

DELIMITER ;

Pada kode di atas, program akan melakukan UPDATE pada tabel t2 dengan menambahkan countt1 dengan nilai 1 ketika proses INSERT sudah berhasil dieksekusi.

Coba buat trigger di atas serta buat tabel t1 dan t2 sama seperti pada contoh lalu jalankan query berikut.

INSERT INTO t1 VALUES (5);

Maka tabel t2 akan otomatis terupdate.

Kita dapat melihat seluruh TRIGGER yang telah kita buat dengan cara berikut.

SELECT * FROM information_schema.triggers

2. Events

Dalam MySQL, sebuah event adalah tugas yang dijadwalkan untuk berjalan pada waktu tertentu atau berjalan dengan interval tertentu. Event pada MySQL memberikan cara untuk mengotomatisasi tugas-tugas yang berulang di dalam server database.

CREATE [OR REPLACE]
    [DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
    EVENT 
    [IF NOT EXISTS]
    event_name    
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    DO sql_statement;

schedule:
    AT timestamp [+ INTERVAL interval] ...
  | EVERY interval 
    [STARTS timestamp [+ INTERVAL interval] ...] 
    [ENDS timestamp [+ INTERVAL interval] ...]

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

Contoh EVENT yang berjalan pada interval tertentu, EVENT akan terus berulang secepat interval yang ditentukan. Pada contoh di bawah ini EVENT akan melakukan update pada tabel t2 setiap 1 detik.

DELIMITER |

CREATE EVENT test_event 
ON SCHEDULE EVERY 1 SECOND
DO 
  UPDATE t2 SET countt1 = countt1 + 1;
|

DELIMITER ;

Contoh EVENT yang berjalan pada waktu tertentu, EVENT akan berjalan sekali saja pada waktu interval yang telah ditentukan. Pada contoh di bawah ini EVENT akan melakukan INSERT pada tabel t1 setelah 1 detik dari waktu sekarang.

DELIMITER |
CREATE EVENT test_event2
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND
DO
BEGIN
    INSERT INTO t1 VALUES (1);
END;
|
DELIMITER ;

2 Hal tersebut dapat digabungkan, jadi EVENT akan melakukan suatu hal setiap interval tertentu pada rentang waktu tertentu. Misal pada contoh di bawah ini, EVENT akan melakukan insert pada tabel t1 setiap 30 menit. EVENT akan dimulai 1 jam dari waktu sekarang dan akan berakhir 6 jam dari waktu sekarang. Meskipun, EVENT berulang setiap 30 menit namun ketika sudah melebihi waktu berakhirnya, EVENT akan berhenti berulang.

DELIMITER |
CREATE EVENT test_event2
ON SCHEDULE EVERY 30 MINUTE
STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR + INTERVAL 5 HOUR
DO
BEGIN
    INSERT INTO t1 VALUES (1);
END;
|
DELIMITER ;

Kita dapat melihat seluruh EVENTyang telah kita buat dengan cara berikut.

SELECT * FROM information_schema.events;

Untuk mengaktifkan EVENT agar dapat berjalan kita harus menjalankan sintaks berikut.

ALTER EVENT [nama_event] ENABLE;

Documentation

https://mariadb.com/kb/en/programming-customizing-mariadb/

Soal

1. buatlah fungsi untuk mengitung banyaknya pendapatan tiap bulan pada penjual. fungsi harus memiliki parameter id untuk id penjual yang dicari dan bulan yang dicari.

2. buatlah procedure untuk melakukan input data pembelian baru dan penilaian baru. jika id pembeli tidak cocok maka tampilkan kesalahan di hasil console. jika rating diisi null atau 0 maka insert data pada penilaian tidak dijalankan dan jika rating diisi lebih dari 5 maka tampilkan error.

3. buatlah event untuk menambahkan tagihan pajak pada penjual setiap bulannya (hanya penjual yang mendapatkan penghasilan lebih dari 100,000 pada bulan tersebut) . besarnya pajak diambil dari 2,5% dari penghasilan bulanan. tambahkan kolom baru bernama tax (jika belum ada kolom tax) yang berisi pajak dari penjual.

hasil perhitungan pajak pada bulan 11