MySQL–HowtoCreateStoredProcedureinMySQL_MySQL

来源：动视网责编：小采时间：2020-11-09 19:13:39

MySQL–HowtoCreateStoredProcedureinMySQL_MySQL

MySQL–HowtoCreateStoredProcedureinMySQL_MySQL:MySQL supports Stored Procedures which can execute set of codes by applying relevant business logics. In this post we will see how to create a stored procedure and execute it.Let us create the following tablesCREATE TABLE items(item_id INT,

推荐度：

点击下载本文 文档为doc格式

导读MySQL–HowtoCreateStoredProcedureinMySQL_MySQL:MySQL supports Stored Procedures which can execute set of codes by applying relevant business logics. In this post we will see how to create a stored procedure and execute it.Let us create the following tablesCREATE TABLE items(item_id INT,

MySQL supports Stored Procedures which can execute set of codes by applying relevant business logics. In this post we will see how to create a stored procedure and execute it.

Let us create the following tables

CREATE TABLE items(item_id INT, item_description VARCHAR(100));
 CREATE TABLE sales(sales_id INT auto_increment KEY,item_id INT, sales_date DATETIME, sales_amount DECIMAL(12,2));
 INSERT INTO items VALUES (1,'Television');
 INSERT INTO items VALUES (2,'Mobile');
 INSERT INTO items VALUES (3,'laptop');
 INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-01-01',1200);
 INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-01-02',200);
 INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-01-09',1700);
 INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-01-29',1700);
 INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-02-11',1700);
 INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-02-16',1200);
 INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-16',200);
 INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-20',200);
 INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-20',200);
 INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-22',200);
 INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-02-24',1700);
 INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-02-24',1200);

Suppose you want to create a stored procedure which will accept item_description and returns the total sales_amount for theenireperiod, you can do it as shown below

DELIMITER $$
 CREATE PROCEDURE Test.usp_get_sales
 (
 param_item_description VARCHAR(100)
 )
 BEGIN
 SELECT item_description,SUM(sales_amount) AS sales_amount FROM items NATURAL
 JOIN sales
 WHERE item_description=param_item_description
 GROUP BY item_description;
 END;
 $$
 DELIMITER;

Note that the creation of the stored procedure starts with setting the Delimiter $$. The default delimiter for MySQL statements are semicolon so in order to instruct the MySQL engine about the start and end of the stored procedure block, you need to use a different delimiter (which in this case $$ is used).

Now you can execute a stored procedure usingCALL keywordas shown below.

Execution 1

CALL usp_get_sales('Television');

When you execute the above code, the result is

Item_description sales_amountTelevision 3600.00

Execution 2

CALL usp_get_sales('laptop');

When you execute the above code, the result is

Item_description sales_amountlaptop 6800.00

Note:The parameters do not start with @ like we use in SQL Server. So in order todifferentiatebetween the actual column name and parameter name, the name param_item_description is used. You may need to use different naming conventions as you like.

I have previously written a similar article here: MySQL – How to Create Stored Procedure.

Reference:Pinal Dave (http://blog.sqlauthority.com)

MySQL–HowtoCreateStoredProcedureinMySQL_MySQL

推荐度：

点击下载本文 文档为doc格式

标签： in mysql create

热门焦点

MySQL–HowtoCreateStoredProcedureinMySQL_MySQL

MySQL–HowtoCreateStoredProcedureinMySQL_MySQL

MySQL–HowtoCreateStoredProcedureinMySQL_MySQL

最新推荐

猜你喜欢

热门推荐