Laboratorul 03 - Cereri cu variabile substituite în SQL*Plus

Conținut

  • Crearea unei copii a unui tabel
  • Variabile substituite
    • Variabile substituite cu un singur ampersand(&)
    • Variabile substituite cu dublu ampersand(&&)
    • Variabile de sistem substituite cu un ampersand(&n)
    • Variabile definite cu ACCEPT
    • Variabile definite cu DEFINE și resetate cu UNDEFINE
  • Exerciții

Crearea unei copii a unui tabel

  • Metoda I
    • Se creează tabelul folosind comanda DDL CREATE;
    • Se iau informațiile din tabelul original și se inserează în copie folosind comanda INSERT cu un SELECT.
  • Metoda II
    • Se folosește comanda CREATE cu SELECT pentru inserare.

Exemplu metoda I, crearea unei copii a tabelului EMP:

Exemplu metoda 2, crearea unei copii a tabelului EMP:

Observații:

  • Niciuna dintre metode nu păstrează constrângerile de Primary Key, Unique, Foreign Key. Excepție este NOT NULL;
  • Prima metoda permite schimbarea numelor de coloana, a doua metoda nu permite acest lucru;
  • Pentru metoda I, dacă copia nu are același număr de coloane atunci trebuie să se specifice coloanele în care se vor insera date;
  • Pentru metoda I, dacă copia nu respectă ordinea coloanelor din tabelul original, atunci trebuie să se specifice coloanele, atât pentru Insert cât și pentru Select.
  • Pentru metoda II, coloanele vor avea același nume ca și coloanele din tabela originală.

Variabile substituite

O cerere SQL poate fi executată folosind anumiți parametri introduși de la tastatură. Acești parametri se mai numesc variabile substituite.

Variabile substituite cu un singur ampersand(&)

O astfel de variabilă se definește folosind &nume_variabila și este un parametru care se introduce de la tastatură în timpul execuției comenzii în care este utilizat.

Parametrul cu un singur ampersand trebuie să fie introdus de fiecare dată, chiar dacă este folosit de mai multe ori în aceeași comandă SQL.

Ex. 1. Să se selecteze id_ang, nume, functie și salariu pentru angajatii care au același sef. Id-ul șefului se va introduce de la tastatură.

Rezultatul execuției exercițiului 1:

Dacă nu se dorește afișarea valorilor vechi și noi se poate face următoarea setare: SET VERIFY OFF

Pentru a introduce de la tastatură valori de tip string, sau dată, acestea trebuie să fie introduse cu apostrof; Pentru a evita acest lucru, se poate introduce variabila cu apostrof direct în comandă.

Ex. 2. Să se selecteze numele, funcția și salariu pentru angajații care au aceași funcție. Funcția va fi introdusa de la tastatură.

Rezultatul execuției exercițiului 2:

Observații: Atenție unde se pune apostrof; Cât timp variabila este variabilă nedefinită, valoarea va fi cerută de fiecare dată când se execută cererea.

Este posibil să fie introduse de la tastatură o expresie, numele unei coloane sau chiar numele unei tabele în timpul execuției.

Ex. 3. Să se selecteze numele, funcția și salariul anual pentru toți angajații din același departament. Salariul anual este calculat după formula 12*salariu. Se va introduce de la tastatură funcția pentru salariul anual, numele tabelului, numele coloanei pe care se pune condiția și id-ul departamentului.

Rezultatul execuției exercițiului 3:

Variabile substituite cu dublu ampersand(&&)

Spre deosebire de variabilele cu un singur ampersand, o variabilă cu dublu ampersand va fi stocată și poate fi apelată pe toată sesiunea de lucru. Definirea se face similar &&nume_variabila și va fi cerută o singură dată. Folosirea ei de mai multe ori în cadrul comenzii se face apelând-o cu &nume_variabila.

Ex. 4. Să se selecteze numele, funcția și venitul lunar pentru toți angajații care au venitul lunar mai mare de 2000. Venitul lunar este o funcție care are formula salariu + nvl(comision, 0).

Rezultatul execuției exercițiului 4:

Pentru a reseta o variabilă dată cu dublu ampersant se va utiliza comanda UNDEFINE nume_variabila. Pentru exercițiul 4, pentru a reseta variabila venit_lunar, se va da comanda:

UNDEFINE venit_lunar

Funcția nvl(param1, param2) este o funcție care verifică dacă param1 este NULL și, în cazul în care param1 este NULL, va substitui valoarea parametrului 1 cu valoarea parametrului 2. Această funcție este implementată în toate RDBMS-urile (Sistem de Gestiune a Bazelor de Date Relaționale)

Variabile de sistem substituite cu un ampersand(&n)

Sunt variabile numerice (1-9) care sunt definite de sistem și funcționează similar cu variabilele cu dublu ampersand.

Avantajul folosirii acestor variabile este că pot fi apelate direct dintr-un fișier cu comenzi, fără a fi definite in prealabil.

Ex. 5. Să se selecteze id-ul angajatului, numele, funcția și data angajării pentru toți angajații care au aceeași funcție și au venit în firmă după o anumită dată. Funcția și data se vor da de la tastatură în momentul când se execută fișierul de comenzi.

Se va scrie următorul select înr-un fișier:

Execuția fișierului:

Cluaza ORDER BY este folosită pentru a ordona rezultatul în funcție de coloanele specificate. Pentru a le ordona ascendent, cuvântul cheie ASC poate să fie specificat după numele coloanei după care se face sortarea, sau să lipsească. Pentru a sorta descrescător, se va folosi cuvântul cheie DESC care va fi specificat după numele coloanei după care se face sortarea.

Variabile definite cu ACCEPT

Când definim o variabilă cu ampersand, promptul va fi numele variabilei; Folosind comanda ACCEPT, se poate redefini promptul și chiar se pot ascunde caracterele introduse de la tastatură.

Ex. 6. Să se selecteze numele, salariul și funcția angajaților care au aceeași funcție. Funcția se va da de la tastatură. Se vor scrie următoarele comenzi într-un fisier (de exemplu ex6.sql)

Execuția fișierul:

Ex. 7. Să se adauge un nou angajat. La introducerea salariului caracterele să fie ascunse.

Execuția fișierului:

O variabilă poate să fie setată folosind comanda DEFINE, în acest caz promptul nu va mai cere să se introducă o valoare pentru respectiva variabilă. O astfel de variabilă rămâne setată pe tot parcursul sesiunii, pană când va fi resetată folosindu-se comanda UNDEFINE.

Ex. 8. Să se calculeze o primă de 15% pentru toți angajații din departamentul 20.

Rezultatul execuției exercițiului 8:

Pentru a vedea setările tuturor variabilelor active, folosiți comanda DEFINE.

Exerciții individuale

1. Să se scrie o comandă SQL care listează toți angajații dintr-un departament (introdus ca parametru de la tastatură), care au venitul anual peste un venit mediu anual (introdus tot de la tastatură)

Dați cel puțin 4 metode de rezolvare.

Bibliografie

  1. Alexandru Boicea - Oracle SQL SQL Plus, Editura Printech
bd/laboratoare/03.txt · Last modified: 2018/10/17 21:21 by iulia.radulescu
CC Attribution-Share Alike 3.0 Unported
www.chimeric.de Valid CSS Driven by DokuWiki do yourself a favour and use a real browser - get firefox!! Recent changes RSS feed Valid XHTML 1.0