面试题之存储过程

一、什么是存储过程

简单来说，存储过程就是一组预先编译好的sql语句集。

二、创建语法

create procedure 存储过程名(参数列表)
begin
//....存储过程体
end;

参数列表主要有三种形式：

• IN : 需要调用者传入
• OUT : 作为返回值
• INOUT : 调用者传入参数，对应的可以有返回值

注意点：

• 由于sql语句默认以";"分号作为分隔符，因此存储过程体中每一条sql语句结尾都必须加上分号；
• 存储过程结尾需要使用delimiter 标记重新去设置存储过程的结束标记；

三、优点

• 提高重用性，减少冗余；
• 减少了sql的编译次数，减少了与服务器连接交互的次数；

四、案例

首先我们先创建两种表emp和dept表，sql脚本如下：

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `dept_id` varchar(255) NOT NULL,
  `dept_name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES ('1', '开发部');
INSERT INTO `dept` VALUES ('2', '技术部');
INSERT INTO `dept` VALUES ('3', '测试部');

DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `emp_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '员工ID',
  `emp_name` varchar(255) DEFAULT NULL,
  `dept_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES ('1', '张三', '1');
INSERT INTO `emp` VALUES ('2', '李四', '2');
INSERT INTO `emp` VALUES ('3', '王五', '3');

 【a】空参数列表

DELIMITER $$
CREATE PROCEDURE allEmpList()
BEGIN
	SELECT * from emp;
END $$

存储过程调用：

call allEmpList() 

运行结果：

【b】带IN 模式的存储过程

DELIMITER $$
CREATE PROCEDURE getEmpByEmpName (IN empName VARCHAR(255))
BEGIN
	SELECT
		*
	FROM
		emp t
	LEFT JOIN dept t2 ON t.dept_id = t2.dept_id
	WHERE
		t.emp_name = empName ;
	END$$

存储过程调用：

call getEmpByEmpName('张三')

运行结果：

【c】带out的存储过程

DELIMITER $$
CREATE PROCEDURE getEmpNameByEmpId (
	IN empId INT,
	OUT empName VARCHAR (255)
)
BEGIN
	SELECT
		t.emp_name INTO empName
	FROM
		emp t
	WHERE
		t.emp_id = empId ; 
END$$

存储过程调用：

call getEmpNameByEmpId(1,@empName);
select @empName as empName;

运行结果：

【d】带INOUT的存储过程

DELIMITER $$
CREATE PROCEDURE customSum (INOUT a INT, INOUT b INT)
BEGIN

SET a = a * 12 ;
SET b = b * 12 ; 
END$$

存储过程调用：

set @a = 10;
set @b = 11;
call customSum(@a,@b);
select @a,@b;

运行结果：