简单的判断插入存储过程

Question

目前一个项目要用到存储过程，以前写的是sqlserver现在用mysql不会写，请指点一下，要求详细点。应该注意那些？

Answer 1

MYSQL与MS SQL在语法上很近似。 你可以参考一下MYSQL的官方手册中的例子和语法说明。MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html

Answer 2

1. 注意写成要转化结束符 
delimiter $$ --改变语句结束符，因为存储过程里面每句语句结束都要用;
create procedure ..()
as
begin 
...
end;
$$delimiter ;  -----这里转回来2.调用存储过程 用CALL关键字 不是exec 而且记住 无论有没参数 一定记得加括号
CALL SPnAME ()具体你还是要看官方帮助文档的

Answer 3

http://www.360doc.com/content/10/0302/00/21705_17263989.shtml
这个自己看看吧~

Answer 4

明天吧我写的发给大侠们看看 我在Navicat for MySQL  这个工具里面用的SQL语句创建的 保存时就出错。
那个存储过程在公司电脑上面，小弟先在这里谢谢，大侠们明天记得来看看我哪里写错了。很急....

Answer 5

不要用图形工具，直接用mysql.exe 行命令工具，这样操作比较容易，错误信息你也可以直接贴出来。

Answer 6

代码就在这里了 麻烦看下哪里错了！delimiter //create procedure seller_clearing (in order_id int,in money_paid decimal,in seller_id int,in add_time date)begin
declare seller_no,seller_pid,seller_ppid,commission_rate int;
set seller_no= ( select cate_no from b2c_tbl_seller where seller_id=(select seller_id from b2c_tbl_order_info where order_id=2369));
if(seller_no=1 or seller_no=5)then
insert into b2c_tbl_commission_settlements(seller_id,commission_amount,settlements_lap_start_date,commission_rate,is_balanced) values(selller_id,money_paid*0.25,add_time,25,0);
end if;
if(seller_no=2 or seller_no=4)then
set seller_pid=(select parent_id from b2c_seller_id where seller_id=seller_id);
if(seller_ppid != 0)then
insert into b2c_tbl_commission_settlements(seller_id,commission_amount,settlements_lap_start_date,commission_rate,is_balanced) values(selller_pid,money_paid*0.2,add_time,20,0);
insert into b2c_tbl_commission_settlements(seller_id,commission_amount,settlements_lap_start_date,commission_rate,is_balanced) values(selller_id,money_paid*0.05,add_time,5,0);
end if;
end if;
if(seller_no=3)then
set seller_pid=(select parent_id from b2c_seller_id where seller_id=seller_id);
set seller_ppid=(select parent_id from b2c_seller_id where seller_id=seller_pid);
if(seller_pid!=0 and seller_ppid!=0)then
insert into b2c_tbl_commission_settlements(seller_id,commission_amount,settlements_lap_start_date,commission_rate,is_balanced) values(selller_id,money_paid*0.1,add_time,10,0);
insert into b2c_tbl_commission_settlements(seller_id,commission_amount,settlements_lap_start_date,commission_rate,is_balanced) values(selller_pid,money_paid*0.1,add_time,10,0);
insert into b2c_tbl_commission_settlements(seller_id,commission_amount,settlements_lap_start_date,commission_rate,is_balanced) values(selller_ppid,money_paid*0.05,add_time,5,0);
end if
if(seller_pid!=0 and seller_ppid=0)then
insert into b2c_tbl_commission_settlements(seller_id,commission_amount,settlements_lap_start_date,commission_rate,is_balanced) values(selller_id,money_paid*0.1,add_time,10,0);
insert into b2c_tbl_commission_settlements(seller_id,commission_amount,settlements_lap_start_date,commission_rate,is_balanced) values(selller_pid,money_paid*0.1,add_time,10,0);
end if;
if(seller_pid=0)then
insert into b2c_tbl_commission_settlements(seller_id,commission_amount,settlements_lap_start_date,commission_rate,is_balanced) values(selller_id,money_paid*0.1,add_time,10,0);
end if;
end if;end;// 

Answer 7

错误信息是：   1064-you have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if(seller_pid !=0 and seller_ppid=0)then insert into b2c_tbl_commission_settleme' at line 24

Answer 8

直接复制执行下面的吧，你的少了个；delimiter //create procedure seller_clearing (in order_id int,in money_paid decimal,in seller_id int,in add_time date)begin
declare seller_no,seller_pid,seller_ppid,commission_rate int;
set seller_no= ( select cate_no from b2c_tbl_seller where seller_id=(select seller_id from b2c_tbl_order_info where order_id=2369));
if(seller_no=1 or seller_no=5)then
insert into b2c_tbl_commission_settlements(seller_id,commission_amount,settlements_lap_start_date,commission_rate,is_balanced) values(selller_id,money_paid*0.25,add_time,25,0);
end if;
if(seller_no=2 or seller_no=4)then
set seller_pid=(select parent_id from b2c_seller_id where seller_id=seller_id);
if(seller_ppid != 0)then
insert into b2c_tbl_commission_settlements(seller_id,commission_amount,settlements_lap_start_date,commission_rate,is_balanced) values(selller_pid,money_paid*0.2,add_time,20,0);
insert into b2c_tbl_commission_settlements(seller_id,commission_amount,settlements_lap_start_date,commission_rate,is_balanced) values(selller_id,money_paid*0.05,add_time,5,0);
end if;
end if;
if(seller_no=3)then
set seller_pid=(select parent_id from b2c_seller_id where seller_id=seller_id);
set seller_ppid=(select parent_id from b2c_seller_id where seller_id=seller_pid);
if(seller_pid!=0 and seller_ppid!=0)then
insert into b2c_tbl_commission_settlements(seller_id,commission_amount,settlements_lap_start_date,commission_rate,is_balanced) values(selller_id,money_paid*0.1,add_time,10,0);
insert into b2c_tbl_commission_settlements(seller_id,commission_amount,settlements_lap_start_date,commission_rate,is_balanced) values(selller_pid,money_paid*0.1,add_time,10,0);
insert into b2c_tbl_commission_settlements(seller_id,commission_amount,settlements_lap_start_date,commission_rate,is_balanced) values(selller_ppid,money_paid*0.05,add_time,5,0);
end if;
if(seller_pid!=0 and seller_ppid=0)then
insert into b2c_tbl_commission_settlements(seller_id,commission_amount,settlements_lap_start_date,commission_rate,is_balanced) values(selller_id,money_paid*0.1,add_time,10,0);
insert into b2c_tbl_commission_settlements(seller_id,commission_amount,settlements_lap_start_date,commission_rate,is_balanced) values(selller_pid,money_paid*0.1,add_time,10,0);
end if;
if(seller_pid=0)then
insert into b2c_tbl_commission_settlements(seller_id,commission_amount,settlements_lap_start_date,commission_rate,is_balanced) values(selller_id,money_paid*0.1,add_time,10,0);
end if;
end if;end;//