SQL 之存储过程

2022-10-22 12:16:22

存储过程
- 是用来执行管理任务或应用复杂的业务规则，
- 存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。

存储过程的优点

- 存储过程已在服务器注册
- 执行速度更快
- 允许模块化程序设计
- 提高系统安全性减少网络流通量　　　　
系统存储过程

1. 　由系统定义，存放在master数据库中
2. 　系统存储过程的名称都以“sp_”开头或“xp_”开头

常见的系统存储过程

exec 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  过程名 [参数]

用户自定义存储过程

　创建不带参数存储过程

--创建存储过程

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;

修改存储过程

--修改存储过程

alter proc proc_get_student

as

select * from student;

带参存储过程

--带参存储过程

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;

带通配符参数存储过程

--带通配符参数存储过程

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%';

带输出参数存储过程

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;

不缓存存储过程

--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;

加密存储过程

--加密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';

带游标参数存储过程

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;--删除游标

分页存储过程

---存储过程、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] 过程名
```

码农公寓

相关文章