SQL DDL:ストアド定義

ストアドプロシージャ定義

CREATE OR REPLACE PROCEDURE MyProcedure
(
 prm IN NUMBER,
 ret OUT NUMBER
) AS

BEGIN

 ret := prm * 100;
 引数の変数はそのまま使用可能

EXCEPTION
 WHEN OTHERS THEN
  ret : =0;
END;

CREATE PROCEDURE ストアド名 引数 型情報 AS
  SELECT 列名 FROM 表名 WHERE 列名 = 引数

ストアドファンクション定義

CREATE OR REPLACE FUNCTION MyFunction
(
 PRM IN VARCHAR1
)
RETURN VARCHAR2 AS

 RET VARCHAR2(4);

BEGIN
 
 RET := PRM;
 
 RETURN RET;
 
EXCEPTION
 WHEN OTHERS THEN
  RETURN NULL;

END MyFunction;

ユーザー定義関数の作成
CREATE OR REPLACE FUNCTION 関数名(変数名 型)
RETURNS
 型
AS $$
DECLARE
 変数名 型;
BEGIN
 処理
 RETURN 戻り値;
END
$$ LANGUAGE plpgsql;


CREATE FUNCTION test() 作成
CREATE OR REPLACE FUNCTION test() 上書き
CREATE OR REPLACE FUNCTION test(prm1 INT, prm2 TEXT) 引数有り
RETURNS
 INT 戻り値有り
 VOID 戻り値無し
AS $$
DECLARE val INT; 省略可
BEGIN
 処理
 RETURN val; 戻り値無しの場合は省略可
END
$$ LANGUAGE plpgsql;

ユーザー定義関数の削除
(接続後)
# drop function PL/pgSQL関数名();
# drop function PL/pgSQL関数名(型);
引数有/無の関数は別々に削除する

# drop function if exists PL/pgSQL関数名();
条件付き削除

パッケージ

・ストアドプロシージャ
・ストアドファンクション
を分類する為の名称
Javaのパッケージそのもの
.NETで言う名前空間

【定義】

仕様部
パッケージに含まれるファンクション(プロシージャ)の名前と引数、戻り値のみを定義

CREATE [OR REPLACE] PACKAGE (パッケージ名) AS

 FUNCTION ファンクション名(引数1 IN 型1, 引数2 IN 型2) RETURN 戻り値型;

 PROCEDURE プロシージャ名(引数1 IN 型1, 引数2 IN 型2);

END;

BODY部
実ファンクション(プロシージャ)、グローバル変数

CREATE [OR REPLACE] PACKAGE BODY パッケージ名 AS

 グローバル変数
 変数名 型;

 FUNCTION ファンクション名(引数1 IN 型1, 引数2 IN 型2) RETURN 戻り値型
  IS
   ~
  END ファンクション名;

 PROCEDURE プロシージャ名(引数1 IN 型1, 引数2 IN 型2)
  IS
   ~
  END プロシージャ名;

END パッケージ名;

【削除】
DROP PACKAGE <パッケージ名>;
DROP PACKAGE BODY <パッケージ名>;

ストアドプログラムの呼び出し

SQL> execute ストアド名('~', '~');
SQL> exe ストアド名('~', '~');
SQL> execute パッケージ.ストアド名('~', '~');

ユーザー定義関数の実行
(接続後)
# select PL/pgSQL関数名();
# select PL/pgSQL関数名(引数);

EXECUTE ストアド名
  @Prm1 = @SyoriYMD1,
  @Prm2 = 1000,
  @Prm3 = 'TEST'