Prosedur Tersimpan - Hologres

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

Batasan

Hologres V3.0 dan versi lebih baru mendukung prosedur tersimpan dengan sintaksis PL/pgSQL. Untuk informasi lebih lanjut tentang sintaksis PL/pgSQL, lihat Bahasa Prosedural SQL.
Prosedur tersimpan Hologres memungkinkan Anda mengeksekusi beberapa pernyataan bahasa definisi data (DDL) dalam satu transaksi atau beberapa pernyataan bahasa manipulasi data (DML) dalam satu transaksi. Namun, Anda tidak dapat mengeksekusi pernyataan DDL dan DML dalam transaksi yang sama. Untuk informasi lebih lanjut, lihat Kemampuan Transaksi SQL.
Prosedur tersimpan tidak mengizinkan pengaturan nilai balikan, sehingga tidak dapat digunakan sebagai fungsi yang ditentukan pengguna (UDF).

Izin

Untuk mengeksekusi pernyataan CREATE PROCEDURE, Anda memerlukan izin CREATE pada sebuah database, sama seperti izin yang diperlukan untuk mengeksekusi pernyataan CREATE TABLE. Untuk informasi lebih lanjut, lihat CREATE PROCEDURE.
Untuk mengeksekusi pernyataan CREATE OR REPLACE PROCEDURE, Anda memerlukan izin CREATE pada sebuah database serta kepemilikan atas prosedur tersimpan yang akan diganti. Untuk informasi lebih lanjut, lihat CREATE PROCEDURE.
Untuk memanggil prosedur tersimpan, Anda memerlukan izin EXECUTE pada prosedur tersebut. Untuk informasi lebih lanjut, lihat CALL.

Sintaksis

Prosedur tersimpan Hologres kompatibel dengan PostgreSQL. Bagian ini menjelaskan sintaksis prosedur tersimpan Hologres.

Membuat prosedur tersimpan

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

Parameter	Deskripsi
procedure_name	Nama dari prosedur tersimpan.
argname	Nama parameter. Parameter ini opsional tergantung pada desain prosedur tersimpan.
argtype	Tipe parameter.
definition	Pernyataan SQL atau blok kode yang mendefinisikan implementasi prosedur tersimpan.

Untuk informasi lebih lanjut tentang parameter, lihat CREATE PROCEDURE.

Memodifikasi prosedur tersimpan

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

Parameter	Deskripsi
new_owner	Nama pengguna baru.
CURRENT_USER	Nama pengguna saat ini.
SESSION_USER	Nama pengguna sesi.

Untuk informasi lebih lanjut tentang parameter, lihat ALTER PROCEDURE.

Menghapus prosedur tersimpan

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

Untuk informasi lebih lanjut tentang parameter, lihat DROP PROCEDURE.

Memanggil prosedur tersimpan

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

Parameter	Deskripsi
argument	Parameter yang dibutuhkan oleh prosedur tersimpan. Parameter ini opsional tergantung pada desain prosedur tersimpan.

Untuk informasi lebih lanjut tentang parameter, lihat CALL.

Contoh

Contoh 1: Membuat dan memanggil prosedur tersimpan yang berisi beberapa pernyataan DDL dalam satu transaksi.

Membuat 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; 
$$;

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

Contoh 2: Membuat dan memanggil prosedur tersimpan yang berisi beberapa pernyataan DML dalam satu transaksi.

Membuat 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;
$$;

Memanggil prosedur tersimpan.

Memanggil procedure_2. Operasi rollback didukung, dan data tidak ditulis.

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

-- Panggil prosedur tersimpan.
CALL procedure_2();

Memanggil procedure_3. Data ditulis.

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

-- Panggil prosedur tersimpan.
CALL procedure_3();

Contoh 3: Membuat dan memanggil prosedur tersimpan yang berisi pernyataan DDL dan DML.

Membuat prosedur tersimpan di mana pernyataan DDL dan DML dilakukan secara terpisah. Hologres tidak mengizinkan Anda mengeksekusi pernyataan DDL dan DML dalam transaksi yang sama.

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;
$$;

Memanggil prosedur tersimpan. Pembuatan tabel dan penulisan data berhasil.

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

-- Panggil prosedur tersimpan.
CALL procedure_4();

Contoh 4: Membuat dan memanggil prosedur tersimpan yang berisi klausa umum, seperti klausa untuk mendefinisikan parameter input, variabel antara, loop, kondisi IF, dan penanganan kesalahan.

Membuat prosedur tersimpan.

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

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

    -- Gunakan variabel antara untuk menulis catatan data ke tabel a3.
    sql1 = 'insert into a3 values(1);';
    EXECUTE sql1;

    -- Definisikan loop FOR.
    FOR i IN 1..10 LOOP
        BEGIN
            -- i=1 sudah ada di tabel, dan satu catatan dicatat.
            IF i IN (SELECT KEY FROM a3) THEN
                RAISE NOTICE 'Data sudah ada.';
            -- Nilai lain tidak ada di tabel. Sesi mencoba menulis nilai-nilai tersebut ke tabel dan menggunakan RAISE EXCEPTION untuk melempar pengecualian. Kemudian, operasi commit dilakukan.
            ELSE
                INSERT INTO a3 VALUES(i);
                RAISE EXCEPTION 'HG_PLPGSQL_NEED_RETRY';
                COMMIT; 
            END IF;
        -- Pengecualian yang dilaporkan dicatat.
        EXCEPTION 
            WHEN OTHERS THEN
                RAISE NOTICE 'Tangkap kesalahan.';
        END;
    END LOOP;

END;
$$;

Memanggil prosedur tersimpan. Hanya nilai 1 yang ditulis ke tabel a3, dan log terkait dicatat.

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

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

Kelola prosedur tersimpan

Query prosedur tersimpan yang telah dibuat.

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;

Query definisi prosedur tersimpan.

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

Pemecahan masalah

Hologres adalah sistem terdistribusi. Oleh karena itu, node frontend (FE) dari instance Hologres diterapkan secara terdistribusi. Metadata dari node FE harus disinkronkan secara real-time. Jika operasi DDL dilakukan pada tabel tetapi metadata belum disinkronkan, operasi DDL mungkin gagal. Dalam hal ini, Hologres secara otomatis mencoba ulang operasi DDL ketika fitur coba ulang otomatis didukung. Tidak diperlukan operasi manual. Namun, dalam prosedur tersimpan, pesan kesalahan "HG_PLPGSQL_NEED_RETRY" dikembalikan karena fitur coba ulang otomatis tidak didukung.

Kami merekomendasikan Anda untuk secara manual mendefinisikan logika coba ulang untuk tabel di mana operasi DDL sering dilakukan dalam prosedur tersimpan. Ini membantu mencegah pesan kesalahan dilaporkan secara berulang. Contoh kode:

CREATE OR REPLACE PROCEDURE procedure_6()
LANGUAGE 'plpgsql'
AS $$
BEGIN
    WHILE TRUE LOOP
        BEGIN
            -- Sesi mencoba mengeksekusi pernyataan DDL dan keluar dari loop ketika eksekusi berhasil.
            CREATE TABLE a3(key int);
            COMMIT;
            EXIT;
        EXCEPTION
            -- Jika pesan kesalahan "HG_PLPGSQL_NEED_RETRY" dilaporkan, sesi mencatat kesalahan dan mencoba ulang operasi secara otomatis.
            WHEN HG_PLPGSQL_NEED_RETRY THEN 
                RAISE NOTICE 'DDL perlu coba ulang';
        END;
    END LOOP;
END;
$$;