SQL Server存储过程——T-SQL

参考视频：https://www.bilibili.com/video/BV1Xt4y1X7wc?seid=13735399956839360622

一、存储过程特点

1、模块化

存储过程根据功能创建，创建后存于数据库中，即可被反复调用，也可以独立应用程序，对其进行修改。

2、提高执行速度

存储过程在首次运行时编译，之后便缓存在内存中，再次调用就不必进行编译，也不必从磁盘读入内存。

3、降低网络通信量

数百行T-SQL语句的存储过程，可以调用存储过程，通过传参，一次执行。不需要再网络中发送数百行代码，减少了T-SQL语句在网络上的传输量。

4、保证系统安全性

系统管理员可以设置用户对存储过程的操作权限，避免非授权用户对数据访问。

二、存储过程类型

1、系统存储过程

定义在系统数据库master中，前缀为sp_。

系统存储过程允许系统管理员执行修改系统表的数据库管理任务，可在任何一个数据库中执行。

创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。

2、用户自定义存储过程

用户基于某一特定数据库，创建存储过程以完成指定功能。

存储过程名称不能以sp_为前缀，且必须唯一，可以附带参数，由用户创建和维护。

3、临时存储过程

属于本地存储过程，名称前有一个“#”是局部临时存储过程，只能在一个用户会话中使用；名称前有两个“##”是全局临时存储过程，可在所有用户会话中使用。

使用临时存储过程，必须创建本地连接，当SQL Server关闭后，临时存储过程会自动删除。

4、远程存储过程

从远程服务器上调用的存储过程。

5、扩展存储过程

在SQL Server环境之外执行的动态链接库称为扩展存储过程，其前缀是sp_。

使用时需要先加载到SQL Server系统中，并按照使用存储过程的方法执行。

扩展存储过程只能添加到master数据库中。

三、创建存储过程的方法

1、利用SSMS图形化界面工具

2、利用Transact-SQL语句

1）变量定义

declare @a int

declare @user nvarchar(50)

2）变量赋值

set @a=10

select @user =user_name from t_user where id=10 

update t_user set @user=user_name where id=10

3）变量打印

print @a

print @user 

4） 创建存储过程

 5）执行存储过程

 6）示例：

CREATE PROC pro_goods_info

AS

BEGIN

     SELECT goods_name,classification_name,unit_price

     FROM t_goods g JOIN t_goods_classification gc 

     ON g.classification_id = gc.classification_id

END

---

EXEC pro_goods_info

CREATE PROC pro_stu1

      @num char(6)='200500',@name char(20),@sex char(2) 

AS

     BEGIN

                INSERT INTO t_student(sno,sname,ssex) VALUES (@num,@name,@sex)

                RETURN 1

     EDN

---

DECLARE @status

EXEC @status = pro_stu1 '200501'，'xiaoming'，'男'

CREATE PROC pro_stu 

      @num char(6),@g int output

AS

       BEGIN

              SELECT @g = SUM(grade)  FROM t_sc WHERE sno=@num and grade is not null

       END

---

DECLARE @grade_sum

EXEC pro_stu '200501',@grade_sum output

CREATE PROC pro_st_info @name='刘%'

AS 

BEGIN 

           SELECT a.sname,c.cname,b.grade FROM t_student a INNER JOIN t_sc b

           ON a.sno=b.sno INNER JOIN t_course c

           ON c.cno=b.cno

           WHERE sname LIKE @name

END

---

EXEC pro_st_info

EXEC pro_st_info '王%'

7）其他命令

修改：ALTER

---

ALTER PROC pro_stu_info

        @name char(8),@cname char(16)

AS   BEGIN

SELECT a.sno,sname,cname,grade,credit FROM t_student a INNER JOIN t_sc b ON a.sno=b.sno INNER JOIN t_course c ON c.cno=b.cno WHERE  a.sname=@name and t.cname=@cname

END

---

删除：DROP

---

DROP PROC pro_stu_info