SQL Basis

Stored Procedures zijn opgeslagen collecties van één of meerdere SQL statements, voor later gebruik. Je kunt ze inzetten voor:

• Het vereenvoudigen van complexe handelingen
  • Controleer bij het verwerken van elke order of: items op voorraad zijn, items gereserveerd moeten worden, items besteld moeten worden, of de klant een korting krijgt en wat er naar de klant gecommuniceerd kan worden m.b.t. zijn bestelling
• Handhaven van data consistentie
  • Het maar één keer hoeven te schrijven van een set handelingen zorgt voor een eenduidig / consistent resultaat
• Voorkomen van fouten
  • Een reeks complexe handelingen één keer definiëren vermindert de kans op fouten
• Voor het vereenvoudigen van het werken met database wijzigingen
  • Tabel of kolomnaam wijzigingen hoef je alleen maar aan te passen in stored Procedure
• Veiligheid waarborgen d.m.v. restricties op toegankelijke data
  • Het afschermen van data middels stored procedures verkleint de kans op datacorruptie
• Snellere verwerking van de handeling
  • Stored procedures worden gecompileerd opgeslagen en worden daardoor sneller verwerkt
• Krachtigere code schrijven
  • Stored procedures kunnen meerdere queries verwerken zodat ook de SQL elementen die alleen gebruikt kunnen worden in één enkele query gecombineerd kunnen worden

Er zijn dus grofweg drie voordelen om met Stored Procedures te werken:

• Eenvoud
• Veiligheid
• Prestatie

Er zijn ook nadelen bij het gebruik van Stored Procedures

• De syntax van Stored Procedures is zeer sterk afhankelijk van het gebruikte DBMS
• Stored Procedures zijn aanzienlijk complexer om te schrijven dan standaard SQL
• Sommige DBA's (Database administrators) beperken het maken van Stored Procedures i.v.m. complexiteit
• SQLite ondersteunt geen Stored Procedures

I.v.m. de veiligheid kunnen sommige DBMS'en zo ingesteld zijn dat alleen bepaalde gebruikers voldoende rechten hebben om Stored Procedures te maken maar dat andere gebruikers ze wel weer kunnen uitvoeren.

Het maken van een Stored Procedure:

• Bij het maken van een Stored Procedure (voor het invoegen van data) gebruiken we de IN operator
• Bekijk in de database eerst de verschillende datatypes van de betreffende tabel

	-- Het maken van een Stored Procedure voor het toevoegen van een product aan de Products tabel
	CREATE OR REPLACE PROCEDURE addProduct(
	  p_id    IN PRODUCTS.PROD_ID%TYPE,
	  v_id    IN PRODUCTS.VEND_ID%TYPE,
	  p_name  IN PRODUCTS.PROD_NAME%TYPE,
	  p_price IN PRODUCTS.PROD_PRICE%TYPE,
	  p_desc  IN PRODUCTS.PROD_DESC%TYPE)
	  IS
	  BEGIN
	    INSERT INTO Products("PROD_ID", "VEND_ID", "PROD_NAME", "PROD_PRICE", "PROD_DESC") 
	    VALUES (p_id, v_id, p_name, p_price, p_desc);
	    COMMIT;
	  END;	

	-- Het gebruiken van een Stored Procedure
	BEGIN
  	addProduct('BR007','BRS01','A Waldorf Puppet',42.50, 'Made of wood and as a result, they are all different');
	END;

	-- Het Verwijderen van een Stored Procedure 
	DROP PROCEDURE addProduct;	

	-- Het maken van een Stored Procedure voor het toevoegen van een product aan de Products tabel 
	DELIMITER //
	CREATE PROCEDURE addProduct(
	   IN p_id char(10),
	   IN v_id char(10),
	   IN p_name char(255),
	   IN p_price decimal(8,2),
	   IN p_desc text
	)
	BEGIN
	   INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
	   values (p_id, v_id, p_name, p_price, p_desc);
	END //
	DELIMITER ;

	-- Het gebruiken van een Stored Procedure 
	CALL addProduct('BR007','BRS01','A Waldorf Puppet',42.50, 'Made of wood and as a result, they are all different!');	

	-- Het Verwijderen van een Stored Procedure 
	DROP PROCEDURE IF EXISTS addProduct;	

	-- Het maken van een Stored Procedure voor het toevoegen van een product aan de Products tabel 
	CREATE PROCEDURE addProduct
	  @p_id    char(10),
	  @v_id    char(10),
	  @p_name  char(255),
	  @p_price decimal(8,2),
	  @p_desc  char(1000)
	  AS
	  BEGIN
	    INSERT INTO Products([prod_id], [vend_id], [prod_name], [prod_price], [prod_desc])
	    VALUES(@p_id, @v_id, @p_name, @p_price, @p_desc)
	  END
	  GO

	-- Het gebruiken van een Stored Procedure 
	EXEC addProduct 
	@p_id = 'BR007',
	@v_id = 'BRS01',
	@p_name = 'A Waldorf Puppet',
	@p_price = 42.50,
	@p_desc = 'Made of wood and as a result, they are all different'; 

	-- Het Verwijderen van een Stored Procedure 
	DROP PROCEDURE IF EXISTS addProduct;

SELECT * 
FROM Products
ORDER BY prod_price;