SQL Basis - Hoofdstuk 18

01 - Wat zijn views

Views zijn virtuele tabellen
De virtuele tabellen bestaan niet in de database maar zijn het resultaat van een query
Views zijn 'opgeslagen' queries die bij het aanroepen pas uitgevoerd worden
Een view bevat geen gegevens, enkel een query

CREATE VIEW <NieuweTabelnaam> AS
SELECT      <kolom1>,
            <kolom2>
FROM        <tabel1> INNER JOIN <tabel2> ON <tabel1.waarde1> = <tabel2.waarde2>

Het maken van een View

Views worden gebruikt om:
- queries te hergebruiken - (gemak)
- complexe SQL opdrachten eenvoudiger te maken - (eenvoud)
- delen van een tabel beschikbaar te maken i.p.v. de gehele tabel (snelheid)
- gebruikers met minder bevoegdheden te laten werken met een deel van de data uit een tabel - (veiligheid)
- het formateren / opmaken van gegevens door bijvoorbeeld kolommen alvast samen te voegen - (presentatie)

Aandachtspunten voor het werken met views:
- views moeten een unieke naam hebben (net als tabellen)
- er is geen limiet aan het aantal views
- om een view te maken heb je voldoende rechten nodig
- views kunnen genest worden (resultaat van een view gebruiken in een andere view)
- ORDER BY wordt niet gebruikt in views (conflicteerd met LIMIT = 5 of TOP 5)
- views kunnen niet geindexeerd worden, triggers of standaard waarden bevatten
- bij sommige DBMS-en is het nodig dat elke kolom in een view een eigen naam krijgt (alias)
- bij sommige DBMS-en (SQLite) werkt een view alleen in 'Read-Only' modus
- Bij sommige DBMS-en is het niet toegestaan om rijen toe te voegen of te updaten waarbij het resultaat onstaat dat de nieuwe rij geen onderdeel meer is van het resultaatvan de view

02 - Views maken

In plaats van telkens onderstaande query uit te voeren

SELECT cust_name,
       cust_contact,
       prod_id
FROM   Customers INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
                 INNER JOIN OrderItems ON Orders.order_num = OrderItems.order_num;

Kan het maken van een view handig zijn

CREATE VIEW ProductCustomers AS
SELECT      cust_name,
            cust_contact,
            prod_id
FROM        Customers INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
                      INNER JOIN OrderItems ON Orders.order_num = OrderItems.order_num;

Wanneer het resultaat van de 1ste query nodig is, kan na het maken van de view de informatie eenvoudiger opgehaald worden

SELECT *
FROM   ProductCustomers;

cust_name	cust_contact	prod_id
Village Toys	John Smith	BR01
Village Toys	John Smith	BR03
Village Toys	John Smith	BNBG01
Village Toys	John Smith	BNBG02
Village Toys	John Smith	BNBG03
Fun4All	Jim Jones	BR01
Fun4All	Jim Jones	BR02
Fun4All	Jim Jones	BR03
Fun4All	Denise L. Stephens	BR03
Fun4All	Denise L. Stephens	BNBG01
Fun4All	Denise L. Stephens	BNBG02
Fun4All	Denise L. Stephens	BNBG03
Fun4All	Denise L. Stephens	RGAN01
The Toy Store	Kim Howard	RGAN01
The Toy Store	Kim Howard	BR03
The Toy Store	Kim Howard	BNBG01
The Toy Store	Kim Howard	BNBG02
The Toy Store	Kim Howard	BNBG03

Probeer een view algemeen te houden, dan kan hij vaker gebruikt worden

03 - Views om data te formateren

Als er vaak samengestelde kolommen nodig zijn:

-- Oracle / PostgreSQL
SELECT   RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')' AS vend_title
FROM     Vendors
ORDER BY vend_name;

-- MySQL
SELECT   CONCAT(vend_name, ' (', vend_country, ')') AS vend_title
FROM     Vendors
ORDER BY vend_name;

-- SQL Server
SELECT   RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' AS vend_title
FROM     Vendors
ORDER BY vend_name;

vend_title

Bear Emporium (usa)

Bears R Us (usa)

Doll House Inc. (usa)

Fun and Games (England)

Furball Inc. (usa)

Jouets et ours (France)

-- Oracle / PostgreSQL
CREATE VIEW VendorLocations AS
SELECT      RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')' AS vend_title
FROM        Vendors;

-- MySQL
CREATE VIEW VendorLocations AS
SELECT      CONCAT(vend_name, ' (', vend_country, ')') AS vend_title
FROM        Vendors;

-- SQL Server
CREATE VIEW VendorLocations AS
SELECT      RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' AS vend_title
FROM        Vendors;

Vergeet niet dat de ORDER BY niet in de view zit

SELECT   *
FROM     VendorLocations
ORDER BY vend_title;