I have been using stored procedures a lot in my recent project development, so I want to share a basic stored PROCEDURE with a simple example borrowed in this article (the interview questions in this article are just a guide to understand the stored PROCEDURE in the example).

Topic:

If table card_info contains the following fields: ID (table ID) card_num (card number) card_balance (card balance) card_jifen (card score) 1 2. If you need to deduct 1000 points from the card that also meets the above conditions, then add 200 yuan. If the points are less than 1000, you will not operate.

DROP TABLE IF EXISTS card_info; CREATE TABLE card_info (id VARCHAR(32) NOT NULL COMMENT 'id ', card_num VARCHAR(10) NOT NULL COMMENT' id ', Card_balance DOUBLE(10,2) DEFAULT 0 COMMENT 'card_balance ', card_jifen INT(10) DEFAULT 0 COMMENT' card_balance ', PRIMARY KEY (id) ) ENGINE = INNODB DEFAULT CHARSET = utf8;Copy the code


Insert sample data:  INSERT INTO card_info(id, card_num, card_balance, card_jifen) VALUES(REPLACE(UUID(),'-',''), '0123456789', 100, 100); INSERT INTO card_info(id, card_num, card_balance, card_jifen) VALUES(REPLACE(UUID(),'-',''), '0154326789', 0, 1000); INSERT INTO card_info(id, card_num, card_balance, card_jifen) VALUES(REPLACE(UUID(),'-',''), '0154329876', 0, 2000);Copy the code

Answer:

1, use general SQL statement:

1.UPDATE card_info SET card_balance = card_balance + 200 WHERE SUBSTR(card_num FROM 3 FOR 4) = '5432';
Copy the code


2.UPDATE card_info SET card_balance = card_balance + 200, card_jifen = card_jifen - 1000 WHERE SUBSTR(card_num FROM 3 FOR 4) = '5432' AND card_jifen >= 1000;
Copy the code

2, Using stored procedures:

Step 1: Create the stored procedure:

DROP PROCEDURE IF EXISTS test_proc; DELIMITER // -- define terminator, Mysql default terminator '; ' CREATE PROCEDURE test_proc (IN param VARCHAR(32), OUT result INTEGER(10) -- Input and output parameters (IN\OUT\INOUT) BEGIN -- DECLARE Define variable DECLARE cardId, cardNum VARCHAR(32); DECLARE cardBalance, cardJifen DOUBLE (10, 2); -- DECLARE flag INT DEFAULT TRUE; -- DECLARE cardInfo CURSOR FOR SELECT ID, card_num, card_balance, card_jifen FROM card_info WHERE SUBSTR(card_num FROM 3 FOR 4) = param; FETCH_STATUS DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = FALSE; SET result = 0; -- OPEN the cursor OPEN cardInfo; FETCH cardInfo INTO cardId, cardNum, cardBalance, cardJifen; FETCH cardInfo INTO cardId, cardNum, cardBalance, cardJifen; WHILE flag DO IF cardJifen >= 1000 THEN SET result = result +1; UPDATE card_info SET card_jifen = cardJifen - 1000, card_balance = cardBalance + 200 WHERE ID = cardId; END IF; FETCH cardInfo INTO cardId, cardNum, cardBalance, cardJifen; FETCH cardInfo INTO cardId, cardNum, cardBalance, cardJifen; END WHILE; Sql Server DEALLOCATE CLOSE cardInfo; END; // -- end the process DELIMITER; -- Change the terminator back to the defaultCopy the code

Step 2: Call the stored procedure:

SET @param = '5432'; CALL test_proc(@param, @result); SELECT @result;Copy the code

Note: Stored procedures in Mysql and Sql Server are slightly different. For example, FETCH NEXT FROM * INTO in Sql Server will have a state @@tetch_status. There is no need to manually define the end tag and bind to the cursor. Sql Server (DEALLOCATE *); This article only uses Mysql as an example.