SQLの窓

2018年05月28日


PL/SQL : Function と Procedure の簡単なサンプル

SELECT 〜 INTO を使用して、社員コードを渡して氏名を取得する

Function : GET_CNAME


簡易 PL/SQL ビルダーのダウンロード(【接続/ダイアログ・ビルド/アイコンクリック・実行/アイコンクリック】 しかできません)

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;


カーソルを使用してデータを読み込んで、csv データをディレクトリオブジェクト経由でディスクにファイルを出力する

Procedure : LIST_MEMBER


PROCEDURE LIST_MEMBER
(
	/* カーソルの範囲指定用  */
	PM_STRING IN VARCHAR2
)

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

	/* カーソルの定義(主に複数読み込み用)  */
	CURSOR cur_main IS
		SELECT
			社員コード||','||
			氏名||','||
			フリガナ||','||
			所属||','||
			性別||','||
			作成日||','||
			更新日||','||
			給与||','||
			手当||','||
			管理者||','||
			生年月日
			from "社員マスタ"
		where 社員コード <= PM_STRING;

	-- ディスクへ書き込むファイル名
	WK_FILENAME VARCHAR2(100) := 'syain.csv';
	-- ファイルにアクセスする為のハンドルの定義
	FILEHANDLE UTL_FILE.FILE_TYPE;

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

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

	-- 第一引数は、ディレクトリオブジェクトの名称( 但し文字列 )
	FILEHANDLE := UTL_FILE.FOPEN( 'LOGDIR', WK_FILENAME, 'w' );

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

		-- 書き込み
		UTL_FILE.PUT_LINE(FILEHANDLE,WK_VALUE);
	 
	END LOOP; 

	-- ファイルを閉じる
	UTL_FILE.FCLOSE( FILEHANDLE );

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

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

データ作成

mdb よりデータを作成する スクリプト(32ビット用/cscript.exe で呼び出せば 64ビット)のダウンロード

26、27、29 行目を環境に合わせて変更

※  ディレクトリオブジェクトの作成

関連する記事

Oracle : PL/SQL Function の使用方法

Oracle 11g 学習環境の作成 : SQLPlus の環境作成
Oracle SQLPlus でスキーマの作成




posted by lightbox at 2018-05-28 12:08 | Oracle : PL/SQL | このブログの読者になる | 更新情報をチェックする

Oracle : PL/SQL Function の使用方法

Function オブジェクトは Procedure オブジェクトと違い、select 文の列名リストの中で使用できるので簡単に結果を体験できる有用なオブジェクトです。

サンプル : MF
CREATE OR REPLACE FUNCTION MF
(
	PM_STRING IN NUMBER	/* 引数の定義 */
)
RETURN VARCHAR2	/* 戻り値の定義 */

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

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

	if PM_STRING = 0 then
		RET_VALUE := '男';
	else
		RET_VALUE := '女';
	end if;
	RETURN RET_VALUE;

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

SQL の窓 build c++ で作成できます


確認用 SQL 文
select 社員コード
	,氏名
	,フリガナ
	,所属
	,性別
	,mf(性別) as 性別名
	,作成日
	,更新日
	,給与
	,手当
	,管理者
	,生年月日 
 from 社員マスタ



DBMS_OUTPUT の結果を表示する為には、SQLPlus を使用します。
( Oracle 11g 学習環境の作成 : SQLPlus の環境作成 )

SET SERVEROUTPUT ON の設定により、select 文を実行しただけで、DBMS_OUTPUT の内容が表示されますが、Call 文を使用して、単独でテストすることが可能です。
SQL> variable str VARCHAR2(10);
SQL> call mf(0) into :str;
デバッグ:開始
デバッグ:引数:0

コールが完了しました。

SQL> print str

STR
----------------------------------------------------------------
男

▼ ソースコードは以下の SQL で取得可能です。
select * from USER_SOURCE where NAME = 'MF' order by LINE
データ作成 mdb よりデータを作成する スクリプト(32ビット用/cscript.exe で呼び出せば 64ビット)のダウンロード 26、27、29 行目を環境に合わせて変更 ※ ディレクトリオブジェクトの作成 関連する記事 PL/SQL : Function と Procedure の簡単なサンプル Oracle 11g 学習環境の作成 : SQLPlus の環境作成 Oracle SQLPlus でスキーマの作成
posted by lightbox at 2018-05-28 11:28 | Oracle : PL/SQL | このブログの読者になる | 更新情報をチェックする

2011年10月02日


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

CSV イメージを最初から SQL で作成しておいて、取りだすデータが 1つの列にしておく事で高速な処理が可能になります
CREATE OR REPLACE PROCEDURE LIST_MEMBER
(
	/* カーソルの範囲指定用  */
	PM_STRING IN VARCHAR2
)

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

	/* カーソルの定義(主に複数読み込み用)  */
	CURSOR cur_main IS
		SELECT
			社員コード||','||
			氏名||','||
			フリガナ||','||
			所属||','||
			性別||','||
			作成日||','||
			更新日||','||
			給与||','||
			手当||','||
			管理者||','||
			生年月日
			from "社員マスタ"
		where 社員コード <= PM_STRING;

	-- ディスクへ書き込むファイル名
	WK_FILENAME VARCHAR2(100) := 'syain.csv';
	-- ファイルにアクセスする為のハンドルの定義
	FILEHANDLE UTL_FILE.FILE_TYPE;

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

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

	-- 第一引数は、ディレクトリオブジェクトの名称( 但し文字列 )
	FILEHANDLE := UTL_FILE.FOPEN( 'MYDIR', WK_FILENAME, 'w' );

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

		-- 書き込み
		UTL_FILE.PUT_LINE(FILEHANDLE,WK_VALUE);
	 
	END LOOP; 

	-- ファイルを閉じる
	UTL_FILE.FCLOSE( FILEHANDLE );

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

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




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 | このブログの読者になる | 更新情報をチェックする
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 ドロップシャドウの参考デモ
BUTTONS (CSS でボタン)
イラストAC
ぱくたそ
写真素材 足成
フリーフォント一覧
utf8 文字ツール
右サイド 終わり
base 終わり