如何书写优雅、漂亮的SQL脚本？

您的位置：
门户
>> 文章精选
>> 软件开发专栏
>> 数据库
>> 查看资讯

如何书写优雅、漂亮的SQL脚本？

发表于：2012-5-28 10:02

作者：潇湘隐者来源：51Testing软件测试网采编

　　首先就应该统一关键字大小写，不要一部分大写、一部分小写。然后从结构上面调整。可能每个人的审美观、习惯的格式不同，这个无所谓，也没有必要统一。但是你书写出来的脚本至少要结构清晰，一目了然。不要让别人费很大的劲去调整格式，然后才能理解它的逻辑，如果写出上面或是比上面更糟糕的脚本，我想项目经理真应该教训教训你。这样只会给后来维护的人痛苦不堪（实际开发中可能比这糟糕十倍呢，想必很多人是深受其害啊）

SELECT Permission FROM  Permission
WHERE
     resourceid =( SELECT TOP 1 resourceid FROM [Resource] WHERE resourcename=@resourcename)
    AND actionid =(SELECT TOP 1 actionid FROM [Action] WHERE actionname=@actionname)
    AND SchemaId IN (SELECT SchemaId FROM dbo.DcUserBindToSchema WHERE DcUserID=@UserID)

　　动态组合语句是否让你的脚本看起来林乱不堪啊，你有没有试过让其在某些方面看起来美观点、优雅点啊、

Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->'SELECT   Order_ID = @OrderID, CampaignCode= @CampaignCode, ProductCode = @ProductCode, StartDate= SpotDate,
   EndDate = EndDate, StartTime= Media_StartTime, EndTime = Media_EndTime, Duration = (CASE WHEN Media_Duration IS NULL OR   ELSE Media_Duration END), Adformat= Media_Adformat , Color = Media_Color , --Media_Showing,                                          Size   = Media_Size, SpotType = Media_SpotType, URL = Media_URL , ScheduleNo   = ScheduleNo ,
   Plan_Insertion_ID = Plan_Insertion_ID

那下面书写格式是不是美观、整洁些呢

SELECT
Order_ID          = @OrderID
, CampaignCode      = @CampaignCode
, ProductCode       = @ProductCode
, StartDate         = SpotDate
, EndDate           = EndDate
, StartTime         = Media_StartTime
, EndTime           = Media_EndTime
, Adformat          = Media_Adformat
, Color             = Media_Color
, Impression        = Media_Impression
, Location          = Media_Location
, Material          = Media_Material
, Position          = Media_Position
, Program           = Media_Program
, Scale             = Media_Scale         --Media_Showing
, Size              = Media_Size
, SpotType          = Media_SpotType
, URL               = Media_URL
, ScheduleNo        = ScheduleNo
, Plan_Insertion_ID = Plan_Insertion_ID
, Position = Media_Position
'