INFORMIX IDS11.5 动态存储过程（DYNAMIC SPL）

INFORMIX IDS11支持 动态存储过程。本文通过举例的方法演示如何在INFORMIX中创建动态存储过程。

 

EXAMPLE 1 

说明 EXECUTE IMMEDIATE 语句

可以通过参数名来动态执行不同的表

 

DATABASE stores_demo;
   
CREATE PROCEDURE create_tab (table_name CHAR(128), column_list CHAR(512))

   DEFINE l_crtstmt   CHAR(1024);

   LET l_crtstmt = "CREATE TABLE " || table_name ||"("|| column_list || " )";  --动态语句，存放到一个变量中

   EXECUTE IMMEDIATE l_crtstmt;          --动态执行SQL

END PROCEDURE;

 

执行存储过程

EXECUTE PROCEDURE create_tab ("tmp_cust","cust_num INTEGER,cust_fname CHAR(30)");

---

 

EXAMPLE 2

说明在SPL中如何利用游标和动态语句

 

DATABASE stores_demo;

-- Procedure to dynamically constructs query `cust_qry' using supplied
-- table_name and returns all rows whose first name is the supplied
-- first_name

CREATE PROCEDURE customer_details(table_name CHAR(30),first_name CHAR(30))
RETURNING INTEGER, CHAR(30), CHAR(30), CHAR(30);

   DEFINE cust_qry VARCHAR(250);
   DEFINE l_cust_num INTEGER;

   DEFINE l_fname CHAR(30);
   DEFINE l_lname CHAR(30);
   DEFINE l_state CHAR(30);

   -- Construct a Dynamic query using SPL argument table_name

   LET cust_qry = "select customer_num, fname, lname, state from "
||table_name || " where fname = ?";

   -- Prepare the above constructed query
   -- Get the statement handle "statement_id"

   PREPARE stmt_id FROM cust_qry;

-- Declare the cursor for the prepared "statement_id"
-- get the cursor handle "cust_cur"

DECLARE cust_cur cursor FOR stmt_id;

           -- Open the declared cursor using handle "cust_cur"
           -- Supply the first_name as an input. This will be
           -- substituted in the place of "?" in the query

   OPEN cust_cur USING first_name;

   WHILE (1 = 1)

                -- Fetch a row from the cursor "cust_cur" and store
                -- the returned column values to the SPL variables

FETCH cust_cur INTO l_cust_num, l_fname, l_lname, l_state;

                -- Check if FETCH reached end-of-table (SQLCODE = 100)
                -- if so, exit from while loop; else return the columns
                -- and continue

IF (SQLCODE != 100) THEN
   RETURN l_cust_num, l_fname,l_lname, l_state WITH RESUME;
ELSE
                   -- break the while loop
   EXIT;
END IF

   END WHILE

           -- Close the cursor "cust_cur"

   CLOSE cust_cur;

           -- Free the resources allocated for cursor "cust_cur"

           FREE cust_cur ;

           -- Free the resources allocated for statement "statement_id"

           FREE stmt_id ;

END PROCEDURE;

 

--执行
-- Execute the procedure; Look for rows with first name "Raymond"
-- in "customer" table

EXECUTE PROCEDURE customer_details("customer", "Raymond");
 

---

 

EXAMPLE 3

 

DATABASE                
dynproc.sh:

dbname=$1
tabname=$2

dbaccess $dbname - << EOF
   DROP PROCEDURE dynproc;
   CREATE PROCEDURE dynproc() RETURNING integer;
DEFINE l_fname CHAR(30);
DEFINE l_lname CHAR(30);

SELECT fname, lname INTO l_fname, l_lname FROM $tabname;

-- Process the fetched data

...   

   END PROCEDURE;

   EXECUTE PROCEDURE dynproc();
   EOF

CREATE PROCEDURE crt_dynproc(database_name CHAR(30), table_name CHAR(30))
system "dynproc.sh " || database_name || " " || table_name
END PROCEDURE;

EXECUTE PROCEDURE crt_dynproc("stores", "customer");

 

 

 

完整的参考文档，请参考如下链接： 

http://www.ibm.com/developerworks/db2/library/techarticle/dm-0806mottupalli/index.html