Memigrasikan pengguna Oracle® ke Cloud SQL untuk PostgreSQL: Kueri, prosedur tersimpan, fungsi, dan pemicu

Dokumen ini adalah bagian dari rangkaian yang memberikan informasi dan panduan penting terkait perencanaan dan pelaksanaan migrasi database Oracle® 11g/12c ke Cloud SQL untuk PostgreSQL versi 12. Selain bagian penyiapan pendahuluan, rangkaian ini meliputi bagian berikut:

Kueri

Oracle dan Cloud SQL untuk PostgreSQL mendukung standar ANSI SQL. Oleh karena itu, migrasi pernyataan SQL umumnya mudah dilakukan dengan hanya menggunakan elemen sintaksis dasar (misalnya, tidak menentukan fungsi skalar atau fitur tambahan Oracle lainnya). Bagian berikut membahas elemen kueri Oracle umum dan Cloud SQL untuk PostgreSQL yang sesuai.

Sintaksis SELECT dan FROM dasar

Nama fitur atau nama sintaksis Oracle Ringkasan atau implementasi Oracle Dukungan Cloud SQL untuk PostgreSQL Solusi yang sesuai atau solusi alternatif Cloud SQL untuk PostgreSQL
Sintaksis dasar SQL untuk pengambilan data SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
Ya SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
SELECT
untuk cetak output
SELECT 1 FROM DUAL Ya SELECT 1
Alias kolom SELECT COL1 AS C1 Ya SELECT COL1 AS C1
OR
SELECT COL1 C1
Kepekaan huruf besar/kecil nama tabel Tidak ada kepekaan huruf besar/kecil
(misalnya, nama tabel dapat berupa orders dan/atau ORDERS).
Ya Nama tidak peka huruf besar/kecil kecuali jika dikutip (misalnya, orders dan ORDERS diperlakukan sama, sedangkan "orders" dan "ORDERS" diperlakukan secara berbeda)

Untuk mengetahui detail selengkapnya tentang sintaksis SELECT Cloud SQL untuk PostgreSQL, lihat dokumentasi.

Tampilan inline

  • Tampilan inline (juga dikenal sebagai tabel turunan) adalah pernyataan SELECT, yang terletak di klausa FROM dan digunakan sebagai subkueri.
  • Tampilan inline dapat membantu menyederhanakan kueri yang kompleks menjadi lebih sederhana dengan menghapus penghitungan gabungan atau menghilangkan operasi gabungan, sekaligus menggabungkan beberapa kueri terpisah menjadi satu kueri yang disederhanakan.
  • Catatan konversi: Tampilan inline Oracle tidak memerlukan penggunaan alias, sedangkan Cloud SQL untuk PostgreSQL memerlukan alias khusus untuk setiap tampilan inline.

Tabel berikut menampilkan contoh konversi dari Oracle ke Cloud SQL untuk PostgreSQL, sebagai tampilan inline.

Oracle 11g/12c Cloud SQL untuk PostgreSQL 12
SQL> SELECT FIRST_NAME,
DEPARTMENT_ID,
SALARY,
DATE_COL
FROM EMPLOYEES,
(SELECT SYSDATE AS DATE_COL FROM DUAL);


Outputnya mirip dengan berikut ini:

FIRST_NAME DEPARTMENT_ID SALARY DATE_COL
-------------------- ------------- ---------- ---------
Steven 90 24000 30-JUL-19
Neena 90 17000 30-JUL-19
Lex 90 17000 30-JUL-19

Tanpa alias untuk tampilan inline:

postgres=> SELECT FIRST_NAME,
postgres-> DEPARTMENT_ID,
postgres-> SALARY,
postgres-> DATE_COL
postgres-> FROM EMPLOYEES, (SELECT NOW() AS DATE_COL);

ERROR: subquery in FROM must have an alias
LINE 5: FROM EMPLOYEES, (SELECT NOW() AS DATE_COL);

HINT: For example, FROM (SELECT ...) [AS] foo.

Menambahkan alias ke tampilan inline:

postgres=> SELECT FIRST_NAME,
postgres-> DEPARTMENT_ID,
postgres-> SALARY,
postgres-> DATE_COL
postgres-> FROM EMPLOYEES, (SELECT NOW() AS DATE_COL) AS C1;

Outputnya mirip dengan berikut ini:

first_name | department_id | salary | date_col
-------------+---------------+----------+--------------------------------
Steven | 90 | 24000.00 | 10/16/2020 08:35:18.470089 UTC
Neena | 90 | 17000.00 | 10/16/2020 08:35:18.470089 UTC
Lex | 90 | 17000.00 | 10/16/2020 08:35:18.470089 UTC

Pernyataan JOIN

Pernyataan JOIN Oracle didukung oleh pernyataan JOIN Cloud SQL untuk PostgreSQL. Namun, penggunaan operator gabungan Oracle (+) tidak didukung oleh Cloud SQL untuk PostgreSQL. Untuk mencapai hasil yang sama, Anda harus mengonversi ke sintaksis SQL standar untuk gabungan luar.

Tabel berikut menampilkan contoh konversi JOIN.

Jenis JOIN Oracle Didukung oleh Cloud SQL untuk PostgreSQL Sintaksis JOIN Cloud SQL untuk PostgreSQL
INNER JOIN Ya SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
CROSS JOIN Ya SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E CROSS JOIN DEPARTMENTS D;
FULL JOIN [ OUTER ] Ya SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E FULL JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
LEFT JOIN [ OUTER ] Ya SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E LEFT JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
RIGHT JOIN [ OUTER ] Ya SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E RIGHT JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
SUBQUERY Ya SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E JOIN (SELECT * FROM DEPARTMENTS)D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

UNION, UNION ALL, INTERSECT, dan MINUS

Cloud SQL untuk PostgreSQL mendukung operator UNION, UNION ALL, dan INTERSECT Oracle. Operator MINUS tidak didukung. Namun, Cloud SQL untuk PostgreSQL mengimplementasikan operator EXCEPT yang setara dengan operator MINUS di Oracle. Selain itu, Cloud SQL untuk PostgreSQL mendukung operator INTERSECT ALL dan EXCEPT ALL yang tidak didukung oleh Oracle.

  • UNION: Melampirkan kumpulan hasil dari dua pernyataan SELECT atau lebih dan menghapus data duplikat.
  • UNION ALL: Melampirkan kumpulan hasil dari dua pernyataan SELECT atau lebih tanpa menghapus data duplikat.
  • INTERSECT: Menampilkan hubungan dari dua pernyataan SELECT atau lebih hanya jika data ada di kedua set data. Data duplikat tidak akan dihapus.
  • INTERSECT ALL (khusus Cloud SQL untuk PostgreSQL): Menampilkan hubungan dua pernyataan SELECT atau lebih hanya jika record ada di kedua set data.
  • MINUS (EXCEPT di Cloud SQL untuk PostgreSQL): Membandingkan dua pernyataan SELECT atau lebih, yang hanya menampilkan baris yang berbeda dari kueri pertama yang tidak ditampilkan oleh pernyataan lain.
  • EXCEPT ALL (khusus Cloud SQL untuk PostgreSQL): Membandingkan dua pernyataan SELECT atau lebih, yang hanya menampilkan baris dari kueri pertama yang tidak ditampilkan oleh pernyataan lain tanpa menghapus data duplikat.

Catatan konversi

Saat melakukan konversi dari operator MINUS Oracle ke Cloud SQL untuk PostgreSQL, gunakan operator EXCEPT.

Contoh

Fungsi Oracle Implementasi Oracle Dukungan Cloud SQL untuk PostgreSQL Solusi yang sesuai atau solusi alternatif Cloud SQL untuk PostgreSQL
UNION SELECT COL1 FROM TBL1
UNION
SELECT COL1 FROM TBL2
Ya SELECT COL1 FROM TBL1
UNION
SELECT COL1 FROM TBL2
UNION ALL SELECT COL1 FROM TBL1
UNION ALL
SELECT COL1 FROM TBL2
Ya SELECT COL1 FROM TBL1
UNION ALL
SELECT COL1 FROM TBL2
INTERSECT SELECT COL1 FROM TBL1
INTERSECT
SELECT COL1 FROM TBL2
Ya SELECT COL1 FROM TBL1
INTERSECT
SELECT COL1 FROM TBL2
MINUS SELECT COL1 FROM TBL1
MINUS
SELECT COL1 FROM TBL2
Ya (Convert MINUS hingga EXCEPT di PostgreSQL) SELECT COL1 FROM TBL1
EXCEPT
SELECT COL1 FROM TBL2

Fungsi skalar (baris tunggal) dan grup

Cloud SQL untuk PostgreSQL menyediakan daftar lengkap fungsi skalar (baris tunggal) dan agregasi. Beberapa fungsi Cloud SQL untuk PostgreSQL serupa dengan fungsi Oracle-nya (berdasarkan nama dan fungsi, atau dengan nama yang berbeda tetapi dengan fungsi yang sama). Meskipun fungsi Cloud SQL untuk PostgreSQL dapat memiliki nama yang identik dengan fungsi Oracle-nya, terkadang fungsi tersebut menunjukkan fungsi yang berbeda.

Tabel berikut menjelaskan kesamaan Oracle dan Cloud SQL untuk PostgreSQL berdasarkan nama dan fungsi (ditentukan oleh "Yes") dan tempat konversi direkomendasikan (semua kasus selain "Yes").

Fungsi karakter
Fungsi Oracle Spesifikasi atau implementasi fungsi Oracle Setara dengan Cloud SQL untuk PostgreSQL Fungsi yang sesuai dengan Cloud SQL untuk PostgreSQL Spesifikasi atau implementasi fungsi Cloud SQL untuk PostgreSQL
CONCAT Menampilkan str1 yang disambungkan dengan str2:

CONCAT('A', 1) = A1
Ya CONCAT Setara dengan Oracle:

CONCAT('A', 1) = A1
LOWER/UPPER Menampilkan char, dengan semua huruf kecil atau huruf besar:

LOWER('SQL') = sql
Ya LOWER/UPPER Setara dengan Oracle:

LOWER('SQL') = sql
LPAD/RPAD Menampilkan expr1, dengan padding kiri atau kanan hingga panjang karakter n dengan urutan karakter dalam expr2:

LPAD('A',3,'*') = **A
Ya LPAD/RPAD Setara dengan Oracle:

LPAD('A',3,'*') = **A
SUBSTR Tampilkan sebagian char, dimulai dari posisi karakter, dengan panjang karakter substring-
:

SUBSTR('PostgreSQL', 8, 3)
= SQL
Sebagian SUBSTR Setara dengan Oracle saat posisi awal adalah angka positif.

SUBSTR('PostgreSQL', 8, 3)
= SQL

Jika angka negatif diberikan sebagai posisi awal di Oracle, angka tersebut akan melakukan operasi substring dari akhir string, yang berbeda dengan Cloud SQL untuk PostgreSQL. Gunakan fungsi RIGHT sebagai pengganti jika menginginkan perilaku Oracle.
INSTR Menampilkan posisi (indeks) string tertentu dari string tertentu:

INSTR('PostgreSQL', 'e')
= 7
Tidak T/A Cloud SQL untuk PostgreSQL tidak memiliki fungsi instr bawaan. Fungsi instr yang kompatibel dengan Oracle dapat diterapkan menggunakan PL/pgSQL.
REPLACE Menampilkan karakter dengan setiap kemunculan string penelusuran yang diganti dengan string pengganti
:

REPLACE('ORADB', 'ORA', 'PostgreSQL')
= PostgreSQLDB
Sebagian REPLACE Parameter string pengganti bersifat opsional di Oracle, sedangkan di Cloud SQL untuk PostgreSQL bersifat wajib. Jika parameter dihilangkan, Oracle akan menghapus semua kemunculan string penelusuran. Perilaku yang sama dapat dicapai di Cloud SQL untuk PostgreSQL dengan menyediakan string kosong sebagai string pengganti.

REPLACE('ORADB', 'ORA', 'PostgreSQL')
= PostgreSQLDB
TRIM Memangkas karakter awal atau akhir (atau keduanya) dari string:

TRIM(both '-' FROM '-PostgreSQL-')
= PostgreSQL
TRIM(' PostgreSQL ')
= PostgreSQL
Ya TRIM Setara dengan Oracle:

TRIM(both '-' FROM '-PostgreSQL-')
= PostgreSQL
TRIM(' PostgreSQL ')
= PostgreSQL
LTRIM/RTRIM Menghapus semua karakter dari ujung kiri atau kanan string yang
muncul dalam penelusuran:

LTRIM(' PostgreSQL', ' ')
= PostgreSQL
Ya LTRIM/RTRIM Setara dengan Oracle:

LTRIM(' PostgreSQL', ' ') = PostgreSQL
ASCII Menampilkan representasi desimal dalam himpunan karakter database dari karakter pertama char:

ASCII('A') = 65
Ya ASCII Setara dengan Oracle:

ASCII('A') = 65
CHR Menampilkan nilai kode ASCII, yang merupakan nilai numerik antara 0 dan 225, menjadi karakter:

CHR(65) = A
Ya CHAR Setara dengan Oracle:

CHR(65) = A
LENGTH Menampilkan panjang string tertentu:

LENGTH ('PostgreSQL') = 10
Ya LENGTH Setara dengan Oracle:

LENGTH ('PostgreSQL') = 10
REGEXP_REPLACE Menelusuri string untuk pola ekspresi reguler:

REGEXP_REPLACE('John', '[hn].', '1') = Jo1
Ya REGEXP_REPLACE Setara dengan Oracle:

REGEXP_REPLACE('John', '[hn].', '1') = Jo1
REGEXP_SUBSTR Memperluas fungsi SUBSTR dengan menelusuri
pola ekspresi reguler pada string:

REGEXP_SUBSTR('https://console.cloud.google.com/sql/instances','https://([[:alnum:]]+\.?){3,4}/?')
= https://console.cloud.google.com/
Tidak T/A Gunakan REGEXP_MATCH PostgreSQL untuk mencapai fungsi yang serupa.
REGEXP_COUNT Menampilkan frekuensi munculnya pola di string sumber. Tidak T/A Gunakan REGEXP_MATCH PostgreSQL untuk mencapai fungsi yang serupa.
REGEXP_INSTR Telusuri posisi string (indeks) untuk pola ekspresi
reguler.
Tidak T/A Mengonversi fungsi ke lapisan aplikasi.
REVERSE Menampilkan string terbalik.

REVERSE('PostgreSQL') = LQSergtsoP
Ya REVERSE Setara dengan Oracle:

REVERSE('PostgreSQL') = LQSergtsoP
Fungsi numerik
Fungsi Oracle Spesifikasi atau implementasi fungsi Oracle Setara dengan Cloud SQL untuk PostgreSQL Fungsi yang sesuai dengan Cloud SQL untuk PostgreSQL Spesifikasi atau implementasi fungsi Cloud SQL untuk PostgreSQL
ABS Nilai absolut n:

ABS(-4.6) = 4.6
Ya ABS Setara dengan Oracle:

ABS(-4.6) = 4.6
CEIL Menampilkan bilangan bulat terkecil yang lebih besar dari atau sama dengan n:

CEIL(21.4) = 22
Ya CEIL Setara dengan Oracle:

CEIL(21.4) = 22
FLOOR Menampilkan bilangan bulat terbesar yang sama dengan atau kurang dari n:

FLOOR(-23.7) = -24
Ya FLOOR Setara dengan Oracle:

FLOOR(-23.7) = -24
MOD Menampilkan sisa m yang dibagi n:

MOD(10, 3) = 1
Ya MOD Setara dengan Oracle:

MOD(10, 3) = 1
ROUND Menampilkan n yang dibulatkan ke tempat bilangan bulat di sebelah kanan titik desimal:

ROUND(1.39, 1) = 1.4
Ya ROUND Setara dengan Oracle:

ROUND(1.39, 1) = 1.4
TRUNC
(angka)
Menampilkan n1 yang dipotong menjadi n2 tempat desimal:

TRUNC(99.999) = 99
TRUNC(99.999, 0) = 99
Ya TRUNCATE
(angka)
Setara dengan Oracle:

TRUNC(99.999) = 99
TRUNC(99.999, 0) = 99
Fungsi DateTime
Fungsi Oracle Spesifikasi atau implementasi fungsi Oracle Setara dengan Cloud SQL untuk PostgreSQL Fungsi yang sesuai dengan Cloud SQL untuk PostgreSQL Spesifikasi atau implementasi fungsi Cloud SQL untuk PostgreSQL
SYSDATE Menampilkan tanggal dan waktu saat ini yang ditetapkan untuk sistem operasi tempat server database berada:

SELECT SYSDATE FROM DUAL
= 31-JUL-2019
Sebagian dengan nama dan format fungsi yang berbeda CURRENT_TIMESTAMP CURRENT_TIMESTAMP akan menampilkan format tanggal dan waktu yang berbeda dari fungsi Oracle SYSDATE:

SELECT CURRENT_TIMESTAMP
= 2019-07-31 06:46:40.171477+00
SYSTIMESTAMP Menampilkan tanggal sistem, termasuk detik pecahan dan zona waktu:

SELECT SYSTIMESTAMP FROM DUAL
= 01-JAN-19 07.37.11.622187000 AM +00:00
Sebagian dengan nama fungsi yang berbeda CURRENT_TIMESTAMP Cloud SQL untuk PostgreSQL menampilkan format tanggal/waktu yang berbeda dari Oracle. Format tanggal diperlukan agar cocok dengan format tanggal/waktu asli:

SELECT CURRENT_TIMESTAMP
= 2019-01-31 07:37:11.622187+00
LOCAL TIMESTAMP Menampilkan tanggal dan waktu saat ini dalam zona waktu sesi pada nilai jenis data TIMESTAMP:

SELECT LOCALTIMESTAMP FROM DUAL
= 01-JAN-19 10.01.10.123456 PM
Sebagian dengan format tanggal/waktu yang berbeda LOCAL TIMESTAMP Cloud SQL untuk PostgreSQL menampilkan format tanggal/waktu yang berbeda dari Oracle. Format tanggal diperlukan agar cocok dengan format tanggal/waktu asli:

SELECT LOCALTIMESTAMP
= 2019-01-31 07:37:11.622187+00
CURRENT_DATE Menampilkan tanggal saat ini dalam zona waktu sesi:

SELECT CURRENT_DATE FROM DUAL
= 31-JAN-19
Sebagian dengan format tanggal/waktu yang berbeda CURRENT_ DATE Cloud SQL untuk PostgreSQL menampilkan format tanggal/waktu yang berbeda dengan Oracle. Format tanggal diperlukan agar cocok dengan format tanggal/waktu asli:

SELECT CURRENT_DATE
= 2019-01-31
CURRENT_TIMESTAMP Menampilkan tanggal dan waktu saat ini dalam zona waktu sesi:

SELECT CURRENT_TIMESTAMP FROM DUAL
= 31-JAN-19 06.54.35.543146 AM +00:00
Sebagian dengan format tanggal dan waktu yang berbeda CURRENT_TIMESTAMP Cloud SQL untuk PostgreSQL menampilkan format tanggal dan waktu yang berbeda dari Oracle. Format tanggal akan diperlukan agar cocok dengan format tanggal dan waktu asli:

SELECT CURRENT_TIMESTAMP FROM DUAL
= 2019-01-31 07:37:11.622187+00s
ADD_MONTHS Menampilkan tanggal serta bulan bilangan bulat:

ADD_MONTHS(SYSDATE, 1)
= 31-JAN-19
Tidak T/A Untuk mendapatkan fungsi yang sama di Cloud SQL untuk PostgreSQL, gunakan operator + / - dan tentukan interval waktu:

SELECT CURRENT_TIMESTAMP + INTERVAL '1 MONTH'
= 2019-01-31 07:37:11.622187+00s
EXTRACT
(bagian tanggal)
Menampilkan nilai kolom tanggal/waktu yang ditentukan dari tanggal/waktu atau ekspresi interval:

EXTRACT(YEAR FROM DATE '2019-01-31')
= 2019
Ya EXTRACT
(bagian tanggal)
Setara dengan Oracle:

EXTRACT(YEAR FROM DATE '2019-01-31')
= 2019
LAST_DAY Menampilkan tanggal dari hari terakhir dalam sebulan yang berisi tanggal yang ditentukan:

LAST_DAY('01-JAN-2019')
= 31-JAN-19
Tidak T/A Sebagai solusi, gunakan operator DATE_TRUNC dan + untuk menghitung hari terakhir setiap bulan. Format tanggal diperlukan agar cocok dengan format tanggal/waktu asli:

SELECT DATE_TRUNC('MONTH', DATE '01-JAN-2019') + INTERVAL '1 MONTH -1 DAY'
= 2019-01-31
MONTH_BETWEEN Menampilkan jumlah bulan antara tanggal 1 dan 2:

MONTHS_BETWEEN(SYSDATE, SYSDATE-60)
= 1.96
Sebagian
dengan
Fungsi pemformatan tanggal dan waktu yang berbeda
AGE Fungsi AGE Cloud SQL untuk PostgreSQL menampilkan interval antara dua stempel waktu:

AGE(DATE '01-JAN-2019', DATE '01-JAN-2019' - 60)
= 1 mon 29 days

Untuk mencapai nilai yang sama dengan fungsi MONTH_BETWEEN Oracle, diperlukan konversi yang lebih spesifik.
TO_CHAR (tanggal/waktu) Mengonversi tanggal dan waktu atau stempel waktu menjadi jenis data, lalu menjadi nilai jenis dataVARCHAR2
dalam format yang ditentukan oleh format tanggal:

TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MI:SS')
= 01-01-2019 10:01:01
Ya To_CHAR Setara dengan Oracle:

TO_CHAR(CURRENT_TIMESTAMP,'DD-MM-YYYY HH24:MI:SS');
01-01-2019 10:01:01
Fungsi encoding dan decoding
Fungsi Oracle Spesifikasi atau implementasi fungsi Oracle Setara dengan Cloud SQL untuk PostgreSQL Fungsi yang sesuai dengan Cloud SQL untuk PostgreSQL Spesifikasi atau implementasi fungsi Cloud SQL untuk PostgreSQL
DECODE Membandingkan ekspresi dengan setiap nilai penelusuran satu per satu menggunakan pernyataan IF-THEN-ELSE. Tidak CASE Gunakan pernyataan CASE Cloud SQL untuk PostgreSQL guna mendapatkan fungsi serupa.
DUMP Menampilkan nilai VARCHAR2 yang berisi kode jenis data, panjang dalam byte, dan representasi internal ekspresi. Tidak T/A Tidak didukung.
ORA_HASH Menghitung nilai hash untuk ekspresi tertentu. Tidak MD5 / SHA224 / SHA256 / SHA385 / SHA512 Gunakan fungsi MD5 Cloud SQL untuk PostgreSQL untuk checksum 128-bit atau fungsi SHA untuk checksum
160-bit guna menghasilkan nilai hash.
Fungsi konversi
Fungsi Oracle Spesifikasi atau implementasi fungsi Oracle Setara dengan Cloud SQL untuk PostgreSQL Fungsi yang sesuai dengan Cloud SQL untuk PostgreSQL Spesifikasi atau implementasi fungsi Cloud SQL untuk PostgreSQL
CAST Mengonversi satu jenis data bawaan atau nilai berjenis koleksi menjadi jenis data bawaan atau nilai berjenis koleksi lainnya:

CAST('1' as int) + 1
= 2
Sebagian CAST Fungsi CAST Cloud SQL untuk PostgreSQL mirip dengan fungsi CAST Oracle. Namun dalam kasus tertentu, fungsi harus disesuaikan karena perbedaan jenis data di antara kedua database:

CAST('1' as int) + 1
= 2
CONVERT Mengonversi string karakter dari satu himpunan karakter ke himpunan karakter lainnya:

CONVERT ( 'Ä Ê Í Õ Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1')
= ?? ?? ?? A B C
Sebagian CONVERT Fungsi CONVERT Cloud SQL untuk PostgreSQL menampilkan nilai bytea, yang merupakan string biner, bukan VARCHAR atau TEXT. Himpunan karakter yang didukung oleh PostgreSQL juga berbeda dengan Oracle.

CONVERT('Ä Ê Í Õ Ø A B C D E', 'UTF8', 'LATIN1')
= [Binary representation of the string in LATIN1 encoding]
TO_CHAR
(string/numerik)
Fungsi ini mengonversi angka atau tanggal menjadi string:

TO_CHAR(22.73,'$99.9')
= $22.7
Sebagian TO_CHAR Fungsi TO_CHAR Cloud SQL untuk PostgreSQL memiliki fungsi yang mirip dengan Oracle. Cloud SQL untuk PostgreSQL mendukung daftar string pemformatan yang sedikit berbeda. Secara default, Cloud SQL untuk PostgreSQL menyimpan satu kolom untuk tanda, sehingga akan ada spasi sebelum angka positif. Hal ini dapat disembunyikan dengan menggunakan imbuhan FM:

TO_CHAR(22.73,'FM$99.9')
= $22.7
TO_DATE Fungsi TO_DATE Oracle mengonversi string menjadi tanggal berdasarkan format tanggal/waktu sumber tertentu:

TO_DATE('2019/01/01', 'yyyy-mm-dd')
= 01-JAN-2019
Sebagian TO_DATE Fungsi TO_DATE Cloud SQL untuk PostgreSQL memiliki fungsi yang mirip dengan Oracle. Cloud SQL untuk PostgreSQL mendukung daftar string pemformatan yang sedikit berbeda:

TO_DATE('2019/01/01', 'yyyy-mm-dd')
= 2019-01-01
TO_NUMBER Mengonversi ekspresi menjadi nilai jenis data NUMBER:

TO_NUMBER('01234')
= 1234
Sebagian TO_NUMBER Fungsi TO_NUMBER Cloud SQL untuk PostgreSQL memerlukan string pemformatan sebagai input, meskipun bersifat opsional di Oracle:

TO_NUMBER('01234','99999')
= 1234

Alternatifnya adalah menggunakan fungsi CAST untuk konversi yang tidak memerlukan string pemformatan kompleks:

CAST('01234' AS NUMERIC)
= 1234
Fungsi SELECT bersyarat
Fungsi Oracle Spesifikasi atau implementasi fungsi Oracle Setara dengan Cloud SQL untuk PostgreSQL Fungsi yang sesuai dengan Cloud SQL untuk PostgreSQL Spesifikasi atau implementasi fungsi Cloud SQL untuk PostgreSQL
CASE Pernyataan CASE memilih dari urutan kondisi dan menjalankan pernyataan
yang sesuai dengan sintaksis berikut:

CASE WHEN condition THEN result [WHEN ...] [ELSE result] END
Ya CASE Setara dengan Oracle:

CASE WHEN condition THEN result [WHEN ...] [ELSE result] END
Fungsi null
Fungsi Oracle Spesifikasi atau implementasi fungsi Oracle Setara dengan Cloud SQL untuk PostgreSQL Fungsi yang sesuai dengan Cloud SQL untuk PostgreSQL Spesifikasi atau implementasi fungsi Cloud SQL untuk PostgreSQL
COALESCE Menampilkan ekspresi non-null pertama dalam daftar ekspresi:

COALESCE(null, '1', 'a')
= a
Ya COALESCE Setara dengan Oracle:

COALESCE(null, '1', 'a')
= 1
NULLIF Bandingkan expr1 dan expr2. Jika keduanya sama, fungsi akan menampilkan null. Jika hasilnya tidak sama, fungsi akan menampilkan expr1:

NULLIF('1', '2')
= 1
Ya NULLIF Setara dengan Oracle:

NULLIF('1', '2')
= 1
NVL Ganti null (yang ditampilkan sebagai kosong) dengan string dalam hasil kueri:

NVL(null, 'a')
= a
Tidak COALESCE Sebagai alternatif, gunakan fungsi COALESCE:

COALESCE(null, 'a')
= a
NVL2 Tentukan nilai yang ditampilkan oleh kueri berdasarkan nilai ekspresi
tertentu, yakni null atau bukan null.
Tidak COALESCE Sebagai alternatif, gunakan fungsi COALESCE:

COALESCE(null, 1, 'a')
= 1
Fungsi lingkungan dan ID
Fungsi Oracle Spesifikasi atau implementasi fungsi Oracle Setara dengan Cloud SQL untuk PostgreSQL Fungsi yang sesuai dengan Cloud SQL untuk PostgreSQL Spesifikasi atau implementasi fungsi Cloud SQL untuk PostgreSQL
SYS_GUID Menghasilkan dan menampilkan ID unik global (nilai RAW) yang terdiri
dari 16 byte:

SELECT SYS_GUID() FROM DUAL
= 8EFA4A31468B4C6DE05011AC0200009E
Sebagian dengan nama dan format fungsi yang berbeda UUID_GENERATE_V4 CloudSQL untuk Cloud SQL untuk PostgreSQL mendukungekstensi uuid-ossp yang menyediakan daftar fungsi yang menghasilkan UUID seperti UUID_GENERATE_V4:

SELECT UUID_GENERATE_v4()
= eaf356a6-2847-478d-af3b-6883f5ac6af2
UID Menampilkan bilangan bulat yang mengidentifikasi pengguna sesi secara unik (pengguna
yang login):

SELECT UID FROM DUAL
= 43
Tidak T/A T/A
USER Menampilkan nama pengguna sesi saat ini:

SELECT USER FROM DUAL
= UserName
Ya USER Setara dengan Oracle:

SELECT USER;
= postgres
USERENV Menampilkan informasi tentang sesi pengguna saat ini dengan konfigurasi parameter saat ini:

SELECT USERENV('LANGUAGE') FROM DUAL
= ENGLISH_AMERICA.AL32UTF8
Tidak T/A Meskipun tidak ada fungsi USERENV yang setara di Cloud SQL untuk PostgreSQL, parameter individual seperti USERENV('SID') dapat diambil menggunakan fungsi informasi sistem seperti PG_BACKGROUND_PID().
ROWID Server Oracle menetapkan ROWID unik pada setiap baris di setiap tabel untuk mengidentifikasi baris dalam tabel. ROWID adalah alamat baris yang berisi nomor objek data, blok data baris, posisi baris, dan file data. Sebagian dengan nama fungsi yang berbeda ctid ctid di Cloud SQL untuk PostgreSQL mengidentifikasi lokasi fisik versi baris dalam tabelnya, yang mirip dengan ROWID Oracle.
ROWNUM Menampilkan angka yang mewakili urutan baris yang dipilih oleh Oracle dari tabel atau tabel gabungan. Tidak LIMIT or ROW_NUMBER() Cloud SQL untuk PostgreSQL mendukung LIMIT dan OFFSET untuk tujuan serupa, bukan membatasi jumlah hasil yang ditampilkan oleh kueri menggunakan ROWNUM.

Fungsi window ROW_NUMBER() dapat menjadi pengganti solusi untuk ROWNUM Oracle dalam skenario lainnya. Namun, pengurutan hasil dan delta performa harus dipertimbangkan sebelum menggunakannya sebagai pengganti.
Fungsi agregat (grup)
Fungsi Oracle Spesifikasi atau implementasi fungsi Oracle Setara dengan Cloud SQL untuk PostgreSQL Fungsi yang sesuai dengan Cloud SQL untuk PostgreSQL Spesifikasi atau implementasi fungsi Cloud SQL untuk PostgreSQL
AVG Menampilkan nilai rata-rata kolom atau ekspresi. Ya AVG Setara dengan Oracle
COUNT Menampilkan jumlah baris yang ditampilkan oleh kueri. Ya COUNT Setara dengan Oracle
COUNT (DISTINCT) Menampilkan jumlah nilai unik dalam kolom atau ekspresi. Ya COUNT
(DISTINCT)
Setara dengan Oracle
MAX Menampilkan nilai maksimum kolom atau ekspresi. Ya MAX Setara dengan Oracle
MIN Menampilkan nilai minimum kolom atau ekspresi. Ya MIN Setara dengan Oracle
SUM Menampilkan jumlah nilai dari kolom atau ekspresi. Ya SUM Setara dengan Oracle
LISTAGG Menampilkan data dalam setiap grup berdasarkan satu baris yang ditentukan dalam klausul ORDER BY dengan menggabungkan nilai-nilai dari kolom ukuran:

SELECT LISTAGG(
DEPARTMENT_NAME, ', ')
WITHIN GROUP
(ORDER BY DEPARTMENT_NAME) DEPT
FROM DEPARTMENTS;

-- Single line results
= Accounting, Administration, Benefits, Construction
Tidak STRING_AGG Gunakan fungsi STRING_AGG Cloud SQL untuk PostgreSQL guna menampilkan hasil yang serupa dengan Oracle. Dapatkan perbedaan sintaksis dengan kasus tertentu:

SELECT STRING_AGG(DEPARTMENT_NAME, ', ' order by DEPARTMENT_NAME) FROM DEPARTMENTS;

-- Single line results
= Accounting, Administration, Benefits, Construction
Pengambilan Oracle 12c
Fungsi Oracle Spesifikasi atau implementasi fungsi Oracle Setara dengan Cloud SQL untuk PostgreSQL Fungsi yang sesuai dengan Cloud SQL untuk PostgreSQL Spesifikasi atau implementasi fungsi Cloud SQL untuk PostgreSQL
FETCH Mengambil baris data dari kumpulan hasil kueri multi-baris:

SELECT * FROM
EMPLOYEES
FETCH FIRST 10 ROWS ONLY;
Tidak LIMIT Gunakan klausul LIMIT Cloud SQL untuk PostgreSQL untuk mengambil kumpulan data tertentu saja:

SELECT * FROM EMPLOYEES LIMIT 10;

Pemfilteran dasar, operator, dan subkueri

Pemfilteran dasar, fungsi operator, dan subkueri bersifat relatif mudah selama proses konversi, sehingga tidak memerlukan upaya tambahan atau hanya memerlukan sedikit upaya.

Catatan konversi

Periksa dan tangani format tanggal karena format Oracle dan Cloud SQL untuk PostgreSQL menampilkan hasil default yang berbeda:

  • Fungsi SYSDATE Oracle secara default menampilkan 01-AUG-19.
  • Fungsi CURRENT_DATE PostgreSQL secara default menampilkan 2019-08-01 (tidak ada waktu bahkan dengan pemformatan). Untuk mengambil tanggal dan waktu saat ini, gunakan fungsi CURRENT_TIMESTAMP yang secara default menampilkan 01-08-2019 00:00:00.000000+00.
  • Format tanggal dan waktu dapat disetel menggunakan fungsi TO_CHAR Cloud SQL untuk PostgreSQL.
Fungsi atau subkueri Oracle Setara dengan Cloud SQL untuk PostgreSQL Fungsi atau subkueri yang sesuai dengan Cloud SQL untuk PostgreSQL Spesifikasi atau implementasi fungsi Cloud SQL untuk PostgreSQL
EXISTS/ NOT EXISTS Ya EXISTS/ NOT EXISTS SELECT * FROM DEPARTMENTS D
WHERE EXISTS (SELECT 1
FROM EMPLOYEES E
WHERE
E.DEPARTMENT_ID =
D.DEPARTMENT_ID);
IN/NOT IN Ya IN/NOT IN SELECT * FROM DEPARTMENTS D
WHERE DEPARTMENT_ID IN
(SELECT DEPARTMENT_ID
FROM EMPLOYEES E);

-- OR
SELECT * FROM EMPLOYEES
WHERE (EMPLOYEE_ID, DEPARTMENT_ID)
IN((100, 90));
LIKE/NOT LIKE Ya LIKE/NOT LIKE SELECT * FROM EMPLOYEES
WHERE FIRST_NAME LIKE '_e_n%';
BETWEEN/ NOT BETWEEN Ya BETWEEN/
NOT BETWEEN
SELECT * FROM EMPLOYEES
WHERE EXTRACT(YEAR FROM HIRE_DATE)
NOT BETWEEN 2001 and 2004;
AND/OR Ya AND/OR SELECT * FROM EMPLOYEES
WHERE DEPARTMENT_ID IN(100, 101)
AND (SALARY >= 1000 OR HIRE_DATE <= '2006-02-05');
SubQuery Ya SubQuery Cloud SQL untuk PostgreSQL mendukung subkueri di level SELECT, untuk pernyataan JOIN dan untuk melakukan pemfilteran di klausul WHERE/AND:

-- SELECT SubQuery
SELECT D.DEPARTMENT_NAME,
(SELECT AVG(SALARY) AS AVG_SAL
FROM EMPLOYEES E
WHERE E.DEPARTMENT_ID =
D.DEPARTMENT_ID) AVG_SAL
FROM DEPARTMENTS D;

-- JOIN SubQuery
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES E JOIN
(SELECT *
FROM DEPARTMENTS
WHERE LOCATION_ID = 2700) D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

-- Filtering Subquery
SELECT FIRST_NAME,
LAST_NAME,
SALARY
FROM EMPLOYEES
WHERE SALARY < (SELECT AVG(SALARY)
FROM EMPLOYEES);
Operator Ya Operator Cloud SQL untuk PostgreSQL mendukung semua operator dasar:

> | >= | < | <= | = | <> | !=

Fungsi analisis (atau fungsi jendela dan peringkat)

Fungsi analisis Oracle memperluas fungsi operasi SQL standar dengan menyediakan kemampuan untuk menghitung nilai agregat pada sekelompok baris (misalnya RANK(), ROW_NUMBER(), FIRST_VALUE()). Fungsi ini diterapkan ke data yang dipartisi secara logis dalam cakupan satu ekspresi kueri. Mereka biasanya digunakan dalam data warehousing, bersama dengan laporan dan analisis business intelligence.

Catatan konversi

Cloud SQL untuk PostgreSQL mendukung banyak fungsi analisis. Fungsi tersebut dikenal di Postgres sebagai fungsi agregat dan fungsi jendela. Jika aplikasi menggunakan fungsi yang kurang umum dan tidak didukung di Postgres, Anda harus mencari ekstensi yang didukung atau memindahkan logika ke lapisan aplikasi.

Tabel berikut berisi fungsi analisis Oracle yang paling umum.

Kelompok fungsi Fungsi terkait Didukung oleh Cloud SQL untuk PostgreSQL
Analisis dan peringkat RANK
AVERAGE_RANK
DENSE_RANK
ROW_NUMBER
PERCENT_RANK
CUME_DIST
NTILE
FIRST_VALUE
LAST_VALUE
OVER (PARTITION BY...)
Ya (Kecuali AVERAGE_RANK)
Hierarkis CONNECT BY
HIER_ANCESTOR
HIER_CHILD_COUNT
HIER_DEPTH
HIER_LEVEL
HIER_ORDER
HIER_PARENT
HIER_TOP
Tidak
Jeda LAG
LAG_VARIANCE LAG_VARIANCE_PERCENT LEAD
LEAD_VARIANCE LEAD_VARIANCE_PERCENT
Ya (khusus LAG dan LEAD)

Ekspresi tabel umum (CTE)

CTE menyediakan cara untuk menerapkan logika kode berurutan untuk menggunakan kembali kode SQL yang mungkin terlalu kompleks atau tidak efisien untuk beberapa penggunaan. CTE dapat diberi nama dan digunakan beberapa kali di berbagai bagian pernyataan SQL menggunakan klausul WITH. CTE didukung oleh Oracle dan Cloud SQL untuk PostgreSQL.

Contoh
Oracle dan Cloud SQL untuk PostgreSQL
WITH DEPT_COUNT
(DEPARTMENT_ID, DEPT_COUNT) AS
(SELECT DEPARTMENT_ID,
COUNT(*)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID)

SELECT E.FIRST_NAME ||' '|| E.LAST_NAME AS EMP_NAME,
D.DEPT_COUNT AS EMP_DEPT_COUNT
FROM EMPLOYEES E JOIN DEPT_COUNT D
USING (DEPARTMENT_ID)
ORDER BY 2 DESC;

Pernyataan MERGE

Pernyataan MERGE (atau UPSERT) menyediakan cara untuk menentukan satu pernyataan SQL yang secara kondisional melakukan operasi DML dalam satu operasi MERGE, bukan satu operasi DML, yang berjalan terpisah. Fungsi ini memilih data dari tabel sumber, kemudian, dengan menentukan struktur logis, secara otomatis melakukan beberapa operasi DML pada tabel target. Fitur ini membantu Anda menghindari penggunaan beberapa penyisipan, update, atau penghapusan. Perhatikan bahwa MERGE adalah pernyataan deterministik. Artinya, setelah sebuah baris diproses oleh pernyataan MERGE, baris tersebut tidak dapat lagi diproses menggunakan pernyataan MERGE yang sama.

Catatan konversi

Cloud SQL untuk PostgreSQL tidak mendukung fungsi MERGE, tidak seperti Oracle. Untuk menyimulasikan sebagian fungsi MERGE, Cloud SQL untuk PostgreSQL menyediakan pernyataan INSERT ... ON CONFLICT DO UPDATE:

  • INSERT… ON CONFLICT DO UPDATE: Jika baris yang disisipkan akan menyebabkan pelanggaran unik atau error pelanggaran batasan pengecualian, tindakan alternatif yang ditentukan dalam klausul ON CONFLICT DO UPDATE akan diambil, misalnya:
INSERT INTO tbl (a,b,c) VALUES (1,2,3)
  ON CONFLICT (a) DO UPDATE SET b = 2, c = 3;

Solusi lainnya adalah mengonversi fungsi MERGE menjadi prosedur tersimpan untuk mengelola operasi DML, menggunakan perintah INSERT, UPDATE, dan DELETE dengan penanganan pengecualian serta duplikasi.

Petunjuk pernyataan SQL

Oracle menyediakan banyak petunjuk kueri SQL yang memungkinkan pengguna memengaruhi perilaku pengoptimal, yang bertujuan untuk menghasilkan rencana eksekusi kueri yang lebih efisien. Cloud SQL untuk PostgreSQL tidak menawarkan level pernyataan SQL yang sebanding, yang memberi petunjuk mekanisme untuk memengaruhi pengoptimal.

Untuk memengaruhi rencana kueri yang dipilih oleh perencana kueri, Cloud SQL untuk PostgreSQL menyediakan kumpulan parameter konfigurasi yang dapat diterapkan pada tingkat sesi. Efek dari parameter konfigurasi ini berkisar dari mengaktifkan/menonaktifkan metode akses tertentu, hingga menyesuaikan konstanta biaya perencana. Misalnya, pernyataan berikut menonaktifkan penggunaan jenis rencana pemindaian berurutan seperti pemindaian tabel lengkap oleh perencana kueri:

SET ENABLE_SEQSCAN=FALSE;

Untuk menyesuaikan estimasi biaya perencana untuk pengambilan halaman disk acak (defaultnya adalah 4.0), gunakan pernyataan berikut:

SET RANDOM_PAGE_COST=2.0;

Mengurangi nilai ini akan menyebabkan Cloud SQL untuk PostgreSQL lebih memilih pemindaian indeks. Hal sebaliknya akan terjadi apabila nilai diperbesar.

Catatan konversi

Karena ada perbedaan mendasar antara pengoptimal Oracle dan Cloud SQL untuk PostgreSQL, serta fakta bahwa Cloud SQL untuk PostgreSQL tidak mendukung petunjuk kueri SQL bergaya Oracle, sebaiknya hapus semua petunjuk kueri saat bermigrasi ke Cloud SQL untuk PostgreSQL. Kemudian, lakukan pengujian performa yang ketat melalui alat Cloud SQL untuk PostgreSQL, periksa kueri menggunakan rencana eksekusi, dan sesuaikan parameter sesi atau instance sesuai dengan kasus penggunaan.

Rencana eksekusi

Tujuan utama dari rencana eksekusi adalah untuk memberikan gambaran mendalam mengenai pilihan yang dibuat oleh pengoptimal kueri untuk mengakses data database. Pengoptimal kueri akan menghasilkan rencana eksekusi untuk pernyataan SELECT, INSERT, UPDATE, dan DELETE untuk pengguna database, yang juga memungkinkan administrator memiliki pandangan yang lebih baik mengenai kueri tertentu dan operasi DML. Parameter ini sangat berguna saat Anda perlu melakukan penyesuaian performa kueri—misalnya, untuk menentukan performa indeks atau untuk menentukan apakah ada indeks yang hilang yang perlu dibuat.

Rencana eksekusi dapat dipengaruhi oleh volume data, statistik data, dan parameter instance (parameter global atau sesi).

Pertimbangan konversi

Rencana eksekusi bukanlah objek database yang perlu dimigrasikan, melainkan alat untuk menganalisis perbedaan performa antara Oracle dan Cloud SQL untuk PostgreSQL yang menjalankan pernyataan yang sama pada set data yang identik.

Cloud SQL untuk PostgreSQL tidak mendukung sintaksis, fungsi, atau output rencana eksekusi yang sama dengan Oracle.

Berikut adalah contoh rencana eksekusi:

Rencana eksekusi Oracle Rencana eksekusi Cloud SQL untuk PostgreSQL
SQL> EXPLAIN PLAN FOR
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = 105;

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY (FORMAT=>'ALL +OUTLINE'));

Plan hash value: 1833546154

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
postgres=> EXPLAIN SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = 105; QUERY PLAN -------------------------------------------------------------------------------- Index Scan using emp_emp_id_pk on employees (cost=0.14..8.16 rows=1 width=71) Index Cond: (employee_id = '105'::numeric) (2 rows)

Prosedur tersimpan, fungsi, dan pemicu

PL/SQL adalah bahasa prosedural Oracle tambahan untuk membuat, menyimpan, dan menerapkan solusi berbasis kode dalam database. Secara umum, prosedur dan fungsi database yang tersimpan adalah elemen kode yang terdiri dari bahasa prosedural tambahan ANSI SQL dan SQL—misalnya, PL/SQL untuk bahasa prosedural Oracle dan MySQL untuk MySQL. PL/pgSQL adalah untuk bahasa prosedural tambahan PostgreSQL.

Tujuan dari prosedur dan fungsi yang tersimpan ini adalah memberikan solusi untuk persyaratan yang lebih cocok dijalankan dari dalam database, bukan dari aplikasi (misalnya, performa, kompatibilitas, dan keamanan). Meskipun prosedur dan fungsi tersimpan menggunakan PL/SQL, prosedur tersimpan digunakan terutama untuk melakukan operasi DDL/DML, dan fungsi terutama digunakan untuk melakukan penghitungan guna menampilkan hasil tertentu.

PL/SQL ke PL/pgSQL

Dari perspektif migrasi Oracle PL/SQL ke Cloud SQL untuk PostgreSQL, PL/pgSQL mirip dengan Oracle PL/SQL dalam hal struktur dan sintaksisnya. Namun, ada beberapa perbedaan utama yang memerlukan migrasi kode. Misalnya, jenis data berbeda antara Oracle dan Cloud SQL untuk PostgreSQL sehingga terjemahan sering kali diperlukan untuk memastikan bahwa kode yang dimigrasikan menggunakan nama jenis data yang sesuai yang didukung oleh Cloud SQL untuk PostgreSQL. Untuk pembahasan mendetail tentang perbedaan antara kedua bahasa tersebut, lihat Melakukan porting dari Oracle PL/SQL.

Hak istimewa dan keamanan objek kode

Di Oracle, untuk membuat prosedur atau fungsi tersimpan, pengguna harus memiliki hak istimewa sistem CREATE PROCEDURE (untuk membuat prosedur atau fungsi di bawah pengguna lain, pengguna database harus memiliki hak istimewa CREATE ANY PROCEDURE). Untuk menjalankan prosedur atau fungsi tersimpan, pengguna database harus memiliki hak istimewa EXECUTE.

Di PostgreSQL, untuk membuat prosedur kode atau fungsi, pengguna harus memiliki hak istimewa USAGE. Untuk menjalankan prosedur atau fungsi, pengguna harus memiliki hak istimewa EXECUTE pada prosedur atau fungsi.

Secara default, prosedur atau fungsi PL/pgSQL ditetapkan sebagai SECURITY INVOKER, yang berarti prosedur atau fungsi akan dijalankan dengan hak istimewa pengguna yang memanggilnya. Atau, SECURITY DEFINER dapat ditentukan agar fungsi dijalankan dengan hak istimewa pengguna yang memilikinya.

Prosedur dan sintaksis fungsi yang tersimpan di Cloud SQL untuk PostgreSQL

Contoh berikut menunjukkan sintaksis prosedur dan fungsi tersimpan Cloud SQL untuk PostgreSQL:

CREATE [ OR REPLACE ] PROCEDURE
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } ...

CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS rettype
      | RETURNS TABLE ( column_name column_type [, ...] ) ]
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | WINDOW
    | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | PARALLEL { UNSAFE | RESTRICTED | SAFE }
    | COST execution_cost
    | ROWS result_rows
    | SUPPORT support_function
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } ...

Pemicu

Pemicu adalah prosedur tersimpan yang diaktifkan saat terjadi peristiwa tertentu. Di Oracle, peristiwa pemicu dikaitkan dengan tabel, tampilan, skema, atau database. Jenis peristiwa pemicu meliputi:

  • Pernyataan Bahasa Pengolahan Data (DML) (misalnya, INSERT, UPDATE, DELETE)
  • Pernyataan Bahasa Definisi Data (DDL) (misalnya, CREATE, ALTER, DROP)
  • Peristiwa Database (misalnya, LOGON, STARTUP, SHUTDOWN)

Pemicu Oracle dapat berupa salah satu dari jenis berikut:

  • Pemicu sederhana: Diaktifkan hanya sekali, sebelum atau setelah peristiwa pemicu yang ditentukan
  • Pemicu gabungan: Diaktifkan di beberapa peristiwa
  • Pemicu INSTEAD OF: Jenis pemicu DML khusus untuk memberikan mekanisme update transparan untuk tampilan yang kompleks dan tidak dapat diedit
  • Pemicu sistem: Diaktifkan pada peristiwa database tertentu

Di Cloud SQL untuk PostgreSQL, pemicu diaktifkan sebelum atau setelah operasi DML pada tabel, tampilan, atau tabel asing tertentu. Pemicu INSTEAD OF didukung untuk menyediakan mekanisme update pada tampilan. Pemicu pada operasi DDL disebut pemicu peristiwa. Cloud SQL untuk PostgreSQL tidak mendukung pemicu sistem Oracle berdasarkan peristiwa database.

Tidak seperti pemicu Oracle, pemicu Cloud SQL untuk PostgreSQL tidak mendukung penggunaan blok PL/pgSQL anonim sebagai isi pemicu. Fungsi bernama yang menggunakan nol argumen atau lebih dan menampilkan pemicu jenis harus disediakan dalam deklarasi pemicu. Fungsi ini dijalankan saat pemicu diaktifkan.

Sintaksis pemicu peristiwa dan pemicu Cloud SQL untuk PostgreSQL

Contoh berikut menunjukkan sintaksis pemicu dan pemicu peristiwa Cloud SQL untuk PostgreSQL:

CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

event dapat berupa salah satu dari: INSERT, UPDATE [ OF column_name [, ... ] ], DELETE, TRUNCATE

CREATE EVENT TRIGGER name
    ON event
    [ WHEN filter_value IN (filter_value [, ... ]) [ AND ... ] ]
    EXECUTE { FUNCTION | PROCEDURE } function_name()

event dapat berupa salah satu dari: ddl_command_start, ddl_command_end, table_rewrite, sql_drop

filter_value hanya boleh berupa: TAG

filter_value dapat berupa salah satu tag perintah yang didukung.

Langkah selanjutnya