C++ 操作 Oracle-阿里云开发者社区

开发者社区> 数据库> 正文

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


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

}



版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章