SqlServer存储器创建

前提

    创建表，并插入数据

IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[User]') AND type IN ('U'))
	DROP TABLE [dbo].[User]
GO

CREATE TABLE [dbo].[User] (
  [id] int  NOT NULL,
  [name] nchar(10) COLLATE Chinese_PRC_CI_AS  NULL,
  [did] int  NULL
)
GO

ALTER TABLE [dbo].[User] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of User
-- ----------------------------
INSERT INTO [dbo].[User] ([id], [name], [did]) VALUES (N'1', N'one       ', NULL)
GO

INSERT INTO [dbo].[User] ([id], [name], [did]) VALUES (N'2', N'tow       ', NULL)
GO

INSERT INTO [dbo].[User] ([id], [name], [did]) VALUES (N'3', N'three     ', NULL)
GO

INSERT INTO [dbo].[User] ([id], [name], [did]) VALUES (N'4', N'faure     ', N'1')
GO

INSERT INTO [dbo].[User] ([id], [name], [did]) VALUES (N'5', N'five      ', N'1')
GO


-- ----------------------------
-- Primary Key structure for table User
-- ----------------------------
ALTER TABLE [dbo].[User] ADD CONSTRAINT [PK_user] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO

1. 常用的系统存储过程有

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;

2.用户自定义存储过程

create proc | procedure pro_name
    [{@参数数据类型} [=默认值] [output],
     {@参数数据类型} [=默认值] [output],
     ....
    ]
as
    SQL_statements

      2.1创建不带参数的存储过程

if(exists(select * from sys.objects where name='proc_get_user'))
    drop proc proc_get_user
go
create proc proc_get_user
as 
     --CreateUser:一棵小白杨
   select * from TestC.dbo.[User];

             2.1.1执行该存储过程

   exec proc_get_User

      2.2创建带参数的存储过程

--带参存储过程
if (object_id('proc_find_user', 'P') is not null)
    drop proc proc_find_user
go
create proc proc_find_user(@startId int, @endId int)
as
    select * from TestC.dbo.[User] where id between @startId and @endId
go

             2.2.1执行该存储过程

exec proc_find_user 2, 4;

      2.3带通配符参数存储过程

--带通配符参数存储过程
if (object_id('proc_findUserByName', 'P') is not null)
    drop proc proc_findUserByName
go
create proc proc_findUserByName(@name varchar(20) = '%t%', @nextName varchar(20) = '%')
as
    select * from TestC.dbo.[User] where name like @name and name like @nextName;
go

             2.3.1执行该存储过程

exec proc_findUserByName;

exec proc_findUserByName '%o%', 't%';

      2.4带通配符参数存储过程

if (object_id('proc_getUserRecord', 'P') is not null)
    drop proc proc_getUserRecord
go
create proc proc_getUserRecord(
    @id int, --默认输入参数
    @name varchar(20) out, --输出参数
    @d_id int output--输入输出参数
)
as
    select @name = name,@d_id =did from TestC.dbo.[User] where id = @id and did = @d_id;
go

             2.4.1执行该存储过程

-- 
declare @id int,
        @name varchar(20),
        @temp varchar(20);
set @id = 4; 
set @temp = 1;
exec proc_getUserRecord @id, @name out, @temp output;
select @name, @temp;
print @name + '#' + @temp;

      2.5不缓存存储过程

--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 TestC.dbo.[User] ;
go

             2.5.1执行该存储过程

exec proc_temp;

      2.6不缓存存储过程

--加密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 TestC.dbo.[User];
go

             2.6.1查看该存储过程

      2.7带游标的存储过程

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 from TestC.dbo.[User];
    open @cur;
go

             2.7.1执行该存储过程

declare @exec_cur cursor;
declare @id int,
        @name varchar(20);
exec proc_cursor @cur = @exec_cur output;--调用存储过程
fetch next from @exec_cur into @id, @name;
while (@@fetch_status = 0)
begin
    fetch next from @exec_cur into @id, @name;
    print 'id: ' + convert(varchar, @id) + ', name: ' + @name ;
end
close @exec_cur;
deallocate @exec_cur;--删除游标

参考链接

SQL-Server存储过程基础