Mariadb Example create Procedure/Function

This is example to create function or procedure using Mariadb. What is Mariadb? This the quotate from Wikipedia:

MariaDB is a community-developed fork of the MySQL relational database management system, the impetus being the community maintenance of its free status under the GNU GPL. Being a fork of a leading open source software system, it is notable for being led by its original developers and triggered by concerns over direction by an acquiring commercial company Oracle. Contributors are required to share their copyright with Monty Program AB.

And this is example I’ve structure table like this:

MariaDB [example]> DESC criteria;
+------------+------------------+------+-----+---------------------+----------------+
| Field      | Type             | Null | Key | Default             | Extra          |
+------------+------------------+------+-----+---------------------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL                | auto_increment |
| name       | varchar(100)     | NO   |     | NULL                |                |
| created_at | timestamp        | NO   |     | 0000-00-00 00:00:00 |                |
| updated_at | timestamp        | NO   |     | 0000-00-00 00:00:00 |                |
+------------+------------------+------+-----+---------------------+----------------+
4 rows in set (0.00 sec)

And this is example procedure from above table:

DELIMITER $$
	CREATE PROCEDURE example_procedure()
    	BEGIN
        	SELECT name FROM criteria;
        END;
;

And to call the procedure, run this command:

MariaDB [example]> CALL example_procedure();
+------------+
| name       |
+------------+
| Matematika |
| Biologi    |
| Kimia      |
| Fisika     |
| Ekonomi    |
| Geografi   |
| Sosiologi  |
+------------+
7 rows in set (0.09 sec)

And how about procedure with parameter?
This the example code:

DELIMITER $$
	CREATE PROCEDURE example_procedure(keyword int)
    	BEGIN
        	SELECT name FROM criteria WHERE id = keyword;
        END;
;

And run the code like this:

MariaDB [example]> CALL example_procedure(31);
+------------+
| name       |
+------------+
| Matematika |
+------------+
1 row in set (0.00 sec)

After we create procedure, now I want share how to create a function. Different procedure with function simplest is the function can join with select command and we must have parameter for the functions. This example code:

MariaDB [example]> SELECT name, example_function(parameter) FROM tables;

The if we use procedure, we only can use with this command:

MariaDB [example]> CALL example_procedure();

So now this is example function:

DELIMITER $$
	CREATE FUNCTION example_function(id INT)
    returns INT
    return id;
;

And run example this command:

MariaDB [example]> SELECT name, example_function(5) FROM criteria;
+------------+---------------------+
| name       | example_function(5) |
+------------+---------------------+
| Matematika |                   5 |
| Biologi    |                   5 |
| Kimia      |                   5 |
| Fisika     |                   5 |
| Ekonomi    |                   5 |
| Geografi   |                   5 |
| Sosiologi  |                   5 |
+------------+---------------------+
7 rows in set (0.00 sec)

And this is a complex function:

DELIMITER $$
CREATE FUNCTION example_function(keyword INT) RETURNS CHAR(20)
BEGIN
	DECLARE result CHAR(20);
    	SELECT name INTO result
        FROM
        	criteria
        WHERE
        	id = keyword;
 	RETURN result;
END;
;

And if we run this command, we can get result like this:

MariaDB [example]> SELECT name, example_function(5) FROM criteria;
+------------+---------------------+
| name       | example_function(5) |
+------------+---------------------+
| Matematika | NULL                |
| Biologi    | NULL                |
| Kimia      | NULL                |
| Fisika     | NULL                |
| Ekonomi    | NULL                |
| Geografi   | NULL                |
| Sosiologi  | NULL                |
+------------+---------------------+
7 rows in set, 7 warnings (0.00 sec)

Why NULL result? Because id == 5 is not exist.
Or another function, this function is add the id + 10, so if I have id = 32, the result is 42:

DELIMITER $$
CREATE FUNCTION example_function(keyword INT) 
RETURNS INT
BEGIN
	DECLARE result INT;
    	SELECT id + 10 INTO result
        FROM
        	criteria
        WHERE
        	id = keyword;
 	RETURN result;
END;
;

Explanations:

  1. Line 1. DELIMITER code for MariaDB is to tell this is an early sign of writing code
  2. Line 2. This code for create function with parameter is INT data type with name “keyword”. Why INT? Because the id from the table using INT data type.
  3. Line 3. This is to the return value and with data type is INT. Example from this code, we can get result the INT data type like 1, 2, 3, …, n and not get a, b, c, d, …
  4. Line 4. It’s the begin main code
  5. Line 5. We declare result with INT data type. Same as line 2, we must get result with int data type like 1, 2, 3, …, n
  6. Line 6. It’s the select command with the summation operator and send the result to name “result” from this code “INTO result”
  7. Line 10. The where statment with parameter from line 2 (keyword) and the type data is INT
  8. Line 11. It’s to get the result value and we must return the result, because the results should be displayed
  9. Line 12. It’s the end code
  10. Line 13. The code for MariaDB is to tell this is a sign of the end of writing code

And run this code:

MariaDB [example]> SELECT name, id, example_function(id)as 'id + 10' FROM criteria;
+------------+----+---------+
| name       | id | id + 10 |
+------------+----+---------+
| Matematika | 31 |      41 |
| Biologi    | 32 |      42 |
| Kimia      | 33 |      43 |
| Fisika     | 34 |      44 |
| Ekonomi    | 35 |      45 |
| Geografi   | 36 |      46 |
| Sosiologi  | 37 |      47 |
+------------+----+---------+
7 rows in set (0.00 sec)

SHOW ALL FUNCTIONS/PROCEDURE:
If we want show/display all function from the database, we can use this command:

MariaDB [example]> SHOW FUNCTION STATUS;
+------------+------------------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db         | Name             | Type     | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------------+------------------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| example | check_value      | FUNCTION | root@localhost | 2014-04-01 00:20:12 | 2014-04-01 00:20:12 | DEFINER       |         | utf8mb4              | utf8mb4_general_ci   | utf8_general_ci    |
| example | example_function | FUNCTION | root@localhost | 2014-04-08 20:45:05 | 2014-04-08 20:45:05 | DEFINER       |         | utf8mb4              | utf8mb4_general_ci   | utf8_general_ci    |
+------------+------------------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
2 rows in set (0.00 sec)

If we want show/display all procedure from the database, we can use this command:

MariaDB [example]> SHOW PROCEDURE STATUS;
+------------+-------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db         | Name              | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------------+-------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| example | example_procedure | PROCEDURE | root@localhost | 2014-04-08 20:32:38 | 2014-04-08 20:32:38 | DEFINER       |         | utf8mb4              | utf8mb4_general_ci   | utf8_general_ci    |
+------------+-------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

The result is to long, if we want only show the name this is the alternative show all procedure/function:

MariaDB [example]> SELECT name FROM mysql.proc;
+-------------------+
| name              |
+-------------------+
| check_value       |
| example_function  |
| example_procedure |
+-------------------+
3 rows in set (0.00 sec)

DROP FUNCTIONS/PROCEDURE:
To delete the function or procedure, we must run this code:

//for procedure
MariaDB [example]> DROP PROCEDURE name_procedure;
//for function
MariaDB [example]> DROP PROCEDURE name_function;

Example code:

//for procedure
MariaDB [spk_sman28]> DROP PROCEDURE example_procedure;
Query OK, 0 rows affected (0.00 sec)
//for function
MariaDB [spk_sman28]> DROP FUNCTION example_function;
Query OK, 0 rows affected (0.00 sec)
Advertisements

One comment

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