Tworzenie innych obiektów schematu

Oprócz tabel, innymi istotnymi obiektami schematu są widoki, sekwencje, indeksy i synonimy. Widok jest tabelą logiczną lub wirtualną. Synonimy to po prostu aliasy obiektów bazy danych. Synonimy również upraszczają pisanie zapytań i zapewniają element bezpieczeństwa systemu, ukrywając rzeczywistą nazwę obiektu bazy danych. Sekwencje to specjalne obiekty bazy danych, które obsługują automatyczne generowanie wartości całkowitych i są często używane do generowanie wartości klucza podstawowego dla tabel. Indeksy są tworzone w kolumnach tabeli, aby ułatwić szybkie pobieranie informacji z tabel.

Wyświetlenia

Widok bazy danych jest logiczną lub wirtualną tabelą opartą na zapytaniu, a widoki są odpytywane tak jak tabele, co oznacza, że ​​z perspektywy programisty lub użytkownika systemu bazodanowego widok wygląda jak tabela. obiekt jest przechowywany w słowniku danych bazy danych; jednak widok sam nie przechowuje danych; baza danych przechowuje również plan wykonania do tworzenia widoku - oznacza to, że dane można szybko pobrać za pomocą widoku, nawet jeśli rzeczywiste dane przedstawione przez zapytanie SELECT widoku nie są przechowywane jako część widoku. Dane są raczej „zbierane razem” za każdym razem, gdy przeglądany jest widok z tabel bazy danych, dla których jest zdefiniowany widok - nazywane są one tabelami podstawowymi.

Ogólna składnia jest podana poniżej.

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW [ViewName]
[(Column Alias Name...)] 
AS [Query]
[WITH [CHECK OPTION] [READ ONLY] [CONSTRAINT]];

Ze składni

Opcja FORCE umożliwia utworzenie widoku, nawet jeśli tabela bazowa, do której odwołuje się widok, jeszcze nie istnieje. Ta opcja służy do tworzenia widoku przed faktycznym utworzeniem tabel podstawowych i towarzyszących danych.

Opcja NOFORCE jest przeciwieństwem FORCE i umożliwia użytkownikowi systemu utworzenie widoku, jeśli ma wymagane uprawnienia do tworzenia widoku i jeśli tabele, z których utworzono widok, już istnieją. Jest to opcja domyślna.

Opcja TYLKO DO ODCZYTU umożliwia utworzenie widoku, który jest tylko do odczytu. Nie można używać poleceń DELETE, INSERT ani UPDATE do modyfikowania danych w widoku tylko do odczytu.

Klauzula WITH CHECK OPTION umożliwia aktualizację wierszy, które można wybrać za pośrednictwem widoku, a także określenie ograniczeń wartości. Klauzula CONSTRAINT działa w połączeniu z klauzulą ​​WITH CHECK OPTION, aby umożliwić administratorowi bazy danych przypisanie unikalnej nazwy Jeśli administrator bazy danych pominie klauzulę CONSTRAINT, Oracle automatycznie przypisze ograniczeniu wygenerowaną przez system nazwę, która nie będzie miała zbyt dużego znaczenia.

Rodzaje widoków

Widok prosty jest tworzony tylko na jednej tabeli, jest to proste zapytanie SELECT bez funkcji lub klauzuli grupowej, ale po prostu wybór kolumn z tabeli bez żadnej transformacji. Jeśli na widoku jest wykonywany DML, jest to natychmiast odzwierciedlane w tabeli bazowej.

Widok złożony jest tworzony na wielu tabelach za pomocą złączeń, może zawierać funkcje SQL, grupuj według funkcji, ale ponieważ widok dotyczy wielu danych i wybór kolumn również nie jest prosty, nie pozwala na działanie na nim DML

Ilustracja

Simple View: W poniższym prostym widoku wybierz nazwisko pracownika, identyfikator działu i wynagrodzenie dla pracowników z ID PRACY jako DEV.

CREATE OR REPLACE VIEW v_emp_dev
AS
SELECT first_name, department_id, salary
FROM employees
WHERE job_id = 'DEV';

Complex view: Poniższy przykład pokazuje nazwę działu, średnią pensję wylosowaną w dziale oraz liczbę zatrudnionych w nim pracowników.

CREATE OR REPLACE VIEW EMP_VU
AS
SELECT department_name, AVG (salary) avg_sal, COUNT (first_name) count
FROM employees E, departments D
WHERE E.department_id = D.department_id
GROUP BY department_name;

DESCRIBE [view name]opisuje strukturę widoku. Kolumny są wyświetlane w tej samej kolejności, jak w definicji widoku.

Operacje DML na widoku

Operacje DML można łatwo wykonywać na prostych widokach. Jak wspomniano wcześniej, operacje wstawiania, aktualizowania i usuwania faktycznie odbywają się na tabeli bazowej.

Wykonując instrukcję UPDATE, DELETE lub INSERT DML w widoku, w rzeczywistości manipulujesz wierszami danych dla tabeli podstawowej lub tabel, w których widok jest zdefiniowany. Istnieją ograniczenia dotyczące używania instrukcji UPDATE, DELETE i INSERT Po pierwsze, aby użyć instrukcji UPDATE, DELETE lub INSERT z widokiem, widok musi być aktualizowany. widok jest aktualizowalny, jeśli klauzula SELECT nie określa żadnej funkcji agregującej na liście SELECT. Ponadto widok nie może zostały utworzone przy użyciu klauzuli lub klauzul GROUP BY, DISTINCT lub UNION. Dopuszczalne jest, aby funkcje agregujące były używane w podzapytaniu SELECT w klauzuli FROM. Ponadto widok nie może mieć żadnych kolumn pochodnych na liście SELECT. Następnie, jeśli widok jest tworzony w wyniku operacji JOIN (widok łączenia), instrukcje UPDATE i INSERT mogą jednocześnie modyfikować lub wstawiać wiersze do jednej z tabel podstawowych. Nie można modyfikować wierszy z dwóch lub więcej tabel za pomocą pojedynczej instrukcji języka manipulacji danymi (DML). Wreszcie instrukcja DELETE może być wykonywana tylko w odniesieniu do widoku, jeśli odwołanie do tabeli jest zawarte w klauzuli FROM. Oznacza to po prostu, że nie można usunąć wierszy z tabeli, która nie została określona.

Klauzula WITH CHECK OPTION

WITH CHECK OPTION to opcjonalna klauzula, która określa poziom sprawdzania, jakie należy wykonać podczas wstawiania lub aktualizowania danych za pośrednictwem widoku.Jeśli widok jest tworzony za pomocą klauzuli WITH CHECK OPTION, każdy wiersz, który jest wstawiany lub aktualizowany w tabeli bazowej za pośrednictwem widoku musi być zgodny z definicją widoku. Zwróć uwagę, że opcji nie można określić, jeśli widok jest tworzony jako tylko do odczytu.

Na przykład widok V_EMP_DEV jest tworzony dla pracowników, którzy są programistami (JOB_ID = DEV).

CREATE OR REPLACE VIEW v_emp_dev
AS
SELECT first_name, department_id, salary, 
FROM employees
WHERE job_id = 'DEV'
WITH CHECK OPTION empvu_dev;

Użytkownik próbuje zaktualizować wynagrodzenie pracownika HR za pośrednictwem widoku, ale napotyka wyjątek. Dzieje się tak, ponieważ widok został utworzony z opcją wyboru.

UPDATE v_emp_dev
SET salary = salary+500
WHERE JOB_ID = 'HR';
ORA-01402: view WITH CHECK OPTION where-clause violation

Gdyby był to prosty widok, instrukcja UPDATE nie wywołałaby żadnego wyjątku.

Upuszczenie widoku

Administrator bazy danych (DBA) lub właściciel widoku może usunąć widok za pomocą instrukcji DROP VIEW. Jeśli widok ma zdefiniowane ograniczenia, należy określić klauzulę CASCADE CONSTRAINTS podczas usuwania widoku; w przeciwnym razie instrukcja DROP VIEW nie zostanie przetworzona. Jeśli inny widok lub inny obiekt bazy danych, taki jak synonim lub widok zmaterializowany (oba te obiekty zostaną omówione w dalszej części tego rozdziału) odwołuje się do widoku porzuconego, Oracle nie usuwa tych obiektów bazy danych; Oracle oznacza je raczej jako nieważne. Możesz usunąć te niepoprawne obiekty lub przedefiniować je w celu przywrócenia ich ważności.

Poniższe polecenie DROP VIEW usuwa widok EMP_VU z bazy danych.

DROP VIEW EMP_VU;

Sekwencje

Oracle zapewnia możliwość generowania sekwencji unikatowych liczb dla tego typu zastosowań i nazywane są sekwencjami. Ogólnie sekwencje są używane do generowania unikalnych, sekwencyjnych wartości całkowitych, które są używane jako wartości klucza podstawowego w tabelach bazy danych. być generowane w kolejności rosnącej lub malejącej. Należy pamiętać, że liczby raz wygenerowanej przez sekwencję nie można cofnąć.

Składnia

CREATE SEQUENCE <sequence name>
[INCREMENT BY < number >]
[START WITH < start value number>]
[MAXVALUE < MAXIMUM VLAUE NUMBER>]
[NOMAXVALUE]
[MINVALUE < minimum value number>]
[CYCLE | NOCYCLE]
[CACHE < number of sequence value to cache> | NOCACHE]
[ORDER | NOORDER];

Ze składni

Instrukcja CREATE SEQUENCE musi określać unikalną nazwę sekwencji. To jedyna wymagana klauzula w oświadczeniu. Jeśli nie określisz żadnej z pozostałych klauzul, wszystkie wygenerowane numery sekwencyjne będą zgodne z domyślnymi ustawieniami Oracle.

Klauzula INCREMENT BY określa, jak sekwencja rośnie wraz z generowaniem każdej liczby. Domyślny przyrost to jeden; jeśli jednak masz dobry powód, aby sekwencja pomijała liczby, możesz określić inny przyrost. dodatni przyrost liczbowy generuje rosnące numery sekwencyjne z interwałem równym wybranemu interwałowi. ujemny przyrost liczbowy generuje malejące numery sekwencyjne.

Klauzula START WITH określa początkową wartość liczbową sekwencji - domyślny numer początkowy to jeden. Dodatkowo musisz określić wartość początkową, jeśli masz już kilka wierszy z danymi w kolumnie, w której będą teraz przechowywane wartości sekwencji.

Klauzula MAXVALUE określa maksymalną wartość, do której można zwiększyć sekwencję. W przypadku braku MAXVALUE, maksymalna dopuszczalna wartość, jaką można wygenerować dla sekwencji, jest dość duża, od 10 do 27 potęgi - 1. Wartość domyślna to NOMAXVALUE.

Klauzula MINVALUE określa minimalną wartość sekwencji dla sekwencji malejącej (takiej, która generuje liczby w porządku malejącym). Wartość domyślna to NOMINVALUE.

Klauzula CYCLE określa, że ​​wartości sekwencji mogą być ponownie użyte, jeśli sekwencja osiągnie określoną wartość MAXVALUE. Jeśli sekwencja jest cykliczna, liczby są generowane od nowa od wartości START Z.

Klauzula CACHE może poprawić wydajność systemu, umożliwiając Oracle generowanie określonej partii sekwencjonowanych numerów do przechowywania w pamięci podręcznej.

Jeśli określisz CACHE bez określania liczby, domyślny rozmiar pamięci podręcznej to 20 numerów sekwencyjnych.Opcjonalnie można określić NOCACHE, aby zapobiec buforowaniu numerów sekwencyjnych.

Klauzula ORDER określa, że ​​numery sekwencyjne są przydzielane w dokładnej kolejności chronologicznej, w jakiej są żądane.

NEXTVAL i CURRVAL

Wartości sekwencji są generowane za pomocą dwóch pseudo kolumn o nazwach currval i nextval. Pseudokolumna zachowuje się jak kolumna tabeli, ale pseudo kolumny nie są w rzeczywistości przechowywane w tabeli. sekwencja jest zwracana Kolejne wybory pseudokolumny nextval powodują przyrost sekwencji zgodnie z klauzulą ​​INCREMENT BY i zwracają nowo wygenerowaną wartość sekwencji. Pseudokolumna currval zwraca bieżącą wartość sekwencji, która jest wartością zwracaną przez ostatnie odniesienie do nextval.

W sesji NEXTVAL, a nie CURRVAL, musi być pierwszym działaniem w sekwencji. Dzieje się tak, ponieważ w sesji, gdy NEXTVAL generuje pierwszy numer sesji z sekwencji, Oracle zachowuje bieżącą wartość w CURRVAL.

Składnia:

Sequence.NEXTVAL
Sequence.CURRVAL

Punkty, na które należy zwrócić uwagę -

• CURRVAL i NEXTVAL mogą być używane tylko w zewnętrznym SQL instrukcji select.

• CURRVAL i NEXTVAL mogą być użyte w instrukcji INSERT do zastąpienia klucza podstawowego kolumny.Można go użyć zarówno jako klauzula podzapytania, jak również w klauzuli VALUES.

• CURRVAL i NEXTVAL mogą służyć do aktualizowania wartości w tabelach.

• CURRVAL i NEXTVAL nie mogą znajdować się na liście wyboru VIEW, ze słowem kluczowym DISTINCT, z klauzulami GROUP BY, HAVING lub ORDER BY oraz wyrażeniem DEFAULT w instrukcji CREATE TABLE lub ALTER TABLE.

Modyfikowanie sekwencji

Właściciel sekwencji może zmodyfikować sekwencję, aby zmienić tylko takie atrybuty, jak klauzule INCREMENT BY value, MINVALUE, MAXVALUE, CYCLE lub CACHE. Zwróć uwagę, że wprowadzone zmiany zostaną odzwierciedlone w nadchodzących liczbach.

Składnia:

ALTER SEQUENCE [sequence name]
INCREMENT BY n
MAXVALUE n
NOCACHE
NOCYCLE

Upuszczam sekwencję

Polecenie DROP SEQUENCE usuwa sekwencje, które muszą zostać odtworzone lub nie są już potrzebne.

DROP SEQUENCE [sequence name]

Indeksy

Indeksy to obiekty bazy danych, które są używane do dostrajania wydajności zapytania SELECT. Istnieją różne typy indeksów, w tym między innymi te używane do wymuszania ograniczeń klucza podstawowego, indeksy unikalne, indeksy nieunikalne i indeksy konkatenowane. zapytania wymagałyby od Oracle przeskanowania wszystkich wierszy w tabeli w celu zwrócenia wymaganych wierszy do tabeli wynikowej W kolumnach tabeli tworzony jest indeks, który następnie przechowuje wszystkie wartości kolumny w segmencie indeksu W przeciwieństwie do sekwencji, indeksy są tabelami specyficzne, są one automatycznie usuwane po usunięciu tabeli.

Indeksy mogą być tworzone automatycznie lub ręcznie. Po określeniu ograniczenia PRIMARY KEY lub UNIQUE, Oracle automatycznie utworzy unikalny indeks do obsługi szybkiego wyszukiwania danych dla określonej tabeli.

Alternatywnie, użytkownik może ręcznie tworzyć indeksy, aby zoptymalizować wydajność zapytań. Ręcznie tworzone indeksy mogą być unikalne lub nieunikalne. Nieunikalne indeksy mogą być indeksami B-Tree, Bitmap lub Function. Domyślnie Oracle tworzy indeksy B-Tree na kolumnach Oto składnia

Składnia

CREATE [UNIQUE][BITMAP]INDEX index
ON table (column [, column]...);

Należy pamiętać, że UNIQUE i BITMAP muszą być określone tylko dla indeksów unikalnych i bitmapowych.Domyślnie Oracle tworzy indeksy B-Tree dla normalnych indeksów.

Indeks złożony (nazywany również indeksem konkatenowanym) to indeks utworzony na wielu kolumnach tabeli. Kolumny w indeksie złożonym mogą pojawiać się w dowolnej kolejności i nie muszą przylegać do kolumn w tabeli. Indeksy złożone zwiększają szybkość pobierania wierszy dla zapytań, w których klauzula WHERE odwołuje się do wszystkich lub wiodących części kolumn w indeksie złożonym. Indeks może zawierać maksymalnie 32 kolumny.

Na przykład użytkownik tworzy indeks IDX_EMP w kolumnie HIRE_DATE tabeli EMPLOYEES. Użycie indeksu zmniejszy liczbę operacji we / wy dysku, przechodząc przez indeksowane skanowanie ścieżki i odnajdując dane filtrowane w kolumnie HIRE_DATE.

CREATE INDEX IDX_EMP ON employees(hire_date);

Upuszczanie indeksu

Indeksów nie można modyfikować, ale można je zmieniać w celu analizy, odbudowy lub obliczania statystyk. Jeśli definicja indeksu ma zostać zmodyfikowana, należy ją usunąć i odtworzyć. Składnia polecenia DROP INDEX jest prosta.

DROP INDEX index_name;

Synonimy

Synonim jest aliasem, czyli formą skrótu używaną w celu uproszczenia zadania odwoływania się do obiektu bazy danych. Koncepcja jest analogiczna do używania pseudonimów dla przyjaciół i znajomych. Odwołanie się do obiektu będącego własnością innego użytkownika wymaga podania nazwy schematu być poprzedzony tym. Z pomocą synonimu zmniejszasz wysiłek związany z odwoływaniem się do obiektu wraz z nazwą schematu. W ten sposób synonim zapewnia przejrzystość lokalizacji, ponieważ nazwa synonimu ukrywa rzeczywistą nazwę obiektu i jego właściciela.

Istnieją dwie kategorie synonimów, publiczne i prywatne. Synonim publiczny umożliwia łatwy dostęp do obiektu wszystkim użytkownikom systemu. W rzeczywistości osoba tworząca publiczny synonim nie jest jego właścicielem - raczej będzie należeć do grupy użytkowników PUBLIC istniejącej w Oracle, natomiast prywatne synonimy należą do użytkownika systemu, który je utworzył i w nim schemat użytkownika.

Składnia

CREATE [PUBLIC] SYNONYM [synonym name] 
FOR OBJECT;

Użytkownik systemu może nadać uprawnienie do używania prywatnych synonimów, które posiada, innym użytkownikom systemu.Aby tworzyć synonimy, musisz mieć uprawnienie CREATE SYNONYM.Ponadto musisz mieć uprawnienie CREATE PUBLIC SYNONYM, aby tworzyć publiczne synonimy Jeśli synonim jest zadeklarowany jako publiczny, nazwa synonimu nie może już być używana jako synonim publiczny. Próba utworzenia publicznego synonimu, który już istnieje, spowoduje niepowodzenie polecenia CREATE PUBLIC SYNONYM, a Oracle zwróci ORA-00955: nazwa jest już używana przez istniejący komunikat o błędzie obiektu.

Ilustracja

Rozważmy dwóch użytkowników U1 i U2.U1 ma dostęp do tabeli PRACOWNICY. Aby więc umożliwić dostęp w tabeli EMPLOYEES również do U2, można utworzyć synonim w schemacie U2. Dostęp musi być udzielony przez U1 do U2.

CONN U2/U2
SQL> CREATE SYNONYM EMP_SYN	FOR U1.employees;

CONN U1/U1
SQL> GRANT ALL ON EMP_SYN TO U2;

CONN U2/U2
SQL> SELECT * FROM EMP_SYN;

Porzucenie synonimu

Użytkownik może porzucić synonim, którego jest właścicielem. Aby usunąć publiczny synonim, musisz mieć uprawnienie DROP PUBLIC SYNONYM.

DROP SYNONYM EMP_SYN;