PRO *C/C++ 动态SQL

(1)动态SQL1: 不能是查询(SELECT)语句,并且没有宿主变量. 
用法:拼一句动态SQL语句,并用EXECUTE IMMEDIATE执行,如:
 
EXEC SQL EXECUTE IMMEDIATE CREATE TABLE test (test_col VARCHAR2(4));
EXEC SQL EXECUTE IMMEDIATE INSERT INTO TABLE test ('AAAA');
EXEC SQL EXECUTE IMMEDIATE DELETE test WHERE test_col='AAAA';

EXEC SQL EXECUTE IMMEDIATE update test  set test_col='bbb' WHERE test_col='AAAA';

 

(2)动态SQL2: 不能是查询(SELECT)语句,并且输入的宿主变量数目是知道的,
用法1:拼一句动态SQL语句,用PREPARE ,EXECUTE语句执行.
strcpy(sqlstring, "DELETE FROM test WHERE test_col = :?"); 
EXEC SQL PREPARE sqlproc FROM :sqlstring;
EXEC SQL EXECUTE sqlproc USING :emp_number; 
PREPARE 是用来解析SQL文本串的。

上面的可以动态传递参数，但也可以使用以下形式动态执行SQL：

sprintf (sSQLCode, "update %s set err_cd ='%s' ", sDtlTbNm, sBatChkFg);
sprintf (sSQLCode, "%s where batch_id ='%s' and batch_tran_sq ='%s' ", sSQLCode, sBatchId, sBatchTranSq); 
EXEC SQL EXECUTE IMMEDIATE :sSQLCode;

 如果想用select语句，要采用动态游标：
 EXEC SQL PREPARE sp FROM
  "SELECT * FROM test WHERE t1=:a and t2=:a and t3=:a";
 EXEC SQL DECLARE cur_qry CURSOR FOR sp;
 EXEC SQL OPEN cur_qry USING :var1, :var2, :var3;
 EXEC SQL FETCH cur_qry into :var4;