SQL stored procedures

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(&#39;a&#39;) + @i) + &#39;的ASCII码是: &#39; + cast(ascii(&#39;a&#39;) + @i as varchar(5))   
         set @i = @i + 1  
      end  
end

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

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))  
end

execute 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)) + &#39;的阶乘之和为:&#39; + cast(@sum as varchar(50))  
end

execute 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 '您输入的用户名不正确，请重新输入'  
end

execute 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  
end

execute 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的次数