實驗4 T-SQL語言.doc

實驗4 T-SQL語言實驗?zāi)康模?、 掌握Transact-SQL語言中的變量、運(yùn)算符、函數(shù)；2、 掌握Transact-SQL語句編寫批處理和流程控制程序。3、 掌握游標(biāo)的創(chuàng)建，打開，數(shù)據(jù)讀取，關(guān)閉，銷毀的基本方法實驗內(nèi)容和步驟：Transact-SQL語言是在SQL Server 2005 中使用的程序設(shè)計語言。不但包含了標(biāo)準(zhǔn)SQL語言部分，而且為了滿足實際應(yīng)用中的編程需要，在Transact-SQL語言中還另外增加了一些語言要素，它們包括注釋、變量、運(yùn)算符、函數(shù)和流程控制語句等。所有的語句都在查詢分析器中執(zhí)行。1. Transact-SQL語句實驗內(nèi)容1： 返回在程序執(zhí)行過程中上一條SQL語句影響的記錄數(shù)（全局變量ROWCOUNT），注意觀察結(jié)果窗口。use teach-打開teach 數(shù)據(jù)庫GO-查詢學(xué)生表中的記錄SELECT * FROM Student-查詢上一條SELECT語句影響的記錄數(shù)SELECT ROWCOUNT AS 第一次查詢返回的記錄數(shù)GO實驗內(nèi)容2： 查詢學(xué)生表的人數(shù)，將返回的記錄數(shù)賦給局部變量RowsUSE teachGODECLARE Rows int-聲明局部變量-給局部變量賦值SET Rows=(SELECT COUNT(*) FROM Student)-顯示局部變量的值SELECT Rows AS SELECT返回的記錄數(shù)GO實驗內(nèi)容3： 設(shè)置一個局部變量為表，進(jìn)行插入和查詢操作- 定義變量為臨時表DECLARE Table_Example TABLE (Col_num int PRIMARY KEY, Col_text char (50)- 向該變量中添加兩行內(nèi)容INSERT INTO Table_Example VALUES (1, this is a)INSERT INTO Table_Example VALUES (2, table data type example)- 從臨時表中查詢SELECT * FROM Table_ExampleGO實驗內(nèi)容4：(練習(xí)，自己試著寫一下)設(shè)置一個局部變量其值為選修了課程的人數(shù)，并顯示提示： DECLARE Rows int-聲明局部變量-給局部變量賦值SET Rows=(SELECT COUNT(distinct sno) FROM SC )-顯示局部變量的值SELECT Rows AS 選課人數(shù)GO實驗內(nèi)容5：檢查sysdatabases中的每一個數(shù)據(jù)庫，使用數(shù)據(jù)庫標(biāo)識號來確定數(shù)據(jù)庫名稱。USE master-打開master 數(shù)據(jù)庫GO-DB_NAME 是一個函數(shù)，功能是根據(jù)數(shù)據(jù)庫標(biāo)識號，來返回數(shù)據(jù)庫的名稱SELECT dbid , DB_NAME(dbid) AS DB_NAMEFROM sysdatabases ORDER BY dbidGO實驗內(nèi)容6：從數(shù)據(jù)庫中返回學(xué)生表的第2列的名稱USE teachGO-定義局部變量，用于存放學(xué)生表的ID號DECLARE 學(xué)生表ID INT-OBJECT_ID是一個函數(shù)，功能是返回數(shù)據(jù)庫對象的編號SET 學(xué)生表ID= OBJECT_ID(Student)-COL_NAME是一個函數(shù)， 返回指定表中指定字段的名稱，即列名SELECT COL_NAME(學(xué)生表ID,2) AS 學(xué)生表中第2列的名稱GO實驗內(nèi)容7：簡單年、月、日函數(shù)SELECT MONTH(03/12/1998), DAY(03/12/1998),YEAR(03/12/1998)實驗內(nèi)容8：從系統(tǒng)當(dāng)前日期中返回系統(tǒng)當(dāng)前的年份數(shù)、月份數(shù)和天數(shù)。- GETDATE()函數(shù)返回系統(tǒng)當(dāng)前日期SELECT YEAR(GETDATE() AS 當(dāng)前年份, MONTH(GETDATE() AS 當(dāng)前月份, DAY(GETDATE() AS 當(dāng)前天數(shù)GO實驗內(nèi)容9：自己編寫函數(shù)。編寫一個函數(shù)，名稱為“函數(shù)_學(xué)生選課數(shù)”，要求根據(jù)輸入的學(xué)生學(xué)號，求此學(xué)生的選課數(shù)。USE teachGO-通過CREATE FUNCTION語句創(chuàng)建用戶的自定義函數(shù)“函數(shù)_學(xué)生選課數(shù)”CREATE FUNCTION 函數(shù)_學(xué)生選課數(shù)(xuehao AS char(9) -函數(shù)的輸入?yún)?shù))RETURNS int -返回結(jié)果類型BEGIN -函數(shù)體 DECLARE 選課數(shù) AS int SELECT 選課數(shù) =COUNT(cno) FROM SC WHERE Sno=xuehao RETURN 選課數(shù)ENDGO-使用自定義函數(shù)，求選課表（SC）中每位學(xué)生的選課數(shù)SELECT DISTINCT Student.Sno,Sname,選課數(shù)=dbo. 函數(shù)_學(xué)生選課數(shù)(SC.Sno)FROM Student,SCWHERE Student .Sno=SC.SnoGO實驗內(nèi)容10: (練習(xí)，自己試著寫一下)練習(xí)自定義函數(shù)，求每門課程的選課人數(shù)提示：USE teachGOCREATE FUNCTION 函數(shù)_選課人數(shù)(cno AS char(9) )RETURNS int BEGIN DECLARE 選課人數(shù) AS int SELECT 選課人數(shù) =COUNT(sno) FROM SC WHERE cno=cno RETURN 選課人數(shù)ENDGO-使用自定義函數(shù)，查詢每門課的選課人數(shù)（注：沒有人選的課程，不會出現(xiàn)）SELECT DISTINCT Course.Cno ,Cname ,選課人數(shù)=dbo. 函數(shù)_選課人數(shù)(SC.Cno)FROM Course,SCWHERE Co=SC.Cno GO-使用自定義函數(shù)，通過左外連接，保留了左邊被舍棄的元組SELECT DISTINCT Course.Cno ,Cname ,選課人數(shù)=dbo. 函數(shù)_選課人數(shù)(SC.Cno)FROM Course left join SC on Co=SC.Cno GO2. 游標(biāo)的創(chuàng)建和使用游標(biāo)（Cursor）是處理數(shù)據(jù)的一種方法，為了查看或者處理結(jié)果集中的數(shù)據(jù)，游標(biāo)提供了在結(jié)果集中一次以行或者多行前進(jìn)或向后瀏覽數(shù)據(jù)的能力。我們可以把游標(biāo)當(dāng)作一個指針，它可以指定結(jié)果中的任何位置，然后允許用戶對指定位置的數(shù)據(jù)進(jìn)行處理。T-SQL游標(biāo)：該游標(biāo)是基于Declare Cursor 語法，主要用于Transaction-SQL腳本、存儲過程以及觸發(fā)器中。Transaction-SQL游標(biāo)在服務(wù)器處理由客戶端發(fā)送到服務(wù)器的Transaction-SQL語句。使用游標(biāo)要遵循以下順序：聲明游標(biāo)打開游標(biāo)讀取數(shù)據(jù)關(guān)閉游標(biāo)刪除游標(biāo)。1聲明游標(biāo)實驗內(nèi)容1：以下是一個符合SQL-92標(biāo)準(zhǔn)的游標(biāo)聲明，此游標(biāo)是只讀的，只能從頭到尾順序提取數(shù)據(jù)。 DECLARE S_CUR1 CURSOR FOR SELECT * FROM STUDENT FOR READ ONLY實驗內(nèi)容2：以下是一個符合T-SQL擴(kuò)展的游標(biāo)聲明，此游標(biāo)是動態(tài)游標(biāo)，可前后滾動，可對姓名列進(jìn)行修改。 DECLARE S_CUR2 CURSOR SCROLL DYNAMIC FOR SELECT * FROM STUDENTFOR UPDATE OF SName2. 打開游標(biāo)聲明游標(biāo)之后，要使用游標(biāo)提取數(shù)據(jù)，必須先打開游標(biāo)。使用OPEN語句可以打開游標(biāo)。打開游標(biāo)后，可以使用全局變量CURSOR_ROWS查看游標(biāo)中數(shù)據(jù)行的數(shù)目。實驗內(nèi)容3：打開游標(biāo)S_CUR1，輸出其行數(shù) OPEN S_CUR1 SELECT 游標(biāo)s_cur1的數(shù)據(jù)行數(shù)=CURSOR_ROWS注意：通過聯(lián)機(jī)叢書來查詢CURSOR_ROWS，來理解其值的含義3讀取數(shù)據(jù)游標(biāo)打開后，就可以使用FETCH命令從中讀取數(shù)據(jù)。具體語句格式見聯(lián)機(jī)叢書。實驗內(nèi)容4：從游標(biāo)S_CUR1中提取數(shù)據(jù)，只能用NEXT提取數(shù)據(jù)。 FETCH NEXT FROM S_CUR1提示：選中該語句，點擊執(zhí)行，查看結(jié)果。再點擊執(zhí)行，查看結(jié)果。重復(fù)執(zhí)行。實驗內(nèi)容5：從游標(biāo)S_CUR2中提取數(shù)據(jù)：OPEN S_CUR2-讀取游標(biāo)第一行FETCH FIRST FROM S_CUR2-讀取下一行FETCH NEXT FROM S_CUR2-讀取上一行FETCH PRIOR FROM S_CUR2-讀取最后一行FETCH LAST FROM S_CUR2-讀取當(dāng)前行的上二行FETCH RELATIVE-2 FROM S_CUR24關(guān)閉游標(biāo)游標(biāo)使用完后，要及時關(guān)閉游標(biāo)，以釋放當(dāng)前的結(jié)果集并解除定位在該游標(biāo)記錄行上的游標(biāo)鎖定。實驗內(nèi)容6：關(guān)閉游標(biāo)S_CUR1CLOSE S_CUR15刪除游標(biāo)關(guān)閉一個游標(biāo)后，其數(shù)據(jù)結(jié)構(gòu)仍存儲在系統(tǒng)中，需要的時候仍然可以再次使用OPEN語句打開和使用該游標(biāo)。如果確定以后不再使用該游標(biāo)，則可以刪除游標(biāo)，將游標(biāo)占用的系統(tǒng)空間釋放出來。實驗內(nèi)容7：刪除游標(biāo)S_CUR1DEALLOCATE S_CUR13. 收獲體會此次實驗與之前的幾次試驗都大有所不同，此次實驗的內(nèi)容主要是關(guān)于Transact-SQL語言中的變量、運(yùn)算符、函數(shù)以及游標(biāo)的相關(guān)操作。對于