SybaseIQ存儲過程學習筆記

本文格式為Word版，下載可任意編輯——SybaseIQ存儲過程學習筆記

實踐:

SYBASEIQ存儲過程學習筆記

1.存儲過程

存儲過程將過程化的SQL語句存儲在數(shù)據(jù)庫中，供所有程序使用。存儲過程中可以包含控制語句,如LOOP循環(huán)語句、IF和CASE這樣的條件語句。存儲過程通過CALL語句進行調(diào)用，使用變量傳入?yún)?shù)和返回結(jié)果?？梢苑祷亟Y(jié)果集，也可以調(diào)用其他的存儲過程。

2.存儲過程和函數(shù)的區(qū)別

自定義函數(shù)是只能返回單一值的特定的存儲過程。函數(shù)不修改傳入的參數(shù)，但是可以使其用于查詢和其他SQL語句之中。

3.存儲過程的調(diào)試

參見附錄CDebuggingLogicintheDatabase

4.存儲過程概要

?常用的存儲過程?創(chuàng)立存儲過程?修改存儲過程?調(diào)用存儲過程?刪除存儲過程

?存儲過程的訪問控制?返回值

?常用的存儲過程

sp_iqprocedure

此存儲過程可以顯示系統(tǒng)和用戶自定義的存儲過程sp_iqprogram

顯示存儲過程的參數(shù)信息,包括結(jié)果集變量和SQLSTATE/SQLCODE錯誤值

?創(chuàng)立存儲過程

CREATEPROCEDUREnew_dept(INidINT,INnameCHAR(35),INhead_idINT)BEGINEND

INSERTINTODBA.department

(dept_id,dept_name,dept_head_id)

VALUES

(id,name,head_id);

1

?修改存儲過程

使用SQL語句ALTERPROCEDURE，將整個新的存儲過程包含其中。必需重新給修改后的存儲過程賦予用戶權(quán)限。

?調(diào)用存儲過程

2

CALLnew_dept(210,‘EasternSales’,902);

?刪除存儲過程

DROPPROCEDUREnew_dept

?存儲過程的訪問控制

見注(2)

?返回值

可以通過三種方式傳回值：使用OUT或者INOUT返回值；返回結(jié)果集；使用RETURN語句返回單值。

3

?使用OUT和INOUT返回值

CREATEPROCEDUREAverageSalary(OUTavgsalNUMERIC(20,3))BEGIN

SELECTAVG(salary)INTOavgsalFROMemployee;END

?返回結(jié)果集

CREATEPROCEDURESalaryList(INdepartment_idINT)

4

RESULT(\1

復合語句,放在BEGIN和END之間;復合語句可以相互嵌套;復合語句用于將多個語句組合成一個單元,其中的SQL語句用分號分隔;除了最終一個分號,其他都是必需的;復合語句中聲明的局部變量只在復合語句中可;可以在BEGIN后面加上ATOMIC,將復合語句聲明為原子性,此時就不可使用COMMIT、ROLLBACK和ROLLBACKTOSAVEPOINT.2

必需賦予EXECUTE權(quán)限才可以執(zhí)行此存儲過程，GRANTEXECUTEONnew_deptTOanother_user;REVOKEEXECUTEONnew_deptFROManother_user3

調(diào)用之前,可以先聲明一個變量保存結(jié)果,語句如下CREATEVARIABLEAverageNUMERIC(20,3)4

存儲過程還可以返回多個同類型的結(jié)果集，必需在客戶端中啟用返回多個結(jié)果集的支持。例:CREATEPROCEDUREListPeople()RESULT(lnameCHAR(36),fnameCHAR(36))BEGINSELECTemp_lname,emp_fnameFROMemployee;SELECTlname,fname

BEGIN

SELECTemp_id,salaryFROMemployee

WHEREemployee.dept_id=department_id;END

假使在存儲過程中一個語句動態(tài)的創(chuàng)立了一個臨時表,然后從里面選取結(jié)果集,為了避免如“Columnnotfound〞這樣的錯誤，必需使用EXECUTEIMMEDIATEWITHRESULTSETON這樣的語法。例：

CREATEPROCEDUREp1(IN@tvarchar(30))BEGIN

EXECUTEIMMEDIATE

5

'SELECT*INTO#resultSetFROM'||@t;EXECUTEIMMEDIATEWITHRESULTSETON'SELECT*FROM#resultSet';

END

5.自定義函數(shù)

?創(chuàng)立自定義函數(shù)

CREATEFUNCTIONfullname(firstnameCHAR(30),lastnameCHAR(30))RETURNSCHAR(61)BEGIN

6

DECLAREnameCHAR(61);

SETname=firstname||''||lastname;RETURN(name);END

和存儲過程存在3個差異

?參數(shù)不需要IN、OUT和INOUT，所有參數(shù)默認是IN?RETURNS語句指明要返回的值?RETURN語句用來返回值

?調(diào)用函數(shù)

7

SELECTfullname(emp_fname,emp_lname)FROMemployee;

?刪除函數(shù)FROMcustomer;SELECTlast_name,first_nameFROMcontact;END5

動態(tài)的創(chuàng)立了臨時表RESULTSET6

和CreateVariable不同之處在于只在BEGINEND中聲明,而創(chuàng)立的變量在整個連接中都存在,知道連接斷開或者DropVariable之后7

凡是授予了EXECUTE權(quán)限的用戶都可以使用函數(shù)

DROPFUNCTIONfullname

?授予權(quán)限

GRANTEXECUTEONfullnameTOanother_user

REVOKEEXECUTEONfullnameFROManother_user

6.BATCH概要以及BATCH中可以使用的SQL語句

?什么是BATCH

由一些分號分隔的SQL語句，

8

?例1：INSERT

INTOdepartment(dept_id,dept_name)VALUES(220,'EasternSales');

UPDATEemployeeSETdept_id=220WHEREdept_id=200ANDstate='MA';

COMMIT;

?例2：BEGIN

IFNOTEXISTS(

SELECT*FROMSYSTABLE

WHEREtable_name='t1')THENCREATETABLEt1(

firstcolINTPRIMARYKEY,secondcolCHAR(30));ELSE

MESSAGE'Tablet1alreadyexists';ENDIFEND

7.控制語句

ControlstatementCompoundstatements8

SyntaxBEGIN[ATOMIC]dbisqlandbatchesAlistofsemicolon-separatedstatements,suchastheabove,isparsedbydbisqlbeforeitissenttotheserver.Inthiscase,dbisqlsendseachstatementindividuallytotheserver,notasabatch.Unlessyouhavesuchparsingcodeinyourapplication,thestatementsaresentandtreatedasabatch.PuttingaBEGINandENDaroundasetofstatementscausesdbisqltotreatthemasabatch.

statement-listEND

Conditionalexecution:IF

IFconditionTHENstatement-listELSEIFconditionTHENstatement-listELSE

statement-listENDIF

CASEexpressionWHENvalueTHENstatement-listWHENvalueTHENstatement-listELSE

statement-listENDCASE

WHILEconditionLOOPstatement-listENDLOOP

FORloop-name

AScursor-name

CURSORFORselectstatementDO

Statement-listENDFORLEAVElabel

CALLprocname(arg,...)

Conditionalexecution:CASE

Repetition:WHILE,LOOP

Repetition:FORcursorloop

Break:LEAVECALL

?使用復合語句

見注(1)

?復合語句中的聲明

局部變量的聲明應當緊接著BEGIN語句,可以聲明如下幾種類型的局部變量:?變量?游標?臨時表

?異常(錯誤標識符)

?原子復合語句

見注(1)

8.存儲過程的結(jié)構(gòu)

?存儲過程中的可用SQL語句

可以使用的SQL語句:

??????

SELECT,UPDATE,DELETE,INSERT,andSETVARIABLETheCALLstatementtoexecuteotherproceduresControlstatements(see“Controlstatements〞)

Cursorstatements(see“Usingcursorsinprocedures〞)

Exceptionhandlingstatements(see“Usingexceptionhandlersinprocedures〞)TheEXECUTEIMMEDIATEstatement

不可以使用的SQL語句:

??

CONNECTstatementDISCONNECTstatement

?存儲過程聲明參數(shù)類型

???

INOUTINOUT

?存儲過程傳參

CREATEPROCEDURESampleProc(...

CREATEVARIABLEV1INT;CREATEVARIABLEV2INT;CREATEVARIABLEV3INT;

v2、v3有默認值，可省略CALLSampleProc(V1);

CALLSampleProc(var1=V1,var3=V3);

INOUTvar1INTDEFAULT1,INOUTvar2intDEFAULT2,INOUTvar3intDEFAULT3)

?函數(shù)傳參

?函數(shù)中可以設(shè)置默認參數(shù),但是不能像存儲過程那樣通過變量名指定的方式傳

入?yún)?shù)

?參數(shù)是傳的值,非引用參數(shù)?OUT、INOUT不可用?無法返回結(jié)果集

9.返回值

?使用RETURN一個值

?使用存儲過程參數(shù)返回值

?SETV1=V2

?SELECTV2INTOV1

?返回單個或者返回多個結(jié)果集

?在RESULT中聲明的變量的數(shù)量必需匹配得上SELECT語句中的數(shù)量。假使類型

不匹配，會做自動轉(zhuǎn)換。

?RESULT語句是CREATEPROCEDURE的一部分，不需要分隔符?SELECT語句中的名稱不需要和RESULT中的進行匹配

?要讓dbisql顯示返回的多個結(jié)果集,必需開多個結(jié)果集返回的開關(guān)

?除非是視圖上面返回的結(jié)果集,是可以修改結(jié)果集的,但是需要有對底層表操作

的權(quán)限

?返回可變結(jié)果集

RESULT語句是可選的，假使省略RESULT語句,那么就可以返回一個可變的結(jié)果集。但是，假使不需要可變結(jié)果集的話，那么就最好加上一條RESULT語句，這樣可以得到更好的性能。

10.使用游標

?游標概要

1)為要執(zhí)行的SELECT語句聲明一個游標,或者使用DECLARE在存儲過程中聲明一

個游標

2)使用OPEN開啟游標

3)使用FETCH一次獲取一個結(jié)果集

4)RowNotFound的告警信號表示到達了行尾5)使用CLOSE關(guān)閉游標

游標會在事務的末尾自動關(guān)閉,使用WITHHOLD可以阻止關(guān)閉,直到顯式關(guān)閉。存儲過程sp_iqcursorinfo可以顯示所有在服務器上面開啟著的游標。

?游標的位置

游標可以放在三個位置上:某行/第一行前面/最終一行后面

新開一個游標時，位置處于第一行前面。使用FETCH命令，可以移動游標，如FETCHABSOLUTE、FETCHFIRST和FETCHLAST；或者使用FETCHRELATIVE、FETCHPRIOR和FETCHNEXT進行相對移動。NEXT是在FETCH命令中隱含的參數(shù)。

在UPDATE和DELETE語句中，假使游標在首行前或者末行后，那么進行操作時會出現(xiàn)Nocurrentrow的錯誤。

?在SELECT語句中使用游標

?廢話版

CREATEPROCEDURETopCustomerValue

(OUTTopCompanyCHAR(36),OUTTopValueINT)BEGIN

--1.聲明一個異往往量DECLAREerr_notfound

9

EXCEPTIONFORSQLSTATE'02000';--2.聲明兩個變量存儲公司名和值DECLAREThisNameCHAR(36);DECLAREThisValueINT;

--3聲明一個ThisCompnay的游標DECLAREThisCompanyCURSORFORSELECTcompany_name,

CAST(sum(sales_order_items.quantity*product.unit_price)ASINTEGER)ASvalueFROMcustomer

INNERJOINsales_order

INNERJOINsales_order_itemsINNERJOINproductGROUPBYcompany_name;--4.初始化TopValue的值SETTopValue=0;--5.開啟游標OPENThisCompany;--6.循環(huán)CompanyLoop:LOOP

FETCHNEXTThisCompany

INTOThisName,ThisValue;IFSQLSTATE=err_notfoundTHENLEAVECompanyLoop;ENDIF;

IFThisValue>TopValueTHENSETTopCompany=ThisName;SETTopValue=ThisValue;ENDIF;

ENDLOOPCompanyLoop;--7.關(guān)閉游標CLOSEThisCompany;END

?精簡版

CREATEPROCEDURETopCustomerValue2(9

詳見Errorsandwarningsinprocedures

OUTTopCompanyCHAR(36),OUTTopValueINT)BEGIN

--InitializetheTopValuevariableSETTopValue=0;--DotheForLoopCompanyLoop:

FORCompanyForASThisCompanyCURSORFOR

SELECTcompany_nameASThisName,

CAST(sum(sales_order_items.quantity*product.unit_price)ASINTEGER)ASThisValueFROMcustomer

INNERJOINsales_order

INNERJOINsales_order_itemsINNERJOINproductGROUPBYThisNameDO

IFThisValue>TopValueTHENSETTopCompany=ThisName;SETTopValue=ThisValue;ENDIF;

ENDFORCompanyLoop;END

11.存儲過程中的錯誤和警告

出現(xiàn)錯誤時,則是更新SQLSTATE和SQLCODE,然后返回；出現(xiàn)警告時,更新了上述兩個變量后,繼續(xù)執(zhí)行。

聲明異常：

DECLARE變量名EXCEPTIONFORSQLSTATE‘02000’

拋出異常:SIGNAL變量名

異常處理:

CREATEPROCEDUREInnerProc()BEGIN

DECLAREcolumn_not_found

EXCEPTIONFORSQLSTATE'52023';MESSAGE'HellofromI