第六章存储过程

第六章存储过程（StoredProcedure）6．1存储过程的概念通常情况下，在客户-服务器体系结构中，运行在客户端的应用程序通过SQL对服务器端的数据库进行访问时，每一条SQL语句是分别地、一句一句地从客户端向服务器发出请求，然后数据库服务器再将结果一个一个地返回给应用程序。但是，有一些应用程序，涉及的服务器端数据库处理活动很多，而与用户的交互活动较少，在这种情况下，将有关数据库的处理活动以储存过程的形式放在数据库服务器上完成，则可以大大减轻网上传输流量，提高应用程序性能。储存过程的概念如下图所示。图6-1普通程序的执行过程图6-2存储过程应用的体系结构使用储存过程的应用程序具有以下优点：减少网上传输流量；改善服务器处理密集型应用的性能；客户端应用程序客户端CAEDB2网络数据库存储过程数据库DB2客户端应用程序客户端CAE网络可以访问数据库服务器特有的功能特性；易于维护；6.2存储过程的程序结构一个完整的使用储存过程的应用程序由两部分组成：一是储存过程本身，它被存放在数据库服务器端并数据库服务器上运行；二是对存储过程进行调用的客户端应用程序，它在客户端上运行。客户端应用程序与服务器端储存过程分别运行在两个不同的进程空间中，并且有不同的功能划分。客户端应用程序的主要功能是：为有关数据结构和主变量定义、分配并初始化存储空间；与数据库进行连接；通过SQLCALL语句调用存储过程完成事务的提交（COMMIT）和回滚（ROLLBACK）（注：除非采用分布式工作单元，服务器端的存储过程也可以完成事务的提交和回滚）；执行CONNECTRESET语句；服务器端储存过程的主要功能是：接收客户端应用程序传送的SQLCA和SQLDA等数据库结构；作为与客户端应用程序相同的事务在数据库服务器上运行；向客户端应用程序返回SQLCA和其它输出数据；6.3客户端应用程序客户端应用程序在使用CALL语句调用存储过程之前必须先执行几个步骤。首先，必须连接数据库，声明、分配并初始化SQLDA结构或宿主变量。提示：不要在数据库服务器上分配这些变量结构。数据库管理系统将根据客户端应用程序中的结构在服务器端自动分配所需的存储。客户端应用程序必须保证在调用存储过程之前已建立了数据库连接，否则，将会产生错误。6.3.1CALL语句客户端应用程序可通过CALL语句向服务器发出存储过程调用请求。CALL的语法格式如下所示：CALL{过程名|主变量名}{（主变量1，…，主变量N）|USINGDESCRIPTION描述符名}CALL语句的作用是将调用参数通过一个SQLDA数据结构从客户端传送给服务器端的存储过程，存储过程执行后再将输出结果通过同一个SQLDA返回给客户端应用程序。1．有关被调用存储过程的名称的规定1）在客户端应用程序中，被调用的存储过程的名称既可以直接给出，也可以通过一个主变量给出。2）如果被调用的存储过程的名称是直接给出的，则它必须是一个通常的标识符（identifier）并且长度不能超过254个字节。由于通常的标识符中不能含有空格或其它特殊字符，所以，如果被调用的存储过程的名称中必须包含有空格或其它特殊字符时，则只能使用主变量来给出其名称。3）如果是通过主变量给出被调用的存储过程的名称，则使用的主变量必须是一个具有长度属性的字符串型的变量，并且其长度不能超过254个字节。4）被调用的存储过程的名称可以采用下列几种格式：proname：表示要装入名为proname的存储过程函数库并执行其中名为proname的函数；proname!funcname：表示要装入名为proname的存储过程函数库并执行其中名为funcname的函数；注：按照缺省方式，在基于UNIX的平台上，数据库管理系统将在INSTHOME/sqllib/function目录中查找被隔离的（fenced）存储过程函数库。而在Intel平台上，存储过程函数库的搜索位置则由操作系统环境变量LIBPATH说明。对于不加隔离的（unfenced）存储过程函数库，其搜索位置分别是INSTHOME/sqllib/unfenced（基于UNIX的系统）或sqllib\dll\unfenced（基于Intel的系统）。绝对路径：如/home/user1/procname!funcname即表示要装入/home/user1目录下的名为proname的存储过程函数库并执行其中名为funcname的函数；2．通过宿主变量传递过程调用参数以下是一段通过宿主变量传递过程调用参数的程序例子：EXECSQLBEGINDECLARESECTION;charhost_var1[15]；floathost_var2;shortind_var2;longhost_var3;shortind_var3;charprocname[254]=“myproc”;EXECSQLENDDECALRESECTION;strcopy(host_var1,”newdata”);host_var2=17.6;ind_var2=0;ind_var3=-1;EXECSQLCALL:procname(:host_var1,:host_var2:ind_var2,:host_var3:ind_var3);如上面的程序段所示，在调用存储过程时，可以通过宿主变量向存储过程传递调用参数。存储过程的调用参数可分为只输入的（inputonly）、只输出的（outputonly）和既输入又输出的（bothinputandoutput）。如果调用参数没有明确被说明为只输入的（inputonly）或只输出的（outputonly），则CALL语句将其缺省处理为既输入又输出的（bothinputandoutput）的调用参数。调用参数的输入输出类型由相应的指示符变量确定。对于只输入的（inputonly）的调用参数其相应指示符变量的值应为0（例如，上面程序段中的host_var2）；对于只输出的（outputonly）的调用参数其相应指示符变量的值应为-1（例如，上面程序段中的host_var3）；3．通过SQLDA传递过程调用参数以下是一段通过SQLDA传递过程调用参数的程序例子：structsqlda*inout_sqlda=(structsqlda*)malloc(SQLDASIZE(3));longhost_var3;shortind_var3=-1;shortind_var2=0;inout_sqlda-sqln=3;inout_sqlda-sqld=3;inout_sqlda-sqlvar[0].sqltype=SQL_TYPE_CSTR;inout_sqlda-sqlvar[0].sqllen=16;inout_sqlda-sqlvar[0].sqldata=(char*)malloc(inout_sqlda-sqlvar[0].sqllen);strcpy(inout_sqlda-sqlvar[0].sqldata,”newdata”);inout_sqlda-sqlvar[1].sqltype=SQL_TYPE_NFLOAT;inout_sqlda-sqlvar[1].sqllen=sizeof(float);inout_sqlda-sqlvar[1].sqldata=(char*)malloc(inout_sqlda-sqlvar[1].sqllen);*(float*)inout_sqlda-sqlvar[1].sqldata=17.6;inout_sqlda-sqlvar[1].sqlind=&ind_var2;inout_sqlda-sqlvar[2].sqltype=SQL_TYPE_NINTEGER;inout_sqlda-sqlvar[2].sqllen=sizeof(long);inout_sqlda-sqlvar[2].sqldata=(char*)&host_var3;inout_sqlda-sqlvar[1].sqlind=&ind_var3;EXECSQLCALLmyprocUSINGDESCRIPTOR:*inout_sqlda;如上面的程序段所示，在调用存储过程时，也可以通过SQLDA向存储过程传递调用参数。6.3.2创建存储过程以下是通过CREATEPROCEDURE语句创建存储过程的例子：CREATEPROCEDUREMYPROC(INOUTHOST1CHAR(15),INHOST2DOUBLE,OUTHOST3INTEGER)EXTERNALNAME‘/home/user1/myfn!fn1’LANGUAGECPARAMETERSTYLEDB2DARI1）CREATEPROCEDURE语句的作用是向数据库服务器注册一个新的存储过程；2）MYPROC为存储过程的指定名。客户端应用程序可以在CALL语句使用这一名字调用相应的存储过程；3）INOUTHOST1CHAR（15）表示存储过程中所需的一个调用参数。其中，参数输入输出类型INOUT表明该参数既可向存储过程提供输入信息，也可接收从存储过程返回的信息。参数输入输出类型IN表示相应的参数只用于输入(inputonly)；参数输入输出类型OUT表示相应的参数只用于输出(outputonly)；4）在同一个模式下，不允许定义名称、参数个数和数据类型都完全相同的存储过程；5）EXTERNALNAME（外部名）表示用户为实现所定义的存储过程而编写的程序代码段的名称；6）LANGUAGEC在CREATEPROCEDURE语句中是必不可少的，其作用是指明存储过程体的语言接口约定。该子句的另一个选项是LANGUAGEJAVA；7）PARAMETERSTYLE的作用是说明向存储过程传递参数以及从存储过程接收返回结果的有关约定。DB2DARI表明存储过程使用的参数传递约定将与C语言的函数调用和连接约定相兼容；PARAMETERSTYLEDB2DARI必须与LANGUAGEC一起使用。DB2GENERAL表明存储过程使用的参数传递约定将与JAVA语言的函数调用和连接约定相兼容；PARAMETERSTYLEDB2GENERAL必须与LANGUAGEJAVA一起使用；6.4服务器端存储过程的实现6.4.1实现存储过程的函数定义在UDB中，存储过程的实现代码一般可应用某种编程语言（如C、C++或JAVA等）来编写。下面以C语言为例，说明在编写存储过程的实现代码时应注意的事项。SQL_API_RCSQL_API_FNmyproc(void*reserved1,void*reserved2,structsqlda*inout_sqlda,structsqlca*ca){/*noconnecttionrelatedstatements*//*runsinbackground*//*nocommandtoterminatecurrentprocess*.//*(noexit,_exit,oratexit)*//*ifDUOWnoCOMMITorROLLBACK*/return(ret_value);}1）上面程序段开头的SQL_API_RC和SQL_API_FN为2个预定义的宏，目的是程序的可移植性；2）在实现存储过程的函数体中不能出现与数据库连接相关的语句，即不能出现CONNECT，CONNECTRESET，CREATEDATABASE，DROPDATABASE，ALTERDATABASE，BACKUP，RESTORE，ROLLFORWORD等语句；3）由于存储过程只在后台运行，所以不允许有写屏幕（如，printf）的动作，但允许写文件（fprintf）；4）存储过程的函数体只是一个被数据库管理系统调用的程序例程，所以当执行结束时总是应当将控制交还该调用它的函数，而不应终止当前进程，即在其函数体中不应出现exit（）,_exit（）这样的函数调用；5）如果调用存储过程的客户端应用程序的数据库连接类型（CONNECTTYPE）为D