一：使用儲存過程：
1、SQL批處理語句:可以一次執行多個語句
select au_lname from authors
select au_fname from authors
如果兩個語句中有一個出錯，則都不會執行。
你可以使用:
select au_lname from authors
go
select au_fname from authors
如果第二個語句錯誤，第一個照樣執行。
2、變數:全域性變數，區域性變數
全域性變數:全域性變數的個數是有限的，你不能自己建立全域性變數。@@identity和@@rowcount
@@identity記錄最後一次插入identity列的值
insert authors(au_lname) values ('xmlf')
select @@identity /*返回標識列的值*/
第二個重要的全域性變數是@@ROWCOUNT,@@ROWCOUNT變數記錄最近一個語句執行時所影響到的列的數目。
如:
update authors set au_lname='xmlf' where au_fname='xmlf'
select @@rowcount
返回au_fname是xmlf的記錄的數目
select * from authors where 1=2
select @@rowcount
返回0，因為不可能有1=2
區域性變數:你可以對區域性變數定義，修改，賦值操作。
不過首先你要先宣告才能使用:
declare @myvar int
select @myvar=3
select @myvar
最後一個返回變數@myvar的值
declare @firstname varchar(20),@lastname varchar(20)
select @firstname="xxit"
select @lastname="xmlf"
select @firstname=@lastname
select @firstname
你也可以把執行sql語句的結果賦給區域性變數，例如:
declare @query varchar(20)
select @query=au_lname from authors where password=111
select @query
假如一個查詢沒有返回值，該變數保留它原先的值。當變數第一次定義時，該變數的值為NULL
declare @query int
select @query=12
select @query=author_id from authors where 1=2
select @query
返回的值是12
3、在螢幕上顯示資料:print
declare @myvar int
select @myvar=12
print @myvar
4、註釋符 /* */ 這個沒什麼好說的，和C語言一樣
5、條件控制語句 if語句
if (select count(*) from authors)>10 print "more than 10 authors"
如果表authors中的記錄數大於10，則列印出more than 10 authors這個語句
if datename(mm,getdate())='July'
begin
print "this is July"
print "happy July"
end
if datename(mm,getdate())='July'
begin
print "this is July"
print "happy July"
if datename(dd,getdate())=4
begin
print "this is 4th"
print "happy 4th of July"
end
end
if datename(dw,getdate())='Friday'
print "this is Friday"
else
print "this is not Friday"
關鍵字exists判斷是否返回了結果
如:
if exists(select au_lname from authors where au_lname="xmlf")
print "xmlf is an author"
else
print "xmlf is not an auhtor"
6、選擇語句:case 語句
select
(case
when siteurl like "%edu" then "Educational"
when siteurl like "%gov" then "Gonvment"
when siteurl like "%com" then "Commercial"
else "Other"
end
) "type",sitename "name",siteurl "url" from site_dir
select
(case sitename
when "yahoo" then "Internet Directory"
when "Micorsoft" then "Software Giant"
else "Other"
end
) "Type",siteurl "url" from site_dir
7、使用return語句返回結果退出批處理
如:
if datename(dw,getdate())='Sunday'
begin
print "this is Sunday"
return
end
print "this is other day"
分析:如果上面語句不加return語句，則每天(包括Sunday)都會執行最後一個語句,而加了一個return語句後，在Sunday那天，遇到return語句後，就退出批處理而返回結果了，不會執行最後一個語句.
8、把語句組加入到事務組中:意思是這組語句要麼全部執行，要麼全部不執行.
例如:
begin transaction
insert user(username,password) values ('xmlf',123456)
INSERT orders(username) VALUES (‘Andrew Jones’)
commit transaction
這組語句只有當執行完commit transaction後才起作用。
begin transaction
insert user(username,password) values ('xmlf',123456)
INSERT orders(username) VALUES (‘Andrew Jones’)
if datename(dw,getdate())='Friday'
rollback transaction
else
commit transaction
上面語句只有在不是星期五執行，如果是星期五則不會執行。
9、儲存過程:把一組複雜的SQL語句儲存在儲存過程中。
儲存過程相對於批處理優點：
處理速度快，因為批處理每次執行都要首先進行編譯；而儲存過程只需要在第一次執行的時候進行編譯。可以非常靈活控制儲存過程，可以對儲存過程輸入輸出值。當使用批處理的時候你需要傳遞每一個語句，而使用儲存過程，你只需要傳遞一個簡單的語句；而且你還可以在一個儲存過程中包含另外一個儲存過程。這樣可以建立很複雜的儲存過程。
有了以上優點，你沒有理由不使用儲存過程。
建立儲存過程:
create procedure retrieve_authors as select * from authors
這樣就建立了一個儲存過程，儲存過程名retrieve_authors 儲存過程中的SQL語句是AS後面的SQL語句，當執行這個儲存過程，返回的是authors表中的所有記錄。
執行一個儲存過程:execute retrieve_authors
如果你想在B資料庫中呼叫A資料庫中的儲存過程，可以使用:execute a..retrieve_authors
你還可以使用sp_helptext來檢視儲存過程中的SQL語句，例如:sp_helptext retrieve_authors
將顯示整個儲存過程的定義:
create procedure retrieve_authors 。。。
select * from authors
有趣的是sp_helptext本身就是個系統儲存過程，你可以使用sp_helptext sp_helptext來檢視sp_helptext內容.
一旦你建立了個儲存過程，你不能修改它，如果要修改它必須先破壞它，然後重建。
你可以使用Drop procedure 儲存過程名 來刪除儲存過程。
例如:drop procedure retrieve_authors
你可以使用sp_help 來檢視資料庫中的所有儲存過程列表，sp_help 儲存過程名 檢視指定的儲存過程的資訊。
給儲存過程傳值:
create procedure check (@firstname varchar(30),@lastname varchar(30))
as
if exists(select name from authors where name=@firstname or name=@lastname)
print "he is an auhtors"
else
print "he is not an authors"
當執行這個儲存過程是賦值:
execute check @firstname="xmlf",@lastname="xxit"
這樣就可以檢測出xmlf或xxit中是否至少有一位是authors.
從儲存過程中獲得值:
create procedure check_philosophers (@philosophers varchar(30),@conculsion varchar(30) output)
as
if exists(select name from authors where name=@philosophers)
select @conculsion="a philosopher"
else
select @conculsion="not a philosopher"
執行這個儲存過程之前，你必須先定義一個變數來接受變數@conclusion的值。
declare @proc_results varchar(30)
execute check_philosophers @philosophers="xmlf",@conculsion=@proc_results output
print @proc_results
注意在該EXECUTE語句中，引數的名稱總是列在前面。你要使用@conclusion=@proc_results來接收引數@conclusion的值，而不是你可能期望的@proc_results=@conclusion。
在儲存過程中使用Return語句:
create procedure check_table (@who varchar(30))
as
if exists(select name from teachers where name=@who)
begin
print "in the teachers table"
return
end
if exists(select name from authors where name=@who)
begin
print "in the authors table"
return
end
print "not in any table"
return
執行這個儲存過程。
execute check_table @who="xmlf"
你還可以使用return語句返回狀態值，例如:
create procedure check_table (@who varchar(30))
as
if exists(select name from teachers where name=@who)
begin
return(1)
end
if exits(select name from authors where name=@who)
begin
return(2)
end
return(3)
這樣你就可以在ASP中通過這些狀態值來判斷所給的值存在在哪個表了。
執行這個儲存過程並顯示出返回的狀態值:
declare @myvar int
execute @myvar=check_table @who="xmlf" /*將儲存過程的返回值賦給了定義的變數@myvar*/
select @myvar /*顯示出返回值*/
二：使用觸發器：
建立觸發器:create trigger user_insert on user for insert as execute master..xp_sendmail "administrator" "New user registered!"
create trigger 觸發器名 on 表名 for 觸發的動作 as 觸發後執行的語句
觸發的動作你也使用:insert,update,delete
在本例中，當有新的資料插入到表user中，會觸發執行傳送email資訊.
記住:觸發器和表是相關聯的，當表被刪除了，觸發器也相應被刪除。在一個表上，你只可以為每個動作建立一個觸發器，不允許在多個觸發器上使用相同的動作。
為一個表最多可以建立三個觸發器，分別是insert,update,delete。
當你新增具有相同動作的第二個觸發器時，會在沒有任何通知的情況下刪除第一個觸發器。
你同樣可以使用sp_help檢視資料庫中的所有儲存過程，觸發器和表。如果只想顯示觸發器的資訊，使用sp_help 觸發器名
建立與多個動作關聯的觸發器：
create trigger username_trigger on users for insert,delete,update as 所要執行的SQL語句
觸發器只有在其FOR短語後面指定的動作發生時執行。你不能直接執行一個觸發器。
刪除觸發器:drop trigger 觸發器名
兩個特殊的表:inserted和deleted
這兩個表僅僅在觸發器被執行的時候存在。
create trigger user_trigger on user for delete as insert user_log(activity) select username from deleted
假使你不小心執行delete user,這樣會刪除表user中的所有記錄。這樣這些記錄會永久的丟失，可是上面的觸發器會在記錄被刪除時觸發，自動將被刪除的username表中的記錄拷貝到user_log表中。
Deleted表和有記錄被刪除的表的列結構一模一樣。在前面的例子內，Deleted表具有和webusers表相同的結構。
同樣，當在表user中插入一條新記錄時並做備份，你可以使用:
create trigger user_trigger on user for insert as insert user_log(activity) select username from inserted
當表user中有新紀錄插入時，會自動執行觸發器user_trigger，並將新記錄從inserted拷貝到user_log中。
你同樣可以使用inserted和deleted來記錄update對觸發器所在的表的動作。當一個和觸發器相關聯的表中資料被修改時，deleted包含被修改前的值，inserted包含了被修改後的值。
記住:inserted和deleted表僅僅在觸發器執行時存在，當觸發器執行結束，這兩個表中的記錄也不復存在。
觸發器和事務的結合:
create trigger user_trigger on users for insert,update,delete as
if datename(dw,getdate())="Sunday"
rollback transaction
該觸發器阻止任何人在Sunday插入，修改或刪除users表中的記錄。
create trigger user_trigger on users for insert,update,delete as
if exists(select username from inserted where username="abc")
rollback transaction
該觸發器阻止使用者名稱為abc的人註冊。當使用者名稱為abc包含在某個insert,update,delete語句中時，就會被rollback回滾回去，阻止執行。