ストアドプロシージャ - PolarDB - Alibaba Cloud ドキュメントセンター

このトピックでは、ストアドプロシージャとその使用方法について説明します。

概要

ストアドプロシージャは、SQL文のセットです。ストアドプロシージャの名前とパラメーターを指定して、ストアドプロシージャを実行できます。

制御ポリシー機能の動作

ストアドプロシージャはメタセンターに永続的に格納され、必要に応じて計算ノードにロードできます。 SQL文は実行のためにSQLエンジンに送信され、実行結果が返されます。ストアドプロシージャの制御フローおよび他のロジックは、プロシージャ言語 (PL) エンジンで実行されます。

実行前に、ストアドプロシージャはランタイムプロシージャマネージャに登録され、ストアドプロシージャによって消費されるメモリは制限されます。

使用上の注意

この機能は、PolarDB-X V5.4.15以降でサポートされています。
条件処理はサポートされていません。

固定形式の例外ハンドラーは1つだけサポートされます。ハンドラーは、declare not found for cursor begin {iterate label | leave label | set statement}; endとして定義できます。これは、カーソルを使用してデータをフェッチしたときに使用できるデータがなくなった場合、ループが再開または終了するか、特定の変数が設定されていることを示します。次のステートメントは、このハンドラーの使用方法を示しています。

CREATE PROCEDURE pro_test()
  BEGIN
  DECLARE a CHAR(16);
  DECLARE b, c int;
  DECLARE cur1 CURSOR FOR SELECTデータ、id FROM t1 idによる順序;
  DECLARE cur2 CURSOR FOR SELECT id FROM t2 idによる順序;
  DECLARE CONTINUE HANDLER FOR NOT FOUND LEAVE read_loop; end;

  オープンcur1;
  OPEN cur2;

  read_loop: ループ
    FETCH cur1 INTO a、b;
    フェッチcur2にc;
    IF b < c THEN
    t3値 (b、a) に挿入します。
    ELSE
    t3値 (c、a) に挿入します。
    END IF;
  END LOOP;

  近いcur1;
  近いcur2;
  エンド; |

ストアドプロシージャの権限管理はサポートされていません。

構文

ストアドプロシージャ

の作成
    [DEFINER = { user | CURRENT_USER}
PROCEDURE sp_name ([proc_parameter[,...]])
    [特性...] routine_body

proc_parameter:
    [IN | OUT | INOUT ] param_nameタイプ

特性:
    コメント '文字列'
  | 言語SQL
  | [NOT] 決定的
  | {SQLを含む | SQLなし | SQLデータを読む | SQLデータの修正}
  | SQLセキュリティ {DEFINER | INVOKER}

routione_body:
有効なSQLルーチンステートメント

ストアドプロシージャの呼び出し

Call PROCEDURE_NAME(params list);

ストアドプロシージャの削除

DROP procedure [IF EXISTS] PROCEDURE_NAME;

すべての定義済みストアドプロシージャを表示

SELECT * information_schema.Routines WHERE ROUTINE_NAME

specificプロシージャを表示PROCEDa ';

SHOW PROCEDURE STATUS [LIKE 'pattern '| WHERE expr]

SHOW CREATE PROCEDUREプロシージャ名;

SELECT * FROM information_schema。 ルーチンROUTINE_NAME ='procedure name';

進行中のストアドプロシージャを表示

show processlist where info like 'call % ';

サンプル結果:

+ ------------------ ----------------- + ------------------------------------------- ---------------------------------------------- + ------------------ +
| Id | ユーザー | ホスト | db | コマンド | 時間 | 状態 | 情報 | TraceId |
------ -------------- ----------------- --------------------------------------------- ---------------------------------------------- + ------------------ +
| 710 | test | 127.0.0.1:53951 | test | クエリ | 79 | | CALL test.test_procedure: executing pl logic | 14c93b7c7bf00000 |
------ -------------- ----------------- ----------------------------------------------- ---------------------------------------------- +

説明 SHOW PROCESSLISTステートメントによって返される結果で、接続のInfo列の値がcall schema.procedure_nameで始まる場合、接続スレッドはストアドプロシージャを実行しています。ストアドプロシージャ内のSQL文が実行中の場合は、その文が表示されます。それ以外の場合は、execute pl logicが表示されます。

進行中のストアドプロシージャのキャンセル

kill {query | connection} connection_id;

ストアドプロシージャのキャッシュを管理する

プロシージャが存在するかどうかを示すストアドプロシージャのすべてのメタデータは、キャッシュに格納されます。ストアドプロシージャの特定のコンテンツは、必要なときにのみキャッシュにロードされます。

説明以下の例では、SIZE列の単位はバイトです。

キャッシュの表示

show procedure cache;

サンプル結果:

+ -------------------- + ------------------------- + ---------- -------- +
| ID | スキーマ | プロシージャ | サイズ |
+ -------------------- + ------------------------- + ----------- + --------
| 10.215.21.156:9090 | テスト | bug6129 | 46 |
| 10.215.21.156:9090 | drds_polarx1_qatest_app | pro_test | 110 |
+ -------------------- + ------------------------- + ----------- + ---------- +

キャッシュサイズの設定

resize procedure cache num;

例：

サイズ変更プロシージャキャッシュ150;
クエリOK、影響を受ける0行 (0.10秒)

どのようにプロシージャキャッシュ;
+ -------------------- + ------------------------- + ----------- + --------
| ID | スキーマ | プロシージャ | サイズ |
+ -------------------- + ------------------------- + ----------- + --------
| 10.215.21.156:9090 | テスト | bug6129 | 46 |
| 10.215.21.156:9090 | drds_polarx1_qatest_app | pro_test | 0 |
+ -------------------- + ------------------------- + ----------- + --------
セットの2列 (0.01秒)

## 全体のサイズが150バイトを超えないように、pro_testによって消費されたメモリが解放されます。

Clear the cache

clear procedure cache;

例：

クリア手続きキャッシュ。クエリOK、影響を受ける0行 (0.08秒)

手続きキャッシュを表示する。+ -------------------- + ------------------------- + ----------- + --------
| ID | スキーマ | プロシージャ | サイズ |
+ -------------------- + ------------------------- + ----------- + --------
| 30.225.XX.XX:9090 | test | bug6129 | 0 |
| 30.225.XX.XX:9090 | drds_polarx1_qatest_app | pro_test | 0 |
+ -------------------- + ------------------------- + ----------- + --------
セットの2列 (0.00秒)

## ストアドプロシージャによって消費されたすべてのメモリが解放されました。

ストアドプロシージャ

リロードプロシージャ;

ストアドプロシージャのメモリ使用量の管理

メモリは主に、ストアドプロシージャの実行中にキャッシュされたカーソルによって占有されます。したがって、PolarDB-Xは、単一のカーソルとストアドプロシージャ全体が実行中に占有できる最大メモリを設定します。このような制限は、パラメーターPL_CURSOR_MEMORY_LIMITとPL_MEMORY_LIMITを使用して設定されます。

PL_CURSOR_MEMORY_LIMITは、各カーソルが占有できる最大メモリを設定します。制限を超えると、キャッシュされたデータはハードディスクに保存されます。 PL_MEMORY_LIMITは、各ストアドプロシージャが占有できる最大メモリを設定します。