变量分为全局变量和局部变量。
p.s.
语句格式:
CREATE PROCEDURE 存储过程名[;版本号]
[{@参数 数据类型} [VARYING] [=默认值] [OUTPUT],...]
[WITH {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}]
[FOR REPLICATION]
AS
SQL语句
例1:创建一个简单的存储过程,用于查看学生表的所有记录
ALTER PROCEDURE EXP1
AS
SELECT * FROM s
例2:嵌套调用存储过程,求总成绩最高的学生信息:
CREATE PROCEDURE EXP6 @sno1 char(10) OUTPUT
AS
SELECT TOP 1 @sno1 = sc.sno
FROM sc
GROUP BY sc.sno
ORDER BY SUM(sc.grade) DESC
GO
CREATE PROCEDURE EXP7
AS
DECLARE @sno2 char(10)
EXECUTE EXP6 @sno2 OUTPUT
SELECT *
FROM s
WHERE sno = @sno2;
GO
例3:利用INSTEAD OF触发器实现级联删除,即若在s表中删除一学生数据,则在sc表中应该同时删除有关学生的成绩信息。
CREATE TRIGGER tr1_S
ON s
INSTEAD OF DELETE
AS
BEGIN TRANSACTION
DELETE FROM sc
WHERE sno IN (SELECT sno FROM DELETED)
DELETE FROM s
WHERE sno IN (SELECT sno FROM DELETED)
COMMIT TRANSACTION;
运行结果:
CREATE TRIGGER tr1_S
ON s
INSTEAD OF DELETE
AS
BEGIN TRANSACTION
DELETE FROM sc
WHERE sno IN (SELECT sno FROM DELETED)
DELETE FROM s
WHERE sno IN (SELECT sno FROM DELETED)
COMMIT TRANSACTION;
> Affected rows: 0
> 时间: 0.017s