In acest capitol sunt descrise variabilele substituite.Variabilele substituite sunt folosite in declaratiile SQL si permite utilizarea valorilor specificate la rulare.
In plus fata de variabilele pe care le vom vedea in limbajul PL/SQL, SQL*Plus este inzestrat cu doua tipuri de variabile ale sale:
O variabila poate fi vazuta ca un container in care valoarea este stocata temporar.
O variabila substituita este prefixata de un singur ampersand(&) si o valoare care ii este asignata.
Urmatoarea declaratie obliga utilizatorul sa dea un numar de departament la executie:
SELECT EMPNO,ENAME,SAL
FROM EMP
WHERE DEPTNO = &DEPARTAMENT_NUMBER;
Enter value for department_number: 10
EMPNO ENAME SAL
----- --------- -----------
7782 CLARK 2,450.00
7839 KING 5,000.00
7934 MILLER 1,300.00
Exemplul anterior utilizeaza conditia WHERE DEPTNO=10.
Cu un singur ampersand utilizatorul este obligat sa dea o valoare de fiecare data cand comanda este executata,deoarece variabila nu este definita si de fiacare data valoarea introdusa nu este salvata .
O valoare de tip caracter sau data are nevoie sa fie inchisa in ghilimele simple cand sunt introduse la cerere.Pentru a evita ghilimelele simple ce tre- buie sa fie introduse la executie ,puteti pune variabilele in ghilimele simple.
In urmatoarea declaratie ,variabila este pusa intre ghilimele simple,
asa ca ghilimele nu mai sunt necesare la introducerea valorii respective:
SELECT ENAME,DEPTNO,SAL*12
FROM EMP
WHERE JOB = ' <JOB_TITLE';
Enter value for job_title:MANAGER
ENAME DEPTNO SAL*12
----- ------ --------
JONES 20 35700
BLAKE 30 34200
CLARK 10 29400
Cat timp variabila este variabila 'nedefinita' ,valoarea va fi ceruta de fiecare data cand declaratia este executata.
Este posibil sa fie cerut numele unei coloane sau chiar numele unei tabele in timpul executiei.
In urmatorul exemplu vi se cere o expresie aritmetica:
SELECT DEPTNO, <ARITHMETIC_EXPRESION
FROM EMP;
Enter value for arithmetic_expression:sal/12
DEPTNO SAL/12
------ -------
20 66.6667
30 133.333
30 104.167
20 247.917
30 104.167
30 237.5
10 204.167
20 250
10 416.667
30 125
20 91.6667
30 79.1667
20 250
10 108.333
SELECT ENAME,DEPTNO,JOB
FROM EMP
WHERE DEPTNO = &&DEPTNO_PLEASE;
Enter value for deptno_please: 10
ENAME DEPTNO JOB
----- ------ --------
CLARK 10 MANAGER
KING 10 PRESIDENT
MILLER 10 CLERK
Puteti folosi comanda SQL*Plus DEFINE pentru a determina daca variabila
este deja definita.Daca variabila este deja definita se afiseaza valoarea
asignata.
SQL> DEFINE
DEFINE DEPTNO_PLEASE = "10" (CHAR)
Comanda DEFINE este de asemenea folosita pentru a crea o variabila utilizator.
SELECT * FROM SALGRADE
WHERE &CONDITION
Enter value for CONDITION: losal>2000
Variabila CONDITION ,de mai sus,trece intreaga conditie a clauzei WHERE
intr-o comanda.Mai departe intregul cod al comenzii(incepand cu cuvantul SELECT)
poate fi trecut intr-o variabila.
SELECT &THE_REST;
O valoare poate fi asignata unei variabile folosind comanda DEF[INE] a lui SQL*PLUS.Valoarea definita trebuie referita intr-o construc- tie SELECT sau un fisier de comenzi , prefixind numele variabilei cu &. Variabilele pot fi golite folosind UNDEF[INE].
In urmatorul exemplu , o variabila a fost definita folosind o expresie aritmetica ce calculeaza remuneratia. In urmatoarele constructii, variabila REM e referita de un numar de ori. Variabila e apoi golita fo- losind UNDEFINE.
SQL> DEFINE REM ='SAL*12+NVL(COMM,0)' SQL>SELECT ENAME, JOB, <REM SQL>FROM EMP SQL>ORDER BY <REM; SQL >undefine REM ENAME JOB SAL*12+NUL(COMM,0) ------------------------------------------------ SMITH CLERK 9600 JAMES CLERK 11400 ADAMS CLERK 13200 WARD SALESMAN 15500 MILLER CLERK 15600 MARTIN SALESMAN 16400 TURNER SALESMAN 18000 ALLEN SALESMAN 19500 CLARK MANAGER 29400 BLAKE MANAGER 34200 JONES MANAGER 35700 SCOTT ANALYST 36000 FORD ANALYST 36000 KING PRESIDENT 60000
'' de la expresii sunt optionale , daca expresia nu contine spatii. Ghilimelele pot fi deasemenea folosite si sunt optionale dar folositoare daca expresia contine blancuri sau apostrofi.
Pornirea unui fisier cu comenzi care contine substitutii de variabile
Presupunind ca doriti o serie de rapoarte care sa listeze angajatii cu functii diferite - de exemplu , una pentru SALESMEN , una pentru CLERKS, una pentru MANAGERS si tot asa.Cunoasteti cum sa folositi substitutia de variabile pentru a obtine aceste rapoarte dintr-o singura constructie SELECT.
Cu toate acestea , exista o tehnica alternativa pe care s-o folositi si anume sa scrieti constructia SELECT intr-un fisier si apoi folosind comanda START sa-l executati.
Aceasta tehnica impune folosirea unor substitutii speciale de variabile. Exista 9 asemenea variabile si ele au intregii de la 1 la 9 ca nume. Pentru folosirea acestor variabile , puneti un '&' urmat de un intreg (1-9) in comanda SQL.Aceste variabile pot fi folosite de ori de cite ori doriti si in orice ordine .
De fiecare data cind comanda e rulata, fiecare '&1' din comanda e inlocuit de primul parametru dupa START fisier, fiecare '&2' e inlo- cuit de al doilea parametru s.a.m.d.
Pentru a crea un fisier de comenzi care ia un parametru specificind functia care trebuie afisata , introduceti:
SELECT EMPNO,ENAME,SAL
FROM EMP
WHERE JOB = ' <1' ;
SQL>SAVE JOB1
SQL*PLUS intoarce mesajul:
Created file job1
Apoi rulati comanda cu parametrul 'CLERK'.
SQL*PLUS inlocuieste variabila cu valoarea parametrului.
SQL> START JOB1 CLERK
EMPNO ENAME SAL
----- ----- ---
7369 SMITH 800
7876 ADAMS 1100
7900 JAMES 950
Observati ca nu se pot folosi variabile cind executati o
comanda cu RUN. Trebuie sa introduceti comanda in fisier si
sa o rulati cu START comanda.
Comanda ACCEPT
Comanda ACCEPT permite unei variabile sa fie create si unei valori , care e introdusa sa fie memorata in ea. Aceasta variabila poate fi apoi referita in constructia SQL. ACCEPT este folosita deseori intr-un fisier de comenzi . Sunt citeva avantaje rezultate din folosirea lui ACCEPT pentru definirea substitutiei de variabile:
Sintaxa comenzii este:
ACC[EPT] variabila [ NUMBER/CHAR] [PROMPT/NOPROMPT 'text'][HIDE]
Sintaxa + Descrierea
Exemple
SQL> ACCEPT SALARY NUMBER PROMPT 'Salary figure :'
Salary figure : 30000
SQL>ACCEPT PASSWORD CHAR PROMPT 'Password :' HIDE
Password :
SQL>ACCEPT COMM NUMBER NOPROMPT
500
SQL>DEFINE
DEFINE SALARY =30000(NUMBER)
DEFINE PASSWORD ="FREEBIES(CHAR)
DEFINE COMM =500(NUMBER)
Cind SQL*Plus intilneste &variabila:
Cind SQL Plus intilneste &&variabila :
La fel ca pasii 1 si 2 de deasupra cu exceptia faptului ca valoarea variabilei e salvata.
Observatie: &1 parametru e tratat ca &&.
Pentru a afla daca o variabila e deja definita, folositi comanda
DEF[INE]:
Pentru a defini o variabila , se poate folosi deasemenea comanda
DEF[INE].
Cit timp ramin variabilele definite? Pina cind le UNDEF[ine] sau le redefinim sau pina iesim din SQL*PLUS.
Alte 2 modalitati de definire a unei variabile:
SQL>ACCEPT variabila [tip] [PROMPT text][HIDE]
SQL>COL[UMN] nume coloana /alias NEW_VALUE variabila (tratata in
capitolul 11)
1. Generati o constructie prin care sa afisati angajatii cu date de
angajare intre 2 date date. Rulati de 2 ori cererea.
Modificati cererea pentru a folosi &&variabila .Rulati-o de citeva
ori. Observati diferenta.
2. Obtineti o cerere care sa accepte o functie data. Pentru
testare executati cererea de citeva ori.
ENAME JOB SAL MGR DEPTINO
----------------------------------------------
SCOTT ANALYST 3,000.00 7566 20
FORD ANALYST 3,000.00 7566 20
3. Definiti o variabila reprezentind expresia folosita pentru
calculul remuneratiei anuale a angajatilor.Folositi variabile
intr-o constructie care gaseste toti angajatii care cistiga
30.000 $ pe an sau mai mult.
ENAME SAL*12+NUL(COMM,0)
----------------------------------
JONES 35700
BLAKE 34200
SCOTT 36000
KING 60000
FORD 36000
Cap.3 Solutii
1. SELECT ENAME,HIREDATE
FROM EMP
WHERE HIREDATE BETWEEN'&FIRST_DATE'AND'&LAST_DATE';
2. SELECT ENAME,JOB,SAL,MGR,DEPTNO
FROM EMP
WHERE JOB ='&JOB';
3. SELECT ENAME,&REM
FROM EMP
WHERE &REM >30000;