使用JavaScript和Python實現Oracle資料庫的儲存過程？

banq發表於2018-12-21
JavaScriptPythonOracle資料庫儲存過程

本文研究了多語言引擎（MLE）如何在GraalVM的幫助下在Oracle資料庫中使用JavaScript和Python，為資料密集型計算帶來巨大的生態系統。使用GraalVM，我們不僅可以快速將新語言引入Oracle資料庫，而且還可以輕鬆獲得高效能的推測JIT編譯器。它可用於為查詢的關鍵部分生成有效程式碼，例如執行時的資料轉換。
在資料庫方面，SQL已經被證明是過去幾十年中查詢資料的首選語言，實現更復雜的業務邏輯，就會達到SQL的限制，這時儲存過程出臺了，但是人才匱乏，找到JavaScript或Python開發人員要比找到PL / SQL開發人員容易得多。
將新的程式語言與資料庫系統整合包括嵌入一個全新的執行時，具有自己的記憶體管理，執行緒機制等。這顯著增加了資料庫系統的體系結構和程式碼庫的複雜性。具有多語言功能的GraalVM及其對嵌入的支援為此問題提供瞭解決方案。只需要嵌入一個執行包，即可在資料庫系統中實現多種程式語言的高效能實現。

多語言引擎
在Oracle，我們目前正致力於將GraalVM嵌入到Oracle資料庫和MySQL中，我們將這些擴充套件稱為多語言引擎（MLE）。在本文中，我們僅關注MLE for Oracle資料庫，Oracle Database MLE目前是一項實驗性功能。
除了將GraalVM嵌入到Oracle資料庫之外，我們還開發了儘可能方便地使用MLE的工具。例如，我們目前正在開發自動打包整個應用程式並使用單個命令將其部署到Oracle資料庫的工具。
另一個主要工作領域是我們在Oracle資料庫中提供的語言。語言需要擴充套件才能變得有用。例如，我們需要一個可以在資料庫型別和語言型別之間進行轉換的轉換引擎，以及Oracle資料庫的SQL引擎和新語言的SQL API之間的橋樑。

MLE提供了兩種不同的方式來執行由MLE支援的語言編寫的程式碼。首先，儲存過程和使用者​​定義的函式可以用MLE語言編寫。其次，提供了一個名為DBMS_MLE的新PL / SQL包，用於動態指令碼，即在執行時定義匿名指令碼並執行它們。

使用動態MLE臨時執行指令碼
DBMS_MLE可以執行在PL / SQL中以字串形式給出的指令碼。資料透過所謂的繫結變數在兩個方向（輸入和輸出）與指令碼交換。最後，指令碼可以列印將放入資料庫輸出緩衝區的訊息。我們來看一個具體的例子：

DECLARE
  script_source CLOB;
  script_handle DBMS_MLE.SCRIPT_HANDLE_T;
  script_result CLOB;
BEGIN
  script_source := q'~
    var hist = {"b2000": "", "b4000": "", "b6000": ""};
    // mle.sql is an obect that gives access to the SQL engine
    for (var row of mle.sql.execute("SELECT SAL FROM EMP").rows) {
      if (row[0] < 2000)
        hist.b2000 += "█";
      else if (row[0] < 4000)
        hist.b4000 += "█";
      else if (row[0] < 6000)
        hist.b6000 += "█";
    }
    console.log(JSON.stringify(hist));
    // mle.binds is an object that holds all bind variables
    mle.binds.hello = mle.binds.hello.replace("PL/SQL", "Graal JavaScript " + Graal.versionJS);
    ~';
  -- create a new MLE script for the JavaScript (JS) code above
  script_handle := DBMS_MLE.CREATE_SCRIPT('JS', script_source);
  -- assign "Hello from PL/SQL" to a bind variable named "hello"
  DBMS_MLE.BIND_VARIABLE(script_handle, 'hello', 'Hello from PL/SQL');
  -- execute the dynamic MLE script
  DBMS_MLE.EXECUTE_SCRIPT(script_handle);
  -- store the value of "hello" after script execution in "script_result"
  DBMS_MLE.VARIABLE_VALUE(script_handle, 'hello', script_result);
  -- remove the 
  DBMS_MLE.DROP_SCRIPT(script_handle);
  DBMS_OUTPUT.PUT_LINE(script_result);
END;


示例中的匿名PL / SQL塊使用變數script_source來儲存JavaScript程式碼片段。此變數傳遞給函式DBMS_MLE.CREATE_SCRIPT（）以建立新的Dynamic MLE指令碼，然後可以透過函式DBMS_MLE.EXECUTE_SCRIPT（）執行該指令碼。
在我們執行指令碼之前，我們透過DBMS_MLE.BIND_VARIABLE（）定義並設定一個名為hello的繫結變數。可以在周圍的PL / SQL程式或Dynamic MLE指令碼中定義，設定和讀取繫結變數。
該指令碼使用內建的MLE SQL驅動程式（自動作為mle.sql提供）來查詢EMP中所有員工的工資表。出於演示目的，我們為工資建立一個簡單的直方圖，並將其放入輸出緩衝區（console.log（））。在將控制權轉移回PL / SQL之前，指令碼操縱繫結變數hello。然後，PL / SQL塊使用DBMS_MLE.VARIABLE_VALUE（）函式提取繫結變數的值，並將其列印到輸出緩衝區。要執行PL / SQL塊，我們可以將它作為單個語句從任何客戶端傳送到資料庫。例如，可以將整個塊複製到SQL * Plus會話中，並透過輸入斜槓字元來執行。
在執行上面的匿名PL / SQL塊之後（例如，在SQL * Plus中），資料庫輸出緩衝區將具有以下內容（在SQL * Plus中顯示SET SERVEROUTPUT ON或使用DBMS_OUTPUT.GET_LINE（）來檢索）：

{"b2000":"████████","b4000":"█████","b6000":"█"}
Hello from Graal JavaScript 1.0


當然，我們可以輕鬆地使用Python來完成同樣的事情：

DECLARE
  script_source CLOB;
  script_handle DBMS_MLE.SCRIPT_HANDLE_T;
  script_result CLOB;
BEGIN
  script_source := q'~
hist = { 'b2000': '', 'b4000': '', 'b6000': '' }
# mle.sql is an obect that gives access to the SQL engine
for row in mle.sql.execute('SELECT SAL FROM EMP'):
    if row[0] < 2000:
        hist['b2000'] += '█'
    elif row[0] < 4000:
        hist['b4000'] += '█'
    elif row[0] < 6000:
        hist['b6000'] += '█'
print(hist);
import sys
# mle.binds is a dictionary that holds all bind variables
mle.binds['hello'] = mle.binds['hello'].replace('PL/SQL', 'Python ' + sys.version);
    ~';
  -- create a new MLE script for the Python code above
  script_handle := DBMS_MLE.CREATE_SCRIPT('PYTHON', script_source);
  -- assign "Hello from PL/SQL" to a bind variable named "hello"
  DBMS_MLE.BIND_VARIABLE(script_handle, 'hello', 'Hello from PL/SQL');
  -- execute the dynamic MLE script
  DBMS_MLE.EXECUTE_SCRIPT(script_handle);
  -- store the value of "hello" after script execution in "script_result"
  DBMS_MLE.VARIABLE_VALUE(script_handle, 'hello', script_result);
  -- remove the 
  DBMS_MLE.DROP_SCRIPT(script_handle);
  DBMS_OUTPUT.PUT_LINE(script_result);
END;


跨語言的元件共享​​​​​​​
在我們的第一個示例中，我們介紹了MLE SQL驅動程式，並展示瞭如何在JavaScript和Python中使用它。它看起來像是用過的語言實現的模組，但事實並非如此。我們不是為我們新增的每種語言實現從語言的SQL API到Oracle資料庫的SQL引擎的完整橋接，而是由於GraalVM的多語言功能，我們必須只完成一次工作的主要部分。
簡而言之，多語言功能允許在GraalVM上執行的語言訪問物件並呼叫屬於另一種語言的函式。因此，我們將所有語言（如資料轉換和MLE SQL驅動程式）所需的基本元件實現為可以直接從所有其他語言使用的新內部語言。為了實現新語言，GraalVM提供了Truffle框架我們用於此目的。我們在每個MLE語言之上新增了一個特定於語言的瘦層，以隱藏一些內部結構並使它們看起來非常原生。
Truffle框架不僅可以實現可共享元件，還可以充分利用GraalVM的推測JIT編譯器。在資料庫的背景下，後者具有極其重要的意義，因為資料轉換通常是主要的成本因素。

MLE儲存過程
雖然在許多情況下執行以現代語言編寫的指令碼很方便，但它並不適合開發大型和複雜的應用程式。動態MLE需要PL / SQL中的骨架，並且不能直接使用第三方庫。此外，程式碼最好由資料庫管理，類似於資料。為了釋放MLE的全部功能，我們允許以由使用者定義的函式和儲存過程組成的模組的形式持久地在資料庫中儲存和維護使用者程式碼。對於無痛包裝和模組部署，我們計劃提供外部工具，透過單個命令完成所有工作。

儲存過程允許開發人員執行需要在資料庫伺服器程式內執行多個SQL語句的程式碼。這避免了資料庫客戶端（通常是中介軟體）與資料庫之間昂貴的網路往返。今天，Oracle資料庫允許開發人員在PL / SQL或Java中實現儲存過程。使用MLE，開發人員還可以使用JavaScript和Python實現儲存過程。

假設我們想提高員工的工資，但禁止非經理人的薪水超過10,000美元。我們可以從一個JavaScript函式開始，該函式更新員工的薪水並返回新薪水：

const sql = _dbRequire('@oracle/sql');
function updateGetSal(empno, raise) {
  if (sql.execute('UPDATE emp SET sal = sal + :1 WHERE empno = :2',
      [raise, empno]).rowsAffected != 0) {
    const row = sql.execute('SELECT sal FROM emp WHERE empno = :1',
        [empno]).firstRow();
    return row[0];
  }
  return false;
}

請注意，為了提高安全性和效能，我們在SQL語句中使用繫結變數。在這種特殊情況下，我們透過給出一個值陣列來設定繫結變數的值。這意味著陣列[raise，empno]中的值的位置確定它替換的繫結變數（即，第一個繫結變數將設定為raise的值，第二個繫結變數將設定為empno的值） 。或者，可以按名稱設定繫結變數。
接下來，我們可以定義一個函式來檢查員工是否是經理：

function isManager(empno) {
  const row = sql.execute('SELECT count(*) FROM emp WHERE mgr = :1',
      [empno]).firstRow();
  return row[0] > 0;
}


有了這兩個輔助函式，我們現在可以實現我們的業務邏輯：

module.exports.salraise = function(empno, raise) {
const newsal = updateGetSal(empno, raise);
if (newsal && newsal > 10000 && !isManager(empno)) {
sql.execute('ROLLBACK');
} else {
sql.execute('COMMIT');
}
};


對module.exports的賦值用於將函式salraise（）匯出到資料庫。將所有內容放在名為load_salraise.js的檔案中，我們可以新增其他程式碼來執行部署到資料庫中：

const async = require('async');
const oracledb = require('oracledb');

// the name of the module being created in the database
const moduleName = 'raise.js';

// database user
const dbUser = 'scott';

// database user password
const dbPwd = 'tiger';

// database connection string
const dbInstance = 'hr.us.example.com/hr.rdbms.dev.us.oracle.com';

oracledb.autoCommit = true;

const doConnect = function(cb) {
  oracledb.getConnection(
      {
        user: dbUser,
        password: dbPwd,
        connectString: dbInstance,
      },
      cb);
};

const doCleanup = function(conn) {
  conn.close(function(err) {
    if (err) {
      console.error(err.message);
    }
  });
};

const source = `
const sql = _dbRequire('@oracle/sql');
function updateGetSal(empno, raise) {
  if (sql.execute('UPDATE emp SET sal = sal + :1 WHERE empno = :2',
      [raise, empno]).rowsAffected != 0) {
    const row = sql.execute('SELECT sal FROM emp WHERE empno = :1',
        [empno]).firstRow();
    return row[0];
  }
  return false;
}
function isManager(empno) {
  const row = sql.execute('SELECT count(*) FROM emp WHERE mgr = :1',
      [empno]).firstRow();
  return row[0] > 0;
}
module.exports.salraise = function(empno, raise) {
  const newsal = updateGetSal(empno, raise);
  if (newsal && newsal > 10000 && !isManager(empno)) {
    sql.execute('ROLLBACK');
  } else {
    sql.execute('COMMIT');
  }
};
`;

const createProcedure = function(conn, cb) {
  conn.execute(
      'CREATE OR REPLACE JAVASCRIPT SOURCE NAMED "' + moduleName + '" AS ' +
      source,
      function(err, result) {
        if (err) {
          return cb(err, conn);
        }
        conn.execute(
            'CREATE OR REPLACE PROCEDURE raiseSal (empno NUMBER, raise ' +
            'NUMBER) AS LANGUAGE JAVASCRIPT NAME ' +
            '\'raise.js.salraise(empno number, raise number)\';',
            function(err, result) {
              if (err) {
                return cb(err, conn);
              }
              return cb(null, conn);
            });
      }
  );
};

async.waterfall(
    [
      doConnect,
      createProcedure,
      doCleanup,
    ],
    function(err, conn) {
      if (err) {
        console.error('In waterfall error cb: ==>', err, '<==');
      }
      if (conn) {
        doCleanup(conn);
      }
    });


我們現在可以執行部署模組程式碼的指令碼，並透過Node.js 將函式salraise（）註冊為儲存過程：

$ npm install oracledb async
$ node load_salraise.js


可以像呼叫任何其他過程一樣呼叫新建立的JavaScript儲存過程。例如，從SQL * Plus：

$ sqlplus scott/tiger

SQL> SELECT sal FROM emp WHERE empno = 7369;

       SAL
----------
       800

SQL> CALL salraise(7369, 200);

Call completed.

SQL> SELECT sal FROM emp WHERE empno = 7369;

       SAL
----------
      1000


 

相關文章