SQL Server 2005 / 関数

関数

関数には、組み込み関数とユーザー定義関数の２種類ある。どんな組み込み関数があるかぐらい覚えておこう・・・

１．目次

１．目次
 ２．関数
　２．１　決定的組み込み関数
　２．２　環境設定関数
　２．３　カーソル関数
　２．４　メタデータ関数
　２．５　セキュリティ関数
　２．６　システム統計関数
　２．７　その他の組み込み関数
 ３．ユーザー定義関数
　３．１　スカラ値ユーザー定義関数
　３．２　インラインテーブル値関数
　３．３　複数ステートメントのテーブル値関数例
 ４．ユーザ定義関数構文
　４．１　スカラ関数構文
　４．２　インラインテーブル値関数構文
　４．３　複数ステートメントのテーブル値関数構文

２ ．組み込み関数

組み込み関数には次のものがある。

　決定的組み込み関数
　環境設定関数
　カーソル関数
　メタデータ関数
　セキュリティ関数
　システム統計関数
　その他の組み込み関数
注意
組み込みの非決定的関数は、ユーザー定義関数の本文では使用できません。例えば、ユーザー定義関数の中に、GETDATE()は使えません。SQL 2005ではOK！

２ ．１　決定的組み込み関数

　

関数説明

ISNULL check_expression と同じデータ型←IS NULL ( check_expression , replacement_value )

ISNUMERIC ISNUMERIC ( expression )
有効な数値・・・１、でなければ０

　

日付関数説明

YEAR int ←YEAR ( date )

MONTH int ←MONTH( date )

DAY int ←DAY ( date )

DATEADD datetime型←DATEADD ( datepart , number, date )

DATEDIFF datetime型←DATEDIFF ( datepart , startdate , enddate )

datepart

新しい値を返す日付の要素を指定するパラメータです。次の表に、日付の要素 (datepart) と Microsoft® SQL Server™ で認識される省略形を示します。

datepart 省略形

Year yy, yyyy

quarter qq, q

Month mm, m

dayofyear dy, y

Day dd, d

Week wk, ww

Hour hh

minute mi, n

second ss, s

millisecond ms

　

算術関数説明

ABS numeric_expression←ABS( numeric_expression )

FLOOR numeric_expression←FLOOR( numeric_expression )

CEILING numeric_expression←CEILING( numeric_expression )

ROUND numeric_expression←ROUND ( numeric_expression , 有効桁数 [ , function ]
function
function は、tinyint、smallint、または int 型。function を指定しない場合、またはその値が 0 (既定値) の場合、numeric_expression は丸められます。0 以外の値を指定した場合は、numeric_expression は切り捨てられます。

SQUARE float 型←SQUARE ( float 型)

SQRT float 型←SQRT( float 型)

POWER numeric_expression←POWER ( numeric_expression , y )

EXP float 型←EXP( float 型)

LOG float 型←LOG ( float 型)

LOG10 float 型←LOG10 ( float 型)

SIGN numeric_expression←SIGN ( numeric_expression )
零、負、正で、0, -1, 1 が返る。

　

三角関数説明

SIN float 型←SIN(float 型ラジアン)

COS float 型←COS(float 型ラジアン)

TAN float 型←TAN(float 型ラジアン)

ASIN float 型ラジアン←ASIN(float 型)

ACOS float 型ラジアン←ACOS(float 型)

ATAN float 型ラジアン←ATAN(float 型)

ATN2 float 型ラジアン←ATN2 ( float 型 , float 型 )

COT float 型ラジアン←COT(float 型)

DEGREES float 型角度←DEGREES(float 型ラジアン)

RADIANS float 型ラジアン←RADIANS(float 型角度)

PI float 型←PI()、円周率

　

その他関数説明

PARSENAME オブジェクト名の指定された各部を返す。
nchar←PARSENAME ( 'object_name' , object_piece )

DATALENGTH int ←DATALENGTH ( expression )

COALESCE expression ←COALESCE ( expression [ ,...n ] )
引数の中から、最初の NULL でない式を返す。
すべての引数が NULL の場合、NULL を返す。

NULLIF NULLIF ( expression , expression )
2 つの式が等価な場合に NULL 値を返す。

object_name

値説明

1 オブジェクト名

2 所有者名

3 データベース名

4 サーバー名

２ ．２　環境設定関数

環境設定関数はすべて非決定的です。

環境設定関数説明

@@DATEFIRST 既定値は 7 の日曜日。
例：1 は月曜、3 は水曜、... 7 は日曜。

@@OPTIONS SET オプションに関する情報を返す。

@@DBTS 現在のデータベースで最後に使用されたタイムスタンプを返す。

@@REMSERVER ログインレコードに登録されているリモート SQLサーバーの名前を返す。

@@LANGID
現在使用している言語のローカル言語識別子 (ID) を返す。

@@SERVERNAME SQLサーバーを実行しているローカルサーバーの名前を返す。

@@LANGUAGE 現在使用されている言語の名前を返す。

@@SERVICENAME 実行しているSQLサーバーに対応するレジストリキーの名前を返す。デフォルトでは、"MSSQLServer"。

@@LOCK_TIMEOUT 現在のセッションで設定されている現在のロックタイムアウトをミリ秒単位で返す。
接続の開始時は -1。

@@SPID 現在のユーザープロセスのサーバープロセス識別子 (ID) を返す。

@@MAX_CONNECTIONS 許可されている同時ユーザー接続数の最大値。接続可能数を少なくするには、sp_configure を使用する。

@@TEXTSIZE SELECT ステートメントから返される text または image データの最大バイト数

@@MAX_PRECISION
decimal 型、numeric 型の規定有効桁数。

@@VERSION 現在のSQLサーバーの日付、バージョン、プロセッサタイプを返す。

@@NESTLEVEL
現在のストアドプロシージャが実行中のトランザクションのネストレベル (初期値は 0)。

２ ．３　カーソル関数

カーソル関数はすべて非決定的。

値説明

@@CURSOR_ROWS

-m    行がカーソルに非同期的に登録されている場合、キーセットにある行数。

-1    カーソルが動的な場合。

0   オープンされているカーソルがない場合。

n    カーソルに行がすべて完全に登録されている場合、行数の合計。

CURSOR_STATUS

1    オープンしている。

0    結果セットは空。

-1   クローズしている。

-2   適用なし。

-3   カーソルは存在しない。

@@FETCH_STATUS

0   正常に実行された。

-1 失敗したか、行が結果セットに収まらない。

-2 取り出した行が無い。

例
USE pubs

SELECT '@@CURSOR_ROWS=' + CONVERT(varchar, @@CURSOR_ROWS, 5)

DECLARE authors_cursor CURSOR FOR
SELECT au_lname FROM authors

OPEN authors_cursor
FETCH NEXT FROM authors_cursor
SELECT '@@FETCH_STATUS=' + CONVERT(varchar, @@FETCH_STATUS, 5)
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM authors_cursor
SELECT '@@FETCH_STATUS=' + CONVERT(varchar, @@FETCH_STATUS, 5)
SELECT '@@CURSOR_ROWS=' + CONVERT(varchar, @@CURSOR_ROWS, 5)
END
CLOSE authors_cursor

DEALLOCATE authors_cursor

２ ．４　メタデータ関数

メタデータ関数はすべて非決定的です。

COL_LENGTH

fn_listextendedproperty

COL_NAME

FULLTEXTCATALOGPROPERTY

COLUMNPROPERTY

FULLTEXTSERVICEPROPERTY

DATABASEPROPERTY

INDEX_COL

DATABASEPROPERTYEX

INDEXKEY_PROPERTY

DB_ID INDEXPROPERTY

DB_NAME

OBJECT_ID

FILE_ID

OBJECT_NAME

FILE_NAME

OBJECTPROPERTY

FILEGROUP_ID

@@PROCID

FILEGROUP_NAME

SQL_VARIANT_PROPERTY

FILEGROUPPROPERTY

TYPEPROPERTY

FILEPROPERTY
　

２ ．５　セキュリティ関数

セキュリティ関数は非決定的です。

fn_trace_geteventinfo

IS_SRVROLEMEMBER

fn_trace_getfilterinfo

SUSER_SID

fn_trace_getinfo

SUSER_SNAME

fn_trace_gettable

USER_ID

HAS_DBACCESS

USER

IS_MEMBER
　

２ ．６　システム統計関数

システム統計関数はすべて非決定的です。

@@CONNECTIONS

@@PACK_RECEIVED

@@CPU_BUSY

@@PACK_SENT

fn_virtualfilestats

@@TIMETICKS

@@IDLE

@@TOTAL_ERRORS

@@IO_BUSY

@@TOTAL_READ

@@PACKET_ERRORS

@@TOTAL_WRITE

２ ．７　その他の組み込み関数

その他のカテゴリの組み込み関数は、常に非決定的です。

@@ERROR

FORMATMESSAGE

NEWID

@@IDENTITY

GETANSINULL

PATINDEX

@@ROWCOUNT

GETDATE

PERMISSIONS

@@TRANCOUNT

GetUTCDate

SESSION_USER

APP_NAME

HOST_ID

STATS_DATE

CHARINDEX

HOST_NAME

SYSTEM_USER

CURRENT_TIMESTAMP

IDENT_INCR

TEXTPTR

CURRENT_USER

IDENT_SEED

TEXTVALID

DATENAME

IDENTITY

USER_NAME
　

３ ．ユーザー定義関数

ユーザー定義関数は、0 個以上の入力パラメータを受け取り、1 つの値を返します。int 値、char 値または decimal 値など、1 つのスカラデータ値を返すユーザー定義関数もあります。

例
CREATE FUNCTION CubicVolume -- Input dimensions in centimeters. (@CubeLength decimal(4,1), @CubeWidth decimal(4,1), @CubeHeight decimal(4,1) ) RETURNS decimal(12,3) -- Cubic Centimeters. AS BEGIN RETURN ( @CubeLength * @CubeWidth * @CubeHeight ) END
この関数は、次に示すように、テーブルの計算列など、整数型の式が使用できる任意の場所で実行できます。
CREATE TABLE Bricks
   (
    BrickPartNmbr   int PRIMARY KEY,
   
次は、table を返す Northwind データベースの関数を作成するステートメントの例です。
CREATE FUNCTION LargeOrderShippers ( @FreightParm money )
RETURNS @OrderShipperTab TABLE
   (
    ShipperID     int,
    ShipperName   nvarchar(80),
    OrderID       int,
    ShippedDate   datetime,
    Freight       money
   )
AS
BEGIN
   INSERT @OrderShipperTab
        SELECT S.ShipperID, S.CompanyName,
               O.OrderID, O.ShippedDate, O.Freight
        FROM Shippers AS S
             INNER JOIN Orders AS O ON (S.ShipperID = O.ShipVia)
        WHERE O.Freight > @FreightParm
   RETURN
END
この関数のローカルの戻り変数の名前は @OrderShipperTab です。この関数のステートメントは、変数 @OrderShipperTab に行を挿入することによって、関数から返される table の結果を構築します。外部のステートメントは、関数を呼び出して、関数から返される table を参照します。
SELECT *
FROM LargeOrderShippers( $500 )
 BrickColor      nchar(20),
    BrickHeight     decimal(4,1),
    BrickLength     decimal(4,1),
    BrickWidth      decimal(4,1),
    BrickVolume AS
              (
               dbo.CubicVolume(BrickHeight,
                         BrickLength, BrickWidth)
              )
   )
　
SQL Server 2000 は、table データ型を返すユーザー定義関数もサポートしています。

関数は、内部 table 変数を宣言し、行をその変数に挿入し、その変数を戻り値として返すことができます。

　

インライン関数と呼ばれるユーザー定義関数のクラスは、SELECT ステートメントの結果セットを table 型の変数として返します。

３ ．１　スカラ値ユーザー定義関数

この例では、ユーザー定義関数 ISOweek が、日付引数を受け取り、ISO 週番号を計算します。この関数が正しい計算を行うためには、関数を呼び出す前に SET DATEFIRST 1 を起動する必要があります。
CREATE FUNCTION ISOweek  (@DATE datetime)
RETURNS int
AS
BEGIN
   DECLARE @ISOweek int
   SET @ISOweek= DATEPART(wk,@DATE)+1
      -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
--Special cases: Jan 1-3 may belong to the previous year
   IF (@ISOweek=0) 
      SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 
         AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special case: Dec 29-31 may belong to the next year
   IF ((DATEPART(mm,@DATE)=12) AND 
      ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
      SET @ISOweek=1
   RETURN(@ISOweek)
END
以下に関数呼び出しを示します。DATEFIRST が 1 に設定されていることに注意してください。
SET DATEFIRST 1
SELECT master.dbo.ISOweek('12/26/1999') AS 'ISO Week'
以下に結果セットを示します。
ISO Week
----------------
51

３ ．２　インラインテーブル値関数

USE pubs GO CREATE FUNCTION SalesByStore (@storeid varchar(30)) RETURNS TABLE AS RETURN (SELECT title, qty FROM sales s, titles t WHERE s.stor_id = @storeid and t.title_id = s.title_id)

３ ．３　複数ステートメントのテーブル値関数例

CREATE TABLE employees (empid nchar(5) PRIMARY KEY, 
      empname nvarchar(50), 
      mgrid nchar(5) REFERENCES employees(empid), 
      title nvarchar(30)
      )

テーブル値関数 fn_FindReports(InEmpID) は、1 つの Employee ID を与えられると、その社員に直接的または間接的にレポートするすべての社員に対応するテーブルを返します。このロジックは、単一のクエリでは表現できないので、ユーザー定義関数として実装するのに適しています。

CREATE FUNCTION fn_FindReports (@InEmpId nchar(5))
RETURNS @retFindReports TABLE (empid nchar(5) primary key,
   empname nvarchar(50) NOT NULL,
   mgrid nchar(5),
   title nvarchar(30))
/*Returns a result set that lists all the employees who report to given 
employee directly or indirectly.*/
AS
BEGIN
   DECLARE @RowsAdded int
   -- table variable to hold accumulated results
   DECLARE @reports TABLE (empid nchar(5) primary key, 
      empname nvarchar(50) NOT NULL,
      mgrid nchar(5),
      title nvarchar(30),
      processed tinyint default 0)
-- initialize @Reports with direct reports of the given employee 
   INSERT @reports
   SELECT empid, empname, mgrid, title, 0
   FROM employees 
   WHERE empid = @InEmpId 
   SET @RowsAdded = @@rowcount
   -- While new employees were added in the previous iteration
   WHILE @RowsAdded > 0
   BEGIN
      /*Mark all employee records whose direct reports are going to be 
   found in this iteration with processed=1.*/
      UPDATE @reports
      SET processed = 1
      WHERE processed = 0
      -- Insert employees who report to employees marked 1.
      INSERT @reports
      SELECT e.empid, e.empname, e.mgrid, e.title, 0
      FROM employees e, @reports r
      WHERE e.mgrid=r.empid and e.mgrid <> e.empid and r.processed = 1
      SET @RowsAdded = @@rowcount
      /*Mark all employee records whose direct reports have been found
   in this iteration.*/
      UPDATE @reports
      SET processed = 2
      WHERE processed = 1
   END
   
   -- copy to the result of the function the required columns
   INSERT @retFindReports
   SELECT empid, empname, mgrid, title 
   FROM @reports
   RETURN
END
GO

-- Example invocation
SELECT * 
FROM fn_FindReports('11234')
GO

４ ．ユーザ定義関数構文

４ ．１　スカラ関数構文

CREATE FUNCTION [ owner_name.] function_name
    ([ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [,...n] ])

RETURNS scalar_return_data_type

[ WITH < function_option> [ [,] ...n] ]

[ AS ]

BEGIN
    function_body
    RETURN scalar_expression
END

４ ．２　インラインテーブル値関数構文

CREATE FUNCTION [ owner_name.] function_name
([ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [,...n] ])

RETURNS TABLE

[ WITH < function_option > [ [,] ...n ] ]

[ AS ]

RETURN [ ( ] select-stmt[) ]

４ ．３　複数ステートメントのテーブル値関数構文

CREATE FUNCTION [ owner_name.] function_name
    ([ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [,...n] ])

RETURNS @return_variable TABLE < table_type_definition >

[ WITH < function_option > [ [,] ...n ] ]

[ AS ]

BEGIN
    function_body
    RETURN END

< function_option > ::=
    { ENCRYPTION | SCHEMABINDING }

< table_type_definition > ::=     ( { column_definition |table_constraint } [ ,...n ] )

C# Programming

関数