1 /*
2 SQLyog Ultimate v12.3.2 (64 bit)
3 MySQL - 5.5.13 : Database - test
4 *********************************************************************
5 */
6
7
8 /*!40101 SET NAMES utf8 */;
9
10 /*!40101 SET SQL_MODE=''*/;
11
12 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
13 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
14 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
15 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
16 CREATE DATABASE /*!32312 IF NOT EXISTS*/`test` /*!40100 DEFAULT CHARACTER SET utf8 */;
17
18 USE `test`;
19
20 /*Table structure for table `depart` */
21
22 DROP TABLE IF EXISTS `depart`;
23
24 CREATE TABLE `depart` (
25 `did` char(1) NOT NULL,
26 `dname` varchar(20) DEFAULT NULL,
27 `dmaster` char(3) DEFAULT NULL,
28 `droom` char(10) DEFAULT NULL,
29 PRIMARY KEY (`did`)
30 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
31
32 /*Data for the table `depart` */
33
34 insert into `depart`(`did`,`dname`,`dmaster`,`droom`) values
35
36 ('1','财务部','003','2201'),
37
38 ('2','人事处','005','2209'),
39
40 ('3','市场部','009','3201'),
41
42 ('4','开发部','001','3206'),
43
44 ('5','科研部','002','4201'),
45
46 ('6','宣传部','004','4202'),
47
48 ('7','工会','006','4203');
49
50 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
51 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
52 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
53 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
54
55
56 -- 带输出参数的存储过程
57 DELIMITER $$
58 #DROP PROCEDURE IF EXISTS test_sp3
59 CREATE PROCEDURE test_sp3(IN id CHAR(1),IN dn VARCHAR(20),IN dm CHAR(3),IN dr CHAR(10),OUT err INT)
60 BEGIN
61 DECLARE t_error INTEGER DEFAULT 0; -- 设置一个事务标识位
62 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1; -- 声明一个sql异常,当触发这个异常时，把标识位置为1
63
64 START TRANSACTION;
65 INSERT INTO depart VALUES(id,dn,dm,dr);
66 IF t_error = 1 THEN -- 标识位为1,事务回滚
67 ROLLBACK;
68 ELSE -- 为0提交事务
69 COMMIT;
70 END IF;
71 SET err=t_error; -- 返回标识位的结果集;(1.代表回滚 0.代表提交)
72 END$$
73 DELIMITER ;
74
75 CALL test_sp3('4','开发部','001','3206',@m);
76 SELECT @m;
