Pro*C中动态SELECT SQL的限制

Pro*C中实现动态SQL的方法大约有如下4种：

1、不带任何宿主变量的SQL语句
   譬如：DELETE FROM EMP WHERE EMPNO=7788
2、带虚拟宿主变量或者指示器变量的SQL语句
   譬如：DELETE FROM EMP WHERE EMPNO=:EMPNO
3  只适用于SELECT的游标动态SQL语句
4、利用sqlda结构的动态SQL语句

 

书上说第一种和第二种形式的动态SQL不能用于SELECT语句。
我试了一下编译是可以的，在预编译的时候加上dynamic=ansi选项即可，但是却得不到SELECT出来的值。

首先看一下PL/SQL中例子

> set serverout on
> desc test;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 ID                                        NOT NULL NUMBER(38)
 NAME                                               VARCHAR2(30)

> select * from test;

        ID NAME
---------- ------------------------------
         1 yansp

> declare
  2  l_name test.name%type;
  3  begin
  4    execute immediate 'select name from test where id=1' into l_name;
  5    dbms_output.put_line(l_name);
  6    execute immediate 'select name from test where id=:id' using 1 into l_name;
  7    dbms_output.put_line(l_name);
  8  end;
  9  /

yansp
yansp

PL/SQL 过程已成功完成。

>

 

在看一下Pro*C的例子：
/home/cpicsrv/yansp > cat test.pc
#include
#include
exec sql include sqlca;

main()
{
 
  exec sql begin declare section;
     char *username="test";
     char *password="test";
     char *dbserver="10.223.18.116/yansp";
     char l_name[20];
     int  id=1;
     char sqltext[128];
  exec sql end declare section;
  exec sql connect :username identified by :password using :dbserver;
 
  memset(l_name,'\0',sizeof(l_name));
  strcpy(sqltext,"select name from test where id=1");
  exec sql prepare s from :sqltext;
  exec sql execute s into :l_name;
  printf("sqlcode=%d,name is :[%s]\n",sqlca.sqlcode,l_name);

  strcpy(sqltext,"select name from test where id=:id");
  exec sql prepare  s from :sqltext;
  exec sql execute  s into :l_name using :id;
  printf("sqlcode=%d,name is :[%s]\n",sqlca.sqlcode,l_name);

 strcpy(sqltext,"select name from test where id=:id");
 exec sql prepare  s from :sqltext;
 exec sql declare  mycursor cursor for s;
 exec sql open mycursor using :id ;
 exec sql fetch mycursor into :l_name;
 printf("sqlcode=%d,name is :[%s]\n",sqlca.sqlcode,l_name);
 exec sql close mycursor;
 

        exec sql commit work release;
}
/home/cpicsrv/yansp > proc parse=none sqlcheck=semantic dynamic=ansi test.pc

Pro*C/C++: Release 10.2.0.1.0 - Production on Mon Jan 16 18:26:31 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

System default option values taken from: /home/oracle/oracle/product/10.2.0/client_1/precomp/admin/pcscfg.cfg

/home/cpicsrv/yansp > gcc $ORACLE_HOME/lib/libclntsh.so test.c -o test
/home/cpicsrv/yansp > ./test
sqlcode=0,name is :[]
sqlcode=0,name is :[]
sqlcode=0,name is :[yansp              ]
/home/cpicsrv/yansp >

 

可以看到前面2个动态SQL是无法取得SELECT出来的值的。

 

不过可以用嵌入式PL/SQL来实现。