实验5 存储过程与函数（数据库原理）

 1.利用s_t数据库中的Students、STC表，编写一个无参存储过程，用于查询学生的学号、姓名、课号和成绩，然后调用该存储过程。

CREATE PROCEDURE s1()
BEGIN
SELECT sname,sno,cno,score    
FROM students NATURAL JOIN stc;
END    #创建存储过程

CALL s1() #调用

2.创建一个存储过程，根据给定不同的姓名，显示相应学生的学号、姓名、课号和成绩，然后调用该存储过程。

CREATE PROCEDURE s2(IN s_name VARCHAR(30))#创建存储过程，进行参数声明，in 指的是需要传入s_name
BEGIN                                                          参数
SELECT sname,sno,cno,score     
FROM students NATURAL JOIN stc   
WHERE sname = s_sname;    
END       

SET @s_name = '汪洋';   #对参数变量进行赋值
CALL s2(@s_name);   #调用

运行结果

3.创建一个stc_info的存储过程，要求该存储过程有3个参数。2个输入参数Tid和type，1个输出参数info。实现根据指定的教师编号（Tid）来查询stc表中的记录。如果type值为1，返回该教师讲授的学生人数；如果type值为2，返回该教师讲授课程的平均成绩，如果type为其他值，返回“error”提示。

    提示：上课人数（整型）、平均成绩（实型）和提示信息‘error！！！’（字符型），全部由info参数返回，因此该参数应定义为字符型。当数值型数据给字符型变量赋值时，需使用cast函数进行类型转换。具体使用格式如下：

     将123.45转换为字符型数据：  CAST(123.45 AS CHAR)      转换后为 '123.45’ .

CREATE PROCEDURE stc_info(IN Tid INT,IN type VARCHAR(30),OUT info VARCHAR(30))
BEGIN
CASE type
WHEN 1 THEN 
SELECT COUNT(sno) INTO info FROM stc
WHERE tno = Tid;
WHEN 2 THEN 
SELECT CAST(AVG(score) AS CHAR) INTO info FROM stc
WHERE tno = Tid;
ELSE 
SET info = 'error!!!';
END CASE;
END;    #运用了流程控制语句case


CALL stc_info(101,2,@info);  #这里101是教师编号，2是type。
SELECT @info;  #显示

显示

4.创建一个存储函数，要求根据给定的学号返回该生的选课门数，并输出。

CREATE FUNCTION s4(s_sno INT(45))
RETURNS INT DETERMINISTIC
BEGIN
  RETURN(
     SELECT COUNT(cno)
     FROM stc
     WHERE sno = s_sno;
)
END

SELECT s4('201615121');#调用

5小总结：

1.begin    end的作用是将所写语句当做一条语句来使用。

2.case 要与end case一起使用

3.存储过程与存储函数调用方式可能不太一样。

4.存储过程用法：

CREATE PROCEDURE 存储过程名 [ ( in | out| inout   参数名   参数类型) ]

begin

              存储过程体;

end

in：有参数无返回值，out：无参数有返回值，in | out：有参数有返回值，inout：有参数有返回值。
5.存储函数用法：

CREATE FUNCTION 存储过程名 (  参数名   参数类型,....)

returns 返回值类型 

begin

              函数体;

end

6.cast 用法