Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

sql server ´æ´¢¹ý³ÌʵÏÖ·ÖÒ³

     ÔÚ¿ª·¢²»ÂÛÊÇWEB»¹ÊÇWinform³ÌÐòÖУ¬Êý¾Ý·ÖÒ³ÊǾ­³£Óöµ½µÄÎÊÌ⣬ҪÊÇÔÚ´úÂëÖÐʵÏִ˹ý³Ì£¬ÏÔÈ»½µµÍÁË·þÎñÆ÷µÄЧÂÊ£¬¿ÉÒÔʹÓÃÒÔÏ´洢¹ý³ÌÀ´ÊµÏÖ¡£
CREATE PROCEDURE pagination
@tblName varchar(255), -- ±íÃû
@strGetFields varchar(1000) = '*', -- ÐèÒª·µ»ØµÄÁÐ
@fldName varchar(255)='', -- ÅÅÐòµÄ×Ö¶ÎÃû
@PageSize int = 10, -- Ò³³ß´ç
@PageIndex int = 1, -- Ò³Âë
@doCount bit = 0, -- ·µ»Ø¼Ç¼×ÜÊý, ·Ç 0 ÖµÔò·µ»Ø
@OrderType bit = 0, -- ÉèÖÃÅÅÐòÀàÐÍ, ·Ç 0 ÖµÔò½µÐò
@strWhere varchar(1500) = '' -- ²éѯÌõ¼þ (×¢Òâ: ²»Òª¼Ó where)
AS
declare @strSQL varchar(5000) -- Ö÷Óï¾ä
declare @strTmp varchar(110) -- ÁÙʱ±äÁ¿
declare @strOrder varchar(400) -- ÅÅÐòÀàÐÍ
if @doCount != 0
begin
if @strWhere !=''
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where '+@strWhere
else
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
end
--ÒÔÉÏ´úÂëµÄÒâ˼ÊÇÈç¹û@doCount´«µÝ¹ýÀ´µÄ²»ÊÇ0£¬¾ÍÖ´ÐÐ×ÜÊýͳ¼Æ¡£ÒÔϵÄËùÓдúÂë¶¼ÊÇ@doCountΪ0µÄÇé¿ö
else
begin
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName +'] desc'
--Èç¹û@OrderType²»ÊÇ0£¬¾ÍÖ´ÐнµÐò£¬Õâ¾äºÜÖØÒª£¡
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
end
if @PageIndex = 1
begin
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder
else
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['+ @tblName + '] '+ @strOrder
--Èç¹ûÊǵÚÒ»Ò³¾ÍÖ´ÐÐÒÔÉÏ´úÂ룬ÕâÑù»á¼Ó¿ìÖ´ÐÐËÙ¶È
end
else
begin
--ÒÔÏ´úÂ븳ÓèÁË@strSQLÒÔÕæÕýÖ´ÐеÄSQL´úÂë
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'+ @strOrder
if @st


Ïà¹ØÎĵµ£º

SQLµÝ¹é²éѯ²¿ÃÅ

create function [dbo].[DeptTree](@initDeptCode  varchar(10))/*¶¨Ò庯Êýc_tree£¬ÊäÈë²ÎÊýΪ³õʼ½ÚµãDeptCode*/
returns @t table(DeptCode varchar(10),UpDeptCode  varchar(10),lev int)/*¶¨Òå±ítÓÃÀ´´æ·ÅÈ¡³öµÄÊý¾Ý*/
begin
  declare @i int/*±êÖ¾µÝ¹é¼¶±ð*/
  set @i=1
  insert @t ......

ÓÅ»¯SQL ServerÊý¾Ý¿â·þÎñÆ÷ÄÚ´æÅäÖÃ

ǰÑÔ
Å©ÒµÒøÐÐ×ÜÐÐ1998ÄêÒÔÀ´ÕýÊ½ÍÆ¹ãÁËаæÍøÂç°æ×ÛºÏÒµÎñͳ¼ÆÐÅϢϵͳ£¬¸ÃϵͳÊÇ»ùÓÚWindowsNT4.0ƽ̨£¬²ÉÓÿͻ§£¯·þÎñÆ÷ģʽ£¬ÒÔMicrosoft SQL ServerΪ»ù´¡½¨Á¢ÆðÀ´µÄ´óÐÍÊý¾Ý¿âÓ¦ÓóÌÐò£¬ÏµÍ³½çÃæÓѺᢲÙ×÷¼ò±ã£¬¼ÆËã¡¢·ÖÎö¡¢¼ìË÷¹¦Äܷdz£Ç¿´ó£¬Îª±£Ö¤Å©ÒµÒøÐÐϵͳ¼°Ê±½øÐÐ×ÝÏòºÍºáÏòÒµÎñÊý¾Ý²É¼¯¡¢°´ÕÕ²»Í¬ÒªÇóÉú³ ......

SQLºº×Öת»»Æ´Òô

/*
¸ù¾Ýºº×Ö»ñȡȫƴ
1.Éú³ÉËùÓжÁÒôÁÙʱ±í
2.¸ù¾ÝChinese_PRC_CS_AS_KS_WS ÅÅÐò»ñÈ¡¶ÁÒô
*/
Create function ChineseSpell(@str varchar(100))
returns varchar(8000)
as
begin
declare @re varchar(8000)
--Éú³ÉÁÙʱ±í
declare @t table(chr nchar(1) collate Chinese_PRC_CS_AS_KS_WS,py nvarchar(20))
ins ......

PL/SQLʵÓÃС¼¼ÇÉ

 1. Ö±½ÓÔÚPL/SQL ÖÐÐÞ¸ÄÊý¾Ý
     selectÓï¾äºóÃæ¼Ó‘for updata’£¬´ò¿ª½çÃæÉϵÄÐ¡Ëø£¬±à¼­£¬°´¹³¹³±£´æ¡£
     eg: select * from xtgldxsyncdw for update; ²éѯ½á¹û´°¿ÚµÄÐ¡Ëø¼´¿É´ò¿ª¡£
2. µ¼È˵¼³ötables
     tools -->import ......

sqlÊý¾ÝÀàÐÍÏê½â

sqlÊý¾ÝÀàÐÍÏê½â
(1)¶þ½øÖÆÊý¾ÝÀàÐÍ
¡¡¡¡¶þ½øÖÆÊý¾Ý°üÀ¨ Binary¡¢Varbinary ºÍ Image
¡¡¡¡Binary Êý¾ÝÀàÐͼȿÉÒÔÊǹ̶¨³¤¶ÈµÄ(Binary),Ò²¿ÉÒÔÊDZ䳤¶ÈµÄ¡£
¡¡¡¡Binary[(n)] ÊÇ n λ¹Ì¶¨µÄ¶þ½øÖÆÊý¾Ý¡£ÆäÖУ¬n µÄȡֵ·¶Î§ÊÇ´Ó 1 µ½ 8000¡£Æä´æ´¢ñ¿µÄ´óСÊÇ n + 4 ¸ö×Ö½Ú¡£
¡¡¡¡Varbinary[(n)] ÊÇ n λ±ä³¤¶ÈµÄ¶þ½øÖÆÊ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ