Stored Procedure 6-add, delete, modify, and query stored procedures

For the following stored procedures, the table structure is as follows:

-- ------------------------------ Table structure for person-- ----------------------------DROP TABLE IF EXISTS `person`;CREATE TABLE `person` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `username` varchar(255) DEFAULT NULL,  `age` int(11) DEFAULT NULL,  `password` varchar(255) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;-- ------------------------------ Records of person-- ----------------------------INSERT INTO `person` VALUES (‘1‘, ‘lisi‘, ‘21‘, ‘li123456‘);INSERT INTO `person` VALUES (‘2‘, ‘zhangsan‘, ‘23‘, ‘zhang123456‘);

I. Add

For MySQL, the default action is to execute a commit statement after each SQL statement is executed, thus effectively separating each statement into a transaction. Therefore, if there are two modify statements in the stored procedure, these two statements are also independent. When the second statement fails after the first statement is successfully executed, the transaction of the first statement will not be rolled back. Therefore, to ensure the synchronization of the transactions of these two statements, you need to add the start transaction; and commit; block, and put the two statements in this block.

Drop procedure if exists proc_person_insert; Create procedure proc_person_insert (in uusername varchar (255), in uage int (11), In upassword varchar (255), out flag int (11 )) begin start transaction; insert into person (username, age, password) values (uusername, uage, upassword); Set flag = row_count (); -- save the number of rows affected by the SQL statement operation. Commit; end; call proc_person_insert ('lisi12', 22, '123456', @ flag); select @ flag;

If the @ falg value is 1, the insert operation is successful. The execution result is as follows:

Ii. Modification

Drop procedure if exists proc_person_update; Create procedure proc_person_update (in uid int (11), In uusername varchar (255), in uage int (11), In upassword varchar (255 ), out flag int (11) begin start transaction; update person set username = uusername, age = uage, password = upassword where id = uid; Set flag = row_count (); -- commit; end; call proc_person_update (1, 'zhaoliu', 20, 'zhao123', @ flag); select @ flag;

If the @ falg value is 1, the update is successful. The execution result is as follows:

Iii. Query

1. query a single

The result set is returned.

DROP PROCEDURE IF EXISTS proc_person_findOne;CREATE PROCEDURE proc_person_findOne(    IN uid INT(11))BEGIN    SELECT username, age, password FROM person WHERE id = uid;END;CALL proc_person_findOne(1);

The execution result is as follows:

2. query all

Returns multiple result sets.

DROP PROCEDURE IF EXISTS proc_person_findAll;CREATE PROCEDURE proc_person_findAll()BEGIN    SELECT id, username, age, password FROM person;END;CALL proc_person_findAll();

The execution result is as follows:

Iv. Delete

DROP PROCEDURE IF EXISTS proc_person_del;CREATE PROCEDURE proc_person_del(    IN uid  INT(11),    OUT flag INT(11))BEGIN    START TRANSACTION;        DELETE FROM person where id = uid;        SET flag = ROW_COUNT();    COMMIT;END;CALL proc_person_del(3, @flag);SELECT @flag;

If the @ falg value is 1, the deletion is successful. The execution result is as follows:

　

Stored Procedure 6-add, delete, modify, and query stored procedures