#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;
}
