sql預存程序總結

1. Sql預存程序概述

在大型資料庫系統中，預存程序和觸發器具有重要的作用。無論是預存程序還是觸發器，都是SQL 語句和流程控制語句的集合。就本質而言，觸發器也是一種預存程序。預存程序在運算時產生執行方式，所以，以後對其再運行時其執行速度很快。 SQL Server 2000 不僅提供了使用者自訂預存程序的功能，也提供了許多可作為工具使用的系統預存程序

1.1概念

儲存程序（Stored Procedure）是一組為了完成特定功能的SQL語句集，經編譯後儲存於資料庫。使用者透過指定預存程序的名字並給出參數（如果該預存程序帶有參數）來執行它。

在SQL Server 的系列版本中預存程序分為兩類：系統提供的預存程序和使用者自訂預存程序。系統過程主要儲存在master 資料庫中並以sp_為前綴，且系統預存程序主要是從系統表中獲取信息，從而為系統管理員管理SQL Server 提供支援。透過系統預存程序，MS SQL Server 中的許多管理性或資訊性的活動（如了解資料庫物件、資料庫資訊）都可以順利有效地完成。儘管這些系統預存程序被放在master 資料庫中，但仍可以在其它資料庫中對其進行調用，在調用時不必在預存程序名前加上資料庫名稱。而且當建立一個新資料庫時，一些系統預存程序會在新資料庫中自動建立。使用者自訂預存程序是由使用者建立並能完成某一特定功能（如查詢使用者所需資料資訊）的預存程序。在本章中所涉及的預存程序主要是指使用者自訂預存程序。

1.2 預存程序優點

利用MS SQL Server 建立應用程式時，Transaction-SQL 是一種主要的程式語言。若運用Transaction-SQL 來進行編程，有兩種方法。一是，在本機上儲存Transaction- SQL 程序，並建立應用程式向SQL Server 傳送指令來處理結果。其二是，可以把部分用Transaction-SQL 編寫的程式作為預存程序儲存在SQL Server 中，並建立​​應用程式來呼叫儲存過程，對資料結果進行處理預存程序能夠透過接收參數向呼叫者傳回結果集，結果集的格式由呼叫者決定；傳回狀態值給呼叫者，指明呼叫是成功或是失敗；包括針對資料庫的操作語句，並且可以在一個預存過程中呼叫另一個預存程序。

我們通常更偏好於使用第二種方法，即在SQL Server 中使用預存程序而不是在客戶電腦上呼叫Transaction-SQL 編寫的一段程序，原因在於預存程序具有以下優點：
1） 預存程序允許標準組件式程式設計

預存程序在被建立以後可以在程式中被多次調用，而不必重新編寫該預存程序的SQL 語句。而且資料庫專業人員可隨時對預存程序進行修改，但對應用程式原始碼毫無影響（因為應用程式原始碼只包含預存程序的呼叫語句），從而大大提高了程式的可移植性。

（2） 預存程序能夠實現較快的執行速度

如果某一操作包含大量的Transaction-SQL 程式碼或分別被多次執行，那麼儲存程序要比批次的執行速度快很多。因為預存程序是預先編譯的，在首次運行預存程序時，查詢最佳化器對其進行分析、最佳化，並給出最終被存在系統表中的執行計劃。而批次的Transaction- SQL語句在每次執行時都要進行編譯和最佳化，因此速度相對要慢一些。

（3） 預存程序能夠減少網路流量

對於同一個針對資料資料庫物件的操作（如查詢、修改），如果此操作所涉及的 Transaction-SQL 語句被組織成一儲存過程，那麼當在當客戶電腦上呼叫該預存程序時，網路中傳送的只是該呼叫語句，否則將是多條SQL 語句，從而大大增加了網路流量，降低網路負載。

（4） 儲存程序可被視為一種安全機制來充分利用

系統管理員透過對執行某一預存程序的權限進行限制，從而能夠實現對應的資料存取權限的限制，避免非授權用戶對資料的訪問​​，確保資料的安全。 （我們將在14 章「SQLServer 的使用者與安全性管理」中對預存程序的此應用作更為明確的介紹）

注意：預存程序雖然既有參數又有回傳值，但是它與函數不同。預存程序的回傳值只是指明執行是否成功，而且它不能像函數一樣直接被調用，也就是在呼叫預存程序時，在預存程序名字前一定要有EXEC保留字。

2. 基本語法 2.1 建立預存程序

Create procedure sp_name //sp_name 自己給預存程序去的名稱

Begin

…

End

Create Proc.
GO



Eg:

-- 要建立預存程序的資料庫

Use Test

-- And

type='P')

-- 刪除預存程序

Drop Procedure dbo.csp_AddInfo

Go .csp_AddInfo

- - 儲存程序參數

@UserName varchar(16),

@Pwd varchar(50),

@Age smallint,

@*** varchar(6)

字句

元

insert into Uname (UserName,Pwd,Age,***)

values (@UserName,@Pwd,@Age,@***)

RETURN

-- 。執行預存程序

EXEC csp_AddInfo 'Junn.A','123456',20,'男'

新儲存程序的名稱。過程名必須符合識別碼規則，且對於資料庫及其擁有者必須唯一。

　　要建立局部暫存過程，可以在 procedure_name 前面加上一個編號符號 (#procedure_name)，要建立全域暫存過程，可以在 procedure_name 前面加上兩個編號符 (##procedure_name)。完整的名稱（包括 # 或 ##）不能超過 128 個字元。指定過程擁有者的名稱是可選的。

2.2 呼叫預存程序

Call procedure sp-name （）

注意：預存程序名稱後面必須加號，哪一種

USE AdventureWorks;

GO

IF OBJECT_ID ( 'Production.usp_GetList',921]P'F) GetList;

GO

CREATE PROCEDURE Production. usp_GetList

@product varchar(40)

, @maxprice money

, @compareprice money OUTPUT Product, p.ListPrice AS 'List Price'

FROM Production.Product p

JOIN Production.ProductSubcategory s

ON p.ProductSubcategoryID = s.ProductSubcategoryID.ProductSubcategoryID = s.ProductSubcategoryID.ProductSubcategoryID = s.ProductSubcategoryID.ProductSubcategoryID  @maxprice;

-- Populate the output variable @listprice .

SET @listprice = (SELECT MAX(p.ListPrice)

FROM Production.Product p

JOIN Production.ProductSubcategory s

JOIN Production.ProductSubcategory s

JOIN Production.ProductSubcategory sProcrcat​​pryopcatr.

WHERE s.name LIKE @product AND p .ListPrice 

-- Populate the output variable @compareprice.

SET @compareprice = @maxprice;

GO

-

Create Proc Test

as

DECLARE @compareprice money, @cost money

EXECUTE Production.usp_Get？

IF @cost 

BEGIN

PRINT 'These products can be purchased for less than

$'+RTRIM(CAST(@compareprice AS varchar(20)))+'.',END

all products in this category exceed

$'+ RTRIM(CAST(@compareprice AS varchar(20)))+'.'

:創建第二種方法GetUserName

as

begin

select 'UserName'

end

Create table #tempTable (userName nvarchar(50))

insert into #tempTable(userName)

exec GetUserName

臨時表清空

drop table #tempTable--要注意的是，這種方法不能嵌套。例如：

... 

procedure a 
begin 

... 

insert #table exec b 
end 

procedure b 

begin 

.

procedure c 

begin

... 

select * from sometable 

end

--這裡ａ調ｂ的結果集，而ｂ中也有這樣的應用ｂ調了ｃ的結果集，這是不允許的，

--會報「 INSERT EXEC 語句不能嵌套」錯誤。在實際應用上要避免這類應用的發生。

3）第三種方法:宣告一個變數,用exec(@sql)執行:

2.3 刪除預存程序、

drop procedure sp_name//

注意：不能在一個儲存過程中刪除另一個儲存程序過程，只能調用另一個存儲過程

2.4 查看存儲過程信息

1.show procedure status
顯示數據庫中所有存儲的存儲過程基本信息，包括所屬數據庫，存儲過程名稱，創建時間等

2. show create procedure sp_name
顯示某一個mysql預存程序的詳細資料

2.5註解

mysql儲存過程可使用兩種風格的註解
雙橫槓：--

mysql儲存過程可使用兩種風格的註解
雙橫槓：--

風格風格用於單行註解樣式：/* 註釋內容 */ 一般用於多行註解

2.6循環語句 2.6.1 If

IF 條件

BEGIN

級執行語句

語END

語。

IF @d = 1

BEGIN

-- 打印

PRINT '正確'

END

ELSE BEGIN

PRINT '錯誤'

END


句
 裡的多重條件選擇語句.

DECLARE @iRet INT, @PKDisp VARCHAR(20)

SET @iRet = 1

Select @iRet = 

CAS二' THEN 2

WHEN @PKDisp = '三' THEN 3

WHEN @PKDisp = '四' THEN 4

WHEN @PKDisp = '五' THEN 5

ELSE 100

END

句




WHILE 條件 BEGIN 
執行語句
END




Eg: CREATE PROCEDURE 

SET @i = 1

WHILE @i set @i=@i+1

END

-- 印製

PRINT @i


.3.變數及定義

自訂變數：DECLARE a INT ; SET a=100; 可用以下語句取代：DECLARE a INT DEFAULT 100;

變數分為使用者變數和全域變數，又分為使用者變數和全域變數層級變數

使用者變數：使用者變數名稱一般以@開頭，濫用使用者變數會導致程式難以理解及管理

-- 變數的宣告,sql裡面宣告變數時必須在變數前加上@符號

DECLARE @I INT

-- 變數的賦值，變數賦值時變數前必須加上set

SET @I = 30

-- 宣告多個變數
DECLARE @s varchar(10),@a INT

3.2 .1算術運算子

+ 加 SET var1=2+2; 4

- 減 SET var2=3-2; 1

* 乘 SET var3=3*2; 6
/ 除 
DIV 整除 SET var5=10 DIV 3; 3

% 取模 SET var6=10%3 ; 1

3.2.2比較運算>= 大於等於 3>=2 TrueBETWEEN 在兩值之間 5 BETWEEN 1 AND 10 True

NOT BETWEEN 不在兩值之間 5 BETWEEN 1 AND 10 True
NOT BETWEEN 不在兩值之間集合中 5 IN (1,2,3,4) False
NOT IN 不在集合中 5 NOT IN (1,2,3,4) True
= 等於 2=3 False
, != 不等於 23 False
 嚴格比較兩個NULL值是否相等 NULLNULL True
LIKE 簡單模式匹配 "Guy Harrison" LIKE "Guy%" True
REGEXP 正則式匹配 "Guy Harrison" REGEXP "[Gg] reg" False
IS NULL 為空 0 IS NULL False
IS NOT NULL 不為空 0 IS NOT NULL True

3.邏輯運算子

4. 與位元運算
>> 右移位
~ 非(單目運算，按位取反)

4. 輸入輸出 （不詳）

mysql存儲過程的參數用在存儲過程的定義，共有三種參數類型, IN,OUT,INOUT

Create procedure|function([[IN |OUT |INOUT ] 參數名稱 資料類別...])

IN 輸入參數
表示此參數的值必須在呼叫儲存程序時指定，並在儲存過程中修改此參數的值不能被傳回，為預設值

OUT 輸出參數
該值可在儲存過程內部改變，並可傳回

INOUT 輸入輸出參數
呼叫時指定，並且可變更並傳回







--建立儲存程序求最大

CREATE PROCEDURE [dbo].[P_Max]

output --輸出

AS

if (@a>@b)

set @Returnc =@a

else

-- 呼叫

declare @Returnc int

exec P_Max 2,3,@Returnc output

select @Returncsql

my

函包括：字串類型，數值類型，日期類型

5.1字串類別

CHARSET(str) //傳回字符串字元集

CONCAT (string2 [,… ]) //連接字串

INSTR (string ,substring ) //傳回substring首次在string中出現的位置,不存在回傳0

LCASE (string2 ) //轉換成小寫

LEFT (string2 ,length ) //從string2中的左邊起取length個字元

LENGTH (string ) // string長度

LOAD_FILE (file_name ) //從檔案讀取內容
LOCATE (substring , string [,start_position ] ) 同INSTR,但可指定開始位置
LPAD (string2 ,length ,pad ) //重複使用pad開頭,直到字符串長度為length
LTRIM (string2 ) //移除前端空格
REPEAT (string2 ,count ) //重複count次
REPLACE (str ,search_str ,replace_str ) //在str中用replace_strsearch_str
RPAD (string2 ,length ,pad) //在str後用pad補充,直到長度為length
RTRIM (string2 ) //移除後端空格
STRCMP (string1 ,string2 ) //逐字比較兩字符串大小,
SUBSTRING (str , position [,length ]) //從str的position開始,取length個字元,
註：mysql處理字串時，預設第一個字元下標為1，即參數position必須大於等於1
mysql> select substring('abcd',0,2);
+———————–+
| substring('abcd',0,2) |
+———————–+
| |
+———————–+
1 row in set (0.00 sec)

mysql> select substring('abcd',1,2);
+———————–+
| substring('abcd',1,2) |
+———————–+
| ab |
+—————–+

1 row in set (0.02 sec)

TRIM ([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //移除指定位置的指定字元
UCASE (string2 ) //轉換成大寫
RIGHT(string2,length) //取string2最後length個字元
SPACE(count) //產生count個空格

5.2 數值型別

ABS (number2 ) //絕對值
BIN (decimal_number ) //十進位轉二進位
CEILING (number2 ) //向上取整CONV(number2,CONVo(number2, ) //向上取整數from_base,to_base) //進制轉換
FLOOR (number2 ) //向下取整

FORMAT (number,decimal_places ) //保留小數位數

HEX (DecimalNumber ) //轉十六進制

注：HEX( )中可傳入字串，則傳回其ASC-11碼，如HEX('DEF')傳回4142143

也可以傳入十進位整數，傳回其十六進位編碼，如HEX(25)回傳19
LEAST (number , number2 [,..]) //求最小值
MOD (numerator ,denominator ) //求餘
POWER (number ,power ) //求指數
RAND([seed]) //隨機數
ROUNDUND (number [,decimals ]) //四捨五入,decimals為小數位數]

註：傳回型別並非皆為整數，如：

(1)預設為整形值
mysql> select round(1.23);
+————-+
| round(1.23) |
+————-+

| 1 |

+————-+

1 row in set (0.00 sec)

mysql> select round( 1.56);
+————-+
| round(1.56) |
+————-+
| 2 |
+————-+
1 row in set (0.00 sec)

( 2)可以設定小數位數，傳回浮點型資料

mysql> select round(1.567,2);
+—————-+
| round(1.567,2) |
+———— —-+
| 1.57 |

+—————-+

1 row in set (0.00 sec)

SIGN (number2 ) //回傳符號,正負或0
SQRT(number2) //開平方

5.3日期類型

ADDTIME (date2 ,time_interval ) //將time_interval加到date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //轉換時區
CURRENT_DATE ( ) //目前日期
RRENT時間戳記
DATE (datetime ) //傳回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或時間
DATE_FORMAT (datetime ,FormatCodes ) //使用日期或時間
DATE_FORMATdatetime ,FormatCodes ) //使用formattime date2 , INTERVAL d_value d_type ) //在date2上減去一個時間
DATEDIFF (date1 ,date2 ) //兩個日期差
DAY (date ) //返回日期的天
DAYNAME (date ) //英文星期
DAEEKEEKEE (date ) //星期(1-7) ,1為星期日
DAYOFYEAR (date ) //一年中的第幾天
EXTRACT (interval_name FROM date ) //從date中提取日期的指定部分
MAKEDATE (year ,day ) //給出年及年中的第幾天,產生日期字串
MAKETIME (hour ,minute ,second ) //產生時間字串
MONTHNAME (date ) //英文月份名稱
NOW ( ) //目前時間
SEC_TO_TIME (seconds ) //秒數轉成時間
STR_TO_DATE (string ,format ) //字符串轉成時間,以format格式顯示
TIMEDIFF (datetime1 ,datetime2 ) //兩個時間差
TIME_TO_SEC (SEC TIME_TO_SEC ） /時間轉秒數]
WEEK (date_time [,start_of_week ]) //第幾週
YEAR (datetime ) //年份
DAYOFMONTH(datetime) //月的第幾天
HOUR(datetime) //小時
LAST_DAY (date) //date的月的最後日期
MICROSECOND(datetime) //微秒
MONTH(datetime) //月
MINUTE(datetime) //分

註：可用在INTERVAL中的類型：DAY ,DAY_HOUR ,DAY_MINUTE ,DAY_SECOND ,HOUR ,HOUR_MINUTE ,HOUR_SECOND ,MINUTE ,MINUTE_SECOND,MONTH ,SECOND ,YEAR

DECLARE variable_name [,variable_name...] datatype; FLOAT, DATE, VARCHAR(length)

例：

DECLARE l_int INT unsigned default 400000; 

DECLARE l_numeric NUMERIC(8,2) DEFAULT 9.95; 

DECLARE l_datetime DATETIME DEFAULT '1999-12-31 23:59:59';
DECLARE l_varchar VARCHAR(255) DEFAULT 'This will not be padded';

6. 
SQL預存程序進行分頁的方法: 

預存程序: 

CREATE procedure p_splitpage 

@sql nvarchar(4000), --要執行的sql語句 

@page int=1, --要顯示的頁碼 

@pageSize intint, -page -每頁的大小 

@pageCount int=0 out, --總頁數 
@recordCount int=0 out --總記錄數 
as 

set nocount on 

declare @p1 int

exec sp_curopenopen @psql. ,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output 

set @recordCount = @pageCount 

select @pagecount=ceiling(1.0*@pagecount/@pagesize) 

,@page=(@page-1) *@pagesize+1 
exec sp_cursorfetch @p1,16,@page,@pagesize 
exec sp_cursorclose @p1 
GO 
ASPASP頁面的內容 
sql = "Select id, c_Newss_ from
page = cint(page_get) 
if page_post""then 
page = cint(page_post) 
end if 
if not page > 0 then 
page = 1 
if not page > 0 then 
page = 1 
if not page > 0 then 
page = 1 
頁 1 set cmd = server.CreateObject("adodb.command") 
cmd.ActiveConnection = conn 
cmd.CommandType = 4 
cmd.CommandText = "p_SplitPage" 
cmd.Parameters.AppParameter,8.AppParameter 1, 4000, sql) 
cmd.Parameters.Append cmd.CreateParameter("@page",4,1, 4, page) 
cmd.Parameters.Append cmd.CreateParameter("@pageSize",44,1, 4,.Append cmd.CreateParameter("@pageSize",44,1, 4,, pageSize) 
cmd.Parameters.Append cmd.CreateParameter("@pageCount",4,2, 4, pageCount) 
cmd.Parameters.Append cmd.CreateParameter("@recordCount",4,2, 485) rs = cmd.Execute 
set rs = rs.NextRecordSet 
pageCount = cmd.Parameters("@pageCount").value 
recordCount = cmd.Parameters("@recordCount").value 
recordCount = cmd.Parameters("@recordCount").value 
recordCount = cmd.Parameters("@recordCount").value Count if page>pageCount then 
response.Redirect("?page="&pageCount) 
end if 
set rs = cmd.Execute 



遊標由SQL Server傳回的結果集。

使用遊標(cursor)的一個主要的原因就是把集合運算轉換成單一記錄處理方式。用SQL語言從資料庫檢索資料後，結果會放在記憶體的一塊區域中，結果往往是一個含有多個記錄的集合。遊標機制允許使用者在SQL server內逐行地存取這些記錄，並按照使用者自己的意願來顯示和處理這些記錄。

2. 遊標的優點

從遊標定義可以得到遊標的如下優點，這些優點使遊標在實際應用中發揮了重要作用：
1）允許程式對由查詢語句select傳回的行集合中的每一行執行相同或不同的操作，而不是對整個行集合執行同一個操作。
2）提供對基於遊標位置的表中的行進行刪除和更新的能力。
3）遊標實際上是作為面向集合的資料庫管理系統（RDBMS）和麵向行的程式設計之間的橋樑，使這兩種處理方式透過遊標溝通起來。

3．遊標的使用

講了這個多遊標的優點，現在我們就親自來揭開遊標的神秘的面紗。

使用遊標的順序： 聲名遊標、開啟遊標、讀取資料、關閉遊標、刪除遊標

CREATE PROCEDURE PK_Test

AS

CREATE PROCEDURE PK_Test

AS

--

declare @A_Salary float

--宣告一個遊標mycursor，

declare mycursor cursor for select mycursor

--從遊標取出資料(select語句中參數的個數必須要和從遊標取出的變數名稱相同)賦值到我們剛剛宣告的個變數中

fetch next from mycursor into @O_ID,@A_Salary

/遊標的狀態

//0 fetch語句成功

//-1 fetch語句失敗或此行不在結果集中

//-2被提取的行不存在*/

--循環一次讀 遊標，得到@O_ID,@A_Salary的值

while (@@fetch_status=0)

--開始讀執

begin

--開始讀執

begin

量，隨便使用。

--顯示我們每次用遊標取出的值

print '遊標成功取出一條數據'

print @O_ID

print @A_Salary

next from mycursor into @O_ID,@A_Salary

end

--關閉遊標

close
GO

Top 的使用

select Top 5 lngWorkID,strWorkName,strExecHumanName,strBeginDate

from worklist where lngExecHumanID= @lngUserID