SQL SERVER 2005·ÖÒ³´æ´¢¹ý³Ì
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[PE011_Page]
@TableName varchar(50), --±íÃû
@Fields varchar(5000) = '*', --×Ö¶ÎÃû(È«²¿×Ö¶ÎΪ*)
@OrderField varchar(5000), --ÅÅÐò×Ö¶Î(±ØÐë!Ö§³Ö¶à×Ö¶Î)
@sqlWhere varchar(5000) = Null,--Ìõ¼þÓï¾ä(²»ÓüÓwhere)
@pageSize int, --ÿҳ¶àÉÙÌõ¼Ç¼
@pageIndex int = 1 , --Ö¸¶¨µ±Ç°ÎªµÚ¼¸Ò³
@totalRecord int = 0 output,
@TotalPage int output --·µ»Ø×ÜÒ³Êý
AS
BEGIN
Begin Tran --¿ªÊ¼ÊÂÎñ
Declare @sql nvarchar(4000);
if @totalRecord<=0 begin
--¼ÆËã×ܼǼÊý
if (@SqlWhere='' or @sqlWhere=NULL)
set @sql = 'select @totalRecord = count(*) from ' + @TableName
else
set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' with(nolock) where ' + @sqlWhere
EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--¼ÆËã×ܼǼÊý
end
--¼ÆËã×ÜÒ³Êý
select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)
if (@SqlWhere='' or @sqlWhere=NULL)
set @sql = 'Select * from (select ROW_NUMBER
Ïà¹ØÎĵµ£º
·ÖÀàͳ¼Æ×ÜÊý²¢ÅÅÐò¶à±íÁªºÏ²éѯµÄÁ½ÖÖ·½·¨
Àý:
²éѯµØÇø±íÖи÷Ê¡ÏÂÃæ³ÇÊÐ×ÜÊý,²¢¶Ô²éѯ½á¹ûÈ¡Ç°Ê®Ãû
·¨Ò»:
select b.id,a.[name],b.counts from n_area a,(select top 10 parent_id as id,sum(parent_id) as counts from n_area where parent_id<>0 group by parent_id order by sum(parent_id) desc
) b where ......
×î½üÓöµ½Ò»¸öÏîÄ¿£¬¿Í»§ÔÚ×ܲ¿Â¼ÈëÐÅÏ¢ºó£¬Êý¾ÝÐèҪͬ²½µ½ÏÂÊôµÄÈô¸É¸ö×ÓϵͳÖС£ÏÖÔÚ×ܲ¿µ¥¶ÀʹÓÃÒ»¸öÊý¾Ý¿â£¬ÏÂÊôµ¥Î»¸÷×ÔʹÓÃ×Ô¼ºµÄÊý¾Ý¿â¡£
¶ÔÓÚÕâ¸öÎÊÌ⣬Ïëµ½Á˼¸¸ö½â¾ö·½°¸£º
1¡¢×Ô¼ºÐ´´úÂë½â¾ö¡£¶¨ÒåºÃ·ÃÎÊÿ¸öÊý¾ ......
SQL ServerÖÐÁª»ú´ÔÊé
SQL Server timestamp Êý¾ÝÀàÐÍÓëʱ¼äºÍÈÕÆÚÎ޹ء£SQL Server timestamp ÊǶþ½øÖÆÊý×Ö£¬Ëü±íÃ÷Êý¾Ý¿âÖÐÊý¾ÝÐ޸ķ¢ÉúµÄÏà¶Ô˳Ðò¡£ÊµÏÖ timestamp Êý¾ÝÀàÐÍ×î³õÊÇΪÁËÖ§³Ö SQL Server »Ö¸´Ëã·¨¡£Ã¿´ÎÐÞ¸Äҳʱ£¬¶¼»áʹÓõ±Ç°µÄ @@DBTS Öµ¶ÔÆä×öÒ»´Î±ê¼Ç£¬È»ºó @@D ......
ÕâÊÇCSDNÂÛ̳ÀïÃæµÄÒ»¸öÌû×Ó£¬×÷ÕßÊÇ°®Ð¾õÂÞ.ع»ª¡£¸Ð¾õ±È½ÏÈ«Ã棬¾ÍÕûÀíÏÂÀ´ÁË£¬Áô×÷ÒÔºó²Î¿¼±¸Óá£ÔÚ´Ë£¬Ôڴ˸Ðл±¾ÎĵÄ×÷Õß¡£
/*
±êÌ⣺һ¸öÏîÄ¿Éæ¼°µ½µÄ50¸öSqlÓï¾ä(ÕûÀí°æ)
×÷Õߣº°®Ð¾õÂÞ.ع»ª(Ê®°ËÄê·çÓ꣬ÊصñùɽѩÁ«»¨¿ª)
ʱ¼ä£º2010-05-10
µØµã£ºÖØÇ캽ÌìְҵѧԺ
˵Ã÷£ºÒÔÏÂÎåÊ®¸öÓï¾ä¶¼°´ÕÕ²âÊÔÊý¾Ý½øÐ ......
1¡¢±à³ÌÓïÑÔÓëOracleÊý¾Ý¿â
1.1¡¢´æ´¢µÄÓëÄäÃûµÄPL/SQL³ÌÐò¿é
Óë´æ´¢µÄPL/SQL³ÌÐò¿éÏà±È£¬ÄäÃûµÄPL/SQL³ÌÐò¿éЧÂʽϵͣ¬´ËÍâÓÉÓÚ¿ÉÄÜÔÚ¶ą̀»úÆ÷Öй«²¼Ô´´úÂ룬»¹»áÒý·¢¹ÜÀíÎÊÌâ¡£
1.2¡¢PL/SQL¶ÔÏó
PL/SQL¶ÔÏó¾ßÓÐÏÂÁÐ5ÖÖÀàÐÍ£º
¹ý³Ì
º¯Êý
³ÌÐò°ü
³ÌÐò°üÖ÷Ìå
´¥·¢Æ÷
2¡¢¹ý³Ì¡¢º¯ÊýÒÔ¼°³ÌÐò°ü
2.1¡¢¹ý³ÌÓëº¯Ê ......