mysql存储过程 

 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;

 

posted @ 2017-02-26 00:49  浪涛飞  阅读(209)  评论(0)    收藏  举报