CREATE PROCEDURE - Amazon Redshift
Hak istimewa yang diperlukanSintaksParameterCatatan penggunaanContoh

Terjemahan disediakan oleh mesin penerjemah. Jika konten terjemahan yang diberikan bertentangan dengan versi bahasa Inggris aslinya, utamakan versi bahasa Inggris.

CREATE PROCEDURE

Membuat prosedur tersimpan baru atau menggantikan prosedur yang ada untuk database saat ini.

Untuk informasi selengkapnya dan contoh tambahan, lihat Membuat prosedur tersimpan di Amazon Redshift.

Hak istimewa yang diperlukan

Anda harus memiliki izin dengan salah satu cara berikut untuk menjalankan CREATE ATAU REPLACEPROCEDURE:

  • Untuk CREATEPROCEDURE:

    • Superuser

    • Pengguna dengan CREATE dan USAGE hak istimewa pada skema tempat prosedur tersimpan dibuat

  • Untuk REPLACEPROCEDURE:

    • Superuser

    • Pemilik prosedur

Sintaks

CREATE [ OR REPLACE ] PROCEDURE sp_procedure_name  
  ( [ [ argname ] [ argmode ] argtype [, ...] ] )
[ NONATOMIC ]
AS $$
  procedure_body
$$ LANGUAGE plpgsql
[ { SECURITY INVOKER | SECURITY DEFINER } ]
[ SET configuration_parameter { TO value | = value } ]

Parameter

ATAU REPLACE

Klausa yang menentukan bahwa jika prosedur dengan nama yang sama dan tipe data argumen masukan, atau tanda tangan, seperti yang sudah ada, prosedur yang ada diganti. Anda hanya dapat mengganti prosedur dengan prosedur baru yang mendefinisikan kumpulan tipe data yang identik.

Jika Anda menentukan prosedur dengan nama yang sama dengan prosedur yang ada, tetapi tanda tangan yang berbeda, Anda membuat prosedur baru. Dengan kata lain, nama prosedur kelebihan beban. Untuk informasi selengkapnya, lihat Nama prosedur overloading.

sp_prosedur_name

Nama prosedurnya. Jika Anda menentukan nama skema (sepertimyschema.myprocedure), prosedur dibuat dalam skema yang ditentukan. Jika tidak, prosedur dibuat dalam skema saat ini. Untuk informasi selengkapnya tentang nama yang valid, lihatNama dan pengenal.

Kami menyarankan Anda mengawali semua nama prosedur yang disimpan dengansp_. Amazon Redshift mencadangkan sp_ awalan untuk nama prosedur yang disimpan. Dengan menggunakan sp_ awalan, Anda memastikan bahwa nama prosedur tersimpan tidak bertentangan dengan nama prosedur atau fungsi tersimpan bawaan Amazon Redshift yang ada atau yang akan datang. Untuk informasi selengkapnya, lihat Penamaan prosedur tersimpan.

Anda dapat menentukan lebih dari satu prosedur dengan nama yang sama jika tipe data untuk argumen input, atau tanda tangan, berbeda. Dengan kata lain, dalam hal ini nama prosedur kelebihan beban. Untuk informasi selengkapnya, silakan lihat Nama prosedur overloading

[argname] [argmode] argtype

Daftar nama argumen, mode argumen, dan tipe data. Hanya tipe data yang diperlukan. Nama dan mode bersifat opsional dan posisinya dapat dialihkan.

Mode argumen bisa IN,OUT, atauINOUT. Defaultnya adalah IN.

Anda dapat menggunakan OUT dan INOUT argumen untuk mengembalikan satu atau lebih nilai dari panggilan prosedur. Ketika ada OUT atau INOUT argumen, panggilan prosedur mengembalikan satu baris hasil yang berisi n kolom, di mana n adalah jumlah total OUT atau INOUT argumen.

INOUTargumen adalah argumen input dan output pada saat yang sama. Argumen masukan mencakup IN dan INOUT argumen, dan argumen keluaran mencakup keduanya OUT dan INOUT argumen.

OUTargumen tidak ditentukan sebagai bagian dari CALL pernyataan. Tentukan INOUT argumen dalam CALL pernyataan prosedur yang disimpan. INOUTargumen dapat berguna saat meneruskan dan mengembalikan nilai dari panggilan bersarang, dan juga saat mengembalikan arefcursor. Untuk informasi lebih lanjut tentang refcursor jenis, lihatCursors.

Tipe data argumen dapat berupa tipe data Amazon Redshift standar apa pun. Selain itu, tipe data argumen dapatrefcursor.

Anda dapat menentukan maksimum 32 argumen masukan dan 32 argumen keluaran.

AS $$ prosedur_body $$

Sebuah konstruksi yang mencakup prosedur yang akan dijalankan. Kata kunci literal AS $$ dan $$ diperlukan.

Amazon Redshift mengharuskan Anda untuk melampirkan pernyataan dalam prosedur Anda dengan menggunakan format yang disebut kutipan dolar. Apa pun di dalam kandang dilewatkan persis seperti apa adanya. Anda tidak perlu melarikan diri dari karakter khusus apa pun karena isi string ditulis secara harfiah.

Dengan kutipan dolar, Anda menggunakan sepasang tanda dolar ($$) untuk menandakan awal dan akhir pernyataan yang akan dijalankan, seperti yang ditunjukkan pada contoh berikut.

$$ my statement $$

Secara opsional, di antara tanda-tanda dolar di setiap pasangan, Anda dapat menentukan string untuk membantu mengidentifikasi pernyataan tersebut. String yang Anda gunakan harus sama di awal dan akhir pasangan enklosur. String ini peka huruf besar/kecil, dan mengikuti batasan yang sama dengan pengenal yang tidak dikutip kecuali bahwa string ini tidak dapat berisi tanda dolar. Contoh berikut menggunakan tes string.

$test$ my statement $test$

Sintaks ini juga berguna untuk kutipan dolar bersarang. Untuk informasi lebih lanjut tentang kutipan dolar, lihat “Konstanta String yang dikutip Dolar” di bawah Struktur Leksikal dalam dokumentasi Postgre. SQL

prosedur_body

Satu set pernyataan PL/PG SQL yang valid. SQLPernyataan PL/PG menambah SQL perintah dengan konstruksi prosedural, termasuk perulangan dan ekspresi bersyarat, untuk mengontrol aliran logis. Sebagian besar SQL perintah dapat digunakan dalam badan prosedur, termasuk bahasa modifikasi data (DML) sepertiCOPY, UNLOAD danINSERT, dan bahasa definisi data (DDL) seperti CREATETABLE. Untuk informasi selengkapnya, lihat Referensi bahasa PL/PGSQL.

LANGUAGEplpgsql

Nilai bahasa. Tentukan plpgsql. Anda harus memiliki izin untuk penggunaan bahasa untuk digunakanplpgsql. Untuk informasi selengkapnya, lihat GRANT.

NONATOMIC

Menciptakan prosedur tersimpan dalam modus transaksi nonatomik. NONATOMICmode secara otomatis melakukan pernyataan di dalam prosedur. Selain itu, ketika kesalahan terjadi di dalam NONATOMIC prosedur, kesalahan tidak dilemparkan kembali jika ditangani oleh blok pengecualian. Untuk informasi selengkapnya, silakan lihat Mengelola transaksi dan MENAIKKAN.

Saat Anda mendefinisikan prosedur tersimpan sebagaiNONATOMIC, pertimbangkan hal berikut:

  • Saat Anda melakukan panggilan prosedur tersimpan, semua prosedur harus dibuat dalam mode transaksi yang sama.

  • SECURITY DEFINEROpsi dan SET configuration_parameter opsi tidak didukung saat membuat prosedur dalam NONATOMIC mode.

  • Setiap kursor yang dibuka (secara eksplisit atau implisit) ditutup secara otomatis ketika komit implisit diproses. Oleh karena itu, Anda harus membuka transaksi eksplisit sebelum memulai kursor loop untuk memastikan bahwa setiap SQL dalam iterasi loop tidak secara implisit berkomitmen.

SECURITY INVOKER | SECURITY DEFINER

SECURITY DEFINEROpsi ini tidak didukung ketika NONATOMIC ditentukan.

Mode keamanan untuk prosedur menentukan hak akses prosedur saat runtime. Prosedur harus memiliki izin untuk mengakses objek database yang mendasarinya.

Untuk SECURITY INVOKER mode, prosedur menggunakan hak istimewa pengguna yang memanggil prosedur. Pengguna harus memiliki izin eksplisit pada objek database yang mendasarinya. Defaultnya adalah SECURITYINVOKER.

Untuk SECURITY DEFINER mode, prosedur menggunakan hak istimewa pemilik prosedur. Pemilik prosedur didefinisikan sebagai pengguna yang memiliki prosedur pada waktu berjalan, belum tentu pengguna yang awalnya mendefinisikan prosedur. Pengguna yang memanggil prosedur memerlukan hak istimewa eksekusi pada prosedur, tetapi tidak memerlukan hak istimewa apa pun pada objek yang mendasarinya.

SETconfiguration_parameter {TO nilai | = nilai}

Opsi ini tidak didukung saat NONATOMIC ditentukan.

SETKlausa menyebabkan yang ditentukan diatur configuration_parameter ke nilai yang ditentukan ketika prosedur dimasukkan. Klausul ini kemudian mengembalikan configuration_parameter ke nilai sebelumnya ketika prosedur keluar.

Catatan penggunaan

Jika prosedur tersimpan dibuat menggunakan SECURITY DEFINER opsi, saat menjalankan USER fungsi CURRENT _ dari dalam prosedur tersimpan, Amazon Redshift mengembalikan nama pengguna pemilik prosedur yang disimpan.

Contoh

catatan

Jika saat menjalankan contoh ini Anda menemukan kesalahan yang mirip dengan:

ERROR: 42601: [Amazon](500310) unterminated dollar-quoted string at or near "$$

Lihat Ikhtisar prosedur tersimpan di Amazon Redshift.

Contoh berikut membuat prosedur dengan dua parameter input.

CREATE OR REPLACE PROCEDURE test_sp1(f1 int, f2 varchar(20))
AS $$
DECLARE
  min_val int;
BEGIN
  DROP TABLE IF EXISTS tmp_tbl;
  CREATE TEMP TABLE tmp_tbl(id int);
  INSERT INTO tmp_tbl values (f1),(10001),(10002);
  SELECT INTO min_val MIN(id) FROM tmp_tbl;
  RAISE INFO 'min_val = %, f2 = %', min_val, f2;
END;
$$ LANGUAGE plpgsql;
catatan

Saat Anda menulis prosedur tersimpan, kami merekomendasikan praktik terbaik untuk mengamankan nilai sensitif:

Jangan membuat kode keras informasi sensitif apa pun dalam logika prosedur yang disimpan. Misalnya, jangan tetapkan kata sandi pengguna dalam CREATE USER pernyataan di badan prosedur tersimpan. Ini menimbulkan risiko keamanan, karena nilai hard-code dapat dicatat sebagai metadata skema dalam tabel katalog. Sebagai gantinya, berikan nilai sensitif, seperti kata sandi, sebagai argumen ke prosedur yang disimpan, melalui parameter.

Untuk informasi selengkapnya tentang prosedur tersimpan, lihat CREATEPROCEDUREdan Membuat prosedur tersimpan di Amazon Redshift. Untuk informasi selengkapnya tentang tabel katalog, lihat Tabel katalog sistem.

Contoh berikut membuat prosedur dengan satu parameter IN, satu OUT parameter, dan satu INOUT parameter.

CREATE OR REPLACE PROCEDURE test_sp2(f1 IN int, f2 INOUT varchar(256), out_var OUT varchar(256))
AS $$
DECLARE
  loop_var int;
BEGIN
  IF f1 is null OR f2 is null THEN
    RAISE EXCEPTION 'input cannot be null';
  END IF;
  DROP TABLE if exists my_etl;
  CREATE TEMP TABLE my_etl(a int, b varchar);
    FOR loop_var IN 1..f1 LOOP
        insert into my_etl values (loop_var, f2);
        f2 := f2 || '+' || f2;
    END LOOP;
  SELECT INTO out_var count(*) from my_etl;
END;
$$ LANGUAGE plpgsql;