Sql Server專題二：資料庫主要對象

標籤：

預存程序

(1)減少網路通訊量。調用一個行數不多的預存程序與直接調用sql語句的網路通訊量可能不會有很大的差別，可是如果預存程序包含上百行sql語句，那麼其效能絕對比一條一條的調用sql語句要高得多。

(2)執行速度更快。有兩個原因：首先，在預存程序建立的時候，資料庫已經對其進行了一次解析和最佳化。其次，預存程序一旦執行，在記憶體中就會保留一份這個預存程序，這樣下次再執行同樣的預存程序時，可以從記憶體中直接調用。

(3)更強的適應性：由於預存程序對資料庫的訪問是通過預存程序來進行的，因此資料庫開發人員可以在不改動預存程序介面的情況下對資料庫進行任何改動，而這些改動不會對應用程式造成影響。

(4) 布式工作：應用程式和資料庫的編碼工作可以分別獨立進行，而不會相互壓制。

預存程序可以用暫存資料表,函數不能用暫存資料表

預存程序可以使用Update,函數不能使用Update

預存程序可以用Getdate()等函數,函數不能使用Getdate()這些函數

一、使用預存程序要注意一些問題

1、在as後面立即跟上一個 set nocount on子句，這樣會減少很大開銷(相對於過程中存在DML操作的時候)

2、建立和引用過程的時候加上架構名

3、避免在返回許多行資料的 SELECT 語句中使用純量涵式(getdate())。因為純量涵式必須應用於每一行。

4、避免使用 SELECT *。而是應指定所需的列名稱。

5、避免處理或返回”過多”的資料，一些沒必要的資料就不要了

6、通過使用 BEGIN/END TRANSACTION 來使用明確交易並且保留儘可能短的事務。更長的事務意味著更長的記錄鎖定和更高的死結風險

7、在 LIKE 子句中避免使用萬用字元作為前置字元，例如 LIKE ‘%a%’。因為第一個字元是不確定的，所以，查詢處理器無法使用可用的索引。應改用 LIKE ‘a%’。

8、使用 Transact-SQL TRY…CATCH 功能進行過程內的錯誤處理

9、建立/修改Table時給列一個Default值

10、使用 IF EXISTS (SELECT TOP 1 FROM table_name) 來代替 IF EXISTS (SELECT * FROM table_name)。

11、使用 UNION ALL 運算子來代替 UNION 或 OR 運算子

12、建立預存程序時在其定義中指定 WITH  RECOMPILE 選項，表明 SQL  Server 將不對該預存程序計划進行快取；該預存程序將在每次執行時都重新編譯。

當預存程序的參數值在各次執行間都有較大差異(個人覺得應該是動態建立預存程序時，使得很可能有時候參數不同)，導致每次均需建立不同的執行計畫時，可使用   WITH RECOMPILE選項。此選項並不常用，因為每次執行預存程序時都必須對其進行重新編譯，這樣會使預存程序的執行變慢，

二、知識點：

1、SQL Server 啟動時可以自動執行一個或多個過程。這些過程必須由系統管理員在 master 資料庫中建立，並以 sysadmin 固定伺服器角色作為後台進程執行。這些過程不能有任何輸入或輸出參數。

2、過程可以嵌套，並且最多可以嵌套 32 級。

3、過程可以引用尚不存在的表。在建立時，只進行語法檢查。直到第一次執行該過程時才對其進行編譯。只有在編譯過程中才解析過程中引用的所有對象。

加密預存程序

Create procedure FirstP

WITH ENCRYPTION

as

   set nocount on

   select * from C

go

EXEC sp_helptext ‘dbo.FirstP‘

預存程序實現遞迴

select * from tb

order by case when ISNUMERIC(col) =1 then ‘‘ else left(col,1) end,

cast(case when ISNUMERIC(col) =0 then stuff(col,1,1,‘‘) else col end  as int)

drop table dbo.Example

create table Example(ID int,ParentID int)

 

insert Example

select 0,null union

select 1,0 union

select 2,0 union

select 3,1 union

select 4,6 union

select 5,2

--取其某個ID為父節點的樹結構的SQL如下：

create procedure display(@ID int) as

begin

WITH Example_Table(ID,ParentID,lv)

AS

(

        --取根節點放入暫存資料表

        SELECT ID,ParentID,0 FROM Example WHERE [email protected]

        --根據已取到的資料遞迴取其位元組點的資料

        UNION ALL

        SELECT A.ID,A.ParentID,B.lv+1 FROM Example A INNER JOIN Example_Table B ON A.ParentID = B.ID

)

SELECT * FROM Example_Table

end

drop table tb

go

 

自訂函數

使用者定義自訂函數像內建函數一樣返回標量值，也可以將結果集用表格變數返回

使用者自訂函數的類型:

純量涵式:返回一個標量值

表格值函數{內聯表格值函數、多表格值函數}:返回行集（即返回多個值）

1、純量涵式

Create function 函數名（參數）

Returns 傳回值資料類型

[with {Encryption | Schemabinding }]

[as]

begin

SQL語句(必須有return 變數或值)

End

Schemabinding :將函數綁定到它引用的對象上（註：函數一旦綁定，則不能刪除、修改，除非刪除綁定）

Create function AvgResult(@scode varchar(10))

Returns real

As

Begin

   Declare @avg real

   Declare @code varchar(11)

   Set @[email protected] + ‘%’

   Select @avg=avg(result) from LearnResult_baijiali

Where scode like @code

Return @avg

End

執行使用者自訂函數

select 使用者名稱。函數名 as 欄位別名

select dbo.AvgResult(‘s0002’) as result

使用者自訂函數傳回值可放到局部變數中，用set ,select,exec賦值

declare @avg1 real ,@avg2 real ,@avg3 real

select @avg1= dbo.AvgResult(‘s0002’)

set @avg2= dbo.AvgResult(‘s0002’)

exec @avg3= dbo.AvgResult ‘s0002’

select @avg1 as avg1 ,@avg2 as avg2 ,@avg3 as avg3

 

函數引用

create function code(@scode varchar(10))

returns varchar(10)

as

begin

declare @ccode varchar(10)

set @scode = @scode + ‘%’

select @ccode=ccode from cmessage

   where ccode like @scode

return @ccode

end

select name from class where ccode = dbo.code(‘c001’)

 

2、表格值函數

a、 內聯表格值函數

格式：

create function 函數名（參數）

returns table

[with {Encryption | Schemabinding }]

as

return(一條SQL語句)

 

create function tabcmess(@code varchar(10))

returns table

as

return(select ccode,scode from cmessage where ccode like @ccode)

b、 多句表格值函數

   create function 函數名（參數）

   returns 表格變數名table (表格變數定義)

   [with {Encryption | Schemabinding }]

as

   begin

    SQL語句

   end 

 多句表格值函數包含多條SQL語句，至少有一條在表格變數中填上資料值

 表格變數格式

returns @變數名 table (column 定義| 約束定義 [,…])

對錶格變數中的行可執行select,insert,update,delete ， 但select into 和 insert 語句的結果集是從預存程序插入。 

Create function tabcmessalot (@code varchar(10))

Returns @ctable table(code varchar(10) null,cname varchar(100) null)

As

Begin

Insert @ctable

Select ccode,explain from cmessage

Where scode like @code

return

End

Select * from tabcmessalot(‘s0003’)

觸發器

一: 觸發器是一種特殊的預存程序﹐它不能被顯式地調用﹐而是在往表中插入記錄﹑更新記錄或者刪除記錄時被自動地啟用。所以觸發器可以用來實現對錶實施複雜的完整性條件約束。  

二: SQL Server為每個觸發器都建立了兩個專用表:Inserted表和Deleted表。這兩個表由系統來維護﹐它們存在於記憶體中而不是在資料庫中。這兩個表的結構總是與被該觸發器作用的表的結構相同。觸發器執行 完成後﹐與該觸發器相關的這兩個表也被刪除。 
Deleted表存放由於執行Delete或Update語句而要從表中刪除的所有行。 
Inserted表存放由於執行Insert或Update語句而要向表中插入的所有行。 
三:Instead of 和 After觸發器 
SQL Server2000提供了兩種觸發器:Instead of 和After 觸發器。這兩種觸發器的差別在於他們被啟用的同: 
Instead of觸發器用於替代引起觸發器執行的T-SQL語句。除表之外﹐Instead of 觸發器也可以用於視圖﹐用來擴充視圖可以支援的更新操作。 
After觸發器在一個Insert,Update或Deleted語句之後執行﹐進行約束檢查等動作都在After觸發器被啟用之前發生。After觸發器只能用於表。 
一個表或視圖的每一個修改動作(insert,update和delete)都可以有一個instead of 觸發器﹐一個表的每個修改動作都可以有多個After觸發器。 
四:觸發器的執行過程 
如果一個Insert﹑update或者delete語句違反了約束﹐那么After觸發器不會執行﹐因為對約束的檢查是在After觸發器被激動之前發生的。所以After觸發器不能超越約束。 
Instead of 觸發器可以取代激發它的操作來執行。它在Inserted表和Deleted表剛剛建立﹐其它任何操作還沒有發生時被執行。因為Instead of 觸發器在約束之前執行﹐所以它可以對約束進行一些預先處理。 
五:使用T-SQL語句來建立觸發器 
基本語句如下: 
create trigger trigger_name 
on {table_name | view_name} 
{for | After | Instead of } 
[ insert, update,delete ] 
as 
sql_statement 
六:刪除觸發器： 
基本語句如下: 
drop trigger trigger_name 
七：查看資料庫中已有觸發器： 
-- 查看資料庫已有觸發器 
use jxcSoftware 
go 
select * from sysobjects where xtype=‘TR‘ 
-- 查看單個觸發器 
exec sp_helptext ‘觸發器名‘ 
八:修改觸發器： 
基本語句如下: 
alter trigger trigger_name 
on {table_name | view_name} 
{for | After | Instead of } 
[ insert, update,delete ] 
as 
sql_statement 
九:相關樣本: 
1:在Orders表中建立觸發器﹐當向Orders表中插入一條訂單記錄時﹐檢查goods表的貨品狀態status是否為1(正在整理)﹐是﹐則不能往Orders表加入該訂單。 
create trigger orderinsert 
on orders 
after insert 
as 
if (select status from goods,inserted 
where goods.name=inserted.goodsname)=1 
begin 
print ‘the goods is being processed‘ 
print ‘the order cannot be committed‘ 
rollback transaction --復原﹐避免加入 
end 
2:在Orders表建立一個插入觸發器﹐在添加一條訂單時﹐減少Goods表相應的貨品記錄中的庫存。 
create trigger orderinsert1 
on orders 
after insert 
as 
update goods set storage=storage-inserted.quantity 
from goods,inserted 
where 
goods.name=inserted.goodsname 
3:在Goods表建立刪除觸發器﹐實現Goods表和Orders表的串聯刪除。 
create trigger goodsdelete 
on goods 
after delete 
as 
delete from orders 
where goodsname in 
(select name from deleted) 
4:在Orders表建立一個更新觸發器﹐監視Orders表的訂單日期(OrderDate)列﹐使其不能手工修改. 
create trigger orderdateupdate 
on orders 
after update 
as 
if update(orderdate) 
begin 
raiserror(‘ orderdate cannot be modified‘,10,1) 
rollback transaction 
end 
5:在Orders表建立一個插入觸發器﹐保證向Orders表插入的貨品名必須要在Goods表中一定存在。 
create trigger orderinsert3 
on orders 
after insert 
as 
if (select count(*) from goods,inserted wheregoods.name=inserted.goodsname)=0 
begin 
print ‘ no entry in goods for this order‘ 
rollback transaction 
end 
6：Orders表建立一個插入觸發器，保證向Orders表插入的貨品資訊要在Order表中添加 
alter trigger addOrder 
on Orders 
for insert 
as 
insert into Order 
select inserted.Id, inserted.goodName,inserted.Number from inserted

遊標

SQl Server的遊標在你需要對記錄集進行單條處理時很有用處。

select返回一個記錄集，但是你想根據每條記錄的不同情況進行單條處理，這時遊標的用處就顯出來了。

一個遊標(cursor)可以被看作指向結果集(aset of rows)中一行的指標(pointer)。遊標每個時間點只能指向一行，但是可以根據需要指向結果集中其他的行。

例如：SELECT* FROM employees WHERE sex=‘M‘會返回所有性別為男的僱員，在初始的時候，遊標被放置在結果集中第一行的前面。使遊標指向第一行，要執行FETCH。當遊標指向結果集中一行的時候，可以對這行資料進行加工處理，要想得到下一行資料，要繼續執行FETCH。FETCH操作可以重複執行，直到完成結果集中的所有行

在預存程序中使用遊標，有如下幾個步驟：

聲明遊標、開啟遊標、根據需要一次一行，講遊標指向的資料取到本地變數(local variables)中、結束時關閉遊標

建立遊標分五個步驟：

CREATE PROCEDURE [dbo].[Proc_Syn_Data]

AS

BEGIN

   SET NOCOUNT ON;

   DECLARE @ID INT;

   TRUNCATE TABLE Table2;

   -- 1.聲明遊標

   DECLARE CUR_MonthID CURSOR FOR

   SELECT ID FROM [Table1]

   -- 2.開啟遊標

   OPEN CUR_MonthID

   -- 3.從一個遊標中尋找資訊，實現自己的資料處理。

   FETCH CUR_MonthID INTO @ID

   WHILE @@FETCH_STATUS=0

   BEGIN

       INSERT INTO [Table2]

       SELECT * FROM [Table1] tm WHERE [email protected]

       FETCH NEXT FROM CUR_MonthID INTO @ID

   END;

   -- 4.關閉遊標

   CLOSE CUR_MonthID;

   -- 5.釋放遊標

   DEALLOCATE CUR_MonthID;

END

Sql Server專題二：資料庫主要對象