| Oracle Rdb SQLリファレンス・マニュアル リリース7.2 E06178-01 |
|
 戻る |
 次へ |
SQL> CREATE MODULE testmod LANGUAGE SQL cont> PROCEDURE testproc; cont> COMMIT; cont> END MODULE; SQL> SHOW MODULE testmod Module name is: TESTMOD Source: TESTMOD LANGUAGE SQL Owner is: Module ID is: 1 |
例2: SQLモジュール言語を使用したストアド・モジュールの作成
次のコード部分は、非ストアド・モジュール内で、プロシージャの一部としてストアド・モジュールを作成する方法を示しています。
PROCEDURE create_them
SQLCODE;
CREATE MODULE my LANGUAGE SQL AUTHORIZATION smith
PROCEDURE p1 ( :x CHAR(5) );
BEGIN
INSERT INTO s (snum) VALUES (:x);
END;
PROCEDURE p2 ( :y SMALLINT );
BEGIN
SELECT STATUS INTO :y FROM s LIMIT TO 1 ROW;
END;
PROCEDURE p3 (:x INT, :y SMALLINT );
BEGIN
INSERT INTO s (snum) VALUES (:x);
SELECT STATUS INTO :y FROM s WHERE snum = :x;
END;
PROCEDURE p4 (:x CHAR(5), :y CHAR(20) );
BEGIN
INSERT INTO s (snum,sname) VALUES (:x, :y);
SELECT sname INTO :y FROM s WHERE snum = :x;
END;
END MODULE;
|
例3: ストアド・ルーチンが組み込まれたストアド・モジュールの作成
SQL> CREATE MODULE utility_functions cont> LANGUAGE SQL cont> -- cont> -- Define a stored procedure. cont> -- cont> PROCEDURE trace_date (:dt DATE); cont> BEGIN cont> TRACE :dt; cont> END; cont> -- cont> FUNCTION mdy (IN :dt DATE) RETURNS CHAR(10) cont> COMMENT 'Returns the date in month/day/year format'; cont> BEGIN cont> IF :dt IS NULL THEN cont> RETURN '**/**/****'; cont> ELSE cont> CALL trace_date (:dt); cont> RETURN CAST(EXTRACT(MONTH FROM :dt) AS VARCHAR(2)) || '/' || cont> CAST(EXTRACT(DAY FROM :dt) AS VARCHAR(2)) || '/' || cont> CAST(EXTRACT(YEAR FROM :dt) AS VARCHAR(4)); cont> END IF; cont> END; cont> END MODULE; |
例4: SELECT文でのストアド・ファンクションの使用
SQL> SELECT mdy(job_end), job_end
cont> FROM job_history WHERE employee_id = '00164';
JOB_END
**/**/**** NULL
9/20/1981 20-Sep-1981
2 rows selected
|
例5: ストアド・プロシージャ内での宣言されたローカル一時表の使用
SQL> -- The following table must exist in order to execute the following
SQL> -- queries.
SQL> --
SQL> CREATE TABLE payroll
cont> (employee_id CHAR(5),
cont> hours_worked INTEGER,
cont> hourly_sal REAL,
cont> week_date CHAR(10));
SQL> COMMIT;
SQL> --
SQL> -- Create the module containing a declared local temporary table.
SQL> --
SQL> CREATE MODULE paycheck_decl_mod
cont> LANGUAGE SQL
cont> DECLARE LOCAL TEMPORARY TABLE module.paycheck_decl_tab
cont> (employee_id ID_DOM,
cont> last_name CHAR(14) ,
cont> hours_worked INTEGER,
cont> hourly_sal INTEGER(2),
cont> weekly_pay INTEGER(2))
cont> ON COMMIT PRESERVE ROWS
cont> --
cont> -- Create the procedure to insert rows.
cont> --
cont> PROCEDURE paycheck_ins_decl;
cont> BEGIN
cont> INSERT INTO module.paycheck_decl_tab
cont> (employee_id, last_name, hours_worked, hourly_sal, weekly_pay)
cont> SELECT p.employee_id, e.last_name,
cont> p.hours_worked, p.hourly_sal,
cont> p.hours_worked * p.hourly_sal
cont> FROM employees e, payroll p
cont> WHERE e.employee_id = p.employee_id
cont> AND p.week_date = '1995-08-01';
cont> END;
cont> --
cont> -- Create the procedure to count the low hours.
cont> --
cont> PROCEDURE low_hours_decl (:cnt INTEGER);
cont> BEGIN
cont> SELECT COUNT(*) INTO :cnt FROM module.paycheck_decl_tab
cont> WHERE hours_worked < 40;
cont> END;
cont> END MODULE;
SQL> --
SQL> -- Call the procedure to insert the rows.
SQL> --
SQL> CALL paycheck_ins_decl();
SQL> --
SQL> -- Declare a variable and call the procedure to count records with
SQL> -- low hours.
SQL> --
SQL> DECLARE :low_hr_cnt integer;
SQL> CALL low_hours_decl(:low_hr_cnt);
LOW_HR_CNT
2
SQL> --
SQL> -- Because the table is a declared local temporary table, you cannot
SQL> -- access it from outside the stored module that contains it.
SQL> --
SQL> SELECT * FROM module.paycheck_decl_tab;
%SQL-F-RELNOTDCL, Table PAYCHECK_DECL_TAB has not been declared in module or
environment
|
例6: 単一文を組み込んだストアド・プロシージャの作成
SQL> CREATE MODULE a cont> LANGUAGE SQL cont> PROCEDURE new_salary_proc cont> (:id CHAR (5), cont> :new_salary INTEGER (2)); cont> UPDATE salary_history cont> SET salary_end = CURRENT_TIMESTAMP cont> WHERE employee_id = :id; cont> END MODULE; |
例7: 2つのルーチン間で情報を交換するグローバル変数の宣言
SQL> CREATE MODULE sample cont> LANGUAGE SQL cont> DECLARE :iter_count INTEGER cont> PROCEDURE set_iter (IN :val INTEGER) cont> COMMENT IS 'Validate the iteration count and assign' cont> / 'to a global variable.'; cont> BEGIN cont> IF (:val IS NULL) OR (:val < 1) THEN cont> SIGNAL 'XXXXX'; --illegal value cont> ELSE cont> SET :iter_count =:val; cont> TRACE 'Iteration count set to ', :val; cont> END IF; cont> END; cont> FUNCTION GET_ITER () cont> RETURNS INTEGER cont> COMMENT IS 'Trace the value used and then return the' cont> / 'value from the global variable.'; cont> BEGIN cont> TRACE 'Using iteration count ', :iter_count; cont> RETURN :iter_count; cont> END; cont> END MODULE; |
例8: 外部ルーチンによって実装されたカーソルの使用
この例では、複数の外部ルーチンを使用して、外部ルーチンのデータベース環境にある表のカーソルを管理します。この管理には、単一カーソルのOPEN、FETCHおよびCLOSEが含まれます。
アプリケーションを含むデータベース内、およびカーソルがオープンされているデータベース内で、パラメータのデータ型が一貫して定義されるよう、複数のドメインが定義されます。
create domain SQLSTATE_T char(5); create domain STATUS_CODE char(1); create domain STATUS_NAME char(8); create domain STATUS_TYPE char(14); |
外部ファンクションのインタフェースは、単一CREATE MODULE文内に組み込まれます。このモジュールでは、単一のストアドSQLプロシージャ内にもアプリケーションが組み込まれます。
create module EX
language SQL
-- These procedure define the interface to the external
-- routines that implement the transaction and cursor operations
--
procedure EX_START_READ_TXN
(inout :ss sqlstate_t);
external location 'TEST$SCRATCH:EX.EXE'
language general
general parameter style
comment is 'start a READ ONLY transaction';
procedure EX_COMMIT
(inout :ss sqlstate_t);
external location 'TEST$SCRATCH:EX.EXE'
language general
general parameter style;
procedure EX_OPEN_CURSOR
(inout :ss sqlstate_t);
external location 'TEST$SCRATCH:EX.EXE'
language general
general parameter style
comment is 'find all rows in WORK_STATUS order by STATUS_CODE';
procedure EX_CLOSE_CURSOR
(inout :ss sqlstate_t);
external location 'TEST$SCRATCH:EX.EXE'
language general
general parameter style;
procedure EX_FETCH_CURSOR
(inout :ss sqlstate_t,
out :s_code STATUS_CODE, out :s_code_ind integer,
out :s_name STATUS_NAME, out :s_name_ind integer,
out :s_type STATUS_TYPE, out :s_type_ind integer);
external location 'TEST$SCRATCH:EX.EXE'
language general
general parameter style;
-- This SQL procedures implements a simple application
--
procedure WORK_STATUS
comment is 'Use an external cursor to fetch all rows in the'
/ 'WORK_STATUS table';
begin
declare :s_code STATUS_CODE;
declare :s_name STATUS_NAME;
declare :s_type STATUS_TYPE;
declare :s_code_ind, :s_name_ind, :s_type_ind integer;
declare :ss sqlstate_t;
-- start a read-only transaction on the PERSONNEL database
call EX_START_READ_TXN (:ss);
if :ss ^= '00000' then
SIGNAL :ss;
end if;
-- open the cursor on the work-status table
call EX_OPEN_CURSOR (:ss);
if :ss ^= '00000' then
SIGNAL :ss;
end if;
-- now loop and fetch all the rows
FETCH_LOOP:
loop
call EX_FETCH_CURSOR (:ss,
:s_code, :s_code_ind,
:s_name, :s_name_ind,
:s_type, :s_type_ind);
case :ss
when '02000' then
-- no more rows to fetch
leave FETCH_LOOP;
when '00000' then
begin
-- we have successfully fetched a row, so display it
trace 'Status Code: ', case when :s_code_ind < 0
then 'NULL'
else :s_code
end;
trace 'Status Name: ', case when :s_name_ind < 0
then 'NULL'
else :s_name
end;
trace 'Status Type: ', case when :s_type_ind < 0
then 'NULL'
else :s_type
end;
trace '***';
end;
else
-- signal will implicitly leave the stored procedure
SIGNAL :ss;
end case;
end loop;
-- close the cursor
call EX_CLOSE_CURSOR (:ss);
if :ss ^= '00000' then
SIGNAL :ss;
end if;
-- commit the transaction
call EX_COMMIT (:ss);
if :ss ^= '00000' then
SIGNAL :ss;
end if;
end;
end module;
|
この例の外部プロシージャは、SQLモジュール言語で記述されています。ただし、埋込みSQLのある言語（Cなど）も使用されている可能性があります。
module EX
language GENERAL
parameter colons
-- EX: Sample application
-- Process the WORK_STATUS table using a table cursor
--
declare alias filename 'PERSONNEL'
declare c cursor for
select status_code, status_name, status_type
from WORK_STATUS
order by status_code
procedure EX_START_READ_TXN
(sqlstate);
begin
-- abort any stray transactions
rollback;
-- start a READ ONLY transaction
set transaction read only;
end;
procedure EX_COMMIT
(sqlstate);
commit work;
procedure EX_ROLLBACK
(sqlstate);
rollback work;
procedure EX_OPEN_CURSOR
(sqlstate);
open c;
procedure EX_CLOSE_CURSOR
(sqlstate);
close c;
procedure EX_FETCH_CURSOR
(sqlstate,
:s_code STATUS_CODE,
:s_code_ind integer,
:s_name STATUS_NAME,
:s_name_ind integer,
:s_type STATUS_TYPE,
:s_type_ind integer);
fetch c
into :s_code indicator :s_code_ind,
:s_name indicator :s_name_ind,
:s_type indicator :s_type_ind;
procedure EX_DISCONNECT
(sqlstate);
disconnect default;
|
アプリケーションを実行すると、外部プロシージャがコールされ、カーソルをオープンして行がフェッチされた後、その行がTRACE文によって表示されます。
SQL> set flags 'trace'; SQL> SQL> call WORK_STATUS (); ~Xt: Status Code: 0 ~Xt: Status Name: INACTIVE ~Xt: Status Type: RECORD EXPIRED ~Xt: *** ~Xt: Status Code: 1 ~Xt: Status Name: ACTIVE ~Xt: Status Type: FULL TIME ~Xt: *** ~Xt: Status Code: 2 ~Xt: Status Name: ACTIVE ~Xt: Status Type: PART TIME ~Xt: *** SQL> |
コール側のセッションが切断される前に、カーソルをクローズし、外部ルーチンのデータベース環境を切断することをお薦めします。これは、NOTIFYルーチンを使用して実現できます。
たとえば、トランザクションを開始する外部プロシージャを次のように変更すると、NOTIFYルーチン（EX_RUNDOWN）を宣言できます。このルーチンがコールされると、カーソルがクローズし、トランザクションがロールバックされてデータベースから切断されます。
procedure EX_START_READ_TXN
(inout :ss sqlstate_t);
external location 'TEST$SCRATCH:EX.EXE'
language general
general parameter style
notify EX_RUNDOWN on BIND
comment is 'start a READ ONLY transaction';
|
BIND通知を使用すると、コール元のDISCONNECT中にEX_RUNDOWNが必ずコールされ、トランザクションがロールバック可能となり、セッションが切断されます。ROLLBACKまたはCOMMITを使用すると、カーソルがWITH HOLDとして定義されていないかぎり、オープンになっているカーソルがすべて暗黙的にクローズされます。この場合、そのカーソルもクローズする必要があります。次のような（Cの）コードに、この要約ルーチンを実装します。
#include <string.h>
#include <stdio.h>
#define RDB$K_RTX_NOTIFY_ACTV_END 2
#define SQLSTATE_LEN 5
void sql_signal ();
void EX_CLOSE_CURSOR (char sqlstate [SQLSTATE_LEN]);
void EX_DISCONNECT (char sqlstate [SQLSTATE_LEN]);
void EX_ROLLBACK (char sqlstate [SQLSTATE_LEN]);
extern void EX_RUNDOWN
(int *func_code,
int *u1, /* U1, U2, U3 are currently unused */
int *u2, /* and are reserved for future use */
int *u3)
{
char sqlstate [SQLSTATE_LEN];
if (*func_code == RDB$K_RTX_NOTIFY_ACTV_END)
{
/* we are running down this external routine, so
* close the cursor
*/
EX_CLOSE_CURSOR (sqlstate);
if (memcmp ("00000", sqlstate, SQLSTATE_LEN) != 0
&& memcmp ("24000", sqlstate, SQLSTATE_LEN) != 0)
/* we expect success or maybe 24000 (bad cursor state)
*/
sql_signal ();
/* rollback the transaction
*/
EX_ROLLBACK (sqlstate);
if (memcmp ("00000", sqlstate, SQLSTATE_LEN) != 0
&& memcmp ("25000", sqlstate, SQLSTATE_LEN) != 0)
/* we expect success or maybe 25000 (bad transaction state)
*/
sql_signal ();
/* disconnect from the database
*/
EX_DISCONNECT (sqlstate);
if (memcmp ("00000", sqlstate, SQLSTATE_LEN) != 0)
/* we expect success or maybe 25000 (bad transaction state)
*/
sql_signal ();
}
}
|
このアプリケーションは、次のDCLコード部分を使用してコンパイルおよび構築できます。
$ create ex.opt symbol_vector = (EX_START_READ_TXN = procedure) symbol_vector = (EX_COMMIT = procedure) symbol_vector = (EX_ROLLBACK = procedure) symbol_vector = (EX_OPEN_CURSOR = procedure) symbol_vector = (EX_CLOSE_CURSOR = procedure) symbol_vector = (EX_FETCH_CURSOR = procedure) symbol_vector = (EX_DISCONNECT = procedure) symbol_vector = (EX_RUNDOWN = procedure) psect_attr = RDB$MESSAGE_VECTOR,noshr psect_attr = RDB$DBHANDLE,noshr psect_attr = RDB$TRANSACTION_HANDLE,noshr sql$user/library $ $ cc EX_RUNDOWN $ sql$mod EX $ link/share EX,EX_RUNDOWN,EX/option |
新しい問合せアウトラインを作成して、そのアウトラインをデータベースに格納します。問合せアウトラインとは、問合せの実装方法の概略の計画であり、問合せの処理時にオプティマイザで選択される結合順序、結合方法、索引の使用方法（またはこれらのすべて）を制御するディレクティブを含めることもできます。問合せアウトラインを使用すると、Oracle Rdbの複数のリリースにわたって問合せのパフォーマンスを高度に安定させることができます。
CREATE OUTLINE文は、対話型SQLでのみ使用できます。
ACCESS PATH ANY
ACCESS PATH SEQUENTIAL
ACCESS PATH DBKEY
ACCESS PATH ROWID
ACCESS PATH NO INDEX
基底データベース表からデータを取得するために使用するアクセス・パスを指定します。次の表では、有効なアクセス・パスをリストしています。
パス 意味 ANY オプティマイザで最も適切な方法を選択できることを示す。 SEQUENTIAL 順次アクセスを使用する必要があることを示す。 DBKEY データベース・キーによるアクセスを使用する必要があることを示す。 ROWID1 データベース・キーによるアクセスを使用する必要があることを示す。 NO INDEX 索引を必要としない任意のアクセス・パスを使用できることを示す。NO INDEXのシノニムとしてNOINDEXも受け入れられる。
デフォルトのアクセス・パスはありません。問合せアウトライン定義内に指定された各データベース表について、アクセス・パスを指定する必要があります。
指定する索引名は、関連するアクセス方法のある表に関連付けられている既存の索引を示している必要があります。
この句は、ID id-number句を使用してアウトラインを作成する場合にのみ必要です。
MANDATORYは、すべてのアウトライン・ディレクティブ（表の順序、索引の使用方法など）が指定どおりである必要があることを示します。オプティマイザがアウトライン・ディレクティブに準拠しない場合、例外が発生します。
OPTIONALは、すべてのアウトライン・ディレクティブがオプションであり、それに準拠できない場合にも例外が発生しないことを示します。OPTIONALを指定した場合、基底リクエストの実行のためにオプティマイザで選択される計画が、アウトラインで指定されている計画と一致しないことがあります。
Oracle Rdbの今後のバージョンでより効率的な計画が検出された場合でも、Oracle Rdbの各バージョンで指定されているとおりの計画をオプティマイザで選択する必要がある場合は、MANDATORYを使用します。
デフォルトはCOMPLIANCE OPTIONALです。
| オプション | 意味 |
|---|---|
| ANY | オプティマイザで任意の最適化方法を選択できることを示す。 |
| FAST FIRST | オプティマイザで、適切な場合にFAST FIRST最適化を使用できることを示す。 |
| NONE | オプションの最適化を適用できないことを示す。 |
| TOTAL TIME | オプティマイザで、適切な場合にTOTAL TIME最適化を使用できることを示す。 |
デフォルトはEXECUTION OPTIONS (ANY)です。
AS句が指定されていない場合、sql-queryがコンパイルされ、結果のアウトラインが格納されます。AS句が指定されている場合、sql-queryはIDを指定するための代替手段となります。USING句が指定されている場合、sql-queryは指定したアウトラインを開始点として使用し、最適化されます。
FROM句でsql-queryとして受け入れられるのはSELECT文のみです。sql-queryはセミコロンで終わらないようにします。
MODE句の指定はオプションです。AS句の指定は必須です。ID id-number句とともにUSING句は指定できません。
| 方法 | 意味 |
|---|---|
| CROSS | クロス計画を使用する必要があることを示す。 |
| MATCH | 一致計画を使用する必要があることを示す。1 |
| ANY METHOD | オプティマイザで、2つのデータソースを結合するために任意の方法を選択できることを示す。 |
デフォルトの結合方法はありません。
単一の問合せ用に複数のアウトラインを作成する場合、それらのアウトラインに同じアウトライン・モードは指定できません。問合せに対して複数のアウトラインが存在する場合は、オプティマイザで使用するアウトラインのアウトライン・モードの値に、論理名RDMS$BIND_OUTLINE_MODEを設定できます。たとえば、日中および夜間に実行する問合せがあり、これに対して2つのアウトラインを作成した場合、日中に使用するアウトライン用にデフォルトのアウトライン・モード0を保持し、夜間に使用するアウトライン用にアウトライン・モード--1を割り当てられます。夜間に論理名RDMS$BIND_OUTLINE_MODEを--1に設定すると、適切なアウトラインが適切な時間に実行されます。
有効なモード値は--2,147,483,648〜2,147,483,647です。正のモード値は今後の使用のために予約されています。そのため、モード値には0〜--2,147,483,648の値を指定することをお薦めします。
QUERYソース・ブロック以外のすべてのデータソース間で、結合方法を指定する必要があります。
順序付きまたは順序付けられていないグループの直前に結合方法がある場合、結合方法は、そのグループ内で最初に指定された結合項目に関連付けられます。 |
UNION計画はUNION演算子を使用する問合せでのみ有効です。UNION演算子を指定する問合せではすべてUNION計画を使用する必要があります。
この句はID id-number句とともには使用できません。
- オプティマイザを使用してアウトラインを作成する方法、および問合せアウトラインをカスタマイズする方法の詳細は、『Oracle Rdb7 Guide to Database Performance and Tuning』で問合せオプティマイザに関する章を参照してください。
- 問合せアウトラインで参照されるすべての表に対して、CREATE権限を持っている必要があります。
- CREATE OUTLINE文はオンライン操作です。他のユーザーは、アウトラインが作成されている場合にデータベースにアタッチできます。
- それぞれの問合せアウトラインに含めることができるSQL文は1つのみです。
- 論理名RDMS$DEBUG_FLAGSとともにSs（二重引用符で囲んだ大文字のS、続いて小文字のs）を指定すると、オプティマイザで生成されたアウトラインを表示できます。または、SET FLAGS 'OUTLINE'文を指定します。詳細は、「SET FLAGS文」および『Oracle Rdb7 Guide to Database Performance and Tuning』を参照してください。
- コメントのそれぞれの文字列リテラルの最大長は1024文字です。
- 宣言されたローカル一時表には索引を定義できないため、使用可能な唯一の表アクセス・パスは、MODULE句の使用時におけるSEQUENTIAL、DBKEYまたはROWIDです。
- 非ストアド・モジュール名はMODULE句では使用できません。
- ストアド・ルーチンは、ON PROCEDURE句またはON FUNCTION句でのみ指定できます。外部ルーチンを指定すると、SQLでエラーが生成されます。
- アウトライン内の問合せの順序は、実行順序ではなく、最適化の順序と一致します。Oracle Rdbにより生成される問合せアウトラインは、読みやすくするため、アウトライン内のQUERYキーワード後にコメントとともに表示されます。「例」の項を参照してください。
- Oracle Rdbにより生成される問合せアウトラインには、プロシージャ内の各文に対応する問合せが含まれない場合もあります。
- すべての文に問合せオプティマイザが必要なわけではありません。たとえば、表を参照しないTRACE文やSET文にはオプティマイザは不要です。
- IF文およびCASE文内の副問合せは、その問合せに関連するオーバーヘッドを軽減するため、オプティマイザによって前の文に繰り入れられる場合があります。
- INSERT文内の副問合せは、INSERT操作の前にSET文が実行されたものとして実行されます。
- INSERT文は問合せの最適化対象ではありません。
- Oracle Rdbでは、制約またはトリガーのコンパイル時に、コンパイルするトリガーまたは制約と同名の問合せアウトラインが検索されます。一致対象が検出されると、そのアウトラインがトリガーまたは制約の問合せコンパイル時に使用されます。該当するオブジェクトの名前と一致するアウトラインがない場合、Oracle Rdbでは問合せのBLR IDを使用して適切なアウトラインの検索が試行されます。次に例を示します。
