vs2019通过OCCI实现的Oracle增删改查试验记录

本文主要是介绍vs2019通过OCCI实现的Oracle增删改查试验记录，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

首先大部分代码参考：https://blog.csdn.net/weixin_41049188/article/details/106606192

一、新建头文件occiemp.h

#pragma once
#include <iostream>
#include <occi.h>
using namespace oracle::occi;
using namespace std;

typedef struct emptable
{
	string username;
	string password;
	string nickname;
}EMP;

class occiemp
{
public:
	occiemp(string user, string passwd, string db);
	~occiemp();

	void displayAllRows();//显示所有数据
	void updateRow(string nickname, string username);
	void deleteRow(string nickname);
	void insertRow(EMP emp);//插入一行数据
	Date Todate(string time);
private:
	Environment* env = nullptr;//上下文环境
	Connection* conn = nullptr;//数据库连接句柄
	Statement* stmt = nullptr;//指向SQL语句声明类

};

二、新建occiemp.cpp

#include "occiemp.h"
#include <iomanip> 
//构造函数
occiemp::occiemp(string user, string passwd, string db)
{
	try
	{
		this->env = Environment::createEnvironment();//创建环境
		this->conn = env->createConnection(user, passwd, db);//创建连接
	}
	catch (SQLException ex)
	{
		cout << "Error number: " << ex.getErrorCode() << endl;
		cout << ex.getMessage() << endl;
	}

}

//析构函数
occiemp::~occiemp()
{
	if (this->env != nullptr)
	{
		this->env->terminateConnection(this->conn);//释放连接
	}
	if (this->env)
	{
		Environment::terminateEnvironment(this->env);//释放环境
	}
}

//显示所有数据
void occiemp::displayAllRows()
{
	string sqlStmt = "select * from JAVATEST1";//order by empno
	this->stmt = this->conn->createStatement(sqlStmt);
	//执行查询语句
	ResultSet* rset = this->stmt->executeQuery();//ResultSet提供对通过执行生成的数据表的访问Statement。表行按顺序检索。在一行中，可以按任何顺序访问列值。ResultSet保持光标指向其当前数据行。最初，光标位于第一行之前。next() 方法将光标移动到下一行。
	try
	{
		cout << "username" << setw(20) << "password" << setw(20) << "nickname" << endl;
		while (rset->next())
		{
			cout << rset->getString(1) << setw(20) << rset->getString(2) << setw(20) << rset->getString(3) << endl;
		}
	}
	catch (SQLException ex)
	{
		cout << "Exception thrown for displayAllRows" << endl;
		cout << "Error number: " << ex.getErrorCode() << endl;
		cout << ex.getMessage() << endl;
	}
	this->stmt->closeResultSet(rset);//释放集合数据
	this->conn->terminateStatement(this->stmt);//释放SQL语句
}

//更新数据
void occiemp::updateRow(string nickname, string username)
{
	string sqlStmt = "UPDATE JAVATEST1 SET \"nickname\" = :x WHERE \"username\" = :y ";
	try
	{
		this->stmt = this->conn->createStatement(sqlStmt);
		stmt->setString(1, nickname);
		stmt->setString(2, username);
		//执行非查询语句
		unsigned int res = stmt->executeUpdate();
		if (res > 0)
		{
			cout << "update - Success " << res << " 行受影响。" << endl;
		}

	}
	catch (SQLException ex)
	{
		cout << "Exception thrown for updateRow" << endl;
		cout << "Error number: " << ex.getErrorCode() << endl;
		cout << ex.getMessage() << endl;
	}

	this->conn->terminateStatement(this->stmt);
}

//根据条件删除数据
void occiemp::deleteRow(string username)
{
	string sqlStmt = "DELETE FROM JAVATEST1 WHERE \"username\" = :x ";

	try
	{
		this->stmt = this->conn->createStatement(sqlStmt);
		this->stmt->setString(1, username);
		unsigned int res = this->stmt->executeUpdate();
		if (res > 0)
		{
			cout << "delete - Success" << res << " 行受影响。" << endl;
		}

	}
	catch (SQLException ex)
	{
		cout << "Exception thrown for deleteRow" << endl;
		cout << "Error number: " << ex.getErrorCode() << endl;
		cout << ex.getMessage() << endl;
	}

	this->conn->terminateStatement(this->stmt);
}

//插入一行数据
void occiemp::insertRow(EMP emp)
{
	string sqlStmt = "INSERT INTO JAVATEST1 VALUES (:x1, :x2, :x3)";
	this->stmt = this->conn->createStatement(sqlStmt);
	try
	{
		this->stmt->setString(1, emp.username);
		this->stmt->setString(2, emp.password);
		this->stmt->setString(3, emp.nickname);

		unsigned int res = this->stmt->executeUpdate();
		if (res > 0)
		{
			cout << "Data saved successfully ," << res << " 行数据！" << endl;
		}
	}
	catch (SQLException ex)
	{
		cout << "Exception thrown for insertRow of emp" << endl;
		cout << "Error number: " << ex.getErrorCode() << endl;
		cout << ex.getMessage() << endl;
	}
	this->conn->terminateStatement(this->stmt);//释放SQL语句
}

Date occiemp::Todate(string strtime)
{
	try
	{
		int year = stoi((strtime.substr(0, 4)));
		unsigned int month = stoi((strtime.substr(4, 2)));
		unsigned int day = stoi((strtime.substr(6, 2)));
		unsigned int hour = stoi((strtime.substr(8, 2)));
		unsigned int minute = stoi((strtime.substr(10, 2)));
		unsigned int seconds = stoi((strtime.substr(12, 2)));
		Date date(this->env, year, month, day, hour, minute, seconds);
		return date;

	}
	catch (const std::exception& e)
	{
		cout << e.what() << endl;
		return nullptr;
	}

}

三、main.cpp

#define WIN32COMMON
#include<cstdlib>
#include"occiemp.h"

using namespace std;
using namespace oracle::occi;


int main(void)
{
	string username = "scott";
	string password = "tiger";
	string srvName = "localhost:1521/orcl";

	try
	{
		occiemp* emp = new occiemp(username, password, srvName);
		emp->displayAllRows();

		//EMP info{"yyy","1234","yyyaa"};

		//emp->insertRow(info);

		//emp->displayAllRows();

		//emp->updateRow("ayyy","yyy");

		//emp->displayAllRows();

		//emp->deleteRow("yyy");

		//emp->displayAllRows();

		delete emp;
	}
	catch (const std::exception& e)
	{
		cout << e.what() << endl;
		system("pause");
		return -1;
	}

	return 0;
}

四、测试时曾出现的问题

一开始更新和删除的SQL语句中我是这样写的：

UPDATE JAVATEST1 SET nickname = :x WHERE username = :y

DELETE FROM JAVATEST1 WHERE username = :x

然后运行时出现了错误：“USERNAME”未定义标识符

后来百度知道Oracle在执行SQL语句时会自动变成大写，如username会变成USERNAME执行，因此出现了此错误。只有在字段名上加上双引号才能让它不自动变成大写执行。而在代码中，用\"字段名\"即可。

UPDATE JAVATEST1 SET \"nickname\" = :x WHERE \"username\" = :y

DELETE FROM JAVATEST1 WHERE \"username\" = :x

这篇关于vs2019通过OCCI实现的Oracle增删改查试验记录的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！