Sybase基本知識(shí)

SYBASE存儲(chǔ)過程
    一、存儲(chǔ)過程簡介
 
    二、存儲(chǔ)過程的創(chuàng)建、修改、刪除
 
    三、存儲(chǔ)過程中的參數(shù)、返回值和變量
 
    四、存儲(chǔ)過程中的流程控制語言
 
    五、存儲(chǔ)過程中的事務(wù)、游標(biāo)
 
    六、ASE存儲(chǔ)過程和IQ存儲(chǔ)過程的常見區(qū)別（附例子）
 
 
    1 存儲(chǔ)過程簡介
    存儲(chǔ)過程(Stored Procedure)是為了完成特定的功能而匯集成一組的SQL語句 集，并為該組SQL語句命名、經(jīng)編譯后 存儲(chǔ)在SQL Server的數(shù)據(jù)庫中。用戶可以 根據(jù)需要決定是 否在每次執(zhí)行時(shí)讓SQL Server進(jìn)行重新編譯。用戶可以指 定存 儲(chǔ)過程的名字和給出參數(shù)來執(zhí)行它。 允許多個(gè)用戶(有權(quán))訪問相同的代碼。 提供一種集中且一致的實(shí)現(xiàn)數(shù)據(jù)完整性邏輯的方法。 存儲(chǔ)過程用于實(shí)現(xiàn)：-- 頻繁使用的查詢 -- 業(yè)務(wù)規(guī)則-- 被其他過程使用的公共例行程序，例如錯(cuò)誤處理例行程序等
    1.1 存儲(chǔ)過程的優(yōu)點(diǎn)
    使用存儲(chǔ)過程可加快運(yùn)行速度 可減少網(wǎng)絡(luò)交通 可重用、可共享性 存儲(chǔ)過程也是一種安全機(jī)制 使用存儲(chǔ)過程實(shí)現(xiàn)數(shù)據(jù)庫完整性 提高數(shù)據(jù)與應(yīng)用的獨(dú)立性
    1.2 存儲(chǔ)過程的分類
    系統(tǒng)存儲(chǔ)過程主要 系統(tǒng)提供存儲(chǔ)過程 用于從系統(tǒng)表中獲取信息、為系統(tǒng)管理員和有權(quán)用戶提供更新系統(tǒng)表 的途徑。系統(tǒng)存儲(chǔ) 過程的名字都以"sp_"為前綴。 如：sp_help 。 用戶定義的存儲(chǔ)過 用戶定義存儲(chǔ)過程 程是由用戶為完成某一特定功能 而編寫的存儲(chǔ)過程。
    1.2.1 系統(tǒng)存儲(chǔ)過程
    在SQL Server安裝時(shí)自動(dòng)建立了一些以sp_為前綴的系統(tǒng)存儲(chǔ)過程， 這些系統(tǒng)過程通常用來顯示或修改系統(tǒng)表它們可為各用戶所供享： 例如
 
 
 
 
    2 存儲(chǔ)過程的創(chuàng)建、修改、刪除
    創(chuàng)建存儲(chǔ)過程 執(zhí)行存儲(chǔ)過程 查看、修改、刪除存儲(chǔ)過程 存儲(chǔ)過程中的注釋
    2.1 創(chuàng)建存儲(chǔ)過程
    create proc procedure_name as begin SQL_statements [return] end
    2.1 創(chuàng)建存儲(chǔ)過程
    存儲(chǔ)過程被放在當(dāng)前正在使用的數(shù)據(jù)庫中。 在存儲(chǔ)過程中可以引用在其他數(shù)據(jù)庫中的對(duì)象 創(chuàng)建存儲(chǔ)過程(create proc)語句不能與其他的SQL語句在同一個(gè)批 中，即創(chuàng)建存儲(chǔ)過程語句必須單獨(dú)成為一個(gè)批。 在存儲(chǔ)過程中可以包含SQL語句，但是不能包含：use, create view, create rule, create default, create proc, create trigger
    2.2 執(zhí)行存儲(chǔ)過程
    語法: 語法: [exec[ute]] procedure_name [參數(shù)]
    2.3 查看、修改和刪除存儲(chǔ)過程
    查看存儲(chǔ)過程 查看創(chuàng)建存儲(chǔ)過程的源代碼，使用：procedure_name sp_helptext
    查看存儲(chǔ)過程所依賴的表和視圖信息，使用：sp_depends procedure_name 查看存儲(chǔ)過程的一般信息，如創(chuàng)建日期等，使用：sp_help procedure_name
    2.3 查看、修改和刪除存儲(chǔ)過程
    重新命名存儲(chǔ)過程 語法：sp_rename old_name , new_name 語法 例：將已創(chuàng)建的存儲(chǔ)過程reports_1改名為reports_1b：exec sp_rename reports_1, report_lb
    2.3 查看、修改和刪除存儲(chǔ)過程
    刪除存儲(chǔ)過程 語法：drop proc procedure_name 語法 例： 刪除已創(chuàng)建的存儲(chǔ)過程reports：drop proc reports
    2.4 存儲(chǔ)過程中的注釋
    SQL Server提供了兩種在T－SQL中的注釋方法： 使用斜杠星號(hào)對(duì)/* 注示內(nèi)容* ?a 使用斜杠星號(hào)對(duì)/* 注示內(nèi)容*/ 例如: /*bind the rule to all columns with datatype*/ exec sp_bindrule ul_tid,tid
    2.4 存儲(chǔ)過程中的注釋
    使用雙連字符?°--?±?°-?a 使用雙連字符?°--?± 例如：--bind the rule to all columns --with datatype tid exec sp_bindrule ul_tid,tid
  
 
 
 
 3 存儲(chǔ)過程中的參數(shù)、返回值和變量
    存儲(chǔ)過程中的參數(shù) 存儲(chǔ)過程的返回狀態(tài) 存儲(chǔ)過程中的變量
    3.1 存儲(chǔ)過程中的參數(shù)
    輸入?yún)?shù)(Input Parameters) 是指由調(diào)用程序向存儲(chǔ)過程 提供的變量值。它們在創(chuàng)建存儲(chǔ)過程語句中被定義，而 在執(zhí) 行該存儲(chǔ)過程語句中給出相應(yīng)的變量值。 使用輸入 參數(shù)的優(yōu) 點(diǎn)是使存儲(chǔ)過程得更加靈活。
    3.1 存儲(chǔ)過程中的參數(shù)
    語法：create proc procedure_name 語法(@parameter_name datatype [, @parameter_name datatype……]) as begin SQL_statements return end
    3.1.1 帶參數(shù)存儲(chǔ)過程
    舉例： 舉例： 創(chuàng)建帶參數(shù)的存儲(chǔ)過程create proc proc_author_addr (@lname varchar(40)) as begin select phone, address, city, state from authors where au_lname = @lname return -- 在調(diào)用程序的執(zhí)行存儲(chǔ)過程命令中，將相應(yīng)的值傳遞給-- 這個(gè)輸入?yún)?shù)：用'Green'替換@lname exec proc_author_addr @lname = 'Green' 或exec proc_author_addr 'Green?? end
    3.1.2 帶有返回參數(shù)的存儲(chǔ)過程
    舉例： 舉例： 創(chuàng)建向調(diào)用程序返回值的存儲(chǔ)過程：
    create proc proc_num_sales (@book_id char(6) = null, /* 輸入?yún)?shù)*/ @tot_sales int output /* 輸出參數(shù)*/ as begin /* 過程將返回對(duì)于給定書號(hào)的書的總銷售量*/ select @tot_sales = sum(qty) from salesdetail where title_id = @book_id return end
    3.2 存儲(chǔ)過程返回狀態(tài)
    每個(gè)存儲(chǔ)過程的執(zhí)行，都將自動(dòng)返回一個(gè)返回狀態(tài)，用于告知調(diào)用程序執(zhí)行該存 儲(chǔ)過程的狀況。調(diào)用程序可根據(jù)返回狀態(tài)作相應(yīng)的處理。 語法create proc procedure_name ( ……) as begin SQL_statements return [ integer ] end
    3.2 存儲(chǔ)過程返回狀態(tài)
    其中：integer為一整數(shù)。如果不指定，系統(tǒng)將自動(dòng)返回一個(gè) 整數(shù)值。系統(tǒng)使用0表示該過程執(zhí)行成功；-1至¨C14 表示該 過程執(zhí)行有錯(cuò)，-15至-99為系統(tǒng)保留值。用戶一般使用大于0的整數(shù)，或小于-100的負(fù)整數(shù)。
    3.3 存儲(chǔ)過程中的變量
    局部變量 全局變量
    3.3.1.1 局部變量的定義與聲明
    局部變量是 －用戶自定義變量 －使用declare語句定義 －具有名和數(shù)據(jù)類型 －通過用戶賦值 －說明時(shí)給變量賦值為空 －局部變量可在存儲(chǔ)過程，或觸發(fā)器中定義
    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 為局部變量賦值
    局部變量被聲明時(shí)， 它的初值為NULL ， 使用SELECT語句將指定值賦給局部變量。 語法select @var = expression [,@var = expression ] [from…[where…]… 舉例declare @var1 int select @var1=99
    3.3.1.2 為局部變量賦值
    注意 — 在一個(gè)賦值給局部變量的select 語句中， 可以使用常數(shù)、 從表中取值、或使用表達(dá)式給局部變量賦值。 — 不能使用同一SELECT 語句既給局部變量賦值，又檢索 數(shù)據(jù)返回給客戶。 — 一個(gè)賦值給局部變量的SELECT 語句，不向用戶顯示任 何值。
    3.3.1.3 對(duì)局部變量的限制
    局部變量必須先用DECLARE定義，再用SELECT語句賦值后才能使用。 局部變量只能使用在T－SQL語句中使用常量的地方。 局部變量不能使用在表名、列名、其它數(shù)據(jù)庫對(duì)象名、保留字使用的地方。 局部變量是標(biāo)量，它們擁有一個(gè)確切的值。 賦值給局部變量的SELECT語句應(yīng)該返回單個(gè)值。如果賦值的SELECT語句沒有返 回值，則該局部變量的值保持不變；如果賦值的SELECT語句返回多個(gè)值，則該局 部變量取最后一個(gè)返回的值。
    3.3.1.4 使用局部變量時(shí)通常發(fā)生的錯(cuò)誤
    在程序中， 使用局部變量通常容易發(fā)生的錯(cuò)誤是數(shù)據(jù)類型不 匹配。 即使用DECLARE 語句定義局部變量的數(shù)據(jù)類型與賦值 給局部變量的值的數(shù)據(jù)類型不匹配。 如果發(fā)生這種情況，SQL Server 總是試圖隱式轉(zhuǎn)換為局部變量的數(shù)據(jù)類型。
    3.3.2 全局變量
    全局變量( Gloabal Variable )是SQL Server系統(tǒng)提供并賦值的變量。 用戶不能建立全局變量，也不能使用SELECT語句去修改全局變量的 值。全局變量的名字用@@開始。大多數(shù)全局變量的值報(bào)告本次SQL Server啟動(dòng)后發(fā)生的系統(tǒng)活動(dòng)，可以使用系統(tǒng)存儲(chǔ)過程sp_monitor顯 示全局變量的當(dāng)前值。通常全局變量的值賦給在同一批中的局部變 量，以便保存和作進(jìn)一步處理
    3.3.2.1 常用的全局變量
    全局變量@@error @@rowcount @@version 賦給的值 由最近一個(gè)語句產(chǎn)生的錯(cuò)誤號(hào) 被最近一個(gè)語句影響的行數(shù)SQL Server的版本號(hào) 允許與該SQL Server連接的最大用戶個(gè)數(shù)
    @@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 存儲(chǔ)過程中的流程控制語言
    流程控制SQL語句的執(zhí)行順序，這在存儲(chǔ)過程、觸發(fā)器、批中非常有用。流控制 關(guān)鍵字（命令）包括：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
    功能 當(dāng)你關(guān)心數(shù)據(jù)是否存在時(shí)，在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
    功能 當(dāng)需要將一個(gè)以上的SQL 語句作為一組語句對(duì)待時(shí)， 可以 使用BEGIN 和END 將它們括起來形成一個(gè)SQL 語句塊。從 語法上看，一個(gè)SQL 語句塊相當(dāng)于一個(gè)SQL 語句。在流控制 語言中， 允許用一個(gè)SQL 語句塊替代單個(gè)SQL 語句出現(xiàn)的地 方。
    4.3 BEGIN…END
    語法BEGIN statement block END 這里：statement block 通常為一個(gè)以上的SQL 語句。當(dāng)然也可是 一個(gè)SQL 語句。
    4.4 RETURN
    功能RETURN 命令無條件退出它所在的批、 存儲(chǔ)過程或觸發(fā)器。 退出時(shí)，能選擇提供返回狀態(tài)。RETURN 語句之后的任何語 句不被執(zhí)行。 語法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 --無條件退出批，其后語句不被執(zhí)行。insert salesdetail values
    (@s_id, @o_num, @t_id,@qty_sold, @disc) end go
    4.5 WHILE
    功能WHILE 關(guān)鍵字為要重復(fù)執(zhí)行的某一語句或語句塊設(shè)置條件， 當(dāng)指定的條件為真（TRUE ）時(shí)，執(zhí)行這一語句或語句塊， 直到條件為假( FALSE ) 或執(zhí)行BREAK 語句。語句塊是由BEGIN 和END 括起來的兩個(gè)或兩個(gè)以上的語句構(gòu)成
    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關(guān)鍵字控制在WHILE循環(huán)中語句塊中語句的執(zhí) 行。 —BREAK關(guān)鍵字將退出它所在的循環(huán)，繼續(xù)執(zhí)行后面的語句(即跳過 從關(guān)鍵字BREAK到它所在循環(huán)結(jié)束END之間的所有語句)。
    —CONTINU關(guān)鍵字使循環(huán)重新開始，即跳過任何在該循環(huán)內(nèi)但在CONTINU關(guān)鍵字之后的語句。
    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 關(guān)鍵字將掛起當(dāng)前的執(zhí)行, 直到指定的事件發(fā) 生。它常常被系統(tǒng)用來實(shí)現(xiàn)有規(guī)律的系統(tǒng)維護(hù)、出錯(cuò)處理、 事件處理和統(tǒng)計(jì)記錄等。 部分語法waitfor {delay time | time time |……}
    4.7 WAITFOR
    舉例 這個(gè)例子是一個(gè)無終止循環(huán)，它每隔半小時(shí)記錄一次鎖的個(gè)數(shù)。while 2>1 /* 這一表達(dá)式總是返回'TRUE' */
    waitfor delay '0:30:00' /* 每隔30分鐘*/ insetrt into num_procs select getdate(), count(*) from master, syslocks
    4.8 PRINT
    功能PRINT關(guān)鍵字用來在屏幕上顯示用戶定義的 信息，局部變量的值或char/varchar類型的全 局變量的值。 語法print {"any ascii characters or string" |local_variable | global_variable } [, arg_list ]
    4.8 PRINT
    舉例(1) print "hello" (2) print @msg /* @msg 是一個(gè)局部變量。*/ (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 存儲(chǔ)過程中的事務(wù)、游標(biāo)
    存儲(chǔ)過程中的事務(wù) 存儲(chǔ)過程中的游標(biāo)
    5.1 嵌套事務(wù)
    嵌套事務(wù) 是指在存儲(chǔ)過程中的事務(wù)的間接嵌套， 即嵌套事務(wù)的形成是因?yàn)檎{(diào)用 含有事務(wù)的過程。@@trancount 記錄了事務(wù)嵌套級(jí)次。@@trancount在第一個(gè)begin tran語句后值為1，以后每遇到一個(gè)begin tran 語句，不論是否在嵌套 過程中，@@trancount的值增加1；每遇到 一個(gè)commit，@@trancount的值就減少1。若@@trancount的 值 等于 零，表示當(dāng)前沒有事務(wù)；若@@trancount的值不等 于零，其值 假定為i，表明當(dāng)前處于第i 級(jí)嵌套事務(wù)中。對(duì)于嵌套事務(wù)，直 到 使用@@trancount 的值為零的那個(gè)commit語句被執(zhí)行,整個(gè) 事務(wù)才被提交。select @@trancount
    舉例： 舉例：在存儲(chǔ)過程中的事務(wù)嵌套
    5.1.1 與事務(wù)相關(guān)的語句
    Rollback 根據(jù)SQL Server的缺省規(guī)定，一個(gè)不帶事務(wù)名或保存 點(diǎn)名的rollback tran 語句，不論它是否在嵌套事務(wù)中， 總是退到最外 面的begin tran語句，即回退包括所有 嵌套事務(wù)在內(nèi)的整個(gè)事務(wù)commit 根據(jù)SQL Server的缺省規(guī)定，即使是在嵌套事務(wù)中 執(zhí)行commit 語 句，@@trancount計(jì)數(shù)值也只減少1。
    5.2 存儲(chǔ)過程中的游標(biāo)
    語法create proc procedure_name as SQL_statements containing cursor processing 其中：SQL_statements containing cursor processing 是指包含游標(biāo)處理的SQL語句。
    5.2 存儲(chǔ)過程中的游標(biāo)
    舉例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 存儲(chǔ)過程中的游標(biāo)
    游標(biāo)的作用域 如果存儲(chǔ)過程是嵌套的話，那么也包括它的所有子域。 這就是說，如果嵌套的存儲(chǔ)過程構(gòu)成一棵調(diào)用樹(Call Tree),那么在 這棵 樹的某個(gè)結(jié)點(diǎn)上定義的游標(biāo)，其作用域就是它位于的樹叉，即 自定義游標(biāo)的那個(gè)結(jié)點(diǎn)的存儲(chǔ)過程及它所包含的所有子域。但是如果 在它所包含的子域中，定義了與它同名的游標(biāo)，那么它將在定義同名
    游標(biāo)的子域內(nèi)及該子域所包含的其他子域內(nèi)失效。
 
 
  6 ASE存儲(chǔ)過程和IQ存儲(chǔ)過程常見區(qū)別、舉例
    ASE存儲(chǔ)過程和IQ存儲(chǔ)過程常見區(qū)別 舉例
    6.1 ASE存儲(chǔ)過程和IQ存儲(chǔ)過程常見區(qū)別
    COMMIT ¨C 在IQ存儲(chǔ)過程中， 每一個(gè)增、 刪、 改、 查的上sql 后都要加上一個(gè)commit以保證語句成功執(zhí)行（ASE不用）BEGIN END ¨C ASE的存儲(chǔ)過程的每個(gè)程序分支要放在BEGIN END中（每個(gè)條件判斷，每個(gè)循環(huán)等），而IQ不用，只要在程 序最外邊有個(gè)BEGIN END 就可以了
    6.1 ASE存儲(chǔ)過程和IQ存儲(chǔ)過程常見區(qū)別
    ASE存儲(chǔ)過程要在存儲(chǔ)過程名之后，BEGIN之前加上AS，IQ不 用 變量定義 ¨C ASE DECLARE @ date_begin CHAR(8); IQ DECLARE date_begin CHAR(8);
    6.1 ASE存儲(chǔ)過程和IQ存儲(chǔ)過程常見區(qū)別
    變量賦值- ASE存儲(chǔ)過程中：select @sql_str = ??1?ˉIQ存儲(chǔ)過程中：select??1' into sql_str 變量引用- ASE存儲(chǔ)過程中：@+變量名IQ存儲(chǔ)過程中：變量名
    6.2 舉例
    例子ASE存儲(chǔ)過程IQ存儲(chǔ)過程