PL/SQLとT-SQL -01 ユーザ定義関数

PL/SQLとT-SQL
どちらも，ストアド用の言語を持っていますが，その能力には相当の違いがあります。T-SQLの言語仕様に比べると PL/SQLはAdaを元にしているだけに格段に複雑な処理を記述できます。ですから，SQL Serverでの開発においては，開発全体のうち，T-SQLの責任範囲をどこまでにするかは，慎重に検討する必要があります。PL/SQLでパッケージを書くのになれている場合，T-SQLでどうやって実装しようか，一度は途方に暮れると思います。でも，注意深く設計すれば，何とかなります。

ユーザ定義関数の違い
Oracleでは純度レベルの設定により，DB参照，DB変更を伴う，もしくは伴わない関数を定義できます。しかしT-SQLの関数はDBの変更を許しません。ですので，DB変更を行う処理はプロシージャとして定義する必要があり，関数の戻り値は，出力パラメータとして戻す必要があります。

以下のOracle 関数は，顧客表のCUSTOMER_SUB_IDを取得して+1し，それを関数の値として戻すと共に，顧客表のCUSTOMER_SUB_ID列を+1した値でUPDATEするものです。Oracleの場合，この関数をパッケージ等で使用することができます。（SELECT文で使うことはできません。)
<style type="text/css"> </style>

0001 CREATE OR REPLACE FUNCTION GET_CUSTOMER_SUB_ID(P_CUSTOMER_ID NUMBER) RETURN NUMBER IS
0002     RESULT NUMBER := NULL;
0003     CURSOR CUR_MAIN IS
0004         SELECT C.CUSTOMER_SUB_ID FROM CUSTOMERS C WHERE C.CUSTOMER_ID = P_CUSTOMER_ID;
0005     REC_MAIN CUR_MAIN%ROWTYPE;
0006 BEGIN
0007     REC_MAIN := NULL;
0008     FOR C IN CUR_MAIN LOOP
0009         REC_MAIN                 := C;
0010         REC_MAIN.CUSTOMER_SUB_ID := NVL(REC_MAIN.CUSTOMER_SUB_ID, 0) + 1;
0011         EXIT;
0012     END LOOP;
0013     IF REC_MAIN.CUSTOMER_SUB_ID IS NOT NULL THEN
0014         RESULT := REC_MAIN.CUSTOMER_SUB_ID;
0015         UPDATE CUSTOMERS
0016            SET CUSTOMER_SUB_ID = REC_MAIN.CUSTOMER_SUB_ID
0017          WHERE CUSTOMER_ID = P_CUSTOMER_ID;
0018     END IF;
0019 
0020     RETURN(RESULT);
0021 END GET_CUSTOMER_SUB_ID;

これと，等価な処理をT-SQLで記述しようとして，以下のようにしたとします。

<style type="text/css"> </style>

0001 IF OBJECT_ID('uspGetCustomerSubID') IS NOT NULL
0002 BEGIN
0003     PRINT 'Dropping function'
0004     DROP FUNCTION uspGetCustomerSubID;
0005     IF @@ERROR = 0
0006         PRINT 'Function dropped'
0007 END
0008 GO
0009 
0010 CREATE FUNCTION uspGetCustomerSubID 
0011 /***********************************************************
0012 * Function description:uspGetCustomerSubID
0013 * Date:   2010/05/31  
0014 * Author: 
0015 *
0016 * Changes
0017 * Date        Modified By            Comments
0018 ************************************************************
0019 *
0020 ************************************************************/
0021 (
0022     @CustomerID INT
0023 )
0024 RETURNS INT
0025 
0026 BEGIN
0027     DECLARE @v_ret INT = NULL;
0028 
0029     SELECT @v_ret = c.CustomerSubID
0030     FROM   Customers c
0031     WHERE  c.CustomerID = @CustomerID;
0032 
0033     IF @@ROWCOUNT <> 0
0034     BEGIN
0035         set @v_ret = ISNULL(@v_ret, 0) + 1;
0036         UPDATE Customers
0037         SET    CustomerSubID = @v_ret
0038         WHERE  CustomerID = @CustomerID;
0039     END
0040 
0041     RETURN @v_ret
0042 END

この関数をコンパイルしようとすると，以下のようなエラーメッセージが返ってきます。
メッセージ 443、レベル 16、状態 15、プロシージャ uspGetCustomerSubID、行 28
副作用のある演算子 'UPDATE' を関数内で使用することはできません。
なので，以下のようなプロシージャに書き換える必要があります。

<style type="text/css"> </style>

0001 IF OBJECT_ID('uspGetCustomerSubID') IS NOT NULL
0002 BEGIN
0003     PRINT 'Dropping procedure'
0004     DROP PROCEDURE uspGetCustomerSubID;
0005     IF @@ERROR = 0
0006         PRINT 'Procedure dropped'
0007 END
0008 GO
0009 
0010 CREATE PROCEDURE uspGetCustomerSubID
0011 /***********************************************************
0012 * Procedure description:
0013 * Date:   2010/05/31 
0014 * Author:
0015 *
0016 * Changes
0017 * Date        Modified By            Comments
0018 ************************************************************
0019 *
0020 ************************************************************/
0021 (@CustomerID INT, @CustomerSubID INT OUTPUT)
0022 AS
0023     SET @CustomerSubID = NULL;
0024 
0025     SELECT @CustomerSubID = c.CustomerSubID
0026     FROM   Customers c
0027     WHERE  c.CustomerID = @CustomerID;
0028 
0029     IF @@ROWCOUNT <> 0
0030     BEGIN
0031         SET @CustomerSubID = ISNULL(@CustomerSubID, 0) + 1;
0032         UPDATE Customers
0033         SET    CustomerSubID = @CustomerSubID
0034         WHERE  CustomerID = @CustomerID;
0035     END
0036 
0037     RETURN 0
0038 GO

Oracle Vs SQL Server どっちが良いかより，どう違うか

OracleとSQL Server（というよりはMS）は互いに「自社のDBの方が向こうより優れている」というキャンペーンをやり合っていたりします。
でも，現場の開発サイドから見れば，どっちがいいかという話より，どう違うのか，それぞれを使った場合の開発のポイント，注意点の方が気になるでしょう。所詮決めるのは現場のどのメンバーでもないでしょうから。

というわけでストアド開発，及びSQL作成時におけるOracleとSQL Serverの違いと，対処方法のヒントをまとめてみます。