notes from learning mysql stored procedures

what is a stored procedure? - it is a set of sql statements that are stored within the database.

Stored procedures advantages

Stored procedure increases performance of application. Once created, stored procedure is compiled and stored in the database catalog. It runs faster than uncompiled SQL commands which are sent from application.

Stored procedure reduced the traffic between application and database server because instead of sending multiple uncompiled long SQL commands statement, application only has to send the stored procedure name and get the result back.

Stored procedure is reusable and transparent to any application which wants to use it. Stored procedure exposes the database interface to all applications so developer doesn’t have to program the functions which are already supported instored procedure in all programs.

Stored procedure is secured. Database administrator can grant the right to application which to access which stored procedures in database catalog without granting any permission on the underlying database table.

Beside those advantages, stored procedure still has its own disadvantages which are bellow

Stored procedures disadvantages

Stored procedure make the database server high load in both memory for and processors. Instead of being focused on the storing and retrieving data, you could be asking thedatabase server to perform a number of logical operations or a complex of business logic which is not the role of it.

Stored procedure only contains declarative SQL so it is very difficult to write a procedure with complexity of business like other languages in application layer such as Java, C#, C++…

You cannot debug stored procedure in almost RDMBSs and in MySQL also. There are some workarounds on this problem but it still not good enough to do so.

Writing and maintain stored procedure usually required specialized skill set that not all developers possess. This introduced the problem in both application development and maintain phrase.

DELIMITER // - changes the delimiter to //
CREATE PROCEDURE GetAllProducts() - GetAllProducts() is the name of the stored procedure.
   BEGIN - tells the start of the procedure's workings
  SELECT *  FROM products; - the actual procedure
  END // - changes the delimiter to // and tells the end of the procedure
 DELIMITER ; - changes back the delimiter to a semicolon

this is how a stored procedure is called - CALL STORED_PROCEDURE_NAME()

a variable is instantiated in a stored procedure this way: declare variable_name data type(size) default value. eg declare totalProducts int default 0

multiple variables with the same data type can be declared together. eg. declare x, y int default 0

there are two ways to assign values to variables:

1 - using the set keyword - set totalProducts = 39;
2 - using select... into - select count(*) into totalProducts from products

variables have scopes which determine their life span.

variables with an @ are session variables and have a life span until the end of the session.

variables have modes: in, out, inout

to be continued