SybaseIQ存储过程学习笔记Word下载.docx

SybaseIQ存储过程学习笔记Word下载.docx

《SybaseIQ存储过程学习笔记Word下载.docx》由会员分享，可在线阅读，更多相关《SybaseIQ存储过程学习笔记Word下载.docx（13页珍藏版）》请在冰豆网上搜索。

♒修改存储过程

使用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