{"id":575,"date":"2013-01-29T13:14:00","date_gmt":"2013-01-29T05:14:00","guid":{"rendered":"http:\/\/note.systw.net\/note\/?p=575"},"modified":"2023-11-04T13:25:04","modified_gmt":"2023-11-04T05:25:04","slug":"mysql-procedure","status":"publish","type":"post","link":"https:\/\/systw.net\/note\/archives\/575","title":{"rendered":"MySQL procedure"},"content":{"rendered":"\n

MYSQL procedure
\u53ef\u76f4\u63a5\u5728mysql\u5167\u64b0\u5beb\u985e\u4f3c\u7a0b\u5f0f\u7684\u7d50\u69cb<\/p>\n\n\n\n


\u5efa\u7acbprocedure<\/strong>
CREATE PROCEDURE sp_name ([proc_parameter[,…]])
[characteristic …] routine_body
ex:
DELIMITER $$
CREATE PROCEDURE generate_data()
select now();
$$
ps:
\u57f7\u884cprocedure
CALL generate_data();<\/p>\n\n\n\n


\u522a\u9664procedure<\/strong>
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name
ex:
DROP PROCEDURE generate_data<\/p>\n\n\n\n

\u67e5\u770bprocedure<\/strong>
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE ‘pattern’]
ex:
SHOW PROCEDURE STATUS<\/p>\n\n\n\n

#################################################################<\/p>\n\n\n\n

\u8b8a\u6578\u90e8\u4efd <\/strong><\/h2>\n\n\n\n

\u8b8a\u6578\u5ba3\u544a<\/strong>
DECLARE < var_name[,…]> < type > [DEFAULT value]
DECLARE\u6307\u4ee4\u5fc5\u9700\u5728BEGIN….END\u5340\u584a\u5167\u4f7f\u7528, \u4e0d\u80fd\u653e\u5340\u584a\u5916, \u5426\u5247\u6703\u6709\u932f\u8aa4\u8a0a\u606f,
\u8b8a\u6578\u524d\u52a0’@’\u7b26\u865f\u7528\u4f86\u8b8a\u8b58\u51fa\u544a\u7684\u5340\u57df\u8b8a\u6578,\u800c\u5169\u500b’@’\u662f\u6307\u7cfb\u7d71\u8b8a\u6578
SET\u6307\u4ee4, \u9019\u662f\u6307\u6d3e\u8b8a\u6578\u503c\u7684\u6307\u4ee4,
ex:
DECLARE @total_year int;
DECLARE @unit_name varchar(10);
ex:
CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
\u3000DECLARE @x int;
\u3000SET @x = 0;
\u3000REPEAT SET @x = @x + 1;
\u3000UNTIL @x > p1 END REPEAT;
END<\/p>\n\n\n\n

\u8a2d\u5b9a\u8b8a\u6578<\/strong>
SET < var_name > = < expr [, var_name = expr] … >
ex:
set var1=’100′,var2=’200′, var3=’300′;<\/p>\n\n\n\n

…<\/p>\n\n\n\n

\u63a5\u6536\u8b8a\u6578\u50b3\u56de\u503c\u7684\u4e09\u7a2e\u65b9\u6cd5<\/strong><\/p>\n\n\n\n

1
\u5c07\u7d50\u679c\u76f4\u63a5\u8f38\u51fa<\/strong>
CREATE PROCEDURE mysp_QryData (id int)
BEGIN
\u3000SELECT LastName, FirstName FROM Customer WHERE CustomerID = id;
END;
\u547c\u53eb\u8a72procedure\u6703\u76f4\u63a5\u8f38\u51fa\u7d50\u679c<\/strong>
CALL mysp_QryData(1);<\/p>\n\n\n\n

2
\u5c07\u7d50\u679c\u5b58\u9032\u8b8a\u6578<\/strong>
CREATE PROCEDURE mysp_QryData2 (id int,out lname varchar(30))
BEGIN
\u3000SELECT LastName INTO lname FROM Customer WHERE CustomerID = id;
END;
\u5728\u5f9e\u8b8a\u6578\u53d6\u51fa\u7d50\u679c<\/strong>
CALL mysp_QryData2(1, @lname);
SELECT @lname;<\/p>\n\n\n\n

3
\u5c07\u7d50\u679c\u56de\u50b3<\/strong>
CREATE FUNCTION func_QryData3 (id int)
RETURNS varchar(30)
BEGIN
\u3000DECLARE lname varchar(30);
\u3000SELECT LastName INTO lname FROM Customer WHERE CustomerID = id;
\/\/\u7b49\u540c\u65bcSET=lname( SELECT LastName FROM Customer WHERE CustomerID = id);
\u3000RETURN lname;
END;
\u8a2d\u5b9a\u63a5\u6536\u7d50\u679c\u7684\u65b9\u5f0f<\/strong>
SET @lname = func_QryData3(1);
SELECT @lname;<\/p>\n\n\n\n

#################################################################<\/p>\n\n\n\n

\u6d41\u7a0b\u63a7\u5236\u90e8\u4efd<\/strong><\/h2>\n\n\n\n

IF<\/strong>
\u8a9e\u6cd5\u5982\u4e0b
IF < search_condition> THEN
\u3000< statement_list
\u3000\u3000[ELSEIF < search_condition> THEN < statement_list>] …
\u3000\u3000[ELSE < statement_list>] >
END IF;<\/p>\n\n\n\n

ex:\nCREATE PROCEDURE addcol()\nBEGIN\n\u3000IF NOT EXISTS(\n\u3000\u3000SELECT * FROM information_schema.COLUMNS\n\u3000\u3000WHERE COLUMN_NAME='rtsp_port' AND TABLE_NAME='device' AND TABLE_SCHEMA='nevionvr'\n\u3000)THEN\n\u3000\u3000ALTER TABLE `device` ADD `rtsp_port` INT( 16 ) UNSIGNED NOT NULL AFTER `ftp_port` ;\nEND IF;\nEND;<\/code><\/pre>\n\n\n\n
case<\/strong>
\u8a9e\u6cd51\u5982\u4e0b<\/p>\n\n\n\n
case < various >\n\u3000when < value1 > then < statement >\n\u3000[ when < value2 > then < statement > ]...\n\u3000ELSE < statement >\nend case;<\/code><\/pre>\n\n\n\n
\u8a9e\u6cd52\u5982\u4e0b<\/p>\n\n\n\n
case\n\u3000when < search_condition > then < statement >\n\u3000[ when < search_condition > then < statement > ]...\n\u3000ELSE < statement >\nend case;\nex:\nCREATE PROCEDURE p()\nBEGIN\n\u3000DECLARE v INT DEFAULT 1;\n\u3000CASE v\n\u3000\u3000WHEN 2 THEN SELECT v;\n\u3000\u3000WHEN 3 THEN SELECT 0;\n\u3000\u3000ELSE\n\u3000\u3000\u3000BEGIN\n\u3000\u3000\u3000END;\n\u3000END CASE;\nEND;<\/code><\/pre>\n\n\n\n
WHILE<\/strong>
\u8a9e\u6cd5\u5982\u4e0b
while < search_condition > do
\u3000< statement_list>
end while;<\/p>\n\n\n\n
REPEAT<\/strong>
\u8a9e\u6cd5\u5982\u4e0b
[ begin_label ]: REPEAT
\u3000< statement_list >
\u3000UNTIL < search_condition >
END REPEAT [ end_label];
ex:
REPEAT
\u3000SET @x = @x + 1;
\u3000UNTIL @x > p1
END REPEAT;<\/p>\n\n\n\n
LOOP<\/strong>
\u8a9e\u6cd5\u5982\u4e0b
< loop_label>: LOOP
\u3000< statement_list>
END LOOP < loop_label>;
ps:
LEAVE < loop_label> \u662f\u96e2\u958bloop<\/p>\n\n\n\n
ex:loop and if<\/p>\n\n\n\n
DELIMITER $$\nCREATE PROCEDURE generate_data()\nBEGIN\n\u3000DECLARE a INT Default 0 ;\n\u3000simple_loop: LOOP\n\u3000\u3000SET a=a+1;\n\u3000\u3000INSERT INTO data(`num`) VALUES (a);\n\u3000\u3000IF a=10 THEN\n\u3000\u3000\u3000LEAVE simple_loop;\n\u3000\u3000END IF;\n\u3000END LOOP simple_loop;\nEND $$<\/code><\/pre>\n\n\n\n

ex:\u65b0\u589e1-365\u5929\u7684\u65e5\u671f<\/p>\n\n\n\n
DELIMITER $$\nCREATE PROCEDURE gen_date_template()\nBEGIN\n\u3000DECLARE a INT Default 0 ;\n\u3000simple_loop: LOOP\n\u3000\u3000INSERT INTO date_template(`date`) VALUES(DATE_ADD('2005-01-01',INTERVAL a DAY));\n\u3000\u3000SET a=a+1;\n\u3000\u3000IF a=365 THEN\n\u3000\u3000\u3000LEAVE simple_loop;\n\u3000\u3000END IF;\n\u3000END LOOP simple_loop;\nEND $$<\/code><\/pre>\n\n\n\n
reference:
http:\/\/ithelp.ithome.com.tw\/question\/10032363
http:\/\/www.dotblogs.com.tw\/walter\/archive\/2009\/07\/05\/how-to-get-mysql-store-procedure-return-value.aspx.aspx<\/p>\n","protected":false},"excerpt":{"rendered":"
MYSQL procedure\u53ef\u76f4\u63a5\u5728mysql\u5167\u64b0\u5beb\u985e\u4f3c\u7a0b …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"","fifu_image_alt":"","_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,"font":"","enabled":false},"version":2}},"categories":[16],"tags":[],"class_list":["post-575","post","type-post","status-publish","format-standard","hentry","category-database"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/systw.net\/note\/wp-json\/wp\/v2\/posts\/575","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/systw.net\/note\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/systw.net\/note\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/systw.net\/note\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/systw.net\/note\/wp-json\/wp\/v2\/comments?post=575"}],"version-history":[{"count":0,"href":"https:\/\/systw.net\/note\/wp-json\/wp\/v2\/posts\/575\/revisions"}],"wp:attachment":[{"href":"https:\/\/systw.net\/note\/wp-json\/wp\/v2\/media?parent=575"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/systw.net\/note\/wp-json\/wp\/v2\/categories?post=575"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/systw.net\/note\/wp-json\/wp\/v2\/tags?post=575"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}