一个简单的Pro*C内嵌PL/SQL块的例子

SQL> show user
USER 为 "SYS"
SQL> conn yansp/yansp
已连接。
SQL> create table test as select * from all_objects;

表已创建。

SQL> create table his_teat as select * from test where 1=0;

表已创建。

SQL> select count(1) from test;

  COUNT(1)
----------
     49588

SQL> select count(1) from his_test;

  COUNT(1)
----------
         0

Pro*C代码如下：

/home/cpicsrv/yansp > cat test.pc
#include
#include
exec sql include sqlca;

main()
{
  exec sql begin declare section;
     char *username="yansp";
     char *password="yansp";
     char *dbserver="10.223.18.116/yansp";
  exec sql end   declare section;
  exec sql whenever sqlerror  stop;
 
  exec sql connect :username identified by :password using :dbserver;
 
exec sql execute
declare
   type rowid_array_type is table of rowid
   index by pls_integer;
   rowid_array rowid_array_type;
  cursor cur_test is select rowid from test;
  begin
    open cur_test;
    loop
      fetch cur_test bulk collect into rowid_array limit 100;//每次处理100条

      exit when rowid_array.count=0;
      forall i in rowid_array.first..rowid_array.last
      insert into his_test
      select * from test where rowid=rowid_array(i);
      forall i in rowid_array.first..rowid_array.last
      delete from test where rowid=rowid_array(i);
      commit;
  end loop;
  close cur_test;
end;
end-exec;
 
  exec sql commit work release;
}
/home/cpicsrv/yansp > proc parse=none sqlcheck=full test.pc

Pro*C/C++: Release 10.2.0.1.0 - Production on Thu Jan 12 11:13:29 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

 

 

SQL> select  count(1) from test;

  COUNT(1)
----------
         0

SQL> select count(1) from his_test;

  COUNT(1)
----------
     49588