C++ 操作 Oracle
#include <string> #include <occi.h> #include <iostream> using namespace std; using namespace oracle::occi; struct Student_struct { int no; int age; string name; }; std::string getSQL(void) { std::string str_sql = "SELECT * FROM emp WHERE mgr=(:1)"; return str_sql; } /* 连接 Oracle 操作步骤 */ void connectOracle() { const string userName = "scott"; const string password = "tiger"; const string connString = "localhost:1521/orcl"; try { Environment *env = Environment::createEnvironment(Environment::DEFAULT); Connection *con = env->createConnection(userName, password, connString); cout << "Success to connect!" << endl; Statement *stmt = con->createStatement(); //动态 SQL stmt->setSQL("SELECT * FROM emp WHERE mgr=(:1) AND sal=(:2)"); stmt->setInt(1, 7902); stmt->setInt(2, 800); ResultSet *rs = stmt->executeQuery(); while (rs->next()) { int no = rs->getInt(1); string name = rs->getString(2); cout << "no is:" << no << endl; cout << "name :" << name << endl; cout << "MGR : "<<rs->getInt(4)<<endl; cout << "SAL :"<<rs->getInt(6)<<endl; } con->terminateStatement(stmt); env->terminateConnection(con); Environment::terminateEnvironment(env); } catch (SQLException &ex) { cout << ex.what() << endl; } system("pause"); } /* 增加 数据 */ void insertData(Connection *p_conn) { Statement *p_stmt = NULL; int m_no; string m_name; //string m_name; int m_age; int Flag = 0; //执行sql，返回结果并显示 cout << "PLEASE INPUT THE no! " << endl; for(;;) { fflush(stdin); if((!scanf_s("%d", &m_no)) || (m_no) < 0) { cout << "The no that you just input is invalid,please input it again !" << endl; continue; } else break; } string Verify_strsql( "select * from student where no =(:1)" ); p_stmt = p_conn->createStatement( Verify_strsql ); p_stmt->setInt(1, m_no); ResultSet *rset = p_stmt-> executeQuery(); /*while( rset->next() ) { Flag = rset->getInt(1); }*/ rset->next(); Flag = rset->getInt(1); if((m_no != 0) && (Flag != m_no)) /*判断是否为0或已经存在*/ { try { cout << "PLEASE INPUT THE name! " << endl; cin >> m_name; cout << "PLEASE INPUT THE age! " << endl; for(;;) { fflush(stdin); if((!scanf_s("%d", &m_age)) || (m_age) < 0) { cout << "The age that you just input is invalid,please input it again !!" << endl; continue; } else break; } string strsql = "insert into student (no,name,age) values (:1,:2,:3)"; p_stmt = p_conn->createStatement( strsql ); //p_stmt->setSQL(strsql); p_stmt->setInt(1, m_no); p_stmt->setString(2, m_name); p_stmt->setInt(3, m_age); cout << "The Statement that you input is " << strsql << endl; p_stmt-> executeUpdate(); p_conn->commit(); cout << "Successfully inserted a new record ! " << endl; } catch(SQLException *e) { cout << "exception: " << e->what() << endl; } p_conn->terminateStatement(p_stmt); } else { cout << "The user_no is invalid or has been exist!,please input it again!" << endl; } } /* 删除 数据 */ void deleteData(Connection *p_conn) { int m_no; int Flag = 0; Statement *p_stmt = NULL; //执行sql，返回结果并显示 //delete_data: cout << "Please input the number that you want to delete :" << endl; for(;;) { fflush(stdin); if(!scanf_s("%d", &m_no)) { cout << "输入字符出错！请重新输入!" << endl; continue; } else break; } string Verify_strsql( "select * from student where no =(:1)" ); p_stmt = p_conn->createStatement( Verify_strsql ); p_stmt->setInt(1, m_no); ResultSet *rset = p_stmt-> executeQuery(); /*while( rset->next() ) { Flag = rset->getInt(1); }*/ rset->next(); Flag = rset->getInt(1); if(Flag) { string strsql( "delete from student where no = (:1)"); p_stmt = p_conn->createStatement( strsql ); p_stmt->setInt(1, m_no); try { p_stmt-> executeUpdate(); p_conn->commit(); cout << "Successfully deleted an old record !" << endl; } catch(SQLException *e) { cout << "exception: " << e->what() << endl; } } else { cout << "The number that you just input didn't exist! please input a new number!" << endl; cout << "_____________________________________________________________________" << endl; //goto delete_data; } p_conn->terminateStatement(p_stmt); } /* 修改 数据 */ void setData(Connection *p_conn) { Statement *p_stmt = NULL; try { int m_no; int Flag = 0; string m_name; int m_age; cout << "Please input the number that you want to updata :" << endl; //cin>>m_no; for(;;) { fflush(stdin); if(!scanf_s("%d", &m_no)) { cout << "输入字符出错！请重新输入!" << endl; continue; } else break; } string Verify_strsql( "select * from student where no =(:1)" ); p_stmt = p_conn->createStatement( Verify_strsql ); p_stmt->setInt(1, m_no); ResultSet *rset = p_stmt-> executeQuery(); /*while( rset->next() ) { Flag = rset->getInt(1); }*/ rset->next(); Flag = rset->getInt(1); if(Flag) { cout << "PLEASE INPUT THE NEW name! " << endl; cin >> m_name; cout << "PLEASE INPUT THE NEW age! " << endl; //cin>>m_age; for(;;) { fflush(stdin); if((!scanf_s("%d", &m_age)) || (m_age) < 0) { cout << "The age that you just input is invalid,please input it again !!" << endl; continue; } else break; } string strsql = "update student set name=(:1),age=(:2) where no=(:3)"; p_stmt = p_conn->createStatement( strsql ); //p_stmt -> setSQL(strsql); int number = m_no; string name = m_name; int age = m_age; p_stmt->setInt(2, age); p_stmt->setString(1, name); p_stmt->setInt(3, number); p_stmt-> executeUpdate(); //cout <<"The Statement that you input is "<<strsql<< endl; cout << p_stmt->getSQL() << endl; p_conn->commit(); cout << " SUCCESSFULLY UPDATA A NEW RECORD !" << endl; } else { cout << "The number that you just input didn't exist!please input a new number!" << endl; cout << "_____________________________________________________________________" << endl; } } catch(SQLException *e) { cout << "exception: " << e->what() << endl; } p_conn->terminateStatement(p_stmt); } /* 查找 数据 */ void displayData(Connection *p_conn) { Statement *p_stmt = NULL; int row = 1; string strsql( "select * from student order by no" ); p_stmt = p_conn->createStatement( strsql ); ResultSet *rset = p_stmt-> executeQuery(); //查询语句 try { cout << setiosflags(ios::left) << setw(5) << "行号" << setiosflags(ios::left) << setw(5) << " 编号" << setiosflags(ios::left) << setw(8) << " 姓名" << setiosflags(ios::left) << setw(8) << " 年龄" << endl; while( rset->next() ) { int no = rset->getInt(1); int age = rset->getInt(3); string name = rset->getString(2); cout << "row." << setiosflags(ios::left) << setw(5) << row << " no=" << setiosflags(ios::left) << setw(5) << no << " name=" << setiosflags(ios::left) << setw(8) << name << " age=" << setiosflags(ios::left) << setw(10) << age << endl; row++; } } catch (SQLException *e) { cout << "exception: " << e->what() << endl; } p_stmt->closeResultSet(rset); p_conn->terminateStatement(p_stmt); } void findData(Connection *p_conn) { Statement *p_stmt = NULL; int no; int Flag = 0; cout << "Please input the number that you want to search :" << endl; //cin>> no; for(;;) { fflush(stdin); if(!scanf_s("%d", &no)) { cout << "输入字符出错！请重新输入!" << endl; continue; } else break; } string Verify_strsql( "select * from student where no =(:1)" ); p_stmt = p_conn->createStatement( Verify_strsql ); p_stmt->setInt(1, no); ResultSet *rset = p_stmt-> executeQuery(); /*while( rset->next() ) { Flag = rset->getInt(1); }*/ rset->next(); Flag = rset->getInt(1); if(Flag) { string strsql( "select * from student where no =(:1)" ); p_stmt = p_conn->createStatement( strsql ); p_stmt->setInt(1, no); ResultSet *rset = p_stmt-> executeQuery(); //查询语句 try { while( rset->next() ) { int no = rset->getInt(1); int age = rset->getInt(3); string name = rset->getString(2); cout << "no==" << no << " name==" << name << " age==" << age << endl; } } catch (SQLException *e) { cout << "exception: " << e->what() << endl; } } else { cout << "The data that you just input didn't exist! please input a new number!" << endl; cout << "___________________________________________________________________" << endl; } p_stmt->closeResultSet(rset); p_conn->terminateStatement(p_stmt); } void Export_data(Connection *p_conn) { unsigned int i = 0; Statement *p_stmt = NULL; Student_struct m_struct_student; vector<Student_struct> stu_strc; stu_strc.reserve(100); string strsql( "select * from student order by no" ); p_stmt = p_conn->createStatement( strsql ); ResultSet *rset = p_stmt-> executeQuery(); //查询语句 try { while( rset->next() ) { m_struct_student.no = rset->getInt(1); m_struct_student.age = rset->getInt(3); m_struct_student.name = rset->getString(2); stu_strc.push_back(m_struct_student); } /*vector<Student_struct>::iterator iter; iter = stu_strc.begin(); while(iter != stu_strc.end()) { cout<<*(iter++)<<endl; }*/ ofstream Vector_to_file("d:\\Database_file.txt"); if(!Vector_to_file) { cout << "error" << endl; return; } else { for(i = 0; i < stu_strc.size(); i++) { Vector_to_file << setiosflags(ios::left) << setw(8) << stu_strc[i].no << setiosflags(ios::left) << setw(8) << stu_strc[i].name << " " << setiosflags(ios::left) << setw(8) << stu_strc[i].age << endl; } Vector_to_file.close(); cout << "您已成功将以下数据写入d:\\Database_file.txt文件中 !" << endl; cout << endl; cout << setiosflags(ios::left) << setw(8) << "编号" << " " << setiosflags(ios::left) << setw(8) << "姓名" << " " << setiosflags(ios::left) << setw(8) << "年龄" << endl; for(i = 0; i < stu_strc.size(); i++) { cout << setiosflags(ios::left) << setw(8) << stu_strc[i].no << " " << setiosflags(ios::left) << setw(8) << stu_strc[i].name << " " << setiosflags(ios::left) << setw(8) << stu_strc[i].age << endl; } } //return EXIT_SUCCESS; } catch (SQLException *e) { cout << "exception: " << e->what() << endl; } p_stmt->closeResultSet(rset); p_conn->terminateStatement(p_stmt); } void Vector_save(Connection *p_conn) { Statement *p_stmt = NULL; Student_struct m_struct_student;//结构体变量 vector<Student_struct> stu_strc;//声明一个向量 stu_strc.reserve(100); string strsql( "select * from student order by no" ); p_stmt = p_conn->createStatement( strsql ); ResultSet *rset = p_stmt-> executeQuery(); //查询语句 try { while( rset->next() ) { m_struct_student.no = rset->getInt(1); m_struct_student.age = rset->getInt(3); m_struct_student.name = rset->getString(2); stu_strc.push_back(m_struct_student); } /*vector<Student_struct>::iterator iter; iter = stu_strc.begin(); while(iter != stu_strc.end()) { cout<<*(iter++)<<endl; }*/ cout << "您已成功将以下数据装入vector容器中 !" << endl; cout << endl; cout << setiosflags(ios::left) << setw(5) << "编号" << " " << setiosflags(ios::left) << setw(5) << "姓名" << " " << setiosflags(ios::left) << setw(5) << "年龄" << endl; for(unsigned int i = 0; i < stu_strc.size(); i++) { cout << setiosflags(ios::left) << setw(5) << stu_strc[i].no << " " << setiosflags(ios::left) << setw(5) << stu_strc[i].name << " " << setiosflags(ios::left) << setw(5) << stu_strc[i].age << endl; } } catch (SQLException *e) { cout << "exception: " << e->what() << endl; } p_stmt->closeResultSet(rset); p_conn->terminateStatement(p_stmt); } int main() { connectOracle(); return 0; }
