SQLの窓

2011年10月02日


PL/SQL : 最も簡単な CSV 出力 の記述

CSV イメージを最初から SQL で作成しておいて、取りだすデータが 1つの列に
しておく事で高速な処理が可能になります
続きを読む( ソースコード )
posted by lightbox at 2011-10-02 00:18 | Oracle : PL/SQL | このブログの読者になる | 更新情報をチェックする

2011年09月10日


Oracle : PL/SQL : 暗黙のカーソルとループブロックを使用した自動行挿入

この書き方の目的は、更新対象の行が存在しなかった場合に自動的に行を追加
して、集計更新の処理を継続する事です。

一回目の UPDATE でもし対象行が無ければ、暗黙のカーソルによって条件が設
定されるので、INSERT 文で初期レコードを作成してループ処理でもう一度 
UPDATE 文が実行されます。その場合は必ず対象行が存在するので、初回の集計
がなされた後、ループを抜ける事になります。

もし、既に行が存在する場合は UPDATE 後には即ループを抜けます
LOOP
	UPDATE 集計用テーブル SET
		"現物数量" = "現物数量" - PM_数量,
		"更新日" = sysdate
	WHERE
		"組織" = V組織
		AND "大分類" = V大分類
		AND "商品コード" = V商品コード;
	IF SQL%NOTFOUND THEN
		INSERT INTO 集計用テーブル
		 VALUES (V組織,V大分類,V商品コード,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
		,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
		,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
		,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
		,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
		,0,0,0,0,0,0,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,sysdate,NULL,sysdate,0,0
		,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
		,0,0,0);
	ELSE
		EXIT;
	END IF;
END LOOP;


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

Oracle : PL/SQL : SELECT 〜 INTO 構文の一般的な使用方法

SELECT 〜 INTO は、カーソルを定義せずにデータを読みだす便利な方法ですが、対象データが where 条件で発見できなかった場合は例外が発生するので注意が必要です。

必ず、BEGIN 〜 EXCEPTION 〜 END; でブロックを作成して起こりうる例外処理に備える必要があります。( NULL 文は何もしない処理です )

但し、起こりうる例外として NO_DATA_FOUND と TOO_MANY_ROWS が考えされますが、グループ用の記述をしている場合は、必ず行が返されてかつ1行の場合もあります。( 例 : GROUP BY の無い count関数 )
BEGIN
	SELECT
		締日
		,締日区分
		,支払日
	INTO
		WK_締日
		,WK_締日区分
		,WK_支払日
	FROM 得意先マスタ
	WHERE
		得意先コード = PM_TCODE;
EXCEPTION
	WHEN NO_DATA_FOUND THEN
		NULL;
	WHEN TOO_MANY_ROWS THEN
		NULL;
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('例外発生:'||SQLCODE||':'||SQLERRM);
END;



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

Oracle : PL/SQL : カーソルを使った一般的なループ処理を行うプロシージャ

カーソルは、PL/SQL 内で記述された SELECT 構文に割り当てられます。定義された
「呼び名」で OPEN、FETCH、CLOSE という一般的なファイルの I/O と同様の処理を
行います。

FETCH 時に行データがメモリに受け渡されますが、そのメモリは事前に定義しておく
必要があります。このサンプルでは最も簡単な例として、定義されたカーソルと同じ
フォーマットを持つレコード変数を定義しています

COMMENT_REC cur_main%ROWTYPE;

これ以外でも、列データを一つづつ FETCH の INTO 句に置く事もできます

カーソルでは、cur_main%NOTFOUND という書式( または cur_main%FOUND ) で、条件
として利用して FETCH 後の処理を記述する事が可能です。
CREATE OR REPLACE PROCEDURE LIST_MEMBER
(
	/* カーソルの範囲指定用  */
	PM_STRING IN VARCHAR2
)

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

	/* カーソルの定義(主に複数読み込み用)  */
	CURSOR cur_main IS
		SELECT * FROM 社員マスタ
		where 社員コード <= PM_STRING;

	/* cur_main に対応する社員マスタのフォーマット  */
	COMMENT_REC cur_main%ROWTYPE; 

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

	/* カーソルを開く */
	OPEN cur_main;

	/* ループ処理(ブロック) */
	LOOP 
		/* 定義したレコード変数に読み込み */
		FETCH cur_main INTO COMMENT_REC;
		/* カーソルに対するシステム変数でデータ終わりを判断 */
		if cur_main%NOTFOUND then 
			/* ループ処理を脱出 */
			EXIT; 
		end if; 

		/* デバッグの表示(実際は更新処理等を行う */
		DBMS_OUTPUT.PUT_LINE('結果:'||COMMENT_REC.氏名);
	 
	END LOOP; 

	/* カーソルを閉じる */
	CLOSE cur_main;

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

関連する記事

PL/SQL : プロシージャの呼び出し
Oracle : PL/SQL : Function のサンプルと使い方
PL/SQL : Function 雛形


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

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;




タグ:SQL Oracle PL/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 %>
この記述は、以下の場所で使用します


Windows
container 終わり

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

Android SDK ポケットリファレンス
改訂版 Webデザイナーのための jQuery入門
今すぐ使えるかんたん ホームページ HTML&CSS入門
CSS ドロップシャドウの参考デモ
Google Hosted Libraries
cdnjs
BUTTONS (CSS でボタン)
イラストAC
ぱくたそ
写真素材 足成
フリーフォント一覧
utf8 文字ツール
右サイド 終わり
base 終わり