日付について色々【VB.NET】

前回の記事から時間が経ってしまいました。
今回は、VB.NETでの日付変換についてです。

'和暦を日付変換することによって、正確な日付が入力されているのか確認する。
If Not DateTime.TryParse(cmb_年号.Text +
						 txt_年.Text + "年" +
						 txt_月.Text + "月" +
						 txt_日.Text + "日",chkDate) Then
	
	Call MsgBox("作成を入力してください",Me)
	cmb_年号.Focus()
	Return False

End If

chkDateの中には、多分こんなものの一部が入っています。
0730...chkDateは、何時何分何秒が入っているだけでした。このように和暦でも、TryParseはできるそうです。ただ、新しい和暦になるとエラーになってしまうかもしれないとのこと。（先輩談）

次は西暦から和暦の変換です。

Public Function chg_西和(ByVal str日付 As String,ByVal intフォーマット As Integer) As String

'基準日の年・月・日を取得
Dim BaseY As Integer
Dim BaseM As Integer
Dim BaseD As Integer

'ループに使う変数
Dim cnt As Integer
Dim 元号 As Integer = UBond(元号一覧) '元号一覧の中身も後で確認

'6桁なら（年月のみ）8桁にする
If str日付.Length = 6 Then
	str日付 = str日付 + "01"
ElseIf str日付 = "0" Then
	Return ""
End If

BaseY = Integer.Parse(Mid(str日付,1,4))
BaseM = Integer.Parse(Mid(str日付,5,2))
BaseD = Integer.Parse(Mid(str日付,7,2))

'戻り値初期化
 chg_西和 = ""

'元号の数だけループ
For cnt = 0 To 元号 Step 1

	With 元号一覧(Cnt)
	'取得した基準日と、DBにある元号開始日・終了日を比べる
	'開始日より基準日が大きい、かつ、基準日が終了日より小さい場合
	If(Integer.Parse(.getKaisi) <= Integer.Parse(str日付) And _
	   Integer.Parse(str日付) <= Integer.Parse(.getSyuryo)) Then

	   If intフォーマット = 0 Then
	   '平成xx年xx月xx日と返す
	   chg_西和 = .getGengoNm & _
	   			 String.Format("{0:D2}",BaseY - _
	   			 				(Integer.Parse(Mid(Trim(.getKaisi),1,4))-1)) &"年"& _
	   			 String.Format("{0:D2}",BaseM) &"月"& _
	   			 String.Format("{0:D2}",BaseD) &"日"&


	   Else If intフォーマット = 1 Then
	   '以下、戻す値の形が変更される処理が続く

	End If

Exit For

Else If (Integer.Parse(.getKaisi) > str日付 Or _
	   str日付 < Integer.Parse(str日付) >= Integer.Parse(.getSyuryo)) Then

	   MsgBox.Show("基準日が正しく設定されていません")
	   'For文を抜ける
	   Exit For
End If

End With
Next

End Function

SQLでの抽出方法 【IN】【ROW_NUMBER】【副問い合わせ】

①IN

SELECT * FROM test_table
WHERE aa IN (SELECT aa FROM (SELECT aa FROM test_table
			GROUP BY aa,bb,cc,dd HAVING COUNT(*) >= 2) AS X)

--6.28 これでもできた。
SELECT * FROM test_table
WHERE aa IN (SELECT aa FROM test_table
			GROUP BY aa,bb,cc,dd HAVING COUNT(*) >= 2)

IN句には複数の条件を記述することができます。
このsql文では、副問い合わせを行っている・・・のですが、副問い合わせの中で同じテーブルを使用するとエラーが出てしまうので、ASで別名をつけています。

書いていて思い出しました。
そのエラーが実際出るのかを試していません。
明日会社でやってみよう。

②ROW_NUMBER

SELECT aa,bb,cc,dd
      ,ROW_NUMBER() OVER(PARTITION BY aa,bb,cc,dd ORDER BY aa) AS 連番
FROM test_table

ROW_NUMBERは検索結果に順番をつけることができます。
ROW_NUMBER関数内にある、PARTITION BY でグループ化ができます。

③副問い合わせ

DELETE test_table FROM test_table AS C
INNER JOIN 
	(
	SELECT aa,bb,cc,dd
	,ROW_NUMBER() OVER(PARTITION BY aa,bb,cc,dd ORDER BY aa) AS 連番
		FROM test_table
	) AS A
	ON C.aa = A.aa
	AND C.bb = A.bb
	AND C.cc = A.cc
WHERE 連番 <> 1

副問い合わせは、SELECT文による問い合わせを入れ子にすることです。
③はINNER JOINの中で副問い合わせを行っています。
同じテーブルで、JOINさせているのはROW_NUMBER（連番）をつけるためなのですが、これはまだあやふやなので試す必要があります。

6.28
なんとなくわかってきたような・・・

上司が先輩におっしゃっていたことなのですが、自分も覚えておかなければならないと思ったので書き残しておきます。

自分の行ったこと（書いたプログラムとか）が、今後他のところでどんな影響を受けるのか考えなさい。

自分が作業した後のことまで考えるのは難しいです。
これから少しずつできるようになればいいな・・・。

SqlServerで、ストアドプロシージャを作成 その２

明日記事を書くと言っていたのに、遅れてしまいました。
反省。

前回の続きからです。

IF @header = 1
BEGIN
-- BCPコマンド出力設定をする
-- 半角スペースある/なしで正常に実行されないので注意する
SET @BCP_str = ''
SET @BCP_str = 'bcp "' + @SQL_heder_edit +'"'
SET @BCP_str = @BCP_str + ' queryout "' + @out_folder + ' Header.csv" '
SET @BCP_str = @BCP_str + '-c -t"," '
SET @BCP_str = @BCP_str + ' -S' + @S_name + ' '
SET @BCP_str = @BCP_str + '-d ' + ' test' + ' '
SET @BCP_str = @BCP_str + '-U ' + @U_id + ' '
SET @BCP_str = @BCP_str + '-P ' + @pass + ' ;'

-- CSVファイル実行
-- xp_cmdshell は、OS のコマンドを実行して出力をテキストの行として返す
EXECUTE @result = master.dbo.xp_cmdshell @BCP_str

IF @result = 1
BEGIN
	SET @R_Msg = 'ヘッダー作成失敗'
	RETURN @R_value
END

END

-- BCPの詳細ファイル出力
SET @BCP_str = ''
SET @BCP_str = 'bcp "' + @SQL +'"'
SET @BCP_str = @BCP_str + ' queryout "' + @out_folder + ' Detail.csv" '
SET @BCP_str = @BCP_str + '-c -t"," '
SET @BCP_str = @BCP_str + ' -S' + @S_name + ' '
SET @BCP_str = @BCP_str + '-d ' + ' test' + ' '
SET @BCP_str = @BCP_str + '-U ' + @U_id + ' '
SET @BCP_str = @BCP_str + '-P ' + @pass + ' ;'

EXECUTE @result = master.dbo.xp_cmdshell @BCP_str

IF @result = 1
BEGIN
	SET @R_Msg = '詳細ファイル作成失敗'
	RETURN @R_value
END

IF @header = 1
BEGIN
-- ヘッダーと詳細のファイル結合
SET @File_copy = ''
SET @File_copy = @File_copy + 'copy /B'
SET @File_copy = @File_copy + @out_folder + ' Header.csv'
SET @File_copy = @File_copy + ' +'
SET @File_copy = @File_copy + @out_folder + ' Detail.csv'
SET @File_copy = @File_copy + ' '
SET @File_copy = @File_copy + @out_folder + file_name
-- CSVファイル結合実行
EXECUTE @result = master.dbo.xp_cmdshell @File_copy
END

IF @header = 0
BEGIN
SET @File_copy = ''
SET @File_copy = @File_copy + 'copy /B'
SET @File_copy = @File_copy + @out_folder + ' Detail.csv'
SET @File_copy = @File_copy + ' '
SET @File_copy = @File_copy + @out_folder + file_name
EXECUTE @result = master.dbo.xp_cmdshell @File_copy
END

IF @header = 1
BEGIN
-- ヘッダー消去
SET @File_Del = ''
SET @File_Del + ' del /Q'
SET @File_Del + @out_folder + ' Header.csv'
EXECUTE master.dbo.xp_cmdshell @File_Del
END

-- 詳細ファイル消去
SET @File_Del = ''
SET @File_Del + ' del /Q'
SET @File_Del + @out_folder + ' Detail.csv'
EXECUTE master.dbo.xp_cmdshell @File_Del

END
-- 例外エラー
END TRY
BEGIN CATCH

RETURN @R_value	-- -1(失敗の値)が代入されている

END CATCH

SET @R_Msg = CAST(@Cnt AS VARCHAR) + '件を書き込みました。'
SET @R_value = 0 --成功の値(戻り値)
RETURN @R_value

END
GO

変数にどんどん代入していって、実行させるための文字列を作成するのが、難しかったです。

仕事を始めて数ヶ月、少しずつ慣れてきました。
まだ沢山学ばねばならないのですが、そろそろキーボードだけでパソコンを動かす方法（ショートカットキーだったかな）も覚えていこうかな。

SqlServerで、ストアドプロシージャを作成 その１

再びストアドを作成したので、２回に渡って復習します。
今回は、カーソル作成までです。

CREATE PROCEDURE SP_ABC (
		@file_name VARCHAR(50)
		,@table_name VARCHAR(50)
		,@out_folder VARCHAR(20) -- 出力先フォルダ（フルパス）
		,@S_name VARCHAR(30)
		,@U_id VARCHAR(20)
		,@pass VARCHAR(20)
		,@header CHAR(1)
		,@R_Msg VARCHAR(1000) OUTPUT	-- 返却メッセージ
)

AS

	DECLARE @hed_str NVARCHAR(3000)	
	-- ↑カーソルからfetchしてきた情報をカンマ区切りで格納
	DECLARE @hed_get VARCHAR(50)
	-- ↑列のヘッダーを格納
	DECLARE @SQL NVARCHAR(1000) -- SQL...は、SQL文を格納
	DECLARE @SQL_header NVARCHAR(1000)
	DECLARE @SQL_cursor NVARCHAR(1000)
	DECLARE @SQL_heder_edit NVARCHAR(1000)
	DECLARE @BCP_str VARCHAR(3000)
	DECLARE @File_copy VARCHAR(3000)
	DECLARE @File_Del VARCHAR(3000)
	DECLARE @R_value int --戻り値
	DECLARE @D_Msg VARCHAR(100)
	DECLARE @result int
	DECLARE @Cnt INTEGER

BEGIN

	SET @D_Msg = 'ストアドエラー発生'
	SET @R_value = -1

	BEGIN TRY

	-- テーブルデータ存在確認
	SET @SQL = N' SELECT * FROM ' + @table_name
	EXECUTE sp_executesql @SQL 	--SQL実行

	SET @Cnt = @@ROWCOUNT
	IF @Cnt = 0
	BEGIN
	SET @R_Msg = 'データなし' + @table_name
	RETURN @R_value
	END

	-- データ抽出SQL作成
	IF @header = 1
	BEGIN
	SET @SQL_header = N' SELECT d.name FROM Sys.tables AS t
					LEFT OUTER JOIN sys.columns AS c
					ON t.id = c.id
					WHRER t.name = ''' + @table_name + ''''
					+ N' ORDER BY c.column_nm '
	-- ↑ここがわかりづらい
　　 -- テーブルの名前を条件にして、カラムとテーブルを結合
	-- Sys.tablesというのはカタログビューと言う。
	-- カタログビューは、SqlServerデータベースエンジンによって使用される情報を返す。
	EXECUTE sp_executesql @SQL_header

	SET @SQL_cursor = N' DECLARE aa CURSOR FOR ' + @SQL_header
　　 -- ↑先ほど外部結合させるSQL文をカーソルの条件にしている。
	EXECUTE sp_executesql @SQL_cursor
	
	OPEN aa
	FETCH NEXT FROM aa INTO @hed_get
	
	IF @@FETCH_STATUS = 0
	BEGIN
	 SET @hed_str += @hed_get
	END

	WHILE @@FETCH_STATUS = 0
	BEGIN
	 FETCH NEXT FROM aa INTO @hed_get
	 SET @hed_str += ',' + @hed_get
	END

	CLOSE aa
	DECLARE aa

	SET @SQL_heder_edit = N'SELECT ' + '''' + @hed_str + ''''
	END
	

以上で、カンマ区切りのSELECT文を作成することができました。
これを、csvに出力させるためにbcpユーティリティを使っていきます。

続きはまた明日。

ストアドプロシージャからストアドプロシージャを呼び出す【SqlServer】

昨日は念願の（！）カーソルのあるストアドプロシージャの作成を行いました。
まだ未完成ですが、復習でコードを載せます。

USE testdb
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE testpro(
		@aaa VARCHAR(20)
		)
AS
	DECLARE @returnval int --戻り値
	DECLARE @SQL NVARCHAR(4000)
	DECLARE @lMsg(4000)

	--カーソルデータ格納変数
	DECLARE @kakunou VARCHAR(1000)

BEGIN
	BEGIN TRY
	SET @returnval = 1

	SET @lMsg = @aaa + 'のデータ移行を開始しました。'
	EXECUTE testbl '案内','',@aaa,0,@lMsg

	IF (@aaa IS NULL OR @aaa = '')
	BEGIN
		SET @lMsg = '引数が未設定'
		EXECUTE testbl 'エラー','',@aaa,1,@lMsg
		RETURN @returnval
	END

	--カーソル作成
	SET @SQL = 'DECLARE abc CUSOR FOR SELECT tosi From'
				+'(SELECT name From' + @aaa + ' .table) AS T'

	EXECUTE sp_executesql @SQL

	--カーソルオープン
	OPEN abc
	--abcから変数@kakunouに１件ずつデータを格納する
	FETCH NEXT FROM abc INTO @kakunou

	IF @@FETCH_STATUS <> 0
	BEGIN
		SET @lMsg ='テーブルが存在しません'
		EXECUTE testbl 'エラー','',@aaa,1,@lMsg
		RETURN @returnval
	END

	While @@FETCH_STATUS = 0
	BEGIN
		EXECUTE abcInsertpro @aaa,@kakunou
　　　　#処理の後、FETCHで再び１行データを取得する
		FETCH NEXT From abc INTO @kakunou
	END

　　CLOSE abc
       DEALLOCATE abc

        SET @lMsg = @aaa + 'のデータ移行を完了しました。'
	EXECUTE testbl '案内','',@aaa,0,@lMsg


	END TRY

	--例外エラー
	BEGIN CATCH
		SET @lMsg = '[ERROR_NUMBER]' + @SQL
		EXECUTE testbl 'エラー','',@aaa,1,@lMsg
		RETURN @returnval
	END CATCH
	SET returnval = 0
END

GO

@@FETCH_STATUSの設定がおかしいかな？
自分でもわかるように、土日で説明を書くつもりです。

...6月4日追加
データ以降が完了したことを判断する処理は、書かなくても良い(正常に処理されれば、下に流れていくため)

ここから雑記

金曜日に、映画「美しい星」という三島由紀夫原作のSF映画を見ました。
人間は人間自身を自然の一部だと思っていない...だったかな？
個人的にとても面白かったです。

MySqlでストアドプロシージャを作成する

MySqlではSqlServerで使えていたRETURNが使えないので、HANDLERを使用する...みたいです。
未完成なのですが、忘れないようにメモ。

.....6月1日
HANDLERでは、希望の処理ができませんでした。

# workbenchで作成しているので、DELIMITERは省略して良い

CREATE PROCEDURE SP_MGR_SetLog
(
		IN aa VARCHAR(3)
		IN bb VARCHAR(10)
		OUT returnmsg VARCHAR(2000)
　　　      OUT returnval int;
)
BEGIN

	DECLARE ssql VARCHAR(4000);

	#このハンドラがおかしい？
        #6月1日...ハンドラは今回使用しません。
	/*DECLARE con1 CONDITION for 1;
	DECLARE EXIT HANDLER for con1
	BEGIN
		SET returnmsg = '引数エラー';
	END;

	#例外エラー（インサート失敗した時）
	DECLARE EXIT HANDLER for SQLEXCEPTION
	BEGIN
		SET returnmsg = 'インサート失敗';
	END;*/

	IF (aa IS NULL) OR (aa = '')
	THEN
                #エラーを検知するのはここで行なっている
                #が、条件のIF文が以下に続くので、エラーは書き換えられてしまう
		SET returnmsg = '必須項目が未設定';
                SET returnval = 0;
	END IF;

	IF NOT bb ='おはよう'　OR bb='こんにちは'
	THEN
		SET returnmsg = '項目の値が間違ってます。';
                SET returnval = 0;
	END IF;

	＃その後、処理が多々...省略

        #INSERTはこのように準備してから実行させる。
	PREPARE ssql From 'INSERT INTO aa VALUES(?,?);'
		SET @a = a;
		SET @b = b;
	EXECUTE ssql USING @a,@b;

END;

INSERT文でコーテーションを考えなくてもよかったので、とても楽になりました。
このprepareは、インジェクションの考え方と同じ（これはよくわからないので、調べる）だそうです。

また、MySqlでは文字列と認識されると緑色になります。
・・SqlServerでは、文字列が赤色で、変数が緑色になっていたような・・これも確認します。

少しずつわかってきました。
今度はカーソルのあるストアドを作りたいなー。
楽しみです。

SqlServerでストアドプロシージャを作成する

ストアドプロシージャを作成したので、復習です。
このストアドプロシージャはインサート処理と、エラー表示を行います（多分・・・）。

USE [AA_DB]
GO

CREATE PROCEDURE [aa]
	(@log VARCHAR(5)				--パラメータを宣言
	,@name VARCHAR(30)
	,@suuzi  int				
	,@MsgReturn VARCHAR(2000) OUTPUT
	)

AS
	DECLARE @SQL NVARCHAR(4000)		--変数を宣言
	DECLARE @DefMsg VARCHAR(1000)
	DECLARE @ReturnVal int
	DECLARE @Err INTEGER

BEGIN
	SET @DefMsg='ストアドエラー'
	SET @ReturnVal = 2                     --失敗したら2がかえってくる

	/** 引数チェック **/
	IF(@log IS NULL OR @log = '' OR
		@name IS NULL OR @name = '' OR
		)
	BEGIN
		SET @MsgReturn = @DefMsg + '引数未設定'
		RETURN @ReturnVal
	END

	BEGIN TRY

	SET @SQL = N'INSERT INTO [AA] VALUES
		('''+ @log + ''''			--引数を文字列として認識するための、コーテーション
		+N',''' + @name +''''	--NはUnicodeとして処理させるためにつける
		+N',' + CAST(@suuizi AS VARCHAR)+N''')' --数値を文字列と認識させるために、キャストする。
	EXECUTE @Err = sp_executeaql @SQL --@Errに、インサートが失敗したのか成功したのかわかる値が入っている
	IF @Err <> 0
	BEGIN
		SET @MsgReturn = @DefMsg + 'インサート失敗'
		RETURN @ReturnVal
	END

	SET @ReturnVal = 0

	END TRY
	BEGIN CAHTCH
		SET @MsgReturn = @DefMsg + '[ERROR_NUMBER]'+@SQL
		RETURN @ReturnVal
	END CAHTCH
END

GO

変数とパラメータが、少しだけ混乱する時があるので気をつけます。