我是 mysql 的新手,我正在尝试使用下面的 SP 获取数据,我在其中使用了 if else 语句
CREATE DEFINER=`Travel_user`@`%` PROCEDURE `new_procedure`(in bus_id int, in travel_id int, in tr_date Date, in Seat_no varchar(45))
BEGIN
select @SeatNo := Fare from Travel_booking.SpecialFares where TravelId=travel_id and date=tr_date ;
IF (@SeatNo = NULL) then
select @SeatNo := sd.Fare from SeatDetails as sd
inner join tbl_ColumnDetails as c on c.ColumnId=sd.ColId
inner join tbl_RowsDetails as r on r.RowId=c.RowID
inner join BusStructure as bs on bs.StructId=r.StructureId
inner join BusDetails as bd on bd.Structure=bs.StructId
where sd.IsActive=1 and bd.BusiId=bus_id and sd.travelid=travel_id and sd.SeatNo=Seat_no;
END IF;
END
我叫它像
call Travel_booking.new_procedure(34, 53, '2018-03-01', 'L2');
第一个选择查询工作正常,并且显示正确的结果。但是,当第一个查询的结果为空时,if语句给出空白结果。
我已经分别检查了这两个查询,如下所示,它们都可以工作。
第一个查询,
select Fare from Travel_booking.SpecialFares
where TravelId=53 and date='2018-03-02';
第二个查询,
select sd.Fare from SeatDetails as sd
inner join tbl_ColumnDetails as c on c.ColumnId=sd.ColId
inner join BusStructure as bs on bs.StructId=r.StructureId
inner join BusDetails as bd on bd.Structure=bs.StructId
where sd.IsActive=1 and bd.BusiId=34 and sd.travelid=53 and sd.SeatNo='L2';
但是当我在 SP 中执行它们并将参数传递给它们时,我在同一查询中的 if 语句中得到空白结果。
感谢大家的回应,帮助我找出我错的地方。我得到了答案只需在 SP 开头声明空变量,例如
BEGIN
select @SeatNo := NULL;
select @SeatNo := Fare from Travel_booking.SpecialFares where TravelId=travel_id and date=tr_date ;....
我的建议是。
1(您有很多joins
请查看每个连接是否有任何值,以及这些joins
是否正确连接,这意味着连接具有相等的值。
2(检查你的参数,在你的SQL环境中,检查你的参数是否产生结果。
3(提示也是,检查您的参数是否正确传递。创建输出会立即告诉您,开发人员您的代码出了什么问题。
IF (@SeatNo 为 NULL( 则
select @SeatNo := sd.Fare from SeatDetails as sd
inner join tbl_ColumnDetails as c on c.ColumnId=sd.ColId
inner join tbl_RowsDetails as r on r.RowId=c.RowID
inner join BusStructure as bs on bs.StructId=r.StructureId
inner join BusDetails as bd on bd.Structure=bs.StructId
where sd.IsActive=1 and bd.BusiId=bus_id and sd.travelid=travel_id and sd.SeatNo=Seat_no;
如果结束;
写入代替"=";