第5章2 T-SQL编程.docVIP

5 T-SQL编程 5.3 存储过程SQL指令序列，存储在数据库中，在服务器上运行。5.3.1 系统存储过程示例 存储在master库中，列出SQL Server中的数据库可以通过网关访问的数据库。EXEC sp_databases 返回当前库中可查询对象的列表 EXEC sp_tables 返回数据库对象、用户定义数据类型。EXEC sp_help 列出指定表的主键 EXEC sp_pkeys SC 列出指定表的外键 EXEC sp_fkeys C 返回指定表的列信息 EXEC sp_columns C 返回指定的表或视图上的所有索引表。 EXEC sp_statistics SC 返回当前环境中的存储过程列表。 EXEC sp_stored_procedures 扩展过程（只能在master中执行） 报告帐户、帐户类型、帐户的特权级别等信息 EXEC xp_logininfo 执行操作系统命令，并以文本行方式返回任何输出。 EXEC xp_cmdshell dir *.exe 5.3.2 用户存储过程示例 用户创建、存储在用户数据库中的存储过程，只能在用户数据库中执行。 1、无参的存储过程 --创建存储过程 USE Test; GO CREATE PROCEDURE AddScore AS UPDATE SC SET SCScore = SCScore + 2; Select * from SC GO 注：存储过程或触发器中不允许使用USE数据库语句。 --执行存储过程 exec AddScore -- 当前数据库是Test exec Test.dbo.AddScore -- 当前数据库不是Test --修改存储过程 ALTER PROCEDURE AddScore AS UPDATE SC SET SCScore = SCScore - 10; Select * from SC GO 2、有参的存储过程 --创建存储过程 CREATE PROCEDURE add_proc1 @num1 INT = 0, -- 缺省值 @num2 INT = 0-- 缺省值 AS DECLARE @num3 INT SET @num3 = @num1 + @num2 PRINT @num3--执行存储过程EXEC add_proc1 2,3 EXEC add_proc1 2 --赋给第一个参数 3、有输出参数的存储过程 --创建存储过程CREATE PROCEDURE add_proc2 @num1 INT = 0, @num2 INT = 0, @num3 INT OUTPUT -- 输出参数 AS SET @num3 = @num1 + @num2; --执行存储过程DECLARE @num AS INT EXEC add_proc2 12, 23, @num OUTPUT PRINT @num 4、练习1：创建存储过程，求三个数中最大数 --创建存储过程CREATE PROCEDURE maxnumber @a INT, @b INT, @c INT AS BEGIN DECLARE @max INT SET @max=@a IF @b@max SET @max=@b IF @c@max SET @max=@c PRINT 最大数是+CONVERT(VARCHAR,@max)END --执行存储过程EXECUTE maxnumber 90 ,70 ,108 5、练习2：创建存储过程，根据给定的院系名计算平均成绩 CREATE PROCEDURE AvgScore1 @dept char(10), @avg float OUTPUT AS SELECT @avg=avg(SCscore) FROM SC WHERE Sno in (SELECT Sno FROM S WHERE Sdept=@dept) DECLARE @avg AS INT exec AvgScore1 信科系,@avg OUTPUT PRINT @avg 6、有返回值的存储过程 返回值的类型：只能是整型。（数值型取整，非数值型出错） 创建存储过程，根据给定的院系名计算平均成绩 CREATE PROCEDURE AvgScore2 @dept char(10) AS BEGIN DECLARE @avg float SELECT @avg=avg(SCscore) FROM SC WHERE Sno in (SELECT Sno F