{"id":1134,"date":"2016-07-26T10:41:58","date_gmt":"2016-07-26T01:41:58","guid":{"rendered":"http:\/\/blog.box.kr\/?p=1134"},"modified":"2016-07-26T10:41:58","modified_gmt":"2016-07-26T01:41:58","slug":"mysql-dynamic-query-in-stored-procedure","status":"publish","type":"post","link":"https:\/\/blog.box.kr\/?p=1134","title":{"rendered":"Mysql dynamic query in stored procedure"},"content":{"rendered":"
CREATE<\/span> PROCEDURE<\/span> `<\/span>test1<\/span>`(<\/span>IN<\/span> tab_name VARCHAR<\/span>(<\/span>40<\/span>),<\/span>IN<\/span> w_team VARCHAR<\/span>(<\/span>40<\/span>))<\/span>\r\nBEGIN<\/span>\r\nSET<\/span> @<\/span>t1 <\/span>=<\/span>CONCAT<\/span>(<\/span>\"SELECT * FROM \"<\/span>,<\/span>tab_name<\/span>,<\/span>\" where team='\"<\/span>,<\/span>w_team<\/span>,<\/span>\"'\"<\/span>);<\/span>\r\n PREPARE stmt3 <\/span>FROM<\/span> @<\/span>t1<\/span>;<\/span>\r\n EXECUTE<\/span> stmt3<\/span>;<\/span>\r\n DEALLOCATE<\/span> PREPARE stmt3<\/span>;<\/span>\r\nEND\r\n\r\n<\/span><\/code>or \r\n\r\n\r\n<\/pre>\n
CREATE<\/span> PROCEDURE<\/span> `<\/span>test1<\/span>`(<\/span> IN<\/span> tab_name VARCHAR<\/span>(<\/span>40<\/span>),<\/span> IN<\/span> w_team VARCHAR<\/span>(<\/span>40<\/span>)<\/span> )<\/span>\r\nBEGIN<\/span>\r\n  SET<\/span> @<\/span>t1 <\/span>=<\/span> CONCAT<\/span>(<\/span> 'SELECT * FROM '<\/span>,<\/span> tab_name<\/span>,<\/span> ' where team = ?'<\/span> );<\/span> -- <-- placeholder<\/span>\r\n  SET<\/span> @<\/span>w_team <\/span>:=<\/span> w_team<\/span>;<\/span>\r\n\r\n  PREPARE stmt3 <\/span>FROM<\/span> @<\/span>t1<\/span>;<\/span>\r\n  EXECUTE<\/span> stmt3 <\/span>USING<\/span> @<\/span>w_team<\/span>;<\/span> -- <-- input for placeholder<\/span>\r\n  DEALLOCATE<\/span> PREPARE stmt3<\/span>;<\/span>\r\nEND<\/span>;\r\n<\/span><\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
CREATE PROCEDURE `test1`(IN tab_name VARCHAR(40),IN w_team VARCHAR(40)) BEGIN SET @t1 =CONCAT(“SELECT * FROM “,tab_name,” where team='”,w_team,”‘”); PREPARE stmt3 FROM @t1; EXECUTE stmt3; DEALLOCATE PREPARE stmt3; END or CREATE PROCEDURE `test1`( IN tab_name VARCHAR(40), IN w_team VARCHAR(40) ) BEGIN SET @t1 = CONCAT( ‘SELECT * FROM ‘, tab_name, ‘ where team = ?’ ); — <– placeholder SET @w_team := w_team; PREPARE stmt3 FROM @t1; EXECUTE stmt3 USING @w_team; — <– input for placeholder DEALLOCATE PREPARE stmt3; END;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false,"ngg_post_thumbnail":0,"spay_email":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true},"categories":[5],"tags":[],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_publicize_connections":[],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5q9Zn-ii","jetpack-related-posts":[{"id":345,"url":"https:\/\/blog.box.kr\/?p=345","url_meta":{"origin":1134,"position":0},"title":"Alter Table [ \uceec\ub7fc \uc0ac\uc774\uc988, \ub370\uc774\ud130 \ud06c\uae30 \uc870\uc815 ]","date":"2014-08-17","format":false,"excerpt":"[DB2] \ucef4\ub7fc\uc758 \ub370\uc774\ud130 \ud0c0\uc785 \ub610\ub294 \uc0ac\uc774\uc988 \uc870\uc815 ALTERTABLE temp \u00a0 \u00a0\u00a0ALTERCOLUMN col1 SET DATA TYPE VARCHAR(60); [ORACLE] alter table\u00a0\u00a0 table_namemodify\u00a0\u00a0 column_name\u00a0 datatype; \uc774\ub7f0\uc2dd\uc73c\ub85c\ub3c4 \ub41c\ub2e4.. alter table\u00a0\u00a0 table_namemodify\u00a0\u00a0 (\u00a0\u00a0 column1_name\u00a0 column1_datatype,\u00a0\u00a0 column2_name\u00a0 column2_datatype,\u00a0\u00a0 column3_name\u00a0 column3_datatype,\u00a0\u00a0 column4_name\u00a0 column4_datatype\u00a0\u00a0 ); [MYSQL] alter table people modify name VARCHAR(35) ;","rel":"","context":"In "DB\uad00\ub828"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":801,"url":"https:\/\/blog.box.kr\/?p=801","url_meta":{"origin":1134,"position":1},"title":"[scrap] MySQL Replication \uc124\uc815\uacfc \uba87 \uac00\uc9c0 \ud14c\uc2a4\ud2b8","date":"2015-05-18","format":false,"excerpt":"http:\/\/blog.hibrainapps.net\/130 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 MySQL\uc744 DB\ub85c \uc0ac\uc6a9\ud558\uba74\uc11c \uc11c\ubc84\uc758 \ubd80\ud558 \ubd84\uc0b0\uc744 \uc704\ud55c \ubc29\ubc95 \uc911 \ud558\ub098\ub85c Replication \uc744 \uc0ac\uc6a9\ud55c\ub2e4. Replication \uc740 Master \ud558\ub098\uc5d0 n\uac1c\uc758 Slave\ub85c \uc9c0\uc815\uc774 \uac00\ub2a5\ud558\ub2e4. Slave\ub294 \ub2e4\uc2dc Master \uc5ed\ud560\uc744 \ud560\uc218 \uc788\uc73c\uba70 \uc5ed\uc2dc \ub610 \ub2e4\ub978 n\uac1c\uc758 Slave\ub97c \uc9c0\uc815\ud560 \uc218 \uc788\ub2e4. \ubd80\ud558 \ubd84\uc0b0\uc758 \ud6a8\uacfc\ub294 inser,update \ub4f1 \ubcc0\uacbd\uacfc \uad00\ub828\ub41c \ubaa8\ub4e0 \uc791\uc5c5\uc740\u2026","rel":"","context":"In "DB\uad00\ub828"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":619,"url":"https:\/\/blog.box.kr\/?p=619","url_meta":{"origin":1134,"position":2},"title":"[\ud38c][IBM][CLI Driver] SQL0418N SQLSTATE=42610","date":"2015-03-13","format":false,"excerpt":"\uac1c\uad04 :\u00a0[IBM][CLI Driver] SQL0418N\u00a0SQLSTATE=42610 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\uba85\ub839\ubb38\uc5d0 \uc720\ud6a8\ud558\uc9c0 \uc54a\uc740 \ub9e4\uac1c\ubcc0\uc218 \ud45c\uc2dc\ubb38\uc790\uac00 \uc0ac\uc6a9\ub418\uc5c8\uc2b5\ub2c8\ub2e4. \uc6d0\uc778: \ub2e4\uc74c \uacbd\uc6b0\uc5d0\ub294 \uc720\ud615\uc774 \uc9c0\uc815\ub418\uc9c0 \uc54a\uc740 \ub9e4\uac1c\ubcc0\uc218 \ud45c\uc2dc\ubb38\uc790\ub97c \uc0ac\uc6a9\ud560 \uc218 \uc5c6\uc2b5\ub2c8\ub2e4. SELECT \ubaa9\ub85d\uc5d0\uc11c \ub0a0\uc9dc \uc2dc\uac04 \uc0b0\uc220 \uc5f0\uc0b0\uc758 \uc720\uc77c\ud55c \uc778\uc218\ub85c \uacbd\uc6b0\uc5d0 \ub530\ub77c \uc2a4\uce7c\ub77c \ud568\uc218\uc758 \uc720\uc77c\ud55c \uc778\uc218\ub85c ORDER BY\uc808\uc5d0\uc11c \uc815\ub82c \ud0a4\ub85c \ub2e4\uc74c\uacfc \uac19\uc740 \uacbd\uc6b0 \ub9e4\uac1c\ubcc0\uc218 \ud45c\uc2dc\ubb38\uc790\ub97c \uc0ac\uc6a9\ud560 \uc218 \uc5c6\uc2b5\ub2c8\ub2e4. \uc900\ube44\ub41c \uba85\ub839\ubb38\uc774\u2026","rel":"","context":"In "DB\uad00\ub828"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":896,"url":"https:\/\/blog.box.kr\/?p=896","url_meta":{"origin":1134,"position":3},"title":"Working with Apache Cassandra on Mac OS X","date":"2015-06-16","format":false,"excerpt":"If you use Mac OS X as your platform for development work, then you may be interested to know how easy it is to use Apache Cassandra on the Mac. The following shows you how to download and setup Cassandra, its utilities, and also use DataStax OpsCenter, which is a\u2026","rel":"","context":"In "\uae30\uc220"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":741,"url":"https:\/\/blog.box.kr\/?p=741","url_meta":{"origin":1134,"position":4},"title":"[DB2] how to use Sequence table","date":"2015-05-07","format":false,"excerpt":"1. describe sequence table describe table syscat.sequences; 2.\u00a0 select sequence information SELECT * FROM syscat.sequences; 3. create sequence CREATE SEQUENCE seq_tab START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 24; -> start 1, increment\u00a01 by 1 \u00a0to\u00a0\u00a0no limit CREATE SEQUENCE seq_tab START WITH 1 INCREMENT BY 1 maxvalue 10000\u2026","rel":"","context":"In "DB\uad00\ub828"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":379,"url":"https:\/\/blog.box.kr\/?p=379","url_meta":{"origin":1134,"position":5},"title":"[\ud38c]\ube60\ub974\uac8c \ud6dd\uc5b4 \ubcf4\ub294 node.js","date":"2014-09-15","format":false,"excerpt":"http:\/\/bcho.tistory.com\/889 \ube60\ub974\uac8c \ud6dd\uc5b4 \ubcf4\ub294 node.js - #6 MongoDB \uc5f0\ub3d9 (mongo-native) \ud074\ub77c\uc6b0\ub4dc \ucef4\ud4e8\ud305 & NoSQL\/Vert.x & Node.js\u00a0|\u00a02014\/04\/03 23:49\u00a0|\u00a0Posted by\u00a0\uc870\ub300\ud611 \ube60\ub974\uac8c \ud6dd\uc5b4\ubcf4\ub294\u00a0node.js #6- mongo-native \ubaa8\ub4c8\uc744 \uc774\uc6a9\ud55c MongoDB \uc5f0\ub3d9 \uc870\ub300\ud611\u00a0(http:\/\/bcho.tistory.com Persistence\u00a0\uc5f0\ub3d9 node.js\ub294\u00a0DB\ub098\u00a0NoSQL\ub4f1\uc758 \uc5f0\ub3d9\uc744 \uc9c0\uc6d0\ud558\ub294\ub370,\u00a0\uc774 \uc5ed\uc2dc \ucca0\uc800\ud558\uac8c\u00a0non-blocking io\u00a0\ubc29\uc2dd\uc73c\ub85c \ub3d9\uc791\ud55c\ub2e4.\u00a0\uc989\u00a0db\u00a0\uc5f0\uacb0\u00a0socket\uc744 \uc5f4\uc5b4\uc11c\u00a0query\ub97c \ub358\uc838\ub193\uace0, query\u00a0\uacb0\uacfc\uac00 \uc624\uba74 \uc774\ubca4\ud2b8\ub97c \ubc1b\uc544\uc11c\u00a0callback\u00a0\ud568\uc218\ub85c \ucc98\ub9ac\ud558\ub294 \uc21c\uc11c\uc774\ub2e4. \uadf8\ub7ec\uba74 \uc5ec\uae30\uc11c\ub294 \uba87\uac00\uc9c0\u00a0persistence\u00a0\uc5f0\ub3d9 \ubc29\uc2dd\uc5d0 \ub300\ud574\uc11c \uc54c\uc544\ubcf4\ub3c4\ub85d \ud55c\ub2e4.\u2026","rel":"","context":"In "\ucc38\uace0\ub97c \uc704\ud55c \uc800\uc7a5\ubb3c"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/posts\/1134"}],"collection":[{"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.box.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1134"}],"version-history":[{"count":1,"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/posts\/1134\/revisions"}],"predecessor-version":[{"id":1135,"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/posts\/1134\/revisions\/1135"}],"wp:attachment":[{"href":"https:\/\/blog.box.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1134"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.box.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1134"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.box.kr\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1134"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}