以下是我MSSQL的储存过程,希望高手能帮我转换为 Mysql 的储存过程谢谢了! 或者大家有没见过这类型的软件啊!
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER procedure [dbo].[ST_ClientBIll] @SheetId varchar(20), @Flag int
as
begin
declare @payMoney dec(12,2)
declare @NewSheetId varchar(20)
set nocount on
update clientBill set flag = 100 where flag=0 and sheetid = @sheetId
declare cur_bill cursor local for
select refsheetId,payMoney from clientBilldetail where sheetID = @sheetID
open cur_bill
while (1=1)
begin
fetch Next from cur_bill into @NewSheetID,@PayMoney
if @@Fetch_Status <> 0 break
if @NewSheetId is Null or @newSheetID = '' continue;
if @PayMoney is null select @payMoney = 0.00
update receipt set payMoney = Isnull(PayMoney,0)+@payMoney where SheetID = @NewSheetID
end
close cur_bill
deallocate cur_bill
Return 0
end
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER procedure [dbo].[ST_ClientBIll] @SheetId varchar(20), @Flag int
as
begin
declare @payMoney dec(12,2)
declare @NewSheetId varchar(20)
set nocount on
update clientBill set flag = 100 where flag=0 and sheetid = @sheetId
declare cur_bill cursor local for
select refsheetId,payMoney from clientBilldetail where sheetID = @sheetID
open cur_bill
while (1=1)
begin
fetch Next from cur_bill into @NewSheetID,@PayMoney
if @@Fetch_Status <> 0 break
if @NewSheetId is Null or @newSheetID = '' continue;
if @PayMoney is null select @payMoney = 0.00
update receipt set payMoney = Isnull(PayMoney,0)+@payMoney where SheetID = @NewSheetID
end
close cur_bill
deallocate cur_bill
Return 0
end
建议自己先试着改一下,哪不对了,或者不理解再来就具体的问题来讨论。语法你可以看官方文档中的存储过程部分。
MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.htmlCREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a CHAR(16);
DECLARE b,c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur1;
OPEN cur2; REPEAT
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF NOT done THEN
IF b < c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END IF;
UNTIL done END REPEAT; CLOSE cur1;
CLOSE cur2;
END
(
in i_SheetId varchar(20),
in i_Flag int
)
as
begin
declare v_payMoney decimal(12,2);
declare v_NewSheetId varchar(20);
declare v_i smallint;
declare cur_bill cursor for
select refsheetId,payMoney from clientBilldetail where sheetID = i_SheetId;
declare continue handler for not found set v_i = 1;
set v_i = 0;
update clientBill set flag = 100 where flag=0 and sheetid = i_SheetId;
open cur_bill;
fetch cur_bill into v_NewSheetId,v_payMoney;
while v_i=0 do
if ifnull(v_NewSheetId,'') <> '' then
if v_payMoney is null then
set v_payMoney = 0.00;
end if;
update receipt set payMoney = ifnull(PayMoney,0)+v_payMoney where SheetID = v_NewSheetId;
end if;
fetch cur_bill into v_NewSheetId,v_payMoney;
end while;
close cur_bill;
end;