| Oracle Rdb SQLリファレンス・マニュアル リリース7.2 E06178-01 |
|
 戻る |
 次へ |
2.6.2.7 CURRENT_TIMEファンクションおよびLOCALTIMEファンクション
CURRENT_TIMEファンクションでは、現在の時間、分、秒を含むTIMEデータ型の値が返されます。
CURRENT_TIMEから返される秒は、0〜2の間で小数精度を指定できます。小数秒精度とは、フィールドに返される桁数を指定する数値のことです。たとえば、小数精度2は100分の1秒（小数点以下2桁）で秒が返され、小数精度1は10分の1秒（小数点以下1桁）で秒が返されることを意味します。
例1: 次の例は、データ型TIMEのドメインを作成する方法およびCURRENT_TIMEを列に挿入する方法を示しています。
SQL> CREATE DOMAIN END_TIME_DOM IS TIME; SQL> CREATE TABLE HOURS_WORKED (END_TIME END_TIME_DOM); SQL> INSERT INTO HOURS_WORKED (END_TIME) VALUES (CURRENT_TIME); 1 row inserted SQL> SELECT * FROM HOURS_WORKED; END_TIME 15:03:07 1 row selected |
次の例に示すように、デフォルト以外の小数秒精度を使用して、時刻フィールドまたはタイムスタンプ・フィールドに対して現在のデフォルトを指定できます。
例2: この例では、現在のデフォルトと異なる小数秒精度をユーザーが指定すると、エラーが発生します。
SQL> CREATE DOMAIN Y TIME(2) DEFAULT CURRENT_TIME(1); %SQL-F-DEFVALINC, You specified a default value for Y which is inconsistent with its data type SQL> CREATE DOMAIN Y TIME(1) DEFAULT CURRENT_TIME(1); |
LOCALTIME組込みファンクションはCURRENT_TIMEのシノニムであり、SQL:1999データベース言語規格によって定義されます。
2.6.2.8 CURRENT_TIMESTAMPファンクションおよびLOCALTIMESTAMPファンクション
CURRENT_TIMESTAMPファンクションでは、今日の年、月、日および現在の時刻の時間、分、秒を含むTIMESTAMPデータ型の値が返されます。
CURRENT_TIMEの場合と同様に、CURRENT_TIMESTAMPから返される秒は、0〜2の間で小数精度を指定できます。小数秒精度とは、フィールドに返される桁数を指定する数値のことです。
CURRENT_TIMESTAMPデータ型には、DATE VMSまたはDATE ANSIのいずれかの形式が有効です。DATE VMS列では、日時算術はできません。DATE VMS形式のCURRENT_TIMESTAMPは、今日の日、月、年および現在の時刻の時間、分、秒を指定します。DATE ANSI形式のCURRENT_TIMESTAMPは、今日の年、月、日の後に現在の時刻の時間、分、秒を続けて指定します。
LOCALTIMESTAMP組込みファンクションはCURRENT_TIMESTAMPのシノニムであり、SQL:1999データベース言語規格によって定義されます。
例: CURRENT_TIMESTAMPファンクションの使用
例1: 次の例では、ORDER_TABLE2でINSERT文を実行するたびにCURRENT_TIMESTAMPの値が記述されます。
SQL> CREATE DOMAIN LOGGING_DATE TIMESTAMP(1) DEFAULT CURRENT_TIMESTAMP(1); SQL> CREATE TABLE ORDER_TABLE2 cont> (PART_NUM INT, cont> ORDER_LOGGED LOGGING_DATE, cont> DELIVERY_DATE TIMESTAMP(1), cont> TIME_TO_DELIVER cont> COMPUTED BY (DELIVERY_DATE - ORDER_LOGGED) DAY(6) TO MINUTE, cont> SLOW_DELIVERY cont> COMPUTED BY (EXTRACT(DAY FROM cont> (DELIVERY_DATE - ORDER_LOGGED) DAY(6)) - 30) cont> ); |
例2: 次の例では、CURRENT_TIMESTAMPデータ型はデフォルトでVMS形式を使用していますが、TIMESTAMPデータ型ではANSI形式を使用しているため、エラー・メッセージが表示されます。
SQL> CREATE DOMAIN LOGGING_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP; %SQL-F-DEFVALINC, You specified a default value for LOGGING_DATE which is inconsistent with its data type |
SQLでは、次のようにDATEデータおよびCURRENT_TIMESTAMPデータをANSI形式に変更する方法がいくつか提供されています。
例3: 次の例は、CURRENT_TIMESTAMPのDATE VMS出力形式およびDATE ANSI出力形式を示しています。
SQL> ATTACH 'FILENAME corporate_data'; SQL> SHOW ANSI DATE DATE data type equates to DATE VMS SQL> SELECT CURRENT_TIMESTAMP FROM DAILY_HOURS LIMIT TO 1 ROW; 15-AUG-1991 10:40:52.83 1 row selected SQL> SET DEFAULT DATE FORMAT 'SQL99'; SQL> SHOW ANSI DATE DATE data type equates to DATE ANSI SQL> SELECT CURRENT_TIMESTAMP FROM DAILY_HOURS LIMIT TO 1 ROW; 1991-08-15 10:41:02.52 1 row selected |
ドメインまたは表を作成する前に、SET DEFAULT DATE FORMAT文を使用する必要があります。データベース定義を作成した後は、この文を使用してデータ型を変更できません。
CURRENT_DATE、CURRENT_TIMEおよびCURRENT_TIMESTAMPの各キーワードには、Oracle Rdbで有効な式のどこからでもアクセスできます。
例4: 次の例は、JOB_HISTORY表のJOB_START列にデータを挿入するSQL文の例を示しています。
SQL> INSERT INTO JOB_HISTORY (JOB_START ...) cont> VALUES (CURRENT_TIMESTAMP, ...); |
CURRENT_DATE、CURRENT_TIMEまたはCURRENT_TIMESTAMPの各キーワードを1つの文で複数回使用すると、同じ値の日時が保持されます。
例5: この問合せでは、絶対日付の差を計算し、計算された期間から年コンポーネントを選択（および出力）する必要があります。
SQL> SELECT FIRST_NAME, LAST_NAME, ' is ', cont> EXTRACT(YEAR FROM (CURRENT_TIMESTAMP - BIRTHDAY) YEAR), cont> ' years old' cont> FROM EMPLOYEES; |
例6: 次の例のトリガーでは、EMPLOYEES表の更新履歴が記録されます。例に示すHISTORY表には、従業員の誕生日、更新ユーザー名、更新行の従業員ID番号を含む行に対して実行したすべての更新の日時が含まれます。
SQL> -- Create a new table for the trigger.
SQL> CREATE TABLE HISTORY
cont> ("DATE" DATE,
cont> USER_NAME CHAR(14),
cont> UPDATED_ID CHAR(5));
SQL> --
SQL> CREATE TRIGGER EMP_UPD_TRIG AFTER UPDATE ON EMPLOYEES
cont> (INSERT INTO HISTORY ("DATE", USER_NAME, UPDATED_ID)
cont> VALUES (CURRENT_DATE, USER, EMPLOYEE_ID))
cont> FOR EACH ROW;
|
通常、文の一部として実行されるトリガーにはすべて同じタイムスタンプが付けられます。タイムスタンプは、文の実行時刻を示しています。
例7: CURRENT_TIMESTAMPキーワードのDEFAULT句に対応する日付を設定することもできます。この場合、INSERT文を実行するたびにCURRENT_TIMESTAMPの値が記述されます。
SQL> CREATE TABLE TIMESTAMP_TABLE cont> (LOG_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP, cont> USER_NAME CHAR(14) DEFAULT USER, cont> UPDATED_ID CHAR(5)); SQL> -- SQL> CREATE TRIGGER EMP_UPD_TRIG AFTER UPDATE ON EMPLOYEES cont> (INSERT INTO HISTORY (UPDATED_ID) cont> VALUES (EMPLOYEE_ID)) cont> FOR EACH ROW; |
CURRENT_UIDファンクションでは、現在のユーザーを表す一意の整数が返されます。このUID値は、ストアド・ルーチンを現在実行しているAUTHORIZATIONユーザーかロール、または現在認可がない場合はSESSION_USERに基づいています。
2.6.2.10 CURRENT_USERファンクション
CURRENT_USERファンクションでは、リクエストに対して現在アクティブなユーザー名が返されます。
定義者の権限リクエストを実行している場合は、モジュール定義者の権利識別子が返されます。定義者の権限リクエストを実行していない場合は、セッション・ユーザー名（存在する場合）が返されます。セッション・ユーザー名が存在しない場合は、システム・ユーザー名が返されます。詳細は、第2.2.2項を参照してください。
データ型はCHAR(31)になります。
CURRENT_USERファンクションでは、トリガー定義者のユーザー名は返されません。
例: CURRENT_USERファンクションの使用
例1: ユーザーが挿入した行にのみアクセスできるように、ビューを作成
SQL> CREATE VIEW SELECTIVE_EMPLOYEES_UPDATE AS cont> SELECT * FROM EMPLOYEES cont> WHERE USER_ID = CURRENT_USER cont> WITH CHECK OPTION CONSTRAINT MUST_HAVE_USER; |
EXTRACTファンクションでは、データ型がDATE、TIME、TIMESTAMPまたはINTERVALの列から整数で表される1つの日時フィールドが返されます。
EXTRACTが返すことのできる日時フィールドには、次のものがあります。
返されるデータ型は、日時フィールドがSECONDでない場合、スケール0の符号付きロングワードです。SECONDフィールドを選択した場合は、スケールが2に設定されます。
WEEKDAYを指定した場合は、抽出ソースとしてTIMESTAMPおよびDATEのデータ型のみを使用できます。それ以外の場合は、抽出ソースにDATE、TIME、TIMESTAMPまたはINTERVALのデータ型を使用できます。WEEKDAYを指定すると、曜日を表す整数が返されます。（月曜日は1、日曜日は7になります。）
EXTRACTファンクションがNULL値に適用されると、NULL値が返されます。
年頭から数えた日数はユリウス日付と呼ばれ、プログラマが直接アクセスする必要のある重要な整数値となることがあります。SQLのEXTRACTファンクションを使用すると、日時データ型として定義された列データからユリウス日付を求めることができます。
JULIANキーワードでは、抽出式の結果がDATE ANSIまたはTIMESTAMPのいずれかの日時データ型になる必要があります。いずれのデータ型にもならない値式は失敗します。たとえば、CURRENT_TIMEデータ型で定義された式からユリウス日付を抽出しようとすると、次のSQLエラー・メッセージが生成されます。
SQL> SELECT EXTRACT(JULIAN FROM CURRENT_TIME) FROM ACCOUNTING.DAILY_HOURS; %RDB-F-CONVERT_ERROR, invalid or unsupported data conversion -RDMS-E-EXT_JULIAN_TS, invalid type for EXTRACT JULIAN, must be DATE or TIMESTAMP SQL> |
EXTRACTファンクションでJULIANキーワードを使用して1858年からの日数を表すことはできません。これは、JULIANでは1月1日から計算されますが、1858年の最初の日が11月18日になっているためです。
オプションのWEEK_NUMBERおよびYEAR_WEEKでは、国際規格ISO 8601:1988「Data elements and interchange formats - Information interchange - Representation of dates and times」で定義されている週番号が返されます。
WEEK_NUMBERは、年の週（ほとんどの年は52週）を表す1〜53までの数値です。週は月曜日から始まり、ほとんどの日付は特定の年に含まれます。
YEAR_WEEKはWEEK_NUMBERのバリエーションであり、週が論理的に属している年（世紀を含む）が含まれます。値の範囲は185901〜999952（9999年以降の年で日付が構成されている場合は、さらに高い値が可能）です。値の最後の2桁は、WEEK_NUMBERオプションから返される値と同じです。
例: EXTRACTファンクションの使用
例1: EXTRACTファンクションを使用した、会社に雇用されている従業員のうち勤続期間が最も長い従業員の検索
SQL> CREATE VIEW MY_VIEW2 cont> (LAST_NAME, TODAYS_DATE, JOB_START, MONTHS_EMPLOYED) cont> AS SELECT E.LAST_NAME, CURRENT_DATE, JH.JOB_START, cont> EXTRACT (MONTH FROM cont> (CURRENT_DATE - CAST(JH.JOB_START AS DATE ANSI)) MONTH) cont> FROM EMPLOYEES E, JOB_HISTORY JH cont> WHERE E.EMPLOYEE_ID = JH.EMPLOYEE_ID cont> AND cont> (CURRENT_DATE - CAST(JH.JOB_START AS DATE ANSI)) MONTH = cont> (SELECT cont> MAX ((CURRENT_DATE - CAST (JH.JOB_START AS DATE ANSI)) MONTH) cont> FROM JOB_HISTORY JH); SQL> SELECT * FROM MY_VIEW2; LAST_NAME TODAYS_DATE JOB_START MONTHS_EMPLOYED Smith 1993-12-02 1-JUL-1975 00:00:00.00 221 Nash 1993-12-02 1-JUL-1975 00:00:00.00 221 Gray 1993-12-02 1-JUL-1975 00:00:00.00 221 Peters 1993-12-02 1-JUL-1975 00:00:00.00 221 . . . Ames 1993-12-02 1-JUL-1975 00:00:00.00 221 Blount 1993-12-02 1-JUL-1975 00:00:00.00 221 43 rows selected |
例2: EXTRACTファンクションを使用した、オーダー項目の支払期限が切れる時期の計算
SQL> SET DEFAULT DATE FORMAT 'SQL92';
SQL> CREATE DOMAIN LOGGING_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
SQL> CREATE TABLE ORDER_TABLE
cont> (ORDER_NUMBER INT,
cont> COMPANY_NAME VARCHAR(40),
cont> ORDER_LOGGED LOGGING_DATE,
cont> DELIVERY_DATE DATE ANSI,
cont> TIME_TO_DELIVER
cont> COMPUTED BY (DELIVERY_DATE - ORDER_LOGGED) DAY(3) TO MINUTE.
cont> SLOW_DELIVERY
cont> COMPUTED BY (EXTRACT(DAY FROM (DELIVERY_DATE - ORDER_LOGGED)
cont> DAY) - 30));
SQL> INSERT INTO ORDER_TABLE
cont> (ORDER_NUMBER,
cont> COMPANY_NAME,
cont> ORDER_LOGGED,
cont> DELIVERY_DATE)
cont>VALUES
cont> (1,
cont> 'ABC INC.',
cont> TIMESTAMP '1991-2-4 10:30:00.00',
cont> DATE '1991-6-1'
cont> );
1 row inserted
SQL> --
SQL> INSERT INTO ORDER_TABLE
cont> (ORDER_NUMBER,
cont> COMPANY_NAME,
cont> DELIVERY_DATE)
cont> VALUES
cont> (2,
cont> 'JJ ROOFING',
cont> DATE '1991-5-1'
cont> );
1 row inserted
SQL> --
SQL> SELECT ORDER_NUMBER, ORDER_LOGGED, DELIVERY_DATE FROM ORDER_TABLE;
ORDER_NUMBER ORDER_LOGGED DELIVERY_DATE
1 1991-02-04 10:30:00.000000 1991-06-01
2 1991-04-18 09:06:05.630000 1991-05-01
2 rows selected
SQL> --
SQL> SELECT TIME_TO_DELIVER, SLOW_DELIVERY FROM ORDER TABLE
cont> WHERE SLOW_DELIVERY >= 0;
TIME_TO_DELIVER SLOW_DELIVERY
116:13:30 86
1 row selected
SQL> --
SQL> SELECT COMPANY_NAME,EXTRACT(WEEKDAY FROM ORDER_LOGGED) FROM ORDER_TABLE;
COMPANY_NAME
ABC INC. 1
JJ ROOFING 4
2 rows selected
|
例3: EXTRACTファンクションを使用した、ユリウス日付の計算
SQL> -- Attach to the multischema database corporate_data and define SQL> -- a default catalog and schema setting. SQL> -- SQL> ATTACH 'FILENAME corporate_data'; SQL> SET CATALOG 'ADMINISTRATION'; SQL> SET SCHEMA 'PERSONNEL'; SQL> -- SQL> -- Create view to show column heads for SELECT statement. The EXTRACT SQL> -- function using the new JULIAN keyword calculates the Julian date SQL> -- of an employee's birthday. SQL> -- SQL> CREATE VIEW JULIAN_YEAR cont> (LAST_NAME, EMPLOYEE_ID, BIRTHDAY, JULIAN_DATE) cont> AS SELECT LAST_NAME, EMPLOYEE_ID, BIRTHDAY, cont> EXTRACT(JULIAN FROM BIRTHDAY) cont> FROM EMPLOYEES WHERE EMPLOYEE_ID = '00415'; SQL> SELECT * FROM JULIAN_YEAR; LAST_NAME EMPLOYEE_ID BIRTHDAY JULIAN_DATE Mistretta 00415 1947-05-23 143 1 row selected SQL> ROLLBACK; |
ユリウス日付143は、1947年1月1日から1947年5月23日までの日数を表します。（従業員がうるう年である1948年の同じ日に生まれた場合は、ユリウス日付144が返されます。）サンプル・ディレクトリのマルチスキーマ・データベースcorporate_dataを使用して、この例を試してください。
例4: YEAR_NUMBERおよびYEAR_WEEKを指定したEXTRACTファンクションの使用
SQL> select dt, cont> extract (week_number from dt), cont> extract (year_week from dt) cont> from week_sample cont> order by dt; DT 1859-01-07 1 185901 1999-01-01 53 199853 1999-01-04 1 199901 1999-01-10 1 199901 1999-12-31 52 199952 2000-01-01 52 199952 2000-01-03 1 200001 2000-02-28 9 200009 2000-02-29 9 200009 2000-03-01 9 200009 9999-12-31 52 999952 11 rows selected |
LENGTHファンクションでは、指定した文字列の文字数が返されます。CHARACTER_LENGTHファンクションも参照してください。
2.6.2.13 LENGTHBファンクション
LENGTHBファンクションでは、指定した文字列のバイト数が返されます。OCTET_LENGTHファンクションも参照してください。
2.6.2.14 LOWERファンクション
LOWERファンクションでは、値式に含まれるすべての大文字が小文字に変換されます。このファンクションは、データベース内の値式で整合性を保持する場合に便利です。
値式の結果がNULLの場合は、NULL値が返されます。
例: LOWERファンクションの使用
LOWERファンクションを使用して、DEPARTMENT_NAMEに含まれる大文字を小文字に変換します。
SQL> SELECT DEPARTMENT_NAME, LOWER(DEPARTMENT_NAME) cont> FROM DEPARTMENTS cont> LIMIT TO 3 ROWS; DEPARTMENT_NAME Corporate Administration corporate administration Electronics Engineering electronics engineering Large Systems Engineering large systems engineering 3 rows selected SQL> |
LOWERファンクションを使用する場合、値式のキャラクタ・セットのルールに従って小文字に変換されます。たとえば、値式のキャラクタ・セットが簡体とASCIIの場合、ASCII文字のみが小文字に変換されます。簡体文字は変換されません。
2.6.2.15 OCTET_LENGTHファンクション
OCTET_LENGTH（またはLENGTHB）ファンクションでは、データ型の値式の長さがオクテット単位で計算されます。
値式の結果がNULLの場合は、NULL値が返されます。それ以外の場合は、値式の長さがオクテット単位で返されます。OCTET_LENGTHのかわりにLENGTHBを使用できます。
例: OCTET_LENGTHファンクションの使用
例1: OCTET_LENGTHファンクションを使用した、CHAR列およびVARCHAR列の値の文字数の計算
SQL> -- This example uses the personnel sample database.
SQL> -- Because the column LAST_NAME is defined as CHAR(14), a fixed-length
SQL> -- data type, SQL pads the values in the column with blanks. The
SQL> -- following statement returns the same value for all the rows.
SQL> --
SQL> SELECT OCTET_LENGTH (LAST_NAME), LAST_NAME
cont> FROM EMPLOYEES
cont> LIMIT TO 3 ROWS;
LAST_NAME
14 Ames
14 Andriola
14 Babbin
3 rows selected
SQL> --
SQL> -- Because the column CANDIDATE_STATUS is defined as VARCHAR(255), a
SQL> -- varying-length data type, SQL does not pad the column with blanks.
SQL> --
SQL> SELECT OCTET_LENGTH (CANDIDATE_STATUS) FROM CANDIDATES;
63
69
46
3 rows selected
SQL>
|
例2: OCTET_LENGTHファンクションおよび複数のオクテット・キャラクタ・セットの使用
POSITIONファンクションでは、文字値式の文字列が検索されます。最初の文字値式は、検索文字列とも呼ばれます。2番目の文字値式は、ソース文字列とも呼ばれます。検索文字列が見つかると、ソース文字列における検索文字列の位置を示す数値が返されます。返される数値は1で開始し、ソース文字列における検索文字列の絶対位置を示します。検索文字列とソース文字列の一致では、大/小文字が区別されます。
ソース文字列で検索文字列が見つからない場合は、値ゼロ（0）が返されます。文字列のいずれかがNULLの場合は、NULLが返されます。
POSITIONファンクションのFROM句はANSI/ISO SQL SQL規格の拡張であり、任意の位置から検索を開始できます。
例: POSITIONファンクションの使用
例1: SELECT文でのPOSITIONファンクションの使用
SQL> SELECT COLLEGE_NAME,
cont> POSITION ('University' IN COLLEGE_NAME)
cont> FROM COLLEGES
cont> WHERE COLLEGE_NAME LIKE '_%University%';
COLLEGE_NAME
American University 10
Drew University 6
Harvard University 9
Purdue University 8
Stanford University 10
Yale University 6
6 rows selected
|
例2: POSITIONファンクションおよびSUBSTRING句の使用
SQL> SELECT SUBSTRING (COLLEGE_NAME FROM 1 FOR
cont> POSITION ('University' IN COLLEGE_NAME) -1)
cont> FROM COLLEGES
cont> WHERE COLLEGE_NAME LIKE '_%University%';
American
Drew
Harvard
Purdue
Stanford
Yale
6 rows selected
|
例3: POSITIONファンクションを使用した個々の語句の検索。この例ではTRACE文を使用するため、RDMS$DEBUG_FLAGS論理名をXtに定義する必要があります。
SQL> BEGIN
cont> DECLARE :TXT VARCHAR(100);
cont> DECLARE :RES VARCHAR(20);
cont> DECLARE :ST, :EN INTEGER;
cont> --
cont> SET :TXT = 'Some words and phrases';
cont> --
cont> -- Start at the beginning
cont> --
cont> SET :ST = 1;
cont> --
cont> -- Loop over all the text looking for space delimiters
cont> --
cont> WHILE :ST <= CHAR_LENGTH(:TXT)
cont> LOOP
cont> SET :EN = POSITION (' ' IN :TXT FROM :ST);
cont> IF :EN = 0 THEN
cont> --
cont> -- No trailing spaces, so assume space after last character
cont> --
cont> SET :EN = CHAR_LENGTH(:TXT) + 1;
cont> END IF;
cont> SET :RES = SUBSTRING(:TXT FROM :ST FOR :EN - :ST);
cont> IF CHAR_LENGTH (TRIM (:RES)) > 0 THEN
cont> --
cont> -- Have a word to display
cont> --
cont> TRACE 'Word: "', :RES, '"';
cont> END IF;
cont> --
cont> -- Advance the start position
cont> --
cont> SET :ST = :EN + 1;
cont> END LOOP;
cont> END;
~Xt: Word: "Some "
~Xt: Word: "words "
~Xt: Word: "and "
~Xt: Word: "phrases "
|
