IBM Informix SPL

存储过程是用户定义的函数：

• 一组SQL语句组成。
• 以可执行代码形式存储在数据库中
• 是数据库的一种对象
• 用存储过程语言（SPL）编写

SPL（Informix Stored Procedure Language）

1. 变量定义和赋值
   DEFINE 和 LET
2. 流程控制
   分支控制：IF
   循环控制：FOR、FOREACH、WHILE、loop、EXIT、CONTINUE
3. 函数调用与返回
   CALL、SYSTEM、RETURN
4. 错误处理和调试
   TRACE、ON EXCEPTION、RAISE EXCEPTION

注：SPL语句只能在存储过程中使用。

与存储过程相关系统表

• SYSPROCEDURES：记录数据库的所有存储过程
• SYSPROCBODY：记录存储过程文本
• SYSPROCPLAN：记录存储过程执行的查询规划
• SYSPROCAUTH：记录授予存储过程的权限

检索存储过程代码

DBSCHEMA:

    dbschema -d 数据库名 -f 过程名

从系统表中检索：

select data
from sysprocedures, sysprocbody
where datakey = 'T'
    and sysprocedures.procname = '过程名'
    and sysprocedures.procid = sysprocbody.procid;

使用存储过程的一些优点

• 存储过程可以减少程序的复杂性
• 在某些情况下具有较高的性能
• 可以提高系统的安全性
• 可以强制业务规则
• 不同的应用可以共享相同的代码
• 在客户/服务器结构中不必所有的客户端发布应用，只有一个代码
• 如果需要在不同的用户界面中执行相同的功能，只需维护一组代码

创建存储过程

用CREATE PROCEDURE语句创建存储过程。该语句一般在DBACCESS中使用。
语法：

CREATE PROCEDURE 过程名(参数[,参数[,...]])
    RETURNING 子句
        语句块
    END PROCEDURE

过程名说明创建的存储过程的名字
参数说明调该存储过程所需的参数个数和类型
一个存储过程可以不返回任何值，或返回一个或多个值，也可返回多组值。返回多组值的存储过程称之为游标式存储过程，对该类存储过程，相应调用函数需做一些特殊处理。

下列SQL语句不能包含在语句块中：

• CREATE DATABASE
• DATEBASE
• CLOSE DATABASE
• CHECK TABLE
• REPAIR INFO OUTPUT LOAD UNLOAD
• CREATE PROCEDURE
• CREATE PROCEDURE FROM

一个简单的存储过程例子

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);
Begin
    select fname, address1, zipcode into p_fname, p_addr, p_zip
        from customer
        where lname = lastname;
    return p_fname, lastname, paddr, p_zip;
end
end procedure

完整的存储过程模板

create procedure sp_xxxx(var1 char(8),var2 int)
returning varchar(200) ---返回值
define var3 char(8);   ---变量定义
···
Begin
---异常处理
    on exception set v_err_sql, v_err_isam, v_err_info
    if v_err_sql = -746 then
            ···
        else
            ···
        end if;
        raise exception
    v_err_sql, v_err_isam, v_err_info;
    end exception;

    set isolation to dirty read; ---设置read模式为脏读
    set lock mode to wait; ---设置等待模式为锁等待
    ---变量初始化
    let var3 = '';
    ···
    ---循环处理
    foreach
        select xxx into var3
            from ···
                where ···
    end foreach

    ---自定义异常
    if ··· then 
        raise exception -746
    end if

    ---事务控制
    begin work;
    ···
    end work;

    ---返回值
    return ···
end
end procedure

执行一个存储过程

在DBACCESS中：

execute procedure raise_prices(5)
execute procedure db@servername:raise_prices(5)
Call raise_prices(5)

CALL语句

利用CALL语句在一个存储过程中调用另一个存储过程。
两种格式：

- CALL 过程名（参数,...）RETURNING 变量,...;
- CALL 过程名（参数名=参数,...）RETURNING 变量,...;

说明：

• 参数可以是SPL表达式或者SELECT语句，只要该语句返回单值，并且具有适当的类型和长度。
• 如果参数个数多于被调用的存储过程的参数，则返回错误。
• 如果参数个数少于被调用的存储过程的参数，则未说明的参数被初始化为缺省值（该值在创建存储过程时说明）若无缺省值，则返回错误。
• RETURNING子句说明的变量用于接受被调用存储过程的返回值，若无返回值，则可省略。

语句实例：

DEFINE i, j INT;
CALL no_return(10,20)
CALL yes_return(empno=10, age=20)
RETURNING i, j;

删除存储过程

用DROP PROCEDURE 语句删除存储过程。
格式：

DROP PROCRDURE 过程名

只有存储过程的所有者或具有DBA权限的用户才能删除存储过程。