SybaseIQ存储过程学习笔记

本文格式为Word版，下载可任意编辑——SybaseIQ存储过程学习笔记

实践:

SYBASEIQ存储过程学习笔记

1.存储过程

存储过程将过程化的SQL语句存储在数据库中，供所有程序使用。存储过程中可以包含控制语句,如LOOP循环语句、IF和CASE这样的条件语句。存储过程通过CALL语句进行调用，使用变量传入参数和返回结果。可以返回结果集，也可以调用其他的存储过程。

2.存储过程和函数的区别

自定义函数是只能返回单一值的特定的存储过程。函数不修改传入的参数，但是可以使其用于查询和其他SQL语句之中。

3.存储过程的调试

参见附录CDebuggingLogicintheDatabase

4.存储过程概要

?常用的存储过程?创立存储过程?修改存储过程?调用存储过程?删除存储过程

?存储过程的访问控制?返回值

?常用的存储过程

sp_iqprocedure

此存储过程可以显示系统和用户自定义的存储过程sp_iqprogram

显示存储过程的参数信息,包括结果集变量和SQLSTATE/SQLCODE错误值

?创立存储过程

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，将整个新的存储过程包含其中。必需重新给修改后的存储过程赋予用户权限。

?调用存储过程

2

CALLnew_dept(210,‘EasternSales’,902);

?删除存储过程

DROPPROCEDUREnew_dept

?存储过程的访问控制

见注(2)

?返回值

可以通过三种方式传回值：使用OUT或者INOUT返回值；返回结果集；使用RETURN语句返回单值。

3

?使用OUT和INOUT返回值

CREATEPROCEDUREAverageSalary(OUTavgsalNUMERIC(20,3))BEGIN

SELECTAVG(salary)INTOavgsalFROMemployee;END

?返回结果集

CREATEPROCEDURESalaryList(INdepartment_idINT)

4

RESULT(\1

复合语句,放在BEGIN和END之间;复合语句可以相互嵌套;复合语句用于将多个语句组合成一个单元,其中的SQL语句用分号分隔;除了最终一个分号,其他都是必需的;复合语句中声明的局部变量只在复合语句中可;可以在BEGIN后面加上ATOMIC,将复合语句声明为原子性,此时就不可使用COMMIT、ROLLBACK和ROLLBACKTOSAVEPOINT.2

必需赋予EXECUTE权限才可以执行此存储过程，GRANTEXECUTEONnew_deptTOanother_user;REVOKEEXECUTEONnew_deptFROManother_user3

调用之前,可以先声明一个变量保存结果,语句如下CREATEVARIABLEAverageNUMERIC(20,3)4

存储过程还可以返回多个同类型的结果集，必需在客户端中启用返回多个结果集的支持。例:CREATEPROCEDUREListPeople()RESULT(lnameCHAR(36),fnameCHAR(36))BEGINSELECTemp_lname,emp_fnameFROMemployee;SELECTlname,fname

BEGIN

SELECTemp_id,salaryFROMemployee

WHEREemployee.dept_id=department_id;END

假使在存储过程中一个语句动态的创立了一个临时表,然后从里面选取结果集,为了避免如“Columnnotfound〞这样的错误，必需使用EXECUTEIMMEDIATEWITHRESULTSETON这样的语法。例：

CREATEPROCEDUREp1(IN@tvarchar(30))BEGIN

EXECUTEIMMEDIATE

5

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

END

5.自定义函数

?创立自定义函数

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

6

DECLAREnameCHAR(61);

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

和存储过程存在3个差异

?参数不需要IN、OUT和INOUT，所有参数默认是IN?RETURNS语句指明要返回的值?RETURN语句用来返回值

?调用函数

7

SELECTfullname(emp_fname,emp_lname)FROMemployee;

?删除函数FROMcustomer;SELECTlast_name,first_nameFROMcontact;END5

动态的创立了临时表RESULTSET6

和CreateVariable不同之处在于只在BEGINEND中声明,而创立的变量在整个连接中都存在,知道连接断开或者DropVariable之后7

凡是授予了EXECUTE权限的用户都可以使用函数

DROPFUNCTIONfullname

?授予权限

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.存储过程的结构

?存储过程中的可用SQL语句

可以使用的SQL语句:

??????

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

Cursorstatements(see“Usingcursorsinprocedures〞)

Exceptionhandlingstatements(see“Usingexceptionhandlersinprocedures〞)TheEXECUTEIMMEDIATEstatement

不可以使用的SQL语句:

??

CONNECTstatementDISCONNECTstatement

?存储过程声明参数类型

???

INOUTINOUT

?存储过程传参

CREATEPROCEDURESampleProc(...

CREATEVARIABLEV1INT;CREATEVARIABLEV2INT;CREATEVARIABLEV3INT;

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

CALLSampleProc(var1=V1,var3=V3);

INOUTvar1INTDEFAULT1,INOUTvar2intDEFAULT2,INOUTvar3intDEFAULT3)

?函数传参

?函数中可以设置默认参数,但是不能像存储过程那样通过变量名指定的方式传

入参数

?参数是传的值,非引用参数?OUT、INOUT不可用?无法返回结果集

9.返回值

?使用RETURN一个值

?使用存储过程参数返回值

?SETV1=V2

?SELECTV2INTOV1

?返回单个或者返回多个结果集

?在RESULT中声明的变量的数量必需匹配得上SELECT语句中的数量。假使类型

不匹配，会做自动转换。

?RESULT语句是CREATEPROCEDURE的一部分，不需要分隔符?SELECT语句中的名称不需要和RESULT中的进行匹配

?要让dbisql显示返回的多个结果集,必需开多个结果集返回的开关

?除非是视图上面返回的结果集,是可以修改结果集的,但是需要有对底层表操作

的权限

?返回可变结果集

RESULT语句是可选的，假使省略RESULT语句,那么就可以返回一个可变的结果集。但是，假使不需要可变结果集的话，那么就最好加上一条RESULT语句，这样可以得到更好的性能。

10.使用游标

?游标概要

1)为要执行的SELECT语句声明一个游标,或者使用DECLARE在存储过程中声明一

个游标

2)使用OPEN开启游标

3)使用FETCH一次获取一个结果集

4)RowNotFound的告警信号表示到达了行尾5)使用CLOSE关闭游标

游标会在事务的末尾自动关闭,使用WITHHOLD可以阻止关闭,直到显式关闭。存储过程sp_iqcursorinfo可以显示所有在服务器上面开启着的游标。

?游标的位置

游标可以放在三个位置上:某行/第一行前面/最终一行后面

新开一个游标时，位置处于第一行前面。使用FETCH命令，可以移动游标，如FETCHABSOLUTE、FETCHFIRST和FETCHLAST；或者使用FETCHRELATIVE、FETCHPRIOR和FETCHNEXT进行相对移动。NEXT是在FETCH命令中隐含的参数。

在UPDATE和DELETE语句中，假使游标在首行前或者末行后，那么进行操作时会出现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.循环CompanyLoop:LOOP

FETCHNEXTThisCompany

INTOThisName,ThisValue;IFSQLSTATE=err_notfoundTHENLEAVECompanyLoop;ENDIF;

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

ENDLOOPCompanyLoop;--7.关闭游标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.存储过程中的错误和警告

出现错误时,则是更新SQLSTATE和SQLCODE,然后返回；出现警告时,更新了上述两个变量后,继续执行。

声明异常：

DECLARE变量名EXCEPTIONFORSQLSTATE‘02000’

抛出异常:SIGNAL变量名

异常处理:

CREATEPROCEDUREInnerProc()BEGIN

DECLAREcolumn_not_found

EXCEPTIONFORSQLSTATE'52023';MESSAGE'HellofromI