SQLの窓

2011年09月10日


Oracle : PL/SQL : Function のサンプルと使い方

Function は、Procedure と違って、一つの戻り値を返す仕様になっているのでselect 構文の中でシステムで定義された関数と同様の使い方ができます。

以下の Function は、引数で与えられた値を元に、社員マスタを参照して3種類の値を select の結果として返す事になります。

1) データが存在した場合 : 氏名
2) 元の値が NULL の場合 : 該当者は存在しません
3) 対象行が存在しなかった場合 : 該当データがシステムに存在しません
select 
	社員コード,
	氏名,
	GET_CNAME(管理者) as 管理者名
 from 社員マスタ
自分で構築したシステム上で使う場合、特殊な計算や複数の煩雑なテーブル参照というような処理を Function で定義しておく事によって、効率が良く見通しの良いアプリケーションを作成する事ができます
CREATE OR REPLACE FUNCTION GET_CNAME
(
	/* 文字列引数の定義 */
	PM_STRING IN VARCHAR2
)
/* 戻り値の定義 */
RETURN VARCHAR2

/**********************************************************/
/* 変数の定義 */
/**********************************************************/
AS
	RET_VALUE	VARCHAR2(2000);
	WK_VALUE	VARCHAR2(2000) := '初期値の設定';

/**********************************************************/
/* 処理開始 */
/**********************************************************/
BEGIN
	/* デバッグ表示 */
	DBMS_OUTPUT.PUT_LINE( 'デバッグ:開始' );
	DBMS_OUTPUT.PUT_LINE( 'デバッグ:引数:' || PM_STRING );
	DBMS_OUTPUT.PUT_LINE( WK_VALUE );

	/* 代入 */
	RET_VALUE := PM_STRING;

	IF RET_VALUE is NULL THEN 
		DBMS_OUTPUT.PUT_LINE( 'NULLデータです' );
		RET_VALUE := '該当者は存在しません';
	ELSE 
		DBMS_OUTPUT.PUT_LINE( 'データ有り' );
		SELECT 氏名 INTO WK_VALUE from 社員マスタ
			WHERE 社員コード = RET_VALUE;
		RET_VALUE := WK_VALUE;
	END IF; 

	/* 戻り値 */
	RETURN RET_VALUE;

/**********************************************************/
/* 一番外側のブロックの例外処理 */
/**********************************************************/
$if 1=1 $then
EXCEPTION
	WHEN OTHERS THEN
		WK_VALUE := '例外発生:'||SQLCODE||':'||SQLERRM;
		DBMS_OUTPUT.PUT_LINE( WK_VALUE );
		RET_VALUE := '該当データがシステムに存在しません';
		RETURN RET_VALUE;
$end
END;

SELECT INTO に対して単純に WHEN OTHERS THEN で例外処理を行っていますがこの場合は、NO_DATA_FOUND が発生しているはずです。

関連する記事

PL/SQL : Function 雛形




タグ:Oracle PL/SQL
posted by lightbox at 2011-09-10 01:18 | Oracle : PL/SQL | このブログの読者になる | 更新情報をチェックする

2010年06月13日


PL/SQL : OUT タイプのパラメータを持つストアードプロシージャの処理

DBMS_OUTPUT と言う、有名なデバッグ用のパッケージがありますが、この出力結果をアプリケーションから取得するプロシージャは以下のような定義がなされています。
DBMS_OUTPUT.GET_LINE (
   line    OUT VARCHAR2,
   status  OUT INTEGER);

line
最後の改行文字を除いて、バッファに入れられた単一行の情報を戻します。
「ORA-06502: PL/SQL: 数値または値のエラーが発生しました: 文字列バッ
ファが小さすぎます。」の発生を回避するために、このパラメータの実際
の値をVARCHAR2 (32767)として宣言する必要があります。
 
status
コールが正常に完了すると、ステータス0(ゼロ)が戻ります。バッファに
これ以上行がない場合は、ステータス1が戻ります。
 

以下は、Windows の PHP で、OracleInProcServer.XOraSession (OO4O) を使って一時的な PL/SQL 実行の中から DBMS_OUTPUT を行って、その結果を取得する例です。

Add メソッドの第二引数は以下のように説明されています。パラメータの初期値を指定するVariant。 初期値によってこのパラメータのデータ型が定義されるため、重要です。 

これは、クライアント側の事で、サーバー側の定義は ServerType プロパティで行っています
<?
com_load_typelib("{F2D4ED20-FFD3-101A-ADF2-04021C007002},5,0");

$OraSession = new COM( "OracleInProcServer.XOraSession" );

// ***********************************************************
// 処理開始
// ***********************************************************
$plsql = <<<MYTEXT
DECLARE 
	WK_KEY VARCHAR2(4) := '0001';
	WK_NAME VARCHAR2(50);
BEGIN 
	SELECT 氏名 INTO WK_NAME
		FROM 社員マスタ
		where 社員コード = WK_KEY;

	DBMS_OUTPUT.PUT_LINE(WK_NAME);
END;
MYTEXT;

// "PC名/サービス", "ユーザ/パスワード"
$OraDatabase = $OraSession->OpenDatabase("night/xe", "LB/LB", ORADB_DEFAULT);
$OraDatabase->LastServerErrReset();

// DBMS_OUTPUT を使用可能にする
$Query = "BEGIN DBMS_OUTPUT.ENABLE(); END;";
$OraDatabase->ExecuteSQL( $Query );

// CrLf では動かないので、Lf に変換して実行
$Query = str_replace("\r\n","\n", $plsql );
$OraDatabase->ExecuteSQL( $Query );

// DBMS_OUTPUT.GET_LINE の仕様に基づいてパラメータの設定
$OraDatabase->Parameters->Add("PARAM", "", ORAPARM_OUTPUT);
$OraDatabase->Parameters["PARAM"]->ServerType = ORATYPE_VARCHAR2;

$OraDatabase->Parameters->Add("STATUS", 0, ORAPARM_OUTPUT);
$OraDatabase->Parameters["STATUS"]->ServerType = ORATYPE_NUMBER;

// 結果を読みだす為のメソッドを実行
$Query = "BEGIN DBMS_OUTPUT.GET_LINE(:PARAM,:STATUS); END;";
$OraDatabase->ExecuteSQL( $Query );

// メモリ上のデータを読み出し
while( $OraDatabase->Parameters["STATUS"]->Value == 0 ) {
	print $OraDatabase->Parameters["PARAM"]->Value . "\n";
	$OraDatabase->ExecuteSQL( $Query );
}
		
// パラメータの削除
$OraDatabase->Parameters->Remove("STATUS");
$OraDatabase->Parameters->Remove("PARAM");


print "処理が終了しました";

?>


タグ:PL/SQL
posted by lightbox at 2010-06-13 14:34 | Oracle : PL/SQL | このブログの読者になる | 更新情報をチェックする

2010年05月19日


PL/SQL : プロシージャの呼び出し

単純な呼び出し先として、入力引数を二つ取るプロシージャです。

1) SQL としての呼び出し
CALL CSV_OUT('0006','0007')
2) 最も単純な PL/SQL ブロックとしての呼び出し
begin CSV_OUT('0006','0007'); end;
3) 一時 PL/SQL の一部として呼び出し
DECLARE 
	WK_FROM VARCHAR2(4) := '0005'; 
	WK_TO VARCHAR2(4) := '0015'; 
BEGIN 
	CSV_OUT(WK_FROM,WK_TO);
END; 
CREATE or REPLACE PROCEDURE CSV_OUT 
( 
	PM_STRING1 IN VARCHAR2 
	,PM_STRING2 IN VARCHAR2 
) 
 
/**********************************************************/ 
/* 変数の定義 */ 
/**********************************************************/ 
AS 
	WK_FILENAME VARCHAR2(100); 
	WK_CHARACTERSET VARCHAR2(100); 
	FILEHANDLE	UTL_FILE.FILE_TYPE; 
 
	CURSOR cur_main IS
		SELECT * FROM 社員マスタ
			where 社員コード 
			between PM_STRING1 and PM_STRING2
			order by フリガナ;
 
	COMMENT_REC cur_main%ROWTYPE;  
 
/**********************************************************/ 
/* 内部プロシージャ( データ出力 ) */ 
/**********************************************************/ 
PROCEDURE WRITE_DATA 
AS 
	WK_CSV VARCHAR2(200); 
BEGIN 
 
	/***********************************************/ 
	/* 1行ぶんのデータの作成 */ 
	/***********************************************/ 
	WK_CSV := COMMENT_REC.社員コード; 
	WK_CSV := WK_CSV || ',' || COMMENT_REC.氏名; 
 
	/***********************************************/ 
	/* AL32UTF8 は、10g Express 対応 */ 
	/***********************************************/ 
	if WK_CHARACTERSET = 'AL32UTF8' then 
		UTL_FILE.PUT_LINE( 
			FILEHANDLE,  
			CONVERT( WK_CSV,'JA16SJIS','AL32UTF8') 
		); 
	else 
		UTL_FILE.PUTF( FILEHANDLE, '%s\n', WK_CSV ); 
	end if; 
 
END; 
 
/**********************************************************/ 
/* 処理開始 */ 
/**********************************************************/ 
BEGIN 
	DBMS_OUTPUT.PUT_LINE('デバッグ:開始'); 
 
	/***********************************************/ 
	/* 行データを変数にセット */ 
	/***********************************************/ 
	SELECT VALUE INTO WK_CHARACTERSET from V$NLS_PARAMETERS 
		where PARAMETER = 'NLS_CHARACTERSET'; 
 
	/***********************************************/ 
	/* 出力ファイル名を設定 */ 
	/***********************************************/ 
	WK_FILENAME := '社員情報.csv'; 
	if WK_CHARACTERSET = 'AL32UTF8' then 
		WK_FILENAME := 
			CONVERT( WK_FILENAME,'JA16SJIS','AL32UTF8'); 
	end if; 
 
	OPEN cur_main; 
 
	FILEHANDLE := UTL_FILE.FOPEN( 'ORACLEDIR', WK_FILENAME, 'w' ); 
 
	LOOP 
		FETCH cur_main INTO COMMENT_REC; 
		EXIT when cur_main%NOTFOUND; 
 
		/* 内部プロシージャの呼び出し */ 
		WRITE_DATA; 
		DBMS_OUTPUT.PUT_LINE(COMMENT_REC.氏名); 
 
	END LOOP; 
 
	UTL_FILE.FCLOSE( FILEHANDLE ); 
 
	CLOSE cur_main; 
 
/**********************************************************/ 
/* 一番外側のブロックの例外処理 */ 
/**********************************************************/ 
EXCEPTION 
	WHEN OTHERS THEN 
		DBMS_OUTPUT.PUT_LINE('例外発生:'||SQLCODE||':'||SQLERRM); 
END;




タグ:PL/SQL Oracle SQL
posted by lightbox at 2010-05-19 21:46 | Oracle : PL/SQL | このブログの読者になる | 更新情報をチェックする

2009年05月12日


PL/SQL : Function 雛形

引数 : 所属コード
戻り値 : 引数で指定された所属部署に属する社員の数

※ count(*) を使用しているので、select into 用の EXCEPTION は必要無い。
※ 例外エラーの行が必要な場合は、1=1 を 1=0 にしてリビルドする
※ count(*) の結果は整数ですが、VARCHAR2 で代用できる
↓利用方法のサンプル
select 社員マスタ.*,test(所属) from 社員マスタ
CREATE or REPLACE FUNCTION TEST
(
	PM_STRING IN VARCHAR2	/* 引数の定義 */
)
RETURN VARCHAR2	/* 戻り値の定義 */

/**********************************************************/
/* 変数の定義 */
/**********************************************************/
AS
	RET_VALUE	VARCHAR2(2000);
	WK_VALUE	VARCHAR2(2000);

/**********************************************************/
/* 処理開始 */
/**********************************************************/
BEGIN
	DBMS_OUTPUT.PUT_LINE('デバッグ:開始');
	SELECT count(*) INTO 	RET_VALUE	FROM 社員マスタ
	WHERE
		所属 = PM_STRING;
	RETURN RET_VALUE;

/**********************************************************/
/* 一番外側のブロックの例外処理 */
/**********************************************************/
$if 1=1 $then
EXCEPTION
	WHEN OTHERS THEN
		RET_VALUE := '例外発生:'||SQLCODE||':'||SQLERRM;
		RETURN RET_VALUE;
$end
END;




タグ:PL/SQL 雛型
posted by lightbox at 2009-05-12 17:19 | Oracle : PL/SQL | このブログの読者になる | 更新情報をチェックする
Seesaa の各ページの表示について
Seesaa の 記事がたまに全く表示されない場合があります。その場合は、設定> 詳細設定> ブログ設定 で 最新の情報に更新の『実行ボタン』で記事やアーカイブが最新にビルドされます。

Seesaa のページで、アーカイブとタグページは要注意です。タグページはコンテンツが全く無い状態になりますし、アーカイブページも歯抜けページはコンテンツが存在しないのにページが表示されてしまいます。

また、カテゴリページもそういう意味では完全ではありません。『カテゴリID-番号』というフォーマットで表示されるページですが、実際存在するより大きな番号でも表示されてしまいます。

※ インデックスページのみ、実際の記事数を超えたページを指定しても最後のページが表示されるようです

対処としては、このようなヘルプ的な情報を固定でページの最後に表示するようにするといいでしょう。具体的には、メインの記事コンテンツの下に『自由形式』を追加し、アーカイブとカテゴリページでのみ表示するように設定し、コンテンツを用意するといいと思います。


※ エキスパートモードで表示しています

アーカイブとカテゴリページはこのように簡単に設定できますが、タグページは HTML 設定を直接変更して、以下の『タグページでのみ表示される内容』の記述方法で設定する必要があります

<% if:page_name eq 'archive' -%>
アーカイブページでのみ表示される内容
<% /if %>

<% if:page_name eq 'category' -%>
カテゴリページでのみ表示される内容
<% /if %>

<% if:page_name eq 'tag' -%>
タグページでのみ表示される内容
<% /if %>
この記述は、以下の場所で使用します
container 終わり



フリーフォントで簡単ロゴ作成
フリーフォントでボタン素材作成
フリーフォントで吹き出し画像作成
フリーフォントではんこ画像作成
ほぼ自由に利用できるフリーフォント
フリーフォントの書体見本とサンプル
画像を大きく見る為のウインドウを開くボタンの作成

CSS ドロップシャドウの参考デモ
イラストAC
ぱくたそ
写真素材 足成
フリーフォント一覧
utf8 文字ツール
右サイド 終わり
base 終わり