我们来复习一下MSS 存储过程

 

 
---创建数据库
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
 
----------------------------------------插入条数据进Users表,进行下面对分页做准备----------------
Begin
    Declare @n bigint
    Declare @Sql nvarchar(225)
    set @n=0
    While @n<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%';