如何在Informix中创建TEMP存储过程？

Hey there! Great question—Informix handles temporary stored procedures a bit differently than SQL Server’s # prefix approach, so let’s break down the two main ways to create them.

1. Session-Level Temporary Procedures (Most Like SQL Server’s Local Temp Procs) #

If you need a stored procedure that only exists for your current database session (and vanishes as soon as you disconnect), use the TEMP keyword in your CREATE PROCEDURE statement. This is the closest equivalent to SQL Server’s # prefixed temp procs.

Example Syntax: #

CREATE TEMP PROCEDURE calculate_discount(original_price DECIMAL(10,2), discount_percent INT)
RETURNING DECIMAL(10,2);
    DEFINE discounted_price DECIMAL(10,2);
    LET discounted_price = original_price * (1 - (discount_percent / 100.0));
    RETURN discounted_price;
END PROCEDURE;

Key Notes: #

• This procedure is only visible to your current session—no other users or connections can access it.
• It’s automatically dropped when your session ends, so you don’t have to manually clean it up.
• You can use standard Informix stored procedure logic (variables, flow control, etc.) inside it, just like a permanent procedure.

2. Temporary Procedures in the tempdb Database (Shared Temporary Procs) #

If you need a temporary procedure that’s accessible to multiple sessions (but still not permanent), you can create it in Informix’s built-in tempdb database. Objects in tempdb are typically cleared when the database restarts, making them a good fit for shared short-lived procedures.

Example Syntax: #

CREATE PROCEDURE tempdb:get_session_user()
RETURNING VARCHAR(32);
    RETURN USER;
END PROCEDURE;

Key Notes: #

• Any user with permissions to access tempdb can execute this procedure.
• Unlike session-level temp procs, this won’t be dropped until the database is restarted (or you manually drop it with DROP PROCEDURE tempdb:get_session_user;).
• Be cautious with this approach—don’t store sensitive logic here, and remember it won’t survive a database reboot.

Quick Comparison to SQL Server #

内容的提问来源于stack exchange，提问作者Sam