Login

Create a stored procedure

Rodrigo

By Rodrigo

Created 2014-07-02 23:39:02 Modified 2015-07-23 12:25:29

A stored procedure is a program (routine) stored in the database, there are too many uses for a stored procedure, may be used for insert data, validate data and then insert them, retrieve data returning a resultset like a query, you can return data as a sql data type, or anything that the sql language allows to do,  you may choose whether returning data or not using a stored procedure; that is the main difference between a function, sql function must return data. They also are called a procsprocStoPro,StoredProcsp or SP

In this tutorial we are going to develop a stored procedure with some logic within, which going to insert data in our table adding some logic for generate passwords

this tutorial was written using the following gear:

  • Hardware:           laptop Thinkpad E430 Intel I5, 4gb Ram, 500gb HDD
  • OS:                     x86_64 GNU/Linux Ubuntu 14.10
  • Kernel:                3.13.0-29-generic
  • IDE/Text editor:   MySql workbench 6.0
  • DB:                      Mysql 5.5.31
  • Mysql client:        MySql workbench 6.0

 

1.- Creating the db:

First we need a database to work on the stored procedure, let's take the following script and run it on mysql

CREATE DATABASE tutorial;
USE tutorial;
 
CREATE TABLE users(
   id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
   name VARCHAR(45) NOT NULL,
   birthdate DATE NOT NULL,
   password VARCHAR(45) NOT NULL
);

2.- Creating SP:

As you may see we have created a database only with one table, fair enough to work on our stored procedure which needs generate passwords

DELIMITER $$
CREATE PROCEDURE insert_user(pname VARCHAR(45), pbirthdate DATE)
BEGIN
  DECLARE gen_pass VARCHAR(45); /*variable declaration*/
  SET gen_pass=CONCAT(pname, SUBSTRING(pbirthdate, 1, 4)); /*adding some logic for passowrd*/
  INSERT INTO users(name, birthdate, password)VALUES(pname,pbirthdate,gen_pass); /*insert*/
END$$
DELIMITER ;

3.- Executing the SP:

up to this point, we are done, now let's execute the stored procedure and see how it works

call insert_user('John','1984-06-30');

now let's see the data

select*from users;

 

Download it in https://docs.google.com/file/d/0BwOqSob-RrBOWk8zbWxCM0xXN0k/edit?pli=1

4.- Conclusion:

With stored procedures we can delegate the work to the database server without using the app server to do some logic, there are some good and bad things about using stored procedures:

Advantages:

  • Speed: Stored procedures are cached on the server
  • Testing: Can be tested independent of the application
  • Maintainability: They are stored on the database, so you know exactly where program is located, if you need to do something with it

Disadvantages:

  • Portability: It is a shame but you can't migrate stored procedures, so if you have a sp in SQL SERVER, and then you migrate to ORACLE DB you need to write the sp again using the PL/SQL syntax
  • Testing: The errors can be only detected in runtime
  • Limited functions: Using sql language for handle the application is always less powerful than using a programming language like php, java, ruby, C#, inter alia

5.- Reference links:




comments powered by Disqus