Linux Informix 存储过程编写指南

Informix 数据库存储过程(SPL - Stored Procedure Language)是一种在数据库中存储和执行的过程化语言。以下是在Linux环境下编写Informix存储过程的详细指南。

基本语法结构

CREATE PROCEDURE procedure_name ([parameter_list])
    [RETURNING return_type]
    [WITH (modifier_list)]
    [DEFINE variable_list]
    [ON EXCEPTION exception_handler]
    [procedure_statements]
END PROCEDURE;

简单示例

CREATE PROCEDURE get_customer_info(cust_id INT)
    RETURNING CHAR(50), CHAR(50), CHAR(15);

    DEFINE lname CHAR(50);
    DEFINE fname CHAR(50);
    DEFINE phone CHAR(15);

    SELECT last_name, first_name, phone
    INTO lname, fname, phone
    FROM customers
    WHERE customer_id = cust_id;

    RETURN lname, fname, phone;
END PROCEDURE;

参数类型

Informix存储过程支持多种参数类型：

1. 输入参数：默认类型
2. 输出参数：使用OUT关键字
3. 输入输出参数：使用INOUT关键字

CREATE PROCEDURE update_salary(emp_id INT, INOUT salary DECIMAL(10,2), OUT status INT)
    DEFINE old_salary DECIMAL(10,2);

    SELECT salary INTO old_salary FROM employees WHERE emp_id = emp_id;

    IF old_salary < 5000 THEN
        LET salary = old_salary * 1.1;
        LET status = 1; -- success
    ELSE
        LET status = 0; -- no update
    END IF

    UPDATE employees SET salary = salary WHERE emp_id = emp_id;
END PROCEDURE;

流程控制

IF-THEN-ELSE

IF condition THEN
    statements;
[ELIF condition THEN
    statements;]
[ELSE
    statements;]
END IF;

FOR循环

FOR var = start TO end [STEP step]
    statements;
END FOR;

WHILE循环

WHILE condition
    statements;
END WHILE;

异常处理

CREATE PROCEDURE risky_operation()
    ON EXCEPTION IN (-206) -- 表不存在错误
        -- 处理代码
        RETURN -1;
    END EXCEPTION;

    -- 主过程代码
END PROCEDURE;

实际案例

案例1：数据迁移

CREATE PROCEDURE migrate_old_data()
    DEFINE done INT DEFAULT 0;
    DEFINE old_id, new_id INT;
    DEFINE cust_name CHAR(100);

    FOREACH SELECT customer_id, customer_name INTO old_id, cust_name
    FROM old_customers
    WHERE migrated = 'N'
        INSERT INTO new_customers (customer_name) VALUES (cust_name);
        SELECT DBINFO('sqlca.sqlerrd1') INTO new_id FROM systables WHERE tabid=1;

        UPDATE old_customers SET migrated = 'Y', new_id = new_id
        WHERE customer_id = old_id;

        IF MOD(new_id, 100) = 0 THEN
            COMMIT WORK;
        END IF
    END FOREACH;

    COMMIT WORK;
END PROCEDURE;

案例2：复杂业务逻辑

CREATE PROCEDURE process_monthly_billing(month_num INT)
    RETURNING INT; -- 返回处理记录数

    DEFINE rec_count INT DEFAULT 0;
    DEFINE cust_id INT;
    DEFINE usage_amt, bill_amt DECIMAL(10,2);
    DEFINE billing_date DATE;

    LET billing_date = MDY(month_num, 1, YEAR(TODAY));

    FOREACH SELECT c.customer_id, SUM(u.usage_amount)
    INTO cust_id, usage_amt
    FROM customers c, usage u
    WHERE c.customer_id = u.customer_id
    AND MONTH(u.usage_date) = month_num
    GROUP BY c.customer_id

        -- 计算账单金额
        IF usage_amt <= 100 THEN
            LET bill_amt = usage_amt * 0.10;
        ELSE
            LET bill_amt = 10 + (usage_amt - 100) * 0.08;
        END IF

        -- 插入账单记录
        INSERT INTO billing (customer_id, billing_date, amount, status)
        VALUES (cust_id, billing_date, bill_amt, 'P');

        LET rec_count = rec_count + 1;

        -- 每100条记录提交一次
        IF MOD(rec_count, 100) = 0 THEN
            COMMIT WORK;
        END IF
    END FOREACH;

    COMMIT WORK;
    RETURN rec_count;
END PROCEDURE;

调试技巧

1. 使用TRACE ON和TRACE OFF语句跟踪执行流程
2. 使用RAISE EXCEPTION手动引发异常
3. 在存储过程中使用SYSTEM命令调用外部程序记录日志

CREATE PROCEDURE debug_demo(param INT)
    DEFINE debug_file CHAR(100);

    LET debug_file = '/tmp/sp_debug_' || USER || '.log';
    SYSTEM 'echo "Starting procedure with param=' || param || '" >> ' || debug_file;

    -- 过程代码

    ON EXCEPTION
        SYSTEM 'echo "Error occurred: ' || STATUS || '" >> ' || debug_file;
        RAISE EXCEPTION;
    END EXCEPTION;
END PROCEDURE;

最佳实践

1. 使用有意义的存储过程命名
2. 添加充分的注释
3. 合理使用事务控制(COMMIT/ROLLBACK)
4. 考虑错误处理和恢复机制
5. 避免过度复杂的存储过程
6. 对频繁使用的存储过程使用WITH LISTING IN 'file'选项进行预编译

存储过程管理

• 查看存储过程：SELECT * FROM sysprocedures WHERE procname='procedure_name'
• 删除存储过程：DROP PROCEDURE procedure_name
• 重新创建存储过程：先删除再创建

希望这份指南能帮助您在Linux环境下编写高效的Informix存储过程。根据您的具体业务需求，可以在此基础上进行扩展和优化。