PDF (US Ltr) - 1.4Mb
PDF (A4) - 1.4Mb
除了会话对象的简化 X DevAPI 语法之外，会话对象还具有一个 sql() 函数，该函数接受任何 SQL 语句作为字符串。
以下示例使用会话在特定节点上调用 SQL 存储过程。
MySQL Shell JavaScript 代码
var mysqlx = require('mysqlx');
// Connect to server using a Session
var mySession = mysqlx.getSession('user:password@localhost');
// Switch to use schema 'test'
mySession.sql("USE test").execute();
// In a Session context the full SQL language can be used
mySession.sql("CREATE PROCEDURE my_add_one_procedure " +
" (INOUT incr_param INT) " +
"BEGIN " +
" SET incr_param = incr_param + 1;" +
"END;").execute();
mySession.sql("SET @my_var = ?;").bind(10).execute();
mySession.sql("CALL my_add_one_procedure(@my_var);").execute();
mySession.sql("DROP PROCEDURE my_add_one_procedure;").execute();
// Use an SQL query to get the result
var myResult = mySession.sql("SELECT @my_var").execute();
// Gets the row and prints the first column
var row = myResult.fetchOne();
print(row[0]);
mySession.close();MySQL Shell Python 代码
from mysqlsh import mysqlx
# Connect to server using a Session
mySession = mysqlx.get_session('user:password@localhost')
# Switch to use schema 'test'
mySession.sql("USE test").execute()
# In a Session context the full SQL language can be used
sql = """CREATE PROCEDURE my_add_one_procedure
(INOUT incr_param INT)
BEGIN
SET incr_param = incr_param + 1;
END
"""
mySession.sql(sql).execute()
mySession.sql("SET @my_var = ?").bind(10).execute()
mySession.sql("CALL my_add_one_procedure(@my_var)").execute()
mySession.sql("DROP PROCEDURE my_add_one_procedure").execute()
# Use an SQL query to get the result
myResult = mySession.sql("SELECT @my_var").execute()
# Gets the row and prints the first column
row = myResult.fetch_one()
print(row[0])
mySession.close()Node.js JavaScript 代码
var mysqlx = require('@mysql/xdevapi');
var session;
// Connect to server using a Low-Level Session
mysqlx
.getSession('user:password@localhost')
.then(function (s) {
session = s;
return session.getSchema('test');
})
.then(function () {
return Promise.all([
// Switch to use schema 'test'
session.sql('USE test').execute(),
// In a Session context the full SQL language can be used
session.sql('CREATE PROCEDURE my_add_one_procedure' +
' (INOUT incr_param INT) ' +
'BEGIN ' +
' SET incr_param = incr_param + 1;' +
'END;').execute(),
session.executeSql('SET @my_var = ?;', 10).execute(),
session.sql('CALL my_add_one_procedure(@my_var);').execute(),
session.sql('DROP PROCEDURE my_add_one_procedure;').execute()
])
})
.then(function() {
// Use an SQL query to get the result
return session.sql('SELECT @my_var').execute(function (row) {
// Print result
console.log(row);
});
});C# 代码
// Connect to server using a Session
var mySession = MySQLX.GetSession("server=localhost;port=33060;user=user;password=password;");
// Switch to use schema "test"
mySession.SQL("USE test").Execute();
// In a Session context the full SQL language can be used
mySession.SQL("CREATE PROCEDURE my_add_one_procedure " +
" (INOUT incr_param INT) " +
"BEGIN " +
" SET incr_param = incr_param + 1;" +
"END;").Execute();
mySession.SQL("SET @my_var = 10;").Execute();
mySession.SQL("CALL my_add_one_procedure(@my_var);").Execute();
mySession.SQL("DROP PROCEDURE my_add_one_procedure;").Execute();
// Use an SQL query to get the result
var myResult = mySession.SQL("SELECT @my_var").Execute();
// Gets the row and prints the first column
var row = myResult.FetchOne();
Console.WriteLine(row[0]);
mySession.Close();Python 代码
# Connector/Python
from mysqlsh import mysqlx
# Connect to server using a Session
mySession = mysqlx.get_session('user:password@localhost')
# Switch to use schema 'test'
mySession.sql("USE test").execute()
# In a Session context the full SQL language can be used
sql = """CREATE PROCEDURE my_add_one_procedure
(INOUT incr_param INT)
BEGIN
SET incr_param = incr_param + 1;
END
"""
mySession.sql(sql).execute()
mySession.sql("SET @my_var = ?").bind(10).execute()
mySession.sql("CALL my_add_one_procedure(@my_var)").execute()
mySession.sql("DROP PROCEDURE my_add_one_procedure").execute()
# Use an SQL query to get the result
myResult = mySession.sql("SELECT @my_var").execute()
# Gets the row and prints the first column
row = myResult.fetch_one()
print(row[0])
mySession.close()Java 代码
import com.mysql.cj.xdevapi.*;
// Connect to server on localhost
Session mySession = new SessionFactory().getSession("mysqlx://127.0.0.1:33060/test?user=user&password=password");
// Switch to use schema 'test'
mySession.sql("USE test").execute();
// In a Session context the full SQL language can be used
mySession.sql("CREATE PROCEDURE my_add_one_procedure " + " (INOUT incr_param INT) " + "BEGIN " + " SET incr_param = incr_param + 1;" + "END")
.execute();
mySession.sql("SET @my_var = ?").bind(10).execute();
mySession.sql("CALL my_add_one_procedure(@my_var)").execute();
mySession.sql("DROP PROCEDURE my_add_one_procedure").execute();
// Use an SQL query to get the result
SqlResult myResult = mySession.sql("SELECT @my_var").execute();
// Gets the row and prints the first column
Row row = myResult.fetchOne();
System.out.println(row.getInt(0));
mySession.close();C++ 代码
#include <mysqlx/xdevapi.h>
// Connect to server on localhost
string url = "mysqlx://127.0.0.1:33060/test?user=user&password=password";
Session mySession(url);
// Switch to use schema 'test'
mySession.sql("USE test").execute();
// In a Session context the full SQL language can be used
mySession.sql("CREATE PROCEDURE my_add_one_procedure "
" (INOUT incr_param INT) "
"BEGIN "
" SET incr_param = incr_param + 1;"
"END;")
.execute();
mySession.sql("SET @my_var = ?;").bind(10).execute();
mySession.sql("CALL my_add_one_procedure(@my_var);").execute();
mySession.sql("DROP PROCEDURE my_add_one_procedure;").execute();
// Use an SQL query to get the result
auto myResult = mySession.sql("SELECT @my_var").execute();
// Gets the row and prints the first column
Row row = myResult.fetchOne();
cout << row[0] << endl;
使用文字/逐字 SQL 时，常见的 API 模式与对表和集合使用 DML 和 CRUD 操作相比大多相同。存在两个差异：设置当前模式和转义名称。