はじめに

・トランザクション処理をストアドプロシージャにまとめることでソースをすっきりさせたい衝動にかられ、ストアドプロシージャを呼び出してみた。
・いままでクライアント側にSQLをベタ打ちしていてセキュリティーの問題がやばいみたいなので...

開発環境

・サーバー構成
　WindowsServr2012R2
　Apache + SQLServer2012 + PHP
　
・クライアント構成
　JavaScript + jQuery

実装コード

・クライアントからのAPIの呼び出し

classDatabase.js

//=========================================
// Database クラス
// データベースに関する命令をまとめる
//=========================================
const DatabaseAsyncOn = true;
const DatabaseAsyncOff = false;

function Database(accountName, userName, async) {
    this.accountName = "dev"; // accountName;
    this.userName = "dev"; // userName;
    this.async = false; // async;
}
Database.prototype = {
    //=========================================
    // SELECT メソッド（prepareを使用したもの）
    // DBからSQLを使ってデータをGETする
    //=========================================
    getProcedures: function(mappingName, prepare) {
        var returnData = null;
        $.ajax({
            type: "POST",
            url: "../../common/php/getProcedures.php",
            data: {
                "accountName": this.accountName,
                "userName": this.userName,
                "mappingName": mappingName,
                "prepare[]": prepare
            },
            dataType: "json",
            async: this.async,
            // 成功したときの処理
            success: function(data) {
                returnData = data;
                console.log('\n\nmappingName: ' + mappingName);
                console.log('parametter: ' + prepare);
                console.log('data: ' + data);
            },
            // エラー処理
            error: function(data) {
                returnData = false;
                console.log('\n\nmappingName: ' + mappingName);
                console.log('parametter: ' + prepare);
                console.log('data: ' + data);
            }
        });
        return returnData;
    }
};

main.js

    // APIの呼び出し
    // マッピング：200402
    self.midokuMember = self.database.getProcedures(
            'mapping200402',[
                self.companyID,
                eventNo,
                today
            ]
    );

サーバー側
　ストアドプロシージャ呼び出し用のAPI

setProcedures.php

<?php
  $accountName = $_POST["accountName"];
  $userName    = $_POST["userName"];
  $mappingName = $_POST["mappingName"];
  $prepare     = $_POST["prepare"];
  $org_timezone = date_default_timezone_get();
  date_default_timezone_set('Asia/Tokyo');
  $dsn = 'sqlsrv:server=【IPアドレス】;database=' . $accountName;
  $password = '【DB接続パスワード】';
  try {
    $dbh = new PDO($dsn, $userName, $password);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE , PDO::FETCH_ASSOC);
    //////////////////////////////////////////////
    // セット処理
    //////////////////////////////////////////////
    // ステートメント準備
    $stringPrepare = "{CALL ".$mappingName."(";
    foreach ($prepare as $key => $value) {
      # code...
      $stringPrepare = $stringPrepare."?,";
    }
    if (count($prepare) != 0) {
      $stringPrepare = substr($stringPrepare, 0, -1);   // 最後尾の文字を削除する
    }
    $stringPrepare = $stringPrepare.")}";
    error_log($stringPrepare);
    $stmt = $dbh->prepare($stringPrepare);
    for ($i=0; $i < count($prepare); $i++) {
      # code...
      $stmt->bindParam($i+1, $prepare[$i], PDO::PARAM_STR);
    }
    $stmt -> execute();
    echo "\nPDO::errorInfo():\n";
    print_r($dbh->errorInfo());
    $dbh = null;
    date_default_timezone_set($org_timezone);
  }
  catch (PDOException $e) {
    echo 'Error : ',  $e->getMessage(), "\n";
    die('MSSQL Serer Connect Error');
  }
?>

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

mapping200409.sql

USE [dev]
GO
/****** Object:  StoredProcedure [dbo].[mapping200402]    Script Date: 2018/12/26 12:10:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[mapping200402]
  @companyID nvarchar(4),
  @eventNo int,
  @today nvarchar(10)
AS
BEGIN TRY
    BEGIN TRANSACTION        --トランザクションの開始
        BEGIN
            -- マッピング2：未読者の取得
            SELECT MK_smartPhone.companyID,
                   MK_smartPhone.staffID,
                   MK_staff.name
            FROM   MK_smartPhone INNER JOIN MK_staff ON 
                   MK_smartPhone.companyID = MK_staff.companyID AND 
                   MK_smartPhone.staffID = MK_staff.staffID LEFT OUTER JOIN
                   MK_history ON 
                   MK_smartPhone.companyID = MK_history.companyID AND 
                   MK_smartPhone.staffID = dbo.MK_history.staffID
            WHERE  (MK_smartPhone.name <> N'card') AND 
                   ((MK_history.category = 2) AND 
                   (MK_history.flag = 0) AND 
                   (MK_history.companyID = @companyID) AND 
                   (MK_history.externNo = @eventNo) AND 
                   (MK_staff.retireDate > @today OR
                    MK_staff.retireDate IS NULL OR
                    MK_staff.retireDate = '') OR
                   (MK_history.flag IS NULL))
            GROUP BY MK_smartPhone.companyID,
                     MK_smartPhone.staffID, 
                     MK_staff.name, 
                     MK_history.externNo
            ORDER BY MK_smartPhone.staffID          
        END
    COMMIT TRANSACTION       --トランザクションを確定
END TRY


--例外処理
BEGIN CATCH
    ROLLBACK TRANSACTION     --トランザクションを取り消し
    PRINT ERROR_MESSAGE()    --エラー内容を戻す
    PRINT 'ROLLBACK TRANSACTION'
END CATCH

RETURN

まとめ

・SQLを別ファイルに分離してソースの読みやすさ管理のしやすさの向上が図れた。
・言語ごとに得意分野があるのでそれぞれ適材適所で使用していきたい。※違う言語が紛れているとソースが読みづらくなる

　下記の様な使い分けがよいと改めて思った。
①JavaScript・・・画面周り、APIの呼び出し（ビジネスロジックは書かない）
②PHP・・・ビジネスロジックだけを書く
③ストアドプロシージャ・・・トランザクションの処理（ビジネスロジックは書かない）