Variable

Interaktive Skripte
Variable
...sal = ? …
… deptno = ? …
.. ename = ? ...
Werte eingeben
während der Laufzeit.
Nutzer
SQL1 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Substitutionsvariable
• Mit SQL*Plus können Werte in Variablen
temporär gespeichert werden.
• Folgende Möglichkeiten
SQL2 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Die &-Substitutionsvariable
Eine Variable, die mit Ampersand (&)
beginnt, fordert den Nutzer zur Eingabe
eines Wertes ein.
SQL> SELECT
2 FROM
3 WHERE
empno, ename, sal, deptno
emp
empno = &employee_num;
– Einfaches Ampersand (&)
– Doppeltes Ampersand (&&)
– DEFINE und ACCEPT Kommandos
SQL3 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Enter value for employee_num: 7369
EMPNO ENAME
SAL
DEPTNO
--------- ---------- --------- --------7369 SMITH
800
20
SQL4 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Verwendung des SET VERIFY
Kommandos
Schaltet die Anzeige des Textes zur
Ausgabe vor und nach dem Kommando,
zeigt das Ersetzen der Variablen durch den
Wert.
SQL>
SQL>
2
3
SET VERIFY ON
SELECT empno, ename, sal, deptno
FROM
emp
WHERE
empno = &employee_num;
Zeichen- und Datumswerte mit
Variablen.
Die Variablen der Datums- und Zeichenwerte in einfache Apostrophe
SQL> SELECT ename, deptno, sal*12
2 FROM
emp
3 WHERE job='&job_title';
Enter value for job_title: ANALYST
Enter value for employee_num: 7369
old
3: WHERE empno = &employee_num
new
3: WHERE empno = 7369
...
SQL5 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Spezifikation von Spaltennamen,
Ausdrücken und Text zur Laufzeit
Verwendung von Substitutionsvariablen
als Ergänzung
• einer WHERE Bedingung
• einer ORDER BY Klausel
• eines Spaltenausdruckes
• eines Tabellennamens
• eines ganzen SELECT Statements
SQL7 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
ENAME
DEPTNO
SAL*12
---------- --------- --------SCOTT
20
36000
FORD
20
36000
SQL6 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Spezifikation von Spaltennamen,
Ausdrücken und Text zur Laufzeit
SQL>
2
3
4
SELECT
FROM
WHERE
ORDER BY
empno, ename, job, &column_name
emp
&condition
&order_column;
Enter value for column_name: sal
Enter value for condition: sal>=3000
Enter value for order_column: ename
EMPNO
--------7902
7839
7788
ENAME
---------FORD
KING
SCOTT
JOB
SAL
--------- --------ANALYST
3000
PRESIDENT
5000
ANALYST
3000
SQL8 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Die && Substitutionsvariable
Verwendung des Doppel-Ampersands (&&)
wenn der Wert der Variablen
wiederverwendet werden soll, ohne dass
der Nutzer mehrfach eingibt.
SQL> SELECT
2 FROM
3 ORDER BY
empno, ename, job, &&column_name
emp
&column_name;
Enter value for column_name: deptno
EMPNO ENAME
JOB
DEPTNO
--------- ---------- --------- --------7839 KING
PRESIDENT
10
7782 CLARK
MANAGER
10
7934 MILLER
CLERK
10
...
14 rows selected.
SQL9 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Definition von Nutzer-Variablen
• Zur Vordefinition von Variablen kann
man eines der beiden SQL*Plus
Kommandos verwenden:
– DEFINE: Erzeugt eine Variable vom
Typ CHAR
– ACCEPT: Liest eine Nutzer-Eingabe
und speichert sie in eine Variable.
SQL10 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Das ACCEPT Kommando
• Erzeugt eine formatierte Nutzer-Eingabeanforderung
• Definiert explizit eine NUMBER oder
DATE Datentyp-Variable
Beispiel für das ACCEPT
Kommando
ACCEPT
SELECT
FROM
WHERE
/
dept PROMPT 'Eingabe des Abteilungsnamens: '
*
dept
dname = UPPER('&dept')
Eingabe des Abteilungsnamens: Sales
• Verbirgt gegebenenfalls Eingaben
(HIDE)
DEPTNO DNAME
LOC
--------- -------------- ------------30 SALES
CHICAGO
ACCEPT variable [datatype] [FORMAT format]
[PROMPT text] {HIDE}
SQL11 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
SQL12 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
DEFINE und UNDEFINE Kommandos
• Eine definierte Variable kann zurückgesetzt
werden mit:
– dem UNDEFINE Kommando
– Exit SQL*Plus
• Man kann die Rücksetzung mit dem
DEFINE Kommando überprüfen
Das DEFINE Kommando
• Man kann eine Variable erzeugen, die
den Abteilungsnamen enthält
SQL> DEFINE deptname = sales
SQL> DEFINE deptname
DEFINE DEPTNAME
= "sales" (CHAR)
• Die Variable kann man wie jede andere
benutzen.
SQL> SELECT *
2 FROM
dept
3 WHERE dname = UPPER('&deptname');
SQL13 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Show echo
SQL14 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
SET Kommando Variable
• ARRAYSIZE {20 | n} Zeilenanzahl setzen
• Verwendung des SET Kommandos zur
Steuerung der Session.
SET system_variable value
• Überprüfung der SET 's mit dem SHOW
Kommando.
SQL> SET ECHO ON
SQL> SHOW ECHO
echo ON
SQL15 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
• COLSEP {_ | text} Spaltentrennung
• FEEDBACK {6 | n |OFF | ON}
unterdrückt Angabe Anzahl Sätze
• LINESIZE {80 | n} Zeilenlänge
• PAGESIZE {24 | n} Zeilen pro Seite
• TERMOUT {OFF | ON} Ausgabe des
Skriptes
SQL16 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
SQL*Plus Format Kommandos
Das COLUMN Kommando
Steuert die Ausgabe einer Spalte
• COLUMN [column option]
• TTITLE [text | OFF | ON]
• BTITLE [text | OFF | ON]
• BREAK [ON report_element]
COL[UMN] [{column|alias} [option]]
• CLE[AR]: löscht die Spalten-Formate
• FOR[MAT] format: Ändert die Ausgabe
nach den FORMAT-Angaben
• HEA[DING] text: Setzt eine Spaltenüberschrift
• JUS[TIFY] {align}: Richtet die Spaltenüberschrift nach rechts, links oder
zentriert aus,
SQL17 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
SQL18 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Beispiel für das COLUMN Kommando
COLUMN Format Elemente
• Eine Überschrift erzeugen
COLUMN ename HEADING 'Employee|Name' FORMAT A15
COLUMN sal JUSTIFY LEFT FORMAT $99,990.00
COLUMN mgr FORMAT 999999999 NULL 'No manager'
Element Description
Example
Result
An
Setzt die Ausgabebreite auf n
N/A
N/A
9
Platzhalter für eine Zahl
999999
1234
0
Erzwingt eine Vornull
099999
01234
$
Dollarzeichen
$9999
$1234
.
Position Dezimalpunkt
9999.99
1234.00
,
Tausender Separator
9,999
1,234
• Spaltenfestlegungen überprüfen
COLUMN ename
• Spaltenfestlegungen zurücksetzen
COLUMN ename CLEAR
SQL19 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
SQL20 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Verwendung des BREAK
Kommandos
Unterdrückung von Duplikaten und
Zeilenumbrüche
• Duplikate unterdrücken
Verwendung der TTITLE und
BTITLE Kommandos
Kopfzeilen und Fusszeilen
TTI[TLE] [text|OFF|ON]
SQL> BREAK ON ename ON job
• Gesamtreport umbrechen
• Kopfzeile für den Report
SQL> TTITLE 'Salary|Report'
SQL> BREAK ON report
• Leerzeilen und Duplikate unterdrücken
• Fusszeile für den Report
SQL> BREAK ON ename SKIP 4 ON job SKIP2
SQL> BTITLE 'Confidential'
SQL21 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Erzeugen eines Skriptfiles und
Erweiterung zum Report
1. Erzeuge ein SQL SELECT Statement.
2. Speichere das SELECT Statement in
ein Skriptfile.
3. Lade das Skriptfile in einen Editor.
4. Füge Format-Kommandos vor das
SELECT Statement.
SQL23 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
SQL22 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Erzeugen eines Skriptfiles und
Erweiterung zum Report
5. Füge die Formatlösch-Kommandos
hinter das SELECT statement.
6. Speichere das Skriptfile
7. Gib “START filename” zur Abarbeitung
des Skriptes ein.
SQL24 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Beispielreport
Fri Oct 24
page
Employee
Report
Job
Category
----------------------CLERK
CLERK
CLERK
CLERK
MANAGER
MANAGER
MANAGER
SALESMAN
SALESMAN
SALESMAN
SALESMAN
Employee
Salary
----------------------- ----------------ADAMS
$1,100.00
JAMES
$950.00
MILLER
$1,300.00
SMITH
$800.00
BLAKE
$2,850.00
CLARK
$2,450.00
JONES
$2,975.00
ALLEN
$1,600.00
MARTIN
$1,250.00
TURNER
$1,500.00
WARD
$1,250.00
Confidential
SQL25 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
1