Ć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 zdefiniowanej w ćwiczeniu 2 wpisać tych pracowników, z relacji prac, którzy pracują na stanowisku profesorskim i zostali zatrudnieni przed rokiem 1978.
INSERT INTO PRAC_KOPIA (SELECT *FROM PRAC
WHERE ZATRUDNIONY < '85/01/01' AND ETAT = 'PROFESOR');
Zad.2. Podnieść pensję pracownikom najgorzej zarabiającym do średniej pensji w zakładzie pracy.
UPDATE PRAC SET PLACA_POD = (SELECT AVG(PLACA_POD) FROM PRAC)
WHERE PLACA_POD < (SELECT AVG(PLACA_POD) FROM PRAC);
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 `*****'.
UPDATE PRAC SET PLACA_DOD = (SELECT AVG(PLACA_POD) FROM PRAC)
WHERE ID_ZESP = 20 AND SZEF LIKE (SELECT ID_PRAC FROM PRAC WHERE NAZWISKO LIKE '_____');
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.
UPDATE PRAC P1 SET (P1.PLACA_POD,P1.PLACA_DOD) =
(SELECT AVG(PLACA_POD)*1.2, MAX(NVL(PLACA_DOD,0)) FROM PRAC
WHERE ID_ZESP=P1.ID_ZESP) WHERE ZATRUDNIONY > '90/01/01';
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.
select nazwisko from prac where upper(etat) like upper('&etat');
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 &.
select nazwisko from prac where zatrudniony between '&data1' and '&data2';
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ą.
SELECT NAZWISKO, ETAT, PLACA_POD FROM PRAC WHERE ETAT <> 'ASYSTENT' AND ID_ZESP=(SELECT ID_ZESP FROM ZESP WHERE UPPER(NAZWA) LIKE UPPER('&NAZWA')) ORDER BY ZATRUDNIONY;
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.
ACCEPT etat PROMPT 'Wprowadz np. profesor'
ACCEPT id_zesp PROMPT 'Wprowadz np. informatyka'
SELECT * FROM PRAC
WHERE ETAT = UPPER('&etat') AND
ID_ZESP=(SELECT ID_ZESP
FROM ZESP WHERE UPPER(NAZWA) LIKE UPPER('&id_zesp'));
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ą.
CREATE VIEW ZADANIE AS
SELECT PRAC.NAZWISKO, ZESP.NAZWA FROM PRAC,ZESP
WHERE PRAC.ID_PRAC= ANY (SELECT PRAC.SZEF FROM PRAC)
AND PRAC.ID_ZESP = ZESP.ID_ZESP;
Lub
CREATE VIEW "Przelozeni i ich nazwy zesp" AS SELECT DISTINCT
szef.NAZWISKO, zesp.NAZWA FROM PRAC praco, PRAC szef, ZESP zesp
WHERE szef.ID_PRAC=praco.SZEF AND szef.ID_ZESP(+)=zesp.ID_ZESP;
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.
CREATE SEQUENCE SEQ START WITH 4000 INCREMENT BY 2;
insert into prac2 values (seq.nextval,'BABACKI','ASYSTENT',100,'90/06/20',1000,
NULL,10);
insert into prac2 values (seq.nextval,'DABACKI','ASYSTENT',
100,to_date('01-07-68','DD-MM-RR'), 2960.00, NULL,20);
SQL> insert into prac
values
(seq_id_prac.nextval,'BABACKI','ASYSTENT',100,'20-JUN-1990',1000,
NULL,10);
// 4 liczniki cykliczne, do przodu I do tyłu, inkrementacja ujemna (cache), podana max wartość i podany start
http://www.geekinterview.com/question_details/67535
dual - można wyświetlić wartość z licznika, datę systemową,..
tabele systemowe - ważne!!
Rosnący /*create sequence bbb start with 2 increment by 1 minvalue 2 maxvalue 5 cycle cache 3;*/
--select bbb.nextval from dual;
|
Malejący /*create sequence ccc start with 2 increment by -1 minvalue 2 maxvalue 5 cycle cache 3;*/
--select ccc.nextval from dual; |
--desc dual
LABORATORIUM z BAZ DANYCH
ORACLE
9