Best practice in PL/SQL

In questo post faccio un breve resoconto delle best practice per la programmazione in PLSQL illustrate da Steven Feuerstein nel video qui sotto.

Steven è un guru del PLSQL, ha all'attivo diversi libri sull'argomento,  potete saperne di più seguendo il link (https://www.stevenfeuerstein.com/)

Nel video ci sono una serie di suggerimenti affinchè il codice scritto sia leggibile e sia ben implementato, in modo da evitare errori nella creazione e nelle successive rilavorazioni.

Il primo suggerimento è di ordine pratico, consiglia di prendersi cura del proprio fisico perchè in questo modo avremo maggiore concentrazione e saremo tendenti meno alla distrazione. Ci consiglia di idratarci, di mangiare sano e di fare esercizio fisico. Consiglia di alzarsi periodicamente dalla scrivania per fare delle pause e di fare dei sit-ups ogni qualvolta ci alziamo.
Io aggiungerei di praticare la meditazione mindfulness, è scientificamente provato che migliora la capacità di concentrazione e quindi ci permette di raggiungere lo stato di flow, di completa immersione in quello a cui ci stiamo dedicando.

Passando al codice di suggerisce di evitare di fare hard-coding, cioè evita di martellare delle costanti nel codice, permettile  all'utente di inserirle via promt, inseriscile in una tabella o in un file di testo o csv.
Hard-code significa inserire qualcosa nel codice che non può essere cambiato senza modificare il codice. Per esempio variabili di configurazione del sistema o del software, numero massimo di record da elaborare, tempo massimo di elaborazione ecc.

Curare molto la scelta dei nomi delle variabili, a tal fine adottare una convenzione e usare dei prefissi per i diversi tipi di entità (differenziare le variabili ad esempio dai nomi delle colonne delle tabelle da cui si estraggono i dati).
Usare dei nomi significativi per le procedure e i packages, questo vi eviterà di scrivere commenti che potrebbero rendere il codice meno leggibile. Evitare di scrivere commenti nel codice ci permette di avere una procedura tutta in una schermata ed è più comprensibile, evita la necessità di dover fare scroll nel codice.

Data isolation e cioè creare delle procedure per estrarre e manipolare i dati, delle API per l'accesso e la manipolazione dei dati di una tabella. Ad esempio SQLDeveloper genera queste API in automatico (nella voce di menù Tabella/GeneraApi)
Nascondere logiche complesse di estrazioni nelle Table Functions.
Spostare le "Select into" in funzioni e usare bulk collect e for all ogni volta che è possibile.

Single point of definition, cioè definire le funzioni in un solo punto per non avere più punti nel codice che fanno la stessa cosa, in futuro se si dovrà modificare il codice lo si fa in un solo punto. A tal fine creare delle piccole utility da riutilizzare .

Usare dei packages per la gestione degli errori e il logging.  Per il logging si può usare una tabella centralizzata che viene aggiornata di volta in volta con errori o log.

Tutto questo al fine di riusare il codice, si risparmia tempo nel creare il codice e nella manutenzione o refactoring.

Non usare VARCHAR2 di lunghezza fissa, in questo modo quando cambiano le dimensioni di una variabile non bisogna modificare il codice. Per questo viene consigliato nel video di usare dei subtype.

Ridurre la lunghezze delle procedure per non rendere illeggibile il codice a chi va a modificare il codice dopo di noi. No spaghetti-code.
Scrivere piccoli packages focalizzati su funzionalità atomiche.
Non superare le 50 righe per procedura e smetterla di scrivere troppo codice SQL
Usare una metodologia top-down

Personalmente ritengo utile rifattorizzare il codice dove necessario quando si apportano delle modifiche allo stesso.

Infine da particolare importanza a testare il codice e nel video viene illustrato come funziona un tool della Quest per gestire i test in automatico.

Nei prossimi post cercherò di parlare più in dettagli di alcuni dei suggerimenti, in modo da rendere disponibili esempi e codice.

Creazione di uno schema di test in Oracle XE

L'articolo descrive come creare e popolare una database minimale per fare tutti i tuoi esperimenti in PL/SQL e SQL. Sono allegati gli script in modo che puoi facilmente riprodurre il db e iniziare a divertirti. Il database è composto dalla tabella degli impiegati e dalla tabella dei dipartimenti.

Come prima cosa bisogna scaricare la versione XE del database Oracle e SQLDeveloper  dal sito della oracle.
In fase di installazione del db viene richiesta la password per l'utente di sistema, questa password sarà poi necessaria per il collegamento.
Dopo aver installato il db e SQLDeveloper bisogna configurare l'accesso al DB con l'utente SYSTEM da quest'ultimo. A tal fine vedi l'immagine allegata.

Una volta configurato accedre allo schema SYSTEM per creare il nostro nuovo database.
Bisogna creare un nuovo schema.

Per capire la differenza tra USER e SCHEMA : SCHEMA VS USER Oracle https://www.oratable.com/oracle-user-schema-difference/

ALTER SESSION SET "_ORACLE_SCRIPT"=true; 
CREATE USER EMP IDENTIFIED BY test;

Dopo aver creato l'utente bisogna dare i privilegi per le varie attività da fare.
Come prima cosa si deve dare il permesso connect in modo da poter aprire una sessione.

GRANT CONNECT TO EMP;

E poi per effettuare le più comuni operazioni:

GRANT CREATE TYPE
GRANT CREATE TABLE
GRANT CREATE TRIGGER
GRANT CREATE SEQUENCE
GRANT CREATE INDEXTYPE
GRANT CREATE PROCEDURE

Oppure si possono  dare i privilegi associati al ruolo di DBA,

GRANT  DBA TO EMP;

Il ruolo contiente tutti i privilegi da DBA, per avere l'elenco completo dei privilegi associati al ruolo :

SELECT * FROM DBA_SYS_PRIVS where grantee='DBA';

Il ruolo DBA è un ruolo predefinito che viene creato nell'installazione del DB. Deve essere dato solo agli amministratori del DB.

A questo punto c'è bisogno dei privilegi per allocare lo spazio per le tabelle quindi per creare i tablespaces.
Da wikipedia: "In informatica un tablespace (in italiano letteralmente: spazio della tabella) è un luogo d'archiviazione dove vengono tenuti gli oggetti di una base di dati correlati ai dati fisici.

Fornisce un livello di astrazione tra i dati fisici e i dati logici e serve ad allocare memoria per tutti i segmenti (un oggetto della base di dati che occupa uno spazio fisico come i dati di una tabella e gli indici) gestiti dal DBMS. Una volta creato, ci si riferisce al tablespace con un nome."

GRANT UNLIMITED TABLESPACE TO EMP;

Di default allo schema viene associato il tablespace user lo si può verificare lanciando la query:
select default_tablespace from dba_users
where  username = 'EMP';

Gli altri grant da dare sono:

GRANT CREATE TABLE TO EMP;
GRANT CREATE VIEW TO EMP;
GRANT CREATE PROCEDURE TO EMP;
GRANT CREATE SEQUENCE TO EMP;

Nel caso si volesse che le nostre tabelle fossero accessibili ad un altro user o schema:

GRANT SELECT, INSERT, UPDATE, DELETE ON schema.TABELLA TO UTENTE;

A questo punto definiamo la connessione a EMP in SQLDeveloper ci connettiamo e  possiamo iniziare a creare le tabelle.

Questi i comandi per creare le tabelle e popolare il db:

create table dept(  

  deptno     number(2,0),  

  dname      varchar2(14),  

  loc        varchar2(13),  

  constraint pk_dept primary key (deptno) 

);

create table emp(  

  empno    number(4,0),  

  ename    varchar2(10),  

  job      varchar2(9),  

  mgr      number(4,0),  

  hiredate date,  

  sal      number(7,2),  

  comm     number(7,2),  

  deptno   number(2,0),  

  constraint pk_emp primary key (empno),  

  constraint fk_deptno foreign key (deptno) references dept (deptno)  

);

insert into DEPT (DEPTNO, DNAME, LOC)

values(10, 'ACCOUNTING', 'NEW YORK');

insert into dept  

values(20, 'RESEARCH', 'DALLAS');

insert into dept  

values(30, 'SALES', 'CHICAGO');

insert into dept  

values(40, 'OPERATIONS', 'BOSTON');

insert into emp  

values(  

 7839, 'KING', 'PRESIDENT', null,  

 to_date('17-11-1981','dd-mm-yyyy'),  

 5000, null, 10  

);

insert into emp  

values(  

 7698, 'BLAKE', 'MANAGER', 7839,  

 to_date('1-5-1981','dd-mm-yyyy'),  

 2850, null, 30  

);

insert into emp  

values(  

 7782, 'CLARK', 'MANAGER', 7839,  

 to_date('9-6-1981','dd-mm-yyyy'),  

 2450, null, 10  

);

insert into emp  

values(  

 7566, 'JONES', 'MANAGER', 7839,  

 to_date('2-4-1981','dd-mm-yyyy'),  

 2975, null, 20  

);

insert into emp  

values(  

 7788, 'SCOTT', 'ANALYST', 7566,  

 to_date('13-JUL-87','dd-mm-rr') - 85,  

 3000, null, 20  

);

insert into emp  

values(  

 7902, 'FORD', 'ANALYST', 7566,  

 to_date('3-12-1981','dd-mm-yyyy'),  

 3000, null, 20  

);

insert into emp  

values(  

 7369, 'SMITH', 'CLERK', 7902,  

 to_date('17-12-1980','dd-mm-yyyy'),  

 800, null, 20  

);

insert into emp  

values(  

 7499, 'ALLEN', 'SALESMAN', 7698,  

 to_date('20-2-1981','dd-mm-yyyy'),  

 1600, 300, 30  

);

insert into emp  

values(  

 7521, 'WARD', 'SALESMAN', 7698,  

 to_date('22-2-1981','dd-mm-yyyy'),  

 1250, 500, 30  

);

insert into emp  

values(  

 7654, 'MARTIN', 'SALESMAN', 7698,  

 to_date('28-9-1981','dd-mm-yyyy'),  

 1250, 1400, 30  

);

insert into emp  

values(  

 7844, 'TURNER', 'SALESMAN', 7698,  

 to_date('8-9-1981','dd-mm-yyyy'),  

 1500, 0, 30  

);

insert into emp  

values(  

 7876, 'ADAMS', 'CLERK', 7788,  

 to_date('13-JUL-87', 'dd-mm-rr') - 51,  

 1100, null, 20  

);

insert into emp  

values(  

 7900, 'JAMES', 'CLERK', 7698,  

 to_date('3-12-1981','dd-mm-yyyy'),  

 950, null, 30  

);

insert into emp  

values(  

 7934, 'MILLER', 'CLERK', 7782,  

 to_date('23-1-1982','dd-mm-yyyy'),  

 1300, null, 10  

);

select ename, dname, job, empno, hiredate, loc  

from emp, dept  

where emp.deptno = dept.deptno  

order by ename