poniedziałek, 8 listopada 2010

Kurs Oracle SQL. Widoki

Widoki

Jeśli często wykonujemy jakiej zapytanie do bazy danych, np. z użyciem wielu tabel lub po prostu długie, możemy zdefiniować widok. Korzystanie z niego będzie o wiele wygodniejsze. Możemy pobierać z niego dane jak ze zwykłej tabeli. Nagle długie zapytanie z wieloma parametrami do którego często trzeba coś dodawać możemy zamienić w mniej więcej coś takiego:

SELECT * FROM NAZWA_WIDOKU

Widoki są strukturami całkowicie dynamicznymi, tzn. zapytanie, które je definiuje w momencie definicji jest tylko sprawdzane pod względem poprawności składniowej i semantycznej, natomiast nie jest wykonywane. Zapytanie to jest wykonywane w momencie odwoływania się do widoku.
Widoki mają też zastosowanie w przypadku nadawania uprawnień. Możemy chcieć udostępnić użytkownikowi tylko część danych, lub część kolumn z tabeli. W takim wypadku tworzymy widok o zadanych właściwościach, a użytkownikowi zezwalamy na dostęp do widoku a nie tabeli.

 

 

Tworzenie widoków

 

CREATE [OR REPLACE] [FORCE] VIEW NAZWA_WIDOKU AS
TREŚĆ_ZAPYTANIA [ WITH READ ONLY] [WITH CHECK OPTION]

Aby stworzyć widok, należy mieć uprawnienia do wszystkich obiektów do których odnosi się widok.

OR REPLACE

Dzięki tej klauzuli w przypadku gdyby widok o takiej nazwie już istniał zostanie nadpisany przez właśnie tworzony.

FORCE

Wymusza stworzenie widoku nawet jeśli zapytanie będące podstawą widoku jest niepoprawne (np. odnosi się do tabeli która jeszcze nie istnieje).

WITH READ ONLY

W niektórych przypadkach można stosować instrukcje DML na widokach, ta instrukcja nawet jeśli formalnie byłoby to możliwe, uniemożliwia to.

WITH CHECK OPTION

Ta opcja sprawia, że w przypadku (jeśli to możliwe dla danego widoku) nie można wrzucić danych albo zmienić ich w taki sposób że nie będą widoczne w tym widoku.


Wykonywanie operacji DML na widokach

 

W przypadku zapytań, widoki funkcjonują tak jak tabele. W zależności od operacji DML widoki dotyczą różne właściwości. W żadnym z poniższych wypadków nie możemy wykonać operacji jeśli widok został stworzony z kauzulą WITH READ ONLY.


Update

  • Widok nie może być oparty na wielu tabelach
  • Nie może zawierać klauzuli DISTINCT
  • Nie może zawierać GROUP BY ani funkcji grupowych
  • Nie może zawierać skorelowanych zapytań
  • Wyrażeń w kolumnie

Delete

  • Widok nie może być oparty na wielu tabelach
  • Nie może zawierać klauzuli DISTINCT
  • Nie może zawierać GROUP BY ani funkcji grupowych
  • Nie może zawierać skorelowanych zapytań

    Insert

    • Widok nie może być oparty na wielu tabelach
    • Nie może zawierać klauzuli DISTINCT
    • Nie może zawierać GROUP BY ani funkcji grupowych
    • Nie może zawierać skorelowanych zapytań
    • Wyrażeń w kolumnie
    • Tabela, na której oparty jest widok zawiera kolumnę NOT NULL bez wartości domyślnej, i kolumna ta nie jest odwzorowana w widoku.
    • Jeżeli widok zostanie utworzony z klauzulą WITH CHECK OPTION, to nie będzie można wstawić do niego żadnego wiersza, który nie byłby później widoczny w tym widoku.

    Usuwanie widoków


    DROP VIEW NAZWA_WIDOKU;




    Ćwiczenia

    1. Stwórz widok który umożliwi wyświetlanie pracowników z departamentu o podanej nazwie.
    2. Stwórz widok który umożliwi dodawanie nowego wiersza do tabeli regions



    Ten temat omawiam na poniższych szkoleniach:
    • Podstawy Oracle SQL
    • Podstawy SQL i PL/SQL
    Możesz w nich uczestniczyć, a jako czytelnik tego bloga otrzymasz 10% zniżki - poinformuj o tym fakcie konsultanta.

    4 komentarze:

    1. witam, mam następujące pytanie. Stworzony został widok recepcja, a także użytkownik recepcjonistka. Nadane zostały uprawnienia insert dla recepcjonistka do widoku. Problem w tym, że w momencie zalogowania z HR do recepcjonistka, widok nie jest widoczny. Czym może być to spowodowane? Z góry dziękuję za pomoc. Pozdrawiam, gość.

      OdpowiedzUsuń
    2. create view "pracownicy_90" AS
      select last_name as robol from employees where department_id=90

      wpisuję powyższą instrukcję i otrzymuję błąd
      00955 - name is already used by an existing object

      jak mam zmienić nazwę obiektu?

      OdpowiedzUsuń
      Odpowiedzi
      1. wpisz 'create or replace' zamiast samego 'create'

        Usuń
    3. Nie wiem czy dobrze ale może ktoś poprawi...

      Ad.1
      create view pracownicy_depart_Fin as
      select last_name from employees join departments using (department_id)
      where department_name like ('Finance');

      Ad.2
      create view region as select region_id, region_name from regions;
      insert into region values (5, 'Polska');

      OdpowiedzUsuń