存储过程详细介绍

存 储 过 程
　　介绍怎样使用存储过程。存储过程是数据库服务器端的一段程序，它有两种类型。一种类似于SELECT查询，用于检索数据，检索到的数据能够以数据集的形式返回给客户。另一种类似于INSERT或DELETE查询，它不返回数据，只是执行一个动作。有的服务器允许同一个存储过程既可以返回数据又可以执行动作。
10.1 概 述
　　在不同类型的服务器上，存储过程的工作方式是不同的。例如，对于InterBase服务器来说，它能够以输出参数的形式返回数据，而对于其他服务器如MicrosoftSQL Server和Sybase，能够以数据集的形式返回数据和信息。
　　在Delphi 4中，要访问和操纵服务器上的存储过程，可以使用TStoredProc构件或TQuery构件。至于到底选择哪一个，取决于存储过程本身是怎样编写的、数据怎样返回和使用哪一种服务器。TStoredProc构件和TQuery构件都是从TDataSet继承下来的。
　　TStoredProc构件适合于执行那些不需要返回数据，并且通过输出参数来返回信息的存储过程。TStoredProc构件的Params属性用于管理这些参数，同时，TStoredProc构件的GetResults函数可以显式地申请返回结果。总之，TStoredProc构件适合于执行那些不需要返回结果或者只是通过输出参数返回结果的存储过程。
　　TQuery构件适合于执行那些能够返回数据集的存储过程，包括InterBase服务器上通过输出参数返回数据集的存储过程。当然，TQuery构件也适合于执行那些不需要返回结果或者只是通过输出参数返回结果的存储过程。
　　参数既可以由存储过程传递给客户程序，也可以由客户程序传递给存储过程，前者称为输出参数，后者称为输入参数。对于有的服务器来说，输出参数只能传递一个值，而有的服务器允许输出参数传递一个数据集。
10.2 什么时候需要用存储过程
　　如果服务器定义了存储过程，应当根据需要决定是否要用存储过程。存储过程通常是一些经常要执行的任务，这些任务往往是针对大量的记录而进行的。在服务器上执行存储过程，可以改善应用程序的性能。这是因为：
.服务器往往具有强大的计算能力和速度。
.避免把大量的数据下载到客户端，减少网络上的传输量。
　　例如，假设一个应用程序需要计算一个数据，这个数据需要涉及到许多记录。如果不使用存储过程的话，把这些数据下载到客户端，导致网络上的流量剧增。
　　不仅如此，客户端可能是一台老掉牙的计算机，它的运算速度很慢。而改用存储过程后，服务器会很快地把数据计算出来，并且只需传递一个数据给客户端，其效率之高是非常明显的。
10.3 怎样使用存储过程
　　应用程序怎样使用存储过程，取决于存储过程本身是怎样编写的、数据怎样返回和使用哪一种服务器。
10.3.1 使用存储过程的一般步骤
　　要访问服务器上的存储过程，一般是这么几个步骤：
　　第一步，把一个TStoredProc构件放到窗体或数据模块上。
　　第二步，设置DatabaseName属性指定一个数据库，可以设为BDE别名或者应用程序专用的别名(如果用TDatabase构件连接数据库的话)。
　　第三步，设置StoredProcName属性指定存储过程的名称。如果前面正确设置了DatabaseName属性，就可以从一个下拉列表中选择一个存储过程。由于经常要在运行期执行不同的存储过程，因此，StoredProcName属性一般是在运行期设置的。
　　第四步，单击Params边上的省略号按钮打开一个编辑器。如果第二步和第三步设置正确的话，在这个编辑器中将显示所有的输入和输出参数，否则，这个编辑器就是空的。
　　要说明的是，并不是所有的服务器都能够提供有关的参数的信息。如果服务器没有提供有关参数的信息，就得自己建立这些参数。
10.3.2 准备和执行存储过程
　　在执行存储过程之前，最好先通知服务器准备好，这就要调用TStoredProc构件的Prepare函数，例如：
　　StoredProc1.Prepare;
　　注意：如果应用程序在运行期改变了参数的信息，必须重新调用Prepare函数。要执行存储过程，可以调用TStoredProc构件的ExecProc函数，程序示例如下：
StoredProc1.Params[0].AsString := Edit1.Text;
StoredProc1.Prepare;
StoredProc1.ExecProc;
　　注意：如果在调用ExecProc之前没有调用Prepare，TStoredProc构件会自动把参数准备好，存储过程执行完毕后，再自动取消准备。不过，如果一个存储过程要反复执行多次的话，最好显式地调用Prepare，不再需要执行存储过程时调用UnPrepare函数。
　　执行了存储过程后，它有可能返回这样几种数据：
.一是数据集，可以用标准的数据控件显示其中的数据。
.二是输出参数。
.三是状态信息。
10.4 创建一个存储过程
　　存储过程一般是用专门的工具编写的。不过，这里要介绍的是怎样用SQL语句在运行期动态地创建存储过程。对于不同的服务器来说，即使是相同功能的存储过程，SQL语句也有可能是不同的，因此，必须事先查阅服务器的文档。
10.4.1 使用SQL语句创建存储过程
　　要使用SQL语句创建存储过程，就要用到TQuery构件的SQL属性。如果存储过程中要用到参数的话，必须把TQuery构件的ParamCheck属性设为False。
　　下面的例子演示了怎样用SQL语句创建一个存储过程：
With Query1 Do
Begin
　 ParamCheck := False;
With SQL Do
Begin
Clear;
Add('CREATE PROCEDURE GET_MAX_EMP_NAME');
Add('RETURNS (Max_Name CHAR(15))');
Add('AS');Add('BEGIN');
Add('SELECT MAX(LAST_NAME)');
Add('FROM EMPLOYEE');
Add('INTO :Max_Name;');
Add('SUSPEND;');
Add('END');End;
ExecSQL;
End;
　　当然，也可以用SQL Explorer来创建存储过程。
10.4.2 用TQuery构件检索数据集
　　要用TQuery构件从存储过程中检索数据集，必须正确设置SQL属性。在SELECT语句中，要用存储过程的名称代替表格的名称。如果存储过程需要传递输入参数的话，要仿照Object Pascal语言的过程那样，在存储过程后面用一对圆括号把参数的值括起来。如果有多个输入参数，彼此之间要用逗号隔开。
　　例如，InterBase服务器上有一个存储过程叫GET_EMP_PROJ，它需要传递一个输入参数叫EMP_NO，并且通过一个输出参数叫PROJ_ID来传递执行结果。下面是这个存储过程的代码：
CREATE PROCEDURE GET_EMP_PROJ (EMP_NO SMALLINT)
　 RETURNS (PROJ_ID CHAR(5))
　 AS
　 　BEGIN
　　 FOR SELECT PROJ_ID
　　 FROM EMPLOYEE_PROJECT
　　 WHERE EMP_NO = :EMP_NO
　　 INTO :PROJ_ID
　　 DO
　　 SUSPEND;
　 END
　　相应地，要通过上面这个存储过程检索数据集，SQL语句可以这样写：
SELECT *
　 FROM GET_EMP_PROJ(52)
10.4.3 用TStoredProc构件检索数据集
　　要用TStoredProc构件从存储过程中检索数据集，必须设置StoredProcName属性指定一个存储过程的名称。如果存储过程需要传递输入参数的话，可以通过Params属性或ParamByName函数提供参数。
　　例如，Sybase服务器上有一个存储过程叫GET_EMPLOYEES，它有个输入参数叫@EMP_NO。下面是这个存储过程的代码：
　　CREATE PROCEDURE GET_EMPLOYEES @EMP_NO SMALLINT
　　 AS SELECT EMP_NAME, EMPLOYEE_NO FROM EMPLOYEE_TABLE
　　 WHERE (EMPLOYEE_NO = @EMP_NO)
　　相应地，要通过上面这个存储过程检索数据集，程序应当这样写：
With StoredProc1 Do
Begin
Close;
ParamByName('@EMP_NO').AsSmallInt := 52;
Active := True;
End;
10.4.4 用TQuery构件通过参数检索数据
　　用TQuery构件通过参数返回的数据是一条记录，即使存储过程只有一个输出参数。因此，应用程序需要从返回的数据中检索出每一个字段的值。
　　首先，要在SELECT语句中用存储过程的名称代替表格的名称。
　　如果有多个输出参数的话，您可以选择其中部分输出参数，也可以用星号表示选择所有输出参数。
　　如果存储过程需要传递输入参数的话，在存储过程后面用一对圆括号把参数的值括起来。如果有多个输入参数，彼此之间要用逗号隔开。
　　例如，InterBase服务器上有一个存储过程叫GET_HIGH_EMP_NAME，通过一个输出参数叫High_Last_Name来返回EMPLOYEE表的LAST_NAME字段。
　　下面是这个存储过程的代码：
CREATE PROCEDURE GET_HIGH_EMP_NAME
RETURNS (High_Last_Name CHAR(15))
AS
BEGIN
SELECT MAX(LAST_NAME)
FROM EMPLOYEE
INTO :High_Last_Name;
SUSPEND;
END
　　相应地，SQL语句应当这样写：
SELECT High_Last_Name
　 FROM GET_HIGH_EMP_NAME
10.4.5 用TStoredProc构件通过参数检索数据
　　要用TStoredProc构件通过参数检索数据，首先要设置StoredProcName属性指定一个存储过程。如果存储过程需要传递输入参数的话，可以通过Params属性或ParamByName函数提供参数。
　　调用ExecProc执行了存储过程后，可以通过Params属性或ParamByName函数访问输出参数。
　　例如，InterBase服务器上有一个存储过程叫GET_HIGH_EMP_NAME，通过一个输出参数叫High_Last_Name返回EMPLOYEE表的LAST_NAME字段。
　　下面是这个存储过程的代码：
CREATE PROCEDURE GET_HIGH_EMP_NAME
RETURNS (High_Last_Name CHAR(15))
AS　
BEGIN
SELECT MAX(LAST_NAME)FROM EMPLOYEE
INTO :High_Last_Name;
SUSPEND;
END
　　相应地，要通过上面这个存储过程检索数据，程序应当这样写：
With StoredProc1 Do
Begin
StoredProcName := 'GET_HIGH_EMP_NAME'ExecProc;
Edit1.Text := ParamByName('High_Last_Name').AsString;
End;
10.4.6 用TQuery构件执行一个动作
　　有的存储过程并不返回数据，它们只是执行一些动作。例如，要删除一条记录，既可以用DELETE语句直接删除记录，也可以执行一个存储过程。
　　要用TQuery构件执行一个动作，需在SQL语句中包含要执行的存储过程的名称。如果存储过程需要传递输入参数的话，在存储过程后面用一对圆括号把参数的值括起来。如果有多个输入参数，彼此之间要用逗号隔开。
　　例如，InterBase服务器上有一个存储过程叫ADD_EMP_PROJ，用于向EMPLOYEE_PROJECT表中增加一条记录。
　　下面是这个存储过程的代码：
CREATE PROCEDURE ADD_EMP_PROJ (EMP_NO SMALLINT, PROJ_ID CHAR(5))
AS
BEGIN
BEGIN
INSERT INTO EMPLOYEE_PROJECT (EMP_NO, PROJ_ID)
VALUES (:EMP_NO, :PROJ_ID);
WHEN SQLCODE -530 DO
EXCEPTION UNKNOWN_EMP_ID;
END
SUSPEND;
END
　　相应地，SQL语句应当这样写：
EXECUTE PROCEDURE ADD_EMP_PROJ(20, 'GUIDE');
10.4.7 用TStoredProc构件执行一个动作
　　要用TStoredProc构件执行一个动作，首先要设置StoredProcName属性指定一个存储过程。可以通过Params属性或ParamByName函数提供输入参数(如果需要的话)。
　　例如，InterBase服务器上有一个存储过程叫ADD_EMP_PROJ，用于向EMPLOYEE_PROJECT表中增加一条记录。它的代码请参见上一小节。
　　要执行这个存储过程，程序应当这样写：
With StoredProc1 Do
Begin
StoredProcName := 'ADD_EMP_PROJ';
ExecProc;
End;
10.5 存储过程的参数
　　要执行服务器上的存储过程，往往要传递一些参数。这些参数分为四种类型：
　　第一种称为输入参数，由客户程序向存储过程传递值。
　　第二种称为输出参数，由存储过程向客户程序返回结果。
　　第三种称为输入/输出参数，既可以由客户程序向存储过程传递值，也可以由存储过程向客户程序返回结果。
　　第四种称为状态参数，由存储过程向客户程序返回错误信息。
　　要说明的是，并不是所有的服务器都支持上述四种类型的参数，例如，InterBase就不支持状态参数。
　　可以通过TStoredProc构件的Params属性访问存储过程的参数(TParam对象)。如果在设计期正确设置了StoredProcName属性，Params属性中将自动包含存储过程的参数，否则，需要自己建立参数。
10.5.1 输入参数
　　输入参数用于由客户程序向存储过程传递值，值实际上是传递给存储过程中的SQL语句。如果一个存储过程有输入参数，一定要在执行该存储过程之前对输入参数赋值。
　　如果用TQuery构件执行存储过程，可以把输入参数用一对圆括号括起来，彼此之间用逗号隔开，就像调用Object Pascal的过程一样。例如，假设要执行一个存储过程叫GET_EMP_PROJ，它需要传递一个输入参数，其值为52，SQL语句如下：
　SELECT PROJ_ID
　　FROM GET_EMP_PROJ(52)
　　如果用TStoredProc构件执行存储过程，可以通过Params属性或ParamByName函数来访问每一个输入参数。要在执行存储过程前对输入参数赋值。例如，假设要执行一个存储过程叫GET_EMP_PROJ，它需要传递一个输入参数叫EMP_NO，其数据类型为SMALLINT，其值为52，相应地程序代码应当这样写：
With StoredProc1 Do
Begin
ParamByName('EMP_NO').AsSmallInt := 52;
ExecProc;
End;
10.5.2 输出参数
　　输出参数用于由存储过程向客户程序传递结果。输出参数是由存储过程赋值的，客户程序只能在执行了存储过程以后，才能访问输出参数的值。
　　要访问输出参数的值，可以通过TStoredProc构件的Params属性或ParamByName函数。例如，下面的代码把输出参数的值显示到一个编辑框中：
With StoredProc1 Do
Begin
ExecProc;
Edit1.Text := Params[0].AsString;
End;
　　大多数存储过程都有一个或几个输出参数，输出参数既可以返回一个单独的值，也可以返回一个数据集。
　　注意：有的服务器如InFormix可能不提供参数的信息，只能从存储过程的代码中查看它有无输出参数。
10.5.3 输入/输出参数
　　输入/输出参数既可以用于由客户程序向存储过程传递值，也可以由存储过程向客户程序返回结果，也就是说，同一个参数兼具两种角色。作为输入参数，必须在执行存储过程之前对它赋值。作为输出参数，只能在执行了存储过程后访问它的值。
　　例如，Oracle服务器中有一个存储过程，它的IN_OUTVAR参数就是一个输入/输出参数。这个存储过程的代码如下：
CREATE OR REPLACE PROCEDURE UPDATE_THE_TABLE (IN_OUTVAR IN OUT INTEGER)
AS
BEGIN
UPDATE ALLTYPETABLE
SET NUMBER82FLD = IN_OUTVAR
WHERE KEYFIELD = 0;
IN_OUTVAR:=1;
END
UPDATE_THE_TABLE;
　　相应地，要执行上面这个存储过程，程序代码应当这样写：
With StoredProc1 Do
Begin
ParamByName('IN_OUTVAR').AsInteger := 103;
ExecProc;IntegerVar := ParamByName('IN_OUTVAR').AsInteger;
End;
10.5.4 状态参数
　　除了返回数据集或输出参数外，有的存储过程还可以返回一个状态参数。状态参数不需要事先赋值，只有在执行了存储过程之后才能访问它的值。
　　要访问输出参数的值，可以通过TStoredProc构件的Params属性或ParamByName函数。例如，下面的代码访问ByOutputParam参数：
　　DateVar := StoredProc1.ParamByName('ByOutputParam').AsDate;
10.5.5 怎样在设计期访问参数
　　如果在设计期正确设置了DatabaseName和StoredProcName属性，就可以在设计期看到这些参数，对于其中的输入参数，可以设置它们的值。不过，有的数据库服务器不提供存储过程的参数信息，这种情况下，只能使用SQLExplorer去查看存储过程的代码，从中找出参数的名称和类型，然后在对象观察器中手动建立这些参数。
　　要在设计期访问参数，可以单击Params属性边上的省略号按钮打开如图10.1所示的编辑器：
　　图10.1 存储过程的参数
　　单击工具栏上的按钮可以创建一个新的参数，单击按钮可以删除一个参数，单击按钮可以把参数的顺序上移，单击按钮可以把参数的顺序下移。
　　选择其中一个参数，对象观察器将同步显示该参数的属性。其中，ParamType属性必须设置，以指定参数的使用类型，可以设为Input、Output、Input/Output或Result。DataType属性也必须设置，以指定参数的数据类型。注：对于Oracle的存储过程来说，要返回数据集，必须把DataType属性设为ftCursor。对于输入参数或输入/输出来说，必须设置Value属性给参数赋值。不能对输出参数和状态参数赋值。
10.5.6 怎样在运行期访问参数
　　如果服务器没有提供有关参数的信息，就必须自己建立参数。在运行期，可以通过TParam的Create或TParams的AddParam来创建一个参数。
　　例如，InterBase服务器上有一个存储过程叫GET_EMP_PROJ，这个存储过程有一个输入参数叫EMP_NO和一个输出参数叫PROJ_ID。这个存储过程的代码如下：
CREATE PROCEDURE GET_EMP_PROJ (EMP_NO SMALLINT)
RETURNS (PROJ_ID CHAR(5))
AS
BEGIN
FOR SELECT PROJ_IDFROM EMPLOYEE_PROJECT
WHERE EMP_NO = :EMP_NO
INTO :PROJ_ID
DO
SUSPEND;
END
下面通过编程动态地创建这两个参数：
var
P1, P2: TParam;
Begin
...
With StoredProc1 Do
Begin
StoredProcName := 'GET_EMP_PROJ';
Params.Clear;
P1 := TParam.Create(Params, ptInput);
P2 := TParam.Create(Params, ptOutput);
TryParams[0].Name := 'EMP_NO';Params[1].Name := 'PROJ_ID';
ParamByname('EMP_NO').AsSmallInt := 52;
ExecProc;Edit1.Text := ParamByname('PROJ_ID').AsString;
FinallyP1.Free;
P2.Free;
End;
End;
...
End;
10.5.7 ParamBindMode属性
　　这个属性用于设置Params属性中的每一个参数与存储过程的参数怎样匹配。
　　如果ParamBindMode属性设为pbByName(默认)，表示Params属性中的参数按名称与存储过程的参数匹配。
　　如果ParamBindMode设为pbByNumber，表示Params属性中的参数按序号与存储过程的参数匹配。
　　建议把ParamBindMode属性设为pbByName，因为按名称匹配不需要参数的顺序，而按序号匹配往往容易搞错。不过，有的情况下可能需要按序号匹配，因为有的服务器并没有提供参数的名称。