プログラミングと日々思ったことなど

ブログ名通りです。仕事でプログラミングをはじめました。

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)

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 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(連番)をつけるためなのですが、これはまだあやふやなので試す必要があります。




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

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

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

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

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

前回の続きからです。

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で、ストアドプロシージャを作成 その1

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

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に1件ずつデータを格納する
	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で再び1行データを取得する
		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);

	#このハンドラがおかしい?
        #61日...ハンドラは今回使用しません。
	/*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

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

キャストについて【C#】

昨日は、キャストが思いつかず悔しい思いをしました。
忘れないように、コードを残します。

問題のキャスト処理を書く前に、条件分岐の処理も復習します。

ublic void kakunin_Click(C1.WPF.DataGrid.DataGridEventArgs e)
{
	this.IsRegistEnable = true; //登録ボタンにバインドさせている。
	bool isError = false;
	string name = e.Column.Name;

	DataRowView dv = (DataRowView)e.Row.DataItem;
	dv.Row.ClearErrors();
	e.Row.Errors.Clear();

	DataGridRowError  dgreroor = new DataGridRowError();

	//必須入力チェック
	//sentakuメソッドで、DataGridの項目の選択か必須かを判断している。
	if(!this.sentaku(name))
	{
		//DtoCheckメソッドにて、エラーの出ているメソッドを判別。
		isError = this.DtoCheck(e.Column,dv.Row)
		if(isError)
		{
			dgreroor.ColumnNames.Add(name);
			dgreroor.Message="必須入力エラー";
			e.Row.Errors.Add(dgreroor);
			dv.Row.SetColumnError(name,"[項目:"+ name + "]を入力してください");
		}
	}

	//switchで、入力した値が項目に合う値なのか(数字なのか、半角英数なのかなど)判別。
	switch(name)
	{
		case"aa":
		case"bb":
		if(!InputAuxiliaryUtil.IsNumeric(dv[name].Tostring()))
		{
			dgreroor.ColumnNames.Add(name);
			dgreroor.Message = "入力エラー"
			e.Row.Errors.Add(dgreroor);
			dgreroor.Row.SetColumnError(name,"数字を入力してください");
		
			isError = true;
		}
		break;

		case"cc":
		if(....)
		{
			//略...
		}
		break;

	}

	//エラーがあった場合、登録ボタンを使えないようにする。
	if(isError)
	{
		e.Cansel = true;
		this.IsRegistEnable = false;

	}

}

SetColumnErrorというのが、DataGridの問題セルに出現するエラーです。(見た目はフキダシみたいなものです)
InputAuxiliaryUtil.IsNumericというメソッドで、判定させます。

#region 文字チェック

public static bool IsNumeric(string value)
{
	if(null == value)
	{
		return true;
	}
	if(string.IsNullOrEmpty(value.Trim()))
	{
		return true;
	}
	if(!Regex.IsMach(value.Trim(),REGEX_Numeric))
	{
		return false;
	}

	return true;
}

#endregion


また、この文字チェック内で使われているREGEX_Numericは、辿っていくと正規表現にたどり着きます。

//こんなの
//これは例なので、正規表現は半角英数字の判別設定です。

private static string REGEX_Numeric = "^[-_/0-9a-zA-Z]+$";

DataGridは表示されるだけなので、入力文字についてはこのように自分で書いていかなければならないのです。

次はキャストです。

else if(e.Column.Name == "ff")
{
	dv[name] = dv[name].Tostring().PadLeft(2,'0')
}

これです。
間違って書いてコードは以下になります。

//このコードは間違っています。
else if(e.Column.Name == "ff")
{
	var i = (DataRowView)e.Row.DataItem;
	i.PadLeft(7,'0')

	string col = e.Column.Tostring();
	col.PadLeft(7,'0')
}

iやcolには値が入っていますが、オブジェクトを知りません。
ただ値を入れているという処理をしているだけなので、DataGridには何も変化がない、です。

値が入っているプロパティを見つけ、キャストして使用できるようにするまでが難しいな・・・。