1. Vyer

Vyer andvänds för att göra det lättare att titta på data. När en vy är skapad kan man använda den i SELECT-satser som om det vore en tabell. Man kan även utföra vissa DML-operationer på vyn.

2. CREATE VIEW

En vy skapas med en SELECT-sats i grunden. CREATE VIEW har följande syntax:

CREATE [OR REPLACE] [[NO]FORCE] VIEW vynamn [({ alias [restriktion] [,alias [restriktion]] }|{ restriktion [,restriktion] })] AS SELECT ... FROM ... [WHERE osv..] [WITH {[READ ONLY] {[CHECK OPTION] [CONSTRAINT restriktionsnamn]}}];

Syntaxen kan förklaras på detta sätt:

• FORCE: Får Oracle att skapa vyn även om den underliggande tabellen inte finns.
• READ ONLY: Innebär att man inte kan utföra DML-kommandon på vyn.
• CHECK OPTION: Innebär att ett INSERT och UPDATE (ej DELETE) måste matcha restriktionerna för vyn.
• CONSTRAINT: Namnger restriktionen.

3. CREATE VIEW- exempel

I detta exmpel skapar vi en vy som visar en sammanfattning av tabellen EMP men tar bara med de som har en månadslön över 22000:

SQL> CREATE VIEW my_emp ( avdelning, namn, lon ) AS SELECT d.dep_name, e.first_name || ' ' || e.last_name, e.salary FROM emp e JOIN dep d USING (dep_pk) WHERE e.salary > 22000; Vyn är skapad.

För att titta på denna vy använder vi den som om det vore en tabell:

SQL> SELECT * FROM my_emp; AVDELNING NAMN LON -------------------- ------------------------ ---------- Ledningsgruppen Janne Svensson 50000 Java-utveckling Per Johansson 23000 Java-utveckling Magnus Magnusson 32000

Här ser vi att Olle (med en lön på 21000) inte listas i resultatet. Vi skapar en vy till med CHECK OPTION bara för att vi ska kunna jämföra senare:

SQL> CREATE VIEW my_emp_chk ( avdelning, namn, lon ) AS SELECT d.dep_name, e.first_name || ' ' || e.last_name, e.salary FROM emp e JOIN dep d USING (dep_pk) WHERE e.salary > 22000 WITH CHECK OPTION;

4. DML och CHECK OPTION

Om en vy inte är skapad READ ONLY kan man alltså utföra DML-kommandon på vyn. Detta är i allmänhet lite krångligt. Till exempel, om vi vill ändra lön på Per Johansson går det bra (nu använder vi vyn med CHECK OPTION):

SQL> UPDATE my_emp_chk SET lon=24000 WHERE namn='Per Johansson'; 1 rad är uppdaterad. SQL> SELECT * FROM my_emp; AVDELNING NAMN LON -------------------- ------------------------ ---------- Ledningsgruppen Janne Svensson 50000 Java-utveckling Per Johansson 24000 Java-utveckling Magnus Magnusson 32000

Däremot, om vi försöker sänka någons lön under 22000 (så att den personen inte längre syns i vyn) händer följande:

SQL> UPDATE my_emp_chk SET lon=20000 WHERE namn='Per Johansson'; UPDATE my_emp * Fel på rad 1: ORA-01402: vyn med alternativet CHECK är oförenlig med where-klausulen

Här fick vi en protest. CHECK OPTION såg att om vi sänker lönen på Per kommer han inte längre att synas. Hur går om vi försöker med vyn MY_EMP som inte har något CHECK OPTION?

SQL> UPDATE my_emp SET lon=20000 WHERE namn='Per Johansson'; 1 rad är uppdaterad. SQL> SELECT * FROM my_emp; AVDELNING NAMN LON -------------------- ------------------------ ---------- Ledningsgruppen Janne Svensson 50000 Java-utveckling Magnus Magnusson 32000

Här gick det tydligen bra. DELETE påverkas inte av CHECK OPTION, det är fullt möjligt att ta bort en rad som är synlig i vyn:

SQL> DELETE FROM my_emp_chk WHERE namn='Janne Svensson'; 1 rad är borttagen. SQL> SELECT * FROM my_emp_chk; AVDELNING NAMN LON -------------------- ------------------------ ---------- Java-utveckling Magnus Magnusson 32000

5. DML och JOIN

Om man har en JOIN i den SELECT-satsen som bygger vyn går det inte att ändra de kolumner som inte kommer från huvudtabellen (den tabell som har referensnycklarna i sig). Till exempel kan vi inte ändra namnet på avdelningen för någon anställd, eftersom det namnet hämtas från tabellen DEP:

SQL> UPDATE my_emp SET avdelning='Handläggare' WHERE namn='Janne Svensson'; SET avdelning='Handläggare' * Fel på rad 2: ORA-01779: kan bara ändra i en vy som baseras på en tabell med nyckel

Vad det gäller INSERT kan man bara specifiera värden som direkt motsvarar värden i den underliggande tabellen:

SQL> INSERT INTO my_emp (lon) VALUES(44000); 1 rad är skapad.

Om vi försöker sätta ett värde på AVDELNING kommer vi att misslyckas:

SQL> INSERT INTO my_emp (lon, avdelning) VALUES(44000, 'Handläggare'); INSERT INTO my_emp (lon, avdelning) VALUES(44000, 'Handläggare') * Fel på rad 1: ORA-01776: kan inte ändra fler en bastabell via en vy med kopplingar

6. ALTER VIEW

Syntaxen för att kompilera om en vy med ALTER VIEW är:

ALTER VIEW vynamn COMPILE;

Övrig syntax för ALTER VIEW är:

ALTER VIEW vynamn { ADD restriktion | MODIFY CONSTRAINT restriktionsnamn [NO]RELY | DROP { CONSTRAINT restriktionsnamn | PRIMARY KEY | UNIQUE(kolumn [,kolumn]) } };

Man kan alltså egentligen bara kontrollera restriktioner med ALTER VIEW.

7. DROP VIEW

Man tar bort en vy med DROP VIEW:

DROP VIEW vynamn [CASCADE CONSTRAINTS];

CASCADE CONSTRAINTS kommer ta bort alla referensrestriktioner (FOREIGN KEYS) som är inbyggda i vyn.

SQL> DROP VIEW my_emp; Vyn är borttagen.

8. USER_VIEWS

Med tabellen USER_VIEWS kan man se sina vyer:

SQL> SELECT view_name FROM user_views; VIEW_NAME ------------------------------ MY_EMP MY_EMP_CHK

Alla kolumner:

SQL> DESC user_views; Namn Null? Typ ----------------------------------------- -------- ---------------------------- VIEW_NAME NOT NULL VARCHAR2(30) TEXT_LENGTH NUMBER TEXT LONG TYPE_TEXT_LENGTH NUMBER TYPE_TEXT VARCHAR2(4000) OID_TEXT_LENGTH NUMBER OID_TEXT VARCHAR2(4000) VIEW_TYPE_OWNER VARCHAR2(30) VIEW_TYPE VARCHAR2(30) SUPERVIEW_NAME VARCHAR2(30)