informix的存储过程


1、简介
存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象,任何一个设计良好的数据库应用程序都应该用到存储过程。
存储过程是由流控制和SQL语句书写的过程,这个过程经编译和优化后存储在数据库服务器中,应用程序使用时只要调用即可。
2、重要概念和疑问解答
1)何谓informix中的SPL
SPLStored Procedure Language)是informix中提供的一种提供流程控制(分支和循环)的SQL。它包括SPL过程(没有返回值)和SPL函数(有返回值)。SPL将在创建的时候被解析和优化,它以可执行的方式保存在system catalog表中。
2
)使用SPL为什么会改善性能?
i)因为它运行在informix引擎中,所以减少了I/O
ii
)降低了应用的复杂性;
iii
)对频率较高的重复操作使用更好。
3
SPL的灵活性表现在哪些部分?
i)它可在其它SQL语法中被调用;
ii
)它也可以在触发器中使用;
iii
)可在SQL中添加流程控制;
iv
)很容易维护。
4
SPL的参数
i)可以传递很多个参数;
ii
SPL的参数的最大限制是32K
iii
)除了如下两种外可以是任何一种SQL数据类型:
Serial/Serial8
Text/Byte (
能够使用REFERENCES关键字传递)
iv
)能够使用很复杂或者用户自定义的数据类型;
v
)可以通过default为参数指定默认值。
5
SPL函数的返回值
i)可以使用RETURNINGRETURNS关键字定义返回值的返回类型;
ii
)除了如下两种数据类型外可以为任何数据类型:
Serial/Serial8
Text/Byte (
可以使用REFERENCES关键字传递)
iii
)在过程体内必须至少有一个return语句;
iv
)可以为返回值取一个名称。例如:

CREATE FUNCTION val_comp (val1 INTEGER, val2 INTEGER)
RETURNINGINTEGER AS comp_res;

6SPL的重载
可定义多个具有相同名字的SPL函数;例如如下SPL函数虽然名称相同,但是参数类型或各不相同:

CREATE FUNCTION val_comp (val1 INTEGER, val2 INTEGER)
CREATE FUNCTION val_comp (val1 CHAR(25), val2 CHAR(25))
CREATE FUNCTION val_comp (val1 DECIMAL, val2 DECIMAL)

可使用SPECIFIC关键字指定SPL函数的简短的、独一无二的被重载的SPL的函数的名称。这个名字在数据库中是唯一的。例如:

CREATE FUNCTION val_comp (val1 INTEGER, val2 INTEGER) SPECIFIC int_comp
CREATE FUNCTION val_comp (val1 CHAR(25), val2 CHAR(25)) SPECIFIC string_comp
CREATE FUNCTION val_comp (val1 DECIMAL, val2 DECIMAL) SPECIFIC dec_comp

7)何谓语句块?
i)语句块是一组SPLSQL语句;
ii
)隐式的语句块包含在CREATE PROCEDURE/FUNCTIONEND PROCEDURE/FUNCITON之间;
iii
)可使用BEGINEND来显式的知名内嵌在另一个语句块中的语句块,例如:

CREATE PROCEDURE myproc1() -- 隐式的语句块的开始
DEFINE x INT;
LETx = 15;
INSERT INTO table1 VALUES (x, 'amigo');
BEGIN -- 显式的语句块的开始
DEFINE y INT;
LETy = 16;
INSERT INTO table1 VALUES ( y, 'xingxing');
END -- 显式的语句块的结束
END PROCEDURE; -- 隐式的语句块的结束

8)如何定义变量?
i)是语句块中使用DEFINE定义变量;
ii
)变量在内存中,而不是在数据库中保存;
iii
)有本地变量和全局变量两种;
iv
)变量可以是除了如下两种类型之外的任何一种SQL数据类型和扩展类型:
Serial/Serial8
Text/Byte (
可以使用REFERENCES关键字来声明)
定义变量举例如下:

DEFINE x, y INT; -- 内建的数据类型
DEFINE p person_type; -- 用户自定义的数据类型person_type
DEFINE mymusic REFERENCES BYTE; -- 使用REFERENCES关键字定义BYTE类型

9)本地变量和全局变量比较
本地变量:
i)本地变量值在SPL中有效;
ii
)在SPL函数或过程结束后就被复位;
iii
)它不能有默认值;
iv
)它的作用域是在它定义的语句块中,或者任何内嵌语句块中;
v
)本地变量能够被重新定义在另一个语句块中。
下面来看一个本地变量的实例:

CREATE PROCEDURE local_scope()
DEFINEx,y,z INT;
LETx = 5;
LETy = 10;
LETz = x + y; -- z等于15
BEGIN
DEFINEx, q INT; -- x被重新定义
DEFINE z CHAR(5); -- z被重新定义
LET x = 100;
LETq = x + y; -- q=110
LET z = 'amigo'; -- z设置了一个新的值
END
LETy = x; -- y等于5
LET x = z; -- z的值是15,而不是amigo
END PROCEDURE;

全局变量:
i)在相同数据库中使用相同的用户会话的地方都能得到全局变量的值;
ii
)必须有一个默认值;
iii
)必须在每一个要使用它的SPL函数或过程中定义;
iv
)不能是一个集合变量。
例如如下定义了两个SPL函数,名称分别为func1func2func1参考语句如下:

CREATE FUNCTION func1() RETURNING INT;
DEFINEGLOBAL gvar INT DEFAULT 2;
LETgvar = gvar + 1;
RETURN gvar;
END FUNCTION;

func2参考语句如下:

CREATE FUNCTION func2()RETURNING INT;
DEFINEGLOBAL gvar INT DEFAULT 5;
LETgvar = gvar + 1;
RETURN gvar;
END FUNCTION;

如果执行两者的顺序如下:

EXECUTE FUNCTION func1();
EXECUTE FUNCTION func2();

则执行完第一句后,gvar被设置了默认值2,且执行了加1的操作,所以第一句执行完毕后gvar的值为3.
接着执行第二句,第这次不在设置gvar的默认值,因此在3的基础上再执行了加1操作,执行完毕后gvar的值为4.
若执行两者的顺序如下:

EXECUTE FUNCTION func2();
EXECUTE FUNCTION func1();

则执行完第一句后,gvar被设置了默认值5,且执行了加1的操作,所以第一句执行完毕后gvar的值为6.
接着执行第二句,第这次不在设置gvar的默认值,因此在6的基础上再执行了加1操作,执行完毕后gvar的值为7.
10
)给变量赋值
i)使用一个未定义的变量将会报错;
ii
)给已定义的变量赋值的方法:
*
使用LET语句直接给变量赋初值,参考语句如下:

LET <变量> = <有效的表达式或函数名>;

*使用SELET INTO语句将查询到的结果给变量赋值,参考语句如下:

SELECTINTO <变量> FROM …;

*使用CALL...RETURNING语句将返回结果赋给变量,参考语句如下:

CALL … RETURNING <变量>;

*使用EXECUTE FUNCTIONINTO语句,将返回结果赋给变量,参考语句如下:

EXECUTE FUNCTIONINTO <变量>;

3、语法
1)创建SPL过程

CREATE PROCEDURE name (parameter list) SPECIFIC specific_name
…{语句块}
END PROCEDURE;

例如,在下面的实例中创建了一个名为set_status的存储过程,可传入myidmystatus两个参数,在这个存储过程中,对item_inventory表进行update操作,id字段满足myid的记录将status字段更新为mystatus。参考语句如下:

CREATE PROCEDURE set_status (myid INTEGER DEFAULT 0, mystatus CHAR(25))
UPDATE item_inventory SET status = mystatus WHERE id = myid;
END PROCEDURE;

2)创建SPL函数

CREATE FUNCTION name (parameter list)
RETURNINGlist SPECIFIC specific_name
…{语句块}
END FUNCTION;

例如,创建一个名为val_compSPL函数,可传入val1val2两个参数,在该函数中,比较这两个变量,如果两者相等,返回0,否则返回1。参考语句如下:

CREATE FUNCTION val_comp (val1 INTEGER, val2 INTEGER)
RETURNINGINTEGER;
DEFINEres INTEGER;
IF (val1 = val2) THEN
LETres = 0;
ELSE
LETres = 1;
END IF;
RETURN res;
END FUNCTION;

4SPL中的流程控制
1
)分支语句if...then...else...end if
参考实例:

IF ( condition ) THEN
statements
ELIF( condition ) THEN
statements

ELSE
statements
END IF;

2)分支语句case
参考实例:

CASE ( condition )
WHEN <value1> THEN
statements
WHEN <value2> THEN
statements

ELSE
statements
END CASE;

3)循环语句for
参考语法如下:

FOR 变量 IN ( 范围或值 )

或者使用如下语法:

FOR 变量=范围

举例如下:

FOR count = 2 TO 30
FOR count = 2 TO 30 STEP 2
FOR count IN ( 2, 5, 7, 9)
FOR count IN ( 2 to 8 STEP 2, 12 to 19, 22)
FOR name IN ("AMY", "MAX",
(SELECT name FROM customer WHERE customer_num = 100) )

4)循环语句while
参考语法如下所示:

WHILE (条件表达式)
执行的语句
END WHILE;

while中的条件表达式的举例如下:

WHILE (count < 20)
WHILE (status matches "A*")
WHILE (EXISTS (SELECT name FROM customer WHERE cus_num=100))
WHILE (status IN ("A", "I","D"))

5)循环语句foreach
i)被用来取多行数据;
ii
)它打开了一个游标;
iii
)游标用来取得当前的行,以便进行行的更新或删除操作。
参考实例如下:

FOREACH SELECT salary INTO ind_sal
FROM customer
WHERE location = “UK"
sum_sal+= ind_sal;
END FOREACH;
RETRUNsum_sal;

6continueexit语句
都可以被使用在forforeachwhile循环语句中。
参考实例如下:

FOR i = 1 TO 5
LETj = i;
WHILE (j > 0)
LETid = foo(j);
IF (id = 5) THEN
LETj = j – 1;
CONTINUE WHILE; -- 不执行后续的操作,继续回到while循环接着执行
END IF;
LETsum = sum + 5;
LETj = j – 1;
IF (sum > 500) THEN
EXIT FOR; -- 退出for循环
END IF;
END WHILE;
END FOR;
RETURN sum;

5、异常处理
1ON EXCEPTION语句
ON EXCEPTION语句语句提供了异常的捕获和处理机制。在IN中指定要捕获的错误,并指定当异常发生时需要执行的动作。在一个语句块中允许有多个ON EXCEPTION语句。
ON EXCEPTION
语句必须在DEFINE语句之后,并在在任何可执行的语句块之前。并且它在内嵌的语句块中也是有效的。它使用SET语句来接收SQLISAM错误码和错误信息。
参考实例如下所示:

CREATE PROCEDURE ex_test()
DEFINEsql_err INTEGER;
DEFINEisam_err INTEGER;
DEFINEerr_txt CHAR(200);
ON EXCEPTION IN (-206) SET sql_err, isam_err, err_txt
CREATE TABLE tab1 ( col1 INT, col2 INT);
INSERT INTO tab1 VALUES (1, 2);
INSERT INTO tab1 VALUES (2, 3);
END EXCEPTION
INSERT INTO tab1 VALUES (1, 2); --如果tab1不存在时,跳到异常处理
INSERT INTO tab1 VALUES (2, 3);
END PROCEDURE;

2WITH RESUME语句
该语句在存储过程发生错误时恢复语句。
如下实例展示了WITH RESUME的使用,当异常处理完毕后,继续后续的语句处理。参考语句如下:

CREATE PROCEDURE ex_test()
ON EXCEPTION IN (-206)
CREATE TABLE tab1 (col1 INT, col2 INT);
INSERT INTO tab1 VALUES (1, 2);
END EXCEPTION WITH RESUME;
INSERT INTO tab1 VALUES (1, 2); -- 如果tab1不存在,跳到异常处理
INSERT INTO tab1 VALUES (2, 3); -- 异常处理完成后,继续该句进行处理
INSERT INTO tab1 VALUES (3, 4);
END PROCEDURE;

3RAISE EXCEPTION语句
i该语句用来创建错误;
ii
)它能够用来指定SQL错误、ISAM错误和错误信息;
iii
)用该语句创建的错误,能够被ON EXCEPTION捕获;
iv
)可以使用指定的错误码-746来表示自定义的错误消息。
如下实例使用ON EXCEPTION语句指明了当发生错误时将错误码、错误信息插入到自定义的错误表my_error_table,当传入的参数小于1时,抛出自定义的-746异常,其余情况成功将数据插入到tab1表中,参考语句如下:

CREATE PROCEDURE ex_test5 (a INT)
DEFINEsql_err INTEGER;
DEFINEisam_err INTEGER;
DEFINEerr_txt CHAR(200);
ON EXCEPTION IN (-746) SET sql_err, isam_err, err_txt
INSERT INTO my_error_table values (sql_err, isam_err, err_txt);
END EXCEPTION;
IF (a < 1) THEN
RAISEEXCEPTION -746, 0, "插入值必须大于0";
END IF;
INSERT INTO tab1 VALUES (1, a);
END PROCEDURE;

6、执行SPL函数或存储过程
可使用EXECUTE PROCEDURE语句来执行SPL存储过程,使用EXECUTE FUNCTION来执行SPL函数;
参考实例1

EXECUTE PROCEDURE foo();

参考实例2

CREATE FUNCTION func1() RETURNING INT
DEFINEmyvalue INT;
CALLfoo() RETURNING myvalue;
INSERT INTO table1 VALUES (myvalue);
RETURN myvalue;
END FUNCTION;

参考实例3

SELECT * FROM table2 WHERE id =get_id("amigo");

参考实例4

UPDATE table2 SET col2=foo() WHERE id=1;

7、删除SPL函数或存储过程
1)使用DROP PROCEDURE来删除SPL存储过程;
2
)使用DROP FUNCTION来删除SPL函数;
3
)使用DROP ROUTINE来删除SPL存储过程或SPL函数。
几个参考实例如下:

DROP PROCEDURE foo;
DROP PROCEDURE foo (INTEGER);
DROP SPECIFIC PROCEDURE foo_int;
DROP FUNCTION foo_ret;
DROP FUNCTION foo_ret (INTEGER);
DROP SPECIFIC FUNCTION foo_ret_int;
DROP ROUTINE foo;
DROP ROUTINE foo_ret;

 

 

 

http://www.blogjava.net/amigoxie/archive/2010/11/05/337364.html

  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值