• 什么是OTL:OTL 是 Oracle, Odbc and DB2-CLI TemplateLibrary 的缩写，是一个操控关系数据库的C++模板库，它目前几乎支持所有的当前各种主流数据库，如下表所示：

主要类包括：otl_stream、otl_connect、otl_exception

    otl_stream类是OTL“流”的概念的具体表现形式，任何通过输入/输出参数使用SQL语句、PL/SQL 块或者是存储过程调用，在C++的编程中都能通过otl_stream类来实现。

        其构造函数为:

   (1)for Oracle 7/8/9/10:

   otl_stream(const int arr_size, // 流的缓存大小

                      const char* sqlstm, // SQL语句或PL/SQL块或存储过程                     

                      otl_connect& db, // OTL数据库连接对象

                       const char* ref_cur_placeholder=0, // 游标引用占位符名称   

#include <iostream>  using namespace std;  #include <stdio.h>  #define OTL_ORA9I // Compile OTL 4.0/OCI9i,  //#define OTL_UNICODE //Enable Unicode OTL for OCI9i  #include <otlv4.h> // include the OTL 4.0 header file  otl_connect db; // connect object  void insert();void insertConstant();void insertBatch();   void insertNoAutoCommit();  void select();  void update();void updateNoAutoCommit();void del();    int main()  {     otl_connect::otl_initialize(); // initialize OCI environment     try{          db.rlogon("dbuser/dbpwd"); // connect to Oracleotl_cursor::direct_exec           (             db,             "drop table person_tab",            otl_exception::disabled // disable OTL exceptions            ); // drop table           otl_cursor::direct_exec           (            db,            "create table person_tab(age number, name varchar2(30))"            );  // create table           insert(); // insert one records into table           insertConstant();//constand insert sql           insertBatch(); // insert batch records into table           insertNoAutoCommit();//insert no  auto commit;           select(); // select records from table           update(); // update records in   table           updateNoAutoCommit(); // update no auto commit           del(); // delete records from table          }catch(otl_exception& p){ // intercept OTL exceptions        cerr<<p.msg<<endl; // print out error message        cerr<<p.stm_text<<endl; // print out SQL that caused the error        cerr<<p.var_info<<endl; // print out the variable that caused                                      // the error    } db.logoff(); // disconnect from Oracle return 0;}void insert()//插入单条数据数据{ // create insert stream  otl_stream o(1, // buffer size            "insert into person_tab values(:v_age<int>,:v_name<char[31]>)",                // INSERT statement               db // connect object               );   o<<30;//assigning :v_age=30   o<<“dengkf”;//assigning :v_name=“dengkf”   //char tmp[32];sprintf(tmp,”邓科峰”);   //o<<(unsigned char*)tmp;   //INSERT automatically executes when all input variables are assigned. }void insertBatch()//批量插入数据{   // create insert stream  otl_stream o(10000, // buffer size            "insert into person_tab values(:v_age<int>,:v_name<char[31]>)",                db // connect object               );   char tmp[32];      for(int i=1;i<=10000;i++){     sprintf(tmp,“NAME%d”,i);     o<<i;     o<<tmp;   }  //INSERT automatically executes when all input variables are assigned.}         void insertNoAutoCommit()//插入数据(事务手动提交){   // create insert stream  otl_stream o(10001, // buffer size            "insert into person_tab values(:v_age<int>,:v_name<char[31]>)",                db // connect object               );   o.set_flush(false);//turning off the stream’s autoflush flag   o.set_commit(0);//turning off the stream‘s autocommit flag   char tmp[32];      for(int i=1;i<=10000;i++){     sprintf(tmp,“NAME%d”,i);     o<<i;     o<<tmp;   }   o.flush();//flushing the stream‘s buffer   db.commit();//committing the changes to the database }    void select()//检索数据{    // create select stream  otl_stream i(50, // buffer size               "select * from person_tab where name=:v_name<char[31]>",               // SELECT statement               db // connect object               );    i<<"dengkf"; // assigning :v_name = 8  // SELECT automatically executes when all input variables are  // assigned. First portion of output rows is fetched to the buffer  int  r_age;  char r_name[31];    while(!i.eof()){ // while not end-of-data    i>>r_age;    i>>r_name;    cout<<"age="<<r_age<<endl;    cout<<"name="<<r_name<<endl;  }}//修改数据(事务自动提交)void update(){    // create update stream  otl_stream s(1, // buffer size               "update person_tab set age=:v_age<int> where                 name=:v_name<char[31]>",               // UPDATE statement               db // connect object               );    s<<31;//assgining :v_age =31  s<<"dengkf"; //assigning :v_name = 8  //UPDATE automatically executes when all input variables are assigned.}//修改数据(事务手动提交)void updateNoAutoCommit(){    // create update stream  otl_stream s(2, // buffer size               "update person_tab set age=:v_age<int> where age<:v_age2<int>",// UPDATE statement               db // connect object               );     s.set_flush(false);               s.set_commit(0);                              s<<31;//assgining :v_age =31  s<<2000; //assigning :v_age2 = 2000    s.flush();  db.commit();  }//删除数据void del(){    // create delete stream  otl_stream l(1, // buffer size               “delete from person_tab where name=:v_name<char[31]>",               // DELETE statement               db // connect object               );    l<<"dengkf"; //assigning :v_name = 8  //DELETE automatically executes when all input variables are assigned.}•SQL使用举例(常量SQL使用)：   常量SQL就是不带任何绑定变量的SQL，OTL通过一个静态方法来操作:   long otl_cursor::direct_exec(otl_connect&db,//OTL数据库对象                                const char* sqltm,//SQL语句                            otl_exception_disable=0,//OTL异常忽略标志                           );   返回值:   ●-1,如果otl_exception_disable被设置成1，并且OTL的底层API发生错误   ●>=0,SQL执行成功,返回实际处理成功的记录数.   o Examples(Oracle)otl_cursor::direct_exec    (db, // connect object     "create table person_tab(age number, name varchar2(30))"     );  // create table   otl_cursor::direct_exec    (db, // connect object     "drop table persion_tab", // SQL statement     otl_exception::disabled // disable OTL exceptions,                            // in other words, ignore any                            // database error     ); // drop tablelong rpc=otl_cursor::direct_exec            (db,//connect object            “delete from persion_tab”);   o Examples(ODBC,DB2-CLI)    otl_cursor::direct_exec     (db, // connect object     "create table person_tab(age numeric, name varchar(30))"     );  // create table   otl_cursor::direct_exec    (db, // connect object      "drop table persion_tab", // SQL statement     otl_exception::disabled // disable OTL exceptions,                            // in other words, ignore any                            //  database error     ); // drop table        long rpc=otl_cursor::direct_exec            (db,//connect object            “delete from persion_tab”);//“OtlPlsqlExample.cpp”#include <iostream>using namespace std;#include <stdio.h>#define OTL_ORA9I // Compile OTL 4.0/OCI9i#include <otlv4.h> // include the OTL 4.0 header fileotl_connect db; // connect objectvoid plsql(void)// invoking PL/SQL block{  otl_stream o(50, // buffer size              "begin "              " :A<int,inout> := :A+1; "              " :B<char[31],out> := :C<char[31],in>; "              "end;",                 // PL/SQL block              db // connect object             );o.set_commit(0); // set stream auto-commit off since                   // the block does not have any transactions                   // to commit o<<1<<"Test String1"; // assigning :A = 1, :C = "Test String1" o<<2<<"Test String2"; // assigning :A = 2, :C = "Test String2" o<<3<<"Test String3"; // assigning :A = 3, :C = "Test String3" o.flush(); // executing PL/SQL block 3 times int a; char b[32]; while(!o.eof()){ // not end-of-data  o>>a>>b;  cout<<"A="<<a<<", B="<<b<<endl; }}int main(){ otl_connect::otl_initialize(); // initialize OCI environment try{  db.rlogon(“dbuser/dbpwd"); // connect to Oracle  plsql();//invoking PL/SQL block } catch(otl_exception& p){ // intercept OTL exceptions  cerr<<p.msg<<endl; // print out error message  cerr<<p.stm_text<<endl; // print out SQL that caused the error  cerr<<p.var_info<<endl; // print out the variable that caused the                            // error } db.logoff(); // disconnect from Oracle return 0;}输出:A=2,B=Test Stri ng1A=3,B=Test String2A=4,B=Test String3 常量PL/SQL块的使用与常量SQL的使用类似，在此不再赘述。

下一篇：OTL使用指南