数据库后台Transact-SQL程序设计

T-SQL中,变量名需要以@符号开头.RETURN的含义不一定是返回某个返回值,而应推广为从查询或过程中无条件退出,之后的语句是不执行的.T-SQL中=默认是判断是否相等的符号,没有原生的赋值号,需要赋值时需要结合其他关键字与上下文,例如DECLARE,SELECT,SET等.

定义函数

函数必须有一个RETURN子句,用于返回函数值.函数头需要定义函数名,返回值类型和参数类型,返回值类型使用RETURNS声明.一般分为标量函数和表值函数.基本格式为:

CREATE FUNCTION [架构名.]函数名 (@参数 参数类型[,...])
RETURNS 返回值类型[TABLE根据需要描述定义]
AS BEGIN [函数体] 
RETURN 标量值[内联表值函数(查询语句)][多语句表值函数中表示结束]
END

标量函数

一般在使用时出现在SELECT语句目标列中,类似于聚集函数,需要返回一个值.

一个简单的例子,用来计算某个商品的总销售额:

CREATE FUNCTION 计算商品销售总额(@商品编号 int)
RETURNS money
AS BEGIN
    DECLARE @销售总额 money
    SELECT @销售总额 = (销售数量*销售额)
    FROM 销售表 WHERE 商品编号 = @商品编号
    RETURN @销售总额
END

调用语句为:SELECT 计算商品销售总额(10086)

内联表值函数

没有相关联的返回变量,没有函数体,直接在RETURNS后接TABLE,RETURN后接查询语句.其作用与视图相似.

一个通过客户姓名来查找其订单详情的函数:

CREATE FUNCTION 订单详情(@客户名)
RETURNS TABLE
AS RETURN(
    SELECT 商品编号, 客户表.客户姓名, 销售数量, 销售单价
    FROM 销售表 JOIN 客户表 
    ON 销售表.客户编号 = 客户表.客户编号 
    AND 客户表.客户姓名 = @客户名
)

多语句表值函数

类似于存储过程,可以用于实现复杂逻辑的过程,一般在查询语句中的FROM子句中调用.其返回值是一个含有特定结果的表,常常类似于派生表子查询调用.

如果需要,需要在RETURNS子句中对返回表表头进行定义.无需显式标注返回的表,结尾带上RETURN即可.往新建表中插入值一般是在函数体中利用INSERT INTO实现.

例如一个获取指定类别商品利润表的函数:

CREATE FUNCTION 计算总利润表(@className int)
RETURNS table @利润表(
    商品号 char(10),
    总利润 int
) AS
BEGIN
    INSERT INTO @利润表
        SELECT 商品表.商品号, SUM((销售单价-进货单价)*销售数量) AS 总利润
        FROM 商品表 JOIN 销售表 ON 商品表.商品号 = 销售表.商品号
        WHERE 商品表.商品号 IN(SELECT 商品号 FROM 商品表 WHERE 类别=@className)
        GROUP BY 商品表.商品号
        ORDER BY 总利润 DESC
    RETURN
END

分区表

分区表是将基本表通过水平分割的方式进行分区,将逻辑上一个整体的表放在悟空出货不同的文件中,便于数据管理和提高查询性能.

创建分区表一般分为三个步骤:建立分区函数,针对于逻辑表,告诉DBMS分割逻辑表的方法;建立分区方案,针对于物理文件,告诉DBMS如何放置分割的数据;创建表,使用分区函数和分分区方案具体创建一个分区表.

建立分区函数时,需要使用LEFT和RIGHT指明各个边界如何处理,左右的含义就是边界包含于哪边的范围中,例如RANGE LEFT FOR VALUES(10, 20, 30);的意思是分为四个区,分别为x<=10, 10<x<=20, 20<x<=30, x>30.

以下是一个分区表创建实例:

-- 建立分区函数
CREATE PARTITION FUNCTION 分区函数1(INT)
AS RANGE LEFT FOR VALUES(边界1, 边界2, 边界3);
GO
-- 建立分区方案
CREATE PARTITION SCHEME 分区方案1
AS PARTITION 分区函数
TO (文件组1, 文件组2, 文件组n);
GO
-- 建立分区表
CREATE TABLE 分区表1(
    主键 INT IDENTITY(1,1) PRIMARY KEY,
    属性1 VARCHAR(16) NOT NULL,
    属性n INT CHECK(属性n <= 限定值)
) ON 分区方案1(主键);

注意建立分区方案时,SCHEME不要和建立架构的SCHEMA混淆;若指定了将所有分区放入一个文件组,需要在分区方案中使用 ALL TO,且不能省略括号;建立分区表时和创建普通表一样,只不过需要在结尾写上ON 分区方案(分区列).

外键

数据定义语句经过DDL编译器编译后会存放在数据库的数据字典中.五种数据约束类型:主键PRIMARY KRY,外键FOREIGN KEY,唯一UNIQUE,缺省DEFAULT,检查CHECK.

创建外键的具体方法是:FOREIGN KEY 本表属性 REFERENCES 外表名(属性).其前面可以选择性的指明外键名称,一般无需指明CONSTRAINT 外键名.

例如创建关系模式为T5(a1, a6, a10)的表,其中(a1, a6)为主键,a1是T1的主键,a6是T3的主键,数据类型均为int:

CREATE TABLE T5(
    a1 int NOT NULL,
    a6 int NOT NULL,
    a10 int NOT NULL,
    PRIMARY KEY (a1, a6),
    FOREIGN KEY a1 REFERENCES T1(a1),
    FOREIGN KEY a6 REFERENCES T3(a6)
)

资料

部分关键字及其释义

参考

[1]何玉洁, 刘乃嘉. 全国计算机等级考试三级教程-数据库技术[M]. 高等教育出版社. 2020.
[2]Ben Forta. SQL必知必会[M]. 人民邮电出版社. 2020.
[3]史嘉权. 数据库系统概论[M]. 清华大学出版社. 2006.
[4]褚华, 霍邱艳. 软件设计师教程[M]. 清华大学出版社. 2018.
[5]王珊, 陈红. 数据库系统原理教程[M]. 清华大学出版社. 1998.
[6]汤小丹, 梁红兵, 哲凤屏, 汤子瀛. 计算机操作系统[M]. 西安电子科技大学出版社. 2014.