SQL存储过程详细介绍及语法
------------恢复内容开始------------
声明:存储过程并不推荐在开发中使用,某里巴巴制定的开发宝典中明令禁止使用存储过程。
那为什么还要学?被逼无奈,维护旧版系统不想全部推翻重来,只有硬着头皮要弄懂,才改的动。
创建存储过程
SQL Server创建存储过程:
create procedure 过程名
@parameter 参数类型
@paramater 参数类型
as
begin
end
执行存储过程
execute 过程名
实例
1.不带参数的存储过程
create procedure proc_sql1 as begin delclare @i int set @i = 26 while @i <1 begin print char(ascii('a') + @i) + '的ASCII码是:' + cast(ascii('a') + @i as varchar(5)) set @i = @i +1 end end
exec proc_sql1
输出结果:
1 a的ASCII码是: 97 2 b的ASCII码是: 98 3 c的ASCII码是: 99
~~~~~
26 z的ASCII码是:122
2.数据查询功能不带参数的存储过程
create procedure proc_sql2 as begin select * from employee where salary > 2000 end execute proc_sql2
在存储过程中可以包含多个select语句,显示姓名中含有“张”字的职工信息及其所在的仓库信息
create procedure pro_sql3 as begin select * from employee where name like '%张%' select * from warehouse where warehouseNo in (select warehouse from employee where name like '%张%') end go execute pro_sql3
3.带有输入参数的存储过程
找出三个数字中最大的数
create proc proc_sql4 @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) ) end execute proc_sql4 3 , 4 ,5
4.求阶乘之和
alter proc proc_sql5 @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)) end execute proc_sql5 5
5.带有输入参数的数据查询功能的存储过程
create proc proc_sql6 @minSalary int, @maxSalary int as begin select * from empolyee where salary > @minSalary and salary< @maxSalary end execute proc_sql6 8000,10000
6. 带有条件判断的插入功能的存储过程
create proc proc_sql7 @id varchar(30), @deptNo varchar(30), @name varchaer(50), @sex varchar(10), @salary int(10) as begin if Exists (select * from employee where id = @id) print ‘该员工号已经存在,请输入正确的id’ else begin if(Exists (select * from dept where de))
7.储存过程的自动执行
使用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为自动执行
8. 监控存储过程
可以使用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的次数
------------恢复内容结束------------