創(chuàng)建存儲過程：
語法：
CREATE PROCEDURE p()
BEGIN
END
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(pro_price) AS priceaverage
FROM products;
END;
# begin…end之間是存儲過程的主體定義
# mysql的分界符是分號（；）
調(diào)用存儲過程的方法是：
# CALL加上過程名以及一個括號
# 例如調(diào)用上面定義的存儲過程
CALL productpricing();
# 哪怕是不用傳遞參數(shù)，存儲過程名字后面的括號“（）”也是必須的
刪除存儲過程的方法是：
DROP PROCUDURE productpricing;
創(chuàng)建帶參數(shù)的存儲過程：
CREATE PROCUDURE productpricing(
OUT p1 DECIMAL(8,2),
OUT ph DECIMAL(8,2),
OUT pa DECIMAL(8,2)
)
BEGIN
SELECT Min(prod_price) INTO pl FROM products;
SELECT Max(prod_price) INTO ph FROM products;
SELECT Avg(prod_price) INTO pa FROM products;
END;
# DECIMAL用于指定參數(shù)的數(shù)據(jù)類型
# OUT用于表明此值是用于從存儲過程里輸出的
# MySQL支持 OUT, IN, INOUT
調(diào)用帶參數(shù)的存儲過程：
CALL productpricing(@pricelow,
@pricehigh,
@priceaverage);
# 所有的參數(shù)必須以@開頭
# 要想獲取@priceaverage的值，用以下語句
SELECT @priceaverage;
# 獲取三個的值，用以下語句
SELECT @pricehigh, @pricelow, @priceaverage;
另一個帶IN和OUT參數(shù)的存儲過程：
CREATE PROCEDURE ordertotal(
IN onumber INT,
OUT ototal DECIMAL(8,2)
)
BEGIN
SELECT Sum(item_price*quantity)
FROM orderitems
WHERE order_num = onumber
INTO ototal;
END;
CALL ordertotal(20005, @total);
SELECT @total;
添加一個完整的例子這是一個自定義分頁的存儲過程)
DELIMITER $
DROP PROCEDURE IF EXISTS `dbcall`.`get_page`$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_page`(
tableName varchar(100),
fieldsNames varchar(100),
pageIndex int,
pageSize int,
sortName varchar(500),
strWhere varchar(500)
)
BEGIN
DECLARE fieldlist varchar(200);
if fieldsNames=''||fieldsNames=null THEN
set fieldlist='*';
else
set fieldlist=fieldsNames;
end if;
if strWhere=''||strWhere=null then
if sortName=''||sortName=null then
set @strSQL=concat('SELECT ',fieldlist,' FROM ',tableName,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
else
set @strSQL=concat('SELECT ',fieldlist,' FROM ',tableName,' ORDER BY ',sortName,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
end if;
else
if sortName=''||sortName=null then
set @strSQL=concat('SELECT ',fieldlist,' FROM ',tableName,' WHERE ',strWhere,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
else
set @strSQL=concat('SELECT ',fieldlist,' FROM ',tableName,' WHERE ',strWhere,' ORDER BY ',sortName,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
end if;
end if;
PREPARE stmt1 FROM @strSQL;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END$
DELIMITER ;
例子：統(tǒng)計一個月以內(nèi)，每個支付金額在每天中的數(shù)量。
比如說： 有一天， 有10000個交易，其中有1000個充值的金額為100等，就是要統(tǒng)計每天中每個交易額的數(shù)量。
DELIMITER //
DROP PROCEDURE IF EXISTS test.GetAllProducts//
CREATE PROCEDURE test.GetAllProducts(IN start_time varchar(20),IN end_time varchar(20))
BEGIN
DECLARE i,num int default 0;
SET @condition = '';
SELECT count(distinct(amount)) into num from trade_log;
WHILE i < num DO
-- select i;
set @index = i;
set @tmp = null;
PREPARE stnm FROM 'SELECT distinct(amount) into @tmp from trade_log limit ?,1';
execute stnm using @index;
-- select @tmp;
set @condition = concat(@condition,"sum(if(amount = ",@tmp,",1,0)) as m",@tmp,",");
-- select @condition;
set i = i + 1;
END WHILE;
-- SET condition = SUBSTRING(@condition,1,LENGTH(@condition)-1);
SET @start = start_time;
SET @end = end_time;
SET @where = concat('create_time >= UNIX_TIMESTAMP("',@start,'") and create_time <= UNIX_TIMESTAMP("',@end,'") group by time');
SET @s = concat('select ',@condition,"FROM_UNIXTIME(create_time,'%Y-%m-%d') as time from trade_log where ",@where);
PREPARE stmt FROM @s;
EXECUTE stmt;
END //
DELIMITER ;
使用過程函數(shù)：
set @start = "2010-8-17";
set @end = "2010-9-17";
call GetAllProducts(@start,@end);/////測試表結(jié)構(gòu)+------+------+------+------+------+------+------+------+------+------+------------+
| m50 | m200 | m100 | m10 | m400 | m240 | m55 | m110 | m500 | m480 | time |
+------+------+------+------+------+------+------+------+------+------+------------+
| 0 | 0 | 4 | 2 | 0 | 9 | 2 | 5 | 0 | 9 | 2010-08-17 |
| 0 | 0 | 0 | 0 | 1 | 2 | 2 | 5 | 0 | 4 | 2010-08-18 |
