SQL学习之存储过程

1 存储过程的概念
    通常情况下，在客户-服务器体系结构中，运行在客户端的应用程序通过SQL对服务器端的数据库进行访问时，每一条SQL语句是分别地、一句一句地从客户端向服务器发出请求，然后数据库服务器再将结果一个一个地返回给应用程序。但是，有一些应用程序，涉及的服务器端数据库处理活动很多，而与用户的交互活动较少，在这种情况下，将有关数据库的处理活动以储存过程的形式放在数据库服务器上完成，则可以大大减轻网上传输流量，提高应用程序性能。储存过程的概念如下图所示。
    使用储存过程的应用程序具有以下优点：
A:减少网上传输流量；
B:改善服务器处理密集型应用的性能；
C:以访问数据库服务器特有的功能特性；
D:易于维护；
2 存储过程的程序结构
    一个完整的使用储存过程的应用程序由两部分组成：一是储存过程本身，它被存放在数据库服务器端并数据库服务器上运行；二是对存储过程进行调用的客户端应用程序，它在客户端上运行。客户端应用程序与服务器端储存过程分别运行在两个不同的进程空间中，并且有不同的功能划分。
    客户端应用程序的主要功能是：
    A:关数据结构和主变量定义、分配并初始化存储空间；
    B:与数据库进行连接；
    C:通过SQL CALL语句调用存储过程
    D:完成事务的提交（COMMIT）和回滚（ROLLBACK）（注：除非采用分布式工作单元，服务器端的存储过程也可以完成事务的提交和回滚）；
    E:执行CONNECT RESET语句；
    服务器端储存过程的主要功能是：
    A:接收客户端应用程序传送的SQLCA和SQLDA等数据库结构；
    B:作为与客户端应用程序相同的事务在数据库服务器上运行；
    C:向客户端应用程序返回SQLCA和其它输出数据；
3 客户端应用程序
    客户端应用程序在使用CALL语句调用存储过程之前必须先执行几个步骤。首先，必须连接数据库，声明、分配并初始化SQLDA结构或宿主变量。
    提示：不要在数据库服务器上分配这些变量结构。数据库管理系统将根据客户端应用程序中的结构在服务器端自动分配所需的存储。
    客户端应用程序必须保证在调用存储过程之前已建立了数据库连接，否则，将会产生错误。
3.1 CALL 语句
     客户端应用程序可通过CALL语句向服务器发出存储过程调用请求。CALL的语法格式如下所示：
     CALL {（主变量1，…，主变量N）|USING DESCRIPTION 描述符名}
     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的系统）或sqllibdllunfenced（基于Intel的系统）。
    绝对路径：如/home/user1/procname!funcname即表示要装入/home/user1目录下的名为proname 的存储过程函数库并执行其中名为funcname的函数；
2.通过宿主变量传递过程调用参数
以下是一段通过宿主变量传递过程调用参数的程序例子：
EXEC SQL BEGIN DECLARE SECTION;
char host_var1[15]；
float host_var2;
short ind_var2;
long host_var3;
short ind_var3;
char procname[254] = “myproc”;
EXEC SQL END DECALRE SECTION;

strcopy(host_var1,”new data”);
host_var2 = 17.6;
ind_var2 = 0;
ind_var3 = -1;
EXEC SQL CALL :procname
(:host_var1,:host_var2 :ind_var2,:host_var3 :ind_var3);

    如上面的程序段所示，在调用存储过程时，可以通过宿主变量向存储过程传递调用参数。存储过程的调用参数可分为只输入的（input only）、只输出的（output only）和既输入又输出的（both input and output）。如果调用参数没有明确被说明为只输入的（input only）或只输出的（output only），则CALL语句将其缺省处理为既输入又输出的（both input and output）的调用参数。调用参数的输入输出类型由相应的指示符变量确定。对于只输入的（input only）的调用参数其相应指示符变量的值应为0（例如，上面程序段中的host_var2）；对于只输出的（output only）的调用参数其相应指示符变量的值应为-1（ 例如，上面程序段中的host_var3）；

3.通过SQLDA传递过程调用参数
以下是一段通过SQLDA传递过程调用参数的程序例子：
struct sqlda *inout_sqlda = (struct sqlda *)malloc(SQLDASIZE(3));
long host_var3;
short ind_var3 = -1;
short ind_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,”new data”);

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;

EXEC SQL CALL myproc USING DESCRIPTOR :*inout_sqlda;

如上面的程序段所示，在调用存储过程时，也可以通过SQLDA向存储过程传递调用参数。
3.2 创建存储过程
以下是通过CREATE PROCEDURE语句创建存储过程的例子：
CREATE PROCEDURE MYPROC(INOUT HOST1 CHAR(15),
IN HOST2 DOUBLE, OUT HOST3 INTEGER)
EXTERNAL NAME ‘/home/user1/myfn!fn1’
LANGUAGE C
PARAMETER STYLE DB2DARI

1)CREATE PROCEDURE语句的作用是向数据库服务器注册一个新的存储过程；
2)MYPROC为存储过程的指定名。客户端应用程序可以在CALL语句使用这一名字调用相应的存储过程；
3)INOUT HOST1 CHAR（15）表示存储过程中所需的一个调用参数。其中，参数输入输出类型INOUT表明该参数既可向存储过程提供输入信息，也可接收从存储过程返回的信息。参数输入输出类型IN表示相应的参数只用于输入(input only)；参数输入输出类型OUT表示相应的参数只用于输出(output only)；
4)在同一个模式下，不允许定义名称、参数个数和数据类型都完全相同的存储过程；
5)EXTERNAL NAME（外部名）表示用户为实现所定义的存储过程而编写的程序代码段的名称；
6)LANGUAGE C 在CREATE PROCEDURE语句中是必不可少的，其作用是指明存储过程体的语言接口约定。该子句的另一个选项是LANGUAGE JAVA；
7)PARAMETER STYLE 的作用是说明向存储过程传递参数以及从存储过程接收返回结果的有关约定。DB2DARI表明存储过程使用的参数传递约定将与C语言的函数调用和连接约定相兼容；PARAMETER STYLE DB2DARI必须与LANGUAGE C一起使用。DB2GENERAL表明存储过程使用的参数传递约定将与JAVA语言的函数调用和连接约定相兼容；PARAMETER STYLE DB2GENERAL必须与LANGUAGE JAVA一起使用；

4 服务器端存储过程的实现
4.1实现存储过程的函数定义
在UDB中，存储过程的实现代码一般可应用某种编程语言（如C、C++或JAVA等）来编写。下面以C语言为例，说明在编写存储过程的实现代码时应注意的事项。
SQL_API_RC SQL_API_FN
myproc(void *reserved1,
void *reserved2,
struct sqlda * inout_sqlda,
struct sqlca *ca)
{
/* no connecttion related statements */
/* runs in background */
/* no command to terminate current process *./
/* (no exit, _exit, or at exit) */
/* if DUOW no COMMIT or ROLLBACK */
return(ret_value);
}
1)上面程序段开头的SQL_API_RC和SQL_API_FN为2个预定义的宏，目的是程序的可移植性；
2)在实现存储过程的函数体中不能出现与数据库连接相关的语句，即不能出现CONNECT，CONNECT RESET，CREATE DATABASE，DROP DATABASE，ALTER DATABASE，BACKUP，RESTORE，ROLLFORWORD等语句；
3)由于存储过程只在后台运行，所以不允许有写屏幕（如，printf）的动作，但允许写文件（fprintf）；
4)存储过程的函数体只是一个被数据库管理系统调用的程序例程，所以当执行结束时总是应当将控制交还该调用它的函数，而不应终止当前进程，即在其函数体中不应出现exit（）,_exit（）这样的函数调用；
5)如果调用存储过程的客户端应用程序的数据库连接类型（CONNECT TYPE）为DUOW，则存储过程中不能发出终止事务的语句，即无论动态或静态的COMMIT、ROLLBACK都不允许出现。

4.2 存储过程的参数传递
SQL_API_RC SQL_API_FN
myproc(void *reserved1,void *reserved2,
struct sqlda *inout_sqlda, struct sqlca *ca)
{
struct sqlca sqlca; /*for local use */
/* use input data in SQLDA */
/* do not change SQLD,SQLTYPE, or SQLLEN */
/* do not change pointer for SQLDATA or SQLIND */
/* return data in SQLDATA(and SQLIND) */
memcpy(ca,&sqlca,sizeof(struct sqlca));
…

1) 存储过程将通过SQLDA中的输入变量得到客户端应用程序传来的输入参数，然后再通过SQLDA中的输出变量将输出结果返回给客户端应用程序。由于SQLDA各个域的原始值都是由客户端应用程序在过程调用前设置的，因此，存储过程的函数体不应改变SQLDA中的SQLD，SQLTYPE，SQLLEN等域的值。此外，虽然存储过程的函数体可以改变SQLDATA及SQLIND中所含指针指向的变量的值，但却不应改变SQLDATA及SQLIND中所含的指针。
注：SQLDA中的变量可以同时既是输入变量又是输出变量。
2) 在存储过程的函数体返回之前，应当显式地将本地SQLCA中的信息拷贝到存储过程的SQLCA参数之中。

4.3存储过程的返回值
SQL_API_RC SQL_API_FN
myproc(void *reserved1,void *reserved2,
struct sqlda *inout_sqlda, struct sqlca *ca)
{
/* processing */
/* in this application,the second SQLVAR field
is used to determine if the client intends to
call the server procedure again, A value of 0
means no further calls. */
if((*float *)inout_sqlda->sqlvar[1].sqlda != 0)
return(SQLZ_HOLD_PROC)
else
return(SQL_DISCONNECT_PROC);
}
1） 应当特别注意的是，存储过程的返回值根本不会返回给客户端应用程序。这里所说的返回值的作用是使数据库管理系统能够确定当存储过程执行终止时是否将存储过程从内存中释放掉。
2） 存储过程可以向数据库管理系统返回下列2个值：
—SQL_DISCONNECT_PROC：其含义是告诉数据库管理系统当所有信息都传递给客户端后，即可释放（或卸载）存储过程及其数据存储；
—SQL_HOLD_PROC：其含义是告诉数据库管理系统将存储过程库函数仍然保持在内存当中，这样将可以保证当客户端下一次发出对该存储过程的DARI调用时被调用的库函数已经在内存当中了，从而提高系统性能。
3） 如果客户端只对存储过程调用一次，则应返回SQL_DISCONNECT_PROC；
4） 如果客户端需要对存储过程调用多次，则应返回SQL_HOLD_PROC，从而保证存储过程不会被卸载；
5） 如果存储过程以SQL_HOLD_PROC作为返回值，则当其被最后一次调用时则应以SQL_DISCONNECT_PROC作为返回值，从而保证最后一次调用后将存储过程从内存中释放掉。否则该存储过程将被一直保存在主存中，直到数据库管理系统停止运行；
客户端应用程序在对存储过程进行最后一次调用时，应将这一信息通知被调用的存储过程。