programmera.net -> oracle -> normal     för utskrift      info@programmera.net

Vyer i Oracle

1. Vyer
2. CREATE VIEW
3. CREATE VIEW- exempel
4. DML och CHECK OPTION
5. DML och JOIN
6. ALTER VIEW
7. DROP VIEW
8. USER_VIEWS

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)