Ćwiczenie 4
MODYFIKACJA ZAWARTOŚCI RELACJI
Sposób wypełniania relacji krotkami
Wstawianie krotek
W celu wstawienie krotki do relacji stosujemy polecenie insert w następującym formacie ogólnym:
SQL> insert into nazwa_relacji (atrybut1, atrybut2, ..)
values (wartość1, wartość2);
W przypadku wstawiania wielu krotek do relacji wygodnie jest użyć zmiennych, np.:
SQL> insert into zesp (id_zesp, nazwa, adres)
2 values (&N_ZESP, `&NAZWA_ZESP','&LOKAL');
i wielokrotne jego wykonanie (poleceniem run).
Wstawiane krotki mogą być wynikiem zapytania select, mogą więc pochodzić z innych relacji. W tym celu stosujemy polecenie insert o formacie ogólnym:
SQL>insert into nazwa_relacji (atrybut1, atrybut2, ..)
2 select lista
from relacja1, relacja2;
Modyfikowanie krotek
Do modyfikowania krotek służy polecenie update o formacie agólnym:
SQL>update relacja [alias]
set atrybut [,atrybut]={wyrażenie|podzapytanie}
where warunki;
np.:
SQL>update prac
set placa_pod=(select placa_pod
from dodatki d
where d.numer=prac.numer)
where numer in
(select numer from dodatki);
Usuwanie krotek z relacji
W celu usunięcia krotki (krotek) z relacji stosujemy polecenie delete o formiacie:
SQL>Delete from relacja
[where warunki];
np.:
SQL> delete from prac
2 where etat='ASYSTENT';
Zadania do samodzielnego wykonania
Zad.1. Do relacji prac kopia wpisać tych pracowników, z relacji prac, którzy pracują na stanowisku profesorskim i zostali zatrudnieni przed rokiem 1978.
Zad.2. Podnieść pensję pracownikom najgorzej zarabiającym do średniej pensji w zakładzie pracy.
Zad.3. Uaktualnić pensję dodatkową pracownikom zespołu 20. Nowa pensja ma być równa średniej pensji podstawowej pracowników, których przełożonym jest `*****'.
Zad.4. Zwiększyć płacę podstawową do 120% średniej płacy podstawowej w zespole pracownika oraz zwiększyć płacę dodatkową do wartości równej maksymalnej płacy dodatkowej w zespole pracownika. Operacji dokonać tylko dla pracowników zatrudnionych po 1990 roku.
Wskazówka: użyć funkcji nvl(wyrażenie, wartość) służącej do obsługi tzw. wartości pustych.
Zmienne
Zmienne umożliwiają parametryzowanie kierowanych do SZBD zapytań, które mogą być wywoływane wielokrotnie (z różnymi parametrami aktualnymi. Rozróżniamy zmienne lokalne poprzedzone & i zmienne globalne prefiksowane &&.
Zmienne lokalne
Zasięg zmiennych lokalnych lokalnych jest ograniczony do pojedynczego wykonania polecenia select. Podczas wykonywania polecenia możliwe jest parametryzowanie nazw atrybutów i relacji np.
SQL> select &atrybut61, atrybut2
from &relacja
where id_zesp = &identyfikator_zespolu
and etat=&nazwa_etatu;
W powyższym przykładzie użyto 5 zmiennych (wartości nie są zapamiętywane po wykonaniu polecenia select
Enter value for atrybut1: nazwisko
Enter value for atrybut2: szef
Old 1: select &atrybut1, &atrybut2
New 1: select nazwisko, szef
Enter value for relacja: prac
Old 2: from &relacja
New 2: from prac
Enter value for identyfikator_zespolu: 20
Old 3: where id_zesp=& identyfikator_zespolu
New 3: where id_zesp=20
Enter value for nazwa_etatu:'ASYSTENT'
Old 4: and etat=&nazwa_etatu
New 4: and etat= `ASYSTENT'
Zadania do samodzielnego wykonania
Zad.1. Wyświetlić nazwiska pracowników pracujących na etacie wprowadzonym interakcyjnie. W zapytaniu nie rozróżniać małych i dużych liter.
Wskazówka.
Enter value for etat: AsYsTeNt
Old 2: where upper(etat) like upper(`&etat')
New 2 where upper(etat) like upper(`AsYsTeNt')
Zad. 2. Wyświetlić nazwiska pracowników zatrudnionych między dwoma interakcyjnie podanymi datami. Wykorzystać zmienne z pojedynczym znakiem &.
Zad.3. Wyświetlić nazwiska, etaty, płace podstawowe pracowników nie będących asystentami, pracujących w zespole, którego nazwa wprowadzana jest interakcyjnie. Uporządkować zgodnie z malejącą datą.
Wskazówka.
Enter value for nazwa_zespolu: BAZY DANYCH
Old 5: and nazwa like `&nazwa_zespolu'
New 5 and nazwa like `BAZY DANYCH'
Zmienne globalne
Wartość zmiennej globalnej, raz określona, zostaje zapamiętana w systemie i jest wykorzystywana przy każdorazowym odwoływaniu się do reprezentującej ja zmiennej. Zmienne definiujemy poleceniem define, np.”
define roczna_placa='placa_pod*12'
Do tak zdefiniowanej zmiennej odwołujemy się w zapytaniu poprzedzając ja znakiem &, np.:
SQL>select nazwisko, &roczna_placa from pracownik;
Wczesniej zdefiniowana zmienna może być usunięta za pomocą polecenia undefine.
Polecenia ze zmiennymi, ze względu na swoją uniwersalność są często zapisywane do plików dyskowych. Umoźliwia to ich wielokrotne stosowanie w różnych sesjach użytkownika.
Do zapamiętywania poleceń w plikach dyskowych stosuje się ponadto polecenie accept do niejawnego definiowania zmiennych.
Ogólny format polecenia accept jest następujący:
accept zmienna [number|char][prompt|noprompt `tekst'][hide]
gdzie:
number|char określa typ oczekiwanej danej;
prompt `tekst' opisuje pobierana daną;
noprompt pominięcie wyświetlania opisu danej;
hide wprowadzana dana nie jest wyświetlana na ekranie konsoli;
np.:
SQL>select nazwisko, numer from prac
2 where etat='&1';
Enter value for 1: `ADIUNKT'
Old 3: where etat='&1'
New 3: where etat='ADIUNKT'
Save praca1 - wydanie tego polecenia spowoduje wyświetlenie komunikatu:
Created file praca1
W celu wykonania zapamiętanego w pliku polecenia, piszemy:
Start praca1 ADIUNKT
Old 3: where etat='&1'
New 3: where etat='ADIUNKT'
Accept haslo char prompt `HASŁO: `hide
Zadanie do samodzielnego wykonania
Zad. 1 Korzystając z polecenia accept zdefiniować zmienną id_zesp oraz etat. Na podstawie powyższych zmiennych wydać zapytanie wyświetlające dane o pracownikach wybranego zespołu i pracujących na wybranym etacie.
Wskazówka.
Old 2: where id_zesp=&id_zesp
New 2: where id_zesp=20
Old 3: where etat=&etat
New 3: where etat='ASYSTENT'
Perspektywy
Strukturą pochodną w stosunku do relacji jest tzw. perspektywa (ang. view), która ogranicza zakres dostępnych danych do atrybutów i krotek określonych w definicji perspektywy, jest definiowana na bazie co najmniej jednej relacji i innej perspektywy i pamiętana w systemie wyłącznie w postaci definicji. Stosujemy je w celu ograniczenia dostępu do danych, uproszczenia zapytań, a także zapytań typu ad hoc, czyli zapewnienia niezależności danych w stosunku do aplikacji.
Rozróżniamy perspektywy proste, udostępniające dane z pojedynczej relacji i złożone, udostępniające dane z wielu relacji, a w jej definicji można użyć operacji połączeń relacji, operacji na zbiorach , grupowania krotek, itp.
Jedną z ważnych własności perspektyw jest możliwość weryfikacji integralności referencyjnej danych. Opcjonalna klauzula with check option pozwala na wstawianie i modyfikację krotek w sposób niezgodny z warunkami selekcji perspektywy.
Format ogólny jest następujący:
SQL> create [or replace] view nazwa_perspektywy
[(atrybut1, atrybut2, atrybut3)]
as select cialo_polecenia_select
[with check option];
np. (erspektywa prosta):
SQL>create view asystenci
as select id_prac, nazwisko
from prac
where etat='ASYSTENT';
i perspektywa złożona:
SQL> create view pods_zesp
(nazwa, placa_pod, placa_max, placa_przec)
as select nazwa, min(placa_pod), max(placa_pod), avg(placa_pod)
from prac, zesp
where prac.id_zesp=zesp.id_zesp)
group by nazwa;
Poprzez użycie opcji weryfikacji integralności, ograniczamy modyfikację zawartości relacji bazowej (nie jest możliwe polecenie update), np.:
SQL> create or repleace view adiunkci
as select id_prac, nazwisko, etat
from prac
where etat='ADIUNKT'
with check option;
Opis utworzonych perspektyw można odczytać odwołując się odpowiednim zapytaniem do perspektyw systemowych ALL_VIEWS i USER_VIEWS.
W celu usunięcia perspektyw stosujemy polecenie drop view o formacie:
SQL>drop view nazwa_perspektywy;
Zadanie do samodzielnego wykonania
Zad.1. Zdefiniować perspektywę wyświetlającą wszystkich przełożonych i nazwy zespołów, w których pracują.
Liczniki
Liczniki są używane do generowania unikalnych identyfikatorów krotek i są obiektami automatycznie zwiększającą swoją wartość po każdorazowym odczycie. Definiujemy:
SQL> create sequence [nazwa użytkownika] nazwa licznika
[increment by liczba]
start with wartość_początkowa]
[cycle|nocycle];
gdzie:
nazwa użytkownika - parametr opcjonalny określający użytkownika, który utworzył licznik,
increment by - opcjonalna wartość zwiększanie (zmniejszania) licznika,
start with - wartość początkowa,
cycle|nocycle - domyślnie nocycle parametr określający, czy po osiągnięciu wartości maksymalnej, licznik rozpocznie zliczanie od wartości początkowej
Odczyt nowej wartości z licznika realizujemy poleceniem:
SQL> select nazwa_licznika.nextval from dual;
a wartości aktualnej:
SQL> select nazwa_licznika.currval from dual;
Po odczytaniu wartości licznika nie można wrócić do jego poprzedniej wartości, nawet poprzez wykonanie rollback.
Opis utworzonych liczników można odczytać odwołując się odpowiednim zapytaniem do perspektyw systemowych ALL_SEQUENCES i USER_ SEQUENCES.
Zadanie do samodzielnego wykonania
Zad.1. Wpisać krotkę do relacji prac, wykorzystując licznik do generowania unikalnych numerów pracowników. Licznik ma być acykliczny, ma rozpocząć zliczanie od wartości 4000 i zwiększać się o 2 po każdym odczycie nowej wartości.
SQL> insert into prac
values
(seq_id_prac.nextval,'BABACKI','ASYSTENT',100,'20-JUN-1990',1000,
NULL,10);
LABORATORIUM z BAZ DANYCH
ORACLE
6