Variabili in SQL*Plus

venerdì 14 agosto 2009 alle 14:30 | Pubblicato in Diario, Varie | 1 commento
Etichette: oracle, sqlplus

SQL*Plus è lo strumento primario di accesso a un database Oracle. Come ha intitolato Tanel Poder una serie di suoi post, sqlplus è la seconda casa del DBA Oracle. Ovunque c’è un database Oracle c’è un sql*plus, non sempre si ha a disposizione altri client, grafici, che magari facilitano e velocizzano certe operazioni, ma solitamente rendono più difficili altre. Ho gia manifestato in passato la mia preferenza per le CLI (command line interface) a scapito delle GUI (graphical user interface). Io utilizzo come interfaccia grafica sul mio pc solo SQL Developer, del quale ho gia parlato descrivendo aspetti positivi e negativi. Il motivo principale per cui utilizzao un client grafico è per avere un output tabellare facile da esaminare, anche se in realtà sempre Tanel Poder ha mostrato come anche questo si può avere in sql*plus.

Il fatto è che anche SQL*Plus come tutti i programmi con interfaccia a linea di comando richiede esperienza, memoria dinamicità ed elasticità mentale.

Qualche giorno fa facendo una ricerca su Google sul’opzione -prelim di sqlplus, citata da Jonathan Lewis in un suo post (infatti ne aveva accennato qualche giorno prima, ma senza spiegare cosa fosse  Miladin Modrakovic in un post che spiega come accedere ad alcune viste fisse tramite accesso diretto alla SGA), sono capitato su questa parte del sito di Oracle, dedicata appunto a SQL*Plus. In questa sezione ho notato un interessante documento intitolato “SQL*Plus Substitution Variables“, documento molto interessante che consiglio a tutti i DBA che vogliono fare un ripasso sull’argomento. Leggendo quel documento mi si è sbloccato qualcosa nel cervello. Mi succede ogni tanto, un concetto mi rimane inceppato li e non riesco a digerirlo per un bel po’ di tempo (in questo caso possiamo parlare di anni) fino a quando tutt’a un tratto vedo la luce, così è stato con “Substitution Variables” in SQL*Plus.

Devo confessare che facevo un po’ confusione fra Bind Variables, quelle che in SQL*Plus si definiscono con il comando VAR[IABLE] [variable [type]] e le “Substitution Variables” che chiamerò “variabili di sostituzione” o semplicemente variabili di sql*plus. Perché le variabili di sql*plus in effetti sono variabili elaborate solo in sql*plus, mentre le bind variables sono variabili elaborate dal database server Oracle, tant’è che per valorizzarle si usano blocchi di PL/SQL e permettono di simulare i preparedStatement .

Le variabili di sql*plus si possono definire in modo esplicito con il comando DEFINE, facendo help define da sqlplus:

DEFINE
——

Specifies a substitution variable and assigns a CHAR value to it, or
lists the value and variable type of a single variable or all variables.

DEF[INE] [variable] | [variable = text]

Ecco, una caratteritica è che in questo modo si possono definire variabili solo di tipo “CHAR”. Però implicitamente è possibile definire anche varibili di tipo NUMBER (non sono previsti altri tipi). Il modo per ottenere variabili di tipo NUMBER è quello di utilizzare il comando ACCEPT, ad esempio (dal manuale);

ACCEPT ENUMBER NUMBER PROMPT 'Employee ID. :'

infatti:

SVILUPPO40@perseo10 > ACCEPT ENUMBER NUMBER PROMPT 'Employee ID. :'
Employee ID. :12
SVILUPPO40@perseo10 > define ENUMBER
DEFINE ENUMBER         =         12 (NUMBER)

Un’altro metodo è utilizzando un trucco che mi piace molto, cioè l’opzione new_value del comando COLUMN:

SCOTT@perseo10 > col empno new_value v_empno
SCOTT@perseo10 > select empno from emp where ename='ALLEN';

 EMPNO
----------
 7499

SCOTT@perseo10 > DEFINE V_EMPNO
DEFINE V_EMPNO         =       7499 (NUMBER)

Se si vogliono vedere utilizzi avanzati delle variabili di sql*plus si possono andare a vedere script per i test utilizzati da Tom Kyte nel suo libro o quelli pubblicati da  Alberto Dell’Era per fare due esempi.

Le variabili vengono solitamente utilizzate per automatizzare il più possibile gli scritpt fare elaborazioni sui valori occorre fare dei magheggi, ad esempio:

SCOTT@perseo10 > select &v_empno+1 empno from dual;
vecchio   1: select &v_empno+1 empno from dual
nuovo   1: select       7499+1 empno from dual

 EMPNO
----------
 7500

SCOTT@perseo10 > DEFINE V_EMPNO
DEFINE V_EMPNO         =       7500 (NUMBER)

Nell’esempio si vede come si utilizza una variabile di sqlplus, ovvero premettendola con il simbolo “&” (impostazione modificabil con il comando “SET DEF[INE] {&|c|ON|OFF}”.  Se la variabile non è stata definita in precedenza viene chiesto un valore al prompt:

SCOTT@perseo10 > select &newvar from emp;
Immettere un valore per newvar: empno
vecchio   1: select &newvar from emp
nuovo   1: select empno from emp

 EMPNO
----------
 7499
 7521
 7566
 7839

SQL*Plus effettua la sostituzione della variabile con il valore prima di eseguire l’operazione e per ogni riga del comando mostra una coppia di righe che iniziano la prima con la stringa “vecchio” (o old) la seconda con “nuovo” (o new) come si vede dall’esempio. In sostanza mostra la riga prima e dopo la sostituzione. Questo comportamento può essere disabilitato con il comando “SET VERIFY OFF”.

Un’altra cosa interessante da dire è  che le variabili possono essere utilizzate premettendo al nome uno o due “&”, la differenza è che quando si usa la doppia “&&” SQL*Plus mostra un prompt per richiedere un valore solo la prima volta che trova la variabile, le successive (anche se in quelle si usa un solo “&”) non lo richiede più e usa sempre lo stesso valore, altrimenti lo richiede ad ogni occorrenza.

Quelle di cui ho parlato sono le caratteristiche che mi interessano e piacciono di più e che voglio memorizzare, altre sono descritte sia qui che naturalmente sul manuale (piccola nota: non è che mi piaccia molto la struttura molto frammentata della manualistica introdotta con la versione 11g).

Quasi dimenticavo,  tempo fa scrissi questo post, un commentatore mi diede una buona indicazione che porta ad un’altra possibilità interessante delle variabili di sostitizione, spiegata sul manuale , ecco un esempio:

C:\oracle\instantclient_11_1>more test_pars.sql
set verify off
select * from emp where empno=&1
/
exit;

C:\oracle\instantclient_11_1>sqlplus scott/tiger@perseo10 @test_pars 7499

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Aug 14 15:37:26 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connesso a:
Oracle Database 10g Release 10.2.0.4.0 - Production

Procedura PL/SQL completata correttamente.

Modificata sessione.

 EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
 7499 ALLEN      SALESMAN        7698 20-02-1981 00:00:00       1840        300         30

Disconnesso da Oracle Database 10g Release 10.2.0.4.0 - Production

C:\oracle\instantclient_11_1>

Ci sono moltre altre cose che voglio approfondire e impare meglio su sql*plus, ne parlerò in altri post, altrimento questo diventa troppo confusionario.

About these ads