Universitatea Politehnica Bucureti

Facultatea de Automatic i Calculatoare

Catedra de Calculatoare

Laborator 3 Rularea cererilor cu variabile

substituite n SQL*Plus

Autori

Conf. Dr. Ing. Alexandru Boicea

As. Drnd. Ing. Ciprian-Octavian Truic

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

Exerciii

Cuprins

05.03.2014 Laborator 3 2

Metoda I

Se creaz tabelul folosind comanda DDL CREATE

Se iau informaiile din tabelul original i se insereaz n copie folosind comanda INSERT cu un SELECT

Metoda II

Se folosete comanda CREATE cu SELECT pentru insert

Crearea unei copii a unui tabel

05.03.2014 Laborator 3 3

Exemplu metoda I, crearea unei copii a tabelului EMP:

Crearea unei copii a unui tabel

05.03.2014 Laborator 3 4

Exemplu metoda 2, crearea unei copii a tabelului EMP:

Crearea unei copii a unui tabel

05.03.2014 Laborator 3 5

Obs. 1. Nici una din metode nu pstreaz constrngerile de Primary Key, Unique, Foreign Key. Excepie este NOT NULL

Obs. 2. Prima metoda permite schimbarea numelor de coloana, a doua metoda nu permite acest lucru.

Obs. 3. Pentru metoda I, dac copia nu are acelai numr de coloane atunci trebuie s se specifice coloanele n care se va insera

Obs 4. Pentru metoda I, dac copia nu respect ordinea coloanelor din tabelul original, atunci trebuie s se specifice coloanele att la clauza insert ct i la clauza select.

Obs. 5. Pentru metoda II, coloanele vor avea acelai nume precum coloanele din tabela original

Crearea unei copii a unui tabel

05.03.2014 Laborator 3 6

O cerere SQL poate fi executat folosind anumii parametrii introdui de la tastatur

Aceti parametri se mai numesc variabile substituite.

Variabile substituie

05.03.2014 Laborator 3 7

O astfel de variabil se definete folosind &nume_variabila i este un parametru care se introduce de la tastatur n timpul execuiei 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 aceai comand SQL

Variabile substituite cu un singur ampersand(&)

05.03.2014 Laborator 3 8

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

Variabile substituite cu un singur ampersand(&)

05.03.2014 Laborator 3 9

Rezultatul execuiei exerciiului 1:

Variabile substituite cu un singur ampersand(&)

05.03.2014 Laborator 3 10

Dac nu se dorete afiarea valorii vechi i noi se poate face urmtoarea setare: SET VERIFY OFF

Variabile substituite cu un singur ampersand(&)

05.03.2014 Laborator 3 11

Pentru a introduce de la tastatur valori de tip string sau dat acestea trebuie s fie intoduse cu ghilimele

Pentru a evita acest lucru, se poate introduce variabila ntre ghilimele direct n comand.

Variabile substituite cu un singur ampersand(&)

05.03.2014 Laborator 3 12

Ex. 2. S se selecteze numele, funcia i salariu pentru angajaii care au aceai funcie. Funcia va fi dat de la tastatur.

Variabile substituite cu un singur ampersand(&)

05.03.2014 Laborator 3 13

Rezultatul execuiei exerciiului 2:

Obs. 1. Atenie unde am pus gilimelele!!!

Obs. 2. Ct timp variabila este variabil nedefinit, valoarea va fi cerut de fiecare dat cnd se execut cererea

Variabile substituite cu un singur ampersand(&)

05.03.2014 Laborator 3 14

Este posibil s fie introduse de la tastatur o expresie, numele unei coloane sau chiar numele unei tabele n timpul execuiei.

Ex. 3. S se selecteze numele, funcia i salariul anual pentru toi angajaii din acelai departament. Salariul anual este calculat dup formula 12*salariu. Se va introduce de la tastatur funcia pentru salariul anual, numele tabelului, numele coloanei pe care se pune condiia i id-ul departamentului.

Variabile substituite cu un singur ampersand(&)

05.03.2014 Laborator 3 15

Rezultatul execuiei exerciiului 3:

Variabile substituite cu un singur ampersand(&)

05.03.2014 Laborator 3 16

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 apelnd-o cu &nume_variabila

Variabile substituite cu dublu ampersand(&&)

05.03.2014 Laborator 3 17

Ex. 4. S se selecteze numele, funcia i venitul lunar pentru toi angajaii care au venitul lunar mai mare de 2000. Venitul lunar este o funcie care are formula salariu + nvl(comision, 0)

Variabile substituite cu dublu ampersand(&&)

05.03.2014 Laborator 3 18

Rezultatul execuiei exerciiului 4:

Variabile substituite cu dublu ampersand(&&)

05.03.2014 Laborator 3 19

Obs. 1. Pentru a reseta o variabil dat cu dublu ampersant se va utiliza comanda UNDEFINE nume_variabila. Pentru exerciiul 4, pentru a reseta variabila venit_lunar, se va da comanda:

SQL> UNDEFINE venit_lunar.

Obs. 2. Funcia nvl(param1, param2) este o funcie care verific dac param1 este NULL i, n cazul n care param1=NULL, va substitui valoarea parametrului 1 cu valoarea parametrului 2. Aceast funcie este implementat n toate RDBMS-urile (Sistem de Gestiune a Bazelor de Date Relaionale)

Variabile substituite cu dublu ampersand(&&)

05.03.2014 Laborator 3 20

Sunt variabile numerice (1-9) care sunt definite de sistem i funcioneaz similar cu variabilele cu dublu ampersand

Avantajul folosirii acestor variabile este c pot fi apelate direct dintr-un fiier cu comenzi

Variabile de sistem substituite cu un ampersand(&n)

05.03.2014 Laborator 3 21

Ex. 5. S se selecteze id-ul angajatului, numele, funcia i data angajrii pentru toi angajaii care au aceai funcie i au venit n firm dup o anumit dat. Funcia i data se vor da de la tastatur n momentul cnd se execut fiierul n care este scris selectul. Se va scrie urmtorul select nr-un fiier:

Variabile de sistem substituite cu un ampersand(&n)

05.03.2014 Laborator 3 22

Execuia fiierului:

Variabile de sistem substituite cu un ampersand(&n)

05.03.2014 Laborator 3 23

Obs. 1. Cluaza ORDER BY este folosit pentru a ordona rezultatul n funcie de coloanele specificate. Pentru a le ordona ascendent cuvntul cheie ASC poate s fie specificat, dup numele coloanei dup care se face sortarea, sau s lipseasc. Pentru a sorta descresctor se va folosi cuvntul cheie DESC care va fi specificat dup numele coloanei dup care se face sortarea.

Variabile de sistem substituite cu un ampersand(&n)

05.03.2014 Laborator 3 24

Cnd definim o variabil cu ampersand, promptul va fi numele variabile

Folosind comanda ACCEPT, se poate redefini promptul i chiar se poate ascunde caracterele introduse de la tastatur

Variabile definite cu ACCEPT

05.03.2014 Laborator 3 25

Ex. 6. S se selecteze numele, salariul i funcia angajailor care au aceai funcie. Funcia se va da de la tastatur.

Se vor scrie urmtoarele comenzi ntr-un fisier (de exemplu ex6.sql)

Variabile definite cu ACCEPT

05.03.2014 Laborator 3 26

Execuia fiierul:

Variabile definite cu ACCEPT

05.03.2014 Laborator 3 27

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

Variabile definite cu ACCEPT

05.03.2014 Laborator 3 28

Execuia fiierului:

Variabile definite cu ACCEPT

05.03.2014 Laborator 3 29

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 rmne setat pe tot parcursul sesiunii pan cnd va fi resetat folosind-se comanda UNDEFINE

Variabile definite cu DEFINE i resetate cu UNDEFINE

05.03.2014 Laborator 3 30

Ex. 8. S se calculeze o prim de 15% pentur toi angajaii din departamentul 20.

Variabile definite cu DEFINE i resetate cu UNDEFINE

05.03.2014 Laborator 3 31

Rezultatul execuiei exerciiului 8:

Variabile definite cu DEFINE i resetate cu UNDEFINE

05.03.2014 Laborator 3 32

Pentru a vedea setrile tuturor variabilelor active, folosii comanda DEFINE

Variabile definite cu DEFINE i resetate cu UNDEFINE

05.03.2014 Laborator 3 33

1. S se scrie o comand SQL care listeaz toi angajaii dintr-un departament (introdus ca parametru de la tastatur), care au venitul anual peste un venit mediu anual (introdus tot de la tastatur)

Dai cel puin 4 metode de rezolvare.

Exerciii

05.03.2014 Laborator 3 34