mysql存储过程练习题_mysql存储过程实例附答案.doc

mysql存储过程实例附答案

存储过程与存储函数

概念题

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表的记录

IdNumNameSexBirthdayAddress11001张三男1984-11-08北京市昌平区21002李四女1970-01-21北京市海淀区3 1003王五男1976-10-30北京市昌平区41004赵六男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,