SQL 저장 프로시저

1: 정의

저장 프로시저는 특정 기능을 완료하도록 설계된 SQL 문 집합입니다. 저장 프로시저를 사용하여 서버 측 데이터베이스에 저장할 수 있습니다. SQL문.

저장 프로시저는 시스템 저장 프로시저와 사용자 지정 저장 프로시저로 구분됩니다.

*시스템 저장 프로시저는 마스터 데이터베이스에 있지만 다른 데이터베이스에서 직접 호출할 수 있으며 호출 시 새 데이터베이스를 생성할 때 저장 프로시저 앞에 데이터베이스 이름을 추가할 필요가 없습니다. 시스템 저장 프로시저

가 새 데이터베이스

에 자동으로 생성됩니다. *사용자가 생성하고 특정 기능을 완료할 수 있는 사용자 정의 저장 프로시저에는 매개 변수와 반환 값이 모두 포함될 수 있습니다. . 그러나 함수와는 다릅니다. 저장 프로시저의 반환 값은 실행 성공 여부만 나타냅니다.

은 함수처럼 직접 호출할 수 없습니다.

2: 저장 프로시저의 장점

* 애플리케이션의 다양성 및 이식성 향상: 저장 프로시저를 생성한 후에는 여러 번 사용할 수 있습니다. 프로그램은 저장 프로시저의 SQL 문을 다시 작성할 필요 없이 호출합니다. 그리고 데이터베이스 전문가는

프로그램 소스 코드에 영향을 주지 않고 언제든지 저장 프로시저를 수정할 수 있으므로 프로그램의 이식성이 크게 향상됩니다.

*데이터베이스 운영을 위한 사용자 권한을 보다 효과적으로 관리할 수 있습니다. SQL Server 데이터베이스에서는 시스템 관리자가 특정 저장 프로시저의 실행 권한을 제한하여 해당 데이터에 대한 액세스를 제어할 수 있습니다.

권한이 없는 사용자가 데이터베이스에 접근하는 것을 방지하고 데이터 보안을 보장합니다.

*SQL의 속도를 향상시킬 수 있습니다. 특정 작업에 많은 양의 SQL 코드가 포함되어 있거나 여러 번 실행되는 경우 단일 SQL 문을 직접 사용하는 것보다 저장 프로시저를 사용하는 것이 더 빠릅니다. . 많은.

* 서버의 부담을 줄인다: 사용자의 작업이 데이터베이스 개체에 대한 것인 경우 단일 호출을 사용하는 경우 저장 프로시저를 사용하는 경우 많은 수의 SQL 문을 네트워크를 통해 전송해야 합니다. 🎜>

그러면 프로세스 호출 명령을 직접 보낼 수 있어 네트워크 부담이 줄어듭니다.

3: 저장 프로시저 만들기

SQL Server에서 저장 프로시저 만들기:

프로시저 프로시저 이름 만들기

@Parameter 매개변수 유형

@Parameter 매개변수 유형

. . .

as

Begin

end

저장 프로시저 실행: 프로시저 이름 실행

OUT | OUT 매개변수 유형

........

🎜> 시작

명령줄 또는 명령 블록

예외

명령줄 또는 명령 블록

             end

4: 매개변수 없음 저장 프로시저

5: 데이터 쿼리 기능에 대한 매개변수 없는 저장 프로시저

저장 프로세스에는 이름에 "张" 문자가 포함된 직원의 정보와 해당 직원이 위치한 창고 정보를 표시하는 여러 개의 선택 문이 포함될 수 있습니다.

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

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