1: Definition
A stored procedure is a set of SQL statements designed to accomplish specific functions. It is compiled and stored in a server-side database. Stored procedures can be used to speed up the execution of SQL statements.
Stored procedures are divided into system stored procedures and custom stored procedures.
* The system stored procedure is in the master database, but can be called directly in other databases, and there is no need to add the database name before the stored procedure when calling, because when a new database is created, the system stored procedure
will be automatically created in the database * Custom stored procedure, a stored procedure created by the user and capable of completing a specific function. The stored procedure can have both parameters and return values, but it is different from the function. The return value of the stored procedure It just indicates whether the execution is successful. It cannot be called directly like a function. You can only use execute to execute the stored procedure.
2: Advantages of stored procedures
* Improve the versatility and portability of applications: After a stored procedure is created, it can be called multiple times in the program without having to rewrite the SQL statement of the stored procedure. And database professionals can modify the stored procedures at any time without affecting the program source code, which greatly improves the portability of the program.
* Can more effectively manage user permissions to operate the database: In the Sql Server database, the system administrator can control the corresponding data access by restricting the permissions to execute a certain stored procedure,
to avoid unauthorized access. Authorize users to access the database to ensure data security. Q *can increase the speed of SQL. The storage procedure is compiled. If a certain operation contains a large amount of SQL code or is executed separately, then the use of the storage procedure is much faster than the use of a single SQL statement directly.
. Just call the command, which reduces the burden on the network.
3: Create a stored procedure
SQL Server creates a stored procedure:
Create procedure Procedure name
Parameter type
Parameter type
. . . as Create a stored procedure:
use using through out out’s out’s out’s out’s out’s out’s’ out’s out’s out through out out through out out out out out through out out out out out out out out out out out out out out out out out out out out out out out out out's'''‐‐‐‐‐‐‐‐down''' r and _ ‐ to Begin
Command line or command block
exception
command Ring or command block
END
4: Storage procedure without parameters © RreeerRreerreeee
5: Data query function without parameter storage processes
Rreee
create procedure proc_sql1
as
begin
declare @i int
set @i=0
while @i<26
begin
print char(ascii('a') + @i) + '的ASCII码是: ' + cast(ascii('a') + @i as varchar(5))
set @i = @i + 1
end
endCopy after login
can include multiple select statements in the storage procedure, displayed, display Employee information containing the character "张" in the name and the warehouse information where it is located,
create procedure pro_sql5
as
begin
select * from 职工 where 姓名 like '%张%'
select * from 仓库 where 仓库号 in(select 仓库号 from 职工 where 姓名 like '%张%')
end
go
execute pro_sql5
Copy after login
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))
endCopy after login
execute proc_sql6 15, 25, 35
Copy after login
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))
endCopy after login
execute proc_sql7 6
Copy after login
6的阶乘之和为：873
8：带有输入参数的数据查询功能的存储过程
create proc proc_sql8
@mingz int,
@maxgz int
as
begin
select * from 职工 where 工资>@mingz and 工资<@maxgz
end
Copy after login
execute proc_sql8 2000,5000
Copy after login
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
Copy after login
declare @maxgz int, @mingz int
execute proc_sql9 'wh1', @maxgz output, @mingz output
select @maxgz as 职工最大工资, @mingz as 职工最小工资
Copy after login
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 '您输入的用户名不正确，请重新输入'
endCopy after login
execute proc_sql10 'hystu1', '11'
Copy after login
密码输入错误
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
endCopy after login
execute proc_sq111 'zg42', 'wh1', '张平', '女', 1350
Copy after login
12： 创建加密存储过程
create proc proc_enerypt
with encryption
as
begin
select * from 仓库
end
Copy after login
所谓加密存储过程，就是将create proc 语句的原始文本转换为模糊格式，模糊代码的输出在SQL Server的任何目录视图中都能直接显示
13： 查看存储过程和功能代码信息
select name, crdate from sysobjects where type='p'
Copy after login
查看指定存储过程的属性信息：
execute sp_help proc_sql1
Copy after login
查看存储过程所使用的数据对象的信息
execute sp_depends proc_sql2
Copy after login
查看存储过程的功能代码
execute sp_helptext proc_sql9
Copy after login
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 '要删除的存储过程不存在'Copy after login
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'
Copy after login
利用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的次数
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
