|
|
1.出现PROCEDURE xxx can't return a result set in the given context when execute ...错误,mysql_real_connect连接时没有指定clientflag为CLIENT_MULTI_STATEMENTS!
2. You can create a prepared statement with the PREPARE statement, supplying the SQL text in a session variable.就是说不能是局部变量,或存储过程范围内的变量.
3.syntax sample for memo
DELIMITER $$;
DROP PROCEDURE IF EXISTS `silvermoney`.`BalanceCashSavings`$$
CREATE PROCEDURE `BalanceCashSavings`(vinCashMoney BIGINT, vinAccountID INT, vinBnCode VARCHAR(
32
), vinBnProcessor VARCHAR(
32
), OUT vErrCode INT)
st_main : BEGIN
DECLARE varCurCashMoney BIGINT;
DECLARE varCurBankSaving BIGINT;
SET vErrCode
=
0
;
SELECT CashMoney, BankMoney INTO varCurCashMoney, varCurBankSaving FROM SilverAccount WHERE AccountID
=
vinAccountID;
IF FOUND_ROWS()
=
1
THEN
SET vErrCode
=
1
;
LEAVE st_main;
END IF;
START TRANSACTION;
IF vinCashMoney
>
0
THEN
IF varCurBankSaving
<
vinCashMoney THEN
SET vErrCode
=
2
;
LEAVE st_main;
END IF;
UPDATE SilverAccount SET CashMoney
=
CashMoney
+
vinCashMoney, BankMoney
=
BankMoney
-
vinCashMoney WHERE AccountID
=
vinAccountID;
IF ROW_COUNT()
!=
1
THEN
SET vErrCode
=
3
;
LEAVE st_main;
END IF;
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET vErrCode
=
27
;
SET @vSQLInsertBn
=
"
INSERT Business_
"
+
CONCAT(CURDATE()
+
0
)
+
"
VALUES('
"
+
vinBnCode
+
"
',
"
+
CONCAT(vinAccountID)
+
"
,
"
+
CONCAT(vinCashMoney)
+
"
, 0, '
"
+
CONCAT(CURDATE()
+
0
)
+
"
', 5, '
"
+
vinBnProcessor
+
"
', '')
"
;
PREPARE dynSQL FROM @vSQLInsertBn;
EXECUTE dynSQL;
DEALLOCATE PREPARE dynSQL;
END;
ELSE
IF varCurCashMoney
<
-
vinCashMoney THEN
SET vErrCode
=
5
;
LEAVE st_main;
END IF;
UPDATE SilverAccount SET CashMoney
=
CashMoney
+
vinCashMoney, BankMoney
=
BankMoney
-
vinCashMoney WHERE AccountID
=
vinAccountID;
IF ROW_COUNT()
!=
1
THEN
SET vErrCode
=
6
;
LEAVE st_main;
END IF;
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET vErrCode
=
27
;
SET @vSQLInsertBn
=
"
INSERT Business_
"
+
CONCAT(CURDATE()
+
0
)
+
"
VALUES('
"
+
vinBnCode
+
"
',
"
+
CONCAT(vinAccountID)
+
"
,
"
+
CONCAT(vinCashMoney)
+
"
, 0, '
"
+
CONCAT(CURDATE()
+
0
)
+
"
', 5, '
"
+
vinBnProcessor
+
"
', '')
"
;
PREPARE dynSQL FROM @vSQLInsertBn;
EXECUTE dynSQL;
DEALLOCATE PREPARE dynSQL;
END;
END IF;
COMMIT;
END$$
DELIMITER ;$$
DELIMITER $$;
DROP PROCEDURE IF EXISTS `silvermoney`.`ChargeSilverCard`$$
CREATE PROCEDURE `ChargeSilverCard`(vinCardID VARCHAR(
32
), vinPassword VARCHAR(
32
), vinBnCode VARCHAR(
32
), vinBnProcessor VARCHAR(
32
), vinAccountID INT, OUT vErrCode INT)
st_main : BEGIN
DECLARE varCardMoney BIGINT;
DECLARE varOrgBnCode VARCHAR(
32
);
DECLARE varBnTableName VARCHAR(
32
);
DECLARE vSQLInsertBn VARCHAR(
256
);
SELECT Money, BusinessCode INTO varCardMoney, varOrgBnCode From SilverCard WHERE CardID
=
vinCardID AND Password
=
vinPassword;
IF FOUND_ROWS()
!=
1
THEN
SET vErrCode
=
1
;
LEAVE st_main;
END IF;
IF varOrgBnCode
!=
NULL THEN
SET vErrCode
=
2
;
LEAVE st_main;
END IF;
START TRANSACTION;
UPDATE SilverCard SET BusinessCode
=
vinBnCode WHERE CardID
=
vinCardID;
IF ROW_COUNT()
!=
1
THEN
SET vErrCode
=
3
;
LEAVE st_main;
END IF;
UPDATE SilverAccount SET CashMoney
=
CashMoney
+
varCardMoney WHERE AccountID
=
vinAccountID;
IF ROW_COUNT()
!=
1
THEN
SET vErrCode
=
4
;
LEAVE st_main;
END IF;
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET vErrCode
=
27
;
SET @vSQLInsertBn
=
"
INSERT Business_
"
+
CONCAT(CURDATE()
+
0
)
+
"
VALUES('
"
+
vinBnCode
+
"
',
"
+
CONCAT(vinAccountID)
+
"
,
"
+
CONCAT(varCardMoney)
+
"
, 0, '
"
+
CONCAT(CURDATE()
+
0
)
+
"
', 6, '
"
+
vinBnProcessor
+
"
', '
"
+
vinCardID
+
"
')
"
;
PREPARE dynSQL FROM @vSQLInsertBn;
EXECUTE dynSQL;
DEALLOCATE PREPARE dynSQL;
END;
COMMIT;
END$$
DELIMITER ;$$
|