{"id":269,"date":"2016-10-05T03:10:08","date_gmt":"2016-10-04T18:10:08","guid":{"rendered":"http:\/\/okamurax.com\/?p=269"},"modified":"2020-05-14T15:21:51","modified_gmt":"2020-05-14T06:21:51","slug":"mysql-%e3%82%b9%e3%83%88%e3%82%a2%e3%83%89","status":"publish","type":"post","link":"https:\/\/appbay.org\/?p=269","title":{"rendered":"\u30b9\u30c8\u30a2\u30c9\u30d7\u30ed\u30b7\u30fc\u30b8\u30e3"},"content":{"rendered":"

SQLServer<\/h2>\n

\u30fb\u5b9a\u7fa9<\/p>\n

create procedure \u30b9\u30c8\u30a2\u30c9\u30d7\u30ed\u30b7\u30fc\u30b8\u30e3\u540d<\/span>
\n@\u30d1\u30e9\u30e1\u30fc\u30bf\u540d \u578b<\/span>
\nas<\/span>
\nbegin<\/span>
\n\u51e6\u7406<\/span>
\nend<\/span><\/p>\n

\u30fb\u5909\u6570<\/p>\n

\u5ba3\u8a00
\ndeclare @\u5909\u6570\u540d \u578b<\/span><\/p>\n

\u30c7\u30fc\u30bf\u578b
\nvarchar\uff1a\u6587\u5b57\u5217<\/span>
\nint\uff1a\u6570\u5024<\/span>
\ndecimal\uff1a\u5c0f\u6570<\/span>
\ndate\uff1a\u65e5\u4ed8<\/span><\/p>\n

\u4ee3\u5165
\nset \u5909\u6570 = \u5024<\/span><\/p>\n

\u30fb\u30ab\u30fc\u30bd\u30eb
\n1\u884c\u305a\u3064\u30eb\u30fc\u30d7\u3057\u3066\u51e6\u7406\u3059\u308b\u65b9\u6cd5\u3002<\/p>\n

\u30ab\u30fc\u30bd\u30eb\u306e\u5ba3\u8a00
\ndeclare \u30ab\u30fc\u30bd\u30eb\u540d cursor for \uff08select \u6587\uff09<\/span><\/p>\n

\u30ab\u30fc\u30bd\u30eb\u3092\u958b\u304f
\nopen \u30ab\u30fc\u30bd\u30eb\u540d<\/span><\/p>\n

\u30c7\u30fc\u30bf\u306e\u53d6\u5f97
\nfetch next from \u30ab\u30fc\u30bd\u30eb\u540d into \u5909\u6570\u30ea\u30b9\u30c8<\/span><\/p>\n

\u30eb\u30fc\u30d7\u51e6\u7406\u306e\u5b9f\u884c<\/p>\n

while \u6761\u4ef6\u5f0f<\/span>
\nbegin<\/span>
\n\u51e6\u7406\u5185\u5bb9 where current of \u30ab\u30fc\u30bd\u30eb\u540d<\/span>
\nend<\/span><\/p>\n

\u203b\u30eb\u30fc\u30d7\u306e\u5b9a\u756a
\nwhile @@fetch_status = 0<\/span><\/p>\n

\u30ab\u30fc\u30bd\u30eb\u3092\u9589\u3058\u308b
\nclose \u30ab\u30fc\u30bd\u30eb\u540d<\/span><\/p>\n

\u30ab\u30fc\u30bd\u30eb\u306e\u958b\u653e
\ndeallocate \u30ab\u30fc\u30bd\u30eb\u540d<\/span><\/p>\n

MySQL<\/h2>\n

CREATE ROUTINE\u306e\u6a29\u9650\u304c\u5fc5\u8981\u3002<\/p>\n

\u30fb\u57fa\u672c
\ncreate procedure sample01()
\n<\/span>select now();
\n<\/span>mysql>call sample01;<\/span><\/p>\n

\u30fb\u524a\u9664
\nmysql>drop procedure sample01;<\/span><\/p>\n

\u30fb\u4e00\u89a7
\nshow procedure status;<\/span><\/p>\n

\u30fb\u4e2d\u8eab
\nshow create procedure sample01;<\/span><\/p>\n

\u30fb\u5f15\u6570\u3092\u3068\u308b
\ncreate procedure sample01(in a int, in b int)
\n<\/span>select a + b;
\n<\/span>mysql>call sample01(10,5);<\/span><\/p>\n

\u30fb\u8fd4\u308a\u5024
\ncreate procedure sample01(out x int)
\n<\/span>set x = 3;
\n<\/span>mysql> call sample01(@var);
\n<\/span>mysql> select @var;<\/span><\/p>\n

\u30fb\u8907\u6570\u306e\u30af\u30a8\u30ea\u3092\u767a\u884c
\ndelimiter \/\/
\n<\/span>create procedure sample01()
\n<\/span>begin
\n<\/span>select 1;
\n<\/span>select 2;
\n<\/span>end\/\/
\n<\/span>delimiter ;
\n<\/span>mysql> call sample01;<\/span><\/p>\n

\u30fbIF\u6587
\ndelimiter \/\/
\n<\/span>create procedure sample02(in x int)
\n<\/span>begin
\n<\/span>if x = 1 then
\n<\/span>select “inp 1”;
\n<\/span>elseif x = 2 then
\n<\/span>select “int 2”;
\n<\/span>else
\n<\/span>select “inp else”;
\n<\/span>end if
\n<\/span>end \/\/
\n<\/span>delimiter ;
\n<\/span>call sample02(1);<\/span><\/p>\n

\u30fbselect\uff5einto\u6587\u3067\u5909\u6570\u3078
\ndelimiter \/\/
\n<\/span>create procedure p(in a int, out sum int)
\n<\/span>begin
\n<\/span>select a * 2 into @tmp;
\n<\/span>set sum = @tmp;
\n<\/span>end \/\/
\n<\/span>delimiter ;
\n<\/span>call p (4,@sum);
\n<\/span>select @sum;<\/span><\/p>\n

\u30fb\u30ab\u30fc\u30bd\u30eb\u5358\u4f4d\u3067\u51e6\u7406\u3059\u308b<\/p>\n

declare xxx cursor\u3068\u3044\u3046\u5f62\u3067\u5ba3\u8a00\u3059\u308b\u3002
\nfor select \u3067select\u306e\u7d50\u679c\u3092\u5b9a\u7fa9\u3059\u308b\u3002
\nfetch cursor for \u30ab\u30e9\u30e0\u3067\u4ee3\u5165\u3002
\n\u51e6\u7406\u524d\u306bopen\u3057\u3066\u6700\u5f8c\u306bclose\u3059\u308b\u3002<\/p>\n

delimiter \/\/\r\ncreate procedure p(in i_id int, in i_str varchar(10), out o_id int, out o_str varchar(10))\r\n  begin\r\n  declare myid int;\r\n  declare mystr varchar(10);\r\n  declare mycur cursor for select id,str from test_tbl;\u00a0\r\n  set @pos = 0;\r\n  select count(*) into @total from test_tbl;\r\n  set o_id = 0;\r\n  set o_str = '';\r\n  open mycur;\r\n  while @total > @pos do\r\n    fetch mycur into myid,mystr;\r\n    if myid = i_id or mystr = i_str then\r\n      set o_id = myid;\r\n      set o_str = mystr;\r\n      set @pos = @total;\r\n    end if;\r\n    set @pos = @pos + 1;\r\n  end while;\r\n  close mycur;\r\nend \/\/\r\ndelimiter ;\r\nmysql> CALL p( 4, 'cc', @id, @str );\r\nmysql> SELECT @id;<\/pre>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
SQLServer \u30fb\u5b9a\u7fa9 create procedure \u30b9\u30c8\u30a2\u30c9\u30d7\u30ed\u30b7\u30fc\u30b8\u30e3\u540d @\u30d1\u30e9\u30e1\u30fc\u30bf\u540d \u578b as begin \u51e6\u7406 end \u30fb\u5909\u6570 \u5ba3\u8a00 declare @\u5909\u6570\u540d \u578b \u30c7\u30fc\u30bf\u578b varchar\uff1a\u6587\u5b57\u5217 in … <\/p>\n
“\u30b9\u30c8\u30a2\u30c9\u30d7\u30ed\u30b7\u30fc\u30b8\u30e3” \u306e<\/span>\u7d9a\u304d\u3092\u8aad\u3080<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[89],"class_list":["post-269","post","type-post","status-publish","format-standard","hentry","category-1","tag-sql"],"_links":{"self":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/269","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=269"}],"version-history":[{"count":6,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/269\/revisions"}],"predecessor-version":[{"id":3557,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/269\/revisions\/3557"}],"wp:attachment":[{"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=269"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=269"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=269"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}