SQL 2005 ´æ´¢¹ý³Ì·ÖÒ³
create PROCEDURE [dbo].[P_PageTest]
@SQL Nvarchar(max), --SQLÓï¾ä²»°üÀ¨ÅÅÐò
@CurPage int, --µ±Ç°Ò³
@PageRows int, --Ò³Ãæ³ß´ç
@Order Nvarchar(20), --ÅÅÐò×Ö¶Î
@OrderType Nvarchar(10), --ÅÅÐòÀàÐ͵¹Ðòdesc»òÕýÐòasc
@TotalRecorder int output
AS
BEGIN
SET NOCOUNT ON;
declare @ExceSQL nvarchar(4000)
--ÉèÖÿªÊ¼ÐкÅ
declare @start_row_num AS int
declare @end_row_num AS int
if(@CurPage=1)
begin
SET @start_row_num = (@CurPage - 1) * @PageRows
SET @end_row_num = @start_row_num+@PageRows
end
else
begin
SET @start_row_num = ((@CurPage - 1) * @PageRows)+1
SET @end_row_num = (@start_row_num+@PageRows)-1
end
--ÉèÖñíʾ
declare @RowNumber nvarchar(100)
set @RowNumber = ', ROW_NUMBER() OVER(ORDER BY ' + @Order +' '+ @OrderType + ') as RowNumber from '
set @SQL = Replace(@SQL,' from ',@RowNumber)
--»ñµÃ×ܼǼÊý
set @ExceSQL = 'WITH tmp AS (' + @SQL + ')
select @TotalRecorder=max(RowNumber) from tmp'
execute sp_executesql @ExceSQL,N'@TotalRecorder int output',@TotalRecorder output
--²éѯÓï¾ä
set @ExceSQL = 'WITH tmp AS (' + @SQL + ')
select * from tmp where RowNumber between ' + Convert(nvarchar,@start_row_num)
+ ' And ' + Convert(nvarchar,@end_row_num)
execute(@ExceSQL)
END
Ïà¹ØÎĵµ£º
²»´íµÄ×ÊÁÏ,ת¹ýÀ´,·½±ãÈÕºó²é¿´Ê¹ÓÃ!!!
--¼à¿ØË÷ÒýÊÇ·ñʹÓÃ
alter index &index_name monitoring usage;
alter index &index_name nomonitoring usage;
select * from v$object_usage where index_name =
&index_name;
--ÇóÊý¾ÝÎļþµÄI/O·Ö²¼
select
df.name,phyrds,phywrts,phyblkrd,phyblkwrt,sin ......
SQLµÄÓÅ»¯Ó¦¸Ã´Ó5¸ö·½Ãæ½øÐе÷Õû£º
1.È¥µô²»±ØÒªµÄ´óÐͱíµÄÈ«±íɨÃè
2.»º´æСÐͱíµÄÈ«±íɨÃè
3.¼ìÑéÓÅ»¯Ë÷ÒýµÄʹÓÃ
4.¼ìÑéÓÅ»¯µÄÁ¬½Ó¼¼Êõ
5.¾¡¿ÉÄܼõÉÙÖ´Ðмƻ®µÄCost
SQLÓï¾ä£º
ÊǶÔÊý¾Ý¿â(Êý¾Ý)½øÐвÙ×÷µÄΩһ;¾¶£»
ÏûºÄÁË70%~90%µÄÊý¾Ý¿â×ÊÔ´£»¶ÀÁ¢ÓÚ³ÌÐòÉè¼ÆÂß¼£¬Ïà¶ÔÓÚ¶Ô³ÌÐòÔ´´úÂëµÄÓÅ»¯£¬¶ÔSQLÓï¾äµÄÓÅ»¯Ô ......
SQLµÄÓÅ»¯Ó¦¸Ã´Ó5¸ö·½Ãæ½øÐе÷Õû£º
1.È¥µô²»±ØÒªµÄ´óÐͱíµÄÈ«±íɨÃè
2.»º´æСÐͱíµÄÈ«±íɨÃè
3.¼ìÑéÓÅ»¯Ë÷ÒýµÄʹÓÃ
4.¼ìÑéÓÅ»¯µÄÁ¬½Ó¼¼Êõ
5.¾¡¿ÉÄܼõÉÙÖ´Ðмƻ®µÄCost
SQLÓï¾ä£º
ÊǶÔÊý¾Ý¿â(Êý¾Ý)½øÐвÙ×÷µÄΩһ;¾¶£»
ÏûºÄÁË70%~90%µÄÊý¾Ý¿â×ÊÔ´£»¶ÀÁ¢ÓÚ³ÌÐòÉè¼ÆÂß¼£¬Ïà¶ÔÓÚ¶Ô³ÌÐòÔ´´úÂëµÄÓÅ»¯£¬¶ÔSQLÓï¾äµÄÓÅ»¯Ô ......
SQLÊÖ¹¤×¢Èë´óÈ«
2006Äê08ÔÂ11ÈÕ ÐÇÆÚÎå 21:00
±È·½ËµÔÚ²éѯidÊÇ50µÄÊý¾Ýʱ£¬Èç¹ûÓû§´«½üÀ´µÄ²ÎÊýÊÇ50 and 1=1£¬Èç¹ûûÓÐÉèÖùýÂ˵Ļ°£¬¿ÉÒÔÖ±½Ó²é³öÀ´£¬SQL ×¢ÈëÒ»°ãÔÚASP³ÌÐòÖÐÓöµ½×î¶à£¬
¿´¿´ÏÂÃæµÄ
1.ÅжÏÊÇ·ñÓÐ×¢Èë
;and 1=1
;and 1=2
2.³õ²½ÅжÏÊÇ·ñÊÇmssql
;and user>0
3.ÅжÏÊý¾Ý¿âϵͳ
;and ......