{"id":372,"date":"2014-11-15T09:20:18","date_gmt":"2014-11-15T00:20:18","guid":{"rendered":"http:\/\/www.dbsheetclient.jp\/blog\/?p=372"},"modified":"2018-06-27T16:26:35","modified_gmt":"2018-06-27T07:26:35","slug":"%e3%82%ab%e3%83%bc%e3%82%bd%e3%83%ab%e3%81%ab%e3%81%a4%e3%81%84%e3%81%a6sql-server","status":"publish","type":"post","link":"https:\/\/www.dbsheetclient.jp\/blog\/?p=372","title":{"rendered":"\u30ab\u30fc\u30bd\u30eb\u306b\u3064\u3044\u3066(SQL Server)"},"content":{"rendered":"

IT\u30b3\u30fc\u30c7\u30a3\u30cd\u30fc\u30bf\u306e\u30b7\u30e5\u30a6\u3067\u3059\u3002<\/p>\n

\"\u7d05\u8449\"<\/a><\/p>\n

\u79cb\u3082\u6df1\u307e\u3063\u3066\u304d\u3066\u3001\u7d05\u8449\u306e\u5b63\u7bc0\u306b\u306a\u3063\u3066\u304d\u307e\u3057\u305f\u3002\u306a\u304b\u306a\u304b\u6642\u9593\u304c\u53d6\u308c\u306a\u3044\u3067\u3059\u304c\u3001\u3072\u3068\u3068\u304d\u3067\u3082\u3001\u7f8e\u3057\u3044\u81ea\u7136\u306e\u4e2d\u3092\u6563\u7b56\u3057\u305f\u3044\u3067\u3059\u306d\u3002

\uff1c\u672c\u65e5\u306e\u984c\u6750\uff1e
\u30ab\u30fc\u30bd\u30eb(SQL Server)<\/strong><\/p>\n

Oracle\u306e\u5834\u5408\u306e\u30ab\u30fc\u30bd\u30eb\u306b\u3064\u3044\u3066\u3001\u524d\u3005\u56de\u3001\u524d\u56de\u3068\u984c\u6750\u3068\u3057\u3066\u30a2\u30c3\u30d7\u3057\u3066\u304d\u307e\u3057\u305f\u304c\u3001\u4eca\u56de\u306fSQL Server\u306e\u5834\u5408\u3092\u4e0a\u3052\u3066\u307f\u3088\u3046\u3068\u601d\u3044\u307e\u3059\u3002\u30ab\u30fc\u30bd\u30eb\u51e6\u7406\u306e\u57fa\u672c\u7684\u306a\u6d41\u308c\u306f\u540c\u3058\u3067\u3059\u304c\u3001SQL Server\u306e\u5834\u5408\u306f\u4ee5\u4e0b\u306e\u3088\u3046\u306b\u306a\u308a\u307e\u3059\u3002

\u3010\u30ab\u30fc\u30bd\u30eb\u51e6\u7406\u306e\u6d41\u308c\u3011
<\/strong>\u30ab\u30fc\u30bd\u30eb\u306e\u5b9a\u7fa9
(DECLARE <\u30ab\u30fc\u30bd\u30eb\u540d> CURSOR FOR <SELECT\u6587><\/strong>)
\u30ab\u30fc\u30bd\u30eb\u306e\u30aa\u30fc\u30d7\u30f3 (OPEN <\u30ab\u30fc\u30bd\u30eb\u540d><\/strong>)
\u30ab\u30fc\u30bd\u30eb\u304b\u30891\u884c\u30c7\u30fc\u30bf\u306e\u53d6\u51fa\u3057
(FETCH NEXT FROM <\u30ab\u30fc\u30bd\u30eb\u540d> INTO <\u5909\u6570\u540d><\/strong>)
@@FETCH_STATUS \u304c 0 (success) \u306e\u9593\u30eb\u30fc\u30d7
(WHILE @@FECTH STATUS = 0<\/strong>
BEGIN<\/strong>
\u00a0 \u00a0 1\u884c\u3054\u3068\u306b\u884c\u3044\u305f\u3044\u51e6\u7406
\u00a0 \u00a0 \u6b21\u306e\u884c\u306e\u53d6\u5f97
(FETCH NEXT FROM <\u30ab\u30fc\u30bd\u30eb\u540d> INTO <\u5909\u6570\u540d><\/strong>)
END<\/strong>)
\u30ab\u30fc\u30bd\u30eb\u306e\u30af\u30ed\u30fc\u30ba (CLOSE <\u30ab\u30fc\u30bd\u30eb\u540d><\/strong>)
\u30ab\u30fc\u30bd\u30eb\u53c2\u7167\u3092\u524a\u9664 (DEALLOCATE <\u30ab\u30fc\u30bd\u30eb\u540d><\/strong>)<\/p>\n

\u3061\u3087\u3063\u3068\u304f\u3069\u3044\u304b\u3082\u77e5\u308c\u307e\u305b\u3093\u304c\u3001\u524d\u56de\u306eOracle\u3067\u884c\u3063\u305f\u30ab\u30fc\u30bd\u30eb\u51e6\u7406\u3092 SQL Server\u3067\u8a66\u3057\u3066\u307f\u307e\u3059\u3002<\/p>\n

DECLARE
jyutyu_cur CURSOR FOR
SELECT J01.\u53d7\u6ce8\u756a\u53f7, J01.\u9867\u5ba2\u756a\u53f7, J01.\u53d7\u6ce8\u65e5, J02.\u5546\u54c1CD, J02.\u5546\u54c1\u6570
FROM dbo.\u53d7\u6ce8\u30c6\u30fc\u30d6\u30eb J01
JOIN dbo.\u53d7\u6ce8\u660e\u7d30\u30c6\u30fc\u30d6\u30eb J02 ON J01.\u53d7\u6ce8\u756a\u53f7 = J02.\u53d7\u6ce8\u756a\u53f7
WHERE CONVERT(VARCHAR, J01.\u53d7\u6ce8\u65e5, 111) = CONVERT(VARCHAR, GETDATE(), 111)
ORDER BY J01.\u53d7\u6ce8\u756a\u53f7, J02.\u53d7\u6ce8\u660e\u7d30\u756a\u53f7;<\/p>\n

DECLARE
@\u53d7\u6ce8\u756a\u53f7 VARCHAR(10)
, @\u9867\u5ba2\u756a\u53f7 VARCHAR(8)
, @\u53d7\u6ce8\u65e5 DATE
, @\u5546\u54c1CD VARCHAR(6)
, @\u5546\u54c1\u6570 DECIMAL(4)
, @\u5728\u5eab\u6570 DECIMAL(8);<\/p>\n

OPEN jyutyu_cur;
FETCH NEXT FROM jyutyu_cur INTO @\u53d7\u6ce8\u756a\u53f7, @\u9867\u5ba2\u756a\u53f7, @\u53d7\u6ce8\u65e5, @\u5546\u54c1CD, @\u5546\u54c1\u6570;<\/p>\n

WHILE (@@fetch_status = 0)
BEGIN<\/p>\n

SELECT @\u5728\u5eab\u6570 =\u5728\u5eab\u6570 FROM dbo.\u5728\u5eab\u30c6\u30fc\u30d6\u30eb
WHERE \u5546\u54c1CD = @\u5546\u54c1CD;<\/p>\n

IF @\u5728\u5eab\u6570 >= @\u5546\u54c1\u6570
BEGIN
Print ‘\u53d7\u6ce8\u756a\u53f7=’+ @\u53d7\u6ce8\u756a\u53f7+’,\u5546\u54c1CD=’+ @\u5546\u54c1CD+’,\u5546\u54c1\u6570=’+ CAST(@\u5546\u54c1\u6570 AS VARCHAR)+’, \u5f15\u5f53OK’;<\/p>\n

UPDATE dbo.\u5728\u5eab\u30c6\u30fc\u30d6\u30eb SET
\u5728\u5eab\u6570 =\u5728\u5eab\u6570 – @\u5546\u54c1\u6570
WHERE \u5546\u54c1CD = @\u5546\u54c1CD;
END;<\/p>\n

ELSE
Print ‘\u53d7\u6ce8\u756a\u53f7=’+ @\u53d7\u6ce8\u756a\u53f7+’,\u5546\u54c1CD=’+ @\u5546\u54c1CD+’,\u5546\u54c1\u6570=’+ CAST(@\u5546\u54c1\u6570 AS VARCHAR)+’, \u5f15\u5f53\u4e0d\u53ef’;<\/p>\n

FETCH NEXT FROM jyutyu_cur INTO @\u53d7\u6ce8\u756a\u53f7, @\u9867\u5ba2\u756a\u53f7, @\u53d7\u6ce8\u65e5, @\u5546\u54c1CD, @\u5546\u54c1\u6570;
END;
CLOSE jyutyu_cur;
DEALLOCATE jyutyu_cur;<\/p>\n

\u7d50\u679c\u306f\u3001\u4ee5\u4e0b
\"\u30ab\u30fc\u30bd\u30eb\u7d50\u679c_sqlsv\"<\/a><\/p>\n

\u51e6\u7406\u5f8c\u306e\u5728\u5eab\u30c6\u30fc\u30d6\u30eb\u306e\u5404\u5546\u54c1\u306e\u5728\u5eab\u6570\u306f\u4ee5\u4e0b\u306b\u306a\u308a\u307e\u3059\u3002<\/p>\n

SELECT \u5546\u54c1CD, \u5728\u5eab\u6570
<\/a> FROM dbo.\u5728\u5eab\u30c6\u30fc\u30d6\u30eb
ORDER BY \u5546\u54c1CD;
\"\u5728\u5eab\u30c6\u30fc\u30d6\u30eb\u7d50\u679c_sqlsv\"<\/a><\/p>\n

SQL\u306e\u8a18\u8ff0\u306e\u4ed5\u65b9\u304c\u3001Oracle\u3068SQL Server\u3067\u306f\u591a\u5c11\u7570\u306a\u308b\u3053\u3068\u304c\u308f\u304b\u308b\u3068\u601d\u3044\u307e\u3059\u304c\u3001\u884c\u3044\u305f\u3044\u51e6\u7406\u306f\u540c\u69d8\u306b\u306a\u3059\u3053\u3068\u304c\u3067\u304d\u3066\u3001\u7d50\u679c\u3082Oracle\u306e\u3068\u304d\u3068\u540c\u3058\u306b\u306a\u308a\u307e\u3059\u3002<\/p>\n

\u4eca\u65e5\u306f\u4ee5\u4e0a\u307e\u3067<\/p>\n","protected":false},"excerpt":{"rendered":"

IT\u30b3\u30fc\u30c7\u30a3\u30cd\u30fc\u30bf\u306e\u30b7\u30e5\u30a6\u3067\u3059\u3002 \u79cb\u3082\u6df1\u307e\u3063\u3066\u304d\u3066\u3001\u7d05\u8449\u306e\u5b63\u7bc0\u306b\u306a\u3063\u3066\u304d\u307e\u3057\u305f\u3002\u306a\u304b\u306a\u304b\u6642\u9593\u304c\u53d6\u308c\u306a\u3044\u3067\u3059\u304c\u3001\u3072\u3068\u3068\u304d\u3067\u3082\u3001\u7f8e\u3057\u3044\u81ea\u7136\u306e\u4e2d\u3092\u6563\u7b56\u3057\u305f\u3044\u3067\u3059\u306d\u3002 \uff1c\u672c\u65e5\u306e\u984c\u6750\uff1e \u30ab\u30fc\u30bd\u30eb(SQL Server) Oracl […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[11],"tags":[],"class_list":["post-372","post","type-post","status-publish","format-standard","hentry","category-11"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/posts\/372","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=372"}],"version-history":[{"count":10,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/posts\/372\/revisions"}],"predecessor-version":[{"id":1972,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/posts\/372\/revisions\/1972"}],"wp:attachment":[{"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=372"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=372"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=372"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}