Gespeicherte SQL-Prozeduren
- 高洛峰Original
- 2016-12-14 15:19:111581Durchsuche
1: Definition
Eine gespeicherte Prozedur ist eine Reihe von SQL-Anweisungen, die zur Ausführung bestimmter Funktionen entwickelt wurden. Sie werden in einer serverseitigen Datenbank kompiliert und gespeichert. Gespeicherte Prozeduren können verwendet werden, um die Ausführung zu beschleunigen SQL-Anweisungen.
Gespeicherte Prozeduren werden in gespeicherte Systemprozeduren und benutzerdefinierte gespeicherte Prozeduren unterteilt.
*Die gespeicherte Systemprozedur befindet sich in der Master-Datenbank, kann jedoch direkt in anderen Datenbanken aufgerufen werden. Beim Aufruf muss der Datenbankname nicht vor der gespeicherten Prozedur hinzugefügt werden, da beim Erstellen einer neuen Datenbank Die gespeicherte Systemprozedur
wird automatisch in der neuen Datenbank erstellt
*Benutzerdefinierte gespeicherte Prozeduren, die von Benutzern erstellt werden und eine bestimmte Funktion ausführen können, können sowohl Parameter als auch Rückgabewerte haben ., aber es unterscheidet sich von einer Funktion. Der Rückgabewert einer gespeicherten Prozedur zeigt nur an, ob die Ausführung erfolgreich ist. Sie können die gespeicherte Prozedur nicht direkt ausführen.
2: Vorteile gespeicherter Prozeduren
* Verbessern Sie die Vielseitigkeit und Portabilität von Anwendungen: Nachdem eine gespeicherte Prozedur erstellt wurde, kann sie mehrmals verwendet werden Das Programm ruft auf, ohne dass die SQL-Anweisung der gespeicherten Prozedur neu geschrieben werden muss. Und Datenbankexperten können
die gespeicherten Prozeduren jederzeit ändern, ohne den Quellcode des Programms zu beeinträchtigen, was die Portabilität des Programms erheblich verbessert.
*Kann Benutzerberechtigungen zum Betrieb der Datenbank effektiver verwalten: In der SQL Server-Datenbank kann der Systemadministrator den entsprechenden Datenzugriff steuern, indem er die Berechtigungen zum Ausführen einer bestimmten gespeicherten Prozedur einschränkt Verhindern Sie den Zugriff unbefugter Benutzer auf die Datenbank und gewährleisten Sie die Datensicherheit.
*Kann die Geschwindigkeit der Kompilierung gespeicherter Prozeduren verbessern. Wenn eine bestimmte Operation eine große Menge SQL-Code enthält oder mehrmals ausgeführt wird, wird die Verwendung einer gespeicherten Prozedur schneller ausgeführt als die direkte Verwendung einer einzelnen SQL Aussage viele.
* Reduzieren Sie die Belastung des Servers: Wenn der Benutzer mit Datenbankobjekten arbeitet und ein einzelner Aufruf verwendet wird, muss eine große Anzahl von SQL-Anweisungen über das Netzwerk übertragen werden. Wenn gespeicherte Prozeduren verwendet werden,
Anschließend können Sie den Prozessaufrufbefehl direkt senden, wodurch die Belastung des Netzwerks verringert wird.
3: Erstellen Sie eine gespeicherte Prozedur
SQL Server erstellt eine gespeicherte Prozedur:
@Parameter-Parametertyp
@Parameter-Parametertyp
. . .
as
end
Parameter in|out|in. . 🎜>Beginn
Befehlszeile oder Befehlsblock
create procedure pro_sql5 as begin select * from 职工 where 姓名 like '%张%' select * from 仓库 where 仓库号 in(select 仓库号 from 职工 where 姓名 like '%张%') end go execute pro_sql5
6：带有输入参数的存储过程
找出三个数字中的最大数：
create proc proc_sql6
@num1 int,
@num2 int,
@num3 int
as
begin
declare @max int
if @num1>@num2
set @max = @num1
else set @max = @num2
if @num3 > @max
set @max = @num3
print '3个数中最大的数字是:' + cast(@max as varchar(20))
endexecute proc_sql6 15, 25, 35
3个数中最大的数字是:35
7：求阶乘之和 如6! + 5! + 4! + 3! + 2! + 1
alter proc proc_sql7
@dataSource int
as
begin
declare @sum int, @temp int, @tempSum int
set @sum = 0
set @temp = 1
set @tempSum = 1
while @temp <= @dataSource
begin
set @tempSum = @tempSum * @temp
set @sum = @sum + @tempSum
set @temp = @temp + 1
end
print cast(@dataSource as varchar(50)) + '的阶乘之和为:' + cast(@sum as varchar(50))
endexecute proc_sql7 6
6的阶乘之和为：873
8：带有输入参数的数据查询功能的存储过程
create proc proc_sql8 @mingz int, @maxgz int as begin select * from 职工 where 工资>@mingz and 工资<@maxgz end
execute proc_sql8 2000,5000
9：带输入和输出参数的存储过程：显示指定仓库号的职工信息和该仓库号的最大工资和最小工资
create proc proc_sql9 @cangkuhao varchar(50), @maxgz int output, @mingz int output as begin select * from 职工 where 仓库号=@cangkuhao select @maxgz=MAX(工资) from 职工 where 仓库号=@cangkuhao select @mingz=MIN(工资) from 职工 where 仓库号=@cangkuhao end
declare @maxgz int, @mingz int execute proc_sql9 'wh1', @maxgz output, @mingz output select @maxgz as 职工最大工资, @mingz as 职工最小工资
10：带有登录判断功能的存储过程
create proc proc_sql10
@hyuer varchar(50),
@hypwd varchar(50)
as
begin
if @hyuer = 'hystu1'
begin
if @hypwd = '1111'
print '用户名和密码输入正确'
else
print '密码输入错误'
end
else if @hyuer = 'hystu2'
begin
if @hypwd = '2222'
print '用户名和密码输入正确'
else
print '密码输入错误'
end
else if @hyuer = 'hystu3'
begin
if @hypwd = '3333'
print '用户名和密码输入正确'
else
print '密码输入错误'
end
else
print '您输入的用户名不正确，请重新输入'
endexecute proc_sql10 'hystu1', '11'
密码输入错误
11：带有判断条件的插入功能的存储过程
create proc proc_sq111
@zghao varchar(30),
@ckhao varchar(30),
@sname varchar(50),
@sex varchar(10),
@gz int
as
begin
if Exists(select * from 职工 where 职工号=@zghao)
print '该职工已经存在，请重新输入'
else
begin
if Exists(select * from 仓库 where 仓库号=@ckhao)
begin
insert into 职工(职工号, 仓库号, 姓名, 性别, 工资)
values(@zghao, @ckhao, @sname, @sex, @gz)
end
else
print '您输入的仓库号不存在，请重新输入'
end
endexecute proc_sq111 'zg42', 'wh1', '张平', '女', 1350
12： 创建加密存储过程
create proc proc_enerypt with encryption as begin select * from 仓库 end
所谓加密存储过程，就是将create proc 语句的原始文本转换为模糊格式，模糊代码的输出在SQL Server的任何目录视图中都能直接显示
13： 查看存储过程和功能代码信息
select name, crdate from sysobjects where type='p'
查看指定存储过程的属性信息：
execute sp_help proc_sql1
查看存储过程所使用的数据对象的信息
execute sp_depends proc_sql2
查看存储过程的功能代码
execute sp_helptext proc_sql9
14：重命名存储过程名
execute sp_rename 原存储过程名, 新存储过程名
15：删除存储过程
drop 过程名
带有判断条件的删除存储过程
if Exists(select * from dbo.sysobjects where name='proc_sql6' and xtype='p')
begin
print '要删除的存储过程存在'
drop proc proc_sq16
print '成功删除存储过程proc_sql6'
end
else
print '要删除的存储过程不存在'16：存储过程的自动执行
使用sp_procoption系统存储过程即可自动执行一个或者多个存储过程，其语法格式如下：
sp_procoption [@procName=] 'procedure', [@optionName=] 'option', [@optionValue=] 'value'
各个参数含义如下：
[@procName=] 'procedure'： 即自动执行的存储过程
[@optionName=] 'option'：其值是startup，即自动执行存储过程
[@optionValue=] 'value'：表示自动执行是开(true)或是关(false)
sp_procoption @procName='masterproc', @optionName='startup', @optionValue='true'
利用sp_procoption系统函数设置存储过程masterproc为自动执行
17：监控存储过程
可以使用sp_monitor可以查看SQL Server服务器的各项运行参数，其语法格式如下：
sp_monitor
该存储过程的返回值是布尔值，如果是0，表示成功，如果是1，表示失败。该存储过程的返回集的各项参数的含义如下：
*last_run: 上次运行时间
*current_run：本次运行的时间
*seconds: 自动执行存储过程后所经过的时间
*cpu_busy:计算机CPU处理该存储过程所使用的时间
*io_busy:在输入和输出操作上花费的时间
*idle：SQL Server已经空闲的时间
*packets_received：SQL Server读取的输入数据包数
*packets_sent：SQL Server写入的输出数据包数
*packets_error:SQL Server在写入和读取数据包时遇到的错误数
*total_read: SQL Server读取的次数
*total_write: SQLServer写入的次数
*total_errors: SQL Server在写入和读取时遇到的错误数
*connections:登录或尝试登录SQL Server的次数
