---创建数据库
create database CRM;
--删除数据库
drop database CRM;
--创建一个表
if exists(select * from sysobjects where name='Users')
drop table Users
go
create table Users(
Id int identity(1,1) not null primary key, --顾客编号,主键 按一进行自动增长
UserName varchar(50)not null,
PassWord varchar(50)not null,
Address varchar(250)not null,
)
go
----------------------------------------插入100条数据进Users表,进行下面对分页做准备----------------
Begin
Declare @n bigint
Declare @Sql nvarchar(225)
set @n=0
While @n<100--导入100条相同的数据进Users表
Begin
Set @Sql='Insert into Users Values(''jilongliang'',''123456'',''广东阳春'')'
Exec (@Sql)
set @n=@n+1
End
End
------------查询一下是不是插入---------------
Select *from Users;
-------------------------存储过程创建语法-----------------------------------
/*
1.存储过程创建语法
create proc | procedure pro_name
[{@参数数据类型} [=默认值] [output],
{@参数数据类型} [=默认值] [output],
....
]
as
SQL_statements
*/
---------------------------------------------------------------------------------
-------------------------- 带参存储过程-----------------------------------
if (object_id('proc_find_users', 'P') is not null)
drop proc proc_find_users
go
create proc proc_find_users(@startId int, @endId int)
as
---between and 表示在那个两个数字之间
select * from users where Id between @startId and @endId
go
exec proc_find_users 42, 64;
---------------------------------------------------------------------------------
--------------------------------带通配符参数存储过程-----------------------------
if (object_id('proc_findUsersByName', 'P') is not null)
drop proc proc_findUsersByName
go
create proc proc_findUsersByName(@UserName varchar(20) = '%j%', @nextName varchar(20) = '%')
as
select * from Users where UserName like @UserName and UserName like @nextName;
go
exec proc_findUsersByName;
exec proc_findUsersByName '%l%', 'j%';
---------------------------------------------------------------------------------------
----------------------------------------带输出参数存储过程---------------------------------------
if (object_id('proc_getUsersRecord', 'P') is not null)
drop proc proc_getUsersRecord
go
create proc proc_getUsersRecord(
@Id int, --默认输入参数
@UserName varchar(20) out, --输出参数
@address varchar(20) output --输入输出参数
)
as
select @UserName = UserName, @address = address from Users where Id = @Id and Address = @address;
go
---------------声明变量
declare @id int,
@address varchar(20),
@UserName varchar(20),
@temp varchar(20);
set @id = 71;
set @temp = 1;
exec proc_getUsersRecord @id, @UserName out, @temp output;
select @UserName as 用户名, @temp as temp,@address as 地址;
print @UserName + '#' + @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 users;
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 users;
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, UserName, Address from Users;
open @cur;
go
--调用
declare @exec_cur cursor;
declare @Id int,
@UserName varchar(50),
@Address varchar(250);
exec proc_cursor @cur = @exec_cur output;--调用存储过程
fetch next from @exec_cur into @Id, @UserName, @Address;
while (@@fetch_status = 0)
begin
fetch next from @exec_cur into @Id, @UserName, @Address;
print 'Id: ' + convert(varchar, @Id) + ', name: ' + @UserName + ', Address: ' + @Address;
end
close @exec_cur;
deallocate @exec_cur;--删除游标
----------------------------------------------------------
select * from sys.messages;
--使用sysmessages中定义的消息
--------------------------------Query---------------------
Create PROCEDURE proc_LoginUser
@userName varchar(50),
@password varchar(50)
as
begin
select UserName,PassWord from Users
where UserName = @userName
and PassWord = @PassWord
end
GO
--这个Go注意不要放在Exec后面,否则报超出了存储过程、函数、
--触发器或视图的最大嵌套层数(最大层数为 32)
exec proc_LoginUser @userName = 'admin',@PassWord = 'admin'
--------------------------------Insert---------------------
Create proc proc_InsertUsers
@UserName varchar(50),
@PassWord varchar(50),
@Address varchar(150)
as
insert into proc_InsertUsers values(@UserName,@PassWord,@Address)
--------------------------------Update---------------------
--修改模块信息,根据模块Id
Create proc proc_UpdateUser
@UserName varchar(50),
@PassWord varchar(50),
@Address varchar(150),
@Id int
as
update Users set UserName=@UserName,PassWord=@PassWord ,Address=@Address
where Id = @Id
--------------------------------Delete---------------------
Create proc proc_DeleteById
@Id int
as
delete from Users where Id=@Id
-----------------------------------------------------------
-------------------------------------------------------------------------------------
----------------------------------分页存储过程--------------------------------------
-------------------------------------------------------------------------------------
If (object_id('pro_page', 'P') is not null)
drop proc proc_Page
GO
create procedure proc_Page(
@pageIndex int,
@pageSize int
)
as
declare @startRow int, @endRow int --声明变量
set @startRow = (@pageIndex - 1) * @pageSize +1 --设值
set @endRow = @startRow + @pageSize -1 --设值
select ID,UserName,Address,PassWord,t.number from --t.number的行号
(
select ID,UserName,Address,PassWord, row_number()
over (order by id asc) as number from Users
) t
where t.number between @startRow and @endRow;
GO
----执行
exec proc_Page 1, 3; -- 一页,三条数据
-------------------------------------------------------------------------------------
---存储过程、row_number完成分页
if (object_id('pro_page', 'P') is not null)
drop proc proc_cursor
-- drop proc pro_page
go
create proc pro_Page
@startIndex int,
@endIndex int
as
select count(*) as Total from Users; --计算出来总数
select * from (
select row_number() over(order by Id) as rowId, * from Users
) temp
where temp.rowId between @startIndex and @endIndex
go
exec pro_Page 1, 4
-------------------------------------------------------------------------------------
-------------------------------------数据库的函数--------------------------------------
exec sp_databases; --查看数据库
exec sp_tables; --查看表
exec sp_columns users;--查看列
exec sp_helpIndex users;--查看索引
exec sp_helpConstraint users;--约束
exec sp_stored_procedures;
exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句
exec sp_rename users, stuInfo;--修改表、索引、列的名称
exec sp_renamedb myTempDB, myDB;--更改数据库名称
exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库
exec sp_helpdb;--数据库帮助，查询数据库信息
exec sp_helpdb master;
-------------------------------------------------------------------------------------
---------------------------数据库的sp_rename重命名函数-------------------------------
--表重命名
exec sp_rename 'users', 'tb_users';
select * from tb_users;
--列重命名
exec sp_rename 'tb_users.name', 'sName', 'column';
exec sp_help 'tb_users';
--重命名索引
exec sp_rename N'tb_users.idx_cid', N'idx_cidd', N'index';
exec sp_help 'tb_users';
--查询所有存储过程
select * from sys.objects where type = 'P';
select * from sys.objects where type_desc like '%pro%' and name like 'sp%';
### SQL Server 存储过程详解及应用实例
#### 一、概述
存储过程是SQL Server中的一种可编程的对象，它可以是一系列预编译的SQL语句的集合，这些语句可以执行复杂的业务逻辑或者数据库操作。存储过程可以接受输入参数，并且能够返回输出结果。它们在提高代码复用性、执行效率以及简化客户端应用程序等方面具有显著的优势。
#### 二、存储过程创建与管理
##### 2.1 创建存储过程
存储过程的基本创建语法如下：
```sql
CREATE PROCEDURE pro_name
[@param1 data_type = default_value OUTPUT, ...]
AS
BEGIN
SQL_statements
END
```
例如，创建一个名为`proc_find_users`的存储过程，该存储过程接收两个整型参数，并返回指定ID范围内的用户记录。
```sql
CREATE PROCEDURE proc_find_users
@startId INT,
@endId INT
AS
BEGIN
SELECT * FROM Users WHERE Id BETWEEN @startId AND @endId;
END
```
##### 2.2 删除存储过程
如果需要删除已存在的存储过程，可以使用`DROP PROCEDURE`命令。
```sql
IF OBJECT_ID('proc_find_users', 'P') IS NOT NULL
DROP PROCEDURE proc_find_users;
```
#### 三、存储过程参数类型
存储过程支持多种类型的参数，包括输入参数、输出参数以及输入输出参数等。
##### 3.1 输入参数
最常见的是输入参数，用于将值传递给存储过程。
```sql
CREATE PROCEDURE proc_find_users
@startId INT,
@endId INT
AS
BEGIN
SELECT * FROM Users WHERE Id BETWEEN @startId AND @endId;
END
```
##### 3.2 输出参数
输出参数用于从存储过程中返回值。
```sql
CREATE PROCEDURE proc_getUsersRecord
@Id INT,
@UserName VARCHAR(20) OUTPUT,
@Address VARCHAR(20) OUTPUT
AS
BEGIN
SELECT @UserName = UserName, @Address = Address FROM Users WHERE Id = @Id;
END
```
调用时需使用`OUTPUT`关键字：
```sql
DECLARE @UserName VARCHAR(20), @Address VARCHAR(20);
EXEC proc_getUsersRecord 71, @UserName OUTPUT, @Address OUTPUT;
SELECT @UserName AS 用户名, @Address AS 地址;
```
##### 3.3 输入输出参数
输入输出参数既可以接收输入值也可以返回输出值。
```sql
CREATE PROCEDURE proc_getUsersRecord
@Id INT,
@UserName VARCHAR(20) OUTPUT,
@Address VARCHAR(20) OUTPUT
AS
BEGIN
SELECT @UserName = UserName, @Address = Address FROM Users WHERE Id = @Id;
END
```
#### 四、存储过程中的通配符使用
通配符可以在存储过程中用于模糊查询。
```sql
CREATE PROCEDURE proc_findUsersByName
@UserName VARCHAR(20) = '%j%',
@nextName VARCHAR(20) = '%'
AS
BEGIN
SELECT * FROM Users WHERE UserName LIKE @UserName AND UserName LIKE @nextName;
END
```
#### 五、特殊存储过程
##### 5.1 不缓存存储过程
使用`WITH RECOMPILE`选项可以让存储过程每次执行时都重新编译，这有助于避免因统计信息变化导致的性能问题。
```sql
CREATE PROCEDURE proc_temp WITH RECOMPILE
AS
BEGIN
SELECT * FROM Users;
END
```
##### 5.2 加密存储过程
对于包含敏感信息的存储过程，可以使用`WITH ENCRYPTION`来加密存储过程的源代码，以防止被查看。
```sql
CREATE PROCEDURE proc_temp_encryption WITH ENCRYPTION
AS
BEGIN
SELECT * FROM Users;
END
```
#### 六、游标参数存储过程
游标允许存储过程逐行处理数据集。
```sql
CREATE PROCEDURE proc_cursor
@cur CURSOR VARYING OUTPUT
AS
BEGIN
SET @cur = CURSOR FORWARD_ONLY STATIC FOR
SELECT Id, UserName, Address FROM Users;
OPEN @cur;
END
```
调用时：
```sql
DECLARE @exec_cur CURSOR;
DECLARE @Id INT, @UserName VARCHAR(50), @Address VARCHAR(250);
EXEC proc_cursor @cur = @exec_cur OUTPUT;
FETCH NEXT FROM @exec_cur INTO @Id, @UserName, @Address;
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM @exec_cur INTO @Id, @UserName, @Address;
PRINT 'Id: ' + CONVERT(VARCHAR, @Id) + ', name: ' + @UserName + ', Address: ' + @Address;
END
CLOSE @exec_cur;
DEALLOCATE @exec_cur;
```
#### 七、分页存储过程
分页存储过程通常用于处理大量数据时的分批加载。
```sql
CREATE PROCEDURE proc_Page
@pageIndex INT,
@pageSize INT
AS
BEGIN
DECLARE @startRow INT, @endRow INT;
SET @startRow = (@pageIndex - 1) * @pageSize + 1;
SET @endRow = @startRow + @pageSize - 1;
SELECT ID, UserName, Address, PassWord, T.Number FROM
(
SELECT ID, UserName, Address, PassWord, ROW_NUMBER() OVER (ORDER BY ID ASC) AS Number FROM Users
) T
WHERE T.Number BETWEEN @startRow AND @endRow;
END
```
#### 八、系统存储过程
系统存储过程用于管理SQL Server数据库系统的各个方面，如查询数据库、表的信息等。
```sql
EXEC sp_databases; -- 查看数据库
EXEC sp_tables; -- 查看表
EXEC sp_columns users; -- 查看列
EXEC sp_helpIndex users; -- 查看索引
EXEC sp_helpConstraint users; -- 约束
EXEC sp_stored_procedures; -- 查看存储过程
EXEC sp_helptext 'sp_stored_procedures'; -- 查看存储过程创建、定义语句
EXEC sp_rename users, stuInfo; -- 修改表、索引、列的名称
EXEC sp_renamedb myTempDB, myDB; -- 更改数据库名称
EXEC sp_defaultdb 'master', 'myDB'; -- 更改登录名的默认数据库
EXEC sp_helpdb; -- 数据库帮助，查询数据库信息
EXEC sp_helpdb master;
```
#### 九、存储过程的其他操作
##### 9.1 重命名存储过程
可以使用`sp_rename`来重命名存储过程。
```sql
EXEC sp_rename 'proc_find_users', 'new_proc_find_users';
```
#### 十、总结
本文详细介绍了SQL Server中存储过程的创建、管理以及各种参数的使用方法，通过具体的示例让读者更好地理解和掌握存储过程的编写技巧。存储过程不仅可以提高数据处理的效率，还能增强应用程序的安全性和稳定性。希望本文能对您的实际工作有所帮助。
perfects1232013-11-19很好 对我挺有用的
