|
INFORMIX IDS11支持 動(dòng)態(tài)存儲(chǔ)過(guò)程。本文通過(guò)舉例的方法演示如何在INFORMIX中創(chuàng)建動(dòng)態(tài)存儲(chǔ)過(guò)程。
EXAMPLE 1
說(shuō)明 EXECUTE IMMEDIATE 語(yǔ)句
可以通過(guò)參數(shù)名來(lái)動(dòng)態(tài)執(zhí)行不同的表
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 || " )"; --動(dòng)態(tài)語(yǔ)句，存放到一個(gè)變量中
EXECUTE IMMEDIATE l_crtstmt; --動(dòng)態(tài)執(zhí)行SQL
END PROCEDURE;
執(zhí)行存儲(chǔ)過(guò)程
EXECUTE PROCEDURE create_tab ("tmp_cust","cust_num INTEGER,cust_fname CHAR(30)");
-------------------------------------------------------------------------------- EXAMPLE 2
說(shuō)明在SPL中如何利用游標(biāo)和動(dòng)態(tài)語(yǔ)句
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 本文來(lái)自CSDN博客，轉(zhuǎn)載請(qǐng)標(biāo)明出處：http://blog.csdn.net/ibminformix/archive/2009/11/19/4833481.aspx
|
|
|
來(lái)自： snowlewolf > 《informix》
