SQL Basics - Chapter 19 - Stored Procedures

01 - What Are Stored Procedures?

Stored procedures are saved collections of one or more SQL statements for later use. They can be used for:

Simplifying complex operations
- For each order, check stock levels, determine whether items must be reserved or ordered, decide whether the customer receives a discount and communicate the order status
Maintaining data consistency
- Writing a set of operations once helps ensure consistent results
Preventing errors
- Defining a complex operation once reduces the risk of mistakes
Making database changes easier to manage
- Changes to table or column names can be made centrally in the stored procedure
Restricting access to data for security
- Protecting data through stored procedures reduces the risk of unintended changes
Processing operations faster
- Stored procedures can benefit from compiled or cached execution plans
Writing more powerful code
- A stored procedure can coordinate several queries and combine operations that cannot all be expressed in one query

The three main advantages of stored procedures are:

Simplicity
Security
Performance

Stored procedures also have disadvantages

Their syntax depends heavily on the DBMS
They are considerably more complex to write than ordinary SQL statements
Some database administrators restrict their creation because of this complexity
SQLite does not support stored procedures

For security, some DBMSs can allow only certain users to create stored procedures while permitting other users to execute them.

02 - Creating and Dropping Stored Procedures

Creating a stored procedure:

Use an IN parameter when defining this stored procedure for inserting data
First inspect the data types in the relevant table

Oracle

	-- Create a stored procedure to add a product to Products
	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;

	-- Use a stored procedure
	BEGIN
  	addProduct('BR007','BRS01','A Waldorf Puppet',42.50, 'Made of wood and as a result, they are all different');
	END;

	-- Drop a stored procedure 
	DROP PROCEDURE addProduct;

MySQL

	-- Create a stored procedure to add a product to Products 
	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 ;

	-- Use a stored procedure 
	CALL addProduct('BR007','BRS01','A Waldorf Puppet',42.50, 'Made of wood and as a result, they are all different!');

	-- Drop a stored procedure 
	DROP PROCEDURE IF EXISTS addProduct;

Use a semicolon (;) to terminate a statement
The semicolon separates statements from each other
A stored procedure can contain several statements ending in semicolons
When defining a stored procedure, those statements must be treated as one definition
For this reason, temporarily choose a different delimiter when defining stored procedures in the MySQL client

SQL Server

	-- Create a stored procedure to add a product to Products 
	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

	-- Use a 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';

	-- Drop a stored procedure 
	DROP PROCEDURE IF EXISTS addProduct;

SELECT * 
FROM Products
ORDER BY prod_price;

prod_id	vend_id	prod_name	prod_price	prod_desc
BNBG01	DLL01	Fish bean bag toy	3.49	Fish bean bag toy complete with bean bag worms with which to feed it
BNBG02	DLL01	Bird bean bag toy	3.49	Bird bean bag toy eggs are not included
BNBG03	DLL01	Rabbit bean bag toy	3.49	Rabbit bean bag toy comes with bean bag carrots
RGAN01	DLL01	Raggedy Ann	4.99	18 inch Raggedy Ann doll
BR01	BRS01	8 inch teddy bear	5.99	8 inch teddy bear comes with cap and jacket
BR02	BRS01	12 inch teddy bear	8.99	12 inch teddy bear comes with cap and jacket
RYL01	FNG01	King doll	9.49	12 inch king doll with royal garments and crown
RYL02	FNG01	Queen doll	9.49	12 inch queen doll with royal garments and crown
BR03	BRS01	18 inch teddy bear	11.99	18 inch teddy bear comes with cap and jacket
BR007	BRS01	A Waldorf Puppet	42.5	Made of wood and as a result they are all different