Używanie manipulacji danymi

Oracle udostępnia polecenia języka Data Manipulation Language do wykonywania operacji na danych w bazie danych. Operacje na danych mogą polegać na zapełnianiu tabel bazy danych aplikacją lub danymi biznesowymi, modyfikowaniu danych i usuwaniu danych z bazy danych, gdy jest to wymagane. Oprócz operacji na danych istnieje zestaw poleceń, które służą do sterowania tymi operacjami. Polecenia te są zgrupowane w języku kontroli transakcji.

Istnieją trzy typy instrukcji DML zaangażowanych w logiczną transakcję SQL, a mianowicie: wstawianie, aktualizowanie, usuwanie i scalanie. Transakcja to logiczny zbiór działań DML w ramach sesji bazy danych.

Instrukcja INSERT

Polecenie INSERT służy do przechowywania danych w tabelach. Polecenie INSERT jest często używane w językach programowania wyższego poziomu, takich jak Visual Basic.NET lub C ++, jako osadzone polecenie SQL; jednak polecenie to można również wykonać w wierszu polecenia SQL * PLUS w trybie poleceń. Istnieją dwie różne formy polecenia INSERT. Pierwsza forma jest używana, jeśli nowy wiersz będzie zawierał wartość wstawioną do każdej kolumny wiersza. Druga postać polecenia INSERT służy do wstawiania wierszy, w których niektóre dane kolumny są nieznane lub domyślnie pochodzą z innej logiki biznesowej. Ta postać polecenia INSERT wymaga określenia nazw kolumn, dla których dane są przechowywane.

Składnia:

Poniższą składnię można zastosować, jeśli wartości dla wszystkich kolumn w tabeli są określone i znane.

INSERT INTO table
VALUES (column1 value, column2 value, 
...);

Poniższą składnię można zastosować, jeśli tylko kilka kolumn z tabeli ma zostać wypełnionych wartością. Pozostałe kolumny mogą wywnioskować ich wartości jako NULL lub z innej logiki biznesowej.

INSERT INTO table (column1 name, column2 name, . . .)
VALUES (column1 value, column2 value, . . .);

Poniższa instrukcja INSERT tworzy nowy rekord pracownika w tabeli EMPLOYEES. Zwróć uwagę, że wstawia wartości w kolumnach podstawowych EMPLOYEE_ID, FIRST_NAME, SALARY i DEPARTMENT_ID.

INSERT INTO employees (EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID)
VALUES (130, 'KEMP', 3800, 10);

W przeciwnym razie pełne dane pracowników można wstawić do tabeli PRACOWNICY bez określania listy kolumn za pomocą poniższej instrukcji INSERT - pod warunkiem, że wartości są wcześniej znane i muszą być zgodne z typem danych i pozycją kolumn w tabeli.

INSERT INTO employees
VALUES (130, 'KEMP','GARNER', '[email protected]', '48309290',TO_DATE ('01-JAN-2012'), 'SALES', 3800, 0, 110, 10);

Wstawiane wartości muszą być zgodne z typem danych kolumny. Literały, wartości stałe i wartości specjalne, takie jak funkcje, SYSDATE, CURRENT_DATE, SEQ.CURRVAL (NEXTVAL) lub USER mogą być używane jako wartości kolumn. Podane wartości muszą być zgodne z regułami ogólnymi. Literały ciągów i wartości dat muszą być umieszczone w cudzysłowie. Wartość daty można podać w formacie DD-MON-RR lub D-MON-RRRR, ale RRRR jest preferowany, ponieważ wyraźnie określa wiek i nie zależy od wewnętrznej logiki obliczania wieku RR.

Instrukcja INSERT-AS-SELECT (IAS)

Dane mogą zostać wprowadzone do tabeli docelowej z tabeli źródłowej za pomocą operacji INSERT..AS..SELECT (IAS). Jest to operacja bezpośredniego odczytu ścieżki.Jest to prosty sposób tworzenia kopii danych z jednej tabeli do drugiej lub tworzenia kopii zapasowej tabeli, w której operacje na tabeli źródłowej są w trybie online.

Na przykład dane można skopiować z tabeli EMPLOYEES do tabeli EMP_HISTORY.

INSERT INTO EMP_HISTORY
SELECT EMPLOYEE_ID, EMPLOYEE_NAME, SALARY, DEPARTMENT_ID
FROM employees;

Instrukcja UPDATE

Polecenie UPDATE modyfikuje dane przechowywane w kolumnie i może aktualizować jeden lub wiele wierszy naraz w zależności od zestawu wyników przefiltrowanego według warunków określonych w klauzuli WHERE. Pamiętaj, że aktualizowanie kolumn różni się od zmieniania kolumn. Wcześniej w tym rozdziale zapoznałeś się z poleceniem ALTER. Polecenie ALTER zmienia strukturę tabeli, ale nie wpływa na dane w tabeli. Polecenie UPDATE zmienia dane w tabeli, a nie w strukturze tabeli.

Składnia:

UPDATE table
SET column = value [, column = value ...]
[WHERE condition]

Ze składni

Kolumna SET = wyrażenie może być dowolną kombinacją znaków, formuł lub funkcji, które będą aktualizować dane w określonej nazwie kolumny.Klauzula WHERE jest opcjonalna, ale jeśli jest zawarta, określa, które wiersze będą aktualizowane.Tylko jedna tabela może być aktualizowane na raz za pomocą polecenia UPDATE.

Poniższe oświadczenie UPDATE aktualizuje wynagrodzenie pracownika JOHN do 5000.

UPDATE employees
SET salary = 5000
WHERE UPPER (first_name) = 'JOHN';

Chociaż predykaty WHERE są opcjonalne, ale muszą być logicznie dołączane, aby zmodyfikować tylko wymagany wiersz w tabeli. Poniższe oświadczenie UPDATE aktualizuje wynagrodzenia wszystkich pracowników w tabeli.

UPDATE employees
SET salary = 5000;

Wiele kolumn można również zaktualizować, określając wiele kolumn w klauzuli SET oddzielonych przecinkami. Na przykład, jeśli zarówno wynagrodzenie, jak i stanowisko pracy muszą zostać zmienione na odpowiednio 5000 i SPRZEDAŻ dla JANA, instrukcja UPDATE wygląda następująco:

UPDATE employees
SET	SALARY = 5000,
	JOB_ID = 'SALES'
WHERE UPPER (first_name) = 'JOHN';

1 row updated.

Innym sposobem aktualizowania wielu kolumn w tym samym wierszu jest użycie podzapytania.

UPDATE employees
SET (SALARY, JOB_ID) = (SELECT 5000, 'SALES' FROM DUAL)
WHERE UPPER (ENAME) = 'JOHN'

Instrukcja DELETE

Polecenie DELETE jest jedną z najprostszych instrukcji SQL. Usuwa jeden lub więcej wierszy z tabeli. Wiele operacji usuwania tabeli jest niedozwolonych w SQL.Składnia polecenia DELETE jest następująca.

DELETE FROM table_name
    [WHERE condition];

Polecenie DELETE usuwa wszystkie wiersze w tabeli, które spełniają warunek z opcjonalnej klauzuli WHERE. Ponieważ klauzula WHERE jest opcjonalna, można łatwo usunąć wszystkie wiersze z tabeli, pomijając klauzulę WHERE, ponieważ klauzula WHERE ogranicza zakres operacji DELETE.

Poniższa instrukcja DELETE usunęłaby dane EDWIN z tabeli EMP.

DELETE employees
WHERE UPPER (ENAME) = 'EDWIN'

1 row deleted.

Uwaga: USUŃ [NAZWA TABELI] i USUŃ Z [NAZWA TABELI] mają to samo znaczenie.

Warunek WHERE w warunkowych instrukcjach usuwania może korzystać z podzapytania, jak pokazano poniżej.

DELETE FROM employees
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
				    FROM LOCATIONS
				    WHERE LOCATION_CODE = 'SFO')

ŚCIĘTY

Truncate to polecenie DDL, które służy do usuwania wszystkich rekordów z tabeli, ale zachowuje strukturę tabeli. Nie obsługuje warunku WHERE do usuwania wybranych rekordów.

Składnia:

TRUNCATE [table name]

Jest to Auto Commit, czyli zatwierdza bieżącą aktywną transakcję w sesji. Obcięcie tabeli nie powoduje usunięcia indeksów zależnych, wyzwalaczy ani ograniczeń tabeli. Jeśli tabela A jest rodzicem ograniczenia odwołania tabeli B w bazie danych, tabeli A nie można obciąć.

Transakcja

Transakcja to logiczna jednostka pracy wykonywanej w bazie danych. Może zawierać -

• Wiele poleceń DML kończących się poleceniem TCL, np. COMMIT lub ROLLBACK

• Jedno polecenie DDL

• Jedno polecenie DCL

Początek transakcji jest oznaczany pierwszym poleceniem DML. Kończy się poleceniem TCL, DDL lub DCL. Polecenie TCL, tj. COMMIT lub ROLLBACK, powoduje jawne zakończenie aktywnej transakcji. Ze względu na ich podstawowe zachowanie, jeśli którekolwiek z poleceń DDL lub DCL zostanie wykonane w sesji bazy danych, zatwierdź trwającą aktywną transakcję w sesji. Jeśli instancja bazy danych ulegnie nienormalnej awarii, transakcja jest zatrzymywana.

COMMIT, ROLLBACK i SAVEPOINT są językami kontroli transakcji. COMMIT wprowadza zmiany danych na stałe do bazy danych, podczas gdy ROLLBACK wykonuje operację zapobiegającą zatwierdzeniu. SAVEPOINT kontroluje serię transakcji, ustawiając znaczniki na różnych etapach transakcji. Użytkownik może cofnąć bieżącą transakcję do żądanego punktu zapisu, który został ustawiony wcześniej.

COMMIT- Zatwierdzenie kończy bieżącą aktywną transakcję poprzez trwałe zastosowanie zmian danych w tabelach bazy danych. COMMIT to polecenie TCL, które jawnie kończy transakcję. Jednak komenda DDL i DCL niejawnie zatwierdza transakcję.

SAVEPOINT- Punkt zapisu służy do oznaczenia określonego punktu w bieżącej transakcji w sesji. Ponieważ jest to logiczny znacznik transakcji, w słownikach danych nie można sprawdzać punktów zapisu.

ROLLBACK- Polecenie ROLLBACK służy do zakończenia całej transakcji poprzez odrzucenie zmian danych. Jeśli transakcja zawiera zaznaczone punkty zapisu, funkcja ROLLBACK TO SAVEPOINT [nazwa] może służyć do wycofania transakcji tylko do określonego punktu zapisu. W rezultacie wszystkie zmiany danych do określonego punktu zapisu zostaną odrzucone.

Demonstracja

Rozważ tabelę PRACOWNICY, która jest zapełniana szczegółami nowo zatrudnionych pracowników w pierwszym kwartale każdego roku. Personel biurowy dołącza do każdego szczegółu pracownika punkt zapisu, aby w dowolnym momencie podczas operacji dostarczania danych przywrócić wszelkie błędne dane. Zwróć uwagę, że zachowuje on nazwy punktów zapisu takie same jak nazwiska pracowników.

INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (105, 'Allen',TO_DATE ('15-JAN-2013','SALES',10000,10);

SAVEPOINT Allen;

INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (106, 'Kate',TO_DATE ('15-JAN-2013','PROD',10000,20);

SAVEPOINT Kate;

INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (107, 'McMan',TO_DATE ('15-JAN-2013','ADMIN',12000,30);

SAVEPOINT McMan;

Załóżmy, że operator przekazujący dane zdaje sobie sprawę, że błędnie wprowadził wynagrodzenie „Kate” i „McMan”. Cofa aktywną transakcję do punktu zapisu Kate i ponownie wprowadza dane pracownika Kate i McMan.

ROLLBACK TO SAVEPOINT Kate;

INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (106, 'Kate',TO_DATE ('15-JAN-2013','PROD',12500,20);

SAVEPOINT Kate;

INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (107, 'McMan',TO_DATE ('15-JAN-2013','ADMIN',13200,30);

SAVEPOINT McMan;

Gdy zakończy wprowadzanie danych, może zatwierdzić całą transakcję wydając polecenie COMMIT w bieżącej sesji.

Przeczytaj spójność

Oracle zachowuje spójność między użytkownikami w każdej sesji w zakresie dostępu do danych i operacji odczytu / zapisu.

Gdy DML występuje w tabeli, oryginalne wartości danych zmienione przez akcję są rejestrowane w bazie danych cofania rekordów. Dopóki transakcja nie zostanie zatwierdzona w bazie danych, każdy użytkownik w innej sesji, który później pyta o zmodyfikowane dane, przegląda oryginalne wartości danych. Oracle wykorzystuje bieżące informacje z globalnego obszaru systemu oraz informacje z rekordów cofania, aby stworzyć spójny do odczytu widok danych tabeli dla zapytania. Dopiero po zatwierdzeniu transakcji zmiany w transakcji są trwałe. Transakcja jest kluczem do strategii Oracle w zakresie zapewniania spójności odczytu.

Punkt początkowy dla widoków zgodnych z odczytem jest generowany w imieniu czytelników

Kontroluje, kiedy zmodyfikowane dane mogą być widoczne przez inne transakcje bazy danych do odczytu lub aktualizacji