MYSQL應(yīng)用
#創(chuàng)建數(shù)據(jù)庫
DROP DATABASE IF EXISTS Dpro;
CREATE DATABASE Dpro
CHARACTER SET utf8
;
USE Dpro;
#創(chuàng)建部分表
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應(yīng)用
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應(yīng)用
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應(yīng)用
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應(yīng)用
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應(yīng)用
DECLARE condition_name CONDITION FOR condition_value
 
condition_value:
  SQLSTATE [VALUE] sqlstate_value
 | mysql_error_code

MYSQL應(yīng)用
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 ;
#執(zhí)行存儲過程
CALL Pro_Employee_insert();
#查詢變量值
SELECT @ID,@X;

MYSQL應(yīng)用
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應(yīng)用
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應(yīng)用
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應(yīng)用
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;
#開始事務(wù)必需在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 ;
#執(zhí)行存儲過程
CALL Pro_Employee_insert();
#查詢
SELECT @ID,@X;