9.5 使用 SQL 结果集_X DevAPI 用户指南

X DevAPI 用户指南 / 第 9 章使用结果集 / 9.5 使用 SQL 结果集

9.5 使用 SQL 结果集

sql()当您使用返回 SqlResult 的方法在 Session 上执行 SQL 操作时。迭代 SqlResult 与处理来自 CRUD 操作的结果相同。以下示例假定用户表存在。

MySQL 外壳 JavaScript 代码

var res = mySession.sql('SELECT name, age FROM users').execute();

var row;
while (row = res.fetchOne()) {
  print('Name: ' + row['name'] + '\n');
  print(' Age: ' + row.age + '\n');
}

MySQL 外壳 Python 代码

res = mySession.sql('SELECT name, age FROM users').execute()

row = res.fetch_one()

while row:
    print('Name: %s\n' % row[0])
    print(' Age: %s\n' % row.age)
    row = res.fetch_one()

Node.js JavaScript 代码

mySession.sql('SELECT name, age FROM users')
  .execute()
  .then(res => {
    while (row = res.fetchOne()) {
      console.log('Name: ' + row[0] + '\n');
      console.log(' Age: ' + row[1] + '\n');
    }
  });

或者，您可以使用回调：

mySession.sql('SELECT name, age FROM users')
  .execute(function (row) {
    console.log('Name: ' + row[0] + '\n');
    console.log(' Age: ' + row[1] + '\n');
});

C#代码

var res = Session.SQL("SELECT name, age FROM users").Execute();

while (res.Next())
{
  Console.WriteLine("Name: " + res.Current["name"]);
  Console.WriteLine("Age: " + res.Current["age"]);
}

Python代码

# Connector/Python
res = mySession.sql('SELECT name, age FROM users').execute()

row = res.fetch_one()

while row:
        print('Name: %s\n' % row[0])
        print(' Age: %s\n' % row.age)
        row = res.fetch_one()

Java代码

SqlResult res = mySession.sql("SELECT name, age FROM users").execute();

Row row;
while ((row = res.fetchOne()) != null) {
  System.out.println(" Name: " + row.getString("name") + "\n");
  System.out.println(" Age: " + row.getInt("age") + "\n");
}

C++代码

SqlResult res = mysession.sql("SELECT name, age FROM users").execute();

Row row;
while ((row = res.fetchOne())) {
  cout << "Name: " << row[0] << endl;
  cout << " Age: " << row[1] << endl;
}

SqlResult 与 CRUD 操作返回的结果的不同之处在于结果集和数据集的表示方式。SqlResult 将生成的结果集（例如，生成的 INSERT）和数据集（例如，生成的）合并为SELECT一个。与 CRUD 操作不同，这两种类型之间没有区别。SqlResult 导出用于数据访问和检索最后插入的 id 或受影响的行数的方法。

使用该hasData()方法了解 SQLResult 是数据集还是结果。当要编写不知道 SQLResult 来源的代码时，该方法很有用。在编写通用应用程序函数以打印查询结果或处理存储过程结果时可能会出现这种情况。如果hasData()返回 true，则 SqlResult 源自 SELECT可以返回行的命令或类似命令。

返回值 true 不表示数据集是否包含任何行。数据集可以为空，例如如果fetchOne()返回 NULL 或 fetchAll()返回一个空列表则为空。以下示例假定该过程my_proc 存在。

MySQL 外壳 JavaScript 代码

var res = mySession.sql('CALL my_proc()').execute();

if (res.hasData()){

  var row = res.fetchOne();
  if (row){
    print('List of rows available for fetching.');
    do {
      print(row);
    } while (row = res.fetchOne());
  }
  else{
    print('Empty list of rows.');
  }
}
else {
  print('No row result.');
}

MySQL 外壳 Python 代码

res = mySession.sql('CALL my_proc()').execute()

if res.has_data():

    row = res.fetch_one()
    if row:
        print('List of rows available for fetching.')
        while row:
            print(row)
            row = res.fetch_one()
    else:
        print('Empty list of rows.')
else:
    print('No row result.')

Node.js JavaScript 代码

mySession.sql('CALL my_proc()')
  .execute()
  .then(function (res) {
    if (!res.hasData()) {
      return console.log('No row result.');
    }

    var row = res.fetchOne();

    if (!row) {
      return console.log('Empty list of rows.');
    }

    console.log('List of rows available for fetching.');

    do {
      console.log(row);
    } while (row = res.fetchOne());
})

C#代码

var res = Session.SQL("CALL my_proc()").Execute();

if (res.HasData)
{

  var row = res.FetchOne();
  if (row != null)
  {
    Console.WriteLine("List of rows available for fetching.");
    do
    {
      PrintResult(row);
    } while ((row = res.FetchOne()) != null);
  }
  else
  {
    Console.WriteLine("Empty list of rows.");
  }
}
else
{
  Console.WriteLine("No row result.");
}

Python代码

# Connector/Python
res = mySession.sql('CALL my_proc()').execute()

if res.has_data():

    row = res.fetch_one()
    if row:
        print('List of rows available for fetching.')
        while row:
            print(row)
            row = res.fetch_one()
    else:
        print('Empty list of rows.')
else:
    print('No row result.')

Java代码

SqlResult res = mySession.sql("CALL my_proc()").execute();

if (res.hasData()){

  Row row = res.fetchOne();
  if (row != null){
    System.out.println("List of rows available for fetching.");
    do {
     for (int c = 0; c < res.getColumnCount(); c++) {
      System.out.println(row.getString(c));
      }
    } while ((row = res.fetchOne()) != null);
  }
  else{
    System.out.println("Empty list of rows.");
  }
}
else {
  System.out.println("No row result.");
}

C++代码

SqlResult res = mysession.sql("CALL my_proc()").execute();

if (res.hasData())
{
  Row row = res.fetchOne();
  if (row)
  {
    cout << "List of rows available for fetching." << endl;
    do {
      cout << "next row: ";
      for (unsigned i=0 ; i < row.colCount(); ++i)
        cout << row[i] << ", ";
      cout << endl;
    } while ((row = res.fetchOne()));
  }
  else
  {
    cout << "Empty list of rows." << endl;
  }
}
else
{
  cout << "No row result." << endl;
}

调用 eitherfetchOne()或 fetchAll()whenhasResult() 表示 SQLResult 不是数据集是错误的。

MySQL 外壳 JavaScript 代码

function print_result(res) {
  if (res.hasData()) {
    // SELECT
    var columns = res.getColumns();
    var record = res.fetchOne();

    while (record){
      for (index in columns){
        print (columns[index].getColumnName() + ": " + record[index] + "\n");
      }

      // Get the next record
      record = res.fetchOne();
    }

  } else {
    // INSERT, UPDATE, DELETE, ...
    print('Rows affected: ' + res.getAffectedItemsCount());
  }
}

print_result(mySession.sql('DELETE FROM users WHERE age < 30').execute());
print_result(mySession.sql('SELECT * FROM users WHERE age = 40').execute());

MySQL 外壳 Python 代码

def print_result(res):
  if res.has_data():
    # SELECT
    columns = res.get_columns()
    record = res.fetch_one()

    while record:
      index = 0

      for column in columns:
        print("%s: %s \n" % (column.get_column_name(), record[index]))
        index = index + 1

      # Get the next record
      record = res.fetch_one()
  else:
    #INSERT, UPDATE, DELETE, ...
    print('Rows affected: %s' % res.get_affected_items_count())

print_result(mySession.sql('DELETE FROM users WHERE age < 30').execute())
print_result(mySession.sql('SELECT * FROM users WHERE age = 40').execute())

Node.js JavaScript 代码

function print_result(res) {
  if (res.hasData()) {
    // SELECT
    var columns = res.getColumns();
    var record = res.fetchOne();

    while (record) {
      for (index in columns) {
        console.log(columns[index].getColumnName() + ": " + record[index]);
      }

      // Get the next record
      record = res.fetchOne();
    }

  } else {
    // INSERT, UPDATE, DELETE, ...
    console.log('Rows affected: ' + res.getAffectedItemsCount());
  }
}

mySession.sql(`DELETE FROM users WHERE age < 30`)
  .execute()
  .then(function (res) {
    print_result(res);
  });

mySession.sql(`SELECT * FROM users WHERE age = 40`)
  .execute()
  .then(function (res) {
    print_result(res);
  });

C#代码

private void print_result(SqlResult res)
{
  if (res.HasData)
  {
    // SELECT
  }
  else
  {
    // INSERT, UPDATE, DELETE, ...
    Console.WriteLine("Rows affected: " + res.RecordsAffected);
  }
}

print_result(Session.SQL("DELETE FROM users WHERE age < 30").Execute());
print_result(Session.SQL("SELECT COUNT(*) AS forty FROM users WHERE age = 40").Execute());

Python代码

# Connector/Python
def print_result(res):
  if res.has_data():
    # SELECT
    columns = res.get_columns()
    record = res.fetch_one()

    while record:
      index = 0

      for column in columns:
        print("%s: %s \n" % (column.get_column_name(), record[index]))
        index = index + 1

      # Get the next record
      record = res.fetch_one()

  else:
    #INSERT, UPDATE, DELETE, ...
    print('Rows affected: %s' % res.get_affected_items_count())


print_result(mySession.sql('DELETE FROM users WHERE age < 30').execute())
print_result(mySession.sql('SELECT * FROM users WHERE age = 40').execute())

Java代码

private void print_result(SqlResult res) {
  if (res.hasData()) {
    // SELECT
     Row row;
        while ((row = res.fetchOne()) != null){
            for (int c = 0; c < res.getColumnCount(); c++) {
                System.out.println(row.getString(c));
            }
        }
  } else {
    // INSERT, UPDATE, DELETE, ...
    System.out.println("Rows affected: " + res.getAffectedItemsCount());
  }
}

print_result(mySession.sql("DELETE FROM users WHERE age < 30").execute());
print_result(mySession.sql("SELECT COUNT(*) AS forty FROM users WHERE age = 40").execute());

C++代码

void print_result(SqlResult &&_res)
{
  // Note: We need to store the result somewhere to be able to process it.

  SqlResult res(std::move(_res));

  if (res.hasData())
  {
    // SELECT
    const Columns &columns = res.getColumns();
    Row record = res.fetchOne();

    while (record)
    {
      for (unsigned index=0; index < res.getColumnCount(); ++index)
      {
        cout << columns[index].getColumnName() << ": "
             << record[index] << endl;
      }

      // Get the next record
      record = res.fetchOne();
    }

  }
  else
  {
    // INSERT, UPDATE, DELETE, ...
    // Note: getAffectedItemsCount() not yet implemented in Connector/C++.
    cout << "No rows in the result" << endl;
  }
}

print_result(mysession.sql("DELETE FROM users WHERE age < 30").execute());
print_result(mysession.sql("SELECT * FROM users WHERE age = 40").execute());

调用存储过程可能导致必须在单次执行过程中处理多个结果集。作为查询执行的结果，将返回一个 SQLResult 对象，它封装了第一个结果集。处理完结果集后，您可以调用nextResult()以前进到下一个结果（如果有）。一旦您前进到下一个结果集，它将替换先前加载的结果，该结果将变得不可用。

MySQL 外壳 JavaScript 代码

function print_result(res) {
  if (res.hasData()) {
    // SELECT
    var columns = res.getColumns();
    var record = res.fetchOne();

    while (record){
      for (index in columns){
        print (columns[index].getColumnName() + ": " + record[index] + "\n");
      }

      // Get the next record
      record = res.fetchOne();
    }

  } else {
    // INSERT, UPDATE, DELETE, ...
    print('Rows affected: ' + res.getAffectedItemsCount());
  }
}


var res = mySession.sql('CALL my_proc()').execute();

// Prints each returned result
var more = true;
while (more){
  print_result(res);

  more = res.nextResult();
}

MySQL 外壳 Python 代码

def print_result(res):
  if res.has_data():
    # SELECT
    columns = res.get_columns()
    record = res.fetch_one()

    while record:
      index = 0

      for column in columns:
        print("%s: %s \n" % (column.get_column_name(), record[index]))
        index = index + 1

      # Get the next record
      record = res.fetch_one()
  else:
    #INSERT, UPDATE, DELETE, ...
    print('Rows affected: %s' % res.get_affected_items_count())

res = mySession.sql('CALL my_proc()').execute()

# Prints each returned result
more = True
while more:
  print_result(res)
  more = res.next_result()

Node.js JavaScript 代码

function print_result(res) {
  if (res.hasData()) {
    // SELECT
    var columns = res.getColumns();
    var record = res.fetchOne();

    while (record) {
      for (index in columns) {
        console.log(columns[index].getColumnName() + ": " + record[index]);
      }

      // Get the next record
      record = res.fetchOne();
    }

  } else {
    // INSERT, UPDATE, DELETE, ...
    console.log('Rows affected: ' + res.getAffectedItemsCount());
  }
}

mySession.sql('CALL my_proc()')
  .execute()
  .then(function (res) {
    // Prints each returned result
    var more = true;

    while (more) {
      print_result(res);

      more = res.nextResult();
    }
  })

C#代码

var res = Session.SQL("CALL my_proc()").Execute();

if (res.HasData)
{
  do
  {
    Console.WriteLine("New resultset");
    while (res.Next())
    {
      Console.WriteLine(res.Current);
    }
  } while (res.NextResult());
}

Python代码

# Connector/Python
def print_result(res):
  if res.has_data():
    # SELECT
    columns = res.get_columns()
    record = res.fetch_one()

    while record:
      index = 0

      for column in columns:
        print("%s: %s \n" % (column.get_column_name(), record[index]))
        index = index + 1

      # Get the next record
      record = res.fetch_one()
  else:
    #INSERT, UPDATE, DELETE, ...
    print('Rows affected: %s' % res.get_affected_items_count())

res = mySession.sql('CALL my_proc()').execute()

# Prints each returned result
more = True
while more:
  print_result(res)

  more = res.next_result()

Java代码

SqlResult res = mySession.sql("CALL my_proc()").execute();

C++代码

SqlResult res = mysession.sql("CALL my_proc()").execute();

while (true)
{
  if (res.hasData())
  {
    cout << "List of rows in the resultset." << endl;
    for (Row row; (row = res.fetchOne());)
    {
      cout << "next row: ";
      for (unsigned i = 0; i < row.colCount(); ++i)
        cout << row[i] << ", ";
        cout << endl;
    }
  }
  else
  {
    cout << "No rows in the resultset." << endl;
  }

  if (!res.nextResult())
    break;

  cout << "Next resultset." << endl;
}

使用 Node.js 时，可以使用回调立即返回单独的行，回调必须提供给 execute()方法。要识别单个结果集，您可以提供第二个回调，调用它来获取标记结果集开始的元数据。

Node.js JavaScript 代码

var resultcount = 0;
var res = session
  .sql('CALL my_proc()')
  .execute(
    function (row) {
      console.log(row);
    },
    function (meta) {
      console.log('Begin of result set number ', resultCount++);
    });

结果集的数量在查询执行后并不知道。查询结果可以流式传输到客户端或在客户端进行缓冲。在流式或部分缓冲模式下，客户端无法判断查询是否发出多个结果集。