Returning编程示例

Returning Into简介

在进行insert、update和delete操作的时候，都可以在末尾加入returning into字句。这字句的作用是将进行DML操作影响到数据行的列值，保存进指定的变量中。 使用该字句的效果，与进行insert、update之后执行select ，以及在delete之前进行select 的效果相同。

returning

/*创建环境句柄、到数据库的连接 和 statement*/
Environment *env = Environment::createEnvironment();
Connection *conn = env->createConnection(user,pwd,link);
Statement *stmt = conn->createStatement();

string drop_tab = "drop table returning_test";
string create_tab = "create table returning_test(id int, name varchar(20))";
string insert_tab = "insert into returning_test values(1, 'zhangsan')";
string select_tab = "select name from returning_test where id = 1";
string update_returning = "update returnint_test set name = 'fawaikuangtu' returning into :name ";
string name;
int val = 0;


stmt->execute(drop_tab);
stmt->execute(create_tab);

/*插入数据*/
stmt->setSQL(insert_tab);
stmt->execute();
stmt->setSQL(select_tab);
ResultSet* rs = stmt->executeQuery();
rs->next();
string name = rs->getString(1);
// name.c_str() = "zhangsan"

/*测试returning语句--insert*/
stmt->setSQL("insert into returning_test values(2,'lisi') returning id,name into :1,:2");
stmt->registerOutParam(1, ACCIINT);
stmt->registerOutParam(2, ACCISTRING,50);
stmt->execute();
val = stmt->getInt(1);
name = stmt->getString(2);
conn->commit();

/*测试returning语句--update*/
stmt->setSQL("update returning_test set name = 'fawaikuangtu' where id = 2 return name into :1");
stmt->registerOutParam(1, ACCISTRING, 50);
stmt->execute();
name = stmt->getString(1);
conn->commit();

/*测试returning语句--delete*/
stmt->setSQL("update returning_test set name = 'fawaikuangtu' where id = 2 return name into :1");
stmt->registerOutParam(1, ACCISTRING, 50);
stmt->execute();
name = stmt->getString(1);
conn->commit();


/*释放statement、数据库连接 和 环境句柄*/
conn->terminateStatement(stmt);
env->terminateConnection(conn);
Environment::terminateEnvironment(env);