實現公司管理數據系統(tǒng)的編程

1、項目五 實現公司管理數據系統(tǒng)的編程 終極目標：會利用變量、函數和流程控制語句編寫transact-sql程序一、促成目標： 1能正確理解和使用sql server變量 2能正確理解和使用sql server 函數 3會使用流程控制語句編寫順序結構、選擇結構和循環(huán)結構的程序二、工作任務 通過使用transact-sql語句編程，實現公司管理數據庫系統(tǒng)的操作。 模塊1 編程獲取某雇員的工作業(yè)績信息一、教學目標 1能正確理解transact-sql批處理 2能正確理解和使用sql server變量 3能正確理解和使用sql server 函數 4會用transact-sql語句編程獲取對用戶有用的信

2、息二、工作任務 通過transact-sql編程，獲取名為王孔若的姓名、性別、出生年月及雇傭日期等信息；然后輸出王孔若所做訂單的明細表、所做的訂單數目及訂單總金額。（一）（一）sql server 2000sql server 2000語法規(guī)則語法規(guī)則 服務器端的程序使用sql語句來編寫。一般而言，一個服務器端的程序是由以下一些成分組成：批、注釋、變量、流程控制語句、錯誤和消息處理。1transact-sql批量 批量是一條或多條sql語句構成的，它被應用程序同時發(fā)送給sql server執(zhí)行。批結束的符號是“go”。 sql server從批中讀取所有語句，并把它們編譯成可執(zhí)行的單元（執(zhí)行計

3、劃），然后，sql server就一次執(zhí)行計劃中的所有語句，所以可以節(jié)省系統(tǒng)開銷。2go 命令 為了在transact-sql腳本中結束批量， sql server用到了關鍵字go。go關鍵字出現在一系列語句之后，使它們能作為一個單獨的批量運行。3續(xù)行 transact-sql語句很多情況下都寫的很長。如果全部放在一行寫，則不利于閱讀與修改?？梢詫⒁粭l語句在多行中進行編寫，transact-sql會忽略空格和行尾的換行符號。4注釋 注釋語句由 “-”（雙減號）或“/*”和“*/”表示。 “-”（雙減號）：用于注釋單行文本。 “/*”和“*/”：用于注釋多行文本，“/*”和“*/”一定要配套使用

4、，否則將會出現錯誤。5、 selectselect語句無源查詢語句無源查詢 select語句無源查詢就是最簡單的語句。 select 常量|變量|函數|表達式 as 別名,n 所謂無源查詢就是使用select語句來查詢不在表中的數據。無源查詢實質上就是在客戶機屏幕上顯示出常量、變量或表達式的值?！纠?】使用select語句查看常量。 select 我是中國人6、print功能：將用戶定義的消息返回客戶端。語法： print 字符串 | 變量 | 字符串的表達式注意： 只允許使用常量、表達式或變量。不允許使用列名。 【例2】使用 print 語句有條件地返回消息。 if exists ( sel

5、ect 產品id from p_order where 產品id=4) print 產品id為4有訂單！（二）sql server 2000變量分類：v局部變量：由用戶自己創(chuàng)建并賦值的，也只有 創(chuàng)建它的用戶可以使用。v全局變量：是有那些由服務器聲明的、通常由服務器賦值的變量。 1、局部變量局部變量局部變量局部變量用用declaredeclare語句聲明，只能用在聲明該變量的過語句聲明，只能用在聲明該變量的過程實體中，即使用范圍是定義它的批、存儲過程和觸程實體中，即使用范圍是定義它的批、存儲過程和觸發(fā)器等。由一個發(fā)器等。由一個 符號開始。符號開始。定義定義：declare declare 局部變

6、量名局部變量名 數據類型數據類型,nn賦值賦值：set set 局部變量名局部變量名= =表達式表達式,nn select select 局部變量名局部變量名= =表達式表達式,nn【例【例3】本模塊聲明了一個不超過本模塊聲明了一個不超過8 8個字符的字符型變量個字符的字符型變量namename的語句為：的語句為： declare name varchar(8)declare name varchar(8)【例4】將王孔若賦給變量name的賦值語句。 declare name varchar(10) set name=王孔若 print 選民 + name【例5】將張立賦給變量name的賦值語

7、句。 declare name varchar(10) select name=張立 print 選民 + name2、全局變量全局變量：由sql server系統(tǒng)提供并賦值的變量，名字由符號開始。用戶不能建立全局變量，也不可能使用set語句去修改全局變量的值。通常應該將全局變量的值賦給在同一個批中的局部變量，以便保存和處理。 全局變量分為兩類：與連接有關的全局變量，如：rowcount 影響的行數；與 系 統(tǒng) 內 部 信 息 有 關 的 全 局 變 量 。 如version sql的版本號。sql常用的全局變量表名稱說明connections返回當前到本服務器的連接的數目。rowcount返

8、回上一條t-sql語句影響的數據行數。error返回上一條t-sql語句執(zhí)行后的錯誤號。procid返回當前存儲過程的id號remserver返回登錄記錄中遠程服務器的名字。spid返回當前服務器進程的id標識。version返回當前sql server服務器的版本和處理器類型。language返回當前sql server服務器的語言。【例6】使用select語句查詢局部變量。declare a int, s varchar(9)set a=40set s=歡迎您select a, s【例7】使用select語句查詢全局變量。select version as 版本select languag

9、e as 語言（三）sql server 2000函數 在t-sql 編程語言中函數可分系統(tǒng)定義函數和用戶定義函數。本節(jié)介紹的是系統(tǒng)定義函數中最常用的數學函數、字符串函數、日期時間函數、聚合函數、系統(tǒng)函數、系統(tǒng)統(tǒng)計函數的最常用的部分。1日期和時間函數 對日期和時間輸入值執(zhí)行操作，返回一個字符串、數字或日期和時間值。名稱函數現在日期時間 getdate()年year(日期型表達式)月month(日期型表達式)日day(日期型表達式)日期 部分datepart(格式串, 日期型表達式)日期 加dateadd(格式串,數值,日期)日期 差datediff(格式串,日期1,日期2) (1) getda

10、te函數功能：返回當前系統(tǒng)日期和服務器的時間。語法： getdate()【例8】輸出當前日期： print getdate()其輸出結果如下： 07 12 2006 3:06am(2) day函數功能：返回某日期的日部分所代表的整數值，該函數等價于datepart(dd,date)。語法： day(date)【例9】 輸出指定日期的日期。 print day(08/30/1998)其輸出結果如下：30(3)month函數功能：返回某日期的月部分所代表的整數值。該函數等價于datepart(mm,date)。語法： month (date)【例10】 輸出指定日期的月份。 print month

11、 (08/30/2006)其輸出結果如下：8(4)year函數功能：返回某日期的年部分所代表的整數值。該函數等價于datepart(yy,date)。語法： year (date)【例11】 輸出指定日期的年份。 print year (08/30/2006) 其輸出結果如下： 2006（5）dateadd函數功能：在某日期上加上一個時間間隔，從而獲得一個新日期。語法： dateadd(datepart, number, date)參數: datepart：是指定應返回的日期部分的參數p123 日期組成部分表日期組成部分表日期部分日期部分縮寫縮寫yearyy, yyyymonthmm, mda

12、yofyeardy, ydaydd, dweekwk, wwweekdaydwhourhhminutemi, n【例12】 獲得指定時間后的新日期 print dateadd (dy, 35, 11/30/2007)輸出結果如下： 01 4 2008 12:00am注意：日期函數完成所有的字符串轉換操作，并返回sql server標準日期格式的輸出結果。所有這些日期函數都會自動執(zhí)行該轉換。(6) datediff函數功能：返回datepart指定的日期的差。語法： datediff(datepart,date1,date2)【例13】 計算兩個日期之間相差的天數。print datediff(

13、dy, 11/30/2007, 1/04/2008)結果如下：35(7) datename函數功能：返回日期的字符串表示。語法： datename (datepart, date)【例14】指定日期是星期幾： print datename (dw, 11/29/2007) 輸出結果如下： 星期四 即2007年11月29日是星期四(8) datepart函數功能：指定datepart的整數值(注意datepart函數與datepart參數不同)。語法： datepart(datepart, date)【例15】從日期中得到月份的數值表示： print datepart(mm, 6/11/2006

14、)輸出結果：62.字符串函數 字符串函數對字符串輸入值執(zhí)行操作，返回一個字符串或數字值。 參考：p127 常用的字符串函數名稱函數ascii碼ascii(字符表達式)字符char(數字表達式)字符char(數字表達式)小寫lower(字符表達式)大寫upper(字符表達式)長度len(字符表達式)空格space(數字表達式)名稱函數截取左左字串left(字符表達式,整數)截取右右字串right(字符表達式,整數)截取中間中間字串substring(字符表達式,起始點,n)求子串位置charindex(字符表達式1, 字符表達式2,開始位置)剪去左左空格ltrim(字符表達式)剪去右右空格rtr

15、im(字符表達式)重復字串replicate(字符表達式,n)倒置字串reverse(字符表達式)數值轉字串str(數字表達式)（1）lower函數功能：將所有字母表中存在的字符轉換為小寫形式。所有在字母表中不存在的字符保持不變。語法： lower(字符串)【例16】 print lower (abcdef)其輸出結果如下：abcdef（2）ltrim函數 功能：刪除字符串中所有的前導空格。 語法： ltrim(字符串)（3）rtrim函數 功能：刪除指定字符串末尾的所有后綴空格。 語法： rtrim(字符串)（4）reverse函數功能：將字符串中的所有字符逆轉。語法： reverse(字符

16、串)【例17】 print reverse(abcdef)其輸出結果如下：fedcba （5）str函數功能：將數值型數據轉換為字符串型數據。語法： str(數值,總長度,小數位數)q “數值”是將要轉換成字符串型數據的數值型數據。q “總長度”可以用來指定字符串的總長度。q “小數位數”參數也是可選項，用來指定數值型數據小數部分的位數(默認值為10)。3.數據類型轉換函數 將數據從一種數據類型轉換為另一種數據類型的工作通常由sql server自動完成。如果要進行顯示轉換數據類型，需要使用兩種函數：cast()或者convert()函數。（1）cast功能：將某種數據類型的表達式顯式轉換為另

17、一種數據類型。語法 cast ( 表達式 as 數據類型 ) 【例18】將 int 列（“庫存量 ” 列）轉換為varchar(20) 列，以便使用 like 子句。select 產品名, 單價, 庫存量from productwhere cast( 庫存量 as varchar(20) like 1_（2）convert功能：將某種數據類型的表達式顯式轉換為另一種數據類型。語法： convert (目的數據類型目的數據類型, 要轉化的表達式，要轉化的表達式，日期樣式日期樣式)【例19】print convert(char(2),month(getdate()工作任務 通過transact-s

18、ql編程，獲取名為“王孔若”的姓名、性別、出生年月及雇傭日期等信息；然后輸出王孔若所做訂單的明細表、所做的訂單數目及訂單總金額。 程序代碼模塊2 編程實現訂購訂單一、教學目標 能正確理解transact-sql中的流程控制語句，包括判斷語句、循環(huán)語句，標簽、return語句以及goto語句。工作任務 雇員王孔若簽訂了一條供應給鹿城中學50個優(yōu)盤的訂單，編程實現將訂單涉及到的相關信息寫入到數據庫中。 流程控制語句流程控制語句 流程控制語句是指那些用來控制程序執(zhí)行和流程分支的命令，在sql server 2000中，流程控制語句主要用來控制sql語句、語句塊或者存儲過程的執(zhí)行流程。 （一） beg

19、in.end 功能：對用來將多行sql語句組織在一起，以達到一起執(zhí)行的目的。 語法： begin sql語句組 end 注意：必須包含在一個單獨的批處理中。（二） if.else語句語法格式： if else 注意：使用select語句作為這個條件表達式時，必須用圓括號將select語句括起來?！纠?0】編程判斷在p_order訂單表中是否存在 “訂貨日期”在2007年5月7號以后的訂單，分別打印相關信息。程序清單1：if (select count(*) from p_order where 訂貨日期 2007-05-07) 0 print 存在2007年5月7號以后的訂單else prin

20、t 不存在2007年5月7號以后的訂單 程序清單2：if exists (select * from p_order where 訂貨日期 2007-05-07) print 存在2007年5月7號以后的訂單else print 不存在2007年5月7號以后的訂單 （三）while循環(huán) while語句用于創(chuàng)建一個循環(huán)。語法：1）while 2）while begin end 【例21】計算并輸出1+2+3+100表達式的和。程序清單：declare sum intdeclare i intselect sum=0select i=1while i=10 and i=30)begin if i%

21、3=0 begin print i set i=i+1 set n=n+1 continue end if i=22 break set i=i+1endprint 有+convert(char(2),n)+ 個數能被3整除【例22】程序功能： 該程序用于輸出能被3整除的整數。其中用了continue語句和break語句。當程序執(zhí)行到i為22時，直接跳出循環(huán)。故其執(zhí)行結果如下：12151821有4個數能被3整除（四） goto語句和return語句 goto語句用于讓sql server跳轉到sql代碼中的指定標簽處，標簽設計成如下形式：語法：goto 標簽名 標簽名: 【例23】分析以下語句

22、的執(zhí)行結果。declare sum int,x int,y intselect x=9,y=10select sum=x+ygoto label1set sum=sum+100label1: print x+y的值為+convert(char(3),sum)【例24】使用循環(huán)計算所給的局部變量的變化值 declare r int , s int , t intselect r=2, s=3 print rwhile r 5 begin select t=100*r+s print t select r=r+2 select s=1end（五） waitfor語句 功能： 指定觸發(fā)語句塊、存儲過

23、程或事務執(zhí)行的時間、時間間隔或事件。語法 waitfor delay time | time time 參數：q delay：指示 一直等到指定的時間過去，最長可達 24 小時。q time：要等待的時間?？梢园?datetime 數據可接受的格式指定 time，也可以用局部變量指定此參數。不能指定日期。q time：指示 sql server 等待到指定時間。 使用 waitfor delay【例25】等待30秒后執(zhí)行select語句 use companyinfo go waitfor delay 00:00:30 select * from product使用 waitfor time【

24、例26】等到11點12分后才執(zhí)行select 語句use companyinfogo waitfor time 11:12:00 slect * from product（六） return語句 用于無條件地終止一個查詢、存儲過程或者批處理，此時位于return語句之后的程序將不會被執(zhí)行。 語法： return integer_expression 參數integer_expression為返回的整型值。存儲過程可以給調用過程或應用程序返回整型值。拓展知識拓展知識 系統(tǒng)內置函數系統(tǒng)內置函數 在transact-sql語言中，函數被用來執(zhí)行一些特殊的運算以支持sql server的標準命令。transact-sql 編程語言提供了三種函數： 標量函數：標量函數用于對傳遞給它的一個或者多個參數值進行處理和計算，并返回一個單一的值。聚合函數：聚合函數用于對一組值執(zhí)行計算并返回一個單一的值。 行集函數：行集函數可以在transact-sql語句中當作表引用。(一一)系統(tǒng)函數系統(tǒng)函數 系統(tǒng)函數用于返回有關sql server系統(tǒng)、用戶、數據庫和數據庫對象的信息。系統(tǒng)函數可以讓用戶在得到信息后，使用條件語句，根