文件首頁
X DevAPI 使用者指南
下載本手冊
PDF (US Ltr) - 1.4Mb
PDF (A4) - 1.4Mb
X DevAPI 使用者指南  /  使用結果集  /  使用 SQL 結果集

9.5 使用 SQL 結果集

當您使用 sql() 方法在工作階段上執行 SQL 操作時，會傳回 SqlResult。 迭代 SqlResult 的方式與使用 CRUD 操作的結果相同。 以下範例假設 users 表格存在。

MySQL Shell 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 Shell 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 的兩種型別之間沒有區別。SqlResult 執行個體匯出用於存取資料、擷取最後插入的 ID 或受影響的列數的方法。

使用 hasData() 方法來瞭解 SqLResult 是資料集還是結果。當程式碼在不瞭解 SqlResult 來源的情況下編寫時，此方法非常有用。在編寫通用應用程式函式以列印查詢結果或處理儲存程序結果時，可能會發生這種情況。如果 hasData() 傳回 true，則 SqlResult 來自 SELECT 或類似的命令，可以傳回資料列。

傳回值 true 並不表示資料集是否包含任何資料列。例如，如果 fetchOne() 傳回 NULL 或 fetchAll() 傳回空清單，則資料集可能為空。而且如果傳回多個結果集，則任何結果集也可能為空。以下範例假設程序 my_proc 存在。

MySQL Shell 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 Shell 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;
}

當 hasData() 指出 SqlResult 不是資料集時，呼叫 fetchOne() 或 fetchAll() 是錯誤的。

MySQL Shell 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 Shell 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 Shell 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 Shell 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++);
    });

結果集的數量在查詢執行後不會立即知道。 查詢結果可以串流到用戶端或在用戶端緩衝。 在串流或部分緩衝模式中，用戶端無法判斷查詢是否發出多個結果集。


上一頁   首頁   上一層   下一頁
下載本手冊
PDF (US Ltr) - 1.4Mb
PDF (A4) - 1.4Mb