b5g23gk
In acest capitol sunt descrise variabilele substituite.Variabilele substituite sunt folosite in declaratiile SQL si permite utilizarea valorilor specificate la rulare.

Variabile in SQL *Plus
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:

Variabile legate
Utilizate pentru stocarea valorilor individuale ce pot fi asignate si citite in timpul rularii(vor fi discutate mai tarziu in acest curs).
Variabile substituite
Utilizate pentru stocarea partilor de text de comanda ,care sunt "editate" in comenzi inainte de executia lor.
Variabile substituite cu un singur ampersand.
Puteti utiliza 'variabile substituite' intr-un fisier de comanda sau intr-o declaratie SQL pentru a reprezenta valori ce vor fi aflate la executie.
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

Variabile substituite cu dublu ampersand.
Daca variabila este prefixata de dublu ampersand(&&),SQL*Plus va cere valoarea pentru variabila doar o singura data .SQL*Plus stocheaza prima valoare furnizata si o foloseste din nou de fiecare data cand declaratia SQL este rulata

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.

Substitutia datelor ca o comanda text
Cand informatia dintr-o comanda substituita este introdusa intr-o co- manda inaintea executiei sale,variabila poate fi folosita pentru a inzestra orice parte a structurii comenzii exceptand numele insusi al comenzii.De exemplu:

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;

Comanda DEFINE
O valoare poate fi asignata unei variabile folosind comanda DEFaINEi 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 UNDEFaINEi.

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:

Tipurile de date pot fi verificate
Prompturile pot fi mai explicative
Valorile de raspuns pot fi ascunse
Sintaxa comenzii este:

ACCaEPTi variabila a NUMBER/CHARi aPROMPT/NOPROMPT 'text'iaHIDEi

Sintaxa + Descrierea

NUMBER/CHAR determina tipul variabilei.Daca valoarea introdusa este nevalida , va fi afisat un mesaj.
PROMPT 'text' afiseaza daca text e specificat
NOPROMPT face ACCEPT sa sara o linie asteptind intrarea
HIDE suprima raspunsul utilizatorului. folositor pentru parole

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)

Rezumat
Cind SQL*Plus intilneste &variabila:

Daca &variabila este deja definita, definitia deja existenta e folosita.
Daca & variabila nu e definita:
Utilizatorul e gata pentru definitie
Definitia furnizata de utilizator e apoi folosita
Definitia furnizata de utilizator e apoi pierduta
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

DEFaINEi:

DEF DEPARTAMENT daca e definit, da definitia lui DEPARTAMENT daca nu specifica 'nedefinit'
Pentru a defini o variabila , se poate folosi deasemenea comanda

DEFaINEi.

DEF COMM =
Ghilimelele nu sunt necesare , dar ajuta daca "NVL (COMM,0)" expresia contine blancuri imbricate sau apostrofi.
Cit timp ramin variabilele definite? Pina cind le UNDEFainei sau le redefinim sau pina iesim din SQL*PLUS.

Alte 2 modalitati de definire a unei variabile:

SQL>ACCEPT variabila atipi aPROMPT textiaHIDEi

SQL>COLaUMNi nume coloana /alias NEW_VALUE variabila (tratata in

capitolul 11)

Cap.3 Exercitii -Rulind cereri cu parametrii.
Acest exercitiu va ofera oportunitatea sa creati fisiere care pot fi rulate interactiv , si care folosesc substitutii de variabile pentru crearea unor criterii de selectie.
TEME

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;