Kapitel 5.1 - Views und Stored Procedures

Kapitel 5.1
Christian
Inauen
Views
Stored
Procedures
Kapitel 5.1
Views und Stored Procedures
Christian Inauen
01.10.2015
Kapitel 5.1
Christian
Inauen
Views
Stored
Procedures
Views
Kapitel 5.1
Idee
Christian
Inauen
Views
Stored
Procedures
Views entstanden aus dem Bedürfnis folgendes tun zu können:
• Sensitive Daten ausblenden zu können (filtern)
• Komplizierte Abfragen zusammenfassen zu können
(aggregieren)
• Tatsächliche Struktur zu verstecken (kapseln)
Kapitel 5.1
Syntax : Create Views
Christian
Inauen
Views
Kompliziert:
Stored
Procedures
CREATE
[ OR REPLACE ]
[ ALGORITHM = { UNDEFINED | MERGE | TEMPTABLE }]
[ DEFINER = { user | CURRENT_USER }]
[ SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [( column_list )]
AS s e l e c t _ s t a t e me n t
[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
Einfacher:
CREATE VIEW name AS SELECT * FROM tablename ;
Kapitel 5.1
Christian
Inauen
Syntax : Create Views 2
Views
Stored
Procedures
Problem: View existiert bereits? Kein Problem:
CREATE OR REPLACE VIEW name AS SELECT * FROM tablename
Problem: Sie wollen andere Attribute-Namen? Kein Problem:
CREATE VIEW name AS SELECT vorname AS vornameNeu ,
nachname AS nachnameNeu
FROM tablename ;
Kapitel 5.1
Christian
Inauen
Views
Stored
Procedures
Stored Procedures
Kapitel 5.1
Idee
Christian
Inauen
Views
Stored
Procedures
Was ist es : Serverseitig gespeicherte SQL-Anweisungen
Anwendung :
•
•
•
•
Kapselung (Tabellen werden hinter SP versteckt)
Redundanzen können besser gepflegt werden
Security (Schnittstellen zur Applikation sind definiert)
Können in der Praxis schneller sein
Kapitel 5.1
Christian
Inauen
Views
Stored
Procedures
Syntax : Create Stored Procedures
CREATE
[ DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([ proc_parameter [ ,...]])
[ characteristic ...] routine_body
proc_parameter :
[ IN | OUT | INOUT ] param_name type
characteristic :
COMMENT ’ string ’
| LANGUAGE SQL
| [ NOT ] DETERMINISTIC
| { CONTAINS SQL | NO SQL |
READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
Kapitel 5.1
Syntax : Interpretation
Christian
Inauen
Views
Stored
Procedures
proc parameter : Pro Parameter / Eingabe- oder
Ausgabewert (oder beides)
characteristic :
• Deterministisch oder nicht (zum Beispiel : Alter
vs. Geburtsdatum)
• In welcher Form liegt die SP vor
• Mit welchen Rechten wird die Prozedur ausgeführt
(Definer oder Invoker)
Kapitel 5.1
Christian
Inauen
Syntax : Beispiel 1
Views
Stored
Procedures
mysql > DELIMITER //
mysql > CREATE PROCEDURE GetAllProducts ()
-> BEGIN
-> SELECT * FROM products ;
-> END ;
-> //
mysql > DELIMITER ;
Kapitel 5.1
Christian
Inauen
Views
Syntax : Variablen
Problem: Können wir Variablen in SP verwenden?
Stored
Procedures
DECLARE varName INT DEFAULT 0;
Problem: Wie definieren wir den Wert von Variablen?
DECLARE varName INT DEFAULT 0;
SET varName = 10;
Problem: Können wir Werte aus einer Tabelle in Variablen
verwenden?
SELECT COUNT (*) INTO varName
FROM list;
Kapitel 5.1
Christian
Inauen
Views
Stored
Procedures
Syntax : SP bearbeiten und
aufrufen
Stored Procedures aufrufen:
mysql > CALL storedProcedure(@a);
Stored Procedures ändern:
ALTER PROCEDURE storedProcedure [ characteristic ...];
Stored Procedures löschen:
DROP PROCEDURE IF EXISTS storedProcedure;
Kapitel 5.1
Beispiel 1 : OUT-Param
Christian
Inauen
Definition:
Views
Stored
Procedures
mysql> delimiter //
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
-> BEGIN
->
SELECT COUNT(*) INTO param1 FROM t;
-> END//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
Aufruf:
mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @a;