Pro*C中Merge用法的异常

一同事在公司的bbs技术栏里发布一帖子，请教如下的问题：

在Pro*C中使用Merge语句，来代替原来的Update or Insert语句，在预编译时，总是报错。代码如下：
EXEC SQL MERGE INTO tab1 a USING
(select * from tab2
where tab2.col1=:parameter1 and tab2.col2=:parameter2) b
ON (a.col1 = b.col1 AND a.col2 = b.col2)
WHEN MATCHED THEN UPDATE SET a.col3 = :parameter3
WHEN NOT MATCHED THEN INSERT VALUES (b.col1, b.col2, b.col3);

所使用的预编译器是:Pro*C/C++: Release 9.2.0.4.0

[@more@]

异常信息如下：
PCC-S-02201, Encountered the symbol "MERGE" when expecting one of the following:

for, register, at, close, commit, connect, declare, describe,
execute, fetch, open, prepare, rollback, select, whenever,
alter, audit, comment, create, delete, drop, get, grant,
insert, lock, noaudit, rename, revoke, set, update, validate,
arraylen, allocate, cache, call, collection, context,
deallocate, enable, free, lob, object, savepoint, analyze,
explain, truncate,
The symbol "alter," was substituted for "MERGE" to continue.

通过查询oracle相关文档，得知如下结论：
The Unified SQL Parser was introduced in the Oracle RDBMS PL/SQL in 9i. The precompilers, as of 10g, have not yet
incorporated this Unified SQL Parser into their syntactic or semantic checking and still use an old version of the PL/SQL Parser.
This means that the existing Precompiler's PL/SQL Parser engine is limited to 8.0 and some 8i syntax.

Precompilers and the Unified SQL ParserAlso, syntactic checking is done by the precompiler, and not the PL/SQL parser. The precompiler was extended in each release to pick up some of the new syntax but not all. For instance 9.2 MERGE is not accepted by the syntax checker so therefore you will find 9i and 10g syntax that the precompilers do not accept.

The most common error reported for syntax that is not accepted is Error: PCC-S-02201
The workaround is to use dynamic SQL so no statement parsing takes place at precompile time.
The Unified Parser for Pro*C and Pro*Cobol is planned to be included in 11g with very high priority.

看来要在Pro*C中使用Merge语句，得使用动态的SQL才行。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/38542/viewspace-928328/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/38542/viewspace-928328/