Assegnazione SELECT INTO No-op SQL dinamico Return Condizionali: IF Condizionali: CASE Loop Cursori RAISE Controllo della transazione

Istruzioni PL/pgSQL supportate

Le istruzioni PL/pgSQL aumentano i comandi SQL con costrutti procedurali, includendo le espressioni di looping e condizionali, per controllare il flusso logico. È possibile utilizzare la maggior parte dei comandi SQL, compresi il linguaggio DML (Data Manipulation Language) come COPY, UNLOAD e INSERT, e il linguaggio DDL (Data Definition Language) come CREATE TABLE. Per un elenco di comandi SQL esaustivi, consultare Comandi SQL. Inoltre, le seguenti istruzioni PL/pgSQL sono supportate da Amazon Redshift.

Assegnazione

L'istruzione di assegnazione assegna un valore a una variabile. L'espressione deve restituire un valore singolo.


identifier := expression;

L'utilizzo di = non standard per l'assegnazione, invece di := è anche accettabile.

Se il tipo di dati dell'espressione non corrisponde al tipo di dati della variabile o la variabile ha una dimensione o precisione, il valore del risultato viene convertito implicitamente.

Di seguito vengono riportati degli esempi.


customer_number := 20;
tip := subtotal * 0.15;

SELECT INTO

L'istruzione SELECT INTO assegna il risultato di colonne multiple (ma solo una riga) in una variabile di record o in un elenco di variabili scalari.


SELECT INTO target select_expressions FROM ...;

Nella sintassi precedente, target può essere una variabile di record o un elenco separato da virgole di variabili semplici e campi record. L'elenco select_expressions e il resto del comando coincidono come nel SQL regolare.

Se un elenco di variabili viene utilizzato come target, i valori selezionati devono corrispondere esattamente alla struttura del target o si verifica un errore di runtime. Quando una variabile di record è il target, si configura automaticamente al tipo di riga delle colonne dei risultati delle query.

La clausola INTO può apparire quasi ovunque nell'istruzione SELECT. Solitamente appare appena dopo la clausola SELECT o appena prima della clausola FROM. Ovvero, appare appena prima o appena dopo l'elenco select_expressions.

Se la query restituisce zero righe, i valori NULL vengono assegnati al target. Se la query restituisce righe multiple, la prima riga viene assegnata al target e le altre vengono eliminate. A meno che l'istruzione contenga un ORDER BY, la prima riga non è deterministica.

Per determinare se l'assegnazione ha restituito almeno una riga, utilizza la variabile speciale FOUND.


SELECT INTO customer_rec * FROM cust WHERE custname = lname;
IF NOT FOUND THEN
  RAISE EXCEPTION 'employee % not found', lname;
END IF;

Per testare se un risultato di un record è null, è possibile utilizzare il condizionale IS NULL. Non c'è modo di determinare se delle righe aggiuntive sono state eliminate. L'esempio seguente gestisce il caso nel quale nessuna riga è stata restituita.


CREATE OR REPLACE PROCEDURE select_into_null(return_webpage OUT varchar(256))
AS $$
DECLARE
  customer_rec RECORD;
BEGIN
  SELECT INTO customer_rec * FROM users WHERE user_id=3;
  IF customer_rec.webpage IS NULL THEN
    -- user entered no webpage, return "http://"
    return_webpage = 'http://';
  END IF;
END;
$$ LANGUAGE plpgsql;

No-op

L'istruzione no-op (NULL;) è un'istruzione segnaposto che non fa nulla. Un'istruzione no-op può indicare un ramo di una catena IF-THEN-ELSE è vuoto.


NULL;

SQL dinamico

Per generare i comandi dinamici che possono includere diverse tabelle o diversi tipi di dati ogni volta che vengono eseguiti da una procedura archiviata PL/pgSQL, utilizza l'istruzione EXECUTE.


EXECUTE command-string [ INTO target ];

Nell'esempio precedente, command-string è un'espressione che produce una stringa (di tipo testo) che contiene il comando da eseguire. Questo valore command-string viene inviato al motore SQL. Nessuna sostituzione delle variabili PL/pgSQL viene effettuata sulla stringa del comando. I valori delle variabili devono essere inseriti nella stringa comando com'è costruito.

Nota

Non è possibile utilizzare le istruzioni COMMIT e ROLLBACK dall'interno di un SQL dinamico. Per informazioni su come utilizzare le istruzioni COMMIT e ROLLBACK all'interno di una procedura archiviata, consultare Gestione delle transazioni.

Quando si lavora con i comandi dinamici, spesso è necessario necessario gestire l'escape di virgolette singole. Consigliamo di racchiudere il testo fisso in virgolette nel testo della funzione utilizzando il dollar quoting. I valori dinamici da inserire in una query costruita richiedono una gestione speciale perché loro stessi potrebbero contenere virgolette. L'esempio seguente utilizza il dollar quoting per tutta la funzione, quindi le virgolette non devono essere raddoppiate.


EXECUTE 'UPDATE tbl SET '
  || quote_ident(colname)
  || ' = '
  || quote_literal(newvalue)
  || ' WHERE key = '
  || quote_literal(keyvalue);

L'esempio precedente mostra le funzioni quote_ident(text) e quote_literal(text). Questo esempio passa variabili che contengono identificatori di colonna e tabella alla funzione quote_ident. Passa anche variabili che contengono stringhe letterali nel comando costruito alla funzione quote_literal. Entrambe le funzioni eseguono i passaggi appropriati per restituire il testo di input racchiuso tra virgolette doppie o singole rispettivamente, dove viene eseguito l'escape di qualsiasi carattere speciale incorporato.

Il Dollar quoting è utile solo per l'utilizzo di virgolette in testo fisso. Non scrivere l'esempio precedente nel seguente formato.


EXECUTE 'UPDATE tbl SET '
  || quote_ident(colname)
  || ' = $$'
  || newvalue
  || '$$ WHERE key = '
  || quote_literal(keyvalue);

Non esegui questa operazione perché l'esempio si interrompe se i contenuti di newvalue contengono $$. Lo stesso problema si applica a qualsiasi delimitatore di Dollar quoting che scegli. Per utilizzare in maniera sicura le virgolette in testo che non conosci in anticipo, utilizza la funzione quote_literal.

Return

L'istruzione RETURN ritorna all'intermediario da una procedura archiviata.


RETURN;

Di seguito viene riportato un esempio.


CREATE OR REPLACE PROCEDURE return_example(a int)
AS $$  
BEGIN
  FOR b in 1..10 LOOP
    IF b < a THEN
      RAISE INFO 'b = %', b;
    ELSE
      RETURN;
    END IF;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

Condizionali: IF

L'istruzione condizionale IF può avere le seguenti forme nel linguaggio PL/pgSQL utilizzato da Amazon Redshift:

IF ... THEN


IF boolean-expression THEN
  statements
END IF;

Di seguito viene riportato un esempio.


IF v_user_id <> 0 THEN
  UPDATE users SET email = v_email WHERE user_id = v_user_id;
END IF;

IF ... THEN ... ELSE


IF boolean-expression THEN
  statements
ELSE
  statements
END IF;

Di seguito viene riportato un esempio.


IF parentid IS NULL OR parentid = ''
THEN
  return_name = fullname;
  RETURN;
ELSE
  return_name = hp_true_filename(parentid) || '/' || fullname;
  RETURN;
END IF;

IF ... THEN ... ELSIF ... THEN ... ELSE

La parola chiave ELSIF si può anche scrivere come ELSEIF.


IF boolean-expression THEN
  statements
[ ELSIF boolean-expression THEN
  statements
[ ELSIF boolean-expression THEN
  statements
    ...] ]
[ ELSE
  statements ]
END IF;

Di seguito viene riportato un esempio.


IF number = 0 THEN
  result := 'zero';
ELSIF number > 0 THEN
  result := 'positive';
ELSIF number < 0 THEN
  result := 'negative';
ELSE
  -- the only other possibility is that number is null
  result := 'NULL';
END IF;

Condizionali: CASE

L'istruzione condizionale CASE può avere le seguenti forme nel linguaggio PL/pgSQL utilizzato da Amazon Redshift:

CASE semplice
```
CASE search-expression
WHEN expression [, expression [ ... ]] THEN
  statements
[ WHEN expression [, expression [ ... ]] THEN
  statements
  ... ]
[ ELSE
  statements ]
END CASE;
```
Una semplice istruzione CASE fornisce l'esecuzione condizionale basata sull'eguaglianza degli operandi.

Il valore espressione di ricerca viene valutato una volta e viene paragonato successivamente a ogni espressione nelle clausole WHEN. Se si trova una corrispondenza, vengono eseguite gli statement corrispondenti e il controllo passa all'istruzione successiva dopo END CASE. Le espressioni WHEN successive non vengono valutate. Se non viene trovata alcuna corrispondenza, vengono eseguite le istruzioni ELSE. Tuttavia, se ELSE non è presente, viene generata un'eccezione CASE_NOT_FOUND.

Di seguito viene riportato un esempio.
```
CASE x
WHEN 1, 2 THEN
  msg := 'one or two';
ELSE
  msg := 'other value than one or two';
END CASE;                  
```
CASE ricercata
```
CASE
WHEN boolean-expression THEN
  statements
[ WHEN boolean-expression THEN
  statements
  ... ]
[ ELSE
  statements ]
END CASE;
```
La forma ricercata di CASE fornisce un'esecuzione condizionale basata sulla verità delle espressioni booleane.

Ogni espressione booleana della clausola WHEN viene valutata a turno, fino a che viene trovata una che genera true. In seguito le istruzioni corrispondenti vengono eseguite e il controllo passa all'istruzione successiva dopo END CASE. Le espressioni WHEN successive non vengono valutate. Se non viene trovato un risultato true , vengono eseguite le istruzioni ELSE. Tuttavia, se ELSE non è presente, viene generata un'eccezione CASE_NOT_FOUND.

Di seguito viene riportato un esempio.
```
CASE
WHEN x BETWEEN 0 AND 10 THEN
  msg := 'value is between zero and ten';
WHEN x BETWEEN 11 AND 20 THEN
  msg := 'value is between eleven and twenty';
END CASE;                                
```

Loop

Le istruzioni loop possono avere le seguenti forme nel linguaggio PL/pgSQL utilizzato da Amazon Redshift:

Loop semplice
```
[<<label>>]
LOOP
  statements
END LOOP [ label ];
```
Un loop semplice definisce un loop non condizionale che viene ripetuto a tempo indeterminato fino a quando viene terminato da un'istruzione EXIT e RETURN. L'etichetta opzionale può essere utilizzata dalle istruzioni EXIT e CONTINUE nei loop nidificati per specificare a quale loop si riferiscono le instruzioni EXIT e CONTINUE.

Di seguito viene riportato un esempio.
```
CREATE OR REPLACE PROCEDURE simple_loop()
LANGUAGE plpgsql
AS $$
BEGIN
  <<simple_while>>
  LOOP
    RAISE INFO 'I am raised once';  
    EXIT simple_while;
    RAISE INFO 'I am not raised';
  END LOOP;
  RAISE INFO 'I am raised once as well';
END;
$$;                
```
Loop exit
```
EXIT [ label ] [ WHEN expression ];
```
Se l'etichetta non è presente, il loop interno viene terminato e viene eseguita l'istruzione successiva a END LOOP. Se l'etichetta è presente, deve essere l'etichetta del livello corrente o del livello esterno del loop o blocco nidificato. In seguito, il loop o blocco denominato viene terminato e il controllo continua con l'istruzione dopo il loop o blocco corrispondente a END.

Se WHEN è specificata, l'uscita del loop avviene solo se l'espressione è true. Altrimenti, il controllo passa all'istruzione dopo EXIT.

È possibile utilizzare EXIT con tutti i tipi di loop, non è limitato all'utilizzo con loop non condizionali.

Quando viene utilizzato un blocco BEGIN, EXIT passa il controllo alla prossima istruzione dopo la fine del blocco. A questo scopo deve essere utilizzata un'etichetta. Un'istruzione EXIT non etichettata non viene mai considerata per un blocco BEGIN.

Di seguito viene riportato un esempio.
```
CREATE OR REPLACE PROCEDURE simple_loop_when(x int)
LANGUAGE plpgsql
AS $$
DECLARE i INTEGER := 0;
BEGIN
  <<simple_loop_when>>
  LOOP
    RAISE INFO 'i %', i;
    i := i + 1;
    EXIT simple_loop_when WHEN (i >= x);
  END LOOP;
END;
$$;                     
```
Loop Continue
```
CONTINUE [ label ] [ WHEN expression ];
```
Se label non viene fornita, l'esecuzione passa alla successiva iterazione del loop più interno. Ovvero, tutte le istruzioni rimanenti nel testo del loop vengono ignorate. Il controllo in seguito ritorna all'espressione di controllo del loop (se presenti) per determinare se un'altra iterazione di un loop è necessaria. Se label è presente, specifica l'etichetta del loop la cui esecuzione viene continuata.

Se l'istruzione WHEN è specificata, l'iterazione successiva del loop inizia solo se expression è true. Altrimenti, il controllo passa all'istruzione dopo CONTINUE.

È possibile utilizzare CONTINUE con tutti i tipi di loop, non è limitato all'utilizzo con loop non condizionali.
```
CONTINUE mylabel;
```

Loop WHILE


[<<label>>]
WHILE expression LOOP
  statements
END LOOP [ label ];

L'istruzione WHILE ripete una sequenza di istruzioni purché l'boolean-expression corrisponda a true. L'espressione viene controllata appena prima di ogni inserimento nel body del loop.

Di seguito viene riportato un esempio.


WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
  -- some computations here
END LOOP;

WHILE NOT done LOOP
  -- some computations here
END LOOP;

Loop FOR (variante numero intero)
```
[<<label>>]
FOR name IN [ REVERSE ] expression .. expression LOOP
  statements
END LOOP [ label ];
```
Il loop FOR (variante numero intero) crea un loop che esegue l'iterazione in una gamma di valori interi. Il nome variabile viene definito automaticamente come tipo intero ed esiste solo nel loop. Qualsiasi definizione esistente del nome della variabile viene ignorato nel loop. Le due espressioni che forniscono il limite inferiore e superiore della gamma vengono valutate una volta quando entrano nel loop. Se si specifica REVERSE, viene sottratto il valore di incremento, piuttosto che aggiunto, dopo ogni iterazione.

Se il limite inferiore è maggiore rispetto al limite superiore (o inferiore, nel caso REVERSE), il body del loop non viene eseguito. Non viene generato alcun errore.

Se un'etichetta viene collegata al loop FOR, puoi fare riferimento alla variabile del loop intero con un nome qualificato, utilizzando quell'etichetta.

Di seguito viene riportato un esempio.
```
FOR i IN 1..10 LOOP
  -- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop
END LOOP;

FOR i IN REVERSE 10..1 LOOP
  -- i will take on the values 10,9,8,7,6,5,4,3,2,1 within the loop
END LOOP;                
```
Loop FOR (variante set di risultati)
```
[<<label>>]
FOR target IN query LOOP
  statements
END LOOP [ label ];
```
Il target è una variabile di record o un elenco separato da virgola di variabili scalari. In seguito, al target viene assegnata ciascuna riga risultante dalla query e il body del loop viene eseguito per ogni riga.

Il loop FOR (variante set di risultati) permette a una procedura archiviata di effettuare l'iterazione attraverso i risultati di una query e manipolare i dati di conseguenza.

Di seguito viene riportato un esempio.
```
CREATE PROCEDURE cs_refresh_reports() AS $$
DECLARE
  reports RECORD;
BEGIN
  FOR reports IN SELECT * FROM cs_reports ORDER BY sort_key LOOP
    -- Now "reports" has one record from cs_reports
    EXECUTE 'INSERT INTO ' || quote_ident(reports.report_name) || ' ' || reports.report_query;
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;                   
```

Loop FOR con SQL dinamico


[<<label>>]
FOR record_or_row IN EXECUTE text_expression LOOP 
  statements
END LOOP;

Un loop FOR con SQL dinamico permette a una procedura archiviata di effettuare l'iterazione attraverso i risultati di una query dinamica e manipolare i dati di conseguenza.

Di seguito viene riportato un esempio.


CREATE OR REPLACE PROCEDURE for_loop_dynamic_sql(x int)
LANGUAGE plpgsql
AS $$
DECLARE
  rec RECORD;
  query text;
BEGIN
  query := 'SELECT * FROM tbl_dynamic_sql LIMIT ' || x;
  FOR rec IN EXECUTE query
  LOOP
    RAISE INFO 'a %', rec.a;
  END LOOP;
END;
$$;

Cursori

Invece di eseguire una query intera in una volta, puoi impostare un cursore. Un cursore incapsula una query e legge il risultato della query un po' di righe alla volta. Un motivo è di evitare il superamento della memoria quando il risultato contiene un ampio numero di righe. Un altro motivo è restituire un riferimento a un cursore che una procedura archiviata ha creato, che permette all'intermediario di leggere le righe. Questo approccio fornisce un modo efficace di restituire set di righe di grandi dimensioni dalle procedure archiviate.

Per utilizzare i cursori in una procedura archiviata NONATOMIC, posizionare il loop del cursore tra START TRANSACTION...COMMIT.

Per impostare un cursore, è necessario prima dichiarare un variabile di cursore. Tutto l'accesso ai cursori in PL/pgSQL passa tramite le variabili di cursore, che sono sempre del tipo di dati speciali refcursor. Un tipo di dati refcursor semplicemente contiene un riferimento a un cursore.

Puoi creare una variabile di cursore dichiarandola come tipo di variabile refcursor. Oppure puoi utilizzare la seguente sintassi di dichiarazione del cursore.


name CURSOR [ ( arguments ) ] FOR query ;

Nell'esempio precedente, arguments (se specificato) è un elenco separato da virgole delle coppie name datatype dove ognuna definisce i nome da sostituite con i valori di parametro nella query. I valori effettivi che sostituiscono questi nomi vengono specificati più avanti, quando il cursore è aperto.

Di seguito vengono riportati degli esempi.


DECLARE
  curs1 refcursor;
  curs2 CURSOR FOR SELECT * FROM tenk1;
  curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;

Tutte e tre queste variabili hanno il tipo di dati refcursor, ma la prima può essere utilizzata con qualsiasi query. Al contrario, il secondo ha una query pienamente specificata già associata e l'ultima ha una query parametrizzata associata. Il valore key è sostituito da un valore di parametro intero quando il cursore è aperto. La variabile curs1 viene definita non vincolata perché non è vincolata ad alcuna query particolare.

Prima di poter utilizzare un cursore per recuperare righe, deve essere aperto. PL/pgSQL ha tre forma di istruzione OPEN, delle quali due utilizzano variabili di cursore non vincolate e la terza utilizza una variabile di cursore vincolata:

Open for select: la variabile del cursore viene aperta e riceve la query specificata da eseguire. Il cursore non può essere già aperto. Inoltre, deve essere stata dichiarata come cursore non vincolato (ovvero come variabile semplice refcursor). La query SELECT viene trattata allo stesso modo delle altre istruzioni SELECT in PL/pgSQL.
```
OPEN cursor_name FOR SELECT ...;                     
```
Di seguito viene riportato un esempio.
```
OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;    
```
Open for execute: la variabile del cursore viene aperta e riceve la query specificata da eseguire. Il cursore non può essere già aperto. Inoltre, deve essere stata dichiarata come cursore non vincolato (ovvero come variabile semplice refcursor). La query è specificata come una espressione di stringa allo stesso modo del comando EXECUTE. Questo approccio fornisce flessibilità in modo che la query possa variare da un'esecuzione all'altra.
```
OPEN cursor_name FOR EXECUTE query_string;
```
Di seguito viene riportato un esempio.
```
OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);
```
Open a bound cursor: questa forma di OPEN viene utilizzata per aprire una variabile di cursore la quale query vi era associata al momento della dichiarazione. Il cursore non può essere già aperto. Un elenco di espressioni di valore dell'argomento deve apparire se e solo se il cursore può accettare argomenti. Questi valori vengono sostituiti nella query.
```
OPEN bound_cursor_name [ ( argument_values ) ];
```
Di seguito viene riportato un esempio.
```
OPEN curs2;
OPEN curs3(42);
```

Dopo l'apertura di un cursore, puoi lavorarci utilizzando le istruzioni descritte di seguito. Queste istruzioni non devono aver luogo nella stessa procedura archiviata che ha aperto il cursore. Puoi restituire un valore refcursor da una procedura archiviata e lasciare che l'intermediario lavori sul cursore. Tutti i portali sono chiusi implicitamente alla fine della transazione. In seguito, puoi utilizzare un valore refcursor per far riferimento a un cursore aperto solo fino alla fine della transazione.

FETCH recupera la riga successiva dal cursore in un target. Questo target può essere una variabile di riga, una variabile di record o un elenco separato da virgole di variabili semplici, come in SELECT INTO. Come con SELECT INTO; puoi controllare la variabile speciale FOUND per vedere se è stata ottenuta una riga.
```
FETCH cursor INTO target;
```
Di seguito viene riportato un esempio.
```
FETCH curs1 INTO rowvar;
```
CLOSE chiude il portale sotto un cursore aperto. È possibile utilizzare questa istruzione per rilasciare le risorse prima della fine della transazione. Puoi anche utilizzare questa istruzione per permettere alla variabile del cursore di essere nuovamente aperta.
```
CLOSE cursor;
```
Di seguito viene riportato un esempio.
```
CLOSE curs1;
```

RAISE

Utilizza l'istruzione RAISE level per segnalare messaggi e generare errori.


RAISE level 'format' [, variable [, ...]];

I livelli possibili sono NOTICE, INFO, LOG, WARNING e EXCEPTION. EXCEPTION genera un errore, che normalmente annulla la transazione corrente. Gli altri livelli generano solo messaggi di diversi livelli di priorità.

Nella stringa formato, % viene sostituito dalla rappresentazione di stringa dell'argomento successivo opzionale. Scrivi %% per emetter un % letterale. Al momento, gli argomenti opzionali devono essere semplici variabili, non espressioni, e il formato deve essere un valore letterale di stringa semplice.

Nell'esempio seguente, il valore di v_job_id sostituisce % nella stringa.


RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;

Utilizza l'istruzione RAISE per generare nuovamente l'eccezione rilevata da un blocco di gestione delle eccezioni. Questa istruzione è valida solo nei blocchi di gestione delle eccezioni delle procedure archiviate in modalità NONATOMIC.


RAISE;

Controllo della transazione

Utilizzare le istruzioni di controllo delle transazioni nel linguaggio PL/pgSQL utilizzato da Amazon Redshift. Per informazioni sull'utilizzo delle dichiarazioni COMMIT, ROLLBACK e TRUNCATE, all'interno di una procedura archiviata, consultare Gestione delle transazioni.

Nelle procedure archiviate in modalità NONATOMIC, utilizza START TRANSACTION per avviare un blocco di transazioni.


START TRANSACTION;

Nota

Di seguito sono indicate le differenze tra l'istruzione PL/pgSQL START TRANSACTION e il comando SQL START TRANSACTION:

All'interno delle procedure archiviate, START TRANSACTION non è sinonimo di BEGIN.
L'istruzione PL/pgSQL non supporta il livello di isolamento opzionale e le parole chiave di autorizzazione di accesso.

Avvertimento JavaScript è disabilitato o non è disponibile nel tuo browser.

Per usare la documentazione AWS, JavaScript deve essere abilitato. Consulta le pagine della guida del browser per le istruzioni.

Convenzioni dei documenti

Struttura di PL/pgSQL

Creazione di viste materializzate