导航
  • 主页
  • ASP
  • .NET Framework
  • Web Services
  • VB
  • VC
  • 图表区
  • 分析与设计
  • 组件/控件开发
  • LINQ
  • 问答

顶不住了,通过.net创建mysql存储过程

aierduo 2010-05-23 04:00:42
需要在程序里面创建一个全新mysql数据库,我创建数据库的流程是这样的
1,创建新的数据库
2,创建表以及试图
3,插入数据
4,创建存储过程和自定义函数

上面这些sql语句都是从文本中读取出来的,但是在创建存储过程的时候,死活创建不上,
其中有一个存储过程如下:

DROP PROCEDURE IF EXISTS `p_check_order`;
CREATE PROCEDURE `p_check_order`(IN _member_id VARCHAR(50)
,IN _match_id INT
,IN _game_id INT
,IN _other_game_id INT
,IN _discount_type INT
,IN _discount DECIMAL(15,3)
,IN _betting_type VARCHAR(50)
,IN _head_number VARCHAR(50)
,IN _order_amount DECIMAL(15,5)
,IN _horse_number VARCHAR(300)
,IN _order_type CHAR(1)
,IN _order_count INT
,OUT result_value VARCHAR(300))
L_END:BEGIN
DECLARE flag_failed CHAR(2) DEFAULT '0|';
DECLARE flag_succeed CHAR(2) DEFAULT '1|';
DECLARE flag_validity CHAR(2) DEFAULT '2|';
DECLARE flag_credit CHAR(2) DEFAULT '3|';
DECLARE flag_limit CHAR(2) DEFAULT '4|';
DECLARE flag_member_status CHAR(3) DEFAULT '5|1';
DECLARE flag_match CHAR(3) DEFAULT '6|1';
DECLARE flag_retire CHAR(2) DEFAULT '7|';
DECLARE flag_discount_type CHAR(2) DEFAULT '8|';
DECLARE flag_discount CHAR(2) DEFAULT '9|';
DECLARE single_order_data VARCHAR(10);
DECLARE data_len INT DEFAULT 0;
DECLARE data_len2 INT DEFAULT 0;
DECLARE _index INT DEFAULT 0;
DECLARE _index2 INT DEFAULT 0;
DECLARE split1 CHAR(1) DEFAULT ';';
DECLARE split2 CHAR(1) DEFAULT '|';
DECLARE split4 CHAR(1) DEFAULT '@';
DECLARE split5 CHAR(1) DEFAULT '#';
DECLARE split6 CHAR(1) DEFAULT ',';
DECLARE game_id_ly CHAR(1) DEFAULT '1';
DECLARE game_id_wz CHAR(1) DEFAULT '2';
DECLARE game_id_wp CHAR(1) DEFAULT '3';
DECLARE game_id_dy CHAR(1) DEFAULT '4';
DECLARE temp_game CHAR(1);

DECLARE retval VARCHAR(100);

DECLARE _number VARCHAR(10);
DECLARE order_amount_all DECIMAL(15,5);
DECLARE new_discount DECIMAL(15,3);
DECLARE _draw_number VARCHAR(10);
SET _draw_number = f_get_draw_number(_match_id);
SET order_amount_all = _order_count * _order_amount;


IF _order_type = 'Z' THEN
IF f_check_member_status(_member_id) = 0 THEN
SET result_value = flag_member_status;
LEAVE L_END;
END IF;
ELSEIF _order_type = 'B' THEN
IF f_check_user_status(_member_id) = 0 THEN
SET result_value = flag_member_status;
LEAVE L_END;
END IF;
END IF;
IF f_check_match_status(_draw_number) = 0 THEN
SET result_value = flag_match;
LEAVE L_END;
END IF;
IF _order_type = 'Z' THEN
SET retval = f_check_member_credit(_member_id,order_amount_all);
IF retval != '1' THEN
SET result_value = CONCAT(flag_credit,retval);
LEAVE L_END;
END IF;
END IF;
IF _order_type = 'Z' THEN
SET retval = f_check_limit

(_member_id,_draw_number,_game_id,_discount_type,_horse_number,_order_amount);
ELSEIF _order_type = 'B' THEN
SET retval = f_check_user_limit

(_member_id,_draw_number,_game_id,_discount_type,_horse_number,_order_amount);
END IF;
IF retval != '1' THEN
SET result_value = CONCAT(flag_limit,retval);
LEAVE L_END;
END IF;

SET new_discount = f_get_discount(_draw_number,_game_id,_discount_type);
IF new_discount != _discount THEN
IF _other_game_id = game_id_wp THEN
IF _game_id = game_id_dy THEN

SET result_value = CONCAT(flag_discount,_horse_number,split5,new_discount,split6
,f_get_discount(_draw_number,game_id_wz,_discount_type),split4);
ELSE
SET result_value = CONCAT(flag_discount,_horse_number,split5
,f_get_discount(_draw_number,game_id_dy,_discount_type),split6,new_discount,split4);
END IF;
ELSE
SET result_value = CONCAT(flag_discount,_horse_number,split5,new_discount,split4);
END IF;

LEAVE L_END;
END IF;

IF _game_id = game_id_ly THEN
SET _index = 0;
WHILE _index < _order_count DO
SET _index = _index + 1;
SET single_order_data = f_get_split_string(_horse_number,split4,_index);
IF TRIM(single_order_data) != '' THEN
SET data_len2 = f_get_split_string_total(single_order_data,' ') + 1;
WHILE _index2 < data_len2 DO
SET _index2 = _index2 + 1;
SET _number = f_get_split_string(single_order_data,' ',_index2);
IF f_check_stop_horse(_draw_number,_number) = 1 THEN
SET result_value = CONCAT(flag_retire,_number,split4);
LEAVE L_END;
END IF;
END WHILE;
CALL p_check_discount_status(_draw_number
,_game_id
,_horse_number
,_discount_type
,@flag
,@new_discount_type
,@discount);
IF @flag = 0 THEN
SET result_value = CONCAT

(flag_discount_type,_horse_number,split5,@new_discount_type,split5,@discount,split4);
LEAVE L_END;
END IF;
END IF;
END WHILE;
ELSE
IF f_check_stop_horse(_draw_number,_horse_number) = 1 THEN
SET result_value = CONCAT(flag_retire,_horse_number,split4);
LEAVE L_END;
END IF;
CALL p_check_discount_status(_draw_number
,_game_id
,_horse_number
,_discount_type
,@flag
,@new_discount_type
,@discount);
IF @flag = 0 THEN
SET result_value = CONCAT

(flag_discount_type,_game_id,split5,_horse_number,split5,@new_discount_type,split5,@discount,split4);
LEAVE L_END;
END IF;
END IF;
SET result_value = CONCAT(flag_succeed,_horse_number,split4);
END ;


=========================
首先,不是DELIMITER 没定义的错误,在通过ExecuteNonQuery创建存储过程的时候不需要定义的(我创建一个简单的存储过程的时候测试过)

捕捉到的错误是:Fatal error encountered during command execution.
那位高手能帮忙看看,不胜感激。数据库版本是5.0.45-community-nt。

另外,这个存储过程在工具里是能执行的,不会是存储过程本身的错。
...全文
113 点赞 收藏 2
写回复
2 条回复
切换为时间正序
请发表友善的回复…
发表回复
wuyq11 2010-05-23
到数据库开发 MySQL区问问
回复
aierduo 2010-05-23
没人气啊,晕倒,有人帮看看吗?
回复
发动态
发帖子
.NET技术社区
创建于2007-09-28

5.8w+

社区成员

.NET技术交流专区
申请成为版主
社区公告
暂无公告