Sybase存儲過程
一、存儲過程簡介
二、存儲過程的創建、修改、刪除
三、存儲過程中的參數、返回值和變量
四、存儲過程中的流程控制語言
五、存儲過程中的事務、游標
六、ASE存儲過程和IQ存儲過程的常見區別（附例子）
1 存儲過程簡介
存儲過程(Stored Procedure)是為了完成特定的功能而匯集成一組的SQL語句 集，並為該組SQL語句命名、經編譯後 存儲在SQL Server的數據庫中。用戶可以 根據需要決定是 否在每次執行時讓SQL Server進行重新編譯。用戶可以指 定存 儲過程的名字和給出參數來執行它。 允許多個用戶(有權)訪問相同的代碼。 提供一種集中且一致的實現數據完整性邏輯的方法。 存儲過程用於實現： -- 頻繁使用的查詢 -- 業務規則 -- 被其他過程使用的公共例行程序，例如錯誤處理例行程序等
1.1 存儲過程的優點
使用存儲過程可加快運行速度 可減少網絡交通 可重用、可共享性 存儲過程也是一種安全機制 使用存儲過程實現數據庫完整性 提高數據與應用的獨立性
1.2 存儲過程的分類
系統存儲過程主要 系統提供存儲過程 用於從系統表中獲取信息、為系統管理員和有權用戶提供更新系統表 的途徑。系統存儲 過程的名字都以"sp_"為前綴。 如：sp_help 。 用戶定義的存儲過 用戶定義存儲過程 程是由用戶為完成某一特定功能 而編寫的存儲過程。
1.2.1 系統存儲過程
在SQL Server安裝時自動建立了一些以sp_為前綴的系統存儲過程， 這些系統過程通常用來顯示或修改系統表它們可為各用戶所供享： 例如
2 存儲過程的創建、修改、刪除
創建存儲過程 執行存儲過程 查看、修改、刪除存儲過程 存儲過程中的注釋
2.1 創建存儲過程
create proc procedure_name as begin SQL_statements [return] end
2.1 創建存儲過程
存儲過程被放在當前正在使用的數據庫中。 在存儲過程中可以引用在其他數據庫中的對象 創建存儲過程(create proc)語句不能與其他的SQL語句在同一個批 中，即創建存儲過程語句必須單獨成為一個批。 在存儲過程中可以包含SQL語句，但是不能包含：use, create vIEw, create rule, create default, create proc, create trigger
2.2 執行存儲過程
語法: 語法: [exec[ute]] procedure_name [參數]
2.3 查看、修改和刪除存儲過程
查看存儲過程 查看創建存儲過程的源代碼，使用： procedure_name sp_helptext
查看存儲過程所依賴的表和視圖信息，使用： sp_depends procedure_name 查看存儲過程的一般信息，如創建日期等，使用： sp_help procedure_name
2.3 查看、修改和刪除存儲過程
重新命名存儲過程 語法：sp_rename old_name , new_name 語法 例：將已創建的存儲過程reports_1改名為reports_1b： exec sp_rename reports_1, report_lb
2.3 查看、修改和刪除存儲過程
刪除存儲過程 語法：drop proc procedure_name 語法 例： 刪除已創建的存儲過程reports： drop proc reports
2.4 存儲過程中的注釋
SQL Server提供了兩種在T－SQL中的注釋方法： 使用斜槓星號對/* 注示內容* ?a 使用斜槓星號對/* 注示內容*/ 例如: /*bind the rule to all columns with datatype*/ exec sp_bindrule ul_tid,tid
2.4 存儲過程中的注釋
使用雙連字符?°--?± ?°-?a 使用雙連字符?°--?± 例如： --bind the rule to all columns --with datatype tid exec sp_bindrule ul_tid,tid
3 存儲過程中的參數、返回值和變量
存儲過程中的參數 存儲過程的返回狀態 存儲過程中的變量
3.1 存儲過程中的參數
輸入參數(Input Parameters) 是指由調用程序向存儲過程 提供的變量值。它們在創建存儲過程語句中被定義，而 在執 行該存儲過程語句中給出相應的變量值。 使用輸入 參數的優 點是使存儲過程得更加靈活。
3.1 存儲過程中的參數
語法： create proc procedure_name 語法 (@parameter_name datatype [, @parameter_name datatype……]) as begin SQL_statements return end
3.1.1 帶參數存儲過程
舉例： 舉例： 創建帶參數的存儲過程 create proc proc_author_addr (@lname varchar(40)) as begin select phone, address, city, state from authors where au_lname = @lname return -- 在調用程序的執行存儲過程命令中，將相應的值傳遞給 -- 這個輸入參數：用'Green'替換@lname exec proc_author_addr @lname = 'Green' 或 exec proc_author_addr 'Green?? end
3.1.2 帶有返回參數的存儲過程
舉例： 舉例： 創建向調用程序返回值的存儲過程：
create proc proc_num_sales (@book_id char(6) = null, /* 輸入參數 */ @tot_sales int output /* 輸出參數 */ as begin /* 過程將返回對於給定書號的書的總銷售量 */ select @tot_sales = sum(qty) from salesdetail where title_id = @book_id return end
3.2 存儲過程返回狀態
每個存儲過程的執行，都將自動返回一個返回狀態，用於告知調用程序執行該存 儲過程的狀況。調用程序可根據返回狀態作相應的處理。 語法create proc procedure_name ( …… ) as begin SQL_statements return [ integer ] end
3.2 存儲過程返回狀態
其中：integer為一整數。如果不指定，系統將自動返回一個 整數值。系統使用0表示該過程執行成功；-1至¨C14 表示該 過程執行有錯，-15至 -99為系統保留值。用戶一般使用大於 0的整數，或小於 -100的負整數。
3.3 存儲過程中的變量
局部變量 全局變量
3.3.1.1 局部變量的定義與聲明
局部變量是 －用戶自定義變量 －使用declare語句定義 －具有名和數據類型 －通過用戶賦值 －說明時給變量賦值為空 －局部變量可在存儲過程，或觸發器中定義
3.3.1.1 局部變量的定義與聲明
語法 DECLARE @var_name data_type [, @var_name data_type] …… 舉例 declare @msg varchar(40) declare @myqty int, @myid char(4)
3.3.1.2 為局部變量賦值
局部變量被聲明時， 它的初值為NULL ， 使用SELECT語句將指定值賦給局部變量。 語法 select @var = expression [,@var = expression ] [from… [where…]… 舉例 declare @var1 int select @var1=99
3.3.1.2 為局部變量賦值
注意 — 在一個賦值給局部變量的select 語句中， 可以使用常數、 從表中取值、或使用表達式給局部變量賦值。 — 不能使用同一SELECT 語句既給局部變量賦值，又檢索 數據返回給客戶。 — 一個賦值給局部變量的SELECT 語句，不向用戶顯示任 何值。
3.3.1.3 對局部變量的限制
局部變量必須先用DECLARE定義，再用SELECT語句賦值後才能使用。 局部變量只能使用在T－SQL語句中使用常量的地方。 局部變量不能使用在表名、列名、其它數據庫對象名、保留字使用的地方。 局部變量是標量，它們擁有一個確切的值。 賦值給局部變量的SELECT語句應該返回單個值。如果賦值的SELECT語句沒有返 回值，則該局部變量的值保持不變；如果賦值的SELECT語句返回多個值，則該局 部變量取最後一個返回的值。
3.3.1.4 使用局部變量時通常發生的錯誤
在程序中， 使用局部變量通常容易發生的錯誤是數據類型不 匹配。 即使用DECLARE 語句定義局部變量的數據類型與賦值 給局部變量的值的數據類型不匹配。 如果發生這種情況， SQL Server 總是試圖隱式轉換為局部變量的數據類型。
3.3.2 全局變量
全局變量( Gloabal Variable )是SQL Server系統提供並賦值的變量。 用戶不能建立全局變量，也不能使用SELECT語句去修改全局變量的 值。全局變量的名字用@@開始。大多數全局變量的值報告本次SQL Server啟動後發生的系統活動，可以使用系統存儲過程sp_monitor顯 示全局變量的當前值。通常全局變量的值賦給在同一批中的局部變 量，以便保存和作進一步處理
3.3.2.1 常用的全局變量
全局變量 @@error @@rowcount @@version 賦給的值 由最近一個語句產生的錯誤號 被最近一個語句影響的行數 SQL Server的版本號 允許與該SQL Server連接的最大用戶個數
@@max_connections @@Servername 、
該SQL Server的名字
3.3.2.2 全局變量舉例
舉例 select @@version declare @book_price money select @book_price = price from titles where title_id = 'BU1032' if @@rowcount = 0 print 'no such title_id' else begin print 'title_id exists with' select 'price of' = @book_price end
4 存儲過程中的流程控制語言
流程控制SQL語句的執行順序，這在存儲過程、觸發器、批中非常有用。流控制 關鍵字（命令）包括： IF ELSE IF EXISTS 和 IF NOT EXISTS BEGIN…END RETURN WHILE BREAK和CONTINUE WAITFOR PRINT
4.1 IF ELSE
部分語法(ASE) if boolean_expression statement [else [if boolean_expression1] statement1 ] 部分語法(IQ) if boolean_expression then statement [else [if boolean_expression1] statement1 ] End if
4.2 IF EXISTS 和 IF NOT EXISTS
功能 當你關心數據是否存在時，在IF 語句中使用[NOT] EXISTS 是很有用的。 語法(ASE) 語法 if [not] exists (select statement) statement block
4.2 IF EXISTS 和 IF NOT EXISTS
舉例(ASE) 舉例 /* 是否存在姓“Smith”的作者 */ declare @lname varchar(40) select @lname = 'Smith' if exists ( select * from authors where au_lname = @lname)
select 'here is a ' + @lname else select 'here is no author called'+@lname
4.3 BEGIN…END
功能 當需要將一個以上的SQL 語句作為一組語句對待時， 可以 使用BEGIN 和END 將它們括起來形成一個SQL 語句塊。從 語法上看，一個SQL 語句塊相當於一個SQL 語句。在流控制 語言中， 允許用一個SQL 語句塊替代單個SQL 語句出現的地 方。
4.3 BEGIN…END
語法 BEGIN statement block END 這裡： statement block 通常為一個以上的SQL 語句。當然也可是 一個SQL 語句。
4.4 RETURN
功能 RETURN 命令無條件退出它所在的批、 存儲過程或觸發器。 退出時，能選擇提供返回狀態。RETURN 語句之後的任何語 句不被執行。 語法 RETURN [integer_expression]
4.4 RETURN
舉例 if not exists ( select * from titles where title_id = @t_id) begin print 'here is no title by this title_id' return --無條件退出批，其後語句不被執行。 insert salesdetail values
(@s_id, @o_num, @t_id,@qty_sold, @disc) end go
4.5 WHILE
功能 WHILE 關鍵字為要重復執行的某一語句或語句塊設置條件， 當指定的條件為真（TRUE ）時，執行這一語句或語句塊， 直到條件為假( FALSE ) 或執行BREAK 語句。語句塊是由 BEGIN 和END 括起來的兩個或兩個以上的語句構成
4.5 WHILE
語法(ASE) 語法 while boolean exprission statement block 語法(IQ) 語法 while boolean exprission loop statement block end loop
4.5 WHILE
舉例 while (select avg(price) from titles) < $40 begin select title_id, price from titles where price > $20 update titles set price = price + $2 end select title_id, price from titles print "Too much for the market to bear"
4.6 BREAK和CONTINUE
功能 BREAK和CONTINU關鍵字控制在WHILE循環中語句塊中語句的執 行。 — BREAK關鍵字將退出它所在的循環，繼續執行後面的語句(即跳過 從關鍵字BREAK到它所在循環結束END之間的所有語句)。
— CONTINU關鍵字使循環重新開始，即跳過任何在該循環內但在 CONTINU關鍵字之後的語句。
4.6 BREAK和CONTINUE
語法(ASE) 語法 WHILE boolean expression BEGIN statement1 statement2 BREAK CONTINU END statement
4.6 BREAK和CONTINUE
舉例(ASE) 舉例 while (select avg(price) from titles) >= $20 begin update titles set price = price / 2 if (select max(price) from titles) < $40 break else if (select avg(price) from titles) < $20 continu print "Average price still over $20" end print "Not too expensive.or Average price under $20"
4.7 WAITFOR
功能 WAITFOR 關鍵字將掛起當前的執行, 直到指定的事件發 生。它常常被系統用來實現有規律的系統維護、出錯處理、 事件處理和統計記錄等。 部分語法 waitfor {delay time | time time |……}
4.7 WAITFOR
舉例 這個例子是一個無終止循環，它每隔半小時記錄一次鎖的個數。 while 2>1 /* 這一表達式總是返回'TRUE' */
waitfor delay '0:30:00' /* 每隔30分鐘 */ insetrt into num_procs select getdate(), count(*) from master, syslocks
4.8 PRINT
功能 PRINT關鍵字用來在屏幕上顯示用戶定義的 信息，局部變量的值或char/varchar類型的全 局變量的值。 語法 print {"any ascii characters or string" |local_variable | global_variable } [, arg_list ]
4.8 PRINT
舉例 (1) print "hello" (2) print @msg /* @msg 是一個局部變量。 */ (3) declare @table_name varchor(30) , @user_name varchar(30) select @table_name = "titles", @user_name = "ezekIEl" print "The table%1! is not owned by the user %2!",@table_name ,@user_name
5 存儲過程中的事務、游標
存儲過程中的事務 存儲過程中的游標
5.1 嵌套事務
嵌套事務 是指在存儲過程中的事務的間接嵌套， 即嵌套事務的形成是因為調用 含有事務的過程。@@trancount 記錄了事務嵌套級次。@@trancount在第一個 begin tran語句後值為1，以後每遇到一個 begin tran 語句，不論是否在嵌套 過程中，@@trancount的值增加1；每遇到 一個commit，@@trancount的值就減少 1。若@@trancount的 值 等於 零，表示當前沒有事務；若@@trancount的值不等 於零，其值 假定為i，表明當前處於第 i 級嵌套事務中。對於嵌套事務，直 到 使用@@trancount 的值為零的那個 commit語句被執行,整個 事務才被提交。 select @@trancount
舉例： 舉例：在存儲過程中的事務嵌套
5.1.1 與事務相關的語句
Rollback 根據SQL Server的缺省規定，一個不帶事務名或保存 點名的 rollback tran 語句，不論它是否在嵌套事務中， 總是退到最外 面的 begin tran語句，即回退包括所有 嵌套事務在內的整個事務 commit 根據SQL Server的缺省規定，即使是在嵌套事務中 執行commit 語 句，@@trancount計數值也只減少1。
5.2 存儲過程中的游標
語法 create proc procedure_name as SQL_statements containing cursor processing 其中：SQL_statements containing cursor processing 是指包含游標處理的SQL語句。
5.2 存儲過程中的游標
舉例 create proc proc_fetch_book As begin declare @book_title char(30), @book_id char(6) declare biz_book cursor for select title, title_id from titles where type = "business" open biz_book fetch biz_book into @book_title, @book_id …… -- 在這裡做某些處理 close biz_book deallocate cursor biz_book return end
5.2 存儲過程中的游標
游標的作用域 如果存儲過程是嵌套的話，那麼也包括它的所有子域。 這就是說，如果嵌套的存儲過程構成一棵調用樹(Call Tree),那麼在 這棵 樹的某個結點上定義的游標，其作用域就是它位於的樹叉，即 自定義游標的那個結點的存儲過程及它所包含的所有子域。但是如果 在它所包含的子域中，定義了與它同名的游標，那麼它將在定義同名
游標的子域內及該子域所包含的其他子域內失效。
6 ASE存儲過程和IQ存儲過程常見區別、舉例
ASE存儲過程和IQ存儲過程常見區別 舉例
6.1 ASE存儲過程和IQ存儲過程常見區別
COMMIT ¨C 在IQ存儲過程中， 每一個增、 刪、 改、 查的上sql 後都要加上一個commit以保證語句成功執行（ASE不用） BEGIN END ¨C ASE的存儲過程的每個程序分支要放在BEGIN END中（每個條件判斷，每個循環等），而IQ不用，只要在程 序最外邊有個BEGIN END 就可以了
6.1 ASE存儲過程和IQ存儲過程常見區別
ASE存儲過程要在存儲過程名之後，BEGIN之前加上AS，IQ不 用 變量定義 ¨C ASE DECLARE @ date_begin CHAR(8); IQ DECLARE date_begin CHAR(8);
6.1 ASE存儲過程和IQ存儲過程常見區別
變量賦值 - ASE存儲過程中：select @sql_str = ??1?ˉ IQ存儲過程中： select??1' into sql_str 變量引用 - ASE存儲過程中：@+變量名 IQ存儲過程中：變量名
6.2 舉例
例子 ASE存儲過程 IQ存儲過程
