MySQL Stored Procedures & Functions

TOPICS:

 

PREPARE my_stmt FROM
	'SELECT COUNT(*) FROM CountryLanguage WHERE CountryCode = ?';  -- Compiled & Optimized Once.

--NOTE: Prepares/pre-compiles above statement and stores the compiled query object in my_stmt. Each '?' is a place holder for variable data that will be provided later.


mysql> SET @code = 'ESP';

mysql> EXECUTE my_stmt USING @code;

+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)


versus> SELECT COUNT(*) FROM CountryLanguage WHERE CountryCode = 'ESP'  -- Compiled & Optimized Once.

mysql> SET @code = 'RUS';


mysql> EXECUTE my_stmt USING @code;  -- Just execute, not re-compile or re-optimized.
+----------+
| COUNT(*) |
+----------+
|       12 |
+----------+
1 row in set (0.00 sec)


versus> SELECT COUNT(*) FROM CountryLanguage WHERE CountryCode = 'RUS'  -- Compiled/Optimized a second time.

DEALLOCATE PREPARE my_stmt;



mysql> PREPARE namepop FROM
    ->  'SELECT Name, Population FROM Country WHERE Code = ?';
Query OK, 0 rows affected (0.09 sec)
Statement prepared   -- & Optimized Just ONCE!!!

mysql> PREPARE error FROM
    ->  'SELECT NonExistingColumn FROM Country WHERE Code = ?';
ERROR 1054 (42S22): Unknown column 'NonExistingColumn' in 'field list'


mysql> SET @var1 = 'USA';


mysql> EXECUTE namepop USING @var1;  -- Just executed, not re-compiled or re-optimized
+---------------+------------+
| Name          | Population |
+---------------+------------+
| United States |  278357000 |
+---------------+------------+
1 row in set (0.01 sec)

mysql> SET @var2 = 'CAN';

mysql> EXECUTE namepop USING @var2; -- Just executed, not re-compiled or re-optimized
+--------+------------+
| Name   | Population |
+--------+------------+
| Canada |   31147000 |
+--------+------------+
1 row in set (0.00 sec)


mysql> SET @var3 = 'GBR';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE namepop USING @var3; -- Just executed, not re-compiled or re-optimized
+----------------+------------+
| Name           | Population |
+----------------+------------+
| United Kingdom |   59623400 |
+----------------+------------+
1 row in set (0.00 sec)


mysql> DEALLOCATE PREPARE namepop;


BENEFITS:  SQL String is send to server and compiled/optimized just once, instead of three times. 

VERSUS:

SELECT Name, Population FROM Country WHERE Code = 'USA'

SELECT Name, Population FROM Country WHERE Code = 'CAN'

SELECT Name, Population FROM Country WHERE Code = 'GBR'

 

PREPARE Can only be used with:

We will see more use of Prepared statements in Client/Server with Java.

 

STORED PROCEDURES, FUNCTIONS:
MySQL code that is stored directly in the database instead of on a client application or file.  Stored  procedures/functions have several advantages over sql files and client embedded SQL.  First, the processing of commands may be done in the DBMS and therefore on the server.  In client-server applications, processing is often shared between the Client and Server, which leads to Network traffic.  Secondly stored procedures/ functions are available to all leading to consistency of business rules and less re-invention of the wheel.  They may also reduce development time by reducing the amount of code that must be written into applications.  This also allows database rules to be enforced where they should be enforced, on the database.  Trusting the client to enforce the integrity of the database is a little like trusting the fox with the key to the hen house.

See Client/Server Architecture

STORED PROCEDURE vs. STORED FUNCTION.
1. Stored Function input Arguments Only, Stored Procedure input and output Arguments.
2. Stored Function one return value via the return statement, 
            - Stored Procedures send multiple return values via output argument.
            - Stored Procedures can also send back multiple ResultSets to client.
3. Stored Functions can usually be used in SQL Statements, Stored Procedures cannot.

STORED FUNCTIONS (CREATE FUNCTION):

 

Syntax

CREATE FUNCTION function_name (argument1 datatype1,

          Argument2 datatype2, ?)

RETURNS datatype

BEGIN

          Statements;  

END;

/* get_balance(p_customer_number): retrieve customer balance.
    @author: rtimlin
    @original: 06-Mar-2001
    @updated: 
    @arguments: p_cust_nr Customer number from the Customer Table:
    @version: 1.0

                    **** Modification History  ****

    Date        User        Description
    ===================================================
    3/6/01    rtimlin        Original.
    3/8/01    flast            Added Exception handler.
*/

CREATE FUNCTION circle_area (radius FLOAT) RETURNS FLOAT
	RETURN PI() * radius * radius;
// Comparison with Java/C++, etc.
public float circle_area (float radius)
{ // BEGIN
	return pi() * radius * radius;
} // END;
 
mysql> SELECT name, surfacearea, circle_area(surfacearea) circle_area, population FROM country;
+----------------------------------------------+-------------+----------------------+------------+
| name                                         | surfacearea | circle_area          | population |
+----------------------------------------------+-------------+----------------------+------------+
| Aruba                                        |      193.00 |          117021.1875 |     103000 |
| Afghanistan                                  |   652090.00 |        1335872323584 |   22720000 |
| Angola                                       |  1246700.00 |        4882854576128 |   12878000 |
| Anguilla                                     |       96.00 |       28952.91796875 |       8000 |
| Albania                                      |    28748.00 |           2596361472 |    3401200 |
| Andorra                                      |      468.00 |          688084.1875 |      78000 |
| Netherlands Antilles                         |      800.00 |           2010619.25 |     217000 |
| United Arab Emirates                         |    83600.00 |          21956464640 |    2441000 |
| Argentina                                    |  2780400.00 |       24286471389184 |   37032000 |
| Armenia                                      |    29800.00 |           2789859840 |    3520000 |
| American Samoa                               |      199.00 |       124410.2109375 |      68000 |

DROP FUNCTION get_balance;
DELIMITER //
CREATE FUNCTION get_balance (p_customer_number INTEGER) RETURNS DECIMAL(10,2)
BEGIN 
    DECLARE v_balance DECIMAL(10,2);
 
    SELECT balance INTO v_balance FROM customer
    WHERE customer_number = p_customer_number;  
 
    RETURN v_balance; 
END;
//
DELIMITER ;


mysql> SELECT *, get_balance(customer_number) as cust_bal FROM orders;
+--------------+------------+-----------------+----------+
| order_number | order_date | customer_number | cust_bal |
+--------------+------------+-----------------+----------+
|            1 | 2008-03-02 |               1 |   500.00 |
|            2 | 2008-03-02 |               2 |   750.00 |
|            3 | 2008-03-02 |               3 |   750.00 |
|            4 | 2008-03-02 |               4 |   750.00 |
|            5 | 2008-03-02 |               5 |  1750.00 |
|            6 | 2008-03-02 |               6 |  1750.00 |
+--------------+------------+-----------------+----------+
6 rows in set (0.00 sec)


CREATE FUNCTION tax (cost DECIMAL(10,2), tax_rate DECIMAL(10,2)) RETURNS DECIMAL(10,4)
	RETURN cost * tax_rate;

Note the indentation above, failure to indent for readability will result in one full letter grade deduction and your work being returned to you to fix.    Also I will not help you with any program that is NOT readable.

 

STORED PROCEDURES (CREATE  PROCEDURE):

?        Invoked in client using the CALL command.

?        Can have IN, OUT, IN OUT modes of parameters.

?        The IN qualifier is used for arguments for which values must be specified when calling the procedure.  In the prior CREATE FUNCTION example, v_customer_number is an IN parameter and therefor must be specified.

?        The OUT qualifier signifies that the procedure passes a value back to caller through this argument.

?        The IN OUT qualifier signifies that this argument is both an IN and an OUT parameter.

 

Syntax:

CREATE PROCEDURE procedure ([IN|OUT|INOUT] argument1 datatype1, [IN|OUT|INOUT] argument2 datatype2, ? )

BEGIN

          Statements;  

END;

Example:

DROP PROCEDURE world_record_count;
delimiter $$

CREATE PROCEDURE world_record_count()
BEGIN
	SELECT 'Country', COUNT(*) FROM Country;
	SELECT 'City', CoUNT(*) FROM City;
	SELECT 'CountryLanguage', COUNT(*) FROM CountryLanguage;
END;
$$
delimiter ;

mysql> call world_record_count();
+---------+----------+
| Country | COUNT(*) |
+---------+----------+
| Country |      239 |
+---------+----------+
1 row in set (0.05 sec)

+------+----------+
| City | CoUNT(*) |
+------+----------+
| City |     4079 |
+------+----------+
1 row in set (0.05 sec)

+-----------------+----------+
| CountryLanguage | COUNT(*) |
+-----------------+----------+
| CountryLanguage |      984 |
+-----------------+----------+
1 row in set (0.05 sec)

Query OK, 0 rows affected (0.08 sec)
DROP PROCEDURE block_example;
delimiter $$
CREATE PROCEDURE block_example ()
BEGIN
	inner_block: BEGIN
		IF DAYNAME(NOW()) = 'Wednesday' THEN
			LEAVE inner_block;
		END IF;
	SELECT 'Today is NOT Wednesday' As isWed;
	END inner_block;
END;
$$
delimiter ;

delimiter //
CREATE PROCEDURE param_test (
	IN 	p_in 	INT, 
	OUT 	p_out 	INT,
	INOUT 	p_inout	INT
)
BEGIN
	SELECT p_in, p_out, p_inout;
	SET p_in=100, p_out=200, p_inout=300;
END;
//
delimiter ;


SET @v_in = 0, @v_out=0, @v_inout=0;

mysql> CALL param_test(@v_in, @v_out, @v_inout);
+------+-------+---------+
| p_in | p_out | p_inout |
+------+-------+---------+
|    0 |  NULL |       0 |
+------+-------+---------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @v_in, @v_out, @v_inout;
+-------+--------+----------+
| @v_in | @v_out | @v_inout |
+-------+--------+----------+
| 0     | 200    | 300      |
+-------+--------+----------+
1 row in set (0.00 sec)



SQL SECURITY {DEFINER | INVOKER}

DETERMINISTIC vs. NON-DETERMINISTIC: 

LANGUAGE SQL:  Right now MySQL only supports SQL as the language, some other databases like Oracle will allow you to write your stored routines in Java.

COMMENT  'String'  Descriptive text about the routine.

DECLARE Statement:  Used for declaring several types of objects in stored routines.  They must be declared within a BEGIN/END block and in the order below.  Names must be unique.  There scope is local to block they are declared in.

 

Assigning Variables Using SELECT

SELECT column1, column2
INTO     v_variable1, v_variable2 
FROM      table_name
WHERE     condition;


delimiter //
CREATE PROCEDURE get_Country_data (
	IN 	p_code 	CHAR(3), 
	OUT 	p_name 	VARCHAR(50),
	OUT 	p_pop	INT
)
BEGIN
	DECLARE name_var VARCHAR(50);
	DECLARE pop_var INT;

	SELECT Name, Population INTO name_var, pop_var
		FROM Country WHERE code = p_code;

	SET p_name = name_var, p_pop = pop_var;
END;
//
delimiter ;

mysql> set @v_name = '';
Query OK, 0 rows affected (0.00 sec)

mysql> set @v_pop=0;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL get_Country_data ('USA', @v_name, @v_pop);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @v_name, @v_pop;
+---------------+-----------+
| @v_name       | @v_pop    |
+---------------+-----------+
| United States | 278357000 |
+---------------+-----------+
1 row in set (0.00 sec)

 

SQL EXCEPTIONS (SQLSTATE):  Fall into one of three categories:

Handlers can be single statement or compound, i.e. between BEGIN & END;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'  SET exit_loop = 1;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
BEGIN

    Statements;

END;

DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;  // this will ignore all warnings

CREATE TABLE unique_names (name VARCHAR(50) NOT NULL PRIMARY KEY);
CREATE TABLE dup_names (name VARCHAR(50));


delimiter //

CREATE PROCEDURE add_name (name_param CHAR(20))
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE '23000'
BEGIN
INSERT INTO dup_names (name) VALUES (name_param);
SELECT 'duplicate key found, inserted into dup_names' AS result;
END;

INSERT INTO unique_names (name) VALUES (name_param);
SELECT 'row inserted successfully into unique_names' AS Result;
END;
//
delimiter ;
 

CURSORS:
Cursors are used for processing multiple rows being returned from a SELECT statement.

Creating a cursor:

-         Declare the cursor.

-         Open the cursor.

-         Fetch data from the cursor.

-         Close the cursor.

 

Declaring Cursors, Syntax:

DECLARE cursor_name CURSOR FOR
                    SELECT columns
                    FROM tables
                    WHERE condition;

Notes:
-         Do not include the INTO clause in the SELECT.
-         Declare variables before cursors.

 

Opening Cursors, Syntax:

  OPEN cursor_name;

Note:  This is done in the BEGIN section and is used to prepare the cursor for execution.

  Fetching Data from the Cursor, Syntax:

  FETCH cursor_name INTO variable1, variable2, etc.;

Notes:
-    Include the same number of variables as is included in the SELECT clause of the cursor.

-         Variables are mapped to columns in the SELECT in the order they are listed.

 

DECLARE EXIT HANDLER FOR SQLSTATE '02000'  BEGIN END;

 

Closing Cursors, Syntax:

CLOSE cursor_name;

Notes:
-    Close the cursor after all the rows are processed.

-         You can't fetch data from the cursor once you have closed it.

 

 

delimiter //
CREATE PROCEDURE cursor_example ()
BEGIN
	DECLARE row_count INT DEFAULT 0;
	DECLARE code_var CHAR(3);
	DECLARE name_var CHAR(52);
	DECLARE c CURSOR FOR  -- Cursor is like a Java ResultSet
		SELECT Code, Name FROM Country WHERE Continent = 'Africa';
	OPEN c;  
	-- ResultSet rst = conn.executeQuery("SELECT Code, Name FROM Country WHERE Continent = 'Africa'");
	BEGIN
		DECLARE EXIT HANDLER FOR SQLSTATE '02000' BEGIN END;
		//NOTE: Exits the nearest BEGIN/END block, not the LOOP underneath it.
		LOOP
			FETCH c INTO code_var, name_var;
			SET row_count = row_count +1;
		END LOOP;
	END;
	CLOSE c;
	SELECT 'number of rows fetched =', row_count;
END;
//
delimiter ;


delimiter //
CREATE PROCEDURE cursor_example2 ()
BEGIN
	DECLARE row_count INT DEFAULT 0;
	DECLARE exit_flag INT DEFAULT 0;
	DECLARE code_var CHAR(3);
	DECLARE name_var CHAR(52);
	DECLARE rst CURSOR FOR  -- Like a Java ResultSet
		SELECT Code, Name FROM Country WHERE Continent = 'Africa';

	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET exit_flag=1;
	-- ResultSet rst = conn.executeQuery("SELECT Code, Name FROM Country WHERE Continent = 'Africa'");

	OPEN rst;
	fetch_loop: LOOP
		/* Next two likes equate to:
		if (!rst.next())
			break;
		*/
		FETCH rst INTO code_var, name_var;  
		IF exit_flag THEN 
			LEAVE fetch_loop;
		END IF;
		SET row_count = row_count +1;
	END LOOP;
	CLOSE rst;  -- rst.close();
	SELECT 'number of rows fetched =', row_count;
END;
//
delimiter ;

 

Flow Control (IF, CASE, LOOP, REPEAT, WHILE)