第三章、SQL语言.ppt

CREATE TABLE Grade(Student_ID Char(11) REFERENCES Student(Student_ID) ON DELETE CASCADE,指定外键，在主键删除时，级联删除外键记录Course_ID Char(6) REFERENCES Course(Course_ID) ON DELETE CASCADE,指定外键，在主键删除时，级联删除外键记录Grade Number(3,0) Not Null,不能为空Primary Key(Student_ID,Course_ID)指定主键的第三种方法); （六）、CREATE PROCEDURE (MS SQLServer) CREATE PROCEDURE student_order(@id CHAR(11)) as DECLARE @order VARCHAR(200) DECLARE @s_name CHAR (24) DECLARE @class_name CHAR (30) DECLARE @avg_grade NUMERIC (5,2) DECLARE @no_student NUMERIC (3) DECLARE @avg_temp NUMERIC(5,2) DECLARE @i INTEGER SELECT @s_name=name,@class_name=class FROM student, class WHERE student.class_id = class.class_id AND student_id = @id IF @s_name IS NULL BEGIN SELECT @order = 无此学号！ PRINT @order RETURN END SELECT @avg_grade=AVG(grade) FROM grade WHERE student_id = @id IF @avg_grade IS NULL BEGIN SELECT @order = 此学生没有成绩记录！ PRINT @order RETURN END DECLARE avg_cur CURSOR FOR SELECT AVG(grade) cj FROM grade,student WHERE grade.student_id = student.student_id and class_id = (SELECT class_id FROM student WHERE student_id = @id) GROUP BY grade.student_id ORDER BY cj DESC SELECT @no_student=COUNT(*) FROM student WHERE class_id = (SELECT class_id FROM student WHERE student_id = @id) OPEN avg_cur SELECT @i= 1 FETCH NEXT FROM avg_cur INTO @avg_temp WHILE @@FETCH_STATUS = 0 BEGIN IF @avg_temp @avg_grade BEGIN SELECT @i = @i + 1 FETCH NEXT FROM avg_cur1 INTO @avg_temp END ELSE BREAK END CLOSE avg_cur DEALLOCATE avg_cur1 SELECT @order=rtrim(@s_name) + (学号: + rtrim(@id) + )在 + rtrim(@class_name) + 班(共 + ltrim(str(@no_student)) + 名学生)的名次为:第 + ltrim(str(@i)) + 名 PRINT @order CREATE OR REPLACE FUNCTION stu_order(id IN char) RETURN VARCHAR as student_order varchar(200); s_name char(24); class_name char(30); avg_grade number(5,2); no_student number(3); avg_temp number(5,2); i number(3); CURSOR avg_cur IS SELECT avg(grade) cj FROM grade,student WHERE grade.student_id = student.student_id and CLAS