第13章 数据库对象
返回到 『四级数据库』13．1 存储过程(PROCEDURE)
13．1．1 存储过程基本概念
- 在关系数据库中，SQL语言是应用程序和数据库管理之间的主要编程接口；
- 使用SQL语言编写代码时，可用两种方法存储和执行代码：
- 在客户端存储代码，并创建向数据库管理系统发送SQL命令，并处理返回结果的应用程序；（现编现用）
- 事先将这些发送的SQL语句存储在数据库管理系统中，
- 这些存储在数据库管理系统中的SQL语句就是存储过程（提前编好,将来套用）然后再创建执行存储过程并处理返回结果的应用程序。
-
使用存储过程的好处：
- 模块化程序设计：只需创建一次存储过程并将其存储在数据库中，以后就可以在应用程序中多次调用存储过程；
- 提高性能：系统在创建存储过程时对其进行分析和优化，并在第一次执行时进行语法检查和编译，编译好的代码放入内存中，以后再执行此存储过程时，只需直接执行内存中的代码，从而提高代码的执行效率；
- 减少网络流量：一个需要数百行SQL代码完成的操作现在只需一条执行存储过程的代码即可实现，因此，不再需要在网络中发送这些多语句；
- 可作为安全机制使用：执行存储过程和执行SQL语句权限有区别
13．1．2 创建和执行存储过程
创建存储过程的SQL语句为：CREATE PROCEDURE，语法格式为：
创建存储过程
CREATE PROCEDURE 存储过程名
[{@ 参数名 数据类型}[=default][OUTPUT]
][,…n]
AS
SQL语句[…n]
by 未闻花名🌸vwhm.net
其中：
- default:表示参数的默认值。如果定义了默认值，则在执行存储过程时，可以不必指定该参数的值，默认值必须是常量或NULL；
- OUTPUT（有返回值）：表明参数是输出参数，该选项的值可以返回给存储过程的调用者。
2、执行存储过程的SQL语句是EXECUTE，语法格式：
EXECUTE（相当于call） 存储过程名 [实参[，OUTPUT][，…n]]
-
执行有多个输入参数的存储过程时，参数的传递方式有两种：
- 按参数位置传递值：指执行存储过程的EXEC语句中的实参的排列顺序必须与定义存储过程时定义的参数的顺序一致；
- 按参数名传递值：指执行存储过程的EXEC语句中, 要指明定义存储过程时指定的参数的名字以及此参数的值，而不关心参数的定义顺序。
-
注意：
- 在执行有输出参数的存储过程时，执行语句中的变量名的后边要加上OUTPUT修饰符；
- 在调用有输出参数的存储过程时，与输出参数对应的是一个变量，此变量用于保存输出参数返回的结果；
13．2 用户自定义函数(FUNCTION)
13．2．1 基本概念
- 用户定义函数可以扩展数据操作的功能，它在概念上类似于一般的程序设计语言中定义的函数。
13．2．2 创建和调用标量函数
标量函数—->返回单个数据值的函数；
1、定义标量函数
定义标量函数
CREATE FUNCTION [拥有者名.] 函数名
（[{@ 参数名[AS]标量数据类型[=default]}[,…n]]）
RETURNS 返回值类型
[AS]
BEGIN
函数体
RETURN 标量表达式
END
by 未闻花名🌸vwhm.net
同存储过程一样，函数的参数也可以有默认值。
- 标量表达式：指定标量函数返回的标量值。
-
调用标量函数：
当调用标量函数时，必须提供至少由两部分组成的名称：函数拥有者名和函数名（写出就行）。
可在任何允许出现表达式的SQL语句中调用标量函数，只要类型一致；
13．2．3 创建和调用内嵌表值函数
- 内嵌表值函数，没有函数体
2、多语句表值函数（相当于标量函数和内嵌表值函数的结合体）
多语句表值函数的返回值是一个表，
因此对多语句表值函数的使用也是放在SELECT语句的FROM子句中。
多语句表值函数
CREATE FUNCTION [拥有者名.] 函数名
（[{@ 参数名[AS]标量数据类型[=default]}[,…n]]）
RETURNS 返回变量 TABLE <表定义>
[AS]
BEGIN
函数体
RETURN
END
<表定义>：：=（{列定义|表约束}[,…n]）
by 未闻花名🌸vwhm.net
13．3 触发器(TRIGGER)
13．3．1 触发器基本概念
- 触发器是一种特殊的存储过程，其特殊性在于它不需要由用户来调用，而是当用户对表中的数据进行UPDATE、INSERT或DELETE操作时自动触发执行；用于保证业务规则和数据完整性。
-
触发器常用于下列场合：
- 完成比CHECK的约束更复杂的数据约束；
- 为保证数据库性能而维护的非规范化数据；
- 实现复杂的业务规则，可使业务的处理任务自动进行。
13．3．2 创建触发器
创建触发器的语句：CREATE TRIGGER，
语法为：
创建触发器
CREATE TRIGGER 触发器名称
ON {表名| 视图名}
[WITH ENCRYPTION]
{FOR |AFTER|INSTEAD OF}{[INSERT][，][DELETE][，][UPDATE]}
AS
[{IF UPDATE（column）…}]
SQL语句
by 未闻花名🌸vwhm.net
触发器名称在数据库中必须是惟一的；
- ON子句用于指定在其上执行触发器的表；
- AFTER：指定触发器只有在引发的SQL语句中指定的操作都已成功执行，并且所有的约束检查也成功完成后，才执行此触发器，这种触发器称为后触发型触发器；
- FOR：作用同AFTER；
-
INSTEAD OF：指定执行触发器而不是执行引发触发器执行的SQL语句，从而替代触发语句的操作，这种触发器称为前触发型触发器；
- INSTERT、DELETE或UPDATE是引发触发器执行的操作，若同时指定多个操作，则各操作之间用逗号分隔；
-
创建触发器时，需要注意：
- 在一个表上可以建立多个名称不同、类型各异的触发器，每个触发器可由增删改三个操作引发；
- 大部分Transact-SQL语句都可用在触发器中，但也有一些限制；
- 在触发器定义中，可以使用IF UPDATE子句测试在INSERT和UPDATE语句中是否对指定字段有影响；
- 通常不要在触发器中返回任何结果。
-
创建后触发型触发器
使用FOR或AFTER选项定义的触发器为后触发的触发器，即只有在引发触发器执行语句中指定的操作都已完成执行，并且所有的约束检查也成功完成后，才执行的触发器；
-
创建前触发型触发器
使用INSTEAD OF 选项定义的触发器为前触发型触发器，在该触发器中，指定执行触发器而不是执行引发触发器执行的SQL语句，从而替代引发语句的操作。
13．4 查看、修改及删除对象
13．4．1 查看对象
对于创建好的存储过程、函数, 可通过企业管理器和查询分析器查看这些对象的代码；
13．4．2 修改对象
- 修改存储过程：ALTER PROCEDURE
修改存储过程
ALTER PROC 存储过程名
[{@参数名 数据类型}[=default][OUTPUT]][,…n]
AS
SQL语句[…n]
by 未闻花名🌸vwhm.net
修改与定义的语句基本一致，只将CREARE PROC 改成ALTER PROC；
2、修改用户自定义函数：
修改与定义的语句基本一致，只将CREARE FUNCTION 改成ALTER FUNCTION；
3、修改触发器：
修改与定义的语句基本一致，只将CREARE TRIGGER 改成ALTER TRIGGER；
13．4．3 删除对象
- 删除存储过程：
DROP PROCEDURE {存储过程名}[，…n]；
2、删除用户自定义函数
DROP FUNCTION {[拥有者名.]函数名}[,…n]
3、删除触发器
DROP TRIGGER {触发器名}[,…n]
附录: 存储过程10个经典实例
1. 创建存储过程
创建存储过程
create proc | procedure pro_name
[{@参数数据类型} [=默认值] [output],
{@参数数据类型} [=默认值] [output],
….
]
as
SQL_statements
by 未闻花名🌸vwhm.net
2. 创建不带参数的存储过程
创建不带参数的存储过程
–创建存储过程
if (exists (select * from sys.objects where name = ‘proc_get_girl‘))
drop proc proc_get_girl
go
create proc proc_get_girl
as
select * from girl;
–调用、执行存储过程
exec proc_get_girl;
by 未闻花名🌸vwhm.net
3.修改存储过程
修改存储过程
–修改存储过程
alter proc proc_get_girl
as
select * from girl;
by 未闻花名🌸vwhm.net
4.带参存储过程
带参数的存储过程
–带参存储过程
if (object_id(‘proc_find_lolita‘, ‘P’) is not null)
drop proc proc_find_lolita
go
create proc proc_find_lolita(@startAge int, @endAge int)
as
select * from girl where age between @startAge and @endAge
go
exec proc_find_lolita 11, 14;
by 未闻花名🌸vwhm.net
5.带通配符参数存储过程
带通配符参数的存储过程
–带通配符参数存储过程
if (object_id(‘proc_findGirlByName‘, ‘P’) is not null)
drop proc proc_findGirlByName
go
create proc proc_findGirlByName(@girlName varchar(20) = ‘%Mathilda%’, @animeName varchar(20) = ‘%’)
as
select * from girl where girlName like @girlName and animeName like @animeName;
go
exec proc_findGirlByName;
exec proc_findGirlByName ‘%面码%’, ‘未闻花名%’;
by 未闻花名🌸vwhm.net
6. 带输出参数存储过程 喵喵喵???
带输出参数的存储过程
if (object_id(‘proc_getStudentRecord‘, ‘P’) is not null)
drop proc proc_getStudentRecord
go
create proc proc_getStudentRecord(
@id int, –默认输入参数
@name varchar(20) out, –输出参数
@age varchar(20) output –输入输出参数
)
as
select @name = name, @age = age from student where id = @id and sex = @age;
go
—
declare @id int,
@name varchar(20),
@temp varchar(20);
set @id = 7;
set @temp = 1;
exec proc_getStudentRecord @id, @name out, @temp output;
select @name, @temp;
print @name + ‘#’ + @temp;
by 未闻花名🌸vwhm.net
7. 不缓存存储过程
不缓存 存储过程
–WITH RECOMPILE 不缓存
if (object_id(‘proc_temp‘, ‘P’) is not null)
drop proc proc_temp
go
create proc proc_temp
with recompile
as
select * from girl;
go
exec proc_temp;
by 未闻花名🌸vwhm.net
8.加密存储过程
加密存储过程
–加密WITH ENCRYPTION
if (object_id(‘proc_temp_encryption‘, ‘P’) is not null)
drop proc proc_temp_encryption
go
create proc proc_temp_encryption
with encryption
as
select * from student;
go
exec proc_temp_encryption;
exec sp_helptext ‘proc_temp‘;
exec sp_helptext ‘proc_temp_encryption‘;
by 未闻花名🌸vwhm.net
9.带游标参数存储过程
带游标参数存储过程
if (object_id(‘proc_cursor‘, ‘P’) is not null)
drop proc proc_cursor
go
create proc proc_cursor
@cur cursor varying output
as
set @cur = cursor forward_only static for
select id, name, age from student;
open @cur;
go
–调用
declare @exec_cur cursor;
declare @id int,
@name varchar(20),
@age int;
exec proc_cursor @cur = @exec_cur output;–调用存储过程
fetch next from @exec_cur into @id, @name, @age;
while (@@fetch_status = 0)
begin
fetch next from @exec_cur into @id, @name, @age;
print ‘id: ‘ + convert(varchar, @id) + ‘, name: ‘ + @name + ‘, age: ‘ + convert(char, @age);
end
close @exec_cur;
deallocate @exec_cur;–删除游标
by 未闻花名🌸vwhm.net
10. 分页存储过程
分页存储过程
—存储过程、row_number完成分页
if (object_id(‘pro_page‘, ‘P’) is not null)
drop proc proc_page
go
create proc pro_page
@startIndex int,
@endIndex int
as
select count(*) from product
;
select * from (
select row_number() over(order by pid) as rowId, * from product
) temp
where temp.rowId between @startIndex and @endIndex
go
–drop proc pro_page
exec pro_page 1, 4
—
–分页存储过程
if (object_id(‘pro_page’, ‘P’) is not null)
drop proc pro_stu
go
create procedure pro_stu(
@pageIndex int,
@pageSize int
)
as
declare @startRow int, @endRow int
set @startRow = (@pageIndex – 1) * @pageSize +1
set @endRow = @startRow + @pageSize -1
select * from (
select *, row_number() over (order by id asc) as number from student
) t
where t.number between @startRow and @endRow;
exec pro_stu 2, 2;
by 未闻花名🌸vwhm.net
.
