Panoramica delle procedure archiviate in Amazon Redshift - Amazon Redshift

Le traduzioni sono generate tramite traduzione automatica. In caso di conflitto tra il contenuto di una traduzione e la versione originale in Inglese, quest'ultima prevarrà.

Panoramica delle procedure archiviate in Amazon Redshift

In questo argomento vengono descritti i dettagli sullo scopo e l'uso delle stored procedure.

Le procedure archiviate vengono comunemente utilizzate per integrare la logica di trasformazione dei dati, quella specifica aziendale nonché la convalida dei dati. Combinando più SQL passaggi in una stored procedure, è possibile ridurre i round trip tra le applicazioni e il database.

Per il controllo granulare degli accessi, è possibile creare procedure archiviate per eseguire funzioni senza consentire all'utente di accedere alle tabelle sottostanti. Ad esempio, solo il proprietario o l'utente con privilegi avanzati può troncare una tabella e a un utente serve l'autorizzazione di scrittura per inserire dati in una tabella. Invece di concedere a un utente le autorizzazioni nella tabelle sottostanti, puoi creare una procedura archiviata che esegua l'operazione. In seguito fornisci l'autorizzazione all'utente per eseguire la procedura archiviata.

Una stored procedure con l'attributo DEFINER security viene eseguita con i privilegi del proprietario della stored procedure. Per impostazione predefinita, una stored procedure è INVOKER protetta, il che significa che utilizza i privilegi dell'utente che la chiama.

Per creare una procedura archiviata, utilizza il comando CREATE PROCEDURE. Per eseguire una procedura, utilizza il comando CALL. Più avanti in questa sezione sono presenti degli esempi.

Nota

Alcuni client possono visualizzare il seguente errore durante la creazione di una procedura archiviata di Amazon Redshift.

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

Questo errore si verifica a causa dell'incapacità del client di analizzare correttamente l'CREATEPROCEDUREistruzione con istruzioni che delimitano punti e virgola e con virgola tra virgola e virgola ($). In questo caso solo una parte dell'istruzione viene inviata al server Amazon Redshift. Spesso si può risolvere questo problema utilizzando l'opzione Run as batch o Execute selected del client.

Ad esempio, quando si utilizza un client Aginity, utilizzare l'opzione Run entire script as batch. Quando usi Workbench/J, ti consigliamo la versione 124SQL. Quando usi SQL Workbench/J versione 125, prendi in considerazione la possibilità di specificare un delimitatore alternativo come soluzione alternativa.

CREATEPROCEDUREcontiene istruzioni SQL delimitate da un punto e virgola (;). Se si definisce un delimitatore alternativo come una barra (/) e lo si colloca alla fine dell'istruzione, l'CREATEPROCEDUREistruzione viene inviata al server Amazon Redshift per l'elaborazione. Di seguito è riportato un esempio.

CREATE OR REPLACE PROCEDURE test()
AS $$
BEGIN
  SELECT 1 a;
END;
$$
LANGUAGE plpgsql
;
/

Per ulteriori informazioni, consulta il delimitatore alternativo nella documentazione di Workbench/J. SQL Oppure usa un client con un supporto migliore per l'analisi CREATE PROCEDURE delle istruzioni, come l'editor di query nella console Amazon Redshift o. TablePlus

Argomenti

L'esempio seguente mostra una procedura senza argomenti di output. Come impostazione predefinita, gli argomenti sono argomenti di input (IN).

CREATE OR REPLACE PROCEDURE test_sp1(f1 int, f2 varchar)
AS $$
BEGIN
  RAISE INFO 'f1 = %, f2 = %', f1, f2;
END;
$$ LANGUAGE plpgsql;

call test_sp1(5, 'abc');
INFO: f1 = 5, f2 = abc
CALL

Nota

Quando si scrivono le stored procedure, si consiglia di attenersi a una best practice per proteggere i valori sensibili:

Non eseguire la codifica fissa delle informazioni sensibili nella logica della procedura archiviata. Ad esempio, non assegnare una password utente in un'CREATEUSERistruzione nel corpo di una procedura memorizzata. Ciò rappresenta un rischio per la sicurezza, poiché i valori con codifica fissa possono essere registrati come metadati dello schema nelle tabelle del catalogo. È invece consigliabile passare i valori sensibili, ad esempio le password, come argomenti alla procedura archiviata, mediante parametri.

Per ulteriori informazioni sulle procedure archiviate, consulta CREATE PROCEDURE e Creazione di procedure archiviate in Amazon Redshift. Per ulteriori informazioni sulle tabelle di catalogo, consulta Tabelle di catalogo di sistema.

L'esempio seguente mostra una procedura con argomenti di output. Gli argomenti sono input (IN), input e output (INOUT) e output (OUT).

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;


call test_sp2(2,'2019');

         f2          | column2
---------------------+---------
 2019+2019+2019+2019 | 2
(1 row)