LUCRUL CU INTEROGARI DE ACTIUNE SI INTEROGARI CU PARAMETRI

Lucrul cu interogari de actiune si interogari cu parametri

Scopul lucrarii

Lucrarea are ca scop familiarizarea studentilor cu crearea si lucrul cu interogarile de actiune si interogarile cu parametri in Microsoft Office 2007.

Notiuni teoretice

Pe langa interogarile simple de selectare, cel mai frecvent utilizate in bazele de date, exista si alte tipuri de interogari, precum cele de actiune (de actualizare, de adaugare, de stergere si de creare tabel), de sinteza (sau tabel incrucisat) sau cele cu parametri.

Deblocarea interogarilor de actiune

In mod implicit, din motive de securitate, Access dezactiveaza toate interogarile de ac iune, in afara cazului in care baza de date se afla intr-o loca ie de incredere sau este semnata. Aceasta inseamna ca, la incercarea de executare a interogarii de ac iune, nu se intampla nimic.

Confirmarea acestei blocari este prezenta barei de mesaje sub Panglica (fig.1).

Nota: Este posibil ca bara de mesaje sa fie ascunsa. Pentru a o afisa, se selecteaza caseta Message Bar din grupul Show/Hide al filei Database Tools (fig. 2).

Activarea interogarilor de ac iune pentru sesiunea de lucru curenta se realizeaza prin selectarea butonului Options din bara de mesaje si, din fereastra de dialog care apare (fig. 3), selectarea optiunii Enable this content.

Crearea unei copii de rezerva a datelor

Inainte de a executa o interogare de actiune, este indicat sa se creeze o copie de rezerva a bazei de date.

Pentru aceasta, dupa un clic butonul Microsoft Office , se alege optiunea Manage si apoi Back Up Database.

In caseta de dialog Save As, se specifica un nume i o loca ie pentru copia de rezerva si apoi se apasa butonul Save.

Interogarile de actualizare

Interogarile de actualizare (update queries) se utilizeaza pentru modificarea datelor dintr-un set de inregistrari existente. Acestea nu pot adauga inregistrari noi la o baza de date si nici terge inregistrari intregi dintr-o baza de date.

Interogarile de actualizare accepta mai multe criterii, permit actualizarea unui numar mare de inregistrari intr-o singura opera iune i permit modificarea inregistrarilor in mai multe tabele in acela i timp.

Nota: Daca se doreste actualizarea unui numar mic de inregistrari, usor de gasit in mod manual, se poate deschide tabelul in vizualizarea Datasheet si modificarile pot fi realizate manual.

Trebuie retinut ca, nu se poate executa o interogare de actualizare pentru mai multe tipuri de campuri, cum ar fi campurile care rezulta din calcule (pentru ca nu au o loca ie permanenta de stocare) sau campurile AutoNumerotare.

Pentru a crea o interogare de actualizare, se creeaza mai intai o interogare simpla de selectare (vezi §3.2.1, §3.2.2 si §3.2.3), se adauga criterii de interogare (vezi §3.2.6) pana cand se restrange numarul de inregistrari la cele care contin campurile de actualizat. In aceasta etapa, executarea interogarii trebuie sa returneze numai inregistrarile de modificat.

Apoi, se efectueaza conversia interogarii intr-o interogare de actualizare. Pentru aceasta, cu interogarea in modul de afisare Design, se apasa butonul Update din fila Design, grupul Query Type (fig. 4).

Dupa aceasta operatie, in panoul inferior al grilei de proiectare a interogarii apare linia Update To: (fig. 5).

Din campurile afisate, se identifica cel care con ine datele care se vor modifica si se tasteaza expresia (criteriile de modificare) in linia Update To: pentru campul respectiv. Pot fi specificate criterii de modificare pentru mai multe campuri.

Tabelul 1. prezinta exemple de expresii valide pentru celulele Update To.

Pentru executarea interogarii de actualizare se apasa butonul Run din grupul Results al filei Design. Inainte ca actualizarea sa fie realizata, apare o fereastra de atentionare asupra imposibilitatii de anularea a efectului dupa actualizare si care precizeaza numarul de inregistrari ce vor fi actualizate (fig. 6).

Nota: In Panoul de navigare, numele interogarii de actualizare este precedat de iconul _.

Tabelul 1 Exemple de expresii valide pentru celulele Update To.

Interogarile de stergere

Interogarile de tergere (delete queries) elimina inregistrari complete din baza de date, respectiv toate datele din fiecare camp, impreuna cu valoarea cheie care da unicitate unei inregistrari.

Nota 1: De obicei, interogarile de tergere se folosesc numai atunci cand se doreste eliminarea rapida a unor cantita i mari de date. Pentru stergerea unui numar mic de inregistrari, usor de gasit in mod manual, se poate deschide tabelul in vizualizarea Datasheet, apoi se selecteaza campurile sau inregistrarile care se doresc a fi terse si se apasa tasta Delete.

Nota 2: Pentru a sterge campuri individuale dintr-un tabel, se poate folosi o interogare de actualizare care foloseste NULL sau irul de lungime zero (" ") pe post de criterii de actualizare celulele Update To).

Deoarece dupa tergerea unor inregistrari nu se pot anula modificarile, se recomanda crearea unei copii de rezerva a datelor inainte de executarea interogarii de tergere (vezi §2.2).

De retinut ca, ca si in cazul interogarilor de actualizare, Access blocheaza interogarea de stergere, in afara cazului in care se acorda incredere bazei de date. Pentru deblocarea interogarilor in sesiunea curenta, a se vedea §2.1.

Daca se sterg inregistrari din mai multe tabele i acele tabele sunt legate, se impune activarea optiunilor de Integritate referentiala (Enforce Referential Integrity) si de Stergere in cascada a campurilor corelate (Cascade Delete Related Records). Integritatea referentiala asigura faptul ca cheile externe dintr-o baza de date con in valori corecte, iar optiunea de stergere in cascada a campurilor corelate face ca tergerea unei inregistrari din partea 'unu' a rela iei sa determine tergerea tuturor inregistrarilor asociate din partea 'mai-mul i' a rela iei. Activarea acestor optiuni presupune editarea relatiilor dintre tabele. Pentru aceasta:

- se afiseaza fereastra Relationships (fig. 7) prin apasarea butonului Relationships din fila Database Tools;

- cu un dublu clic pe fiecare relatie (linie de legatura) unu-la-mai-multi (1 - ∞) se afiseaza fereastra Edit Relationships si se bifeaza cele doua optiuni (fig. 8).

Crearea si executarea unei interogari de stergere se realizeaza parcurgand pasii care urmeaza.

1. Se creeaza o interogare de selectare in modul Design numai pe baza tabelului din partea 'unu' a rela iei. E obligatoriu ca toate campurile tabelului sa fie incluse in interogare pentru a fi permisa stergerea de inregistrari complete.

2. Se specifica criteriile de interogare dorite pentru campuri astfel incat sa fie identificate doar inregistrarile de sters.

3. Se executa interogarea de selectare pentru a verifica daca sunt returnate inregistrarile de sters.

Se comuta din nou interogarea in modul Design .

5. Se transforma interogarea de selectare intr-o interogare de stergere. Pentru aceasta, din Fila Design, grupul Query Type, se apasa butonul Delete. Ca o confirmare, in panoul de jos al grilei de proiectare apare linia Delete ale carei celule afiseaza cuvantul Where.

6. Se executa interogarea de stergere, actiune care solicita o confirmare.

Nota: In Panoul de navigare, numele interogarii de actualizare este precedat de iconul

Interogarile cu parametri

Interogarile cu parametri sunt intrebari stocate in baza de date care solicita, inainte de executare, furnizarea interactiva de informatii suplimentare (parametri). Se extinde astfel flexibilitatea interogarii.

Pentru fiecare parametru, o interogare cu parametri afi eaza o caseta de dialog separata, care solicita o valoare pentru parametrul respectiv (fig. 9).

Interogarea cu parametri nu este un tip particular de interogare, parametrii putand fi asociati oricarei interogari de selectare sau de actualizare.

Pentru a crea o interogare cu parametri, se parcurg secventele de mai jos.

1. Se creeaza o interogare de selectare in modul Design.

2. In randul Criteria al campului pentru care se doreste aplicarea unui parametru, se tasteaza textul care se doreste a fi afi at in caseta de dialog asociata parametrului, incadrat in paranteze drepte (de exemplu: [Numele studentului

Nota: La executarea interogarii, textul de solicitare a parametrului apare in caseta de dialog fara a fi incadrat in paranteze drepte.

3. Daca este cazul, se repeta pasul 2 pentru a adauga si alti parametri pentru alte campuri.

Folosirea unor criterii de potrivire pentru o parte a valorii de camp

La aplicarea unui parametru, este posibil ca interogarea sa accepte un ir text i sa gaseasca potriviri cu acel ir in orice parte a campului. Pentru aceasta, in randul Criteria al campului pentru care se doreste aplicarea parametrului se tasteaza cuvantul cheie Like in combina ie cu metacaractere, ca in exemplul de mai jos:

Like '*'&[Text de afisat in caseta de dialog ]&'*'

Cu aceasta sintaxa, sirul text din parametru (tastat in caseta text la solicitarea de parametru) va fi gasit in toate aparitiile sale in valorile de camp.

Nota 1: Pentru a gasi potriviri cu inceputul unei valori de camp, nu se mai introduce secventa '*'& inaintea parantezei drepte de deschidere.

Nota 2: Pentru a gasi potriviri cu finalul unei valori de camp, nu se mai introduce secventa &'*' dupa paranteza dreapta de inchidere.

Folosirea unor criterii de potrivire pe baza valorilor inegale

La aplicarea unui parametru, este posibil ca interogarea sa returneze randurile care respecta alte criterii de compara ie pe baza parametrului. Pentru aceasta, se tasteaza un operator de compara ie la stanga primei paranteze patrate care incadreaza solicitarea de parametru, ca de exemplu >[Nota:], unde Nota este textul afi at in caseta de dialog asociata parametrului campului.

Chestiuni de studiat

3.1. Pe baza tabelelor existente, se va crea crea o interogare de actualizare, care sa modifice toate notele de 2 in

3.2. Se va crea o interogare de actualizare care sa determine ca toate disciplinele care sunt de tipul Facultativa sau Optionala si au tipul de evaluare Examen sa il transforme pe acesta din urma in Colocviu.

3.3. Se va crea o interogare de stergere care sa stearga toate inregistrarile din baza de date care se refera la discipline facultative.

Se va crea o interogare cu parametri care sa afiseze, pentru un student al carui nume este solicitat, urmatoarele informatii: prenumele, sectia, denumirea disciplinei, nota si numarul de credite obtinute la fiecare disciplina.

3.5. Pe baza interogarii create la punctul 3.4, se va crea o alta interogare cu parametri care sa afiseze situatia studentilor (numele, prenumele, sectia, denumirea disciplinei, nota si numarul de credite obtinute la fiecare disciplina) de la una din sectiile facultatii, ordonati ascendent dupa nume.

3.6. Pe baza interogarii create la punctul 3.5, se va crea o alta interogare cu parametri care sa afiseze aceeasi situatie a studentilor, de la toate sectiile, dar numai pentru disciplinele a caror denumire incepe cu un sir impus de caractere.

Modul de lucru

Se lanseaza in executie Microsoft Office Access 2007 si se deschide baza de date de lucru.

Se deblocheaza interogarile de ac iune pentru sesiunea de lucru curenta (vezi §2.1).

Se creeza o copie de rezerva a bazei de date (vezi §2.2)

Pe baza celor trei tabele (TblStudenti, TblDiscipline si TblNote), in vizualizarea Design, se creeaza o interogare de selectare pe baza campurilor Nume, Prenume, DenumireDisciplina si Nota.

Pentru a selecta doar inregistrarile pentru care nota este egala cu 2, pentru campul Nota se stabileste criteriul =2 prin tastare in linia Criteria.

Se executa interogarea pentru a afisa inregistrarile care vor fi actualizate.

Se trece in modul Design si se efectueaza conversia interogarii intr-o interogare de actualizare (vezi §2.3).

Pentru a modifica toate notele de 2 in 4, in linia Update To, coloana Note se tasteaza

Se executa interogarea si se deschide tabelul TblNote pentru a constata actualizarea notelor.

Se salveaza interogarea cu numele QryModificareNote.

Pe baza tabelului TblDiscipline, in vizualizarea Design, se creeaza o interogare de selectare pe baza campurilor DenumireDisciplina, TipDisciplina si TipEvaluare.

Se specifica urmatoarele criterii pe campuri (vezi §3.2.6)

- pentru campul TipDisciplina, criteriile 'Optionala'or 'Facultativa';

- pentru campul TipEvaluare, criteriul 'Examen'.

Se ruleaza interogarea pentru a verifica corectitudinea selectiei (afisarea inregistrarilor care vor fi actualizate), conform criteriilor specificate.

Se trece in modul Design si se efectueaza conversia interogarii intr-o interogare de actualizare (vezi §2.3).

Pentru a realiza actualizarea, in linia Update To si campul TipEvaluare se tasteaza Colocviu.

Se executa interogarea si se deschide tabelul TblDiscipline pentru a constata actualizarea tipului de evaluare la disciplinele Optionala si facultativa.

Se salveaza interogarea cu numele QryModificareEvaluare.

Inainte de a crea interogarea de stergere se parcurg cele doua etape pregatitoare:

Se verifica daca, in relatiile intre tabele, sunt active optiunile de Integritate referentiala (Enforce Referential Integrity) si de Stergere in cascada a campurilor corelate (Cascade Delete Related Records) - vezi §2.4;

Se verifica daca interogarile de ac iune sunt deblocate pentru sesiunea de lucru curenta (vezi §2.1).

Se creeaza o interogare de selectare in modul Design pe baza tabelului TblDiscipline si toate campurile acestuia. Pentru a include, printr-o singura actiune, toate campurile tabelului in interogare, se procedeaza asa cum este specificat la punctul 2 in

In grila de proiectare, pentru campul TipDisciplina, in linia Criteria, se introduce tasteaza Like Facultativa.

Se executa interogarea pentru a verifica daca sunt returnate inregistrarile care trebuie sterse.

Se comuta interogarea in modul Design si se efectueaza conversia interogarii de selectare intr-o interogare de stergere (vezi punctul 5 din §2.4).

Se executa interogarea de stergere si se deschide tabelul TblDiscipline pentru a constata inexistenta disciplinelor facultative.

Se salveaza interogarea cu numele QryStergereDiscipline.

Se creeaza o interogare de selectare in modul Design pe baza celor trei tabele (TblStudenti, TblDiscipline si TblNote) si a campurilor Nume, Prenume, Sectie, DenumireDisciplina si Nota.

Se adauga un camp calculat pentru a valida creditele asociate disciplinei in urma promovarii. Pentru aceasta, in prima celula libera din linia Field, se tasteaza expresia:

CrediteObtinute: IIf([Nota]>=5;1;0)*[NrCredite]

Se asociaza parametrul interogarii prin tastarea, in linia Criteria a campului Nume a textului [Numele studentului:]

Se executa interogarea, care solicita numele unui student din baza de date intr-o caseta de dialog.

Se salveaza interogarea cu numele QrySituatieStudent.

In panoul de navigare Microsoft Office Acces, se identifica interogarea creata anterior (QrySituatieStudent) si se copiaza in Clipboard folosindu-se optiunea Copy din meniul de comenzi rapide afisat cu un clic dreapta al mouse-ului.

Cu optiunea Paste a aceluiasi meniu de comenzi rapide, afisat cu un clic dreapta al mouse-ului in zona panoului de navigare, se creeaza o copie a interogarii, careia i se specifica, in fereastra de dialog care apare, numele QrySituatieSectie

Se deschide noua interogare in modul Design cu un clic dreapta al mouse-ului pe numele acesteia in panoul de navigare si selectarea optiunii Design View.

Atentie Un dublu clic cu mouse-ul pe numele interogarii cu parametri sau selectarea optiunii Open din meniul de comenzi rapide determina rularea interogarii, deci solicitarea parametrului printr-o fereastra de dialog.

Pentru campul Nume, se sterge textul din linia Criteria si, in linia Sort, se selecteaza optiunea Ascending.

Pentru campul Sectie, se tasteaza criteriul parametru [Sectia:].

Se salveaza interogarea si se ruleaza.

Analog punctelelor 30, 31 si 32, se creeaza si se deschide in modul de afisare Design o copie a interogarii QrySituatieSectie, cu numele QrySituatieDisciplina

Se sterge criteriul parametru existent pentru campul Sectie si se introduce unul pentru campul DenumireDisciplina cu urmatorul continut:

Like [Primele cateva caractere din numele unei discipline:] & '*'

Se salveaza interogarea si se ruleaza.

Se salveaza baza de date si se inchide.