In questo tutorial ti spiegherò come implementare il salvataggio dei dati usando una procedura PL/SQL.

Questo metodo è particolarmente utile quando devi implementare una Interactive Grid o una Form che non hanno una sorgente dati una tabella del database ma piuttosto una query SQL oppure una vista.

In queste situazioni, come vedremo tra poco, non è possibile infatti utilizzare il metodo Automatic Row Processing (DML) per salvare i dati nel database.

Prima di iniziare

Il PL/SQL è (Procedural Language/Structured Query Language) è un linguaggio di programmazione proprietario per database di Oracle, procedurale, server-based, estensione dell’SQL.

Se non sai usarlo puoi dare un’occhiata a questa guida veloce per principianti alla programmazione in PL/SQL: troverai tutte le nozioni fondamentali per iniziare a sviluppare procedure in PL/SQL.

Descrizione dell’applicazione

Supponiamo di dover implementare un report basato su Interactive Grid che mostra in un’unica vista i dipendenti e il dipartimento in cui lavorano.

Il database della nostra applicazione consiste di 2 tabelle DIPENDENTE e DIPARTIMENTO.

 CREATE SEQUENCE   "DIPARTIMENTO_SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL
/
CREATE SEQUENCE   "DIPARTIMENTO_SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL
/
/* TABELLA DIPENDENTE*/
CREATE TABLE  "DIPENDENTE" 
   (	"NOME" VARCHAR2(250), 
	"COGNOME" VARCHAR2(250), 
	"RUOLO" VARCHAR2(250), 
	"DIPARTIMENTO" NUMBER, 
	"ID" NUMBER, 
	 CONSTRAINT "DIPENDENTE_PK" PRIMARY KEY ("ID")
  USING INDEX  ENABLE
   )
/
CREATE OR REPLACE EDITIONABLE TRIGGER  "BI_DIPENDENTE" 
  before insert on "DIPENDENTE"               
  for each row  
begin   
  if :NEW."ID" is null then 
    select "DIPENDENTE_SEQ".nextval into :NEW."ID" from sys.dual; 
  end if; 
end; 
/
ALTER TRIGGER  "BI_DIPENDENTE" ENABLE
/
/* TABELLA DIPARTIMENTO*/
CREATE TABLE  "DIPARTIMENTO" 
   (	"ID" NUMBER, 
	"NOME" VARCHAR2(250), 
	 CONSTRAINT "DIPARTIMENTO_PK" PRIMARY KEY ("ID")
  USING INDEX  ENABLE, 
	 CONSTRAINT "DIPARTIMENTO_UK1" UNIQUE ("NOME")
  USING INDEX  ENABLE
   )
/
CREATE OR REPLACE EDITIONABLE TRIGGER  "BI_DIPARTIMENTO" 
  before insert on "DIPARTIMENTO"               
  for each row  
begin   
  if :NEW."ID" is null then 
    select "DIPARTIMENTO_SEQ".nextval into :NEW."ID" from sys.dual; 
  end if; 
end; 
/
ALTER TRIGGER  "BI_DIPARTIMENTO" ENABLE
/
CREATE OR REPLACE FORCE EDITIONABLE VIEW  "DIPENDENTI_V" ("DIPENDENTE_ID", "NOME", "COGNOME", "RUOLO", "DIPARTIMENTO") AS 
  SELECT DEP.ID DIPENDENTE_ID, DEP.NOME, DEP.COGNOME, DEP.RUOLO, DIP.ID DIPARTIMENTO
  FROM DIPENDENTE DEP, DIPARTIMENTO DIP
 WHERE DEP.DIPARTIMENTO = DIP.ID
/

Qualche dato di esempio che puoi usare per usare l’applicazione fin da subito.

INSERT INTO DIPARTIMENTO (NOME) VALUES ('AMMINISTRAZIONE VENDITE');
INSERT INTO DIPARTIMENTO (NOME) VALUES ('REPARTO SPEDIZIONI');
INSERT INTO DIPARTIMENTO (NOME) VALUES ('UFFICIO COMMERCIALE');
INSERT INTO DIPARTIMENTO (NOME) VALUES ('UFFICIO ACQUISTI');

La nostra applicazione deve permettere di aggiornare le informazioni relative al dipendente (nome, cognome, ruolo e dipartimento di lavoro) usando la vista DIPENDENTI_V appositamente creata.

Setup Interactive Grid

Creiamo un report basato su Interactive Grid con sorgente la vista DIPENDENTI_V.

Se non sai come fare, leggi questo tutorial.

• Crea una nuova Interactive Grid basata sulla vista DIPENDENTI_V

• Accedi al tab Attributes dell’IG
• Vai alla sezione Edit ed attiva le seguenti opzioni:
  • Enabled: On
  • Allowed Operations: Add Row, Update Row, Delete Row

• Seleziona il campo Dipartimento

• Imposta il tipo a Select List

• Configura la List of Values

• Seleziona il campo DIPENDENTE_ID

• Imposta il tipo a Hidden

• Prova ad eseguire il report
• Inserisci un record e premi Salva

• L’applicazione restituisce questo errore: ORA-01776: cannot modify more than one base table through a join view

• Nel prossimo paragrafo ti spiegherò come gestire correttamente il salvataggio dei dati tramite codice PL/SQL.

Come processare con una procedura PL/SQL

• Accedi al tab Processes della tua applicazione
• Clicca sul processo che APEX ha create quando hai creato l’Interactive Grid

• Seleziona il tipo Execute Code
• Seleziona come Language PL/SQL

• Nel campo PL/SQL inserisci il seguente script PL/SQL

begin
   if :apex$row_status = 'D' then 
      -- DELETE the record 
      delete from dipendente
       where
         id = :dipendente_id;
   elsif :apex$row_status = 'U' then
   -- UPDATE the record 
      update dipendente
         set
         nome = :nome,
         cognome = :cognome,
         ruolo = :ruolo,
         dipartimento = :dipartimento
       where
         id = :dipendente_id;
   elsif :apex$row_status = 'C' then
      -- INSERT the record 
      insert into dipendente (
         nome,
         cognome,
         ruolo,
         dipartimento
      ) values (
         :nome,
         :cognome,
         :ruolo,
         :dipartimento
      );
   end if;
end;

Ecco le cose importanti da tenere a mente:

• La procedura PL/SQL che abbiamo scritto viene eseguita automaticamente al salvataggio per ogni record dell’Interactive Grid che è stato modificato
• La variabile APEX$ROW_STATUS può assumere 3 valori per ciascun record da processare. In funzione del valore la procedura PL/SQL riesce a capire se deve eseguire una operazione di INSERT (C), UPDATE (U) oppure DELETE (D) sulla tabella DIPENDENTE.
• La chiave primaria della tabella DIPENDENTE, che viene estratta direttamente dalla query della vista, ci da la certezza che andremo a manipolare il record corretto.