Buat dan eksekusi stored procedure di Hologres-Hologres-Alibaba Cloud - Hologres

Prosedur tersimpan adalah kumpulan pernyataan SQL yang telah dikompilasi sebelumnya dan dapat disimpan di dalam database untuk dipanggil berulang kali. Topik ini menjelaskan cara menggunakan prosedur tersimpan di Hologres.

Batasan

Hologres mendukung prosedur tersimpan dengan sintaks PL/pgSQL mulai dari versi V3.0. Untuk informasi selengkapnya mengenai sintaks PL/pgSQL, lihat SQL Procedural Language.
Dalam prosedur tersimpan Hologres, Anda dapat menjalankan beberapa pernyataan DDL dalam satu transaksi atau beberapa pernyataan DML dalam satu transaksi. Namun, Anda tidak dapat menjalankan pernyataan DDL dan DML dalam transaksi yang sama. Untuk informasi selengkapnya, lihat Transaksi.
Prosedur tersimpan tidak mendukung nilai kembali dan tidak dapat digunakan sebagai user-defined function (UDF).
Prosedur tersimpan tidak mendukung pernyataan RETURN QUERY untuk mengembalikan set hasil. Untuk mengembalikan data, gunakan Tampilan atau tabel temporary.
Prosedur tersimpan tidak mendukung operasi CURSOR.
Anda tidak dapat mendefinisikan prosedur tersimpan di dalam prosedur tersimpan lainnya.
Prosedur tersimpan tidak mendukung pernyataan EXECUTE ... INTO untuk menetapkan hasil SQL dinamis ke variabel. Gunakan pernyataan SELECT ... INTO sebagai gantinya.

Izin

Untuk menjalankan pernyataan CREATE PROCEDURE, Anda harus memiliki izin CREATE pada database—izin yang sama yang diperlukan untuk membuat tabel. Untuk informasi selengkapnya, lihat CREATE PROCEDURE.
Untuk menjalankan pernyataan CREATE OR REPLACE, Anda harus memiliki izin CREATE pada database dan menjadi Pemilik prosedur tersimpan yang ingin Anda ganti. Untuk informasi selengkapnya, lihat CREATE PROCEDURE.
Untuk memanggil prosedur tersimpan, Anda harus memiliki izin EXECUTE pada prosedur tersebut. Untuk informasi selengkapnya, lihat CALL.

Referensi perintah

Hologres mendukung sintaks prosedur tersimpan yang kompatibel dengan PostgreSQL. Bagian berikut menjelaskan sintaks tersebut.

Membuat prosedur tersimpan

CREATE [ OR REPLACE ] PROCEDURE
    <procedure_name> ([<argname> <argtype>])
LANGUAGE 'plpgsql'
AS <definition>;

Parameter	Deskripsi
procedure_name	Nama prosedur tersimpan.
argname	Nama argumen. Parameter ini opsional dan tergantung pada desain prosedur tersimpan.
argtype	Tipe data argumen.
definition	Implementasi spesifik dari prosedur tersimpan. Ini dapat berupa pernyataan SQL atau Blok kode.

Untuk informasi selengkapnya mengenai parameter, lihat CREATE PROCEDURE.

Mengubah prosedur tersimpan

ALTER PROCEDURE <procedure_name> ([<argname> <argtype>])
    OWNER TO <new_owner> | CURRENT_USER | SESSION_USER;

Parameter	Deskripsi
new_owner	Pemilik baru.
CURRENT_USER	User saat ini.
SESSION_USER	User sesi.

Untuk informasi selengkapnya mengenai parameter, lihat ALTER PROCEDURE.

Menghapus prosedur tersimpan

DROP PROCEDURE [ IF EXISTS ] <procedure_name> ([<argname> <argtype>]);

Untuk informasi selengkapnya mengenai parameter, lihat DROP PROCEDURE.

Memanggil prosedur tersimpan

CALL <procedure_name> ([<argument>]);

Parameter	Deskripsi
argument	Argumen untuk prosedur tersimpan. Parameter ini opsional dan tergantung pada desain prosedur.

Untuk informasi selengkapnya mengenai parameter, lihat CALL.

Contoh

Contoh 1: Prosedur tersimpan dengan transaksi DDL multi-pernyataan

Buat prosedur tersimpan.

CREATE OR REPLACE PROCEDURE procedure_1()
LANGUAGE 'plpgsql'
AS $$
BEGIN
    --- TXN1 --- 
    CREATE TABLE a1(key int);
    CREATE TABLE a2(key int);
    COMMIT; 

    --- TXN2 ---
    CREATE TABLE a3(key int);
    CREATE TABLE a4(key int);
    ROLLBACK;
END; 
$$;

Panggil prosedur tersimpan. Tabel a1 dan a2 dibuat, tetapi tabel a3 dan a4 tidak dibuat.
```
CALL procedure_1();
```

Contoh 2: Prosedur tersimpan dengan transaksi DML multi-pernyataan

Buat prosedur tersimpan.

CREATE OR REPLACE PROCEDURE procedure_2()
LANGUAGE 'plpgsql'
AS $$
BEGIN
    INSERT INTO a1 VALUES(1);
    INSERT INTO a2 VALUES(2);
    ROLLBACK;
END;
$$;

CREATE OR REPLACE PROCEDURE procedure_3()
LANGUAGE 'plpgsql'
AS $$
BEGIN
    INSERT INTO a1 VALUES(1);
    INSERT INTO a2 VALUES(2);
END;
$$;

Panggil prosedur tersimpan.

Panggil procedure_2. Transaksi di-rollback, dan tidak ada data yang ditulis.

-- Aktifkan fitur transaksi DML.
SET hg_experimental_enable_transaction = ON;

-- Panggil prosedur tersimpan.
CALL procedure_2();

Panggil procedure_3. Data berhasil ditulis.

-- Aktifkan fitur transaksi DML.
SET hg_experimental_enable_transaction = ON;

-- Panggil prosedur tersimpan.
CALL procedure_3();

Contoh 3: Prosedur tersimpan dengan pernyataan DDL dan DML

Buat prosedur tersimpan. Karena Hologres tidak mendukung pencampuran pernyataan DDL dan DML dalam satu transaksi, Anda harus melakukan commit terhadap operasi DDL dan DML secara terpisah di dalam prosedur tersimpan.

CREATE OR REPLACE PROCEDURE procedure_4()
LANGUAGE 'plpgsql'
AS $$
BEGIN
    INSERT INTO a1 VALUES(1);
    COMMIT;	
    CREATE TABLE bb(key int);
    COMMIT;	
    INSERT INTO a1 VALUES(2);
    INSERT INTO bb VALUES(1);    
    COMMIT;	
END;
$$;

Panggil prosedur tersimpan. Tabel dibuat dan data berhasil ditulis.

-- Aktifkan fitur transaksi DML.
SET hg_experimental_enable_transaction = ON;

-- Panggil prosedur tersimpan.
CALL procedure_4();

Contoh 4: Prosedur tersimpan yang menunjukkan fitur umum, seperti mendefinisikan parameter input, variabel antara, loop, kondisi IF, dan penanganan EXCEPTION.

Buat prosedur tersimpan.

CREATE OR REPLACE PROCEDURE procedure_5(input text)
LANGUAGE 'plpgsql'
AS $$
-- Definisikan variabel antara.
DECLARE
sql1 text;
BEGIN
    -- Masukkan satu baris data ke tabel yang ditentukan oleh parameter input.
    EXECUTE 'insert into ' || input || ' values(1);';
    COMMIT;

    -- Buat tabel a3.
    CREATE TABLE a3(key int);
    COMMIT;

    -- Gunakan variabel antara untuk memasukkan satu baris ke tabel a3.
    sql1 = 'insert into a3 values(1);';
    EXECUTE sql1;

    -- Definisikan loop FOR.
    FOR i IN 1..10 LOOP
        BEGIN
            -- Karena i=1 sudah ada di tabel, hanya pemberitahuan yang muncul.
            IF i IN (SELECT KEY FROM a3) THEN
                RAISE NOTICE 'Data already exists.';
            -- Angka lain tidak ada di tabel. Sistem mencoba memasukkannya,
            -- memunculkan EXCEPTION, lalu melakukan commit.
            ELSE
                INSERT INTO a3 VALUES(i);
                RAISE EXCEPTION 'HG_PLPGSQL_NEED_RETRY';
                COMMIT; 
            END IF;
        -- Untuk EXCEPTION yang muncul, tampilkan pemberitahuan.
        EXCEPTION 
            WHEN OTHERS THEN
                RAISE NOTICE 'Catch error.';
        END;
    END LOOP;

END;
$$;

Panggil prosedur tersimpan. Nilai 1 ditulis ke tabel a3, tidak ada data lain yang ditulis, dan semua pemberitahuan terkait dicetak.

-- Aktifkan fitur transaksi DML.
SET hg_experimental_enable_transaction = ON;

-- Panggil prosedur tersimpan.
CALL procedure_5('a1');

Contoh 5: Menggunakan ekspresi CASE WHEN untuk menghitung dan menggunakan kembali variabel secara dinamis.

Buat tabel tujuan dan prosedur tersimpan. Prosedur ini mendeklarasikan variabel dengan DECLARE, menghitung parameter waktu secara dinamis menggunakan ekspresi CASE WHEN, dan menggunakan kembali variabel tersebut dalam beberapa pernyataan INSERT.

-- Buat tabel tujuan.
CREATE TABLE test_dynamic_param (
    event_name TEXT,
    start_time TIMESTAMPTZ,
    end_time TIMESTAMPTZ
);

-- Buat prosedur tersimpan yang menggunakan ekspresi CASE WHEN untuk menghitung parameter waktu secara dinamis.
CREATE OR REPLACE PROCEDURE procedure_dyn_time()
LANGUAGE 'plpgsql'
AS $$
DECLARE
    new_end_time TIMESTAMPTZ;
    base_start_time TIMESTAMPTZ := '2024-01-01 00:00:00+08'::TIMESTAMPTZ;
BEGIN
    -- Gunakan CASE WHEN untuk menghitung end_time secara dinamis.
    new_end_time := CASE
        WHEN NOW() > base_start_time + INTERVAL '5 min'
        THEN base_start_time + INTERVAL '1 hour'
        ELSE base_start_time + INTERVAL '5 min'
    END;

    -- Gunakan kembali variabel dalam beberapa pernyataan INSERT.
    INSERT INTO test_dynamic_param VALUES('event1', base_start_time, new_end_time);
    INSERT INTO test_dynamic_param VALUES('event2', base_start_time, new_end_time);
END;
$$;

Panggil prosedur tersimpan dan verifikasi hasilnya. Nilai end_time untuk kedua catatan identik karena berasal dari perhitungan CASE WHEN yang sama.

-- Aktifkan fitur transaksi DML.
SET hg_experimental_enable_transaction = ON;

-- Panggil prosedur tersimpan.
CALL procedure_dyn_time();

-- Verifikasi: Nilai end_time untuk kedua catatan identik.
SELECT * FROM test_dynamic_param;

Mengelola prosedur tersimpan

Lihat prosedur tersimpan.

SELECT
    p.proname AS procedure_name,
    pg_get_function_identity_arguments(p.oid) AS argument_types,
    REPLACE(pg_get_functiondef(p.oid),'$procedure$','$$') AS procedure_detail,
    n.nspname AS schema_name,
    r.rolname AS owner_name,
    d.description AS description
FROM
    pg_proc p
    INNER JOIN pg_namespace n ON p.pronamespace = n.oid
    INNER JOIN pg_roles r ON p.proowner = r.oid
    LEFT JOIN pg_description d ON p.oid = d.objoid
WHERE
    r.rolname != 'holo_admin'
    AND p.prokind = 'p'
ORDER BY
    n.nspname,
    p.proname;

Lihat definisi prosedur tersimpan.

SELECT pg_get_functiondef('<procedure_name>'::regproc);

FAQ

Hologres adalah sistem terdistribusi yang harus menyinkronkan metadata di seluruh node frontend (FE) secara real time selama operasi DDL. Jika sinkronisasi metadata belum lengkap, operasi DDL mungkin gagal. Meskipun Hologres biasanya mencoba ulang operasi DDL yang gagal secara otomatis, mekanisme ini tidak didukung di dalam prosedur tersimpan. Jika masalah ini terjadi dalam prosedur tersimpan, sistem akan mengembalikan error HG_PLPGSQL_NEED_RETRY.

Untuk mencegah error pada tabel yang sering mengalami perubahan DDL, terapkan logika retry manual di dalam prosedur tersimpan Anda. Kode berikut memberikan contohnya:

CREATE OR REPLACE PROCEDURE procedure_6()
LANGUAGE 'plpgsql'
AS $$
BEGIN
    WHILE TRUE LOOP
        BEGIN
            -- Coba jalankan pernyataan DDL. Jika berhasil, keluar dari loop.
            CREATE TABLE a3(key int);
            COMMIT;
            EXIT;
        EXCEPTION
            -- Jika terjadi error HG_PLPGSQL_NEED_RETRY, tampilkan pemberitahuan dan coba ulang operasi.
            WHEN HG_PLPGSQL_NEED_RETRY THEN 
                RAISE NOTICE 'DDL need retry';
        END;
    END LOOP;
END;
$$;