INFORMIX存储过程编写.docx
《INFORMIX存储过程编写.docx》由会员分享，可在线阅读，更多相关《INFORMIX存储过程编写.docx（17页珍藏版）》请在冰豆网上搜索。
INFORMIX存储过程编写
INFORMIX存储过程开发指南
周兴华
2002-07-09
一、开发前环境准备
1、正确运行的INFORMIX数据库实例环境;
2、建立能访问数据库的用户,用户需要有CONNECT和RESOURCE权限;
3、建议有独立的测试数据库;
4、创建存储过程的数据库需要是日志数据库;
5、一个习惯的文本编辑器;（建议
6、FTP工具（若文本编辑器带FTP功能,则该项不要（建议
二、基本操作
1、检查数据库状态
条件:
操作用户可以操作INFORMIX实例;（可以直接使用informix用户
命令:
onstat-
结果:
该命令输出中包含”On-Line“字样,表示数据库实例启动正常;
2、dbaccess工具的使用
工具的具体使用见附件Int1.pdf;
3、根据错误号查找错误信息:
在命令提示下键入命令:
finderrerr_num
三、编写存储过程基础
1、常用数据类型
类型说明
char（nn个字节的字符串1<=n<=32767,默认为1
varchar（n可变长字节的字符串,最大长度为n个字节
smallint2字节整数
integer4字节整数
int88字节/64位平台
money存储空间为:
精度/2+1,
decimal（m,n存储空间为:
精度/2+1,decimal（m表示浮点数,float与具体机型相关,一般8个
real4字节,smallfloat
serial4字节（自动增长
date日期,4字节,默认显示格式mm/dd/yyyy
datetime日期时间,包含7个字段
YEAR/MONTH/DAY/HOUR/MINUTE/SECOND/FRACTION
blob任意种类的二进制数据,最大4TB
clob任意文本数据,最大4TB
booleanTRUE（'t'/FALSE（'f'/NULL
2、常用操作语句
1数据定义语句:
用于创建数据库并定义其结构。
2数据操作语句:
用于数据库中选择、插入、更新或删除数据
3指针操作语句:
用于指针操作（打开、读取、关闭一个指针
4动态管理命令:
用于动态管理运行过程中的资源。
5数据访问语句:
用于确定如何访问数据（DCL语句
6数据完整性语句:
用于维护数据完整性。
7查询优化信息语句:
用于获取有关查询执行的信息。
8存储过程语句:
用于执行和调试存储过程。
9辅助语句:
Informix产品附加的SQL语句。
3、常用函数:
见附件:
常用函数
4、存储过程基本语法:
1创建存储过程
createprocedureproc_name（[....in_parameter_list]
returningout_para_list/out_result_set;
2删除存储过程
dropprocedureproc_name;
3存储过程存放
存储过程信息放在sysprocedures系统目录表中,权限存放再sysprocauth表中。
sysprocbody表存放每个存储过程的文本、编译伪代码、等信息。
sysprocplan表存放查询执行计划和存储过程中引用的表格之间的相关性清单。
4执行存储过程
。
是标准SQL语言,通过数据库访问语言直接运行:
executeprocedureproc_name（in_para_list
returningout_para_list/out_result_set;
。
在存储过程中调用;
callproc_name（in_para_listreturningout_para_list/out_result_set;
或letlocal_variable=my_spl（"argumeny_1;
调用不同数据库的存储过程:
executeproceduredatabase:
my_sp1（;
调用远程数据库不同网络节点:
executeprocedure
database@sitename:
my_sp1（;
5、其他常用语句
1分支
if...then
...
elif...then
...
else
...
endif
2循环（三种
。
for
fori=1to10
...
endfor（没有分号
。
while
while...
...
endwhile（没有分号
。
foreach
foreachselectcol1,..intoval1,...
fromtabnamewhere...
....
endforeach（没有分号
3循环控制语句:
。
continue:
条过余下的循环体,启动下一轮循环;
。
EXIT:
终止循环;
。
RETURN:
完成退出存储过程;
。
RAISEEXCEPTION:
退出,在循环外捕获错误;
例子:
foreachselectcol1,..intoval1,...
fromtabnamewhere...
....
if...then
continueforeach;
endif;
...
fori=1to10
...
if...then
exitfor;
endif;
endfor;
while...
if...then
return...;
endif;
endwhile;
endforeach（没有分号
4分块:
BEGIN和END必须成对出现
...
BEGIN
onexcewption
....
endexception
begin
....
end;
END;
四、SELECT语句
1、WHERE子句的关键字:
1BETWEEN:
指定数值范围;
2IN:
指定数值表;
3LIKE:
指定通配符文本查询;
4MATCHES:
指定通配符文本查询;
5ISNULL:
查询NULL值;
6NOT:
否定查询结果;
2、WHERE子句的操作符
1=;2!
=不等于3<>不等于4>大于5>=大于或等于;
6<:
小于;7<=:
小于或等于;
eg1:
select*fromtablename
wherecol1[not]betweenval1andval2;
eg2:
select*fromtablename
wherecol1[not]in[val1,val2,...valn];
3、字符值搜索
1子串:
字符列名后用[],指定自串的开始和结束位置;如COL1[1,5];
注:
选择不是从第一个字符开始的子串时,不能使用该列的索引;并且消耗资源多;
第一个数字代表开始位置;第二个数字代表终止位置,该数字不能大于列长度;
2LIKE:
。
%:
代表0个或多个字符;
。
下画线（_代表一个字符;可以连续使用多个下画线代表多个字符;
。
搜索原字符（%,_,需使用转意符（\;例如:
select*fromtablenamewherecol1like"%\%%":
查询带%的内容;
注:
。
LIKE关键字是ANSI标准,方便移植;MATCHES功能有扩展;
。
大小写有区别;
3MATCHES:
根据通配符搜索字符列。
通配符与LIKE不同.（注:
该命令大小写有区别
。
*:
代表0个或多个字符;
。
?
:
代表一个字符;
。
能对一个字符位指定字符范围和字符表,利用[]实现。
（该功能LIKE没有;若要
指定字符范围,可以使用连字符（-分开两个字符;例如:
查询从A到L字母开始
的内容:
select*fromtablenamewherecol1matches"[A-L]*";
。
可以在前面加上插入符（^否定一个范围的结果,例如:
查询排除A到L字母开头
的内容:
select*fromtablenamewherecol1matches"[^A-L]*";
。
可以省略连字符,用[]指定字符表,将每个字符放在[]中,例如:
查询以A、B、C开头的信息;
select*fromtablenamewherecol1matches"[ABC]*";
。
前面加上插入符（^可以否定查询结果,例如:
上例取反:
select*fromtablenamewherecol1matches"[^ABC]*";
。
根据字母组合选择值:
例如查询COL1中任何地方带‘ABC或abc’的信息:
select*fromtabnamewherecol1matches"*[aA][bB][cC]*"
说明:
由于区分大小写,所以要对每一位指定大小写;
select*fromtabnamewherecol1matches"[A-Ls]*"
查询COL1中以A-L和s开头的信息;
。
若要使用元字符（*和?
则需要使用转意符（\,例如:
查询COL1中有*的信
息:
select*fromtabnamewherecol1matches"*\**"
4、NULL处理
1列的值未知时,使用NULL值标识。
2NULL值不同于0或空格,使用IS[NOT]NULL查询未知值或NULL值;
5、歧义列名
1指定多个表中都存在的列名,需要加上表名限制,例如:
selectcol1,col2,col3fromt1,t2wheret1.col1=t2.col1--错误,因COL1没限制
selecta.col1,a.col2,b.col3fromt1a,t2bwherea.col1=b.col1;
5、外连接
1返回主表的全部满足条件的记录（不管从表是否有匹配和从表的匹配记录（相交记
录;
2考虑点:
。
一个表是主表,一个是从表;
。
取得主表的所有记录,不管从表是否有匹配;
。
从表名放在OUTER关键字后面;
。
若从表没有符合连接条件的记录,则从表中的列未NULL值;
。
若指定外连接,则连接条件放在WHERE子句中;
例子:
selecta.col1,a.col2,b.col3fromt1a,outert2b
wherea.col1=b.col1;
6、用括号嵌套简单连接（简单连接
例子:
selecta.col1,col2,b.col3,col4,col5fromt1a,OUTER（t2b,t3c
wherea.col1=b.col1
andb.col2=c.col2
andcol5='abc';（COL5在T3表中
1例子中首先将T3连接到T2,查询COL5=‘ABC’的内容;
2然后将结果于表T1连接,取得最后结果;（是外连接
3OUTER关键字用于外连接T2和T3表的连接结果;
7、用括号嵌套外连接
例子:
selecta.col1,col2,b.col3,col4,col5
fromt1a,OUTER（t2b,OUTERt3c
wherea.col1=b.col1
andb.col2=c.col2
andcol5='abc';（COL5在T3表中
注:
T2与T3是外连接,T1与（T2和T3的外连接结果是外连接;
8、GROUPBY子句和HAVING子句
1例:
selecttabname[1,3],count（*ascntfromsystablesgroupbytabname[1,3]
2HAVING子句对一组采用过滤条件,通常与GROUPBY子句一起使用,指定每个组的
过滤条件;
selecttabname[1,3],count（*ascnt
fromsystablesgroupbytabname[1,3]
havingcount（*>1
9、ORDERBY子句
1指定返回结果顺序;
2该子句中列出一个或多个列;优先级依次降低;
3ASC指定升序,DESC执行降序;
10、INTOTEMP子句
1生成临时表,在对话期间存在;
2可以用DROPTABLE显示删除;
3下列条件来临时表自动删除:
1、退出程序时2、关闭当前数据库时3、切断当前数据库连接时。
五、日期时间操作
1、数据类型:
date,datetime;
2、当前日期时间:
current;
3、当前日期:
today;
4、相关函数:
参见附件“常用函数”
5、例子:
1当前日期时间:
letcur_dtime_var=current;--datetime
2当前日期:
letcur_date_var=today;--date
3日期加减:
。
lettmp_date=today+3UNITSday--当前时间加三天
。
lettmp_date=today+interval（7daytoday--当前时间加上7天
4日期/时间转换成字符串:
defineschar（20;
defineydatetimeyeartosecond;
lety=current;
lets=year（y||month（y||day（y
||hour（y||extend（y,minutetominute
||extend（y,secondtosecond;
5字符串转换成日期/时间:
definedtdatetimeyeartosecond;
letdt=EXTEND（DATETIME（2002-07-0901:
02:
03YEARTOsecond,YEARTOsecond;
六、游标操作
1、定义
foreachcur_namefor
select....into....from....where.....
....
endforeach;
2、例子
definev1integer;
definev2datetimeyeartosecond;
definev3varchar（10;
definev4varchar（20;
FOREACH
selectcol1,col2,col3,col4intov1,v2,v3,v4
fromt1
RETURNv1,v2,v3,v4WITHRESUME;
ENDFOREACH;
七、事务
1、语法:
beginwork;
.....
commitwork/rollbackwork;
2、说明:
1必须在日志数据库中,才能使用事务,否则提示错误;
2事务中的操作不能过多,否则可能造成长事务被取消或造成死锁;
八、异常处理方法
方法1:
在每次操作后进行判断。
缺点:
该方法操作烦琐,效率低下;
优点:
能准确定位问题出处;
方法2:
使用数据库提供的异常保护功能:
onexception
....;
endexception;
缺点:
不能准确定位问题的出处;
优点:
操作简单、高效;
使用建议:
根据实际情况选择,往往两者灵活结合使用效果最好;
九、存储过程跟踪方法
方法1、将跟踪内容写入表中,过程执行完成后查询;
方法2、在过程中直接返回跟踪对象的内容;
方法3、使用trace功能,详细使用参见后面的例子;
十、存储过程加载
方法一:
使用dbaccess工具。
使用NEW菜单项中直接输入或拷贝存储过程语句,再使用
RUN功能运行;
缺点:
由于BUFFER大小有限,存储过程语句不能过多,超过部份自动截断;
方法二:
使用dbaccess工具,使用choose功能直接选择编辑好的SQL文件,再使用RUN功
能
运行。
方法三:
使用工具dbaccess,直接加载SQL脚本:
dbaccessdb_namefile_name.sql
十一、例子
1、用存储过程中返回一个或多个值
createprocedureproc_test（returningint,datetimeyeartosecond,varchar（10;definev1integer;
definev2datetimeyeartosecond;
definev3varchar（10;
letv1=1;
letv2=current;
letv3="test";
returnv1,v2,v3;
endprocedure;
2、用存储过程中返回一条记录
预设条件:
表T1,字段col1int,col2datetimeyeartoday,col3char（10;
createprocedureproc_test（returningint,datetimeyeartosecond,varchar（10;definev1integer;
definev2datetimeyeartosecond;
definev3varchar（10;
selectcol1,col2,col3intov1,v2,v3
fromt1
wherecol1=1;--col1为关键字,满足该条件的记录只有一条;
returnv1,v2,v3;
endprocedure;
3、用存储过程中返回多条记录（游标--cursor
预设条件:
表T1,字段col1int,col2datetimeyeartoday,col3char（10;
createprocedureproc_test（returningint,datetimeyeartosecond,varchar（10;definev1integer;
definev2datetimeyeartosecond;
definev3varchar（10;
foreach
selectcol1,col2,col3intov1,v2,v3
fromt1;
returnv1,v2,v3withresume;
endforeach;
endprocedure;
4、将日期转换为字符
createprocedurep2（returningvarchar（8;
definesvarchar（8;
definec_yearchar（4;
definec_monthchar（2;
definec_daychar（2;
letc_year=year（today;
letc_month=month（today;
letc_day=day（today;
iflength（c_month<2then
letc_month='0'||c_month;
endif;
iflength（c_day<2then
letc_day='0'||c_day;
endif;
lets=c_year||c_month||c_day;
returns;
endprocedure;
5、使用事务
实现功能:
将表1中的一条记录移到另一张表2中
假设:
两张表的字段相同,都只有两个字段（col1integer,col2varchar（10
createprocedureproc_trans（returninginteger;
definecol1_valinteger;
definecol2_valvarchar（10;
selectcol1,col2intocol1_val,col2_valfromt1wherecol1=1;
ifdbinfo（'sqlca.sqlerrd2'<1then
return9;--记录不存在
endif;
beginwork
insertintot2（col1,col2values（col1_val,col2_val;
ifdbinfo（'sqlca.sqlerrd2'<1then
rollbackwork;
return1;--插入操作失败
endif;
deletefromt1wherecol1=1;
ifdbinfo（'sqlca.sqlerrd2'<1then
rollbackwork;
return1;--删除操作失败
endif;
commitwork;
return0;--操作成功
endprocedure;
6、异常处理
1事务中每步关键操作进行跟踪处理,参见例5;
2使用数据库的提供的异常保护功能:
例5重写:
createprocedureproc_trans（returninginteger;
definecol1_valinteger;
definecol2_valvarchar（10;
onexception
rollback;
return99;--数据库操作异常
endexception;
selectcol1,col2intocol1_val,col2_valfromt1wherecol1=1;
ifdbinfo（'sqlca.sqlerrd2'<1then
return9;--记录不存在
endif;
beginwork
insertintot2（col1,col2values（col1_val,col2_val;
ifdbinfo（'sqlca.sqlerrd2'<1then
rollbackwork;
return1;--插入操作失败
endif;
deletefromt1wherecol1=1;
ifdbinfo（'sqlca.sqlerrd2'<1then
rollbackwork;
return2;--删除操作失败
endif;
commitwork;
return0;--操作成功
endprocedure;
7、调试跟踪
createprocedureproc_trans（returninginteger;
definecol1_valinteger;
definecol2_valvarchar（10;
--设置异常处理
onexception
rollback;
return99;--数据库操作异常
endexception;
--设置跟踪模式
setdebugfileto"trace_check";--withappend;
--说明“withappend”表示以追加模式打开跟踪结果文件
traceprocedure;--on--全部;procedure--部分
--说明选项"on"表示跟踪所有内容;
--选项"procedure"表示只跟踪指定部份内容;
selectcol1,col2intocol1_val,col2_valfromt1wherecol1=1;
ifdbinfo（'sqlca.sqlerrd2'<1then
return9;--记录不存在
endif;
--跟踪查询结果值;
trace
升级会员 