存储过程简单的用法



存储过程的概念

    存储过程Procedure是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。

    存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。

    由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中，所以存储过程运行要比单个的SQL语句块要快。同时由于在调用时只需用提供存储过程名和必要的参数信息，所以在一定程度上也可以减少网络流量、简单网络负担。

 

    1、 存储过程的优点

        A、 存储过程允许标准组件式编程

        存储过程创建后可以在程序中被多次调用执行，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，但对应用程序源代码却毫无影响，从而极大的提高了程序的可移植性。

        B、 存储过程能够实现较快的执行速度

        如果某一操作包含大量的T-SQL语句代码，分别被多次执行，那么存储过程要比批处理的执行速度快得多。因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析、优化，并给出最终被存在系统表中的存储计划。而批处理的T-SQL语句每次运行都需要预编译和优化，所以速度就要慢一些。

        C、 存储过程减轻网络流量

        对于同一个针对数据库对象的操作，如果这一操作所涉及到的T-SQL语句被组织成一存储过程，那么当在客户机上调用该存储过程时，网络中传递的只是该调用语句，否则将会是多条SQL语句。从而减轻了网络流量，降低了网络负载。

        D、 存储过程可被作为一种安全机制来充分利用

        系统管理员可以对执行的某一个存储过程进行权限限制，从而能够实现对某些数据访问的限制，避免非授权用户对数据的访问，保证数据的安全。

 

Ø 系统存储过程

    系统存储过程是系统创建的存储过程，目的在于能够方便的从系统表中查询信息或完成与更新数据库表相关的管理任务或其他的系统管理任务。系统存储过程主要存储在master数据库中，以“sp”下划线开头的存储过程。尽管这些系统存储过程在master数据库中，但我们在其他数据库还是可以调用系统存储过程。有一些系统存储过程会在创建新的数据库的时候被自动创建在当前数据库中。

数据库（table）：T_STUDENT_GXF004

代码分页实现：

---存储过程、row_number完成分页
if(OBJECT_ID('proc_page1', 'p') is not null)
 drop proc pro_page1
go
create proc proc_page1
(
 @startIndex int,
 @endIndex int
)
as
begin
 select count(*) from T_STUDENT_GXF004
 select * from
 (select row_number() over(order by C_CODE asc,C_SCORER desc)as rowId,* from T_STUDENT_GXF004) T
 where T.rowId between @startIndex and @endIndex
end
go
--drop proc proc_page1
exec proc_page1 1,4

效果实现：

代码多分页实现：

if(object_id('proc_page2', 'p') is not null)
 drop proc proc_page2
go
create proc proc_page2
(
 @pageIndex int,
 @pageSize int
)
as
begin
 declare @startRow int;
 declare @endRow int;
 declare @sql nvarchar(max);
 set @startRow = (@pageIndex -1)*@pageSize +1;
 set @endRow = @startRow + @pageSize -1;
 select *from
 (select row_number() over(order by C_CODE asc)as rowId,* from T_STUDENT_GXF004) T
 where T.rowId between @startRow and @endRow
end

exec proc_page2  2, 4;

代码效果实现；

实现分页（排序要求分部门，每个部门安编号asc排序）

if(object_id('proc_page4','p')is not null)
 drop proc proc_page4
go
create proc proc_page4
(
 @pageIndex int,
 @pageSize int
)
as
begin
 declare @startRow int;
 declare @endRow int;
 set @startRow = (@pageIndex-1)*@pageSize + 1;
 set @endRow = @startRow + @pageSize -1;
 select *from
 (select row_number() over(partition by C_SCORER order by C_CODE asc)as rowId,* from T_STUDENT_GXF004) T
 where T.rowId between @startRow and @endRow
end

exec proc_page4 1,4

或者

if(object_id('proc_page4','p')is not null)
 drop proc proc_page4
go
create proc proc_page4
(
 @pageIndex int,
 @pageSize int
)
as
begin
 declare @startRow int;
 declare @endRow int;
 set @startRow = (@pageIndex-1)*@pageSize + 1;
 set @endRow = @startRow + @pageSize -1;
 with aa as
 (
 select *,row_number() over(order by C_SCORER asc, C_CODE asc)as rowId from T_STUDENT_GXF004
 )
 select *from aa
 where rowId between @startRow and @endRow
end
exec proc_page4 1, 4

附件：

存储过程的基本模型

alter PROCEDURE stu_info
(
 --add the parameters for the stored procedure here
 @c_id uniqueidentifier = null,
 @c_create_time datetime =null,
 @c_status int=null,
 @ORDER_sort int=null
)
AS
BEGIN
 SET NOCOUNT ON;
 --declare parameter
 declare @sql nvarchar(1000)
 declare @WHERE nvarchar(max)
 declare @ORDER_BY nvarchar(max)
 declare @ORDER_TYPE nvarchar(max)
 declare @TABLENAME nvarchar(100)
 declare @TABLE_COLUMN nvarchar(100) = null;
 declare @PAGEINDEX INT=1;
 DECLARE @PAGESIZE INT=3;
 --insert ststements for procedure here
 set @TABLENAME = 'dbo.t_stu_score'
 set @WHERE = 'where  1=1'
 set @TABLE_COLUMN = 'c_id'
 -- sort condition
 IF @ORDER_sort = 0
  BEGIN
  SET @ORDER_TYPE = 'DESC'
  END
 ELSE
  BEGIN
  SET @ORDER_TYPE = 'ASC' 
  END
 set @ORDER_BY=N'order by '+@TABLE_COLUMN+''+@ORDER_TYPE
 set @sql='SELECT * FROM (select row_number()over('+@ORDER_BY+ ')as rw,* from'+@TABLENAME+' '

 +@WHERE+')AS T WHERE T.RW BETWEEN '+CAST((@PAGEINDEX-1)*@PAGESIZE+1 AS NVARCHAR) +' AND '+CAST

(@PAGEiNDEX*@PAGESIZE AS NVARCHAR)+';SELECT COUNT(*) FROM '+@TABLENAME+' ' +@WHERE
print @sql

exec(@sql)
END

数据类型转换
Convert(varchar,@startRow)
 CAST((@PAGEINDEX-1)*@PAGESIZE+1 AS NVARCHAR)

附件二：

--题目：

use
 ty_pmi_ljfl  
go 

select * from  dbo.t_pmi_role
select * from dbo.t_pmi_user
select * from  dbo.t_pmi_user_in_role

--1 用分页存储过程查询用户信息，  查询参数  根据姓名(c_name)， 角色名称(c_name)     登录名称查（c_login_name）
--姓名，角色 用模糊查询 。根据用户时间倒叙 （c_db_created_date），

--
--查询显示的字段     姓名id， 姓名 ，  角色名称（inner join）   角色id   ， 角色名称（用子查询显示）。

USE [ty_pmi_ljfl]
GO
/****** Object:  StoredProcedure [dbo].[pro_user_fetch2]    Script Date: 06/20/2014 09:06:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[pro_user_fetch2]
(
	@p_page_index int=1,
	@p_page_size int=10,
	@c_user_name nvarchar(50)=null,
	@c_role_name nvarchar(50)=null,
	@c_login_name nvarchar(50)=null
)
as
begin
	declare @p_score_start int=0;
	declare @p_score_end int=0;
	declare @field varchar(2000);
	declare @where varchar(max);
	declare @sql varchar(max);
	
	set @p_score_start = (@p_page_index -1)*@p_page_size +1;
	set @p_score_end = @p_score_start + @p_page_size - 1;
	
	set @field='(select c_name from dbo.t_pmi_role where c_fid = T.c_fid) as N''角色名称(子查询显示)'''
	--(select * from dbo.t_pmi_role T.c_fid = T1.c_fid) as N''角色名称1''
	--(select c_name from dbo.t_pmi_role where c_fid = T.c_fid) as N''角色名称1''
	set @where = 'WHERE T.c_db_status= 0'
	
	if @c_user_name is not null and @c_user_name<>''
	set @where = @where + '
	and T.c_name like @c_user_name
	';
	if @c_role_name is not null and @c_role_name<>''
	set @where = @where + '
	and T1.c_name like @c_role_name
	';
	
	if @c_login_name is not null and @c_login_name<>''
	set @where = @where + '
	and T.c_login_name like @c_login_name
	';
	
	
--with T as
--(
--	select *,row_number() over(order by c_db_created_date desc)as rowId from dbo.t_pmi_user
--)

set @sql='
select *from (
select row_number() over(order by T.c_db_created_date desc)as rowId,T.c_id as N''姓名id'',
	T.c_name as N''名字'',
	T1.c_name as N''角色名称(inner join)'',
	T1.c_id as N''角色id'','
	+@field+ ' from dbo.t_pmi_user as T
	inner join dbo.t_pmi_role T1 on T.c_fid = T1.c_fid '
	+ @where + '
	)p where p.rowId between '+Convert(varchar,@p_score_start) +' and '+Convert(varchar, @p_score_end);
--	print @sql
	exec(@sql);
 --EXECUTE sp_executesql   
 --@sql,   
 --N'                       
 --@p_page_index int,                      
 --@p_page_size  int,        
 -- ',   
 --@p_page_index,   
 --@p_page_size 
end