本章的内容分为三个小结:
1.创建存储过程
2.创建函数
3.创建视图
案例表为:Students表和Class表
Students:
Class:
第一个小结:
如何创建存储过程:
首先我们在什么情况下可以用到存储过程呢?一般情况下是在一些逻辑比较复杂的情况下选择使用存储过程,因为如果不使用存储过程的话,那么我们在写代码的时候,可能因为一些需求没有理解到位或者要修改什么东西,那么一旦写的不是存储过程,修改起来是很麻烦的一件事,而使用存储过程就方便了,不需要进入代码里修改,只需要修改SQL即可,可以节省很大一部分麻烦。
关键语句:
CREATE PROCEDURE [存储过程名字]
存储过程分为无参存储过程和有参存储过程:
无参数:
无参数的存储过程在写完创建语句后,直接就可以往下接后面的逻辑语句了,不需要在名字后面加()来填写参数。
调用:
exec [存储过程名字]
案例:
CREATE PROCEDURE Demo
AS
SET NOCOUNT ON
BEGIN
SELECT * FROM dbo.Students s LEFT JOIN dbo.Class c ON c.ClassId = s.ClassId
END
GO
结果:EXEC Demo
有参数:
有参数的存储过程比较麻烦一点,在调用的时候需要把所有的参数都要写出来,不能多也不能少,并且对类型也是有要求的,比如说如果定义了一个int类型的参数,那么你调用的时候这个参数必须要写int类型的内容,否则就会报错,不写还不行。
调用:
exec [存储过程名字] @xxx='',@xxx=''
案例:
ALTER PROCEDURE Demo
(
@Name NVARCHAR(50),
@Sex CHAR(1),
@Teacher NVARCHAR(50)
)
AS
SET NOCOUNT ON
BEGIN
SELECT s.Name,s.Sex,s.Age,s.ClassId,c.ClassId,c.ClassName,c.Teacher
FROM dbo.Students s LEFT JOIN dbo.Class c ON c.ClassId = s.ClassId
WHERE 1=CASE WHEN @Name='' THEN 1 ELSE CASE WHEN s.Name=@Name THEN 1 ELSE 0 END END
AND 1=CASE WHEN @Sex='' THEN 1 ELSE CASE WHEN s.Sex=@Sex THEN 1 ELSE 0 END END
AND 1=CASE WHEN @Teacher='' THEN 1 ELSE CASE WHEN c.Teacher=@Teacher THEN 1 ELSE 0 END END
END
GO
结果:
1.EXEC Demo @Name='熊大',@Sex='',@Teacher=N''
2.EXEC Demo @Name='',@Sex='M',@Teacher=N''
3.EXEC Demo @Name='',@Sex='',@Teacher=N'翠花老师'
在创建存储过程后如何修改存储过程呢?
语句:
ALTER PROCEDURE [存储过程名字]
第二个小结:
如何创建SQL函数:
在SQL中创建函数一般是把一些公用的模块或者数据打包,然后在使用的时候直接传递参数过去就可以直接使用。
关键语句:
CREATE FUNCTION [函数名字]
案例:
CREATE FUNCTION dbo.MyFunction
(
@num INT,
@num1 INT
)
RETURNS INT
AS
BEGIN
DECLARE @Result INT;
-- 在这里编写函数的逻辑
SET @Result = (@num * 2) + (@num1 * 3)
RETURN @Result;
END;
GO
结果:
1.直接传递参数查看结果
SELECT dbo.MyFunction(5,5) AS num
2.可以在查询表字段的时候给某个字段赋值:
SELECT Age=dbo.MyFunction(2,10),* FROM dbo.Students WHERE Name=N'光头强'
3.如果创建的函数返回的是一个表,那么这个函数还可以与其他表做关联,比如 LEFT JOIN、RIGHT JOIN等
第三个小结:
如何创建视图:
创建视图是在数据库中定义一个虚拟表,它实际上是一个基于 SQL 查询结果的可查询的对象。视图可以简化复杂查询、隐藏表结构、提供安全性和简化应用程序中的操作等。
关键语句:
CREATE VIEW [视图名字]
案例:
CREATE VIEW DemoView
AS
SELECT s.Name,s.Sex,s.Age,s.ClassId,c.ClassName,c.Teacher
FROM dbo.Students s
LEFT JOIN dbo.Class c ON c.ClassId = s.ClassId
GO
结果:
因为视图是一种虚拟的表,所以视图的用法就和普通的表一样,可以查询字段,where判断条件,与其他表关联等操作。
SELECT * FROM DemoView