Creazione e gestione di sottoprogrammi standalone

È possibile creare e gestire sottoprogrammi PL/SQL standalone.

Nota: per eseguire le esercitazioni riportate nel presente documento è necessario aver definito la connessione a Oracle Database come utente HR da SQL Developer.

Informazioni sulla struttura dei sottoprogrammi

Un sottoprogramma segue la struttura di blocchi PL/SQL, ossia è costituito da:

• Parte dichiarativa (facoltativa)

  La parte dichiarativa contiene le dichiarazioni di tipi, costanti, variabili, eccezioni, cursori dichiarati e sottoprogrammi nidificati. Questi elementi sono locali rispetto al sottoprogramma e cessano di esistere quando viene completata l'esecuzione dello stesso.

• Parte eseguibile (obbligatoria)

  La parte eseguibile contiene istruzioni che assegnano valori, controllano l'esecuzione e manipolano i dati.

• Parte di gestione delle eccezioni (facoltativa)

  La parte di trattamento delle eccezioni contiene codice che gestisce le eccezioni (gli errori in fase di lavorazione).

I commenti possono comparire in qualsiasi punto del codice PL/SQL. Il compilatore PL/SQL li ignora. L'aggiunta di commenti al programma aumenta la leggibilità. Un commento di una linea singola inizia con un doppio trattino (--) e si estende fino alla fine della linea. Un commento multi linea inizia con una barra e l'asterisco (/*) e termina con un asterisco e una barra (*/).

La struttura di una procedura è la seguente:

PROCEDURE name [ ( parameter_list ) ]
{ IS | AS }
  [ declarative_part ]
BEGIN  -- executable part begins
  statement; [ statement; ]...
[ EXCEPTION -- executable part ends, exception-handling part begins]
  exception_handler; [ exception_handler; ]... ]
END; /* exception-handling part ends if it exists;
        otherwise, executable part ends */

La struttura di una funzione è simile a quella di una procedura, tranne che include una clausola RETURN e almeno un'istruzione RETURN (e alcune clausole facoltative che non rientrano nell'ambito di questo documento):

FUNCTION name [ ( parameter_list ) ] RETURN data_type [ clauses ]
{ IS | AS }
  [ declarative_part ]
BEGIN  -- executable part begins
  -- at least one statement must be a RETURN statement
  statement; [ statement; ]...
[ EXCEPTION -- executable part ends, exception-handling part begins]
  exception_handler; [ exception_handler; ]... ]
END; /* exception-handling part ends if it exists;
        otherwise, executable part ends */

Il codice che inizia con PROCEDURE o FUNCTION e termina con IS o AS è la firma di sottoprogramma. Le parti dichiarative, eseguibili e di gestione di eccezioni costituiscono il corpo dei sottoprogrammi. La sintassi del gestore delle eccezioni è descritta in "Informazioni sui gestori delle eccezioni e delle eccezioni".

Vedere anche: Oracle Database PL/SQL Language Reference per ulteriori informazioni sulle parti del sottoprogramma

Esercitazione: Creazione di una procedura standalone

Questa esercitazione mostra come utilizzare lo strumento Crea procedura per creare una procedura standalone denominata ADD_EVALUATION che aggiunge una riga alla tabella EVALUATIONS.

La tabella EVALUATIONS è stata creata nell'Esempio 4-1.

Per creare una procedura standalone, utilizzare lo strumento di SQL Developer Crea procedura o l'istruzione DDL CREATE PROCEDURE.

Passi per creare una procedura standalone utilizzando lo strumento Crea procedura:

1. Nel frame Connessioni espandere hr_conn.

2. Nella lista dei tipi di oggetto schema, fare clic con il pulsante destro del mouse su Procedure.

3. Nell'elenco di opzioni, fare clic su Nuova procedura.

   Viene visualizzata la finestra Crea procedura.

4. Per Schema accettare il valore predefinito HR.

5. Per Nome, modificare PROCEDURE1 in ADD_EVALUATION.

6. Fare clic sull'icona Aggiungi parametro.

   Compare una riga sotto le intestazioni di colonna. I campi contengono i seguenti valori predefiniti: Nome, PARAM1; Modalità, IN; Nessuna copia, deselezionata; Tipo di dati, VARCHAR2; Valore predefinito, vuoto.

7. Per Nome, modificare PARAM1 in EVALUATION_ID.

8. Per Modalità accettare il valore predefinito IN.

9. Per Tipo di dati, selezionare NUMERO dal menu.

10. Lasciare vuoto il campo Valore predefinito.

11. Aggiungere un secondo parametro ripetendo i passi da 6 a 10 con nome EMPLOYEE_ID e il tipo di dati NUMBER.

12. Aggiungere un terzo parametro ripetendo i passi da 6 a 10 con il nome EVALUATION_DATE e la data del tipo di dati.

13. Aggiungere un quarto parametro ripetendo i passi da 6 a 10 con il Nome JOB_ID e il Tipo di dati VARCHAR2.

14. Aggiungere un quinto parametro ripetendo i passi da 6 a 10 con il nome MANAGER_ID e NUMERO tipo di dati.

15. Aggiungere un sesto parametro ripetendo i passi da 6 a 10 con il nome DEPARTMENT_ID e con il tipo di dati NUMBER.

16. Aggiungere un settimo parametro ripetendo i passi da 6 a 10 con il nome TOTAL_SCORE e con il tipo dati NUMBER.

17. Fare clic su OK.

    CREATE OR REPLACE PROCEDURE ADD_EVALUATION
    (
      EVALUATION_ID IN NUMBER
    , EMPLOYEE_ID IN NUMBER
    , EVALUATION_DATE IN DATE
    , JOB_ID IN VARCHAR2
    , MANAGER_ID IN NUMBER
    , DEPARTMENT_ID IN NUMBER
    , TOTAL_SCORE IN NUMBER
    ) AS
    
    BEGIN
    
      NULL;
    
    END ADD_EVALUATION;
    

    Il titolo del riquadro ADD_EVALUATION è in corsiva, a indicare che la procedura non è stata ancora salvata nel database.

    Poiché la parte di esecuzione della procedura contiene solo l'istruzione NULL, la procedura non fa nulla.

18. Sostituire l'istruzione NULL con questa istruzione:

    INSERT INTO EVALUATIONS (
      evaluation_id,
      employee_id,
      evaluation_date,
      job_id,
      manager_id,
      department_id,
      total_score
    )
    VALUES (
      ADD_EVALUATION.evaluation_id,
      ADD_EVALUATION.employee_id,
      ADD_EVALUATION.evaluation_date,
      ADD_EVALUATION.job_id,
      ADD_EVALUATION.manager_id,
      ADD_EVALUATION.department_id,
      ADD_EVALUATION.total_score
    );
    

    La qualificazione dei nomi di parametro con il nome della procedura garantisce che non vengano confusi con le colonne che hanno lo stesso nome.

19. Dal menu File, selezionare Salva.

Oracle Database compila la procedura e la salva. Il titolo del riquadro ADD_EVALUATION non è più in corsivo. Il messaggio del riquadro Messaggio - Log è Compiled.

Vedere anche:

• Oracle SQL Developer User's Guide per un altro esempio di utilizzo di SQL Developer per creare una procedura standalone

• "Informazioni sulle istruzioni DDL (Data Definition Language)" per informazioni generali valide per l'istruzione CREATE PROCEDURE

• Oracle Database PL/SQL Language Reference per informazioni sull'istruzione CREATE PROCEDURE

Esercitazione: Creazione di una funzione standalone

Questa esercitazione mostra come utilizzare lo strumento Crea funzione per creare una funzione standalone denominata CALCULATE_SCORE con tre parametri e restituisce un valore di tipo NUMBER.

Per creare una funzione standalone, utilizzare lo strumento di SQL Developer Crea funzione o l'istruzione DDL CREATE FUNCTION.

Passi per creare una funzione autonoma utilizzando lo strumento Crea funzione:

1. Nel frame Connessioni espandere hr_conn.

2. Nella lista dei tipi di oggetto schema fare clic con il pulsante destro del mouse su Funzioni.

3. Nell'elenco delle opzioni, fare clic su Nuova funzione.

   Viene visualizzata la finestra Crea funzione.

4. Per Schema accettare il valore predefinito HR.

5. Per Nome, modificare FUNCTION1 in CALCULATE_SCORE.

6. Per Tipo reso, selezionare NUMERO dal menu.

7. Fare clic sull'icona Aggiungi parametro.

   Compare una riga sotto le intestazioni di colonna. I campi contengono i seguenti valori predefiniti: Nome, PARAM1; Modalità, IN; Nessuna copia, deselezionata; Tipo di dati, VARCHAR2; Valore predefinito, vuoto.

8. Per Nome, modificare PARAM1 in cat.

9. Per Modalità accettare il valore predefinito IN.

10. Per il tipo di dati, accettare il valore predefinito VARCHAR2.

11. Lasciare vuoto il campo Valore predefinito.

12. Aggiungere un secondo parametro ripetendo i passi da 7 a 11 con nome score e il tipo di dati NUMBER.

13. Aggiungere un terzo parametro ripetendo i passi da 7 a 11 con il nome weight e con il tipo di dati NUMBER.

14. Fare clic su OK.

    Viene aperto il riquadro CALCULATE_SCORE che mostra l'istruzione CREATE FUNCTION che ha creato la funzione.

    CREATE OR REPLACE FUNCTION CALCULATE_SCORE
    (
      CAT IN VARCHAR2
    , SCORE IN NUMBER
    , WEIGHT IN NUMBER
    ) RETURN NUMBER AS
    
    BEGIN
    
      RETURN NULL;
    
    END CALCULATE_SCORE;
    

    Il titolo del riquadro CALCULATE_SCORE è in corsiva, a indicare che la funzione non è stata ancora salvata nel database.

    Poiché l'unica istruzione nella parte eseguibile della funzione è RETURN NULL, la funzione non fa nulla.

15. Sostituire NULL con score * weight.

16. Dal menu File, selezionare Salva.

    Oracle Database compila la funzione e la salva. Il titolo del riquadro CALCULATE_SCORE non è più in corsivo. Il messaggio del riquadro Messaggio - Log è Compiled.

Vedere anche:

• "Informazioni sulle istruzioni DDL (Data Definition Language)" per informazioni generali valide per l'istruzione CREATE FUNCTION

• Oracle Database PL/SQL Language Reference per informazioni sull'istruzione CREATE FUNCTION

Modifica dei sottoprogrammi standalone

Per modificare un sottoprogramma standalone, utilizzare lo strumento di SQL Developer Edit oppure l'istruzione DDL ALTER PROCEDURE o ALTER FUNCTION.

Passi per modificare un sottoprogramma standalone utilizzando lo strumento Modifica:

1. Nel frame Connessioni espandere hr_conn.

2. Nella lista dei tipi di oggetto schema, espandere Funzioni o Procedure.

   Viene visualizzata una lista di funzioni e procedure.

3. Fare clic sulla funzione o sulla procedura da modificare.

   A destra del pannello Connessioni viene visualizzato un frame. la scheda superiore ha il nome del sottoprogramma da modificare. Nel riquadro Codice viene visualizzato il codice che ha creato il sottoprogramma.

   Il riquadro Codice è in modalità di scrittura. Se si fa clic sull'icona a forma di matita, la modalità passa dalla modalità di scrittura alla modalità di sola lettura o viceversa.

4. Nel riquadro Codice modificare il codice.

   Il titolo del riquadro viene visualizzato in corsivo, a indicare che la modifica non è stata ancora salvata nel database.

5. Dal menu File, selezionare Salva.

   Oracle Database compila il sottoprogramma e lo salva. Il titolo del riquadro non è più in corsivo. Il messaggio del riquadro Messaggio - Log è Compiled.

Vedere anche:

• "Informazioni sulle istruzioni DDL (Data Definition Language)" per informazioni generali relative alle istruzioni ALTER PROCEDURE e ALTER FUNCTION

• Oracle Database PL/SQL Language Reference per informazioni sull'istruzione ALTER PROCEDURE

• Oracle Database PL/SQL Language Reference per informazioni sull'istruzione ALTER FUNCTION

Esercitazione: Test di una funzione autonoma

Questa esercitazione mostra come utilizzare lo strumento di SQL Developer Run per eseguire il test della funzione standalone CALCULATE_SCORE.

Per effettuare il test della funzione CALCULATE_SCORE utilizzando lo strumento Esegui, attenersi alla procedura riportata di seguito.

1. Nel frame Connessioni espandere hr_conn.

2. Nella lista dei tipi di oggetto schema espandere Funzioni.

3. Nell'elenco delle funzioni, fare clic con il pulsante destro del mouse su CALCULATE_SCORE.

4. Nell'elenco di scelte fare clic su Esegui.

   Viene visualizzata la finestra Esegui PL/SQL. Il riquadro Blocco PL/SQL include il seguente codice:

    v_Return := CALCULATE_SCORE (
        CAT => CAT,
        SCORE => SCORE,
        WEIGHT => WEIGHT
      );
   

5. Modificare i valori di SCORE e WEIGHT rispettivamente in 8 e 0.2:

    v_Return := CALCULATE_SCORE (
        CAT => CAT,
        SCORE => 8,
        WEIGHT => 0.2
      );
   

6. Fare clic su OK.

   Nel riquadro Codice viene visualizzata la finestra In esecuzione, che mostra il seguente risultato:

    Connecting to the database hr_conn.
    Process exited.
    Disconnecting from the database hr_conn.
   

   A destra della scheda In esecuzione è la scheda Variabili di output.

7. Fare clic sulla scheda Variabili di output.

   Vengono visualizzati due frame, Variabile e Valore, che contengono rispettivamente i valori <Valore restituito> e 1,6.

Vedere anche: Oracle SQL Developer User's Guide per informazioni sull'uso di SQL Developer per l'esecuzione e il debug di procedure e funzioni

Eliminazione di sottoprogrammi standalone

Per eliminare un sottoprogramma standalone, utilizzare il frame e lo strumento Eliminazione delle connessioni SQL Developer oppure l'istruzione DDL DROP PROCEDURE o DROP FUNCTION.

Attenzione: non eliminare la procedura ADD_EVALUATION o la funzione CALCULATE_SCORE: sono necessari per le esercitazioni successive. Se si desidera provare a eliminare i sottoprogrammi, creare quelli semplici e quindi eliminarli.

Per eliminare un sottoprogramma standalone utilizzando lo strumento Elimina, attenersi alla procedura riportata di seguito.

1. Nel frame Connessioni espandere hr_conn.

2. Nella lista dei tipi di oggetto schema, espandere Funzioni o Procedure.

3. Fare clic con il pulsante destro del mouse sulla funzione o sulla procedura da eliminare nell'elenco di funzioni o procedure.

4. Nell'elenco delle opzioni, fare clic su Elimina.

5. Nella finestra Rilascia fare clic su Applica.

6. Nella finestra Conferma, fare clic su OK.

Vedere anche:

• "Informazioni sulle istruzioni DDL (Data Definition Language)" per informazioni generali relative alle istruzioni DROP PROCEDURE e DROP FUNCTION

• Oracle Database SQL Language Reference per informazioni sull'istruzione DROP PROCEDURE

• Oracle Database SQL Language Reference per informazioni sull'istruzione DROP FUNCTION