SybaseIQ存储过程学习笔记Word文件下载.docx
《SybaseIQ存储过程学习笔记Word文件下载.docx》由会员分享，可在线阅读，更多相关《SybaseIQ存储过程学习笔记Word文件下载.docx（15页珍藏版）》请在冰点文库上搜索。
♒修改存储过程
使用SQL语句ALTERPROCEDURE，将整个新的存储过程包含其中。
必须重新给修改后的存储过程赋予用户权限。
♒调用存储过程
CALLnew_dept（210,‘EasternSales’,902）;
♒删除存储过程
DROPPROCEDUREnew_dept
♒存储过程的访问控制
见注
（2）
♒返回值
可以通过三种方式传回值：
使用OUT或者INOUT返回值；
返回结果集；
使用RETURN语句返回单值。
→使用OUT和INOUT返回值
CREATEPROCEDUREAverageSalary（OUTavgsalNUMERIC（20,3））
SELECTAVG（salary）INTOavgsalFROMemployee;
→返回结果集
CREATEPROCEDURESalaryList（INdepartment_idINT）
RESULT（"
EmployeeID"
INT,"
Salary"
NUMERIC（20,3））
SELECTemp_id,salary
FROMemployee
WHEREemployee.dept_id=department_id;
如果在存储过程中一个语句动态的创建了一个临时表,然后从里面选取结果集,为了避免如“Columnnotfound”这样的错误，必须使用EXECUTEIMMEDIATEWITHRESULTSETON这样的语法。
例：
CREATEPROCEDUREp1（IN@tvarchar（30））
BEGIN
EXECUTEIMMEDIATE
'
SELECT*INTO#resultSetFROM'
||@t;
EXECUTEIMMEDIATEWITHRESULTSETON
'
SELECT*FROM#resultSet'
;
5.自定义函数
♒创建自定义函数
CREATEFUNCTIONfullname（firstnameCHAR（30）,
lastnameCHAR（30））
RETURNSCHAR（61）
DECLAREnameCHAR（61）;
SETname=firstname||'
||lastname;
RETURN（name）;
和存储过程存在3个差异
♒参数不需要IN、OUT和INOUT，所有参数默认是IN
♒RETURNS语句指明要返回的值
♒RETURN语句用来返回值
♒调用函数
SELECTfullname（emp_fname,emp_lname）FROMemployee;
♒删除函数
DROPFUNCTIONfullname
♒授予权限
GRANTEXECUTEONfullnameTOanother_user
REVOKEEXECUTEONfullnameFROManother_user
6.BATCH概要以及BATCH中可以使用的SQL语句
♒什么是BATCH
由一些分号分隔的SQL语句，
→例1：
INSERT
INTOdepartment（dept_id,dept_name）
VALUES（220,'
EasternSales'
）;
UPDATEemployee
SETdept_id=220
WHEREdept_id=200
ANDstate='
MA'
;
COMMIT;
→例2：
IFNOTEXISTS（
SELECT*FROMSYSTABLE
WHEREtable_name='
t1'
）THEN
CREATETABLEt1（
firstcolINTPRIMARYKEY,
secondcolCHAR（30）
）;
ELSE
MESSAGE'
Tablet1alreadyexists'
ENDIF
7.控制语句
Controlstatement
Syntax
Compoundstatements
BEGIN[ATOMIC]
statement-list
Conditionalexecution:
IF
IFconditionTHEN
statement-list
ELSEIFconditionTHEN
ELSE
ENDIF
CASE
CASEexpression
WHENvalueTHEN
WHENvalueTHEN
ELSE
ENDCASE
Repetition:
WHILE,LOOP
WHILEconditionLOOP
ENDLOOP
FORcursorloop
FORloop-name
AScursor-name
CURSORFORselectstatement
DO
Statement-list
ENDFOR
Break:
LEAVE
LEAVElabel
CALL
CALLprocname（arg,...）
♒使用复合语句
见注
（1）
♒复合语句中的声明
局部变量的声明应当紧接着BEGIN语句,可以声明如下几种类型的局部变量:
→变量
→游标
→临时表
→异常（错误标识符）
♒原子复合语句
8.存储过程的结构
♒存储过程中的可用SQL语句
可以使用的SQL语句:
→SELECT,UPDATE,DELETE,INSERT,andSETVARIABLE
→TheCALLstatementtoexecuteotherprocedures
→Controlstatements（see“Controlstatements”）
→Cursorstatements（see“Usingcursorsinprocedures”）
→Exceptionhandlingstatements（see“Usingexceptionhandlersinprocedures”）
→TheEXECUTEIMMEDIATEstatement
不可以使用的SQL语句:
→CONNECTstatement
→DISCONNECTstatement
♒存储过程声明参数类型
→IN
→OUT
→INOUT
♒存储过程传参
CREATEPROCEDURESampleProc
（INOUTvar1INTDEFAULT1,
INOUTvar2intDEFAULT2,
INOUTvar3intDEFAULT3）
...
CREATEVARIABLEV1INT;
CREATEVARIABLEV2INT;
CREATEVARIABLEV3INT;
v2、v3有默认值，可省略
CALLSampleProc（V1）;
CALLSampleProc（var1=V1,var3=V3）;
♒函数传参
→函数中可以设置默认参数,但是不能像存储过程那样通过变量名指定的方式传入参数
→参数是传的值,非引用参数
→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）
--1.声明一个异常常量
DECLAREerr_notfound
EXCEPTIONFORSQLSTATE'
02000'
--2.声明两个变量存储公司名和值
DECLAREThisNameCHAR（36）;
DECLAREThisValueINT;
--3声明一个ThisCompnay的游标
DECLAREThisCompanyCURSORFOR
SELECTcompany_name,
CAST（sum（sales_order_items.quantity*
product.unit_price）ASINTEGER）
ASvalue
FROMcustomer
INNERJOINsales_order
INNERJOINsales_order_items
INNERJOINproduct
GROUPBYcompany_name;
--4.初始化TopValue的值
SETTopValue=0;
--5.打开游标
OPENThisCompany;
--6.循环
CompanyLoop:
LOOP
FETCHNEXTThisCompany
INTOThisName,ThisValue;
IFSQLSTATE=err_notfoundTHEN
LEAVECompanyLoop;
ENDIF;
IFThisValue>
TopValueTHEN
SETTopCompany=ThisName;
SETTopValue=ThisValue;
ENDLOOPCompanyLoop;
--7.关闭游标
CLOSEThisCompany;
→精简版
CREATEPROCEDURETopCustomerValue2（
OUTTopCompanyCHAR（36）,
--InitializetheTopValuevariable
--DotheForLoop
FORCompanyForASThisCompany
CURSORFOR
SELECTcompany_nameASThisName,
product.unit_price）ASINTEGER）
ASThisValue
FROMcustomer
INNERJOINsales_order
INNERJOINsales_order_items
INNERJOINproduct
GROUPBYThisName
DO
ENDIF;
ENDFORCompanyLoop;
11.存储过程中的错误和警告
出现错误时,则是更新SQLSTATE和SQLCODE,然后返回；
出现警告时,更新了上述两个变量后,继续执行。
声明异常：
DECLARE变量名EXCEPTIONFORSQLSTATE‘02000’
抛出异常:
SIGNAL变量名
异常处理:
CREATEPROCEDUREInnerProc（）
DECLAREcolumn_not_found
52003'
MESSAGE'
HellofromInnerProc.'
SIGNALcolumn_not_found;
LinefollowingSIGNAL.'
EXCEPTION
WHENcolumn_not_foundTHEN
MESSAGE'
Columnnotfoundhandling.'
WHENOTHERSTHEN
RESIGNAL;
异常处理中可用的语句:
IF
SELECT@variable=
CASE
LOOP
LEAVE
CONTINUE
EXECUTE
SIGNAL
RESIGNAL
DECLARE
SETvariable
12.存储过程Tips
→检查是否需要更改delimiter
→每行SQL语句最好都进行delimiter
→使用表的全称限定词
→显式转换日期和时间
→验证传入的参数
13.常用类型及函数
♒日期及相关函数
日期类型有,
DATE:
YYYY-MM-DD
DATETIME:
YYYY-MM-DDHH:
MM:
SS.XXXXXX
SMALLDATETIME:
TIME:
HH:
TIMESTAMP:
YYYY-MM-DDHH:
♒常用的函数
datepart（year/month/day,datetime）
dateadd（year/month/day,[number],datetime）
today（）
now（）
♒判断一个表是否存在
ifexists（select*fromsysobjectswherename=[变量]）then
--dosomething
endif
升级会员 