Mysql实例理解MySQL变量和条件

MYSQL应用
#创建数据库
DROP DATABASE IF EXISTS Dpro;
CREATE DATABASE Dpro
CHARACTER SET utf8
;
USE Dpro;
#创建部分表
DROP TABLE IF EXISTS Employee;
CREATE TABLE Employee
(id INT NOT NULL PRIMARY KEY COMMENT '主键',
 name VARCHAR(20) NOT NULL COMMENT '人名',
 depid INT NOT NULL COMMENT '部分id'
);
INSERT INTO Employee(id,name,depid) VALUES(1,'陈',100),(2,'王',101),(3,'张',101),(4,'李',102),(5,'郭',103);

MYSQL应用
DROP PROCEDURE IF EXISTS Pro_Employee;
DELIMITER $$
CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
DECLARE pname VARCHAR(20) DEFAULT '陈';
SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid;
END$$
DELIMITER ;

MYSQL应用
SET var_name = expr [, var_name = expr] ...
DROP PROCEDURE IF EXISTS Pro_Employee;
DELIMITER $$
CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
DECLARE pname VARCHAR(20) DEFAULT '陈';
SET pname='王';
SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid AND name=pname;
END$$
DELIMITER ;
CALL Pro_Employee(101,@pcount);
 SELECT @pcount;

MYSQL应用
DROP PROCEDURE IF EXISTS Pro_Employee;
DELIMITER $$
CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
DECLARE pname VARCHAR(20) DEFAULT '陈';
SET pname='王';
SET @ID=1;
SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid AND name=pname;
SELECT @ID;
END$$
DELIMITER ;
CALL Pro_Employee(101,@pcount);

MYSQL应用
DROP PROCEDURE IF EXISTS Pro_Employee;
DELIMITER $$
CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
DECLARE pname VARCHAR(20) DEFAULT '陈';
DECLARE Pid INT;
SELECT COUNT(id) INTO Pid FROM Employee WHERE depid=pdepid AND name=pname;
SELECT Pid;
END$$
DELIMITER ;
CALL Pro_Employee(101,@pcount);

MYSQL应用
DECLARE condition_name CONDITION FOR condition_value
 
condition_value:
  SQLSTATE [VALUE] sqlstate_value
 | mysql_error_code

MYSQL应用
DROP PROCEDURE IF EXISTS Pro_Employee_insert;
DELIMITER $$
CREATE PROCEDURE Pro_Employee_insert()
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
SET @ID=1;
INSERT INTO Employee(id,name,depid) VALUES(1,'陈',100);
SET @ID=2;
INSERT INTO Employee(id,name,depid) VALUES(6,'陈',100);
SET @ID=3;
END$$
DELIMITER ;
#执行存储过程
CALL Pro_Employee_insert();
#查询变量值
SELECT @ID,@X;

MYSQL应用
DROP PROCEDURE IF EXISTS Pro_Employee_insert;
DELIMITER $$
CREATE PROCEDURE Pro_Employee_insert()
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
#定义条件名称,
DECLARE reprimary CONDITION FOR 1062;
#引用前面定义的条件名称并做赋值处理
DECLARE EXIT HANDLER FOR reprimary SET @x=1;
SET @ID=1;
INSERT INTO Employee(id,name,depid) VALUES(1,'陈',100);
SET @ID=2;
INSERT INTO Employee(id,name,depid) VALUES(6,'陈',100);
SET @ID=3;
END$$
DELIMITER ;
CALL Pro_Employee_insert();
SELECT @ID,@X;

MYSQL应用
DROP PROCEDURE IF EXISTS Pro_Employee_insert;
DELIMITER $$
CREATE PROCEDURE Pro_Employee_insert()
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
#定义条件名称,
DECLARE reprimary CONDITION FOR SQLSTATE '23000';
#引用前面定义的条件名称并做赋值处理
DECLARE CONTINUE HANDLER FOR reprimary SET @x=1;
SET @ID=1;
INSERT INTO Employee(id,name,depid) VALUES(1,'陈',100);
SET @ID=2;
INSERT INTO Employee(id,name,depid) VALUES(6,'陈',100);
SET @ID=3;
END$$
DELIMITER ;
CALL Pro_Employee_insert();
SELECT @ID,@X;

MYSQL应用
DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement
 
handler_type:
  CONTINUE| EXIT| UNDO
 
condition_value:
  SQLSTATE [VALUE] sqlstate_value
 | condition_name
 | SQLWARNING
 | NOT FOUND
 | SQLEXCEPTION
 | mysql_error_code

MYSQL应用
DROP PROCEDURE IF EXISTS Pro_Employee_insert;
DELIMITER $$
CREATE PROCEDURE Pro_Employee_insert()
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
#引用前面定义的条件名称并做赋值处理
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @x=2;
#开始事务必需在DECLARE之后
START TRANSACTION ;
SET @ID=1;
INSERT INTO Employee(id,name,depid) VALUES(7,'陈',100);
SET @ID=2;
INSERT INTO Employee(id,name,depid) VALUES(6,'陈',100);
SET @ID=3;
IF @x=2 THEN
 ROLLBACK;
ELSE
 COMMIT;
END IF; 
END$$
DELIMITER ;
#执行存储过程
CALL Pro_Employee_insert();
#查询
SELECT @ID,@X;