Apa itu Stored Procedure

Stored procedure adalah suatu kumpulan logika yang ditulis oleh seorang pengembang dan dapat dipanggil secara berulang dalam SQL. Logika yang terdapat dalam stored procedure dapat melakukan berbagai operasi database, mulai dari menjalankan pernyataan SQL hingga membuat dan menjalankan pernyataan SQL secara dinamis.

Contoh Stored Procedure

Mari kita lihat contoh sederhana dari stored procedure bernama myproc dengan sebuah handler Python bernama run. Prosedur ini memiliki fungsi utama untuk memindahkan data dari satu tabel ke tabel lain dengan batasan jumlah baris yang ditransfer.

Kode SQL untuk membuat stored procedure ini adalah sebagai berikut:

create or replace procedure myproc(from_table string, to_table string, count int)
  returns string
  language python
  runtime_version = '3.8'
  packages = ('snowflake-snowpark-python')
  handler = 'run'
as
$$
def run(session, from_table, to_table, count):
  session.table(from_table).limit(count).write.save_as_table(to_table)
  return "SUCCESS"
$$;

Setelah stored procedure dibuat, kita dapat memanggilnya menggunakan perintah CALL. Contohnya:

CALL myproc('table_a', 'table_b', 5);

Ini akan mentransfer lima baris data dari table_a ke table_b.

Bahasa yang Didukung

Stored procedure dapat ditulis dalam beberapa bahasa pemrograman. Pilihan bahasa tergantung pada kebutuhan dan persyaratan tugas tertentu, kemampuan pengembang, serta pustaka atau fitur yang tersedia dalam bahasa tersebut.

Dalam Snowflake, Anda dapat menulis handler (logika) dari stored procedure dalam salah satu dari bahasa berikut:

• Java (menggunakan API Snowpark)
• JavaScript
• Python (menggunakan API Snowpark)
• Scala (menggunakan API Snowpark)
• Snowflake Scripting (SQL)

Setiap bahasa memungkinkan manipulasi data sesuai dengan batasan bahasa dan lingkungan runtime-nya. Terlepas dari bahasa handler yang dipilih, prosedur itu sendiri dibuat dengan cara yang sama menggunakan SQL, dengan menentukan handler dan bahasa handler.

Membuat Stored Procedure

Anda dapat membuat stored procedure dengan langkah-langkah berikut:

1. Menulis kode handler
   Ini adalah kode yang akan dieksekusi saat prosedur dipanggil. Kode ini harus dirancang untuk menangani operasi database spesifik yang prosedur tersebut dibuat untuk.

2. Menentukan lokasi kode handler
   Kode handler dapat ditempatkan dalam baris tunggal dalam pernyataan SQL CREATE PROCEDURE, atau dapat dirujuk di suatu stage. Stage adalah ruang file yang diberi nama dan dapat berupa table stage, user stage, atau Snowflake stage.

3. Menjalankan pernyataan CREATE PROCEDURE
   Pernyataan tersebut dieksekusi dalam SQL dan menentukan properti-properri dari prosedur.

Berikut adalah contoh:

create or replace procedure myProc(fromTable STRING, toTable STRING, count INT)
returns string
language java
runtime_version = '11'
packages = ('com.snowflake:snowpark:latest')
handler = 'MyClass.myMethod'
AS
$$
  import com.snowflake.snowpark_java.*;

  public class MyClass
  {
    public String myMethod(Session session, String fromTable, String toTable, int count)
    {
      session.table(fromTable).limit(count).write().saveAsTable(toTable);
      return "Success";
    }
  }
$$;

Pada contoh ini, stored procedure myProc dibuat dengan handler dalam baris tunggal MyClass.myMethod. Bahasa handler yang digunakan adalah Java, dan handler membutuhkan objek Session dari pustaka Snowpark, yang disertakan dalam klausa PACKAGES.
Ketika membuat prosedur, properti-properti berikut biasanya digunakan:

• Nama prosedur
  Tidak perlu sama dengan nama handler. Pernyataan CREATE PROCEDURE mengaitkan nama prosedur dengan handler.
• Argumen prosedur
  Pada saat runtime, nilai argumen prosedur diikat ke argumen handler prosedur. Mereka dapat diikat berdasarkan nama yang cocok atau berdasarkan posisi argumen, tergantung pada bahasa yang digunakan untuk handler.
• Tipe pengembalian
  Ditentukan dengan klausa RETURNS.
• Nama handler
  Ditentukan dengan klausa HANDLER. Jika diperlukan, ini adalah nama kelas atau metode yang berisi kode yang dieksekusi saat prosedur dipanggil.
• Ketergantungan yang diperlukan oleh handler
  Ditetapkan dengan menggunakan klausa IMPORTS atau PACKAGES.
• Runtime bahasa handler
  Ditentukan dengan klausa RUNTIME_VERSION.

Handler dalam Baris Tunggal

Kode dalam contoh berikut membuat prosedur bernama my_proc dengan fungsi handler Python dalam baris tunggal run.

create or replace procedure my_proc(from_table string, to_table string, count int)
  returns string
  language python
  runtime_version = '3.8'
  packages = ('snowflake-snowpark-python')
  handler = 'run'
as
$$
def run(session, from_table, to_table, count):
  session.table(from_table).limit(count).write.save_as_table(to_table)
  return "SUCCESS"
$$;

Handler dengan Staging

Kode dalam contoh berikut membuat prosedur bernama my_proc dengan metode handler Java yang di-staging MyClass.myMethod. Melalui klausa PACKAGES, kode merujuk pustaka Snowpark yang disertakan untuk Java, di mana objek Session diperlukan ketika bahasa handler adalah Java. Dengan klausa IMPORTS, kode merujuk file JAR yang di-staging yang berisi kode handler.

create or replace procedure my_proc(fromTable string, toTable string, count int)
  returns string
  language java
  runtime_version = '11'
  packages = ('com.snowflake:snowpark:latest')
  imports = ('@mystage/myjar.jar')
  handler = 'MyClass.myMethod';

Memanggil Stored Procedure

Setelah Anda membuat stored procedure, langkah selanjutnya adalah memanggilnya dan menggunakannya.
Untuk memanggil stored procedure, Anda menggunakan perintah CALL. Syntax dasarnya adalah sebagai berikut:

CALL procedure_name(argument1, argument2, ...);

PHak Akses untuk Memanggil Stored Procedure

Untuk memanggil stored procedure, peran pengguna harus memiliki hak akses USAGE untuk stored procedure tersebut. Hak akses USAGE memungkinkan peran untuk menjalankan stored procedure tetapi tidak untuk mengelolanya.

Menentukan Argumen

Jika stored procedure memiliki argumen, Anda dapat menentukan argumen tersebut berdasarkan nama atau posisi.

Sebagai contoh, pertimbangkan stored procedure berikut yang menerima tiga argumen:

CREATE OR REPLACE PROCEDURE sp_concatenate_strings(
    first VARCHAR,
    second VARCHAR,
    third VARCHAR)
  RETURNS VARCHAR
  LANGUAGE SQL
  AS
  $$
  BEGIN
    RETURN first || second || third;
  END;
  $$;

Saat memanggil prosedur, Anda dapat menentukan argumen berdasarkan nama:

CALL sp_concatenate_strings(
  first => 'one',
  second => 'two',
  third => 'three');

Jika Anda menentukan argumen berdasarkan nama, Anda tidak perlu menentukan argumen dalam urutan tertentu:

CALL sp_concatenate_strings(
  third => 'three',
  first => 'one',
  second => 'two');

Anda juga dapat menentukan argumen berdasarkan posisi:

CALL sp_concatenate_strings(
  'one',
  'two',
  'three');

Referensi

https://docs.snowflake.com/en/sql-reference/stored-procedures-overview
https://docs.snowflake.com/en/sql-reference/stored-procedures-creating-sql
https://docs.snowflake.com/en/sql-reference/stored-procedures-calling