『壹』 一條查詢的sql語句,它有個查詢欄位為list集合,該怎麼弄
SQL查詢語句精華大全 一、 簡單查詢
簡單的Transact-SQL查詢只包括選擇列表、FROM子句和WHERE子句。它們分別說明所查詢列、查詢的
表或視圖、以及搜索條件等。
例如,下面的語句查詢testtable表中姓名為「張三」的nickname欄位和email欄位。
SELECT nickname,email
FROM testtable
WHERE name='張三'(一) 選擇列表
選擇列表(select_list)指出所查詢列,它可以是一組列名列表、星號、表達式、變數(包括局部變
量和全局變數)等構成。1、選擇所有列
例如,下面語句顯示testtable表中所有列的數據:
SELECT *
FROM testtable2、選擇部分列並指定它們的顯示次序
查詢結果集合中數據的排列順序與選擇列表中所指定的列名排列順序相同。
例如:
SELECT nickname,email
FROM testtable3、更改列標題
在選擇列表中,可重新指定列標題。定義格式為:
列標題=列名
列名 列標題
如果指定的列標題不是標準的標識符格式時,應使用引號定界符,例如,下列語句使用漢字顯示列
標題:
SELECT 昵稱=nickname,電子郵件=email
FROM testtable4、刪除重復行
SELECT語句中使用ALL或DISTINCT選項來顯示表中符合條件的所有行或刪除其中重復的數據行,默認
為ALL。使用DISTINCT選項時,對於所有重復的數據行在SELECT返回的結果集合中只保留一行。5、限制返回的行數
使用TOP n [PERCENT]選項限制返回的數據行數,TOP n說明返回n行,而TOP n PERCENT時,說明n是
表示一百分數,指定返回的行數等於總行數的百分之幾。
例如:
SELECT TOP 2 *
FROM testtable
SELECT TOP 20 PERCENT *
FROM testtable(二) FROM子句
FROM子句指定SELECT語句查詢及與查詢相關的表或視圖。在FROM子句中最多可指定256個表或視圖,
它們之間用逗號分隔。
在FROM子句同時指定多個表或視圖時,如果選擇列表中存在同名列,這時應使用對象名限定這些列
所屬的表或視圖。例如在usertable和citytable表中同時存在cityid列,在查詢兩個表中的cityid時應
使用下面語句格式加以限定:
SELECT username,citytable.cityid
FROM usertable,citytable
WHERE usertable.cityid=citytable.cityid
在FROM子句中可用以下兩種格式為表或視圖指定別名:
表名 as 別名
表名 別名
例如上面語句可用表的別名格式表示為:
SELECT username,b.cityid
FROM usertable a,citytable b
WHERE a.cityid=b.cityid
SELECT不僅能從表或視圖中檢索數據,它還能夠從其它查詢語句所返回的結果集合中查詢數據。
例如:
SELECT a.au_fname+a.au_lname
FROM authors a,titleauthor ta
(SELECT title_id,title
FROM titles
WHERE ytd_sales>10000
) AS t
WHERE a.au_id=ta.au_id
AND ta.title_id=t.title_id
此例中,將SELECT返回的結果集合給予一別名t,然後再從中檢索數據。(三) 使用WHERE子句設置查詢條件
WHERE子句設置查詢條件,過濾掉不需要的數據行。例如下面語句查詢年齡大於20的數據:
SELECT *
FROM usertable
WHERE age>20
WHERE子句可包括各種條件運算符:
比較運算符(大小比較):>、>=、=、<、<=、<>、!>、!<
范圍運算符(表達式值是否在指定的范圍):BETWEEN…AND…
NOT BETWEEN…AND…
列表運算符(判斷表達式是否為列表中的指定項):IN (項1,項2……)
NOT IN (項1,項2……)
模式匹配符(判斷值是否與指定的字元通配格式相符):LIKE、NOT LIKE
空值判斷符(判斷表達式是否為空):IS NULL、NOT IS NULL
邏輯運算符(用於多條件的邏輯連接):NOT、AND、OR
1、范圍運算符例:age BETWEEN 10 AND 30相當於age>=10 AND age<=30
2、列表運算符例:country IN ('Germany','China')
3、模式匹配符例:常用於模糊查找,它判斷列值是否與指定的字元串格式相匹配。可用於char、
varchar、text、ntext、datetime和smalldatetime等類型查詢。
可使用以下通配字元:
百分號%:可匹配任意類型和長度的字元,如果是中文,請使用兩個百分號即%%。
下劃線_:匹配單個任意字元,它常用來限製表達式的字元長度。
方括弧[]:指定一個字元、字元串或范圍,要求所匹配對象為它們中的任一個。
[^]:其取值也[] 相同,但它要求所匹配對象為指定字元以外的任一個字元。
例如:
限制以Publishing結尾,使用LIKE '%Publishing'
限制以A開頭:LIKE '[A]%'
限制以A開頭外:LIKE '[^A]%'
4、空值判斷符例WHERE age IS NULL
5、邏輯運算符:優先順序為NOT、AND、OR
(四)查詢結果排序
使用ORDER BY子句對查詢返回的結果按一列或多列排序。ORDER BY子句的語法格式為:
ORDER BY {column_name [ASC|DESC]} [,…n]
其中ASC表示升序,為默認值,DESC為降序。ORDER BY不能按ntext、text和image數據類型進行排
序。
例如:
SELECT *
FROM usertable
ORDER BY age desc,userid ASC
另外,可以根據表達式進行排序。二、 聯合查詢
UNION運算符可以將兩個或兩個以上上SELECT語句的查詢結果集合合並成一個結果集合顯示,即執行聯
合查詢。UNION的語法格式為:
select_statement
UNION [ALL] selectstatement
[UNION [ALL] selectstatement][…n]
其中selectstatement為待聯合的SELECT查詢語句。
ALL選項表示將所有行合並到結果集合中。不指定該項時,被聯合查詢結果集合中的重復行將只保留一
行。
聯合查詢時,查詢結果的列標題為第一個查詢語句的列標題。因此,要定義列標題必須在第一個查詢語
句中定義。要對聯合查詢結果排序時,也必須使用第一查詢語句中的列名、列標題或者列序號。
在使用UNION 運算符時,應保證每個聯合查詢語句的選擇列表中有相同數量的表達式,並且每個查詢選
擇表達式應具有相同的數據類型,或是可以自動將它們轉換為相同的數據類型。在自動轉換時,對於數值類
型,系統將低精度的數據類型轉換為高精度的數據類型。
在包括多個查詢的UNION語句中,其執行順序是自左至右,使用括弧可以改變這一執行順序。例如:
查詢1 UNION (查詢2 UNION 查詢3)三、連接查詢
通過連接運算符可以實現多個表查詢。連接是關系資料庫模型的主要特點,也是它區別於其它類型
資料庫管理系統的一個標志。
在關系資料庫管理系統中,表建立時各數據之間的關系不必確定,常把一個實體的所有信息存放在
一個表中。當檢索數據時,通過連接操作查詢出存放在多個表中的不同實體的信息。連接操作給用戶帶
來很大的靈活性,他們可以在任何時候增加新的數據類型。為不同實體創建新的表,爾後通過連接進行
查詢。
連接可以在SELECT 語句的FROM子句或WHERE子句中建立,似是而非在FROM子句中指出連接時有助於
將連接操作與WHERE子句中的搜索條件區分開來。所以,在Transact-SQL中推薦使用這種方法。
SQL-92標准所定義的FROM子句的連接語法格式為:
FROM join_table join_type join_table
[ON (join_condition)]
其中join_table指出參與連接操作的表名,連接可以對同一個表操作,也可以對多表操作,對同一
個表操作的連接又稱做自連接。
join_type 指出連接類型,可分為三種:內連接、外連接和交叉連接。內連接(INNER JOIN)使用比
較運算符進行表間某(些)列數據的比較操作,並列出這些表中與連接條件相匹配的數據行。根據所使用
的比較方式不同,內連接又分為等值連接、自然連接和不等連接三種。
外連接分為左外連接(LEFT OUTER JOIN或LEFT JOIN)、右外連接(RIGHT OUTER JOIN或RIGHT JOIN)
和全外連接(FULL OUTER JOIN或FULL JOIN)三種。與內連接不同的是,外連接不只列出與連接條件相匹
配的行,而是列出左表(左外連接時)、右表(右外連接時)或兩個表(全外連接時)中所有符合搜索條件的
數據行。
交叉連接(CROSS JOIN)沒有WHERE 子句,它返回連接表中所有數據行的笛卡爾積,其結果集合中的
數據行數等於第一個表中符合查詢條件的數據行數乘以第二個表中符合查詢條件的數據行數。
連接操作中的ON (join_condition) 子句指出連接條件,它由被連接表中的列和比較運算符、邏輯
運算符等構成。
無論哪種連接都不能對text、ntext和image數據類型列進行直接連接,但可以對這三種列進行間接
連接。例如:
SELECT p1.pub_id,p2.pub_id,p1.pr_info
FROM pub_info AS p1 INNER JOIN pub_info AS p2
ON DATALENGTH(p1.pr_info)=DATALENGTH(p2.pr_info)(一)內連接
內連接查詢操作列出與連接條件匹配的數據行,它使用比較運算符比較被連接列的列值。內連接分
三種:
1、等值連接:在連接條件中使用等於號(=)運算符比較被連接列的列值,其查詢結果中列出被連接
表中的所有列,包括其中的重復列。
2、不等連接: 在連接條件使用除等於運算符以外的其它比較運算符比較被連接的列的列值。這些
運算符包括>、>=、<=、<、!>、!<和<>。
3、自然連接:在連接條件中使用等於(=)運算符比較被連接列的列值,但它使用選擇列表指出查詢
結果集合中所包括的列,並刪除連接表中的重復列。
例,下面使用等值連接列出authors和publishers表中位於同一城市的作者和出版社:
SELECT *
FROM authors AS a INNER JOIN publishers AS p
ON a.city=p.city
又如使用自然連接,在選擇列表中刪除authors 和publishers 表中重復列(city和state):
SELECT a.*,p.pub_id,p.pub_name,p.country
FROM authors AS a INNER JOIN publishers AS p
ON a.city=p.city
(二)外連接
內連接時,返回查詢結果集合中的僅是符合查詢條件( WHERE 搜索條件或 HAVING 條件)和連接條件
的行。而採用外連接時,它返回到查詢結果集合中的不僅包含符合連接條件的行,而且還包括左表(左外
連接時)、右表(右外連接時)或兩個邊接表(全外連接)中的所有數據行。
如下面使用左外連接將論壇內容和作者信息連接起來:
SELECT a.*,b.* FROM luntan LEFT JOIN usertable as b
ON a.username=b.username
下面使用全外連接將city表中的所有作者以及user表中的所有作者,以及他們所在的城市:
SELECT a.*,b.*
FROM city as a FULL OUTER JOIN user as b
ON a.username=b.username(三)交叉連接
交叉連接不帶WHERE 子句,它返回被連接的兩個表所有數據行的笛卡爾積,返回到結果集合中的數
據行數等於第一個表中符合查詢條件的數據行數乘以第二個表中符合查詢條件的數據行數。
例,titles表中有6類圖書,而publishers表中有8家出版社,則下列交叉連接檢索到的記錄數將等
於6*8=48行。
SELECT type,pub_name
FROM titles CROSS JOIN publishers
ORDER BY typeSQL核心語句(非常實用的幾個技巧)插入數據向表中添加一個新記錄,你要使用SQL INSERT 語句。這里有一個如何使用這種語句的例子:INSERT mytable (mycolumn) VALUES (『some data』)這個語句把字元串』some data』插入表mytable的mycolumn欄位中。將要被插入數據的欄位的名字在第一個括弧中指定,實際的數據在第二個括弧中給出。INSERT 語句的完整句法如下:INSERT [INTO] {table_name|view_name} [(column_list)] {DEFAULT VALUES |</p><p>Values_list | select_statement}如果一個表有多個欄位,通過把欄位名和欄位值用逗號隔開,你可以向所有的欄位中插入數據。假設表mytable有三個欄位first_column,second_column,和third_column。下面的INSERT語句添加了一條三個欄位都有值的完整記錄:INSERT mytable (first_column,second_column,third_column)VALUES (『some data』,』some more data』,』yet more data』)注意你可以使用INSERT語句向文本型欄位中插入數據。但是,如果你需要輸入很長的字元串,你應該使用WRITETEXT語句。這部分內容對本書來說太高級了,因此不加討論。要了解更多的信息,請參考Microsoft SQL Sever 的文檔。如果你在INSERT 語句中只指定兩個欄位和數據會怎麼樣呢?換句話說,你向一個表中插入一條新記錄,但有一個欄位沒有提供數據。在這種情況下,有下面的四種可能:如果該欄位有一個預設值,該值會被使用。例如,假設你插入新記錄時沒有給欄位third_column提供數據,而這個欄位有一個預設值』some value』。在這種情況下,當新記錄建立時會插入值』some value』。如果該欄位可以接受空值,而且沒有預設值,則會被插入空值。如果該欄位不能接受空值,而且沒有預設值,就會出現錯誤。你會收到錯誤信息:The column in table mytable may not be null.最後,如果該欄位是一個標識欄位,那麼它會自動產生一個新值。當你向一個有標識欄位的表中插入新記錄時,只要忽略該欄位,標識欄位會給自己賦一個新值。注意向一個有標識欄位的表中插入新記錄後,你可以用SQL變數@@identity來訪問新記錄的標識欄位的值。考慮如下的SQL語句:INSERT mytable (first_column) VALUES(『some value』)INSERT anothertable(another_first,another_second)VALUES(@@identity,』some value』)如果表mytable有一個標識欄位,該欄位的值會被插入表anothertable的another_first欄位。這是因為變數@@identity總是保存最後一次插入標識欄位的值。欄位another_first應該與欄位first_column有相同的數據類型。但是,欄位another_first不能是應該標識欄位。Another_first欄位用來保存欄位first_column的值。刪除記錄要從表中刪除一個或多個記錄,需要使用SQL DELETE語句。你可以給DELETE 語句提供WHERE 子句。WHERE子句用來選擇要刪除的記錄。例如,下面的這個DELETE語句只刪除欄位first_column的值等於』Delete Me』的記錄:DELETE mytable WHERE first_column=』Deltet Me』DELETE 語句的完整句法如下:DELETE [FROM] {table_name|view_name} [WHERE clause]在SQL SELECT 語句中可以使用的任何條件都可以在DELECT 語句的WHERE子句中使用。例如,下面的這個DELETE語句只刪除那些first_column欄位的值為』goodbye』或second_column欄位的值為』so long』的記錄:DELETE mytable WHERE first_column=』goodby』 OR second_column=』so long』如果你不給DELETE 語句提供WHERE 子句,表中的所有記錄都將被刪除。你不應該有這種想法。如果你想刪除應該表中的所有記錄,應使用第十章所講的TRUNCATE TABLE語句。注意為什麼要用TRUNCATE TABLE 語句代替DELETE語句?當你使用TRUNCATE TABLE語句時,記錄的刪除是不作記錄的。也就是說,這意味著TRUNCATE TABLE 要比DELETE快得多。更新記錄要修改表中已經存在的一條或多條記錄,應使用SQL UPDATE語句。同DELETE語句一樣,UPDATE語句可以使用WHERE子句來選擇更新特定的記錄。請看這個例子:UPDATE mytable SET first_column=』Updated!』 WHERE second_column=』Update Me!』這個UPDATE 語句更新所有second_column欄位的值為』Update Me!』的記錄。對所有被選中的記錄,欄位first_column的值被置為』Updated!』。下面是UPDATE語句的完整句法:UPDATE {table_name|view_name} SET [{table_name|view_name}]{column_list|variable_list|variable_and_column_list}[,{column_list2|variable_list2|variable_and_column_list2}…[,{column_listN|variable_listN|variable_and_column_listN}]][WHERE clause]注意你可以對文本型欄位使用UPDATE語句。但是,如果你需要更新很長的字元串,應使用UPDATETEXT語句。這部分內容對本書來說太高級了,因此不加討論。要了解更多的信息,請參考Microsoft SQL Sever 的文檔。如果你不提供WHERE子句,表中的所有記錄都將被更新。有時這是有用的。例如,如果你想把表titles中的所有書的價格加倍,你可以使用如下的UPDATE 語句:你也可以同時更新多個欄位。例如,下面的UPDATE語句同時更新first_column,second_column,和third_column這三個欄位:UPDATE mytable SET first_column=』Updated!』Second_column=』Updated!』Third_column=』Updated!』WHERE first_column=』Update Me1』技巧SQL忽略語句中多餘的空格。你可以把SQL語句寫成任何你最容易讀的格式。用SELECT 創建記錄和表你也許已經注意到,INSERT 語句與DELETE語句和UPDATE語句有一點不同,它一次只操作一個記錄。然而,有一個方法可以使INSERT 語句一次添加多個記錄。要作到這一點,你需要把INSERT 語句與SELECT 語句結合起來,象這樣:INSERT mytable (first_column,second_column)SELECT another_first,another_secondFROM anothertableWHERE another_first=』Copy Me!』這個語句從anothertable拷貝記錄到mytable.只有表anothertable中欄位another_first的值為』Copy Me!』的記錄才被拷貝。當為一個表中的記錄建立備份時,這種形式的INSERT 語句是非常有用的。在刪除一個表中的記錄之前,你可以先用這種方法把它們拷貝到另一個表中。如果你需要拷貝整個表,你可以使用SELECT INTO 語句。例如,下面的語句創建了一個名為newtable的新表,該表包含表mytable的所有數據:SELECT * INTO newtable FROM mytable你也可以指定只有特定的欄位被用來創建這個新表。要做到這一點,只需在欄位列表中指定你想要拷貝的欄位。另外,你可以使用WHERE 子句來限制拷貝到新表中的記錄。下面的例子只拷貝欄位second_columnd的值等於』Copy Me!』的記錄的first_column欄位。SELECT first_column INTO newtableFROM mytableWHERE second_column=』Copy Me!』使用SQL修改已經建立的表是很困難的。例如,如果你向一個表中添加了一個欄位,沒有容易的辦法來去除它。另外,如果你不小心把一個欄位的數據類型給錯了,你將沒有辦法改變它。但是,使用本節中講述的SQL語句,你可以繞過這兩個問題。例如,假設你想從一個表中刪除一個欄位。使用SELECT INTO 語句,你可以創建該表的一個拷貝,但不包含要刪除的欄位。這使你既刪除了該欄位,又保留了不想刪除的數據。如果你想改變一個欄位的數據類型,你可以創建一個包含正確數據類型欄位的新表。創建好該表後,你就可以結合使用UPDATE語句和SELECT 語句,把原來表中的所有數據拷貝到新表中。通過這種方法,你既可以修改表的結構,又能保存原有的數據。
『貳』 SQL語言包括哪三種類型,每種類型又包括哪些語句
SQL包含四種程序設計語言類別的語句:數據定義語言(DDL)、數據操作語言(DML)、數據控制語言(DCL)和事物控制語言(TCL)。
1、Data Definition Language(DDL) :CREATE DATABASE - 創建新資料庫 ;ALTER DATABASE - 修改資料庫;CREATE TABLE - 創建新表;ALTER TABLE - 變更(改變)資料庫表;DROP TABLE - 刪除表;CREATE INDEX - 創建索引(搜索鍵);DROP INDEX - 刪除索引。
2、Data Manipulation Language(DML) :SELECT - 從資料庫表中獲取數據;UPDATE - 更新資料庫表中的數據;DELETE - 從資料庫表中刪除數據;INSERT INTO - 向資料庫表中插入數據。
3、Data Control Language(DCL) :GRANT - 授權;REVOKE - 撤銷授權;DENY - 拒絕授權。
4、Transaction Control Language(TCL) :SAVEPOINT - 設置保存點;ROLLBACK - 回滾;COMMIT - 提交。
(2)sql聲明集合擴展閱讀:
SQL功能極強,但由於設計巧妙,語言十分簡潔,完成數據定義、數據操縱、數據控制的核心功能只用了9個動詞: CREATE、 ALTER、DROP、 SELECT、 INSERT、 UPDATE、 DELETE、GRANT、 REVOKE。且SQL語言語法簡單,接近英語口語,因此容易學習,也容易使用。
SQL採用集合操作方式,不僅查找結果可以是元組的集合,而且一次插入、刪除、更新操作的對象也可以是元組的集合。
『叄』 關於SQL中的集合查詢
比較兩個查詢的結果,返回非重復值。
EXCEPT 從左查詢中返回右查詢沒有找到的所有非重復值。
INTERSECT 返回 INTERSECT 操作數左右兩邊的兩個查詢都返回的所有非重復值。
以下是將使用 EXCEPT 或 INTERSECT 的兩個查詢的結果集組合起來的基本規則:
所有查詢中的列數和列的順序必須相同。
數據類型必須兼容。
Transact-SQL 語法約定
語法
{ <query_specification> | ( <query_expression> ) }
{ EXCEPT | INTERSECT }
{ <query_specification> | ( <query_expression> ) }
參數
<query_specification> | ( <query_expression> )
查詢規范或查詢表達式返回與來自另一個查詢規范或查詢表達式的數據相比較的數據。在 EXCEPT 或 INTERSECT 運算中,列的定義可以不同,但它們必須在隱式轉換後進行比較。如果數據類型不同,則用於執行比較並返回結果的類型是基於數據類型優先順序的規則確定的。
如果類型相同,但精度、小數位數或長度不同,則根據用於合並表達式的相同規則來確定結果。有關詳細信息,請參閱 精度、小數位數和長度 (Transact-SQL)。
查詢規范或表達式不能返回 xml、text、ntext、image 或非二進制 CLR 用戶定義類型列,因為這些數據類型不可比較。
EXCEPT
從 EXCEPT 操作數左邊的查詢中返回右邊的查詢未返回的所有非重復值。
INTERSECT
返回 INTERSECT 操作數左右兩邊的兩個查詢均返回的所有非重復值。
備注
如果 EXCEPT 或 INTERSECT 操作數左邊和右邊的查詢返回的可比較列的數據類型是具有不同排序規則的字元數據類型,則根據排序規則優先順序的規則執行所需的比較。如果無法執行此轉換,Microsoft SQL Server 2005 資料庫引擎將返回錯誤。
通過比較行來確定非重復值時,兩個 NULL 值被視為相等。
EXCEPT 或 INTERSECT 返回的結果集的列名與操作數左側的查詢返回的列名相同。
ORDER BY 子句中的列名或別名必須引用左側查詢返回的列名。
EXCEPT 或 INTERSECT 返回的結果集中的任何列的為空性與操作數左側的查詢返回的對應列的為空性相同。
如果 EXCEPT 或 INTERSECT 與表達式中的其他運算符一起使用,則在以下優先順序的上下文中對其進行評估:
括弧中的表達式
INTERSECT 操作數
基於在表達式中的位置從左到右求值的 EXCEPT 和 UNION
如果 EXCEPT 或 INTERSECT 用於比較兩個以上的查詢集,則數據類型轉換是通過一次比較兩個查詢來確定的,並遵循前面提到的表達式求值規則。
EXCEPT 和 INTERSECT 不能在分布式分區視圖定義、查詢通知中使用,也不能與 COMPUTE 和 COMPUTE BY 子句一起使用。
EXCEPT 和 INTERSECT 可在分布式查詢中使用,但只在本地伺服器上執行,不會被推送到鏈接伺服器。因此,在分布式查詢中使用 EXCEPT 和 INTERSECT 可能會影響性能。
快速只進游標和靜態游標與 EXCEPT 或 INTERSECT 運算一起使用時,在結果集中完全受支持。如果由鍵集驅動的游標或動態游標與 EXCEPT 或 INTERSECT 運算一起使用,則運算的結果集的游標轉換為靜態游標。
使用 SQL Server Management Studio 中的圖形顯示計劃功能顯示 EXCEPT 運算時,該運算顯示為 left anti semi join,INTERSECT 運算顯示為 left semi join。
『肆』 SQL 存儲過程建立和使用方法
Sql Server的存儲過程是一個被命名的存儲在伺服器上的Transacation-Sql語句集合,是封裝重復性工作的一種方法,它支持用戶聲明的變數、條件執行和其他強大的編程功能。 存儲過程相對於其他的資料庫訪問方法有以下的優點: (1)重復使用。存儲過程可以重復使用,從而可以減少資料庫開發人員的工作量。 (2)提高性能。存儲過程在創建的時候就進行了編譯,將來使用的時候不用再重新編譯。一般的SQL語句每執行一次就需要編譯一次,所以使用存儲過程提高了效率。 (3)減少網路流量。存儲過程位於伺服器上,調用的時候只需要傳遞存儲過程的名稱以及參數就可以了,因此降低了網路傳輸的數據量。 (4)安全性。參數化的存儲過程可以防止SQL注入式的攻擊,而且可以將Grant、Deny以及Revoke許可權應用於存儲過程。 存儲過程一共分為了三類:用戶定義的存儲過程、擴展存儲過程以及系統存儲過程。 其中,用戶定義的存儲過程又分為Transaction-SQL和CLR兩種類型。 Transaction-SQL 存儲過程是指保存的Transaction-SQL語句集合,可以接受和返回用戶提供的參數。 CLR存儲過程是指對.Net Framework公共語言運行時(CLR)方法的引用,可以接受和返回用戶提供的參數。他們在.Net Framework程序集中是作為類的公共靜態方法實現的。(本文就不作介紹了) 創建存儲過程的語句如下:Code
CREATE { PROC | PROCEDURE } [schema_name.] procere_name [ ; number ]
[ { @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ [ OUT [ PUT ]
] [ ,n ]
[ WITH <procere_option> [ ,n ]
[ FOR REPLICATION ]
AS { <sql_statement> [;][ n ] | <method_specifier> }
[;]
<procere_option> ::=
[ ENCRYPTION ]
[ RECOMPILE ]
[ EXECUTE_AS_Clause ]
<sql_statement> ::=
{ [ BEGIN ] statements [ END ] }
<method_specifier> ::=
EXTERNAL NAME assembly_name.class_name.method_name [schema_name]: 代表的是存儲過程所屬的架構的名稱 例如: Create Schema yangyang8848
Go
Create Proc yangyang8848.AllGoods
As Select * From Master_Goods
Go 執行:Exec AllGoods 發生錯誤。 執行:Exec yangyang8848.AllGoods 正確執行。 [;Number]: 用於對同名過程進行分組的可選整數。使用一個 DROP PROCEDURE 語句可將這些分組過程一起刪除。 例如: Create Proc S1 ;1
AS
Select * From Master_Goods
Go
Create Proc S1 ;2
As
Select * From Master_Location
Go 創建完畢了兩個存儲過程。它們在同一個組S1里,如果執行Exec S1 則存儲過程默認執行 Exec S1 ;1 。如果我們想得到所有據點信息則需要執行Exec S1 ;2。當我們要刪除存儲過程的時候,只能執行Drop Exec S1 則該組內所有的存儲過程被刪除。 [@ parameter]: 存儲過程中的參數,除非將參數定義的時候有默認值或者將參數設置為等於另一個參數,否則用戶必須在調用存儲過程的時候為參數賦值。 存儲過程最多有2100個參數。 例如: Create Proc yangyang8848.OneGoods
@GoodsCode varchar(10)
As
Select * From Master_Goods Where GoodsCode = @GoodsCode
Go 調用的代碼: Declare @Code varchar(10)
Set @Code = '0004'
Exec yangyang8848.OneGoods @Code 在參數的後邊加入Output 表明該參數為輸出參數。 Create Proc yangyang8848.OneGoods
@GoodsCode2 varchar(10) output,@GoodsCode varchar(10) = '0011'
As
Select * From Master_Goods Where GoodsCode = @GoodsCode
Set @GoodsCode2 = '0005'
Go 調用方法:
Declare @VV2 varchar(10)
Exec yangyang8848.OneGoods @Code out 注意:如果存儲過程的兩個參數一個有默認值一個沒有,那麼我們要把有默認值得放在後邊,不然會出問題哦~~ 細心的朋友,可能看到上邊的語句有一些不同,比如,存儲過程用的是output,而調用語句用的是out。我要告訴您,兩者是一樣的。 [RECOMPILE]:指示資料庫引擎 不緩存該過程的計劃,該過程在運行時編譯。如果指定了 FOR REPLICATION,則不能使用此選項。對於 CLR 存儲過程,不能指定 RECOMPILE。 這個說一個非常好用的函數 OBJECT_ID :返回架構范圍內對象的資料庫對象標識號。 例如:我們創建存儲過程時,可以如下寫代碼 If Object_ID('yangyang8848.OneGoods') Is Not Null
Drop Proc yangyang8848.OneGoods
Go Create Proc yangyang8848.OneGoods
@GoodsCode2 varchar(10) out,@GoodsCode varchar(10) = '0011'
As
Select * From Master_Goods Where GoodsCode = @GoodsCode
Set @GoodsCode2 = '0005'
Go 針對於上邊的這個存儲過程,我們調用以下SQL查詢 Select definition From sys.sql_moles
Where object_id = Object_ID('yangyang8848.OneGoods'); 我們是可以查到結果的。 可是如果我們對該存儲過程加入[ ENCRYPTION ] 那麼你將無法看到任何結果 If Object_ID('yangyang8848.OneGoods') Is Not Null
Drop Proc yangyang8848.OneGoods
Go Create Proc yangyang8848.OneGoods
@GoodsCode2 varchar(10) out,@GoodsCode varchar(10) = '0011' With Encryption
As
Select * From Master_Goods Where GoodsCode = @GoodsCode
Set @GoodsCode2 = '0005'
Go</SPAN> 然後我們查詢 sys.sql_moles 目錄視圖,將返回給你Null。</p> 然後我們執行以下SQL: Exec sp_helptext 'yangyang8848.OneGoods' 你將得到以下結果:The text for object 'yangyang8848.OneGoods' is encrypted. 說到這里你應該明白了,參數[ ENCRYPTION ]:是一種加密的功能, 將 CREATE PROCEDURE 語句的原始文本轉換為模糊格式。模糊代碼的輸出在 SQL Server 2005 的任何目錄視圖中都不能直接顯示。對系統表或資料庫文件沒有訪問許可權的用戶不能檢索模糊文本。但是,可通過 DAC 埠訪問系統表的特權用戶或直接訪問資料庫文件的特權用戶可使用此文本。此外,能夠向伺服器進程附加調試器的用戶可在運行時從內存中檢索已解密的過程。 前兩天寫了一篇關於游標的介紹文章 ,下邊寫一個例子,將游標與存儲過程一起使用上: If Object_ID('dbo.GetMasterGoods') Is Not Null
Drop Proc dbo.GetMasterGoods
Go Create Proc GetMasterGoods
@MyCursor Cursor Varying Output
With Encryption
As
Set @MyCursor = Cursor
For
Select GoodsCode,GoodsName From Master_Goods
Open @MyCursor
Go --下邊建立另外一個存儲過程,用於遍歷游標輸出結果 Create Proc GetAllGoodsIDAndName
As Declare @GoodsCode varchar(18)
Declare @GoodsName nvarchar(20)
Declare @MasterGoodsCursor Cursor
Exec GetMasterGoods @MasterGoodsCursor out
Fetch Next From @MasterGoodsCursor
InTo @GoodsCode,@GoodsName
While(@@Fetch_Status = 0)
Begin
Begin
Print @GoodsCode + ':' + @GoodsName
End
Fetch Next From @MasterGoodsCursor
InTo @GoodsCode,@GoodsName
End
Close @MasterGoodsCursor
Deallocate @MasterGoodsCursor
Go 最後執行Exec GetAllGoodsIDAndName結果為以下內容 0003:品0003
0004:品0004
0005:123123
0006:品0006
0007:品0007
0008:品0008
0009:品0009
0010:品0010
0011:品0011
0012:品0012
0013:品0013
0014:品0014
『伍』 sql 存儲過程可以存儲集合嗎
SQL Server 通過 RETURN TABLE 來實現。
1> CREATE FUNCTION getHelloWorld()
2> RETURNS TABLE
3> AS
4> RETURN
5> SELECT 'Hello' AS A, 'World' AS B;
6> GO
1> SELECT * FROM getHelloWorld();
2> go
A B