[第07章存储过程和触发器.pptVIP

执行： exec student_info1 王林,计算机基础 exec student_info1 @name=王林,@cname=计算机基础 * * Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. * * (3) 使用带有通配符参数的存储过程 【例7.3】从三个表的联接中返回指定学生的学号、姓名、所选课程名称及该课程的成绩。该存储过程在参数中使用了模式匹配，如果没有提供参数，则使用预设的默认值。 USE XSCJ IF EXISTS (SELECT name FROM sysobjects WHERE name = st_info AND type = P) DROP PROCEDURE st_info GO CREATE PROCEDURE st_info @name varchar(30) = 刘% AS SELECT a.学号,a.姓名,c.课程名,b.成绩 FROM XS a INNER JOIN XS_KC b ON a.学号 =b.学号 INNER JOIN KC c ON c.课程号= b.课程号 WHERE 姓名 LIKE @name GO Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. 执行： EXEC st_info /*使用默认值参数*/ EXEC st_info 王% /*传递给@name的实参为‘王%’ */ * * Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. * * (4) 使用带OUTPUT参数的存储过程 OUTPUT变量必须在创建表和使用该变量时都进行定义。 【例7.4】用于计算指定学生的选修过课程的总学分，存储过程中使用了一个输入参数和一个输出参数。 USE XSCJ GO IF EXISTS(SELECT name FROM sysobjects WHERE name = totalcredit AND type = P) DROP PROCEDURE totalcredit GO USE XSCJ GO CREATE PROCEDURE totalcredit @name varchar(40), @total int OUTPUT AS SELECT @total= SUM(XS_KC.学分) FROM XS,XS_KC WHERE 姓名=@name AND XS.学号= XS_KC.学号 GROUP BY XS.学号 GO Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. 执行（定义时的参数名和调用时的参数名不一定要匹配，但数据类型和参数位置必须匹配） USE XSCJ DECLARE @t_credit char(20) ,@total int SET @t_credit=程明 EXECUTE totalcredit @t_credit ,@total OUTPUT SELECT @t_credit AS 姓名,@total AS 总学分 * * Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. * * (5) 使用OUTPUT游标参数的存储过程 OUTPUT 游标参数用于返回存储过程的局部游标。 【例7.5】在 XSCJ数据库的XS表上声明并打开一个游标。 USE XSCJ IF EXISTS (SELECT name FROM sysobjects WHERE name = st_cursor and type = P) DROP PROCEDURE st_cursor GO CREATE PROCEDURE st_cursor @st