一般的,InterBase的存储过程分为两大类,它是根据客户端在调用存储过程时使用的不同方法划分的。一类是选择式存储过程,它返回一个数据集,客户端使用select语句调用存储过程,此时存储过程的作用好像和表、视图一样,选择式存储过程必须通过输出参数返回一个或多个数据行。另一类是执行式存储过程,它不返回数据集,客户端使用execute procedure来调用存储过程,这类存储过程大多数只执行一些动作而不返回数据行,当然它也可以返回输出参数信息。
大家一定要注意,InterBase不存在这样的存储过程,它既返回数据集,同时又返回输出参数,你只能选择其一,不可能两者兼得,否则就会出错。
1、创建存储过程的语法
注意:在存储过程中,除了Create Procedure,As,Begin…End语句之外,任何其他语句末尾都要添加分号结束。因此,如果你使用isql创建存储过程,你必须另外定义其它的符号来代表创建存储过程的结束,通常使用set term语句完成。比如,在创建存储过程之前,使用set term !! ;把!!作为分号来表示存储过程创建的结束,在创建存储过程的语句之后,再用set term ; !!恢复分号的作用。如: Set Term ## ; 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 Return; End ##
2、定义局部变量
定义局部变量的语法是:
DECLARE VARIABLE var datatype
再次强调一遍:每一个局部变量都必须使用上述语法进行定义,而不能一次定义多个局部变量。
特别注意:不论是输入参数、输出参数还是局部变量,如果在SQL语句中使用,前面必须加上冒号。如果不和SQL语句一起使用,则不要加冒号。
3、SUSPEND、END、EXIT语句:
SUSPEND语句在选择式存储过程中,暂时挂起存储过程的执行,并经控制权交给调用程序,直到调用程序发出下一个FETCH命令后,接着从SUSPEND下一条语句继续执行。SUSPEND同时也将存储过程的输出参数值传递给调用程序。让我们来详细地解释SUSPEND的这个功能:
当你在存储过程中使用select…into语句通过输出参数返回一个或多个数据行时,调用该存储过程的程序到底是如何获得这些数据行的呢?实际上,它采用的方法就是将存储过程返回的结果集当作一个游标来对待,存储过程的各个输出参数作为游标的字段列,然后使用FETCH命令一次从该游标取一行数据直到全部取完为止。在存储过程中使用SUSPEND,就是告诉调用程序应该用FETCH命令取数据行了。因此,SUSPEND不仅仅用在FOR SELECT语句中,任何时候当存储过程需要向调用程序返回值时,都要用SUSPEND,这样可以防止存储过程终止直到调用程序获得了返回值。这就是SUSPEND在存储过程中应用的本质。
在选择式存储过程中,SUSPEND之后的所有可执行语句均被执行,尽管此时已经没有数据行要返回。
SUSPEND语句并不被推荐在执行式存储过程中使用,因为这类存储过程一般并不返回数据行,不存在上述的游标,无需FETCH任何数据行,所以在执行式存储过程中跟在SUSPEND语句之后的所有语句都不会被执行,就像提前退出存储过程一样。InterBase强烈建议在执行式存储过程中使用EXIT来代替SUSPEND。
EXIT不论在选择式还是执行式存储过程中,其作用都是将程序执行
语句上。在选择式存储过程中,EXIT相当于提前退出存储过程的执行。
4、在存储过程中使用异常
在存储过程中激发异常,使用EXCEPTION name;其中,name是已经创建的异常名称。例如: IF (any_sales > 0) THEN EXCEPTION REASSIGN_SALES;
当异常激发时,如果没有When…Do对之进行处理,它将: ·
终止存储过程的执行,撤销存储过程所做的一切动作,包括直接的和间接的。
·向调用存储过程的程序返回异常定义的逻辑错误信息。
可见,异常特别适合用在根据一定的条件终止操作的执行并回退的情况,因此,对于实现商业逻辑具有十分重要的意义。
存储过程的结构:
CREATE PROCEDURE ProcedureName <parameter list>
RETURNS <return parameter list>
AS <local variable declarations>
BEGIN
<body of procedure>
END
1、BEGIN…END:
定义一个复合语句,BEGIN表示复合语句的开始,END代表复合语句的结束。
2、variable=expression:
赋值语句。将表达式的值赋给变量、局部变量、输入、输出参数。
再如:
Declare Variable X Integer;
Declare Variable Y Integer;
Declare Variable Z Integer;
上面的语句定义了3个局部变量,下面给它们赋值:
x = 9;
y = 2 * x;
z = 4 * x / (y - 6);
注意:在InterBase存储过程和触发器语言代码中,除了上面提到的复合语句Begin、End之外,所有的语句必须以分号结束,这一点必须明确。
3、/*…..*/:
用来在程序内注释。注释可以跨多行,但一个注释内不能嵌套另一个注释。
4、exception exception_name:
用来在存储过程或触发器中激发一个异常。例如:
Create Exception Myexception ’这是一个异常’;
激发异常: Exception Myexception;
再如:
Create Exception Reassign_Sales ’Reassign The Sales Records Before Deleting This Employee.’
If (Any_Sales > 0) Then
Exception Reassign_Sales;
5、execute procedure proc_name[var[,var…]][returning_values var[,var…]]:
执行一个存储过程,带有输入、输出参数。允许存储过程的嵌套和递归。
6、EXIT:
跳到存储过程的最后一个END语句,结束存储过程的执行。
7、单行选择语句Select…Into:
将选择的结果存储到Into之后的变量中,into子句必须放在整个选择语句的末尾。
例如:
Select Count(*) from Employee Into :varcount;
将Employee表中数据行数返回给局部变量varcount。
8、多行选择语句FOR select_statement DO compound_statement: 对select_statement
返回的每一条记录重复执行compounde_statement语句,实际是一个循环结构。其中:select_statement是必须带有into子句的选择语句,而且into子句必须放在选择语句的末尾,而且后面不能跟分号。使用该语句可以返回多个数据行。语法结构是:
For select_statement
Do
Compound_statement
例如,下面的存储过程返回多个数据行,并且实现了其它数据库的SELECT TOP-N …的功能:
CREATE PROCEDURE SP_Select_TopN_Orders (WhichCust INTEGER, HowMany SMALLINT )
RETURNS ( WhichOrd INTEGER, WhenSold DATE, HowBig FLOAT)
AS
DECLARE VARIABLE i SMALLINT;
BEGIN
i = 0;/*局部变量,控制返回的数据行数*/
FOR SELECT OrderID, SaleDate, TotalInvoice
FROM Orders
WHERE CustomerID = :WhichCust
ORDER BY TotalInvoice DESC
INTO :WhichOrd, :WhenSold, :HowBig/*提取信息到输出参数中*/
DO
BEGIN SUSPEND;/*返回输出参数*/
i = i + 1;/*行数加1*/
IF ( i = :HowMany ) THEN
EXIT;/*达到规定的行数退出*/
END
END
9、compound_statement:
单一语句或用BEGIN…END括起来的复合语句,要注意END之后没有分号。
10、IF (condition) THEN compound_statement[ELSE compound_statement]:
条件语句。如果condition条件为真,执行THEN之后的语句,否则,执行下一条语句或ELSE之后的语句。
11、POST_EVENT event_name:
发送一个事件。事件是InterBase一种异步消息机制,通过事件可以向客户端返回需要的信息。
12、SUSPEND: 暂时终止即挂起存储过程的执行,并将参数返回给客户端。这是存储过程专用的语句。
13、WHILE condition DO compound_statement:
当condition条件为真时,重复执行compounde_statement语句,直到condition条件为假时退出
14、WHEN{error[,error…]|ANY} DO compound_statement:
错误处理语句。当指定的错误之一出现时,执行compound_statement语句。如果使用了WHEN,那么必须将其放在整个存储过程或触发器的最后一个语句。error可以是异常、SQLCODE错误码、GDSCODE代码。ANY表示处理任何错误。