{"id":94207,"date":"2022-05-09T17:38:57","date_gmt":"2022-05-09T17:38:57","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=94207"},"modified":"2022-05-09T17:38:57","modified_gmt":"2022-05-09T17:38:57","slug":"working-with-mysql-stored-functions","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/mysql\/working-with-mysql-stored-functions\/","title":{"rendered":"Working with MySQL stored functions"},"content":{"rendered":"<p><strong>This article is part of Robert Sheldon's continuing series on Learning MySQL. To see all of the items in the series, <a href=\"https:\/\/www.red-gate.com\/simple-talk\/mysql-coding-basics\/\">click here<\/a>.<\/strong><\/p>\n\n<p>In the previous three articles in this series, I focused on creating basic database objects that you can use to get started with MySQL. You learned how to build an initial database and then add tables, views, and stored procedures. In this article, I cover one more important type of object, the stored function, a routine that is stored in a database and can be invoked on-demand, similar to a user-defined scalar function in SQL Server or other database systems.<\/p>\n<p>Stored functions work much like MySQL built-in functions. You can call either type of function in an expression, such as those in a query\u2019s <code>SELECT<\/code>, <code>WHERE<\/code>, or <code>ORDER<\/code> <code>BY<\/code> clause. For example, you might use the <code>CAST<\/code> built-in function in a <code>SELECT<\/code> clause to convert a column to a different data type, as in <code>CAST(plane_id<\/code> <code>AS<\/code> <code>CHAR)<\/code>. The expression converts the <code>plane_id<\/code> column (an integer) to a character data type. In the same way, you can use a stored function in your expression, applying your own logic to the <code>plane_id<\/code> column or any other column.<\/p>\n<p>Before I go any further with stored functions, it\u2019s important to note that there are three different types of MySQL functions that you can add at the database or server level:<\/p>\n<ul>\n<li><strong>Stored functions.<\/strong> Functions that you create as database objects by using the <code>CREATE<\/code> <code>FUNCTION<\/code> statement.<\/li>\n<li><strong>Loadable functions.<\/strong> Functions that are compiled as library files and then loaded to the server dynamically by running a <code>CREATE<\/code> <code>FUNCTION<\/code> statement.<\/li>\n<li><strong>Native functions.<\/strong> Functions that are added to the server by modifying the MySQL source code and compiling it into <strong>mysqld<\/strong>.<\/li>\n<\/ul>\n<p>This article focuses on creating stored functions, which share many of the same characteristics as MySQL stored procedures. In fact, I had considered writing about both of them in the previous article, but I think there are enough differences between them to warrant a separate article. It also provides a more consistent way of introducing each of the primary MySQL object types, even if it does mean repeating some of the information. With this in mind, let\u2019s get started with the stored function.<\/p>\n<h2>Preparing your MySQL environment<\/h2>\n<p>As with the previous few articles, the examples in this article are based on the <code>travel<\/code> database. If you already have it installed, you can skip this section. If not, you can start by running the following SQL script to create the database and its tables:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">DROP DATABASE IF EXISTS travel;\r\nCREATE DATABASE travel;\r\nUSE travel;\r\nCREATE TABLE manufacturers (\r\n  manufacturer_id INT UNSIGNED NOT NULL AUTO_INCREMENT,\r\n  manufacturer VARCHAR(50) NOT NULL,\r\n  create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\r\n  last_update TIMESTAMP NOT NULL \r\n    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\r\n  PRIMARY KEY (manufacturer_id) ) \r\nENGINE=InnoDB AUTO_INCREMENT=1001;\r\nCREATE TABLE airplanes (\r\n  plane_id INT UNSIGNED NOT NULL AUTO_INCREMENT,\r\n  plane VARCHAR(50) NOT NULL,\r\n  manufacturer_id INT UNSIGNED NOT NULL,\r\n  engine_type VARCHAR(50) NOT NULL,\r\n  engine_count TINYINT NOT NULL,\r\n  max_weight MEDIUMINT UNSIGNED NOT NULL,\r\n  wingspan DECIMAL(5,2) NOT NULL,\r\n  plane_length DECIMAL(5,2) NOT NULL,\r\n  parking_area INT GENERATED ALWAYS AS \r\n        ((wingspan * plane_length)) STORED,\r\n  icao_code CHAR(4) NOT NULL,\r\n  create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\r\n  last_update TIMESTAMP NOT NULL \r\n    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\r\n  PRIMARY KEY (plane_id),\r\n  CONSTRAINT fk_manufacturer_id FOREIGN KEY (manufacturer_id) \r\n    REFERENCES manufacturers (manufacturer_id) ) \r\nENGINE=InnoDB AUTO_INCREMENT=101;<\/pre>\n<p>The <code>airplanes<\/code> table includes a foreign key that references the <code>manufacturers<\/code> table, so be sure to create the tables in the order shown here. After you create the tables, you can add sample data to them so you\u2019ll be able to test your function. To populate the tables, run the following <code>INSERT<\/code> statements:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">INSERT INTO manufacturers (manufacturer)\r\nVALUES ('Airbus'), ('Beechcraft'), ('Piper');\r\nINSERT INTO airplanes \r\n  (plane, manufacturer_id, engine_type, engine_count, \r\n    max_weight, wingspan, plane_length, icao_code)\r\nVALUES \r\n  ('A380-800', 1001, 'jet', 4, 1267658, 261.65, 238.62, 'A388'),\r\n  ('A319neo Sharklet', 1001, 'jet', 2, 166449, 117.45, 111.02, 'A319'),\r\n  ('ACJ320neo (Corporate Jet version)', 1001, 'jet', 2, 174165, \r\n               117.45, 123.27, 'A320'),\r\n  ('A300-200 (A300-C4-200, F4-200)', 1001, 'jet', 2, 363760, 147.08, \r\n               175.50, 'A30B'),\r\n  ('Beech 390 Premier I, IA, II (Raytheon Premier I)', 1002, 'jet', \r\n               2, 12500, 44.50, 46.00, 'PRM1'),\r\n  ('Beechjet 400 (from\/same as MU-300-10 Diamond II)', 1002, 'jet', \r\n              2, 15780, 43.50, 48.42, 'BE40'),\r\n  ('1900D', 1002, 'Turboprop', 2,17120,  57.75, 57.67, 'B190'),\r\n  ('PA-24-400 Comanche', 1003, 'piston', 1, 3600, 36.00, 24.79, 'PA24'),\r\n  ('PA-46-600TP Malibu Meridian, M600', 1003, 'Turboprop', 1, 6000, \r\n         43.17, 29.60, 'P46T'),\r\n  ('J-3 Cub', 1003, 'piston', 1, 1220, 38.00, 22.42, 'J3');<\/pre>\n<p>As with the <code>CREATE<\/code> <code>TABLE<\/code> statements, you should run the <code>INSERT<\/code> statements in the order specified here so you don\u2019t violate the foreign key defined on the <code>airplanes<\/code> table. Once you have the tables set up and populated, you can start creating stored functions.<\/p>\n<h2>Creating a stored function in MySQL<\/h2>\n<p>To add a stored function to a MySQL database, you can use the <code>CREATE<\/code> <code>FUNCTION<\/code> statement. The statement is similar to a <code>CREATE<\/code> <code>PROCEDURE<\/code> statement in several respects. In both cases, you must provide a name for the object and you must define a routine. You also have the option to include a <code>DEFINER<\/code> clause, one or more characteristics, and one or more parameters.<\/p>\n<p>Despite these similarities, the <code>CREATE<\/code> <code>FUNCTION<\/code> statement differs in several important ways:<\/p>\n<ul>\n<li>A stored function can return only one value, unlike a stored procedure, which can return multiple values or an entire result set.<\/li>\n<li>A stored function supports input parameters only. A stored procedure supports <code>IN<\/code>, <code>OUT<\/code>, and <code>INOUT<\/code> parameters in any combination.<\/li>\n<li>A stored function must include a <code>RETURNS<\/code> clause in its definition before the routine. The clause specifies the data type for the function\u2019s returned value. Stored procedures do not support this clause.<\/li>\n<li>A stored function\u2019s routine must include a <code>RETURN<\/code> statement that specifies the function\u2019s returned value. The routine does not have to include any other statements, only the <code>RETURN<\/code> statement. If it does include other statements, only the <code>RETURN<\/code> statement can return a value.<\/li>\n<\/ul>\n<p>With these guidelines in mind, let\u2019s look at a simple example of a <code>CREATE<\/code> <code>FUNCTION<\/code> statement, which defines a stored function that converts pounds to kilograms:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">DELIMITER \/\/\r\nCREATE FUNCTION lbs_to_kg(lbs MEDIUMINT UNSIGNED)\r\nRETURNS MEDIUMINT UNSIGNED \r\nDETERMINISTIC\r\nBEGIN\r\n  RETURN (lbs * 0.45359237);\r\nEND\/\/\r\nDELIMITER ;<\/pre>\n<p>The function is named <code>lbs_to_kg<\/code> and includes one input parameter named <code>lbs<\/code>. You do not have to include a parameter when defining a function, but typically you\u2019ll want at least one. If you add more than one, you need to separate them with commas.<\/p>\n<p>The parameter definition is enclosed in parentheses and includes the parameter\u2019s data type, <code>MEDIUMINT<\/code> <code>UNSIGNED<\/code>. I chose this data type because I ultimately want to use the function for the <code>max_weight<\/code> column in the <code>airplanes<\/code> table, which is also defined with that data type.<\/p>\n<p>In addition, I used the <code>MEDIUMINT<\/code> <code>UNSIGNED<\/code> data type for the <code>RETURNS<\/code> clause. The clause specifies that the function\u2019s returned value should be an integer in the range permitted by this data type. I figured we were safe with this data type because one pound is equivalent to 0.45359237 kilograms, so the returned value would never exceed the maximum value in the <code>max_weight<\/code> column.<\/p>\n<p>If you want to support a greater range of values, you can instead use the <code>INT<\/code> or <code>BIGINT<\/code> data type for the <code>lbs<\/code> parameter and the <code>RETURNS<\/code> clause. This would provide you with more flexibility should you want to use the function to convert values that exceed those in the <code>max_weight<\/code> column.<\/p>\n<p>The <code>RETURNS<\/code> clause is followed by the <code>DETERMINISTIC<\/code> characteristic. A characteristic is one of several options that can be added to a function definition, each one affecting the function in a different way. For example, you can add a characteristic to indicate the routine language or to define the routine\u2019s nature. These are the same characteristics available to stored procedures.<\/p>\n<p>The <code>DETERMINISTIC<\/code> characteristic indicates that the function will return the same results for the same input parameter each time the function runs. By default, a function is considered nondeterministic unless specified otherwise. Using the <code>DETERMINISTIC<\/code> characteristic can help the optimizer make better execution plan choices. However, assigning the characteristic to a nondeterministic function could cause the optimizer to make incorrect choices.<\/p>\n<p>The function\u2019s routine comes after the listed characteristics. For this routine, I\u2019ve used the <code>BEGIN\u2026END<\/code> syntax to set up a compound statement, even though there is only one <code>RETURN<\/code> statement. Often your routine will include a compound statement\u2014a block of one or more SQL statements\u2014and I wanted to be sure you understood how to include them in your function definition. As with stored procedures, it\u2019s not uncommon for developers to use a compound statement, even if it includes only a single SQL statement.<\/p>\n<p>The <code>RETURN<\/code> statement defines a simple mathematic expression that multiples the <code>lbs<\/code> input parameter value by <code>0.45359237<\/code> to arrive at the number of kilograms for the specified weight. The result from this calculation is what is returned by the function when you run it.<\/p>\n<p>The preceding example also includes two <code>DELIMITER<\/code> statements that surround the function definition. The first <code>DELIMITER<\/code> statement changes the delimiter to double forward slashes (\/\/), and the second <code>DELIMITER<\/code> statement changes the delimiter back to a semi-colon (the default). As you saw in the previous article, this provides a way to pass the entire function definition to the server as a single statement.<\/p>\n<h2>Verifying a newly created stored function<\/h2>\n<p>After you run the <code>CREATE<\/code> <code>FUNCTION<\/code> statement, you can verify that it\u2019s been added to the <code>travel<\/code> database by viewing it in Navigator, as shown in Figure 1. (You might need to refresh Navigator to see the new function.)<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-94208\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/05\/word-image.png\" alt=\"An image showing the Navigator. The lbs_to_kg function can be seen in the Functions folder\" width=\"287\" height=\"644\" \/><\/p>\n<p class=\"caption\"><strong>Figure 1. Viewing the function in Navigator<\/strong><\/p>\n<p>From Navigator, you can open the function definition in the <em>Routine<\/em> tab by clicking the wrench icon next to the function name. Figure 2 shows the function definition on the <em>Routine<\/em> tab. The <code>CREATE<\/code> <code>FUNCTION<\/code> statement is nearly identical to what you created, except that it now includes the <code>DEFINER<\/code> clause after the <code>CREATE<\/code> keyword.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-94209\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/05\/word-image-1.png\" alt=\"An image showing the function definition in the dialog. CREATE DEFINER='root'@'localhost' FUNCTION 'lbs_to_kg'(lbs MEDIUMINT UNSIGNED) RETURNS mediumint unsigned DETERMINISTIC BEGIN RETURNS (lbs * 0.45359237); END\" width=\"1002\" height=\"517\" \/><\/p>\n<p class=\"caption\"><strong>Figure 2. Viewing the function definition on the <em>Routine<\/em> tab<\/strong><\/p>\n<p>As you saw with views and stored procedures, the <code>DEFINER<\/code> clause specifies which account has been designated as the object creator. I ran the <code>CREATE<\/code> <code>FUNCTION<\/code> statement when I was signed in under the root account on my local MySQL instance, so that\u2019s the username added to the definition. By default, MySQL uses the account of the user who runs the <code>CREATE<\/code> <code>PROCEDURE<\/code> statement, but you can specify a different account as long as it\u2019s been granted adequate permissions.<\/p>\n<p>You might have noticed that the function definition on the <em>Routine<\/em> tab does not include the <code>DELIMITER<\/code> statements or custom delimiter. However, if you were to update the definition and click <em>Apply,<\/em> Workbench would add those elements for you. (It would also add a <code>DROP<\/code> <code>PROCEDURE<\/code> statement that needs to run before the <code>CREATE<\/code> <code>FUNCTION<\/code> statement.)<\/p>\n<p>Another way you can verify that the function has been created is to query the <code>routines<\/code> view in the <code>INFORMATION_SCHEMA<\/code> database:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">SELECT * FROM information_schema.routines\r\nWHERE routine_schema = 'travel';<\/pre>\n<p>The results should include the <code>lbs_to_kg<\/code> function, along with any other stored functions or stored procedures that have been created in the database. If you\u2019re using the setup from the last article, your database might include the <code>get_plane_info<\/code> stored procedure.<\/p>\n<p>In the preceding example, I included a <code>WHERE<\/code> clause that limits the results to the <code>travel<\/code> database. However, you can further limit the results by also specifying the function name in the <code>WHERE<\/code> clause and by specifying which column or columns to return. For example, the following <code>SELECT<\/code> statement limits the results to the <code>routine_definition<\/code> column and to the <code>lbs_to_kg<\/code> function in the <code>travel<\/code> database:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">SELECT routine_definition\r\nFROM information_schema.routines\r\nWHERE routine_schema = 'travel'\r\n  AND routine_name = 'lbs_to_kg';<\/pre>\n<p>The statement should now return only a single value, although it might be difficult to read. As you saw with stored procedures, you can view a value in its entirety in a separate window. Right-click the value directly in the results and click <em>Open Value in Viewer<\/em>. MySQL launches a window that displays the value, as shown in Figure 3. (Select the <em>Text<\/em> tab if it\u2019s not already selected.)<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-94210\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/05\/word-image-2.png\" alt=\"An image showing the full definition of the function in a dialog. BEGIN RETURN (lbs * 0.45359237); END\" width=\"808\" height=\"515\" \/><\/p>\n<p class=\"caption\">Figure 3. Examining the function\u2019s routine body in Viewer<\/p>\n<p>As you can see, the window displays only the function\u2019s routine body, which in this case, is a compound statement that includes a <code>RETURN<\/code> statement.<\/p>\n<h2>Using a stored function in a MySQL query<\/h2>\n<p>After you verify that your function has been created, you should check that it works as expected. One way to do this is to create a simple <code>SELECT<\/code> statement that does nothing but call the <code>lbs_to_kg<\/code> function. For example, the following <code>SELECT<\/code> statement includes only a <code>SELECT<\/code> clause, and that clause contains only one expression:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">SELECT lbs_to_kg(132) AS max_kg;<\/pre>\n<p>The expression calls the <code>lbs_to_kg<\/code> function, passing in <code>132<\/code> as the parameter value. The expression also provides a name for the output column (<code>max_kg<\/code>). The statement should return a value of <code>60<\/code>.<\/p>\n<p>In many cases, you\u2019ll want to use your stored function for more than just running it in a simple <code>SELECT<\/code> statement. For example, you can use a stored function to transform or augment a column\u2019s values when retrieving data, which is what I\u2019ve done in the following <code>SELECT<\/code> statement:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">SELECT a.plane, max_weight AS max_lbs, \r\n  lbs_to_kg(max_weight) AS max_kg\r\nFROM airplanes a INNER JOIN manufacturers m\r\n  ON a.manufacturer_id = m.manufacturer_id\r\nWHERE m.manufacturer = 'airbus'\r\nORDER BY a.plane;<\/pre>\n<p>The statement joins the <code>airplanes<\/code> and <code>manufacturers<\/code> tables based on the <code>manufacturer_id<\/code> column in each table. The statement\u2019s <code>SELECT<\/code> clause includes an expression that uses the <code>lbs_to_kg<\/code> function to convert the <code>max_weight<\/code> column to kilograms and return a column named <code>max_kg<\/code>. The statement returns the results shown in Figure 4.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-94211\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/05\/word-image-3.png\" alt=\"An image showing the results of running the query. Four rows returned. Plane, max_lbs, max_kg. In each row the lbs has been translated into kg\" width=\"532\" height=\"166\" \/><\/p>\n<p class=\"caption\">Figure 4. Using the stored function in your query<\/p>\n<p>The results include the original weight (in pounds) in the <code>max_lbs<\/code> column and the weight in kilograms in the <code>max_kg<\/code> column after converting the <code>max_weight<\/code> values. By including both weights, you can quickly compare them to get a general sense of whether the function seems to be returning the expected results.<\/p>\n<h2>Updating a stored function in MySQL<\/h2>\n<p>As pointed out earlier, MySQL stored functions are similar to stored procedures in several ways. For example, they both support characteristics, input parameters, and the <code>DEFINER<\/code> clause. They\u2019re also similar in another important way. You can alter only the characteristics. You cannot change the routine or any other statement elements. Instead, you must first drop the function and then re-create it, incorporating any new elements.<\/p>\n<p>To drop a stored function, you can use the <code>DROP<\/code> <code>FUNCTION<\/code> statement, as shown in the following example:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">DROP FUNCTION IF EXISTS lbs_to_kg;<\/pre>\n<p>The <code>IF<\/code> <code>EXISTS<\/code> clause is optional, but it\u2019s a handy way to avoid generating errors when you try to drop a function. The clause can be particularly useful when you\u2019re developing your database schema and you\u2019re regularly updating the objects.<\/p>\n<p>After you\u2019ve dropped the function, you can modify the definition to meet your new requirements. For example, the following <code>CREATE<\/code> <code>FUNCTION<\/code> statement re-creates the <code>lbs_to_kg<\/code> function but this time adds a <code>DECLARE<\/code> statement and an <code>IF<\/code> construction to the compound statement:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk \">DELIMITER \/\/\r\nCREATE FUNCTION lbs_to_kg(lbs MEDIUMINT UNSIGNED)\r\nRETURNS VARCHAR(50) \r\nDETERMINISTIC\r\nBEGIN\r\n  DECLARE msg VARCHAR(50);\r\n  IF lbs &gt; 999999 THEN SET msg = \r\n    CONCAT(ROUND((lbs * 0.45359237), 0), \r\n            ' kg exceeds airport weight limits.');\r\n  ELSEIF lbs &gt;= 100000 AND lbs &lt;= 999999 THEN SET msg = \r\n    CONCAT(ROUND((lbs * 0.45359237), 0), \r\n            ' kg exceeds runway weight limits.');\r\n  ELSE SET msg = CONCAT(ROUND((lbs * 0.45359237), 0), \r\n            ' kg within weight limits.');\r\n  END IF;\r\n  RETURN msg;\r\nEND\/\/\r\nDELIMITER ;<\/pre>\n<p>The <code>DECLARE<\/code> statement declares the <code>msg<\/code> local variable and assigns it the <code>VARCHAR<\/code> data type. Notice that the <code>RETURNS<\/code> clause has also been updated to the <code>VARCHAR<\/code> data type to match the <code>msg<\/code> variable. The variable can then be used in the final <code>RETURN<\/code> statement to provide the function\u2019s output value.<\/p>\n<p>The compound statement also includes an <code>IF<\/code> statement. The statement starts with an initial conditional clause, which is followed by an <code>ELSEIF<\/code> clause and then an <code>ELSE<\/code> clause. Each clause implements the same logic based on the value of the <code>lbs<\/code> input parameter. If the <code>lbs<\/code> value falls within the specified range, the <code>msg<\/code> variable is set to a predefined value based on that range. (We\u2019ll be covering conditional statements in more detail later in the series.)<\/p>\n<p>The <code>msg<\/code> value is determined first by converting the <code>lbs<\/code> value to kilograms and then concatenating the results with a string (the message body). For example, if the <code>lbs<\/code> value is greater than 99999, the <code>msg<\/code> variable is set to the number of kilograms plus the message \u2018 kg exceeds the airport weight limits.\u2019<\/p>\n<p>To help carry out this logic, each conditional clause also includes two built-in functions: <code>ROUND<\/code> and <code>CONCAT<\/code>. The <code>ROUND<\/code> function rounds the calculated kilograms to a whole number, and the <code>CONCAT<\/code> function concatenates the rounded kilograms with the specified text. For example, if the weight in pounds is 120,000, the <code>IF<\/code> statement will set the <code>msg<\/code> variable to \u201854431 kg exceeds runway weight limits.\u2019 You can see this for yourself by running the following <code>SELECT<\/code> statement:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">SELECT lbs_to_kg(120000) AS max_kg;<\/pre>\n<p>The statement should return the results shown in Figure 5.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-94212\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/05\/word-image-4.png\" alt=\"An image showing the results of the query. max_kg 54431 kg exceeds the runway weight limits\" width=\"519\" height=\"124\" \/><\/p>\n<p class=\"caption\">Figure 5. Viewing the results returned by the updated stored function<\/p>\n<p>You can also use the <code>lbs_to_kg<\/code> function in a more elaborate <code>SELECT<\/code> statement, just like you did earlier:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">SELECT m.manufacturer, a.plane, \r\n  max_weight AS max_lbs, \r\n  lbs_to_kg(max_weight) AS max_kg\r\nFROM airplanes a INNER JOIN manufacturers m\r\n  ON a.manufacturer_id = m.manufacturer_id\r\nORDER BY m.manufacturer, a.plane;<\/pre>\n<p>Now each returned row includes one of the three messages in the <code>max_kg<\/code> column. The message is based on the number of pounds in the <code>max_weight<\/code> column, which is passed to the function through its parameter. Figure 6 shows the results returned by the <code>SELECT<\/code> statement.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-94213\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/05\/word-image-5.png\" alt=\"An image showing the results of the query. One row for each plan is returned with the appropriate message that translated lbs to kg and then either exceeds runway weight limits, exceeds airport weight limits or within weight limits\" width=\"670\" height=\"256\" \/><\/p>\n<p class=\"caption\">Figure 6. Using a stored function in your query expressions<\/p>\n<p>Your function can, of course, include a much more complex routine than what I\u2019ve done here, but these examples should be enough to give you a sense of what you can do with stored functions and how valuable they can be when building your queries.<\/p>\n<h2>Altering a stored function in MySQL<\/h2>\n<p>As I mentioned earlier, the only elements of a stored function definition that you can alter are the characteristics. For this, you can use an <code>ALTER<\/code> <code>FUNCTION<\/code> statement. For example, the following statement adds a <code>COMMENT<\/code> characteristic and <code>SQL<\/code> <code>SECURITY<\/code> characteristic:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">ALTER FUNCTION lbs_to_kg\r\nCOMMENT 'converts weight to kilograms and generates message'\r\nSQL SECURITY INVOKER;<\/pre>\n<p>The <code>COMMENT<\/code> characteristic simply adds a comment that describes the function\u2019s purpose. The <code>SQL<\/code> <code>SECURITY<\/code> characteristic instructs MySQL to run the routine under the security context of the user account that invokes the function rather than using the definer account (the default behavior).<\/p>\n<p>After you run the <code>ALTER<\/code> <code>FUNCTION<\/code> statement, you can verify that the characteristics have been added by viewing the function definition on the <em>Routine<\/em> tab, as shown in Figure 7.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-94214\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/05\/word-image-6.png\" alt=\"An images showing the new function definition in a dialog \" width=\"1073\" height=\"625\" \/><\/p>\n<p class=\"caption\">Figure 7. Viewing the stored function definition on the <em>Routine<\/em> tab<\/p>\n<p>The <code>CREATE<\/code> <code>PROCEDURE<\/code> statement now includes three characteristics\u2014the one you added originally and the two you added when you ran the <code>ALTER<\/code> <code>FUNCTION<\/code> statement.<\/p>\n<h2>Working with stored functions in MySQL<\/h2>\n<p>Although stored functions are similar to stored procedures, they serve a distinctly different purpose: to return a value that can be used by an expression during its evaluation. For this reason, stored functions can be extremely useful and are well worth adding to your arsenal of tools, especially since they\u2019re so easy to create and execute. However, they can also impact performance if not carefully implemented. For example, if your query returns thousands of rows of data, an overly complex function could bring your system to its knees as MySQL tries to apply the logic to each row. When used properly, however, stored functions can be incredibly beneficial, especially as you become more adept at building SQL queries.<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Stored functions in MySQL return a scalar value and can be used in a SQL statement. In this article, Robert Sheldon explains how to create and use a MySQL stored function.&hellip;<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,145792],"tags":[95506],"coauthors":[6779],"class_list":["post-94207","post","type-post","status-publish","format-standard","hentry","category-featured","category-mysql","tag-automate"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/94207","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=94207"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/94207\/revisions"}],"predecessor-version":[{"id":94219,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/94207\/revisions\/94219"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=94207"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=94207"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=94207"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=94207"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}