mysql存储过程实例附答案.doc

存储过程与存储函数 概念题 1 存储过程与存储函数的概念 2 存储过程与存储函数联系与区别 实践题 在teacher表上创建名为teacher_info1的存储过程，要求：teacher_info1有3个参数。输入参数为teacher_id和type，输出参数为info。存储过程的作用是根据编号teacher_id来查询teacher表中的记录。如果type的值为1时，将姓名name传给输出参数info；如果type的值为2时，将年龄传给输出参数info；如果type的值为其他值，则返回字符串”Error”。 Teacher表的定义如下所示 Teacher表的定义 字段名 字段描述 数据类型 主键 外键 非空 唯一 自增 Id 编号 Int(4) 是 否 是 是 是 Num 教工号 Int(10) 否 否 是 是 否 Name 姓名 Varchar(20) 否 否 是 否 否 Sex 性别 Varchar(4) 否 否 是 否 否 Birthday 出生日期 Datetime 否 否 否 否 否 Address 家庭住址 Varchar(50) 否 否 否 否 否 需要插入到teacher表的记录 Id Num Name Sex Birthday Address 1 1001 张三 男 1984-11-08 北京市昌平区 2 1002 李四 女 1970-01-21 北京市海淀区 3 1003 王五 男 1976-10-30 北京市昌平区 4 1004 赵六 男 1980-06-05 北京市顺义区 步骤 1 创建teacher表并插入记录 2 创建存储过程teacher_info1 3 调用存储过程,teacher_id为2，type为1 4 删除存储过程。 概念题 1 存储过程和函数是在数据库中定义一些SQL语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且，存储过程和函数是在MySQL服务器中存储和执行的，可以减少客户端和服务器端的数据传输。 2 存储过程与存储函数一样，都是由sql语句和过程式语句所组成的代码片段，并且可以被应用程序和其他sql语句调用。 区别： 存储函数不能拥有输出参数，因为存储函数自身就是输出参数；而存储过程可以拥有输出参数。 存储函数可以直接对存储函数进行调用，而不需要使用call语句;而对存储过程的调用，需要使用call语句。 存储函数中必须包含一条return语句，而这条特殊的sql语句不允许包含于存储过程中。 实践题 1 CREATE TABLE teacher( id INT(4) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT, num INT(10) NOT NULL UNIQUE , name VARCHAR(20) NOT NULL , sex VARCHAR(4) NOT NULL , birthday DATETIME , address VARCHAR(50) ); INSERT INTO teacher VALUES(1, 1001, 张三,男 ,1984-11-08 ,北京市昌平区); INSERT INTO teacher VALUES (2, 1002, 李四,女 ,1970-01-21 ,北京市海淀区) , (NULL, 1003, 王五,男 ,1976-10-30 ,北京市昌平区) , (NULL, 1004, 赵六,男 ,1980-06-05 ,北京市顺义区) ; 2 DELIMITER CREATE PROCEDURE teacher_info1(IN teacher_id INT, IN type INT,OUT info VARCHAR(20)) READS SQL DATA BEGIN CASE type WHEN 1 THEN SELECT name INTO info FROM teacher WHERE id=teacher_id; WHEN 2 THEN SELECT YEAR(NOW())-YEAR(birthday) INTO info FROM teacher WHERE id=teacher_id; ELSE SELECT ‘ERROR’ INTO info; END CASE; END DELIMITER ; 3 CALL teacher_info1(2,1,@info); SELECT @info; 4 DROP PRO