回复人: masterz(www.fruitfruit.com) ( ) 信誉:100 2005-03-05 16:53:00 得分: 0
The following code example demonstrates how to perform simple insert, delete,
and update operations on a table column of type Nested Table:
/**
*occicoll - To exhibit simple insert, delete & update operations"
* " on table having a Nested Table column
*
*Description
* Create a program which has insert,delete and update on a
* table having a Nested table column.
* Perform all these operations using OCCI interface.
*/
#include <iostream.h>
#include <occi.h>
using namespace oracle::occi;
using namespace std;
typedef vector<string> journal;
class occicoll
{
private:
Environment *env;
Connection *conn;
Statement *stmt;
string tableName;
string typeName;
public:
occicoll (string user, string passwd, string db)
{
env = Environment::createEnvironment (Environment::OBJECT);
conn = env->createConnection (user, passwd, db);
initRows();
}
~occicoll ()
{
env->terminateConnection (conn);
Environment::terminateEnvironment (env);
}
void setTableName (string s)
{
tableName = s;
}
void initRows ()
{
try{
Statement *st1 = conn->createStatement ("DELETE FROM journal_tab");
st1->executeUpdate ();
st1->setSQL("INSERT INTO journal_tab (jid, jname) VALUES (22, journal
('NATION', 'TIMES'))");
st1->executeUpdate ();
st1->setSQL("INSERT INTO journal_tab (jid, jname) VALUES (33, journal
('CRICKET', 'ALIVE'))");
st1->executeUpdate ();
conn->commit();
conn->terminateStatement (stmt);
}catch(SQLException ex)
{
cout<<ex.what();
}
}
/**
* Insertion of a row
*/
void insertRow ()
{
int c1 = 11;
journal c2;
c2.push_back ("LIFE");
c2.push_back ("TODAY");
c2.push_back ("INVESTOR");
cout << "Inserting row with jid = " << 11 <<
" and journal_tab (LIFE, TODAY, INVESTOR )" << endl;
try{
stmt = conn->createStatement (
"INSERT INTO journal_tab (jid, jname) VALUES (:x, :y)");
stmt->setInt (1, c1);
setVector (stmt, 2, c2, "JOURNAL");
stmt->executeUpdate ();
}catch(SQLException ex)
{
cout<<"Exception thrown for insertRow"<<endl;
cout<<"Error number: "<< ex.getErrorCode() << endl;
cout<<ex.getMessage() << endl;
}
cout << "Insertion - Successful" << endl;
conn->terminateStatement (stmt);
}
// Displaying all the rows of the table
void displayAllRows ()
{
cout << "Displaying all the rows of the table" << endl;
stmt = conn->createStatement (
"SELECT jid, jname FROM journal_tab");
journal c2;
ResultSet *rs = stmt->executeQuery();
try{
while (rs->next())
{
cout << "jid: " << rs->getInt(1) << endl;
cout << "jname: ";
getVector (rs, 2, c2);
for (int i = 0; i < c2.size(); ++i)
cout << c2[i] << " ";
cout << endl;
}
}catch(SQLException ex)
{
cout<<"Exception thrown for displayRow"<<endl;
cout<<"Error number: "<< ex.getErrorCode() << endl;
cout<<ex.getMessage() << endl;
}
stmt->closeResultSet (rs);
conn->terminateStatement (stmt);
} // End of displayAllRows()
Top
回复人: masterz(www.fruitfruit.com) ( ) 信誉:100 2005-03-05 16:54:00 得分: 0
// Deleting a row in a nested table
void deleteRow (int c1, string str)
{
cout << "Deleting a row in a nested table of strings" << endl;
stmt = conn->createStatement (
"SELECT jname FROM journal_tab WHERE jid = :x");
journal c2;
stmt->setInt (1, c1);
ResultSet *rs = stmt->executeQuery();
try{
if (rs->next())
{
getVector (rs, 1, c2);
c2.erase (find (c2.begin(), c2.end(), str));
}
stmt->setSQL ("UPDATE journal_tab SET jname = :x WHERE jid = :y");
stmt->setInt (2, c1);
setVector (stmt, 1, c2, "JOURNAL");
stmt->executeUpdate ();
}catch(SQLException ex)
{
cout<<"Exception thrown for delete row"<<endl;
cout<<"Error number: "<< ex.getErrorCode() << endl;
cout<<ex.getMessage() << endl;
}
cout << "Deletion - Successful" << endl;
conn->commit();
stmt->closeResultSet (rs);
conn->terminateStatement (stmt);
} // End of deleteRow (int, string)
// Updating a row of the nested table of strings
void updateRow (int c1, string str)
{
cout << "Updating a row of the nested table of strings" << endl;
stmt = conn->createStatement (
"SELECT jname FROM journal_tab WHERE jid = :x");
journal c2;
stmt->setInt (1, c1);
ResultSet *rs = stmt->executeQuery();try{
if (rs->next())
{
getVector (rs, 1, c2);
c2[0] = str;
}
stmt->setSQL ("UPDATE journal_tab SET jname = :x WHERE jid = :y");
stmt->setInt (2, c1);
setVector (stmt, 1, c2, "JOURNAL");
stmt->executeUpdate ();
}catch(SQLException ex)
{
cout<<"Exception thrown for updateRow"<<endl;
cout<<"Error number: "<< ex.getErrorCode() << endl;
cout<<ex.getMessage() << endl;
}
cout << "Updation - Successful" << endl;
conn->commit();
stmt->closeResultSet (rs);
conn->terminateStatement (stmt);
} // End of UpdateRow (int, string)
};//end of class occicoll
int main (void)
{
string user = "SCOTT";
string passwd = "TIGER";
string db = "";
try
{
cout << "occicoll - Exhibiting simple insert, delete & update operations"
" on table having a Nested Table column" << endl;
occicoll *demo = new occicoll (user, passwd, db);
cout << "Displaying all rows before the operations" << endl;
demo->displayAllRows ();
demo->insertRow ();
demo->deleteRow (11, "TODAY");
demo->updateRow (33, "New_String");
cout << "Displaying all rows after all the operations" << endl;
demo->displayAllRows ();
delete (demo);
cout << "occicoll - done" << endl;
}catch (SQLException ea)
{
cerr << "Error running the demo: " << ea.getMessage () << endl;
}
}
Top
回复人: masterz(www.fruitfruit.com) ( ) 信誉:100 2005-03-05 16:56:00 得分: 0
www.fruitfruit.com/oci9i.zip
The following code example demonstrates how to perform simple insert, delete,
and update operations on a table column of type Nested Table:
/**
*occicoll - To exhibit simple insert, delete & update operations"
* " on table having a Nested Table column
*
*Description
* Create a program which has insert,delete and update on a
* table having a Nested table column.
* Perform all these operations using OCCI interface.
*/
#include <iostream.h>
#include <occi.h>
using namespace oracle::occi;
using namespace std;
typedef vector<string> journal;
class occicoll
{
private:
Environment *env;
Connection *conn;
Statement *stmt;
string tableName;
string typeName;
public:
occicoll (string user, string passwd, string db)
{
env = Environment::createEnvironment (Environment::OBJECT);
conn = env->createConnection (user, passwd, db);
initRows();
}
~occicoll ()
{
env->terminateConnection (conn);
Environment::terminateEnvironment (env);
}
void setTableName (string s)
{
tableName = s;
}
void initRows ()
{
try{
Statement *st1 = conn->createStatement ("DELETE FROM journal_tab");
st1->executeUpdate ();
st1->setSQL("INSERT INTO journal_tab (jid, jname) VALUES (22, journal
('NATION', 'TIMES'))");
st1->executeUpdate ();
st1->setSQL("INSERT INTO journal_tab (jid, jname) VALUES (33, journal
('CRICKET', 'ALIVE'))");
st1->executeUpdate ();
conn->commit();
conn->terminateStatement (stmt);
}catch(SQLException ex)
{
cout<<ex.what();
}
}
/**
* Insertion of a row
*/
void insertRow ()
{
int c1 = 11;
journal c2;
c2.push_back ("LIFE");
c2.push_back ("TODAY");
c2.push_back ("INVESTOR");
cout << "Inserting row with jid = " << 11 <<
" and journal_tab (LIFE, TODAY, INVESTOR )" << endl;
try{
stmt = conn->createStatement (
"INSERT INTO journal_tab (jid, jname) VALUES (:x, :y)");
stmt->setInt (1, c1);
setVector (stmt, 2, c2, "JOURNAL");
stmt->executeUpdate ();
}catch(SQLException ex)
{
cout<<"Exception thrown for insertRow"<<endl;
cout<<"Error number: "<< ex.getErrorCode() << endl;
cout<<ex.getMessage() << endl;
}
cout << "Insertion - Successful" << endl;
conn->terminateStatement (stmt);
}
// Displaying all the rows of the table
void displayAllRows ()
{
cout << "Displaying all the rows of the table" << endl;
stmt = conn->createStatement (
"SELECT jid, jname FROM journal_tab");
journal c2;
ResultSet *rs = stmt->executeQuery();
try{
while (rs->next())
{
cout << "jid: " << rs->getInt(1) << endl;
cout << "jname: ";
getVector (rs, 2, c2);
for (int i = 0; i < c2.size(); ++i)
cout << c2[i] << " ";
cout << endl;
}
}catch(SQLException ex)
{
cout<<"Exception thrown for displayRow"<<endl;
cout<<"Error number: "<< ex.getErrorCode() << endl;
cout<<ex.getMessage() << endl;
}
stmt->closeResultSet (rs);
conn->terminateStatement (stmt);
} // End of displayAllRows()
Top
回复人: masterz(www.fruitfruit.com) ( ) 信誉:100 2005-03-05 16:54:00 得分: 0
// Deleting a row in a nested table
void deleteRow (int c1, string str)
{
cout << "Deleting a row in a nested table of strings" << endl;
stmt = conn->createStatement (
"SELECT jname FROM journal_tab WHERE jid = :x");
journal c2;
stmt->setInt (1, c1);
ResultSet *rs = stmt->executeQuery();
try{
if (rs->next())
{
getVector (rs, 1, c2);
c2.erase (find (c2.begin(), c2.end(), str));
}
stmt->setSQL ("UPDATE journal_tab SET jname = :x WHERE jid = :y");
stmt->setInt (2, c1);
setVector (stmt, 1, c2, "JOURNAL");
stmt->executeUpdate ();
}catch(SQLException ex)
{
cout<<"Exception thrown for delete row"<<endl;
cout<<"Error number: "<< ex.getErrorCode() << endl;
cout<<ex.getMessage() << endl;
}
cout << "Deletion - Successful" << endl;
conn->commit();
stmt->closeResultSet (rs);
conn->terminateStatement (stmt);
} // End of deleteRow (int, string)
// Updating a row of the nested table of strings
void updateRow (int c1, string str)
{
cout << "Updating a row of the nested table of strings" << endl;
stmt = conn->createStatement (
"SELECT jname FROM journal_tab WHERE jid = :x");
journal c2;
stmt->setInt (1, c1);
ResultSet *rs = stmt->executeQuery();try{
if (rs->next())
{
getVector (rs, 1, c2);
c2[0] = str;
}
stmt->setSQL ("UPDATE journal_tab SET jname = :x WHERE jid = :y");
stmt->setInt (2, c1);
setVector (stmt, 1, c2, "JOURNAL");
stmt->executeUpdate ();
}catch(SQLException ex)
{
cout<<"Exception thrown for updateRow"<<endl;
cout<<"Error number: "<< ex.getErrorCode() << endl;
cout<<ex.getMessage() << endl;
}
cout << "Updation - Successful" << endl;
conn->commit();
stmt->closeResultSet (rs);
conn->terminateStatement (stmt);
} // End of UpdateRow (int, string)
};//end of class occicoll
int main (void)
{
string user = "SCOTT";
string passwd = "TIGER";
string db = "";
try
{
cout << "occicoll - Exhibiting simple insert, delete & update operations"
" on table having a Nested Table column" << endl;
occicoll *demo = new occicoll (user, passwd, db);
cout << "Displaying all rows before the operations" << endl;
demo->displayAllRows ();
demo->insertRow ();
demo->deleteRow (11, "TODAY");
demo->updateRow (33, "New_String");
cout << "Displaying all rows after all the operations" << endl;
demo->displayAllRows ();
delete (demo);
cout << "occicoll - done" << endl;
}catch (SQLException ea)
{
cerr << "Error running the demo: " << ea.getMessage () << endl;
}
}
Top
回复人: masterz(www.fruitfruit.com) ( ) 信誉:100 2005-03-05 16:56:00 得分: 0
www.fruitfruit.com/oci9i.zip
解决方案 »
- HP unix系统装了oracle10G,报错版本的8.1.7
- 数据表填充SQL求解
- 重作日志挖掘的问题,请求高手指教!!!
- 请教PL/SQL包的问题,不知是否发在这里
- yaozw_mountain(山林)yaozw_mountain(山林)
- oracel8.16内存调整后的问题
- oracle本来用的挺好的,后来连接出现了无法解析连接描述符中制定的SID
- 如何启动服务:Oracle Management Server?
- 怎样买oracle?配置plsql网络应用程序要用9iAS和db,怎样购买license?cpu和named user怎么算?
- oracle登陆异常
- 如何将oracle 9i中一个用户拥有的“角色”全部取消?
- pro*c/c++ 中怎样调用带有结果集输出(游标引用输出参数)的存储过程?
http://www.cnblogs.com/allanyan/articles/105159.html
#include <iostream>
#include <stdio.h>
using namespace std;#define OTL_ORA9I // Compile OTL 4.0/OCI9i
#include "otlv4.h" // include the OTL 4.0 header fileotl_connect db; // connect objectvoid select()
{
otl_stream i(1, // buffer size
"SELECT * FROM ABC",
// SELECT statement
db // connect object
);
// create select stream
int a;
cout<<"Now in table"<<endl;
cout<<"| A |"<<endl;
cout<<"======="<<endl;
while(!i.eof())
{
// while not end-of-data
i>>a;
cout<<" "<<a<<endl;
}
}void deleted()
{
cout<<"Clear old data"<<endl;
otl_stream i (1,
"delete abc",
db
);
}void insert()
{
otl_stream i (1,
"insert into abc values(:A<int>)",
db
);
int j=0;
for(j=0;j<10;j++)
{
cout<<"insert "<<j+1<<endl;
i<<j+1;
}
}int main()
{
otl_connect::otl_initialize(); // initialize OCI environment
try
{
cout<<"Connect to database..."<<endl;
db.rlogon("scott/tiger@oracle9i"); // connect to Oracle
deleted();
insert();
select();
}
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.sqlstate<<endl; // print out SQLSTATE message
cerr<<p.var_info<<endl; // print out the variable that caused the error
} db.logoff(); // disconnect from Oracle return 0;
}