MySQL存储过程，并在.net中调用

来源：互联网发布：json数据怎么用编辑：程序博客网时间：2024/06/03 14:15

MySQL 存储过程是从 MySQL 5.0 开始增加的新功能。

存储过程最主要优点是执行效率和SQL 代码封装。

相对于SQL Server和Oracle存储过程写法大体类似，不过有些特殊语法要注意。

最简洁的语法结构如下:

DELIMITER $$ CREATE    PROCEDURE `dbbook`.`test`()  //在dbbook数据中创建名为test的存储过程    BEGIN    END$$DELIMITER ;

修改存储过程:

DELIMITER $$USE `benq_ebook`$$DROP PROCEDURE IF EXISTS `user_buy_analyze`$$CREATE DEFINER=`root`@`%` PROCEDURE `user_buy_analyze`(IN antype VARCHAR (10),IN datefrom VARCHAR (10),IN dateto VARCHAR (50))BEGIN  DECLARE sumuser INT ;  IF antype!='rec'  THEN    SET datefrom= CONCAT(datefrom,' 00:00:00');  SET dateto= CONCAT(dateto,' 23:59:59');   SELECT          COUNT(*) INTO sumuser        FROM         user_profile AS u        WHERE u.uid IN          (SELECT            lo.uid         FROM          log_order AS lo         INNER JOIN log_order_book AS lob              ON lo.orderNo = lob.orderNo              AND lob.goods_type != '08'             AND lob.b_pay_status=1            AND lo.o_est_datetime BETWEEN datefrom AND dateto);    END IF;    CASE    antype -- 性别百分比    WHEN 'sex'     THEN     BEGIN            SELECT         CASE u.sex           WHEN 'F'           THEN N'女'           WHEN 'M'           THEN N'男'           ELSE N'其他'         END AS sex,        u.sex  AS sexid,        (COUNT(*) / sumuser)*100  AS sexpercent       FROM        user_profile AS u       WHERE u.uid IN         (SELECT           lo.uid         FROM          log_order AS lo          INNER JOIN log_order_book AS lob             ON lo.orderNo = lob.orderNo             AND lob.goods_type != '08'            AND lob.b_pay_status=1            AND lo.o_est_datetime BETWEEN datefrom AND dateto)       GROUP BY u.sex       ORDER BY u.sex ;    END ;    -- 婚姻百分比    WHEN 'mar'     THEN     BEGINSELECT         CASE          u.marriage           WHEN 1           THEN N'未婚'           WHEN 2           THEN N'已婚，無小孩'           WHEN 3          THEN N'已婚，有小孩'          ELSE N'其他'         END AS mar,        IFNULL(u.marriage,0)  AS marid,        (COUNT(*) / sumuser)*100 AS marpercent       FROM        user_profile AS u       WHERE u.uid IN         (SELECT           lo.uid         FROM          log_order AS lo           INNER JOIN log_order_book AS lob             ON lo.orderNo = lob.orderNo             AND lob.goods_type != '08'             AND lob.b_pay_status=1            AND lo.o_est_datetime BETWEEN datefrom AND dateto)       GROUP BY u.marriage       ORDER BY u.marriage ;            END;    -- 教育    WHEN 'edu'     THEN     BEGIN      SELECT         CASE          u.education          WHEN 1           THEN N'國中'           WHEN 2           THEN N'高中(職)'           WHEN 3          THEN N'大學'          WHEN 4          THEN N'碩士'          WHEN 5         THEN N'博士'          ELSE N'其他'         END AS edu,        IFNULL(u.education,0) AS eduid,        (COUNT(*)/ sumuser)*100 AS edupercent       FROM        user_profile AS u       WHERE u.uid IN         (SELECT           lo.uid         FROM          log_order AS lo           INNER JOIN log_order_book AS lob             ON lo.orderNo = lob.orderNo             AND lob.goods_type != '08'             AND lob.b_pay_status=1            AND lo.o_est_datetime BETWEEN datefrom AND dateto)       GROUP BY u.education       ORDER BY u.education;    END ;        -- 职业    WHEN 'pro'     THEN     BEGIN     SELECT         CASE          u.profession          WHEN 1           THEN N'學生'           WHEN 2           THEN N'科技業'           WHEN 3          THEN N'服務業'          WHEN 4          THEN N'製造業'          WHEN 5          THEN N'批發零售'           WHEN 6          THEN N'文創'           WHEN 7          THEN N'軍公教'          WHEN 8          THEN N'醫療'          WHEN 9          THEN N'其他'         ELSE N'(其他)'         END AS pro,        IFNULL(u.profession,0) AS proid,        (COUNT(*)/ sumuser)*100 AS propercent       FROM        user_profile AS u       WHERE u.uid IN         (SELECT           lo.uid         FROM          log_order AS lo           INNER JOIN log_order_book AS lob             ON lo.orderNo = lob.orderNo             AND lob.goods_type != '08'             AND lob.b_pay_status=1            AND lo.o_est_datetime BETWEEN datefrom AND dateto)       GROUP BY u.profession       ORDER BY u.profession;    END ;        -- 购买的书种    WHEN 'rec'     THEN     BEGIN                  SELECT  COUNT(c.class_name) INTO sumuserFROM log_order_book AS lob  INNER JOIN log_order AS lo   ON lo.orderNo = lob.orderNo  INNER JOIN book_class bc    ON lob.book_seq = bc.book_seq INNER JOIN class c    ON c.class_seq = bc.class_seq    AND lob.goods_type != '08'     AND lob.b_pay_status=1   AND c.class_name IN (     '商業理財',     '文學小說',     '藝術設計'     '心靈養生',     '休閒娛樂',     '圖文漫畫')     AND lo.o_est_datetime BETWEEN datefrom AND dateto;      SELECT  c.class_name AS rec, IFNULL(c.class_seq,0) AS recid, (COUNT(c.class_name)/sumuser)*100 AS recpercentFROM log_order_book AS lob  INNER JOIN log_order AS lo   ON lo.orderNo = lob.orderNo  INNER JOIN book_class bc    ON lob.book_seq = bc.book_seq INNER JOIN class c    ON c.class_seq = bc.class_seq    AND lob.goods_type != '08'     AND lob.b_pay_status   AND c.class_name IN (     '商業理財',     '文學小說',     '藝術設計',     '心靈養生',     '休閒娛樂',     '圖文漫畫')     AND lo.o_est_datetime BETWEEN datefrom AND datetoGROUP BY c.class_name;    END ;  END CASE ;END$$DELIMITER ;

调用存储过程:

CALL user_buy_analyze('edu','2011-01-01','2012-09-21')

注意事项:

MySQL 存储过程参数如果不显式指定“in”、“out”、“inout”，则默认为“in”。习惯上，对于是“in” 的参数，我们都不会显式指定。
MySQL 存储过程不需要在 procedure body 前面加 “as”。而 SQL Server 存储过程必须加 “as” 关键字。
不能在 MySQL 存储过程中使用。return 只能出现在函数中。
如果 MySQL 存储过程中包含多条 MySQL 语句，则需要 begin end; 关键字。
MySQL 存储过程中的每条语句的末尾，都要加上分号 “;”
调用 MySQL 存储过程时候，需要在过程名字后面加“()”，即使没有一个参数，也需要“()”
因为 MySQL 存储过程参数没有默认值，所以在调用 MySQL 存储过程时候，不能省略参数。可以用 null 来替代。
MySQL 存储过程中的变量，不需要在变量名字前加“@”，虽然 MySQL 客户端用户变量要加个“@”。

ASP.NET中访问MySQL存储过程:

using MySql.Data.MySqlClient;using System.Data.SqlClient;public DataSet GetDataSet(string type,string DateFrom,string DateTo)        {            string connection = eBookConnection.connectionMySql;            MySqlConnection myCon = new MySqlConnection(eBookConnection.connectionMySql);            myCon.Open();            DataTable dt = new DataTable();            try            {                MySqlDataAdapter command = new MySqlDataAdapter();                DataSet ds = new DataSet();                command.SelectCommand = new MySqlCommand();                command.SelectCommand.Connection = myCon;                command.SelectCommand.CommandText = "user_buy_analyze";                command.SelectCommand.CommandType = CommandType.StoredProcedure;                MySqlParameter antype_para = new MySqlParameter("?antype", MySqlDbType.VarChar, 10);//mysql的存储过程参数是以?打头的                 MySqlParameter datefrom_para = new MySqlParameter("?datefrom", MySqlDbType.VarChar, 50);                MySqlParameter dateto_para = new MySqlParameter("?dateto", MySqlDbType.VarChar, 50);                antype_para.Value = type;                datefrom_para.Value = DateFrom;                dateto_para.Value = DateTo;                command.SelectCommand.Parameters.Add(antype_para);                command.SelectCommand.Parameters.Add(datefrom_para);                command.SelectCommand.Parameters.Add(dateto_para);                  command.Fill(ds);                return ds;            }            catch (Exception e)            {                throw e;            }            finally            {                myCon.Close();            }        }