MySQL STORED PROCEDURES

Today I’m going to talk about another important topic in MySQL, Stored Procedures. Actually stored procedures are used to execute a query by calling a function. It is good for the security reasons. For an example, think about a situation where revealing the data of the database is not secure, but we want to allow others to modify or do calculations using those data. In such a situation we can use stored procedures to store the business logic in the data base server. Then the logic will be executed at the server and the result will be delivered to the users.

1.png

Let’s see the above scenario with an example.

  • First of all I’m creating a new database.

4

  • Then I’m going to create a new table.

5

6

  • After that we can insert some values to the table.

7

  • Let’s try to retrieve data from the table.

9

  • Now I’m going to create a new user.

10

  • I want to change permissions of the new user only to execute. So the new user can execute stored procedures but can’t read, write data from the tables.

11

  • Let’s login to the server from the newly created user.

12.png

  • Now try to access the databases and tables.

13

14

  • So as you can see, new user can see the database, but not the table.
  • Now let’s login from the admin client and create a stored procedure.
  • Here what I have done is changing the end of line command to a different one. Normally MySQL identify the end of a command using the ‘;‘. But after this command, the end of command will be identified with ‘$$‘.

16

  • Let’s see it has worked or not. It’s not working.

17

  • Now let’s try using $$. It works!
  • Now I’m going to create a stored procedure for retrieving data from the table. This is the normal query what we are using.

18

  • Now this is my stored procedure. Will see later how to create stored procedures in deeper. Now just I’m using it to show as an example.

20

  • Let’s call it. It retrieves data successfully.

21

  • Now I’m going to the use the new user account again.

22

  • Let’s try to find the table. It’s not there as earlier.

23

28

  • Finally, let’s try to execute the stored procedure. But it works.

24

So as you saw, though the user can’t access the table, he can execute the procedure and retrieve the out put. So it’s clear now that we can use stored procedures for security purposes.

From here we can see how to create and use stored procedures.

  • Using the show command we can get information about a previously created stored procedure.

25

  • You can’t use it, if you haven’t create the procedure yet.

29.png

  • We can simply create a stored procedure using the ,

CREATE PROCEDURE <PROCEDURE_NAME>()                           // () is must

BEGIN

<SQL_QUERY>;

END

30.png

  • We can call or execute the stored procedure using the,

CALL <PROCEDURE_NAME>()                                                             // () is optional

31.png

In the previous example we create the stored procedure without any parameters. But if you want you can use parameters as well. There are three types of parameters used in Stored Procedures.

  1. IN (default) : Using this way we can send inputs to the procedure, the input will not change throughout the procedure.
  2. OUT : This is used to return a value from the procedure.
  3. INOUT : This is the special parameter. Using this type we can send an input to the procedure and procedure will modify the value and return it to the user.

Also there are two types of variables in used as well,

  1. local : We can declare these within the BEGIN and END of the procedure. It has a local scope.
  2. global : These variables have a global scope and declared using the ‘@’ before the variable name.

Let’s see those what we have talked in examples,

IN

  • Let’ create a new procedure with an IN parameter.

CREATE PROCEDURE <PROCEDURE_NAME>(IN <VARIABLE_NAME> <DATA_TYPE>)                           

BEGIN

<SQL_QUERY>;

END

38

  • Let’s call the procedure. Here we have to pass a INT value as well.

CALL <PROCEDURE_NAME>(<VALUE>)

39

OUT

  • Now let’s try to create another procedure with OUT.

CREATE PROCEDURE <PROCEDURE_NAME>(OUT <VARIABLE_NAME> <DATA_TYPE>)                           

BEGIN

<SQL_QUERY>;

END

32

  • Let’s execute the procedure.

CALL <PROCEDURE_NAME>(<PARAMETER>)

33

34

INOUT

  • Finally we can see the INOUT type.

CREATE PROCEDURE <PROCEDURE_NAME>(INOUT <VARIABLE_NAME> <DATA_TYPE>)                           

BEGIN

<SQL_QUERY>;

END

40

  • Let’s execute the procedure.

SET <PARAMETER> = <VALUE>

CALL <PROCEDURE_NAME>(<PARAMETER>)

41

42

43

  • If we call the procedure again, it will update the value again.

44

VARIABLES

  • We can declare a variable using the,

DECLARE <VARIABLE_NAME> <DATA_TYPE> DEFAULT <VALUE>;

35

36

CONDITIONS

We can use If Else, If – Else IF – Else with stored procedures.

  • Let’s see an example.

IF <CONDITION> THEN

<STATEMENT>;

ELSE IF <CONDITION> THEN

<STATEMENT>;

ELSE

<STATEMENT>;

45

  • Let’s call the procedure.

46

47.png

LOOPS

We can use While and Repeat-Until loops with stored procedures.

  • Let’s create a while loop.

WHILE <CONDITION> DO

<STATEMENT>;

END WHILE;

50

  • Let’s call the procedure.

51

  • Now let’s see the syntax of RepeatUntil loop.

REPEAT

<STATEMENT>;

UNTIL <CONDITION>

END REPEAT;

  • Let’s call the procedure.

53

54

If we want we can get all the stored procedures of a database like this,

SHOW PROCEDURE STATUS;

56

We can use the WHERE clause with the above command to get results in a more specific way.

SHOW PROCEDURE STATUS WHERE <COLUMN_NAME> = <VALUE>;

57

Hope now you have a clear idea about MySQL stored procedures. See you soon with another important topic. Thank You!

Advertisements

2 thoughts on “MySQL STORED PROCEDURES

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s