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、根据错误号查找错误信息：在命令提示下键入命令： finderr err_num 三、编写存储过程基础 1、常用数据类型 类型 说明 char(n) n个字节的字符串 1=n=32767,默认为1 varchar(n) 可变长字节的字符串，最大长度为n个字节 smallint 2字节整数 integer 4字节整数 int8 8字节/64位平台 money 存储空间为：精度/2+1 , decimal(m,n) 存储空间为：精度/2+1 ,decimal(m)表示浮点数， float 与具体机型相关,一般8个 real 4 字节,smallfloat serial 4 字节（自动增长） date 日期,4字节，默认显示格式mm/dd/yyyy datetime 日期时间，包含7个字段YEAR/MONTH/DAY/HOUR/MINUTE/SECOND/FRACTION blob 任意种类的二进制数据，最大4TB clob 任意文本数据，最大4TB boolean TRUE(t) /FALSE(f) /NULL 2、常用操作语句 1）数据定义语句：用于创建数据库并定义其结构。 2）数据操作语句：用于数据库中选择、插入、更新或删除数据 3）指针操作语句：用于指针操作（打开、读取、关闭一个指针） 4）动态管理命令：用于动态管理运行过程中的资源。 5）数据访问语句：用于确定如何访问数据（DCL语句） 6）数据完整性语句：用于维护数据完整性。 7）查询优化信息语句：用于获取有关查询执行的信息。 8）存储过程语句：用于执行和调试存储过程。 9）辅助语句：Informix产品附加的SQL语句。 3、常用函数：见附件：常用函数 4、存储过程基本语法： 1）创建存储过程 create procedure proc_name( .in_parameter_list) returning out_para_list / out_result_set; 2）删除存储过程 drop procedure proc_name; 3）存储过程存放 存储过程信息放在sysprocedures系统目录表中，权限存放再sysprocauth表中。sysprocbody表存放每个存储过程的文本、编译伪代码、等信息。sysprocplan表存放查询执行计划和存储过程中引用的表格之间的相关性清单。 4）执行存储过程 。是标准SQL语言，通过数据库访问语言直接运行： execute procedure proc_name(in_para_list) returning out_para_list / out_result_set; 。在存储过程中调用； call proc_name(in_para_list) returning out_para_list / out_result_set; 或 let local_variable = my_spl(argumeny_1);调用不同数据库的存储过程: execute procedure database:my_sp1();调用远程数据库不同网络节点：execute procedure databasesitename:my_sp1(); 5、 其他常用语句 1）分支 if . then . elif . then . else . end if 2）循环(三种) 。for for i=1 to 10 . end for (没有分号) 。while while . . end while(没有分号) 。foreach foreach select col1,. into val1,. from tabname where. . end foreach(没有分号) 3）循环控制语句： 。continue：条过余下的循环体，启动下一轮循环； 。EXIT：终止循环； 。RETURN：完成退出存储过程； 。RAISE EXCEPTION：退出，在循环外捕获错误； 例子： foreach select col1,. into val1,. from tabname where. . if . then continue foreach; end if; . for i=1 to 10 . if . then exit for; end if; end for; while . if . then return .; end if; end while; end foreach(没有分号) 4）分块：BEGIN和END必须成对出现 . BEGIN on excewption . end exception begin . end; END；四、SELECT语句 1、WHERE子句的关键字： 1）BETWEEN：指定数值范围； 2）IN：指定数值表； 3）LIKE：指定通配符文本查询； 4）MATCHES：指定通配符文本查询； 5）IS NULL：查询NULL值； 6）NOT：否定查询结果； 2、WHERE子句的操作符 1）= ； 2）！= 不等于 3） 不等于 4） 大于 5）= 大于或等于； 6）：小于； 7）1 9、ORDER BY 子句 1）指定返回结果顺序； 2）该子句中列出一个或多个列；优先级依次降低； 3）ASC指定升序，DESC执行降序； 10、INTO TEMP 子句 1）生成临时表，在对话期间存在； 2）可以用DROP TABLE显示删除； 3）下列条件来临时表自动删除：1、退出程序时2、关闭当前数据库时3、切断当前数据库连接时。五、日期时间操作 1、数据类型：date , datetime; 2、当前日期时间：current; 3、当前日期：today; 4、相关函数：参见附件“常用函数” 5、例子： 1）当前日期时间： let cur_dtime_var = current; - datetime 2）当前日期：let cur_date_var = today; -date 3）日期加减： 。 let tmp_date = today + 3 UNITS day - 当前时间加三天 。let tmp_date = today + interval(7) day to day -当前时间加上7天 4）日期/时间转换成字符串： define s char(20); define y datetime year to second; let y = current; let s = year(y)|month(y)|day(y) |hour(y)|extend(y,minute to minute) | extend(y,second to second); 5）字符串转换成日期/时间： define dt datetime year to second; let dt = EXTEND(DATETIME(2002-07-09 01:02:03) YEAR TO second, YEAR TO second);六、游标操作 1、定义 foreach cur_name for select . into .from . where . . end foreach; 2、例子define v1 integer; define v2 datetime year to second; define v3 varchar(10); define v4 varchar(20); FOREACH select col1,col2,col3,col4 into v1,v2,v3,v4 from t1 RETURN v1,v2,v3,v4 WITH RESUME; END FOREACH;七、事务 1、语法： begin work; . commit work / rollback work; 2、说明： 1）必须在日志数据库中，才能使用事务，否则提示错误； 2）事务中的操作不能过多，否则可能造成长事务被取消或造成死锁；八、异常处理方法 方法1：在每次操作后进行判断。 缺点：该方法操作烦琐，效率低下； 优点：能准确定位问题出处； 方法2：使用数据库提供的异常保护功能： on exception .; end exception; 缺点：不能准确定位问题的出处； 优点：操作简单、高效； 使用建议：根据实际情况选择，往往两者灵活结合使用效果最好； 九、存储过程跟踪方法 方法1、将跟踪内容写入表中，过程执行完成后查询； 方法2、在过程中直接返回跟踪对象的内容； 方法3、使用trace功能，详细使用参见后面的例子；十、存储过程加载 方法一：使用dbaccess工具。使用NEW菜单项中直接输入或拷贝存储过程语句，再使用 RUN功能运行； 缺点：由于BUFFER大小有限，存储过程语句不能过多，超过部份自动截断； 方法二：使用dbaccess工具，使用choose功能直接选择编辑好的SQL文件，再使用RUN功能 运行。 方法三：使用工具dbaccess，直接加载SQL脚本： dbaccess db_name file_name.sql 十一、例子 1、用存储过程中返回一个或多个值 create procedure proc_test() returning int,datetime year to second,varchar(10); define v1 integer; define v2 datetime year to second; define v3 varchar(10); let v1 = 1; let v2 = current; let v3 = test; return v1,v2,v3; end procedure; 2、用存储过程中返回一条记录 预设条件：表T1，字段col1 int, col2 datetime year to day,col3 char(10); create procedure proc_test() returning int,datetime year to second,varchar(10); define v1 integer; define v2 datetime year to second; define v3 varchar(10); select col1,col2,col3 into v1,v2,v3 from t1 where col1=1; -col1为关键字，满足该条件的记录只有一条； return v1,v2,v3; end procedure; 3、用存储过程中返回多条记录（游标 - cursor） 预设条件：表T1，字段col1 int, col2 datetime year to day,col3 char(10); create procedure proc_test() returning int,datetime year to second,varchar(10); define v1 integer; define v2 datetime year to second; define v3 varchar(10); foreach select col1,col2,col3 into v1,v2,v3 from t1; return v1,v2,v3 with resume; end foreach; end procedure; 4、将日期转换为字符 create procedure p2() returning varchar(8); define s varchar(8); define c_year char(4); define c_month char(2); define c_day char(2); let c_year = year(today); let c_month = month(today); let c_day = day(today); if length(c_month)2 then let c_month = 0 | c_month; end if; if length(c_day)2 then let c_day = 0 | c_day; end if; let s = c_year | c_month | c_day; return s; end procedure; 5、使用事务 实现功能：将表1中的一条记录移到另一张表2中 假设：两张表的字段相同，都只有两个字段（col1 integer,col2 varchar(10) create procedure proc_trans() returning integer; define col1_val integer; define col2_val varchar(10); select col1,col2 into col1_val,col2_val from t1 where col1=1; if dbinfo(sqlca.sqlerrd2) 1 then return 9; -记录不存在 end if; begin work insert into t2 (col1,col2) values(col1_val,col2_val); if dbinfo(sqlca.sqlerrd2) 1 then rollback work; return 1; -插入操作失败 end if; delete from t1 where col1=1; if dbinfo(sqlca.sqlerrd2) 1 then rollback work; return 1; -删除操作失败 end if; commit work; return 0; -操作成功 end procedure; 6、异常处理 1）事务中每步关键操作进行跟踪处理，参见例5 ； 2）使用数据库的提供的异常保护功能：例5重写： create procedure proc_trans() returning integer; define col1_val integer; define col2_val varchar(10); on exception rollback; return 99; -数据库操作异常 end exception; select col1,col2 into col1_val,col2_val from t1 where col1=1; if dbinfo(sqlca.sqlerrd2) 1 then return 9; -记录不存在 end if; begin work insert into t2 (col1,col2) values(col1_val,col2_val); if dbinfo(sqlca.sqlerrd2) 1 then rollback work; return 1; -插入操作失败 end if; delete from t1 where col1=1; if dbinfo(sqlca.sqlerrd2) 1 then rollback work; return 2; -删除操作失败 end if; commit work; return 0; -操作成功 end procedure; 7、调试跟踪 create procedure proc_trans() returning integer; define col1_val integer; define col2_val varchar(10); -设置异常处理 on exception rollback; return 99; -数据库操作异常 end exception; -设置跟踪模式 set debug file to trace_check; - with append; -说明“with append”表示以追加模式打开跟踪结果文件 trace procedure; -on - 全部；procedure-部分 -说明 选项on表示跟踪所有内容； - 选项