Informix-存储过程

SPL语句只能在存储过程中使用
2019/11/15
存储过程内外SQL语句的比较
存储过程之外的SQL语句：
应用
数据库服务器
编译SQL 语句
SQL编译．优化．执行
存储过程中的SQL语句：
应用
传递执行 过程语句
数据库服务器
SQL语句检索和执行
2019/11/15
存储过程的编译
存储过程属性
存储过程文本
- CREATE DATABASE - DATEBASE - CLOSE DATEBASE - CHECK TABLE - REPAIR INFO OUTPUT LOAD UNLOAD - CREATE PROCEDURE - CREATE PROCEDURE FROM
DOCUMENT 子句可对存储过程作一简单说明， 如输入、输出 参数， 作用等。 该子句可省略。
WITH LISTING IN 选项说明接受编译器输出信息的文件名， 如省略， 则编译器不产生输出。
2019/11/15
创建存储过程(续2)
一个完整的例子
CREATE PROCEDURE read_address(lastname CHAR(15)) RETURNING CHAR(15), CHAR(15), CHAR(20), CHAR(5); DEFINE p_lname, p_fname CHAR(15); DEFINE p_addr CHAR(20); DEFINE p_zip CHAR(5); SELECT fname, address1, zipcode INTO p_fname, p_addr, p_zip FROM customer WHERE lname=lastname; RETURN p_fname, lastname, paddr, p_zip;
传进的变量
create procedure delete_order (p_order_num int default null)
delete form items
where order_num = p_order_num; delete from orders
缺省值
where order_num = p_order_num;
在ESQL/C中：
execute procedure raise_prices(5) execute procedure db@servername:raise_prices(5)
2019/11/15
准备EXECUTE PROCEDURE
如果存储过程在程序中需要执行多次，准备EXECUTE PROCEDURE语句会提高执行效率.
2019/11/15
存储过程缓冲区
应用
应用
OnLine数据库 服务器
存储过程 缓冲池
2019/11/15
缓冲区 系统目录表
与存储过程相关系统表
•SYSPROCEDURES:记录数据库的所有存储过程 •SYSPROCBODY：记录存储过程文本 •SYSPROCPLAN：记录存储过程执行的查询规划 •SYSPROCAUTH：记录授予存储过程的权限
在4GL必须通过 oldpare执行存储过程。如下例所示：
let p_percent = 5 oldpare proc_stmt from “execute procedure raise_price(?)” execute proc_stmt using p_percent
2019/11/15
向存储过程传递变量
and sysprocedures.procid = sysprocbody.procid;
2019/11/15
使用存储过程的一些优点
存储过程可以减少程序的复杂性 在某些情况下具有较高的性能 可以提高系统的安全性 可以强制业务规则 不同的应用可以共享相同的代码 在客户/服务器结构中不必向所有的客户端发布应用
Create procedure raise_prices (per_cent int) update stock --increase by percentage set unit_price = unit_price * (unit_price * (per_cent / 100));
end procedure; document “USAGE: EXECUTE PROCEDURE raise_price (xxx), xxx = percentage from 1 - 100”2019Leabharlann 11/15创建存储过程示例
过程名
Create procedure raise_prices (per_cent int) update stock --increase by percentage set unit_price = unit_price * (unit_price * (per_cent / 100));
with listing in “warn_file”;
语句注释 使用描述
2019/11/15
在ESQL/C 中创建存储过程
存储过程代码 保存在文件中 ＄create procedure from “raise.spl”
2019/11/15
执行一个存储过程
在DBACCESS中：
execute procedure raise_prices(5) execute procedure db@servername:raise_prices(5)
2019/11/15
练习
– 演示数据库stores7有多少个存储过程？ – 如何查找存储过程的文本？ – 如何确定存储过程输入的参数的类型和个数？ – 如何确定存储过程返回的数据类型和个数？
2019/11/15
创建和执行存储过程
在本章中你将了解到： 创建存储过程 执行存储过程 删除存储过程 向存储过程传递参数 从存储过程中返回值 应用程序如何接收返回值
- 变量定义和赋值 * DEFINE， LET
- 流程控制 * 分支控制 IF * 循环控制 FOR，FOREACH，WHILE，loop EXIT， CONTINUE
- 函数调用与返回 * CALL， SYSTEM ，RETURN
- 错误处理和调试 * TRACE， ON EXCEPTION， RAISE EXCEPTION
end procedure; document “USAGE: EXECUTE PROCEDURE raise_price (xxx), xxx = percentage from 1 - 100”
with listing in “warn_file”;
参数 语句块
编译时的警告
2019/11/15
创建存储过程示例
end procedure;
execute procedure delete_order (1001);
2019/11/15
RETURN 语句
RETURN 语句从存储过程中返回。返回值可以有零个 或多个，也可以返回多条记录。
RETURN 语句说明的返回值的个数和类型必须与创建 存储过程时说明的返回值的个数和类型一致，或者不 返回任何值，在后一情形下，所有的返回值为空值。
过程名说明创建的存储过程的名字 参数说明调用该存储过程所需的参数个数和类型 一个存储过程可以不返回任何值，或返回一个或多个值，也可返回
多组值。返回多组值的存储过程称之为游标式存储过程，对该类存储 过程，相应调用函数需作一些特殊处理。
2019/11/15
创建存储过程(续1)
语句块子句组成存储过程的过程体， 由SPL语句和SQL 语句组成。 下列SQL语句不能包含在语句块中：
IBM Informix SPL 存储过程语言
2019/11/15
存储过程
存储过程是一个用户定义的函数,由存储过程语句(SPL) 一组SQL语句组成
以可执行代码形式存储在数据库中 是数据库的一种对象 用存储过程语言(SPL)编写
2019/11/15
存 储 过 程 语 言SPL
SPL(Informix Stored Procedure Language)
END PPROCEDURE DOCUMENT “this procedure takes the last name of a customer as its only argument, it returns the name and the address of the customer”
WITH LISTENING IN “/tmp/test /listfile”
2019/11/15
检索存储过程代码
DBSCHEMA：
dbschema -d 数据库名 -f 过程名
从系统表中检索：
select data from sysprocedures, sysprocbody where datakey = “T”
and sysprocedures.procname = “过程名”
，只有一个代码 如果需要在不同的用户界而中执行相同的功能，只需
维护一组代码
2019/11/15
存储过程的性能缺陷
存储过程需要从磁盘上读取 过程必须从ASCII格式转换成二进制格式 必须判定是否需要重新优化
2019/11/15
讨论
给出在你的应用中可能用到存储过程的例子。
– 数据类型转换 – 任何事务，如存款、取款 – 查找函数 – 测试数据的产生 – 清理功能 – 需要特殊安全性的操作
2019/11/15
创建存储过程
用CREATE PROCEDURE 语句创建存储过程。该语句一般在 ISQL（DBACCESS）中使用。
语法： CREATE 〖DBA〗 PROCEDURE 过程名（参数[, 参数 [ ,...]] ) RETURNING 子句 语句块 END PROCEDURE DOCUMENT 子句 WITH LISTING IN 文件名
返回P_order_num和 item_count的值
execute procedure delete_order (1001);
2019/11/15
应用程序中如何接收返回值
oldpare oldp_stmt from “execute procedure delete_order(?)”
declare del_curs cursor for oldp_stmt
open del_curs using p_order_num
INFORMIX-4GL
fetch del_curs into p_order_num p_item_count
close del_surs
exec sql execute procedure delete_order (:p_order_num) into :p_order_num, :p_item_count ;
sysprocedures
伪码pcode
转换成 ASCII
查询树．关系列表
转换成 ASCII
sysprocbody
sysprocplan
2019/11/15
存储过程的执行
sysprocbody 伪码pcode
存储过程参数
解释器
sysprocplan 查询树．关系列表
转换成 二进制
检查依赖关系 如果需要重新优化
2019/11/15
从存储过程返回值
create procedure delete_order (p_order_num int)
returning int , int;
返回两个整数
define item_count
int;
select count(*) into item_count fromitems
where order_num = p_order_num;
delete form items
where order_num = p_order_num;
delete from orders
where order_num = p_order_num; return p_order_num, item_count; end procedure;
WITH RESUME子句将保证存储过程继续执行，所有 的变量均保持原有的值，该子句用于返回多组值。
2019/11/15
RETURN语句（续）
返回值中可以包括变量． 表达式或过程。 return p_order_num;
return p_order_num with resume;
WITH RESUME将保证存储过程继续执行，所有的变量 均保持原有的值。FETCH语句将控制执行RETURN后的 语句。