动态SQL语句的两种执行方式

1.如果SQL语句已经被构造在host-variable字符串变量中，则

　　（1）立即执行语句：运行时编译并执行(构造的字符串SQL内部没有“变量”参数)

　　　　exec sql execute immediate :host-variable;

　　（2）Prepare-Execute-Using语句：Prepare语句先编译，编译后的SQL语句允许动态参数(即允许高级语言传递参数到SQL语句中)，execute语句执行，用using语句将动态参数值传递给编译好的SQL语句（构造的字符串SQL内部有“变量”参数）

　　　　exec sql prepare sql_temp from :host-variable;　　// 预编译

　　　　... ...

　　　　exec sql execute sql_temp using :cond-variable;　　//cond-variable 动态参数

2.Prepare-Execute-Using举例

 1 //Prepare-Execute-Using
 2 #include
 3 #include"prompt.h"
 4 exec sql include sqlca;
 5 exec sql begin declare section;
 6     char cust_id[5];
 7     char sqltext[256];
 8     char user_name[20];
 9     char user_pwd[10];
10 exec sql end declare section;
11 char cid_prompt[] = "Name customer cid to be deleted: ";
12 
13 int main()
14 {
15     strcpy(sqltext,"delete from customers where cid = :dcid");
16     ... ...
17     while(prompt(cid_prompt, 1, cust_id, 4)>=0)
18     {
19         exec sql whenever not found goto no_such_cid;
20         exec sql prepare delcust from :sqltext;        //为编译提供了基础 
21         exec sql execute delcust using :cust_id;    //cust_id是输入的字符串变量
22         exec sql commit work;
23         
24         no_such_cid:printf("No cust %s in table\n",cust_id);
25             continue; 
26     }
27     ...
28 }