SQL 之存储过程

sp_databases; --查看数据库 exec sp_tables; --查看表 exec sp_columns student;--查看列 exec sp_helpIndex student;--查看索引 exec sp_helpConstraint student;--约束 exec sp_stored_procedures; exec sp_helptext ‘sp_stored_procedures‘;--查看存储过程创建、定义语句 exec sp_rename student, stuInfo;--修改表、索引、列的名称 exec sp_renamedb myTempDB, myDB;--更改数据库名称 exec sp_defaultdb ‘master‘, ‘myDB‘;--更改登录名的默认数据库 exec sp_helpdb;--数据库帮助，查询数据库信息 exec sp_helpdb master;

 

•  扩充存储过程

• •  可以执行DOS命令下的一些的操作
  •  以文本行方式返回任何输出
  •  调用语法： EXEC xp_cmdshell DOS命令 [NO_OUTPUT]
  •  启动方法：

     EXEC sp_configure ‘show advanced options‘, 1
    GO
    RECONFIGURE
    GO
    EXEC sp_configure ‘xp_cmdshell‘, 1
    GO
    RECONFIGURE
    GO 

     

  • 示例：

    /*创建数据库bankDB，要求保存在D:\bank
    */
    
    USE master
    GO
    EXEC xp_cmdshell ‘mkdir d:\bank‘, NO_OUTPUT
    IF EXISTS(SELECT * FROM sysdatabases
                                WHERE name=‘bankDB‘)
       DROP DATABASE bankDB
    GO
    CREATE DATABASE bankDB
     (
      …
    )
    GO
    EXEC xp_cmdshell ‘dir D:\bank\‘ --查看文件

     

 　　　

•  定义存储过程的语法

 　　　　

CREATE PROC[DEURE]  存储过程名
@参数1 数据类型 = 默认值 [OUTPUT],
@参数2 数据类型 = 默认值 [OUTPUT],
...
@参数n 数据类型 = 默认值 [OUTPUT]

AS

SQL语句

GO

 

•  调用存储过程　　　

 　　　　

EXEC  过程名 [参数]

 

 

•  用户自定义存储过程

  

1. 　创建不带参数存储过程

   --创建存储过程
   if (exists (select * from sys.objects where name = ‘proc_get_student‘))
       drop proc proc_get_student
   go
   create proc proc_get_student
   as
       select * from student;
   
   --调用、执行存储过程
   exec proc_get_student;

    

2.  修改存储过程

   --修改存储过程
   alter proc proc_get_student
   as
   select * from student;

    

3. 带参存储过程

   --带参存储过程
   if (object_id(‘proc_find_stu‘, ‘P‘) is not null)
       drop proc proc_find_stu
   go
   create proc proc_find_stu(@startId int, @endId int)
   as
       select * from student where id between @startId and @endId
   go
   
   exec proc_find_stu 2, 4;

    

4. 带通配符参数存储过程

   --带通配符参数存储过程
   if (object_id(‘proc_findStudentByName‘, ‘P‘) is not null)
       drop proc proc_findStudentByName
   go
   create proc proc_findStudentByName(@name varchar(20) = ‘%j%‘, @nextName varchar(20) = ‘%‘)
   as
       select * from student where name like @name and name like @nextName;
   go
   
   exec proc_findStudentByName;
   exec proc_findStudentByName ‘%o%‘, ‘t%‘;

    

5. 带输出参数存储过程

   if (object_id(‘proc_getStudentRecord‘, ‘P‘) is not null)
       drop proc proc_getStudentRecord
   go
   create proc proc_getStudentRecord(
       @id int, --默认输入参数
       @name varchar(20) out, --输出参数
       @age varchar(20) output--输入输出参数
   )
   as
       select @name = name, @age = age  from student where id = @id and sex = @age;
   go
   
   -- 
   declare @id int,
           @name varchar(20),
           @temp varchar(20);
   set @id = 7; 
   set @temp = 1;
   exec proc_getStudentRecord @id, @name out, @temp output;
   select @name, @temp;
   print @name + ‘#‘ + @temp;

    

6. 不缓存存储过程

   --WITH RECOMPILE 不缓存
   if (object_id(‘proc_temp‘, ‘P‘) is not null)
       drop proc proc_temp
   go
   create proc proc_temp
   with recompile
   as
       select * from student;
   go
   
   exec proc_temp;

    

7.  加密存储过程

   --加密WITH ENCRYPTION 
   if (object_id(‘proc_temp_encryption‘, ‘P‘) is not null)
       drop proc proc_temp_encryption
   go
   create proc proc_temp_encryption
   with encryption
   as
       select * from student;
   go
   
   exec proc_temp_encryption;
   exec sp_helptext ‘proc_temp‘;
   exec sp_helptext ‘proc_temp_encryption‘;

    

8.  带游标参数存储过程

   if (object_id(‘proc_cursor‘, ‘P‘) is not null)
   
       drop proc proc_cursor
   go
   create proc proc_cursor
       @cur cursor varying output
   as
       set @cur = cursor forward_only static for
       select id, name, age from student;
       open @cur;
   go
   --调用
   declare @exec_cur cursor;
   declare @id int,
           @name varchar(20),
           @age int;
   exec proc_cursor @cur = @exec_cur output;--调用存储过程
   fetch next from @exec_cur into @id, @name, @age;
   while (@@fetch_status = 0)
   begin
       fetch next from @exec_cur into @id, @name, @age;
       print ‘id: ‘ + convert(varchar, @id) + ‘, name: ‘ + @name + ‘, age: ‘ + convert(char, @age);
   end
   close @exec_cur;
   deallocate @exec_cur;--删除游标

    

9. 分页存储过程

   ---存储过程、row_number完成分页
   if (object_id(‘pro_page‘, ‘P‘) is not null)
       drop proc proc_cursor
   go
   create proc pro_page
       @startIndex int,
       @endIndex int
   as
       select count(*) from product
   ;    
       select * from (
           select row_number() over(order by pid) as rowId, * from product 
       ) temp
       where temp.rowId between @startIndex and @endIndex
   go
   --drop proc pro_page
   exec pro_page 1, 4
   --
   --分页存储过程
   if (object_id(‘pro_page‘, ‘P‘) is not null)
       drop proc pro_stu
   go
   create procedure pro_stu(
       @pageIndex int,
       @pageSize int
   )
   as
       declare @startRow int, @endRow int
       set @startRow = (@pageIndex - 1) * @pageSize +1
       set @endRow = @startRow + @pageSize -1
       select * from (
           select *, row_number() over (order by id asc) as number from student 
       ) t
       where t.number between @startRow and @endRow;
   
   exec pro_stu 2, 2;

    

    

•  删除存储过程

  DROP PROC[EDURE] 过程名

   

SQL 之存储过程

标签：stat   查询   数据   默认值   null   安全   tor   mds   _id