ویوسافت

ویوسافت

شما در ویوسافت میتوانید از پروژه ها ، کد سورس ها و آموزش های رایگان استفاده کنید



حمایت مالی

شما می توانید برای هر چه بهتر شدن مطالب سایت و ارائه مطالب جدید ، آموزش تکلونوژی های جدید از سایت حمایت مالی کرده تا این آموزش ها با حمایت های شما تهیه و در سایت انتشار یابد

SQL Server برنامه نویسی و توابع بانک اطلاعاتی


نویسنده : علی خازنی
دسته بندی : پایگاه داده ها
1395/11/08

 

آموزش توابع sql server

 

 

 

 

 

 

 

 

 برنامه نویسی بانک اطلاعاتی SQL Server

  – توابع داخلی : در هرزبان برنامه نویسی آشنائی با توابع داخلی مهم زبان از اهمیت ویژه ای برخوردار است ، دانستن پارامترهای ورودی و عملکرد خروجی تابع داخلی قابلیت برنامه نویسی را افزایش داده و باعث طراحی نرم افزارهای بسیار پیچیده تر خواهد شد بدین منظور تعدادی از توابع داخلی مهم در    SQL Server  تشریح می گردد.

 –  Left : این تابع تعدادی کارکتر از سمت چپ یک رشته استخراج می نماید و در شکل کلی ذیل بکار می رود.

تعدادی کارکتر از سمت  چپ رشته استخراج شده است .              ( تعداد کارکتر , رشته )  Left

  .

Right : این تابع تعدادی کارکتر از سمت راست یک رشته استخراج می نماید و در شکل کلی ذیل بکار می رود.

تعدادی کارکتر از سمت  راست رشته استخراج شده است                ( تعداد کارکتر , رشته )  Right

SubString : این تابع تعدادی کارکتر از نقطه ای از یک رشته را استخراج می کند و در شکل کلی ذیل بکار می رود.

تعدادی کارکتر از نقطه شروع ( [ تعداد], شروع استخراج , رشته )  SubString  یک رشته استخراج می گردد

در این تابع درصورتیکه پارامتر تعداد ذکر نگردد از نقطه شروع تا انتهای رشته فرآیند استخراج زیر رشته صورت می پذیرد. بعنوان مثال در دستور ذیل مقدار ذخیره شده در متغیر x  رشته Ali  خواهد بود.

(Set   @x = Left (´And´, ۱) + SubString (´Hello´ , ۳ , ۱ ) + Left (´in´ , ۱

–  تابع LTrim :  این تابع فضاهای خالی از سمت چپ یک رشته را حذف می کند و در شکل کلی ذیل بکار می رود:

حذف فضای خالی سمت چپ رشته  ( رشته ) LTrim

–  تابع RTrim :  این تابع فضای خالی از سمت راست یک رشته را حذف می کند و در شکل کلی ذیل بکار می رود:

حذف فضای خالی سمت راست رشته                     ( رشته ) RTrim

بعنوان مثال در دستور ذیل کلیه فضاهای خالی دو طرف رشته حذف خواهند شد :

(‘Set   @x = LTrim (RTrim (´ Hello

–  تابع Upper :  این تابع حروف یک رشته را به حروف بزرگ تبدیل خواهد کرد و به شکل کلی ذیل بکار می رود.

رشته به حروف بزرگ تبدیل خواهد شد                    ( رشته ) Upper

–  تابع Lower :  این تابع حروف یک رشته را به حروف کوچک تبدیل خواهد کرد و به شکل کلی ذیل بکار می رود.

رشته به حروف کوچک تبدیل خواهد شد                     ( رشته ) Lower

–  تابع Len :  این تابع تعداد کاراکترهای یک رشته را محاسبه می کند و در شکل کلی ذیل بکار می رود.

 طول رشته به عدد             ( رشته ) Len

مثال : برنامه ای بنویسید که یک رشته را معکوس نماید

(Declare   @x  VarChar (50) , @C  TinyInt , @y VarChar (50

‘Set   @x = ´Hello

´´=Set   @y

Set   @C = 0

While   @C < Len (@x)  Begin

Set   @C = @C + 1

Set   @y = SubString (@x , @C , 1) + @y

END

–  تابع Char :  این تابع کاراکتر موردنظر را باتوجه به کد اسکی آن باز می گرداند و به شکل ذیل بکار می رود.

 ( شماره کاراکتر ) Char

– تابع Reverse :  این تابع یک رشته را معکوس می کند و در شکل کلی ذیل بکار می رود.

رشته معکوس می گردد                ( رشته ) Reverse

– تابع Replace :  این تابع یک مجموعه رشته ای را کاراکتر به کاراکتر ترجمه می کند و در شکل کلی ذیل بکار می رود.

رشته اصلی کاراکتر به  کاراکتر ترجمه می شود

       (کاراکترهای ترجمه شونده,کاراکترهای قابل ترجمه , رشته اصلی)Replace

بعنوان مثال داریم :

(‘Set   @x = Replace (´Hello´,´ale´,´xyz

که در نتیجه حروف ale نظیر به نظیر به حروف xyz تبدیل خواهند شد ومتغیر x شامل رشته´Hzyyo´ خواهد بود.

– تابع Space :  این تابع به اندازه اعلام شده فضای خالی ایجاد خواهد کرد و در شکل کلی ذیل بکار می رود.

به اندازه تعداد فضای خالی ایجاد می گردد                  ( تعداد ) Space

– تابعUsername : این تابع به شکل(  )Username نام کاربری که هم اکنون بهSQL Server متصل شده است را باز می گرداند.

– تابع ABS :  این تابع قدر مطلق یک عدد را تعیین می کند و معادل  |x|در ریاضی است: (عدد) ABS

– تابعFloor: این تابع جزء صحیح یک عدد را تعیین می کند ومعادل[x] درریاضی است:(عدد) Floor

– تابع Power :  درپایگاه داده SQL Server عملگری برای توان وجود ندارد و می بایستی با استفاده از تابع Power به شکل ذیل استفاده گردد.

عدد به توان خواهد رسید               ( توان , عدد ) Power

– تابع Sqrt :  درصورتیکه درنظر باشد ریشه دوم یک عدد مثبت حاصل شود از تابع Sqrt به شکل ذیل استفاده می گردد.

جذر عدد                        ( عدد ) Sqrt

– تابع Isnull :  این تابع بسیار مفید اعتبارسنجی لازم جهت Null بودن یک مقدار را بعهده دارد و به شکل کلی ذیل بکار می رود.

               ( پیش فرض , عبارت ) Isnull

بعنوان مثال داریم :

(Set   @x = Isnull (@y,0

در این حالت درصورتیکه متغیر y مقدار Null را داشته باشد مقدار x صفر خواهد بود درغیراینصورت مقادیر x و y مساوی خواهند شد.

– تابع GetDate :  این تابع تاریخ و ساعت جاری Server  را باز می گرداند.

– تابع Convert :  به منظور تبدیل انواع داده ها به یکدیگر از این تابع تحت شرایط خاصی استفاده می گردد که قالب کلی آن به شکل ذیل خواهد بود.

عبارت موردنظر به داده اعلام شده تبدیل می گردد                   ( عبارت , نوع داده ) Convert

  بعنوان مثال خواهیم داشت:

(Set   @x = Convert (VarChar (10) , @y

که در این دستور متغیر @y به یک نوع داده ای کاراکتری بدون Unicode و حداکثر طول ۱۰ بایت تبدیل شده و در متغیر x جانشین می گردد.

– تابع CharIndex :  از این تابع بسیار مفید به منظور تعیین مختصات قرار گرفتن یک رشته فرعی در یک رشته اصلی استفاده می گردد که در شکل کلی ذیل قابل ارائه می باشد.

 موقعیت رشته فرعی در رشته اصلی را بدست می دهد        ([                 ]رشته اصلی , رشته فرعی ) CharIndex

به مثال ذیل دقت کنید:

IF   CharIndex (@x , @y) < > 0

                   ‘Set   @z = ´Ok

ELSE

Set    @z = ´Not´

– طراحی(UDF( User Define Function  :  یکی از قابلیت های پایگاه داده SQL Server  ایجاد UDF است که کاربران قادرند متناسب با نیاز در هر پروژه به طراحی آن اقدام نمایند

 

هرUDF  طراحی شده بعنوان یک تابع در کلیه دستورات SQL  قابل استفاده است از طرفی درصورتیکه از EXECute  استفاده گردد هر UDF  می تواند مانند یک SP  اجرا گردد همچنین در یک تابع UDF  خروجی می تواند بصورت یک جدول باشد در این حالت از کلمه Table  به جای نوع مقدار برگشتی استفاده خواهد شد و مقدار بازگشتی یک دستور Select  خواهد بود که در مقابل دستور Return  بکار خواهد رفت و تعداد پارامترهای تعریف شده در هر UDF  حداکثر ۱۰۲۴  عدد خواهد بود و مشابه SP  درصورتیکه پارامتر موردنظر ارائه نگردد پیش فرض بعنوان مقدار ورودی پارامتر مدنظر قرار خواهد گرفت نکته قابل ذکر در این خصوص به شرح ذیل می باشد:

 

– درهنگام اجرای یک تابع نام Owner می بایستی ذکر گردد بعنوان مثال ( dbo )

– تعدادی از دستورات درون طراحی UDF قابل اجرا نمی باشند.

– از (ESP) Extended Stored Procedure   درون تابع می توانید استفاده نمائید.

مثال : تابعی بنویسید که فاکتوریل یک عدد را محاسبه کند.

(Create    Function    Fact  (@n  TinyInt

Returns    BigInt

AS

Begin

Declare   @RtnValue   BigInt

IF   @n = 0  or  @n = 1

Set   @RtnValue = 1

ELSE

Set   @RtnValue = dbo.Fact (@n – ۱) *@n

Return   @RtnValue

END

حال  فرض کنید که قرار است تابع فوق در یک برنامه استفاده گردد و قرار باشد سری  محاسبه گردد بدین منظور به شکل ذیل عمل خواهیم نمود:

Declare   @S   BigInt , @C   TinyInt

Set    @S = 0

Set    @C = 0

While    @C < 10    Begin

Set   @C = @ C + 1

Set   @S = @ S + dbo.Fact (@C )

END

Select   @S

مثال :  تابعی برای محاسبه تعداد افراد تحت تکفل یک شخص طراحی کنید و سپس با استفاده از آن یک SP  جهت تعیین فهرست کارکنانی که بیش از n  تحت تکفل دارند بنویسید :

ابتدا تابعی بنام NoChild  که کد پرسنلی ورودی آن است به شکل ذیل طراحی می کنیم.

(Create  Function  NoChild (@P Int

Returns  TinyInt

As

Begin

Declare  @Cnt  TinyInt

(Set  @Cnt = (Select  Count(*)  From  Child  Where  PrsID = @P

Return   @Cnt

END

مثال :  بااستفاده از تابع فهرست کارکنان را براساس بالاترین مدرک تحصیلی افراد تحت تکفل آنها مرتب کنید.

ابتدا تابعی طراحی می کنیم که بزرگترین مدرک تحصیلی افراد تحت تکفل یک شخص را استخراج نماید که در این حالت فرض شده است که با افزایش مدرک تحصیلی میزان کد مدرک تحصیلی نیز افزایش خواهد یافت بنابراین خواهیم داشت:

(Create  Function  dbo.MaxCL (@PID   Int

Returns  TinyInt

As

Begin

Return  (Select  Max(LID)  From  ChdLvL

(Where  PrsID = @PID

END

حال دستور SQL  درخواست شده را می نویسیم بنابراین :

Select  * , dbo.MaxCL(PrsID) AS  CMCL ,

(Select LName From LvL Where LID=dbo.MaxCL(P.PrsID)) AS NMCL

From  Person  AS  P

Order  By  CMCL  Desc , PrsID

طبقه بندی توابع داخلی :

    در فصول گذشته تعدادی تابع داخلی مفید جهت استفاده معرفی گردید تعداد توابع داخلی در هر زبان برنامه نویسی ابزارهای مفیدی جهت توسعه نرم افزار به شمار می روند ولی تعداد آنها در مواردی بسیار زیاد است که فراگیری آنها کمی مشکل به نظر می رسد بدین منظور تعدادی از توابع داخلی مهم در   SQL Server  جهت استفاده های بعدی طبقه بندی شده است.

مطابق شکل هر یک از توابع داخلی در SQL Server  می تواند در یکی از گروههای ۹ گانه قرار گیرد بدین منظور اهم توابع داخلی در جداول ذیل به اختصار درج گردیده است :

توابع رشته ای

نام تابع

عملکرد تابع

مثال

نتیجه

Ascii

کداسکی یک کارکتر را باز می گرداند

(‘Select Ascii (´A

۶۵

Char

کارکتر معادل یک کداسکی را باز می گرداند

(Select   Char (65

´A´

CharIndex

موقعیت قرار گرفتن یک زیر رشته را در یک رشته اعلام می دارد

Select CharIndex (´o´,´Hello

۵

Left

تعدادی کارکتر از سمت چپ یک رشته را استخراج می کند

(Select Left (´Hello´ , ۲

´He´

Len

طول یک رشته را باز می گرداند

(‘Select Len (´Hello

۵

توابع رشته ای

نام تابع

عملکرد تابع

مثال

نتیجه

Lower

یک مجموعه رشته ای را به حروف کوچک تبدیل می کند

(‘Select Lower (´Hello

´hello´

LTrim

فضای خالی یک رشته را از سمت چپ حذف می کند

Select LTrim (´     Hello´)

´Hello´

NChar

کارکتر مربوط به یک عدد را در unicode استاندارد باز می گرداند و عددی بین ۰ تا ۶۵۵۳۵ می باشد

(Select NChar (65

´A´

Replace

یک رشته را کارکتر به کارکتر ترجمه      می کند

Select Replace( (´abc´,´a´,´x

´xbc´

Replicate

یک رشته را به تعداد اعلام شده تکرار      می کند

Select Replicate (´ok´,۲

´okok´

Reverse

یک رشته کارکتری را معکوس می کند

Select Reverse (´Hello´)

´olleH´

Right

تعدادی کارکتر را از سمت راست یک رشته استخراج می کند

Select Right (´Hello´,۲ )

´lo´

RTrim

فضای خالی یک رشته را از سمت راست حذف می کند

Select RTrim (´Hello    ´)

´Hello´

Space

به تعداد اعلام شده کارکتر خالی ایجاد         می نماید

Select Space (5)

´          ´

Str

یک مقدار عددی را به یک رشته با طول ثابت ونقطه شناور ثابت تبدیل می کند و پیش فرض طول ۱۰ بایت و پیش فرض اعشار صفر است

Select Len ( Str (500.5) )

۱۰

 

Select Str (500.3,5,2 )

´۵۰۰٫۳´

Select Str (500.3188,7,3 )

´۵۰۰٫۳۱۹´

Stuff

تعدادی کارکتر را در درون یک رشته حذف و جانشین می کند

Select Stuff (´Hello´,۲,۱,´ok´ )

´Hokllo´

Substring

تعدادی کارکتر را از یک رشته استخراج      می کند

Select Substring (´Hello´,۳,۲ )

´ll´

Unicode

یک عدد صحیح که نشانه مقدار Unicode یک رشته است را باز می گرداند

Select Unicode ( @Mystr )

Upper

یک مجموعه رشته ای را به حروف بزرگ تبدیل می کند

Select Upper (´Hello´)

´HELLO´

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

توابع محاسباتی

نام تابع

عملکرد تابع

مثال

نتیجه

ABS

معادل قدرمطلق یک عدد در ریاضی است

Select ABS (-1.7)

۱٫۷

Ceiling

یک عدد صحیح بزرگتر از عدد ورودی را ارائه می دهد

Select Ceiling (123.45)

۱۲۴

Exp

معادل ex   در ریاضی است

Select Exp (0)

۱

Floor

معادل جزء صحیح x  یعنی [x]  در ریاضی می باشد

Select Floor (-2.7)

Log

مقدار لگاریتم در مبنای عدد e (نپر) را ارائه می دهد

Select Log (Exp (1) )

۱

Log10

مقدار لگاریتم در مبنای ۱۰  را ارائه می دهد

Select Log10 (100)

۲

Power

یک عدد را به توان عدد دیگر می رساند که معادل ab    می باشد

Select Power (10,2)

۱۰۰

Rand

یک عدد تصادفی بین ۰  تا ۱  تولید می کند

Select Rand ( )

Round

یک عدد را تحت شرایطی گرد می کند

Select Round (123.4567,2 )

۱۲۳٫۴۶۰۰

Sign

علامت یک عدد را باز می گرداند و شامل اعداد +۱ , -۱ , ۰  خواهد بود

Select Sign ( -3.7 )

SQuare

توان دوم یک عدد را محاسبه می کند

Select SQuare ( -5 )

۲۵٫۰

SQrt

جذر یک عدد مثبت را باز می گرداند

Select SQrt (1.44 )

۱٫۲

توابع مثلثاتی

نام تابع

عملکرد تابع

مثال

نتیجه

ACos

آرک کسینوس یک عدد را به رادیان محاسبه می کند

 Select ACos (1)

۰٫۰

ASin

آرک سینوس یک عدد را به رادیان محاسبه می کند

Select ASin (0)

۰٫۰

ATAN

آرک تانژانت یک عدد را به رادیان محاسبه می کند

Select ATtan (1)

۰٫۷۸۵۳۹۸

Cos

کسینوس یک عدد را محاسبه می کند

Select Cos (0)

۱٫۰

Cot

کتانژانت یک عدد را محاسبه می کند

Select Cot (1)

۰٫۶۴۲۰۹

Degrees

یک مقدار زاویه را از رادیان  به درجه تبدیل می کند

Select Degrees (PI( ) /2)

۹۰

PI

مقدار عدد پی را باز می گرداند

Select PI ( )

۳٫۱۴۱۵۹۲

Radians

یک زاویه به درجه را به رادیان تبدیل       می کند

Select Radians (180.0)

۳٫۱۴۱۵۹۲

Sin

سینوس یک عدد را محاسبه می کند

Select Sin (Radians (90.0) )

۱٫۰

Tan

تانژانت یک عدد را محاسبه می کند

Select Tan ( PI ( ) /4 )

۰٫۹۹۹۹۹۹

توابع سیستمی و تبدیل داده ای و امنیتی

نام تابع

عملکرد تابع

مثال

نتیجه

APP_Name ( )

نام برنامه اجراکننده را باز می گرداند که بسیار برای موارد امنیتی مفید است

   

Cast

یک نوع داده ای را به نوع دیگری تبدیل می کند

   

Convert

مشابه تابع Cast یک نوع داده ای را به نوع دیگر داده ای تبدیل می کند

   

توابع سیستمی و تبدیل داده ای و امنیتی

نام تابع

عملکرد تابع

مثال

نتیجه

Current _User

نام Owner کنونی را باز می گرداند

   

DataLength

طول داده ای برای یک نوع داده را باز می گرداند

Select DataLength (100)

۴

@@Error

درصورتیکه در فرآیند اجرای یک فرمان خطائی ایجاد گردد شماره خطا را باز می گرداند

   

Host _ID ( )

شماره شناسائی مربوط به یک کامپیوتر متصل شده را باز می گرداند

   

Host_Name ( )

نام کامپیوتر متصل شده به پایگاه داده را باز      می گرداند

   

IsNull

درصورتیکه مقدار ورودی Null  باشد از مقدار پیش فرض استفاده خواهد کرد

   

IsNumeric

درصورتیکه مقدار رشته محتوای عددی داشته باشد مقدار ۱ باز می گردد

Select IsNumeric (´۱۲´)

۱

NewID

یک مقدار منحصر به فرد از  نوع داده ای UniquIdentifier  را تولید می کند

Select  NewID ( )

@@RowCount

تعداد رکوردهای تحت تاثیر دستوراتی نظیر Select یا UpDate و … را باز   می گرداند

   

RowCount_Big

مشابه تابع @@RowCount  آخرین رکوردهای تحت تاثیر را باز می گرداند

   

System_User

در صورتیکه دسترسی به SQL Server و ویندوز معادل باشند در این صورت نام کاربر ویندوز و درغیراینصورت نام کاربر متصل به SQL Server  خواهد بود

Select System _User

 

User_Name

بدون پارامتر معادل تابعCurrent_User خواهد بود و درصورتیکه در عدد ورودی شماره User  اعلام گردد نام کاربر اشاره شده را باز می گرداند

Select User_Name (1)

 

CheckSum

با این تابع می توان یک عدد از محتوای فیلدهای خاص در یک رکورد را  از نظر امنیتی کنترل کرد

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

کنفرانس سعید عبدی —آزمایشگاه  پایگاه داده ها


علی خازنی

مهندس تکلونوژی نرم افزارهستم 25 سال دارم و مدیر ارشد گروه نرم افزاری ویوسافت رو برعهده دارم این سایت رو برای برنامه نویسان و دانشجویان رشته نرم افزار و کامپیوتر و کلیه علاقه مندان این رشته طراحی کردم بتوانم کمک کوچکی برای این عزیزان کرده باشم .

ارسال دیدگاه






کد امنیتی را وارد کنید