An Introduction to Sql Procedures

As I mentioned in my previous post "Lets play with Sql" we can use SQL  programmatically for data manipulation. SQL Procedures and functions helps to full fill this. Using procedures we can execute number of operations or queries with in one procedure call. More over we can call a procedure with in another procedure like other languages.

Syntax of Creating Procedure is

CREATE PROCEDURE procedure_name()

          BEGIN
            .
            .
            .
          END 

With in BEGIN and END we write the procedure body includes queries,loops,string manipulations etc. We can store this procedure for future use. We can call the stored procedure in future. The syntax for calling procedure is

CALL procedure_name()

There is another good way to create and call procedures. We can store procedures in a text file. Then first load this text file to sql by this time the procedure will store. For this we can use

source file_name

in MySQL prompt. After loading we can call the procedure using CALL command.

We can Drop an existing procedure using command
DROP PROCEDURE procedure_name

Now we can go more deeper. For that Lets look in to one example.

DELIMITER $$
 DROP PROCEDURE IF EXISTS WhileLoopProc$$
 CREATE PROCEDURE WhileLoopProc()
 BEGIN
   DECLARE x  INT;
   DECLARE str  VARCHAR(255);
   SET x = 1;
   WHILE x  <= 30 DO
     SET str = (SELECT name FROM sanity WHERE id=x);
     SET  x = x + 1;
     SELECT str;
   END WHILE;
 END$$
DELIMITER;

In this example we have already discussed the section before 'BEGIN'. DECLARE is the key word using for declaring variables and 'SET' is using for assigning value to a variable.The next section is a while loop for selecting name corresponds to id and print the same. For printing a value we use 'SELECT' key word.

If you understand above example it is time to go little more deeper. Before that it is better to go through the link given below.
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
It gives descriptions about various string functions.

DELIMITER $$
 DROP PROCEDURE IF EXISTS CursorProc$$
 CREATE PROCEDURE CursorProc()
 BEGIN
  DECLARE str  VARCHAR(255);
  DECLARE cur1  CURSOR FOR SELECT name FROM sanity LIMIT 500;
  OPEN cur1;
  read_loop: LOOP
   FETCH cur1 INTO str;
   SET str = TRIM(LEADING '"' FROM str);
   SET str =TRIM(TRAILING '"' FROM str);
   CALL selct_proc( str);
  END LOOP; 
  CLOSE cur1;
 END$$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS selct_proc$$
CREATE PROCEDURE selct_proc(INOUT str1 VARCHAR(25))
BEGIN
 DECLARE str_dot  VARCHAR(255);
 DECLARE str_rplce  VARCHAR(255);
 DECLARE first_str  VARCHAR(255);
 SET str_dot = LOCATE(".",str1);
 IF str_dot != 0 then
  SET str_rplce = REPLACE(str1, '.', ' ');
  SET first_str = SUBSTRING_INDEX(str_rplce,' ',1);
  SELECT str_rplce,first_str;
 END IF;
END$$
DELIMITER ; 

This above example covers almost all basics like Loop, If conditions calling a procedure with in another procedure,passing parameters and using strong string functions for manipulating strings.


In the above example we using a variable cur1 it is for storing all the data after executing the query. Then inside a loop using FETCH key word we are accessing each value into a string. Then using some string function we styling the string. Then we are calling a procedure inside and passing a variable. In the second one we accessing the variable,here you see a key word INOUT this fore returning the value back. Here we are using a small IF construct to familiar with that.

This is only a small introduction to the topic. But by going through this you can start to play with procedures. And the main advantage of using procedures is its speed of execution. It is very much faster than writing script  for job done. So lets try.