如果存储过程中还有其他内容 [英] If else in Stored procedure
问题描述
你好专家,
如果
PRM_QTY
值为null那么它应该执行查询的某些部分,如果应该在sotred程序中检查这个条件
看起来很简单,但我不知道为什么这不起作用。
ALTER SPECIFIC PROCEDURE R34FILES.HBINCONRAC REPLACE( IN PRMLOC CHARACTER( 2 ), IN PRM_ITEM CHARACTER( 20 ), IN PRM_TRLN CHARACTER( 50 ), IN PRM_CC CHARACTER( 50 ), IN PRM_QTY CHARACTER( 5 ))修改SQL数据并发访问权限解析< span class =code-keyword> DEFAULT 动态结果集 2 OLD SAVEPOINT LEVEL COMMIT ON RETURN NO DISALLOW DEBUG MODE BEGIN
DECLARE CR1 CURSOR WITH RETURN FOR
IF PRM_QTY < span class =code-keyword> IS NULL
BEGIN
SELECT
VINBINI。 BALOC AS BABLOC,VINBINI。 BAITEM AS BAITEM,VINITEM。 ICDSC1 AS ICDSC1,
VINITEM。 ICDSC2 AS ICDSC2,VINBINI。 BABLOC AS BABLOC,
INTEGER(VINBINI.BAQOH) AS QOH,INTEGER(VINBINI.BAQCM ) AS QCM,
VINBINI。 BACCTU,PALBINPF。 PALLETID,PALBINPF。 NBRRCKBINB,
PALLITEMPF。 UNITSPER,VININDEX。 I001004
FROM PRODASP。 R34FILES。 PALBINPF PALBINPF,PRODASP。 R34FILES。 PALLITEMPF PALLITEMPF,PRODASP。 R34FILES。 VINBINI VINBINI,PRODASP。 R34FILES。 VININDEX VININDEX,PRODASP。 R34FILES。 VINITEM VINITEM WHERE VINBINI。 BABLOC = PALBINPF。 IMBLOC AND VINBINI。 BACMP = PALBINPF。 TACOMP AND VINBINI。 BAITEM = VINITEM。 ICITEM AND VINITEM。 ICTRLN = VININDEX。 I001TRLN AND PALLITEMPF。 ICITEM = VINITEM。 ICITEM AND PALLITEMPF。 PALLETID = PALBINPF。 PALLETID AND VINBINI。 BAITEM = PALLITEMPF。 ICITEM AND
(VINBINI。BAITEM LIKE UPPER(CONCAT(TRIM(PRM_ITEM),' %'))或 PRM_ITEM IS NULL ) AND
(UPPER( VINITEM.ICTRLN) LIKE UPPER(CONCAT(TRIM(PRM_TRLN),' %')) OR PRM_TRLN IS NULL ) AND
(VINBINI。BALOC = PRMLOC) AND ( VINBINI.BAQOH> 0 ) AND (VININDEX.I001004 LIKE (CONCAT(TRIM(PRM_CC),' %'))ORPRM_CC IS NULL )
ORDER BY BAITEM ASC ;
END ;
ELSE
BEGIN
SELECT
VINBINI。 BALOC AS BABLOC,VINBINI。 BAITEM AS BAITEM,VINITEM。 ICDSC1 AS ICDSC1,
VINITEM。 ICDSC2 AS ICDSC2,VINBINI。 BABLOC AS BABLOC,
INTEGER(VINBINI.BAQOH) AS QOH,INTEGER(VINBINI.BAQCM ) AS QCM,
VINBINI。 BACCTU,PALBINPF。 PALLETID,PALBINPF。 NBRRCKBINB,
PALLITEMPF。 UNITSPER,VININDEX。 I001004
FROM PRODASP。 R34FILES。 PALBINPF PALBINPF,PRODASP。 R34FILES。 PALLITEMPF PALLITEMPF,PRODASP。 R34FILES。 VINBINI VINBINI,PRODASP。 R34FILES。 VININDEX VININDEX,PRODASP。 R34FILES。 VINITEM VINITEM WHERE VINBINI。 BABLOC = PALBINPF。 IMBLOC AND VINBINI。 BACMP = PALBINPF。 TACOMP AND VINBINI。 BAITEM = VINITEM。 ICITEM AND VINITEM。 ICTRLN = VININDEX。 I001TRLN AND PALLITEMPF。 ICITEM = VINITEM。 ICITEM AND PALLITEMPF。 PALLETID = PALBINPF。 PALLETID AND VINBINI。 BAITEM = PALLITEMPF。 ICITEM AND
(VINBINI。BAITEM LIKE UPPER(CONCAT(TRIM(PRM_ITEM),' %'))或 PRM_ITEM IS NULL ) AND
(UPPER( VINITEM.ICTRLN) LIKE UPPER(CONCAT(TRIM(PRM_TRLN),' %')) OR PRM_TRLN IS NULL ) AND
(VINBINI。BALOC = PRMLOC) AND ( VINBINI.BAQOH> 0 ) AND (VINBINI.BAQOH<(INTEGER(PRM_QTY)))< span class =code-keyword> AND (VININDEX.I001004 LIKE (CONCAT(TRIM(PRM_CC),' %'))ORPRM_CC IS NULL )
ORDER BY BAITEM ASC ;
END ;
OPEN CR1;
END ;
或者我可以完全忽略If else并执行以下操作
SELECT
VINBINI。 BALOC AS BABLOC,VINBINI。 BAITEM AS BAITEM,VINITEM。 ICDSC1 AS ICDSC1,
VINITEM。 ICDSC2 AS ICDSC2,VINBINI。 BABLOC AS BABLOC,
INTEGER(VINBINI.BAQOH) AS QOH,INTEGER(VINBINI.BAQCM ) AS QCM,
VINBINI。 BACCTU,PALBINPF。 PALLETID,PALBINPF。 NBRRCKBINB,
PALLITEMPF。 UNITSPER,VININDEX。 I001004
FROM PRODASP。 R34FILES。 PALBINPF PALBINPF,PRODASP。 R34FILES。 PALLITEMPF PALLITEMPF,PRODASP。 R34FILES。 VINBINI VINBINI,PRODASP。 R34FILES。 VININDEX VININDEX,PRODASP。 R34FILES。 VINITEM VINITEM WHERE VINBINI。 BABLOC = PALBINPF。 IMBLOC AND VINBINI。 BACMP = PALBINPF。 TACOMP AND VINBINI。 BAITEM = VINITEM。 ICITEM AND VINITEM。 ICTRLN = VININDEX。 I001TRLN AND PALLITEMPF。 ICITEM = VINITEM。 ICITEM AND PALLITEMPF。 PALLETID = PALBINPF。 PALLETID AND VINBINI。 BAITEM = PALLITEMPF。 ICITEM AND
(VINBINI。BAITEM LIKE UPPER(CONCAT(TRIM(PRM_ITEM),' %'))或 PRM_ITEM IS NULL ) AND
(UPPER( VINITEM.ICTRLN) LIKE UPPER(CONCAT(TRIM(PRM_TRLN),' %')) OR PRM_TRLN IS NULL ) AND
(VINBINI。BALOC = PRMLOC) AND ( VINBINI.BAQOH> 0 ) AND (VINBINI.BAQOH< INTEGER(PRM_QTY)或 PRM_QTY IS NULL ) AND (VININDEX。 I001004 LIKE (CONCAT(TRIM(PRM_CC),' % ')) OR PRM_CC IS NULL )
ORDER BY BAITEM ASC ;
能否帮我一下这个
而不是
IF PRM_QTY IS NULL
尝试构建在函数中
IF isnull(PRM_QTY)那么
请参阅 MYSQL:ISNULL FUNCTION [ ^ ]
不要忘了然后在IF声明中。
[更新]
你也可以尝试ifnull功能。
< pre lang =SQL> SET PRM_QTY = ifnull(PRM_QTY, NULL );
或者,无论如何转换为整数
DECLARE varQTY INT ;
SET varQTY = ifnull(PRM_QTY, 0 ); - 或者您希望拥有的任何默认值
Hello Experts,
If
PRM_QTY
value is null then it should execure certain part of query other if should check for this condition in sotred procedure
Seems pretty simple but I don't know why this is not working.
ALTER SPECIFIC PROCEDURE R34FILES.HBINCONRAC REPLACE (IN PRMLOC CHARACTER (2) , IN PRM_ITEM CHARACTER (20) , IN PRM_TRLN CHARACTER (50) , IN PRM_CC CHARACTER (50) , IN PRM_QTY CHARACTER (5) ) MODIFIES SQL DATA CONCURRENT ACCESS RESOLUTION DEFAULT DYNAMIC RESULT SETS 2 OLD SAVEPOINT LEVEL COMMIT ON RETURN NO DISALLOW DEBUG MODE BEGIN
DECLARE CR1 CURSOR WITH RETURN FOR
IF PRM_QTY IS NULL
BEGIN
SELECT
VINBINI . BALOC AS BABLOC , VINBINI . BAITEM AS BAITEM , VINITEM . ICDSC1 AS ICDSC1 ,
VINITEM . ICDSC2 AS ICDSC2 , VINBINI . BABLOC AS BABLOC ,
INTEGER ( VINBINI . BAQOH ) AS QOH , INTEGER ( VINBINI . BAQCM ) AS QCM ,
VINBINI . BACCTU , PALBINPF . PALLETID , PALBINPF . NBRRCKBINB ,
PALLITEMPF . UNITSPER , VININDEX . I001004
FROM PRODASP . R34FILES . PALBINPF PALBINPF , PRODASP . R34FILES . PALLITEMPF PALLITEMPF , PRODASP . R34FILES . VINBINI VINBINI ,PRODASP . R34FILES . VININDEX VININDEX , PRODASP . R34FILES . VINITEM VINITEM WHERE VINBINI . BABLOC = PALBINPF . IMBLOC AND VINBINI . BACMP = PALBINPF . TACOMP AND VINBINI . BAITEM = VINITEM . ICITEM AND VINITEM . ICTRLN = VININDEX . I001TRLN AND PALLITEMPF . ICITEM = VINITEM . ICITEM AND PALLITEMPF . PALLETID = PALBINPF . PALLETID AND VINBINI . BAITEM =PALLITEMPF . ICITEM AND
( VINBINI . BAITEM LIKE UPPER ( CONCAT ( TRIM ( PRM_ITEM ) , '%' ) ) OR PRM_ITEM IS NULL ) AND
( UPPER ( VINITEM . ICTRLN ) LIKE UPPER ( CONCAT ( TRIM ( PRM_TRLN ) , '%' ) ) OR PRM_TRLN IS NULL ) AND
( VINBINI . BALOC = PRMLOC ) AND ( VINBINI . BAQOH > 0 ) AND ( VININDEX . I001004 LIKE ( CONCAT ( TRIM ( PRM_CC ) , '%' ) ) ORPRM_CC IS NULL )
ORDER BY BAITEM ASC ;
END;
ELSE
BEGIN
SELECT
VINBINI . BALOC AS BABLOC , VINBINI . BAITEM AS BAITEM , VINITEM . ICDSC1 AS ICDSC1 ,
VINITEM . ICDSC2 AS ICDSC2 , VINBINI . BABLOC AS BABLOC ,
INTEGER ( VINBINI . BAQOH ) AS QOH , INTEGER ( VINBINI . BAQCM ) AS QCM ,
VINBINI . BACCTU , PALBINPF . PALLETID , PALBINPF . NBRRCKBINB ,
PALLITEMPF . UNITSPER , VININDEX . I001004
FROM PRODASP . R34FILES . PALBINPF PALBINPF , PRODASP . R34FILES . PALLITEMPF PALLITEMPF , PRODASP . R34FILES . VINBINI VINBINI ,PRODASP . R34FILES . VININDEX VININDEX , PRODASP . R34FILES . VINITEM VINITEM WHERE VINBINI . BABLOC = PALBINPF . IMBLOC AND VINBINI . BACMP = PALBINPF . TACOMP AND VINBINI . BAITEM = VINITEM . ICITEM AND VINITEM . ICTRLN = VININDEX . I001TRLN AND PALLITEMPF . ICITEM = VINITEM . ICITEM AND PALLITEMPF . PALLETID = PALBINPF . PALLETID AND VINBINI . BAITEM =PALLITEMPF . ICITEM AND
( VINBINI . BAITEM LIKE UPPER ( CONCAT ( TRIM ( PRM_ITEM ) , '%' ) ) OR PRM_ITEM IS NULL ) AND
( UPPER ( VINITEM . ICTRLN ) LIKE UPPER ( CONCAT ( TRIM ( PRM_TRLN ) , '%' ) ) OR PRM_TRLN IS NULL ) AND
( VINBINI . BALOC = PRMLOC ) AND ( VINBINI . BAQOH > 0 ) AND (VINBINI.BAQOH < (INTEGER(PRM_QTY))) AND ( VININDEX . I001004 LIKE ( CONCAT ( TRIM ( PRM_CC ) , '%' ) ) ORPRM_CC IS NULL )
ORDER BY BAITEM ASC ;
END;
OPEN CR1 ;
END ;
Or can I completely ignore If else and do something like below
SELECT
VINBINI . BALOC AS BABLOC , VINBINI . BAITEM AS BAITEM , VINITEM . ICDSC1 AS ICDSC1 ,
VINITEM . ICDSC2 AS ICDSC2 , VINBINI . BABLOC AS BABLOC ,
INTEGER ( VINBINI . BAQOH ) AS QOH , INTEGER ( VINBINI . BAQCM ) AS QCM ,
VINBINI . BACCTU , PALBINPF . PALLETID , PALBINPF . NBRRCKBINB ,
PALLITEMPF . UNITSPER , VININDEX . I001004
FROM PRODASP . R34FILES . PALBINPF PALBINPF , PRODASP . R34FILES . PALLITEMPF PALLITEMPF , PRODASP . R34FILES . VINBINI VINBINI , PRODASP . R34FILES . VININDEX VININDEX , PRODASP . R34FILES . VINITEM VINITEM WHERE VINBINI . BABLOC = PALBINPF . IMBLOC AND VINBINI . BACMP = PALBINPF . TACOMP AND VINBINI . BAITEM = VINITEM . ICITEM AND VINITEM . ICTRLN = VININDEX . I001TRLN AND PALLITEMPF . ICITEM = VINITEM . ICITEM AND PALLITEMPF . PALLETID = PALBINPF . PALLETID AND VINBINI . BAITEM = PALLITEMPF . ICITEM AND
( VINBINI . BAITEM LIKE UPPER ( CONCAT ( TRIM ( PRM_ITEM ) , '%' ) ) OR PRM_ITEM IS NULL ) AND
( UPPER ( VINITEM . ICTRLN ) LIKE UPPER ( CONCAT ( TRIM ( PRM_TRLN ) , '%' ) ) OR PRM_TRLN IS NULL ) AND
( VINBINI . BALOC = PRMLOC ) AND ( VINBINI . BAQOH > 0 ) AND ( VINBINI . BAQOH < INTEGER ( PRM_QTY ) OR PRM_QTY IS NULL ) AND ( VININDEX . I001004 LIKE ( CONCAT ( TRIM ( PRM_CC ) , '%' ) ) OR PRM_CC IS NULL )
ORDER BY BAITEM ASC ;
Can you please help me with this
Instead of
IF PRM_QTY IS NULL
try the built in function
IF isnull(PRM_QTY) THEN
See MYSQL: ISNULL FUNCTION[^]
And don't forget the THEN in the IF statement.
[UPDATE]
You could also try the ifnull function.
SET PRM_QTY = ifnull(PRM_QTY, NULL);
or, as you convert to an integer anyway
DECLARE varQTY INT; SET varQTY = ifnull(PRM_QTY, 0); -- Or whatever default value you want to have
这篇关于如果存储过程中还有其他内容的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!