｛
｝I言語勉強2：SQL
1. リレーショナルデータベースは専用の言語のSQLを使ってデータの定義と操作をします。
2. リレーショナルデータベースはデータを一番簡単な構造のテーブルと呼ばれる２次元の表で持ちます。縦が列（カラム、フィールド）でデータの名前を持ち、横が行（ロー、レコード）と呼ばれ、一件づつのデータと成ります。
| 名前 | 性別 | 生年月日 |
|---|---|---|
| 山田太郎 | 男 | 1948-11-01 |
| 山田花子 | 女 | 1952-04-01 |
3. テーブルには６種類の正規形が有りますが、実際のデータは２次元の表で表せるような単純な構造ではないので、正しい構造にするのではなく、あくまでも矛盾が起きにくい構造にする為ですので、プログラム開発中に矛盾が起きた時点で変更が加わるのが常です。
4. ６種類の正規形を忠実に実行するのも良いですが、開発してみないと矛盾が見つからないので、次の事を基本にテーブルを作ります。
4.1 １個のテーブルは基本的には同時に発生するデータを入れます。
4.2 列には繰り返しデータは持たないので、例えば、家族データをテーブルにしたい場合、生年月日は構成人数分必要ですので、１家族１行では無く、構成人数分の行で持ち、家族を区別出来る列を設けます。（第１正規形）
| 世帯主 | 世帯主枝番 | 名前 | 性別 | 生年月日 |
|---|---|---|---|---|
| 山田太郎 | 1 | 山田太郎 | 男 | 1948-11-01 |
| 山田太郎 | 1 | 山田花子 | 女 | 1952-04-01 |
（家族は世帯主と世帯主枝番が同じ行が１家族と成ります、尚、家族IDで区別出来る列を新規に設け、家族を分ける場合も有ります）
4.3 主キーと呼ばれる他の行と区別出来る列又は複数の列の集合を探し、主キーが決まれば値が決まる列を含めて１個のテーブルとします。（第2正規形以降）
4.4 他の列から導き出せる物は、他の列が変更された時点で矛盾が起きてしまうので、極力持たないようにします。ただし、処理効率上あえて持つ事も有ります。
5. リレーショナルデータベースはRDBと表現する事があります。
6. RDBを実現するソフトをRDBMS(リレーショナルデータベースマネージメントシステム）と言います。
１．SQLはJIS規格にもなっており、基本部分はどのRDBMSでも共通ですが、拡張部分は方言のような異なる文法の部分も多いので注意が必要です。
◎I言語では、拡張部分をI言語の文法でカバーし、極力同じ書き方でどのRDBMSでも動くようにしています。
2. 定義は物に対する定義とそれ以外が有ります。
2.1 定義する物はテーブルが主ですが主な物が下記です、createで作り、alterで変更し、dropで消すSQL文が有ります。
2.1.1 table---データを記憶する物です。
2.1.2 index---データの検索を早くしたり、ユニークな値以外を持てない制約を定義出来ます。
2.1.3 view---仮想的なテーブルを定義します。（プログラムの解析が難しくなるので、極力使わない事を推奨します、I言語側では使っています）
★黄色は、RDBMS毎に文法が異なるので、必要な物は可能な限りI言語側の支援ソフトで対応する事で、使わなくても良いようにしたので、極力使わない事を推奨している物です。このような物が存在する事は記憶に留めておいて下さい。
2.1.4 function---ユーザー関数を定義します。（プログラムの解析が難しくなるので、極力使わない事を推奨します、I言語側では使っています）
2.1.5 procedure---RDBMS固有のプログラムを定義します。（RDBMS毎に文法が大きく異なるので、極力使わない事を推奨します、希にcousorとfetchを使って一行づつ処理したい場合、処理スピードを保証する為使う場合が有りいます）
2.1.6 trigger---テーブルが更新された事を引き金に実行する処理を定義します。（システムの解析が難しくなるので、極力使わない事を推奨します）
2.1.7 sequence---連続した数値を発生する物を定義します。（プログラムの解析が難しくなるので、極力使わない事を推奨します）
2.1.8 synonym---物に別名を定義します。（プログラムの解析が難しくなるので、極力使わない事を推奨します）
2.1.9 user---ユーザーを定義します。（I言語では運用支援プログラムが定義します、RDBMSへの接続はどれかのユーザーを指定して接続します）
2.1.10 schema---スキーマを定義します（SQL Serverの場合dboスキーマが設定されており、schemaを増やすと管理が大変ですので、極力使わない事を推奨します、テーブル等は必ずどれかのスキーマに所属します）
2.1.11 database---データベースを定義します。（I言語では運用支援プログラムが定義します）
2.2 物以外の定義です。
2.2.1 grant---ユーザーに権限を付与します。（I言語では開発支援プログラムが使います、SELECT,INSERT,UPDATE,DELETEの権限を個別に設定出来ます）
2.2.2 revoke---ユーザーの権限を削除します。（I言語では開発支援プログラムが使います）
2.2.3 begin---トランザクションを開始します。（トランザクションはcommitかrollbackが実行れるまでテーブル更新を保留します）
2.2.4 commit---トランザクション開始後の、更新を実行します。
2.2.5 rollback---トランザクション開始後の、更新を取り消します。
3. 操作するSQLは主に下記の４個です。
3.1 select---データを検索します。
□ select [distinct] 式... [from テーブル名 [別名] [結合 テーブル名 [別名][on 条件]].....] [where 条件] [group by 式...] [having 条件] [order by 式 [desc]...]
★[]内は省略可能の意味です。
★...はカンマ区切で複数個書ける意味です。
★.....は空白区切で複数個書ける意味です。
3.2 insert---データを登録します。
□ insert into テーブル名 [(列名...)] values(式...)
□ insert into テーブル名 [(列名...)] select文
3.3 update---データを変更します。
3.3.1 テーブルが１個の場合。
□ update テーブル名 set 列名=式... [where 条件]
3.3.2 テーブルが２個の場合。（RDBMS毎に文法が異なります）
□ update O set 列名=式... from テーブル名1 O,from テーブル名2 I where 条件
3.3.2.1 PostgreSQLの場合 （参考）
□ update テーブル名1 O set 列名=式... from テーブル名2 I where 条件
3.3.2.2 Oracle,DB2の場合 （参考）
□update テーブル名1 O set (列名...)=(select 式... from テーブル名2 I where 条件) where exists(select 0 from テーブル名2 I where 条件)
3.3.2.3 MariaDBの場合 （参考）
□update テーブル名1 O set 列名=(select 式 from テーブル名2 I where 条件)... where exists(select 0 from テーブル名2 I where 条件)
3.3.2.4 I言語の場合（参考）
□=UPDATE{テーブル名1,列名...}{式...}{テーブル名2 I WHERE 条件};
3.4 delete---データを削除します。
□ delete from テーブル名 [where 条件](I言語では削除はupdateを使って論理削除で対応しています、運用支援プログラムで使っています）
1. 左下の「START」をクリックします。
2.「Microsoft SQL Server Tools 17」をクリックします、
3.「Microsoft SQL Server Management Studio 17」をクリックします、
4.「サーバーへの接続」でサーバー名が「コンピュータ名￥SQLEXPRESS」で有る事を確認し「接続」をクリックします。
（認証はWindows認証です、これはWindowsにログインしているログインIDでSQL Serverに接続する意味で、SQL ServerをインストールしたユーザーIDはSQL Serverの管理者権限が与えられており、SQL Serverに接続出来るように成っています）
5.「新しいクエリ」をクリックします。
6.新しく表示された画面上でSQLを実行します。
drop table t_a
★青い部分がSQLです、コピーと貼り付けでSSMSにコピーして「実行」をクリックすれば実行します。
１．実行するとメッセージに「テーブル 't_a' を 削除 できません。存在しないか、権限がありません。」とテーブルが無いのでエラーが出ます。
drop table t_a
go
create table t_a(c_a char(6) default ' ' not null,c_b nchar(6) default ' ' not null,c_c decimal(5,2) default 0 not null,primary key(c_a,c_b))
1.SSMSでは複数のSQLを書けますが、SQLの区切りには「go」を書くルールになっています。
2.charは半角文字コード用のデータ型で、6は文字数です。（ただし、全角文字も登録出来ます）
3.ncharは全角文字コード用のデータ型です。（ただし、半角文字も登録出来ます）
4.deimalは数値用のデータ型です、5は有効桁数で、2は小数部桁数です。
5.char,nchar,decimal以外にもデータ型が有りますが、基本は上記３個とdate型とdatetime型です、その他は、charの固定長に対しvarcharの可変長、ncharの固定長に対しnvarcharの可変長で、小数点無しで有効桁数８桁以内であればint、小さな値から大きな値まで持てるが計算結果は近似値で良い物にfloatが有ります。
6.primary keyは主キーの列名を指定します。
7.実行でメッセージにdropのエラーが出ますが、再度実行で、テーブルが作られており、テーブルを消して作っているのでメッセージに「コマンドは正常に完了しました。」が出ます。
◎列名は省略できますが、プログラム内では、列名の数が変わるとエラーと成ってしますので、列名を必ず書くようにします。
insert into t_a (c_a,c_b,c_c) values('abcd','efgh',1)
go
insert into t_a (c_a,c_b,c_c) values('abcd','ijkl',-2.6)
go
insert into t_a (c_a,c_b,c_c) values('mnop','qrst',3.4)
go
insert into t_a (c_a,c_b,c_c) values('abcd','efgh',4)
1.charとncharの内容は引用符で囲みます。
2.実行でメッセージに「PRIMARY KEY 違反」が出ており、最後のデータのPRIMARY KEYが同じ物は登録出来ない事が分かります。
★式は列名や値やそれ以外の値を行単位に１個又は０個返す物が書けます。
◎最初の式は*を使って全列の指定が出来ますが、プログラム内では、列の数が変わると検索結果が変わってしまうので、*は問題が出ない場合以外は使いません。
select c_a,c_b,c_c from t_a order by c_a,c_b
1.実行で結果に3件の行が表示されます。
2.orderを書かないと順不同で表示されます。
3.SQL Serverの場合テーブルが必要無い時は「from テーブル名」を省略する事も出来ます。
◎SQLは今までのプログラムのように１件づつのデータを順次処理する方法ではなく、全てのデータを対象に処理されます,
尚、今までのように１件づつ処理する方法もcursorとfetchを使えば可能ですが、出番は殆ど無いです。
select c_a,c_b,c_c from t_a where c_a='abcd'
1.実行で結果に条件に合致した2件の行が表示されます。
2.条件の演算子は更に下記の物が有ります。
2.1=(等しい),＞(大きい),＜(小さい),＞=（以上) ,＜=（以下）,!=(等しくない),＜＞(等しくない)
,[not] between a and b（ a以上でb以下で真）
,[not] in(a,b...) (一覧の1つに一致で真,in内にサブクエリと呼ばれるselect文を書く事も出来ます)
,[not] like（パターンに一致,'%'が0個以上の全ての文字に一致、'_'が１文字の全ての文字に一致,'ab%'は先頭がabの物全ての意味に成ります）
,[not] exists(サブクエリ)（サブクエリでデータが有れば真、前後に式は不要）
2.2複数の条件が必要な場合はandで両方の条件が真,orでどちらか一方が真で真となり、andとorではandが先に判定されますが、カッコを付ければカッコ内を先に判定します、又notで否定判定も出来ます。
2.3null(値無しのデータ）の判定は「is [not] null」の判定以外は真に成りませんので注意して下さい。
◎nullが有ると「!=」等の等しくない判定でも真に成らないので、単純ミスをする可能性が有るので、なるべく使わないようにします、その為にはcreate tableでnot nullを指定すればnullが登録出来ませんが、insertで列名を指定しないとエラーとなってしまうので、defaultで初期値に空白か0も定義する事を推奨します。
select c_c,cast(c_c as decimal(3,0))as c_c2 from t_a
1.castで小数点以下を無くしています。
select c_a,cast(c_a as decimal(6,0))as c_a2 from t_a
1.文字は数値に変換出来ません。
select c_a,sum(c_c) as sum,max(c_c) as max,min(c_c) as min,avg(c_c) as avg,count(*) as count
,stdev(c_c)as stdev,stdevp(c_c)as stdevp,var(c_c)as var,varp(c_c)as varp
,string_agg(c_a,',')as string_agg from t_a group by c_a having count(*)>1
1.sum(合計),max(最大値),min(最小値),avg(平均値),count(件数)
,●stdev(標本標準偏差),●stdevp(標準偏差)､●var(標本分散),●varp(分散)
,▲string_agg(区切り文字で結合)。
★RDBMSによっては使えない物も有ります、DB2、Oracle、PostgreSQL、MariaDBを含む５個のRDBMSで、 文法が異なるが使える物は●を、複数のRDBMSで使える物は■を、SQL Serverのみ使える物は▲を付けています。
2.集計関数を使った条件判定はwhereでは無くhavingを使います。
select c_a,c_b,case c_c when 1 then 'abc' when 2.6 then 'xyz' else 'zzz' end as case1 from t_a
1.別の書き方でcase when c_c=1 then 'abc' when c_c=2.6 then 'xyz' else 'zzz' endが有り、等しい以外の演算子も使えます。
select nullif(c_a,c_a),nullif(c_a,c_b) from t_a
1.式１と式2が等しい時nullをそれ以外は式１を返します。
2.nullが発生するので、ほとんど出番は有りません。
select c_a,c_b,c_c,(select c_d from t_b where t_b.c_a=t.c_a)as c_d from (select * from t_a)t
1.サブクエリは便利ですので、複雑なニーズに柔軟に対応出来ます。
create table t_b(c_a char(4) default ' ' not null,c_d decimal(5,2) default 0 not null,primary key(c_a))
go
insert into t_b(c_a,c_d) values('abcd',2)
go
insert into t_b(c_a,c_d) values('wxyz',2)
go
select * from t_a,t_b
1.行同士が結合し、両テーブルの列が集合して表示されます。
2.「実行」で実行します。
3.結果に6件の行が表示されます。t_aの３件の１行づつにt_bの2件を単純に付加して、6件が表示されます。
4.昔のSQLは、これにwhereで条件を付けて処理をしていましたが、最近は別の方法が使われています。尚、複雑な処理には必要な場合も有ります。
★[.....]は複数個書ける意味です。
select * from t_a inner join t_b on t_a.c_a=t_b.c_a
1.実行で結果にonの条件が真の2件の行が表示されます。
select * from t_a left outer join t_b on t_a.c_a=t_b.c_a
1.実行で結果にonの条件が真の2件の行とt_aで結合相手が無い１行の計３行が表示されます。
◎一般的なシステムでは処理対象のデータテーブルと基本情報を持っているマスタテーブルと結合し必要な情報を得て、処理を行います、left outer joinはマスタテーブルの主キーと結合すれば、データテーブルの件数は常に同じと成るので、一番多く使われる結合方法です。
3.right outer joinでleftと逆のテーブルのonでの結合が無い行が付加されます。
select coalesce(t_a.c_a,tb.ca),c_b,c_c_c_d from t_a full outer join t_b on t_a.c_a=t_b.c_a
1.両方のテーブルのonでの結合が無い行が付加され4行表示されます。
2.coaresce(t_a.c_a,tb.ca)は最初のnull以外の値を採用するので、full outer joinの結合キーの表示には最適です。
select c_a,c_b,c_c from t_a union all select c_a,null,c_d from t_b
1.実行で結果にt_aの３行とt_bの２行の計５行が表示されます。
2.unionのみにすると、全く同じ内容の行は１行にまとめられます、尚、普通のselectはselect distinct ...とすれば同じ内容の行が１行と成ります。
select c_c,c_c+2 as p,c_c-2 as mi,c_c*2 as mu,c_c/2 as d,c_c%2 as r from t_a
1.*は乗算で、/は除算で、■％は除算の余りです。 尚、I言語では可能な限りシステム変数で提供し、RDBMSが異なってもそのまま動くように対応しています。
2.算術演算子を複数使った複雑な計算も可能ですし、括弧を使って計算の順序を変える事もできます。
select c_c,abs(c_c)as abs,acos(c_c/10)as acos,asin(c_c/10)as asin
,atan(c_c)as atan,atn2(c_c,c_c*2)as atn2,ceiling(c_c)as ceiling,cos(c_c)as cos
,cot(c_c)as cot,degrees(c_c)as degrees,exp(c_c)as exp,floor(c_c)as floor
,log(abs(c_c)*10)as log,log10(abs(c_c)*10)as log10,pi()as pi,power(c_c,2)as power
,radians(c_c)as radians,rand(c_c) as rand,round(c_c,0)as round0
,round(c_c,0,1)as round1,sign(c_c)as sign,sin(c_c)as sin,sqrt(abs(c_c))as sqrt
,square(c_c)as square,tan(c_c)as tan from t_a
1.abs(絶対値),acos(逆余弦),asin(逆正弦,atan(逆正接),●atn2(点 (y, x) までの線の間の角度をラジアンで返す)
,●ceiling(最小整数),cos（余弦),■cot(コタンジェント),■degrees(ラジアンを度に変換),exp(指数値),floor(最大整数)
,log(自然対数),log10(底が10の対数),pi(円周率),power(べき乗),■radians(度をラジアンに変換),●rand(疑似乱数)
,round(式,まるめ位置)(四捨五入),■round(式,まるめ位置,1)(切捨て),sign(正は+1,負は-1、０は０)
,sin(サイン),sqrt(平方根),▲square(２乗),tan(タンジェント)。
select c_a,c_b,c_c,ascii(c_b)as ascii,char(ascii(c_b))as char,charindex('fg',c_b)as charindex,c_a+c_b as concat0,concat(c_a,c_b)as concat
,concat_ws('#',c_a,c_b,c_a)as concat_ws,difference(c_a,c_b)as difference,format(c_c+123456,'##/##/##')as format
,left(c_b,3)as left0,len(c_b)as len,lower(c_b)as lower,ltrim(' '+c_b)as ltrim,nchar(ascii(c_a))as nchar
,patindex('%fg%',c_b)as patindex,quotename(rtrim(c_a)+']'+rtrim(c_b))as quotename,replace(c_b,'fg','xx')as peplace
,replicate('x',3)as replicate,reverse(c_b)as reverse,right(c_b,4)as right0,rtrim(c_b)+'x' as rtrim,soundex(c_b) as soundex
,'|'+space(c_c)+'|' as space,str(c_c,4,1)as str,string_escape('/"','json')as string_escape,stuff(c_a,2,2,'xy')as stuff
,substring(c_a,2,2)as substring,translate(c_a,'bcno','1234')as translate,trim('ap' from rtrim(c_a))as trim
,unicode(c_a)as unicode,upper(c_a)as upper from t_a
ascii(先頭文字をアスキーコードに変換),char(アスキーコードを文字に変換),●charindex(検索),●+(結合),concat(結合)
,■concat_ws(区切り文字付きで結合),▲difference(soundex()値の差)
,▲format(書式設定),■left(左から抽出),●len(文字数),lower(小文字化),ltrim(左の空白を削除)
,■nchar(ユニコードを文字に変換),▲patindex(パターン検索),■quotename(引用符変更),peplace(置き換え)
,■replicate(繰り返す),■reverse(反転),■right(右から抽出),rtrim(右の空白を削除),▲soundex(2 つの文字列の類似性を評価)
,■space(空白繰り返シ),▲str(数値を文字に変換）,▲string_escape(エスケープを付加します),▲stuff(部分入れ替え)
,●substring(部分抜き出し),trim(両端の指定文字と空白を削除します),■translate(複数の文字単位の置き換え)
,▲unicode(先頭文字をユニコードに変換),upper(大文字化)。
select c_a,c_b,c_c,cume_dist() over(partition by c_a order by c_c)as cume_dist
,first_value(c_b) over(partition by c_a order by c_c)as first_value
,lag(c_b,1) over(partition by c_a order by c_c)as lag
,last_value(c_b) over(partition by c_a order by c_c)as last_value
,lead(c_b,1) over(partition by c_a order by c_c)as lead
,percentile_cont(0.5) within group(order by c_c) over(partition by c_a)as percentile_cont
,percentile_disc(0.5) within group(order by c_c) over(partition by c_a)as percentile_disc
,percent_rank() over(partition by c_a order by c_c)as percent_rank
,dense_rank() over(partition by c_a order by c_c)as dense_rank
,ntile(2) over(partition by c_a order by c_c)as ntile
,rank() over(partition by c_a order by c_c)as rank
,row_number() over(partition by c_a order by c_c)as row_number
from (select * from t_a union all select 'mnop',c_a,c_d from t_b)t order by c_a,c_c,c_b
■cume_dist(累積分布),■first_value(最初の値),■lag(前オフセット値),■last_value(最後の値)■lead(後オフセット値)
■percentile_cont(百分位数),■percentile_disc(特定の百分位数),■percent_rank(相対的な順位)
■dense_rank(順位が飛ばない),■ntile(指定値で分割),■rank(順位が飛ぶ)■row_number(順番)
create table t_c(c_d date default getdate() not null,c_t datetime default getdate() not null)
go
insert into t_c(c_d,c_t) values('2018-09-01','2018-04-01 03:30:30.333')
go
insert into t_c(c_d,c_t) values('2018-08-31','2018-05-31 23:59:59.333')
go
insert into t_c(c_d,c_t) values(getdate(),getdate())
go
select c_d,dateadd(yy,3,c_d)as yy3,dateadd(q,-3,c_d)as q_3,dateadd(m,3,c_d)as m3,dateadd(y,3,c_d)as y3
,dateadd(d,3,c_d)as d3,dateadd(ww,3,c_d)as ww3,dateadd(w,3,c_d)as w3 from t_c
go
select c_t,dateadd(hh,3,c_t)as hh3,dateadd(mi,3,c_t)as mi3,dateadd(s,3,c_t)as s3,dateadd(ms,3,c_t)as ms3 from t_c
go
select c_d,datediff(yy,c_d,getdate())as yy,datediff(q,c_d,getdate())as q,datediff(m,c_d,getdate())as m,datediff(y,c_d,getdate())as y
,datediff(d,c_d,getdate())as d,datediff(ww,c_d,getdate())as ww,datediff(w,c_d,getdate())as w
,c_t,datediff(hh,c_t,getdate())as hh,datediff(mi,c_t,getdate())as mi,datediff(s,c_t,getdate())as s,datediff(ms,c_t,c_t)as ms from t_c
go
select c_d,datepart(yy,c_d)as p_yy,datepart(q,c_d)as p_q,datepart(m,c_d)as p_m,datepart(y,c_d)as p_y
,datepart(d,c_d)as p_d,datepart(ww,c_d)as p_ww,datepart(w,c_d)as p_w
,c_t,datepart(hh,c_t)as p_hh,datepart(mi,c_t)as p_mi,datepart(s,c_t)as p_s,datepart(ms,c_t)as p_ms from t_c
go
1.データ型は日付のみのdateと日付と時間のdatetimeが有ります。
2.getdate()で現時点の日付と時間が設定出来ます。
3.dateaddで日付と時間の計算(負の値で戻る計算も出来ます)が,datediffで差が、datepartで部分が出せます。
yy(年),q(四半期),y(年始からの日数),d(日数),ww(週),w(曜日)、hh(時),mi(分),s(秒,ms(ミリ秒）
update t_a set c_b=case c_b when 'efgh' then 'zzzz' else 'zzzzzz' end,c_c=999.99 where c_a='abcd'
go
select * from t_a
1.whereを書かないと全行が対象となってしまうので注意が必要です。
2.2行分が変更されています。
delete t_a where c_a='abcd'
go
select * from t_a
1.whereを書かないと全行が対象となってしまうので注意が必要です。
2.2行分が削除されて、１行に成っています。
create index t_a1 on t_a(c_b,c_c)
1.uniqueを書くと重複を認めません。
drop index t_a1 on t_a
1.「on テーブル名」を書かないRDBMSも有ります。
create table t_d(c_oya char(6) default ' ' not null,c_ko char(6) default ' ' not null,primary key(c_oya,c_ko))
go
insert into t_d (c_oya,c_ko) values('a','aa')
go
insert into t_d (c_oya,c_ko) values('a','ab')
go
insert into t_d (c_oya,c_ko) values('a','ac')
go
insert into t_d (c_oya,c_ko) values('ab','aba')
go
insert into t_d (c_oya,c_ko) values('ac','aca')
go
insert into t_d (c_oya,c_ko) values('ac','acb')
go
insert into t_d (c_oya,c_ko) values('acb','acba')
go
insert into t_d (c_oya,c_ko) values('acb','acbb')
go
with t_with as (select * from t_d where c_oya='a'
union all select t_d.* from t_d,t_with where t_d.c_oya=t_with.c_ko
)
select * from t_with order by 1
1.withで事前にSQLを宣言出来ますが、宣言したテーブル自身をwith内に書く事で再帰的にSQLを実行出来ます。
create table t_in(c_item char(1),c_date char(8),c_order decimal(5,0),c_order_start decimal(5,0),primary key(c_item,c_date ))
go
create table t_out(c_item char(1),c_date char(8) ,c_order decimal(5,0),c_order_start decimal(5,0),primary key(c_item,c_date))
go
create table t_stock(c_item char(1),stock decimal(5,0),stock_start decimal(5,0),primary key(c_item))
go
insert into t_in(c_item,c_date,c_order,c_order_start)values('a','20180110',100,100)
go
insert into t_in(c_item,c_date,c_order,c_order_start)values('a','20180111',200,200)
go
insert into t_in(c_item,c_date,c_order,c_order_start)values('b','20180112',300,300)
go
insert into t_in(c_item,c_date,c_order,c_order_start)values('c','20180112',600,600)
go
insert into t_stock(c_item,stock,stock_start)values('a',250,250)
go
insert into t_stock(c_item,stock,stock_start)values('b',500,500)
go
insert into t_stock(c_item,stock,stock_start)values('d',900,900)
go
begin
declare @o_end_sw decimal(5,0)=0; /*1で終了判定*/
declare @f_item char(5); /*@f_...はfetchで設定*/
declare @f_date char(8);
declare @f_order decimal(5,0);
declare @f_order_start decimal(5,0);
declare @w_item char(5)=' '; /*@w_...は作業用*/
declare @w_date char(8)=' ';
declare @w_order decimal(5,0)=0;
declare @w_order_start decimal(5,0)=0;
declare @w_stock decimal(5,0)=0;
declare @w_stock_start decimal(5,0)=0;
declare @w_update decimal(5,0)=0;
declare @w_count decimal(5,0)=0;
declare o_cursor cursor for select c_item,c_date,c_order,c_order_start from t_in order by c_item,c_date; /*cursorのselect文*/
begin open o_cursor;
while 0 = 0 begin /*無限ループ*/
fetch o_cursor into @f_item,@f_date,@f_order,@f_order_start; /*fetchで@f_...に値を設定*/
if @@fetch_status<>0 set @o_end_sw=1;/*終了時@o_end_swを1に設定*/
if @w_item<>@f_item OR @o_end_sw<>0 begin /*処理対象のitem値が変化で後処理実行*/
if @w_update=1 begin update t_stock set stock=@w_stock where c_item=@w_item;end; /*引き当て有った場合はt_stockをupdate*/
if @o_end_sw=0 begin /*終了でない場合前処理実施*/
set @w_item=@f_item;set @w_update=0;/*前処理*/
select @w_count=count(*) from t_stock where c_item=@w_item and stock>0 ; /*t_stockにデータ有るか件数select*/
if @w_count=1 begin /*t_stockにデータ有リは引き当て計算開始*/
select @w_stock=stock,@w_stock_start=stock_start /*t_stock情報@w_...に設定*/
from t_stock where c_item=@w_item and stock>0;end;
else begin set @w_stock=0;
end;end;end;
if @o_end_sw=0 begin /*引き当て計算*/
if @f_order>0 and @w_count=1 begin
if @w_stock>@f_order begin /*stockが大き*/
set @w_stock=@w_stock - @f_order; /*stockから引く*/
set @f_order=0; /*orderは0*/
end; else begin
set @f_order=@f_order - @w_stock; /*orderから引く*/
set @w_stock=0; /*stockは0*/
end;
set @w_update=1;/*引き当てした*/
end;
insert into t_out(c_item,c_date,c_order,c_order_start) values(@f_item,@f_date,@f_order,@f_order_start); /*t_outにinsert*/
end;
if @o_end_sw<>0 break; /*終了していたら、無限ループ解消*/
end;
close o_cursor; deallocate o_cursor;end;end /*cursorを閉じる*/
go
select * from t_out
go
select * from t_stock
1.aが100と200で合計300ですが、在庫が250しかないので、最後のオーダーが50と成ります。
2.bは在庫が500有り、発注側の300が引かれ発注は0で在庫は200mと成ります。
3.cは在庫が無いのでそのまま600を発注します。
4.dは発注が無いので、在庫が900のままとなります。
5.cursorとfetch関連はRDBMSで大きく文法が異なるので、上記はSQL Serverのみ動きます、尚、I言語では共通に書ける命令が有ります。
◎このようにSQLでかなりの処理を実現できますが、データの定義と操作以外は出来ないので、プログラムを作るにはプログラミング言語が必要と成ります。
All Rights Reserved, Copyright (C) 2018-2018 Nobumichi Harasawa.