Migrer des utilisateurs Oracle® vers Cloud SQL pour PostgreSQL : requêtes, procédures stockées, fonctions et déclencheurs

Ce document fait partie d'une série qui fournit des informations clés et des conseils pour planifier et effectuer des migrations de bases de données Oracle® 11g/12c vers Cloud SQL pour PostgreSQL version 12. Outre la section d'introduction à la configuration, la série inclut les éléments suivants :

Requêtes

Oracle et Cloud SQL pour PostgreSQL sont compatibles avec la norme ANSI SQL. Il est donc simple de migrer des instructions SQL en n'utilisant que des éléments de syntaxe de base (par exemple, ne pas spécifier de fonctions scalaires ni aucune autre fonctionnalité étendue d'Oracle). La section suivante aborde les éléments de requête Oracle courants et leurs équivalents dans Cloud SQL pour PostgreSQL.

Syntaxe SELECT et FROM de base

Nom de la fonctionnalité ou syntaxe Oracle Présentation ou mise en œuvre dans Oracle Compatibilité avec Cloud SQL pour PostgreSQL Solution Cloud SQL pour PostgreSQL correspondante ou alternative
Syntaxe SQL de base pour la récupération de données SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY 		Oui SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
SELECT
pour l'impression en sortie		 SELECT 1 FROM DUAL Oui SELECT 1
Alias de colonne SELECT COL1 AS C1 Oui SELECT COL1 AS C1
OR
SELECT COL1 C1
Sensibilité à la casse des noms de table Pas de sensibilité à la casse
(par exemple, le nom de la table peut être orders et/ou ORDERS).		 Oui Les noms ne sont pas sensibles à la casse, sauf s'ils sont entre guillemets (par exemple, orders et ORDERS sont traités de la même manière, tandis que "orders" et "ORDERS" sont traités différemment).

Pour en savoir plus sur la syntaxe SELECT de Cloud SQL pour PostgreSQL, consultez la documentation.

Vues intégrées

  • Les vues intégrées (également appelées tables dérivées) sont des instructions SELECT situées dans la clause FROM et utilisées comme sous-requêtes.
  • Les vues intégrées peuvent simplifier les requêtes complexes en supprimant les calculs composés ou en éliminant les opérations de jointure, tout en regroupant plusieurs requêtes distinctes en une seule requête simplifiée.
  • Note de conversion : Les vues intégrées Oracle ne nécessitent pas l'utilisation d'alias, tandis que Cloud SQL pour PostgreSQL requiert des alias spécifiques pour chaque vue intégrée.

Le tableau suivant présente un exemple de conversion d'Oracle vers Cloud SQL pour PostgreSQL sous forme d'une vue intégrée.

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

Le résultat ressemble à ce qui suit :

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

Sans alias pour la vue intégrée :

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.

Ajout d'un alias à la vue intégrée :

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

Le résultat ressemble à ce qui suit :

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

Instructions JOIN

Les instructions JOIN d'Oracle sont compatibles avec les instructions JOIN de Cloud SQL pour PostgreSQL. Toutefois, l'opérateur de jointure (+) d'Oracle n'est pas compatible avec Cloud SQL pour PostgreSQL. Pour obtenir le même résultat, vous devez le convertir en syntaxe SQL standard pour les jointures externes.

Le tableau suivant présente un exemple de conversion JOIN.

Type d'instruction JOIN Oracle Compatible avec Cloud SQL pour PostgreSQL Syntaxe JOIN de Cloud SQL pour PostgreSQL
INNER JOIN Oui SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
CROSS JOIN Oui SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E CROSS JOIN DEPARTMENTS D;
FULL JOIN [ OUTER ] Oui SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E FULL JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
LEFT JOIN [ OUTER ] Oui SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E LEFT JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
RIGHT JOIN [ OUTER ] Oui SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E RIGHT JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
SUBQUERY Oui 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 et MINUS

Cloud SQL pour PostgreSQL est compatible avec les opérateurs UNION, UNION ALL et INTERSECT d'Oracle. L'opérateur MINUS n'est pas compatible. Cependant, Cloud SQL pour PostgreSQL met en œuvre l'opérateur EXCEPT, qui est l'équivalent de l'opérateur MINUS d'Oracle. De plus, Cloud SQL pour PostgreSQL est compatible avec les opérateurs INTERSECT ALL et EXCEPT ALL, qui ne sont pas compatibles avec Oracle.

  • UNION : joint les ensembles de résultats d'au moins deux instructions SELECT et supprime les enregistrements en double.
  • UNION ALL : joint les ensembles de résultats d'au moins deux instructions SELECT sans éliminer les enregistrements en double.
  • INTERSECT : renvoie l'intersection de deux instructions SELECT ou plus uniquement si un enregistrement existe dans les deux ensembles de données. Les enregistrements en double ne sont pas éliminés.
  • INTERSECT ALL (Cloud SQL pour PostgreSQL uniquement) : renvoie l'intersection de deux instructions SELECT ou plus uniquement si un enregistrement existe dans les deux ensembles de données.
  • MINUS (EXCEPT (dans Cloud SQL pour PostgreSQL) : compare deux instructions SELECT ou plus, en renvoyant uniquement les lignes distinctes de la première requête qui ne sont pas renvoyées par les autres instructions.
  • EXCEPT ALL (Cloud SQL pour PostgreSQL uniquement) : compare deux instructions SELECT ou plus, en renvoyant uniquement les lignes de la première requête qui ne sont pas renvoyées par les autres instructions sans éliminer les enregistrements en double.

Notes de conversion

Lors de la conversion d'opérateurs MINUS d'Oracle vers Cloud SQL pour PostgreSQL, utilisez plutôt les opérateurs EXCEPT.

Exemples

Fonction Oracle Mise en œuvre dans Oracle Compatibilité avec Cloud SQL pour PostgreSQL Solution Cloud SQL pour PostgreSQL correspondante ou alternative
UNION SELECT COL1 FROM TBL1
UNION
SELECT COL1 FROM TBL2 		Oui SELECT COL1 FROM TBL1
UNION
SELECT COL1 FROM TBL2
UNION ALL SELECT COL1 FROM TBL1
UNION ALL
SELECT COL1 FROM TBL2 		Oui SELECT COL1 FROM TBL1
UNION ALL
SELECT COL1 FROM TBL2
INTERSECT SELECT COL1 FROM TBL1
INTERSECT
SELECT COL1 FROM TBL2 		Oui SELECT COL1 FROM TBL1
INTERSECT
SELECT COL1 FROM TBL2
MINUS SELECT COL1 FROM TBL1
MINUS
SELECT COL1 FROM TBL2 		Oui (de Convert MINUS à EXCEPT dans PostgreSQL) SELECT COL1 FROM TBL1
EXCEPT
SELECT COL1 FROM TBL2

Fonctions scalaires (à une ligne) et de groupe

Cloud SQL pour PostgreSQL fournit une longue liste de fonctions scalaires (à une seule ligne) et d'agrégation. Certaines fonctions Cloud SQL pour PostgreSQL sont similaires à leurs équivalents Oracle (nom et fonctionnalité identiques, ou nom différent et fonctionnalité similaire). Bien que les fonctions Cloud SQL pour PostgreSQL portent des noms identiques à leurs équivalents Oracle, elles présentent parfois des fonctionnalités différentes.

Les tableaux suivants indiquent quand Oracle et Cloud SQL pour PostgreSQL sont identiques en nom et fonctionnalité (mention "Oui") et quand une conversion est recommandée (tous les cas autres que "Oui").

Fonctions de caractères
Fonction Oracle Spécification ou mise en œuvre de la fonction Oracle Équivalent Cloud SQL pour PostgreSQL Fonction correspondante dans Cloud SQL pour PostgreSQL Mise en œuvre ou spécification de fonction Cloud SQL pour PostgreSQL
CONCAT Renvoie str1 concaténé avec str2 :

CONCAT('A', 1) = A1 		Yes CONCAT Équivalent dans Oracle :

CONCAT('A', 1) = A1
LOWER/UPPER Renvoie char, avec toutes les lettres en minuscules ou en majuscules :

LOWER('SQL') = sql 		Yes LOWER/UPPER Équivalent dans Oracle :

LOWER('SQL') = sql
LPAD/RPAD Renvoie expr1, complétée à gauche ou à droite (pour atteindre une longueur de n caractères) à l'aide de la séquence de caractères de expr2 :

LPAD('A',3,'*') = **A 		Yes LPAD/RPAD Équivalent dans Oracle :

LPAD('A',3,'*') = **A
SUBSTR Renvoie une partie de char qui commence à la position du caractère sous la forme d'une sous-chaîne
dont la longueur est de :

SUBSTR('PostgreSQL', 8, 3)
= SQL 		En partie SUBSTR Identique à Oracle lorsque la position de départ est un nombre positif.

SUBSTR('PostgreSQL', 8, 3)
= SQL
Le fait de fournir un nombre négatif en tant que position de départ dans Oracle résulte en une opération de sous-chaîne appliquée à partir de la fin de la chaîne, ce qui est différent du comportement dans Cloud SQL pour PostgreSQL. Utilisez la fonction RIGHT en remplacement si vous souhaitez bénéficier du comportement habituel d'Oracle.
INSTR Renvoie la position (index) d'une chaîne spécifique à partir d'une chaîne donnée :

INSTR('PostgreSQL', 'e')
= 7 		Non ND Cloud SQL pour PostgreSQL ne dispose pas de fonction instr intégrée. Une fonction instr compatible avec Oracle peut être mise en œuvre à l'aide de PL/pgSQL.
REPLACE Renvoie char avec chaque occurrence d'une chaîne de recherche remplacée
par une chaîne de remplacement :

REPLACE('ORADB', 'ORA', 'PostgreSQL')
= PostgreSQLDB		 En partie REPLACE Le paramètre de chaîne de remplacement est facultatif dans Oracle alors qu'il est obligatoire dans Cloud SQL pour PostgreSQL. Lorsque le paramètre est omis, Oracle supprime toutes les occurrences des chaînes de recherche. Vous pouvez obtenir le même comportement dans Cloud SQL pour PostgreSQL en fournissant une chaîne vide comme chaîne de remplacement.

REPLACE('ORADB', 'ORA', 'PostgreSQL')
= PostgreSQLDB
TRIM Coupe les caractères de début ou de fin (ou les deux) d'une chaîne :

TRIM(both '-' FROM '-PostgreSQL-')
= PostgreSQL
TRIM(' PostgreSQL ')
= PostgreSQL 		Yes TRIM Équivalent dans Oracle :

TRIM(both '-' FROM '-PostgreSQL-')
= PostgreSQL
TRIM(' PostgreSQL ')
= PostgreSQL
LTRIM/RTRIM Supprime de l'extrémité gauche ou droite de la chaîne tous les caractères qui
apparaissent dans la recherche :

LTRIM(' PostgreSQL', ' ')
= PostgreSQL 		Yes LTRIM/RTRIM Équivalent dans Oracle :

LTRIM(' PostgreSQL', ' ') = PostgreSQL
ASCII Renvoie la représentation décimale (dans le jeu de caractères de la base de données) du premier caractère de "char" :

ASCII('A') = 65 		Yes ASCII Équivalent dans Oracle :

ASCII('A') = 65
CHR Renvoie la valeur du code ASCII (valeur numérique comprise entre 0 et 225) pour un caractère :

CHR(65) = A 		Yes CHAR Équivalent dans Oracle :

CHR(65) = A
LENGTH Renvoie la longueur d'une chaîne donnée :

LENGTH ('PostgreSQL') = 10 		Yes LENGTH Équivalent dans Oracle :

LENGTH ('PostgreSQL') = 10
REGEXP_REPLACE Recherche un modèle d'expression régulière dans une chaîne :

REGEXP_REPLACE('John', '[hn].', '1') = Jo1 		Yes REGEXP_REPLACE Équivalent dans Oracle :

REGEXP_REPLACE('John', '[hn].', '1') = Jo1
REGEXP_SUBSTR Étend la fonctionnalité de la fonction SUBSTR en recherchant un
modèle d'expression régulière dans une chaîne :

REGEXP_SUBSTR('https://console.cloud.google.com/sql/instances','https://([[:alnum:]]+\.?){3,4}/?')
= https://console.cloud.google.com/ 		Non ND Utilisez la fonction REGEXP_MATCH de PostgreSQL pour obtenir une fonctionnalité similaire.
REGEXP_COUNT Renvoie le nombre d'occurrences d'un modèle dans une chaîne source. Non ND Utilisez la fonction REGEXP_MATCH de PostgreSQL pour obtenir une fonctionnalité similaire.
REGEXP_INSTR Recherche une position de chaîne (index)
d'un motif d'expression régulière		 Non ND Convertissez la fonctionnalité en couche d'application.
REVERSE Renvoie une chaîne inversée.

REVERSE('PostgreSQL') = LQSergtsoP 		Yes REVERSE Équivalent à Oracle :

REVERSE('PostgreSQL') = LQSergtsoP
Fonctions numériques
Fonction Oracle Spécification ou mise en œuvre de la fonction Oracle Équivalent Cloud SQL pour PostgreSQL Fonction correspondante dans Cloud SQL pour PostgreSQL Mise en œuvre ou spécification de fonction Cloud SQL pour PostgreSQL
ABS Valeur absolue de n :

ABS(-4.6) = 4.6 		Yes ABS Équivalent dans Oracle :

ABS(-4.6) = 4.6
CEIL Renvoie le plus petit entier supérieur ou égal à n :

CEIL(21.4) = 22 		Yes CEIL Équivalent dans Oracle :

CEIL(21.4) = 22
FLOOR Renvoie le plus grand nombre entier inférieur ou égal à n :

FLOOR(-23.7) = -24 		Yes FLOOR Équivalent dans Oracle :

FLOOR(-23.7) = -24
MOD Renvoie le reste de m divisé par n :

MOD(10, 3) = 1 		Yes MOD Équivalent dans Oracle :

MOD(10, 3) = 1
ROUND Renvoie n arrondi à un nombre entier de chiffres à droite de la virgule :

ROUND(1.39, 1) = 1.4 		Yes ROUND Équivalent dans Oracle :

ROUND(1.39, 1) = 1.4
TRUNC
(numéro)		 Renvoie n1 tronqué à n2 décimales :

TRUNC(99.999) = 99
TRUNC(99.999, 0) = 99 		Yes TRUNCATE
(number)		 Équivalent dans Oracle :

TRUNC(99.999) = 99
TRUNC(99.999, 0) = 99
Fonctions de date et heure
Fonction Oracle Spécification ou mise en œuvre de la fonction Oracle Équivalent Cloud SQL pour PostgreSQL Fonction correspondante dans Cloud SQL pour PostgreSQL Mise en œuvre ou spécification de fonction Cloud SQL pour PostgreSQL
SYSDATE Renvoie la date et l'heure actuelles définies pour le système d'exploitation sur lequel réside le serveur de base de données :

SELECT SYSDATE FROM DUAL
= 31-JUL-2019 		En partie, avec un nom et une mise en forme de fonction différents CURRENT_TIMESTAMP La fonction CURRENT_TIMESTAMP renvoie un format de date et d'heure différent de la fonction SYSDATE d'Oracle :

SELECT CURRENT_TIMESTAMP
= 2019-07-31 06:46:40.171477+00
SYSTIMESTAMP Renvoie la date du système, y compris les secondes fractionnelles et le fuseau horaire :

SELECT SYSTIMESTAMP FROM DUAL
= 01-JAN-19 07.37.11.622187000 AM +00:00 		En partie avec un nom de fonction différent CURRENT_TIMESTAMP Cloud SQL pour PostgreSQL renvoie un format de date/heure différent de celui d'Oracle. Le format de date doit correspondre aux formats de date et d'heure d'origine :

SELECT CURRENT_TIMESTAMP
= 2019-01-31 07:37:11.622187+00
LOCAL TIMESTAMP Renvoie la date et l'heure actuelles dans le fuseau horaire de la session dans une valeur dont le type de données est TIMESTAMP :

SELECT LOCALTIMESTAMP FROM DUAL
= 01-JAN-19 10.01.10.123456 PM 		En partie avec une mise en forme de date/heure différente LOCAL TIMESTAMP Cloud SQL pour PostgreSQL renvoie un format de date/heure différent de celui d'Oracle. Le format de la date doit être identique au format de date/heure d'origine :

SELECT LOCALTIMESTAMP
= 2019-01-31 07:37:11.622187+00
CURRENT_DATE Renvoie la date actuelle dans le fuseau horaire de la session :

SELECT CURRENT_DATE FROM DUAL
= 31-JAN-19 		En partie avec une mise en forme de date/heure différente CURRENT_ DATE Cloud SQL pour PostgreSQL renvoie un format de date/heure différent de celui d'Oracle. Le format de la date doit être identique au format de date/heure d'origine :

SELECT CURRENT_DATE
= 2019-01-31
CURRENT_TIMESTAMP Renvoie la date et l'heure actuelles dans le fuseau horaire de la session :

SELECT CURRENT_TIMESTAMP FROM DUAL
= 31-JAN-19 06.54.35.543146 AM +00:00 		En partie, avec une mise en forme de date/heure différente CURRENT_TIMESTAMP Cloud SQL pour PostgreSQL renvoie un format de date et d'heure différent de celui d'Oracle. Le format de la date doit être identique au format de date/heure d'origine :

SELECT CURRENT_TIMESTAMP FROM DUAL
= 2019-01-31 07:37:11.622187+00s
ADD_MONTHS Renvoie la date et un nombre entier de mois :

ADD_MONTHS(SYSDATE, 1)
= 31-JAN-19 		Non Non disponible Pour obtenir la même fonctionnalité dans Cloud SQL pour PostgreSQL, utilisez les opérateurs + / - et spécifiez l'intervalle de temps :

SELECT CURRENT_TIMESTAMP + INTERVAL '1 MONTH'
= 2019-01-31 07:37:11.622187+00s
EXTRACT
(partie de la date)		 Renvoie la valeur d'un champ de date/heure spécifié à partir d'une expression de date/heure ou d'intervalle :

EXTRACT(YEAR FROM DATE '2019-01-31')
= 2019 		Yes EXTRACT
(partie de la date)		 Équivalent dans Oracle :

EXTRACT(YEAR FROM DATE '2019-01-31')
= 2019
LAST_DAY Renvoie la date du dernier jour du mois contenant la date spécifiée :

LAST_DAY('01-JAN-2019')
= 31-JAN-19 		Non ND Pour contourner le problème, utilisez DATE_TRUNC et un opérateur + pour calculer le dernier jour du mois. Le format de date doit être identique à la mise en forme de date/heure d'origine :

SELECT DATE_TRUNC('MONTH', DATE '01-JAN-2019') + INTERVAL '1 MONTH -1 DAY'
= 2019-01-31
MONTH_BETWEEN Renvoie le nombre de mois compris entre les dates date1 et date2 :

MONTHS_BETWEEN(SYSDATE, SYSDATE-60)
= 1.96 		En partie,
avec
un format de date/heure différent de la fonction		 AGE La fonction Cloud SQL pour PostgreSQL AGE renvoie l'intervalle entre deux horodatages :

AGE(DATE '01-JAN-2019', DATE '01-JAN-2019' - 60)
= 1 mon 29 days
Pour parvenir aux mêmes valeurs que la fonction Oracle MONTH_BETWEEN, une conversion plus spécifique est requise.
TO_CHAR (date/heure) Convertit une date/heure ou un horodatage en type de données ou en valeur de VARCHAR2
type de données au format spécifié par le format de date :

TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MI:SS')
= 01-01-2019 10:01:01 		Yes To_CHAR Équivalent dans Oracle :

TO_CHAR(CURRENT_TIMESTAMP,'DD-MM-YYYY HH24:MI:SS');
01-01-2019 10:01:01
Fonctions d'encodage et de décodage
Fonction Oracle Spécification ou mise en œuvre de la fonction Oracle Équivalent Cloud SQL pour PostgreSQL Fonction correspondante dans Cloud SQL pour PostgreSQL Mise en œuvre ou spécification de fonction Cloud SQL pour PostgreSQL
DECODE Compare l'expression à chaque valeur de recherche une par une à l'aide d'une instruction IF-THEN-ELSE. Non CASE Utilisez l'instruction Cloud SQL pour PostgreSQL CASE pour obtenir une fonctionnalité similaire.
DUMP Renvoie une valeur VARCHAR2 contenant le code du type de données, la longueur en octets et la représentation interne de l'expression. Non N/A Non compatible
ORA_HASH Calcule une valeur de hachage pour une expression donnée. Non MD5 / SHA224 / SHA256 / SHA385 / SHA512 Utilisez la fonction Cloud SQL pour PostgreSQL MD5 pour la somme de contrôle à 128 bits ou la fonction SHA pour la somme de contrôle à 160 bits
afin de générer des valeurs de hachage.
Fonctions de conversion
Fonction Oracle Spécification ou mise en œuvre de la fonction Oracle Équivalent Cloud SQL pour PostgreSQL Fonction correspondante dans Cloud SQL pour PostgreSQL Mise en œuvre ou spécification de fonction Cloud SQL pour PostgreSQL
CAST Convertit un type de données intégré ou une valeur de type collection en un autre type de données intégré ou valeur de type collection :

CAST('1' as int) + 1
= 2 		En partie CAST La fonction Cloud SQL pour PostgreSQLCAST est similaire à celle d'Oracle CAST mais, dans certains cas, elle doit être ajustée en raison des différences de type de données entre les deux bases de données :

CAST('1' as int) + 1
= 2
CONVERT Convertit une chaîne de caractères d'un jeu de caractères en un autre :

CONVERT ( 'Ä Ê Í Õ Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1')
= ?? ?? ?? A B C 		En partie CONVERT La fonction CONVERT de Cloud SQL pour PostgreSQL renvoie une valeur bytea qui est une chaîne binaire plutôt que VARCHAR ou TEXT. Les ensembles de caractères compatibles avec PostgreSQL sont également différents de ceux d'Oracle.

CONVERT('Ä Ê Í Õ Ø A B C D E', 'UTF8', 'LATIN1')
= [Binary representation of the string in LATIN1 encoding]
TO_CHAR
(chaîne/numérique)		 La fonction convertit un nombre ou une date en chaîne :

TO_CHAR(22.73,'$99.9')
= $22.7 		En partie TO_CHAR La fonction TO_CHAR de Cloud SQL pour PostgreSQL offre une fonctionnalité semblable à celle d'Oracle. Cloud SQL pour PostgreSQL accepte une liste de chaînes de mise en forme légèrement différente. Par défaut, Cloud SQL pour PostgreSQL réserve une colonne pour le signe. Il y aura donc un espace avant les nombres positifs. Ce comportement peut être évité en utilisant le préfixe FM :

TO_CHAR(22.73,'FM$99.9')
= $22.7
TO_DATE La fonction Oracle TO_DATE convertit une chaîne en date en utilisant le format de date/heure spécifique à la source :

TO_DATE('2019/01/01', 'yyyy-mm-dd')
= 01-JAN-2019 		En partie TO_DATE La fonction TO_DATE de Cloud SQL pour PostgreSQL offre une fonctionnalité semblable à celle d'Oracle. Cloud SQL pour PostgreSQL accepte une liste de chaînes de mise en forme légèrement différente :

TO_DATE('2019/01/01', 'yyyy-mm-dd')
= 2019-01-01
TO_NUMBER Convertit l'expression en une valeur d'un type de données NUMBER :

TO_NUMBER('01234')
= 1234 		En partie TO_NUMBER La fonction TO_NUMBER de Cloud SQL pour PostgreSQL requiert une chaîne de mise en forme en tant qu'entrée, bien qu'elle soit facultative dans Oracle :

TO_NUMBER('01234','99999')
= 1234
Vous pouvez également utiliser la fonction CAST pour les conversions ne nécessitant pas de chaînes de mise en forme complexes :

CAST('01234' AS NUMERIC)
= 1234
Fonctions SELECT conditionnelles
Fonction Oracle Spécification ou mise en œuvre de la fonction Oracle Équivalent Cloud SQL pour PostgreSQL Fonction correspondante dans Cloud SQL pour PostgreSQL Mise en œuvre ou spécification de fonction Cloud SQL pour PostgreSQL
CASE L'instruction CASE choisit parmi une séquence de conditions et exécute
une instruction correspondante avec la syntaxe suivante :

CASE WHEN condition THEN result [WHEN ...] [ELSE result] END 		Yes CASE Équivalent dans Oracle :

CASE WHEN condition THEN result [WHEN ...] [ELSE result] END
Fonctions nulles
Fonction Oracle Spécification ou mise en œuvre de la fonction Oracle Équivalent Cloud SQL pour PostgreSQL Fonction correspondante dans Cloud SQL pour PostgreSQL Mise en œuvre ou spécification de fonction Cloud SQL pour PostgreSQL
COALESCE Renvoie la première expression non nulle de la liste d'expressions :

COALESCE(null, '1', 'a')
= a 		Yes COALESCE Équivalent dans Oracle :

COALESCE(null, '1', 'a')
= 1
NULLIF Compare expr1 et expr2. Si les expression sont égales, la fonction renvoie la valeur "null". Si elles ne sont pas égales, la fonction renvoie expr1 :

NULLIF('1', '2')
= 1 		Yes NULLIF Équivalent dans Oracle :

NULLIF('1', '2')
= 1
NVL Remplace la valeur "null" (renvoyée sous forme de valeur vide) par une chaîne dans les résultats d'une requête :

NVL(null, 'a')
= a 		Non COALESCE Vous pouvez également utiliser la fonction COALESCE :

COALESCE(null, 'a')
= a
NVL2 Détermine la valeur renvoyée par une requête selon qu'une expression
spécifiée est nulle ou non nulle.		 Non COALESCE Vous pouvez également utiliser la fonction COALESCE :

COALESCE(null, 1, 'a')
= 1
Fonctions d'environnement et d'identification
Fonction Oracle Spécification ou mise en œuvre de la fonction Oracle Équivalent Cloud SQL pour PostgreSQL Fonction correspondante dans Cloud SQL pour PostgreSQL Mise en œuvre ou spécification de fonction Cloud SQL pour PostgreSQL
SYS_GUID Génère et renvoie un identifiant global unique (valeur RAW) composé
de 16 octets :

SELECT SYS_GUID() FROM DUAL
= 8EFA4A31468B4C6DE05011AC0200009E 		En partie, avec un nom et un format de fonction différents UUID_GENERATE_V4 CloudSQL pour Cloud SQL pour PostgreSQL est compatible avecExtension de uuid-ossp qui fournit une liste de fonctions de génération d'UUID telles que UUID_GENERATE_V4 :

SELECT UUID_GENERATE_v4()
= eaf356a6-2847-478d-af3b-6883f5ac6af2
UID Renvoie un entier qui identifie de manière unique l'utilisateur de la session (l'utilisateur
qui s'est connecté) :

SELECT UID FROM DUAL
= 43 		Non N/A Non disponible
USER Renvoie le nom de l'utilisateur de la session actuelle :

SELECT USER FROM DUAL
= UserName 		Yes USER Équivalent dans Oracle :

SELECT USER;
= postgres
USERENV Renvoie des informations sur la session utilisateur actuelle avec la configuration de paramètre actuelle :

SELECT USERENV('LANGUAGE') FROM DUAL
= ENGLISH_AMERICA.AL32UTF8		 Non ND Bien qu'il n'existe pas de fonction USERENV équivalente dans Cloud SQL pour PostgreSQL, des paramètres individuels tels que USERENV('SID') peuvent être récupérés à l'aide des fonctions d'informations système telles que PG_BACKGROUND_PID().
ROWID Le serveur Oracle attribue à chaque ligne de chaque table un ROWID unique pour identifier la ligne dans la table. ROWID est l'adresse de la ligne contenant le numéro de l'objet de données, le bloc de données de la ligne, la position de la ligne et le fichier de données. En partie avec un nom de fonction différent ctid Dans Cloud SQL pour PostgreSQL, ctid identifie l'emplacement physique de la version de ligne dans sa table, de manière similaire à la méthode ROWID d'Oracle.
ROWNUM Renvoie un nombre représentant l'ordre dans lequel une ligne est sélectionnée par Oracle dans une table ou des tables jointes. Non LIMIT or ROW_NUMBER() Au lieu de limiter le nombre de résultats renvoyés par les requêtes à l'aide de ROWNUM, Cloud SQL pour PostgreSQL est compatible avec LIMIT et OFFSET à des fins similaires.

La fonction de fenêtrage ROW_NUMBER() permet de remplacer l'élément ROWNUM d'Oracle pour les autres scénarios. Toutefois, l'ordre des résultats et les deltas de performances doivent être pris en compte avant de l'utiliser en tant que remplacement.
Fonctions d'agrégation (groupe)
Fonction Oracle Spécification ou mise en œuvre de la fonction Oracle Équivalent Cloud SQL pour PostgreSQL Fonction correspondante dans Cloud SQL pour PostgreSQL Mise en œuvre ou spécification de fonction Cloud SQL pour PostgreSQL
AVG Renvoie la valeur moyenne dans la colonne ou l'expression. Oui AVG Équivalent à Oracle
COUNT Renvoie le nombre de lignes renvoyées par une requête. Oui COUNT Équivalent à Oracle
COUNT (DISTINCT) Renvoie le nombre de valeurs uniques dans la colonne ou l'expression. Oui COUNT
(DISTINCT) 		Équivalent à Oracle
MAX Renvoie la valeur maximale dans la colonne ou l'expression. Oui MAX Équivalent à Oracle
MIN Renvoie la valeur minimale dans la colonne ou l'expression. Oui MIN Équivalent à Oracle
SUM Renvoie la somme des valeurs dans la colonne ou l'expression. Oui SUM Équivalent à Oracle
LISTAGG Affiche les données de chaque groupe sur une seule ligne spécifiée dans la clause ORDER BY en concaténant les valeurs de la colonne de mesure :

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

-- Single line results
= Accounting, Administration, Benefits, Construction 		Non STRING_AGG Utilisez la fonction Cloud SQL pour PostgreSQL STRING_AGG pour renvoyer des résultats semblables à ceux d'Oracle, mais attendez-vous des différences de syntaxe dans certains cas :

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

-- Single line results
= Accounting, Administration, Benefits, Construction
Oracle 12c Fetch
Fonction Oracle Spécification ou mise en œuvre de la fonction Oracle Équivalent Cloud SQL pour PostgreSQL Fonction correspondante dans Cloud SQL pour PostgreSQL Mise en œuvre ou spécification de fonction Cloud SQL pour PostgreSQL
FETCH Récupère les lignes de données de l'ensemble de résultats d'une requête sur plusieurs lignes :

SELECT * FROM
EMPLOYEES
FETCH FIRST 10 ROWS ONLY; 		Non LIMIT Utilisez la clause Cloud SQL pour PostgreSQL LIMIT pour ne récupérer qu'un ensemble spécifique d'enregistrements :

SELECT * FROM EMPLOYEES LIMIT 10;

Filtrage de base, opérateurs et sous-requêtes

Lors de la conversion, le filtrage de base, les fonctions d'opérateur et les sous-requêtes sont relativement simples à effectuer et ne nécessitent presque aucun effort.

Notes de conversion

Vérifiez et gérez les formats de date car les différents formats d'Oracle et Cloud SQL pour PostgreSQL renvoient des résultats par défaut différents :

  • La fonction SYSDATE Oracle renvoie par défaut 01-AUG-19.
  • La fonction CURRENT_DATE par défaut de PostgreSQL renvoie 2019-08-01 (sans heure de la journée, même avec mise en forme). Pour récupérer la date et l'heure actuelles, utilisez la fonction CURRENT_TIMESTAMP qui renvoie par défaut la valeur 2019-08-01 00:00:00.000000+00.
  • Les formats de date et d'heure peuvent être définis à l'aide des fonctions Cloud SQL pour PostgreSQL TO_CHAR.
Fonction ou sous-requête Oracle Équivalent Cloud SQL pour PostgreSQL Fonction ou sous-requête correspondante dans Cloud SQL pour PostgreSQL Spécification ou mise en œuvre de la fonction Cloud SQL pour PostgreSQL
EXISTS/ NOT EXISTS Oui EXISTS/ NOT EXISTS SELECT * FROM DEPARTMENTS D
WHERE EXISTS (SELECT 1
FROM EMPLOYEES E
WHERE
E.DEPARTMENT_ID =
D.DEPARTMENT_ID);
IN/NOT IN Oui 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 Oui LIKE/NOT LIKE SELECT * FROM EMPLOYEES
WHERE FIRST_NAME LIKE '_e_n%';
BETWEEN/ NOT BETWEEN Oui BETWEEN/
NOT BETWEEN 		SELECT * FROM EMPLOYEES
WHERE EXTRACT(YEAR FROM HIRE_DATE)
NOT BETWEEN 2001 and 2004;
AND/OR Oui AND/OR SELECT * FROM EMPLOYEES
WHERE DEPARTMENT_ID IN(100, 101)
AND (SALARY >= 1000 OR HIRE_DATE <= '2006-02-05');
SubQuery Yes SubQuery Cloud SQL pour PostgreSQL est compatible avec les sous-requêtes au niveau SELECT pour les instructions JOIN et pour le filtrage dans les clauses 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);
Opérateurs Oui Opérateurs Cloud SQL pour PostgreSQL est compatible avec tous les opérateurs de base :

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

Fonctions d'analyse (ou fonctions de fenêtre et de classement)

Les fonctions analytiques d'Oracle étendent les fonctionnalités des opérations SQL standard en permettant de calculer des valeurs agrégées sur un groupe de lignes (par exemple RANK(), ROW_NUMBER(), FIRST_VALUE()). Ces fonctions sont appliquées aux enregistrements partitionnées de manière logique dans le cadre d'une seule expression de requête. Elles sont généralement utilisées pour l'entreposage de données, conjointement aux rapports d'analyse et à l'analytique en général.

Notes de conversion

Cloud SQL pour PostgreSQL intègre de nombreuses fonctions analytiques, appelées fonctions d'agrégation et fonctions de fenêtrage. Si votre application utilise une fonction moins courante qui n'est pas acceptée dans Postgres, vous devez rechercher une extension compatible ou déplacer la logique vers la couche d'application.

Le tableau suivant répertorie les fonctions analytiques les plus courantes d'Oracle.

Famille de fonctions Fonctions associées Compatible avec Cloud SQL pour PostgreSQL
Analyse et classement RANK
AVERAGE_RANK
DENSE_RANK
ROW_NUMBER
PERCENT_RANK
CUME_DIST
NTILE
FIRST_VALUE
LAST_VALUE
OVER (PARTITION BY...) 		Oui (sauf AVERAGE_RANK)
Hiérarchique CONNECT BY
HIER_ANCESTOR
HIER_CHILD_COUNT
HIER_DEPTH
HIER_LEVEL
HIER_ORDER
HIER_PARENT
HIER_TOP 		Non
Lag LAG
LAG_VARIANCE LAG_VARIANCE_PERCENT LEAD
LEAD_VARIANCE LEAD_VARIANCE_PERCENT 		Oui (LAG et LEAD uniquement)

Expression de table commune (CTE)

Les CTE permettent de mettre en œuvre la logique du code séquentiel afin de réutiliser du code SQL pouvant être trop complexe ou peu efficace pour une utilisation polyvalente. Les CTE peuvent être nommées, puis utilisées plusieurs fois dans différentes parties d'une instruction SQL à l'aide de la clause WITH. Les CTE sont compatibles avec Oracle et Cloud SQL pour PostgreSQL.

Exemples
Oracle et Cloud SQL pour 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;

Instruction MERGE

L'instruction MERGE (ou UPSERT) permet de spécifier des instructions SQL uniques permettant d'effectuer des opérations LMD de manière conditionnelle dans une opération MERGE, au lieu d'une seule opération LMD exécutée séparément. Elle sélectionne les enregistrements de la table source, puis, en spécifiant une structure logique, effectue automatiquement plusieurs opérations LMD sur la table cible. Cette fonctionnalité vous permet d'éviter l'utilisation de plusieurs insertions, mises à jour ou suppressions. Notez que MERGE est une instruction déterministe, ce qui signifie qu'une fois qu'une ligne a été traitée par l'instruction MERGE, elle ne peut plus être traitée à l'aide de la même instruction MERGE.

Notes de conversion

Contrairement à Oracle, Cloud SQL pour PostgreSQL n'est pas compatible avec la fonctionnalité MERGE. Pour simuler partiellement la fonctionnalité MERGE, Cloud SQL pour PostgreSQL fournit les instructions INSERT ... ON CONFLICT DO UPDATE :

  • INSERT… ON CONFLICT DO UPDATE : Si une ligne insérée entraîne une erreur de violation unique ou une erreur de violation de contrainte d'exclusion, l'action alternative spécifiée dans la clause ON CONFLICT DO UPDATE est appliquée, par exemple :
INSERT INTO tbl (a,b,c) VALUES (1,2,3)
  ON CONFLICT (a) DO UPDATE SET b = 2, c = 3;

Une autre solution consiste à convertir la fonctionnalité MERGE en procédure stockée pour gérer les opérations LMD à l'aide des commandes INSERT, UPDATE et DELETE avec gestion des exceptions et des doublons.

Suggestions pour les instructions SQL

Oracle fournit une vaste collection d'indicateurs de requête SQL permettant aux utilisateurs d'influencer le comportement de l'optimiseur dans le but de produire des plans d'exécution des requêtes plus efficaces. Cloud SQL pour PostgreSQL n'offre pas de mécanisme comparable basé sur un niveau d'instruction SQL pour influencer l'optimiseur.

Pour influencer les plans de requête choisis par le planificateur de requêtes, Cloud SQL pour PostgreSQL fournit un ensemble de paramètres de configuration qui peuvent être appliqués au niveau de la session. Les effets de ces paramètres de configuration incluent l'activation ou la désactivation d'une certaine méthode d'accès ainsi que l'ajustement des constantes de coût du planificateur. Par exemple, l'instruction suivante désactive l'utilisation par le planificateur de requêtes des types de plans d'analyse séquentiels tels que les analyses complètes de table :

SET ENABLE_SEQSCAN=FALSE;

Pour ajuster l'estimation du coût de récupération d'une page de disque aléatoire (4.0 par défaut), utilisez l'instruction suivante :

SET RANDOM_PAGE_COST=2.0;

Lorsque vous réduisez cette valeur, Cloud SQL pour PostgreSQL favorise les analyses d'index. Le fait d'augmenter cette valeur produit l'effet inverse.

Notes de conversion

Du fait des différences fondamentales entre les optimiseurs Oracle et Cloud SQL pour PostgreSQL et dans la mesure où Cloud SQL pour PostgreSQL n'est pas compatible avec les suggestions de requête SQL de type Oracle, nous vous recommandons de supprimer toutes les suggestions de requête lors de votre migration vers Cloud SQL pour PostgreSQL. Effectuez ensuite des tests de performances rigoureux avec les outils Cloud SQL pour PostgreSQL, examinez vos requêtes à l'aide des plans d'exécution et ajustez les paramètres de l'instance ou de la session en fonction du cas d'utilisation.

Plans d'exécution

L'objectif principal des plans d'exécution est de fournir un aperçu des choix effectués par l'optimiseur de requête pour accéder aux données de la base de données. L'optimiseur de requête génère des plans d'exécution pour les instructions SELECT, INSERT, UPDATE et DELETE pour les utilisateurs de base de données, ce qui offre aux administrateurs une visibilité accrue sur des requêtes et des opérations LMD spécifiques. Ils sont particulièrement utiles lorsque vous devez ajuster les performances des requêtes, par exemple pour déterminer les performances d'index ou pour déterminer si des index manquants doivent être créés.

Les plans d'exécution peuvent être affectés par les volumes de données, les statistiques de données et les paramètres d'instance (paramètres globaux ou de session).

Remarques sur les conversions

Les plans d'exécution ne sont pas des objets de base de données à migrer. Au lieu de cela, ils constituent un outil permettant d'analyser les différences de performances entre Oracle et Cloud SQL pour PostgreSQL en exécutant une même instruction sur des ensembles de données identiques.

Cloud SQL pour PostgreSQL ne prend pas en charge les mêmes syntaxes, fonctionnalités ou sorties de plan d'exécution qu'Oracle.

Voici un exemple de plan d'exécution :

Plan d'exécution Oracle Plan d'exécution de Cloud SQL pour 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)

Procédures stockées, fonctions et déclencheurs

PL/SQL est le langage procédural étendu d'Oracle permettant de créer, de stocker et d'appliquer des solutions basées sur du code directement dans la base de données. En général, les procédures et fonctions de base de données sont des éléments de code composés d'ANSI SQL et de langage procédural étendu SQL (par exemple, PL/SQL pour Oracle ou le langage procédural MySQL pour MySQL). PL/pgSQL désigne le langage procédural étendu de PostgreSQL.

Le but de ces procédures et fonctions stockées est de fournir des solutions pour des exigences plus adaptées à l'exécution depuis la base de données et non depuis l'application (par exemple, performances, compatibilité et sécurité). Bien que les procédures stockées et les fonctions utilisent toutes deux PL/SQL, les procédures stockées sont principalement utilisées pour effectuer des opérations LDD/LMD, alors que les fonctions sont principalement utilisées pour effectuer des calculs afin de renvoyer des résultats spécifiques.

PL/SQL vers PL/pgSQL

Du point de vue de la migration d'Oracle PL/SQL vers Cloud SQL pour PostgreSQL, PL/pgSQL est semblable au PL/SQL d'Oracle en termes de structure et de syntaxe. Cependant, quelques différences majeures nécessitent une migration de code. Par exemple, les types de données différents entre Oracle et Cloud SQL pour PostgreSQL nécessitent souvent une traduction pour s'assurer que le code migré utilise les noms de type de données correspondants compatibles avec Cloud SQL pour PostgreSQL. Pour en savoir plus sur les différences entre les deux langages, consultez la section Portage depuis Oracle PL/SQL.

Droits et sécurité des objets de code

Dans Oracle, pour créer une procédure ou une fonction stockée, l'utilisateur doit disposer du droit système CREATE PROCEDURE (pour créer des procédures ou des fonctions pour des utilisateurs différents, les utilisateurs de la base de données doivent disposer du droit CREATE ANY PROCEDURE). Pour exécuter une procédure ou une fonction stockée, les utilisateurs de la base de données doivent disposer du droit EXECUTE.

Dans PostgreSQL, pour créer une procédure de code ou une fonction, l'utilisateur doit disposer du privilège USAGE. Pour exécuter une procédure ou une fonction, l'utilisateur doit disposer du privilège EXECUTE sur la procédure ou la fonction.

Par défaut, une procédure ou une fonction PL/pgSQL est définie comme SECURITY INVOKER, ce qui signifie que la procédure ou la fonction doit être exécutée avec les privilèges de l'utilisateur qui l'appelle. Vous pouvez également spécifier SECURITY DEFINER pour que la fonction soit exécutée avec les privilèges de l'utilisateur qui en est le propriétaire.

Syntaxe de procédure ou de fonction stockée dans Cloud SQL pour PostgreSQL

L'exemple suivant montre la syntaxe de procédure ou de fonction stockée dans Cloud SQL pour 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'
  } ...

Déclencheurs

Un déclencheur est une procédure stockée qui se déclenche lorsqu'un événement spécifique se produit. Dans Oracle, l'événement déclencheur est associé à une table, à une vue, à un schéma ou à la base de données. Les types d'événements déclencheurs sont les suivants :

  • Instructions LMD (langage de manipulation de données) (par exemple, INSERT, UPDATE, DELETE)
  • Instructions LDD (langage de définition de données) (par exemple, CREATE, ALTER, DROP)
  • Événements de base de données (par exemple, LOGON, STARTUP, SHUTDOWN)

Les déclencheurs d'Oracle peuvent être des types suivants :

  • Déclencheur simple : se déclenche exactement une seule fois, avant ou après l'événement déclencheur spécifié
  • Déclencheur composé : se déclenche en réponse plusieurs événements
  • Déclencheur INSTEAD OF : type spécial de déclencheur LMD qui fournit un mécanisme de mise à jour transparent pour les vues complexes et non modifiables
  • Déclencheur système : se déclenche en réponse à des événements de base de données spécifiques

Dans Cloud SQL pour PostgreSQL, un déclencheur s'active avant ou après une opération LMD sur une table, une vue ou une table étrangère spécifique. Le déclencheur INSTEAD OF est compatible et constitue un mécanisme de mise à jour des vues. Un déclencheur qui s'active en réponse à des opérations LDD est appelé un déclencheur d'événement. Cloud SQL pour PostgreSQL n'est pas compatible avec les déclencheurs système d'Oracle basés sur des événements de base de données.

Contrairement aux déclencheurs Oracle, les déclencheurs Cloud SQL pour PostgreSQL ne sont pas compatibles avec l'utilisation d'un bloc PL/pgSQL anonyme comme corps de déclencheur. Une fonction nommée qui utilise zéro arguments ou plus et renvoie un déclencheur de type doit être fournie dans la déclaration de déclencheur. Cette fonction est exécutée lorsque le déclencheur est activé.

Syntaxe du déclencheur Cloud SQL pour PostgreSQL et du déclencheur d'événements

L'exemple suivant illustre la syntaxe du déclencheur Cloud SQL pour PostgreSQL et du déclencheur d'événement :

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 peut être une valeur parmi : 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 peut être une valeur parmi : ddl_command_start, ddl_command_end, table_rewrite, sql_drop

filter_value ne peut être qu'une valeur : TAG

filter_value peut être l'un des tags de commande compatibles.

Étape suivante