{"id":152,"date":"2011-05-10T22:36:35","date_gmt":"2011-05-10T14:36:35","guid":{"rendered":"http:\/\/guoh.ai\/lifelog\/?p=152"},"modified":"2011-07-17T21:44:17","modified_gmt":"2011-07-17T13:44:17","slug":"memos-about-mysql-procedure","status":"publish","type":"post","link":"https:\/\/guoh.ai\/lifelog\/2011\/05\/memos-about-mysql-procedure\/","title":{"rendered":"MySQL\u5b58\u50a8\u8fc7\u7a0b\u5907\u5fd8"},"content":{"rendered":"
<p>\u7a81\u7136\u8981\u5f04MySQL\u5b58\u50a8\u8fc7\u7a0b<br \/>\n\u597d\u4e45\u6ca1\u6709\u600e\u4e48\u63a5\u89e6\u8fc7\u6570\u636e\u5e93\u5b58\u50a8\u8fc7\u7a0b\u4e86\uff0c\u90fd\u5fd8\u4e86\uff0c\u8bb0\u5728\u8fd9\u91cc<\/p>\n<pre class="brush: sql; title: ; notranslate" title="">create table email (id int auto_increment primary key, name text, content text, int size);\r\ncreate table email_trash (id int auto_increment primary key, name text, content text, int size);\r\n\r\ncreate table attachment (id int auto_increment primary key, email_id int, url text);\r\ncreate table attachment_trash (id int auto_increment primary key, email_id int, url text);<\/pre>\n<pre class="brush: sql; title: ; notranslate" title="">delimiter \/\/\r\ndrop procedure if exists fix_data_trash_magic \/\/<\/pre>\n<pre class="brush: sql; title: ; notranslate" title="">CREATE PROCEDURE `fix_data_trash_magic`()\r\nBEGIN\r\n\r\n DECLARE done INT DEFAULT 0;\r\n\r\n DECLARE _amount INT DEFAULT 0;\r\n DECLARE _size INT;\r\n DECLARE _id INT;\r\n DECLARE _newid LONG;\r\n\r\n DECLARE email_loop CURSOR FOR select id, size from email_trash where name = 'jemmy';\r\n\r\n DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;\r\n\r\n OPEN email_loop;\r\n REPEAT\r\n FETCH email_loop INTO _id, _size;\r\n IF NOT done THEN\r\n insert into email(name, content, size) select name,content,size from email_trash where id = _id;\r\n\r\n select last_insert_id() into _newid;\r\n\r\n insert into attachment(email_id, url) select _newid,url from attachment_trash where email_id = _id;\r\n \r\n SET _amount = _size + _amount;\r\n END IF;\r\n UNTIL done END REPEAT;\r\n CLOSE email_loop;\r\nEND<\/pre>\n<pre class="brush: sql; title: ; notranslate" title="">call fix_data_trash_magic()\/\/<\/pre>\n<p>\u53e6\u5916\u8fd8\u6709\u5e38\u7528\u7684\u51e0\u4e2a\u547d\u4ee4<\/p>\n<pre class="brush: bash; title: ; notranslate" title="">mysql> show procedure status;\r\nmysql> show create procedure procedure_name; \/\/ SHOW THE CREATING SQL OF THE SP<\/pre>\n<p>\u5305\u542b\u5f02\u5e38\u5904\u7406\uff0c\u4fdd\u5b58\u70b9\u7684\u5b58\u50a8\u8fc7\u7a0b<\/p>\n<pre class="brush: sql; title: ; notranslate" title="">DELIMITER ;;\r\nCREATE PROCEDURE ps_with_exception_etc(in_domain varchar(50), in_account varchar(32), in_passwd varchar(32), OUT out_no int, OUT out_msg varchar(50))\r\nBEGIN\r\n DECLARE s_fullid varchar(100);\r\n\r\n DECLARE EXIT HANDLER FOR SQLSTATE '23000'\r\n BEGIN\r\n SET out_no = -1;\r\n SET @prc_work_error = 'Repeated key';\r\n ROLLBACK TO sp_01;\r\n END;\r\n\r\n DECLARE EXIT HANDLER FOR SQLEXCEPTION\r\n BEGIN\r\n SET out_no = -1;\r\n SET @prc_work_error = 'Unknown error';\r\n ROLLBACK TO sp_01;\r\n END;\r\n\r\n -- \u58f0\u660e\u4e4b\u7c7b\u7684\u90fd\u5e94\u8be5\u653e\u5728\u524d\u9762\r\n\r\n SET AUTOCOMMIT = 0;\r\n SELECT concat(in_account, '@', in_domain) INTO s_fullid;\r\n START TRANSACTION;\r\n SAVEPOINT sp_01;\r\n -- DO OO HERE\r\n -- DO XX HERE\r\n COMMIT;\r\n SET out_no = 1;\r\nEND\r\n;;\r\nDELIMITER ;<\/pre>\n<p>\u5b58\u50a8\u8fc7\u7a0b\u4e2d\u7684\u52a8\u6001SQL<br \/>\n\u8fd9\u91cc\u53ea\u662f\u6f14\u793a\u52a8\u6001SQL\u7684\u5199\u6cd5\uff0c\u8981\u662f\u771f\u8fd9\u4e48\u5199\u592a\u6d6a\u8d39\u4e86\uff0c\u76f4\u63a5\u53ef\u4ee5\u5199\u51fa\u6765\u7684SQL\u5c31\u4e0d\u9700\u8981\u52a8\u6001SQL<\/p>\n<pre class="brush: sql; title: ; notranslate" title="">DELIMITER ;;\r\nCREATE PROCEDURE ps_with_dynamic_sql(in_domain varchar(50), in_account varchar(32), OUT out_no int, OUT out_msg varchar(50))\r\nBEGIN\r\n DECLARE fullid varchar(100);\r\n\r\n DECLARE DROP_USER_TABLEK varchar(500);\r\n DECLARE DROP_USER_MAILS varchar(500);\r\n\r\n DECLARE EXIT HANDLER FOR SQLEXCEPTION\r\n BEGIN\r\n SET out_no = -1;\r\n SET @prc_work_error = 'Unknown error';\r\n ROLLBACK TO sp_01;\r\n END;\r\n\r\n SET AUTOCOMMIT = 0;\r\n START TRANSACTION;\r\n SAVEPOINT sp_01;\r\n\r\n SET @MyQuery = concat('DELETE FROM tablek WHERE uuid = ', in_account);\r\n PREPARE DROP_USER_TABLEK from @MyQuery;\r\n\r\n SET @MyQuery = concat('DELETE FROM mails WHERE uuid = ', in_account);\r\n PREPARE DROP_USER_MAILS from @MyQuery;\r\n\r\n EXECUTE DROP_USER_TABLEK;\r\n EXECUTE DROP_USER_MAILS;\r\n COMMIT;\r\n SET out_no = 1;\r\nEND\r\n;;\r\nDELIMITER ;<\/pre>\n<p>\u8fd8\u6709\u4e2a\u7b80\u5355\u7684<\/p>\n<pre class="brush: sql; title: ; notranslate" title="">CREATE PROCEDURE `insert_data`(in item integer)\r\nBEGIN\r\n DECLARE counter INT;\r\n SET counter = item;\r\n WHILE counter >= 1 DO\r\n \/\/ DO YOUR BIZ HERE\r\n SET counter = counter - 1;\r\n END WHILE;\r\nEND<\/pre>\n<p>\u53e6\u5916\u5728\u5b58\u50a8\u8fc7\u7a0b\u4e2d\u58f0\u660e\u53d8\u91cf\uff0c\u6e38\u6807\uff0c\u5904\u7406\u5668\u662f\u6709\u4e00\u5b9a\u7684\u987a\u5e8f\u7684\uff0c\u5426\u5219\u4f1a\u62a5\u8bed\u6cd5\u9519\u8bef<\/p>\n<p>\u89e6\u53d1\u5668<\/p>\n<pre class="brush: sql; title: ; notranslate" title="">create trigger insert_after_addr\r\nafter insert on addr\r\nfor each row\r\nbegin\r\nset @aid=new.id;\r\ninsert into data(addr_id, type, value) values (@aid, 5, 'hello trigger');\r\nend<\/pre>\n<pre class="brush: bash; title: ; notranslate" title="">show triggers\/\/\r\ndrop trigger insert_after_addr\/\/<\/pre>\n<pre class="brush: sql; title: ; notranslate" title="">alter table data modify id int(22) not null auto_increment<\/pre>\n<pre class="brush: sql; title: ; notranslate" title="">CREATE trigger insert_before_tablek\r\n before\r\n insert on tablek\r\n for each row\r\n begin\r\n set new.status = 25;\r\n end<\/pre>\n<\/div>

\u7a81\u7136\u8981\u5f04MySQL\u5b58\u50a8\u8fc7\u7a0b
\n\u597d\u4e45\u6ca1\u6709\u600e\u4e48\u63a5\u89e6\u8fc7\u6570\u636e\u5e93\u5b58\u50a8\u8fc7\u7a0b\u4e86\uff0c\u90fd\u5fd8\u4e86\uff0c\u8bb0\u5728\u8fd9\u91cc<\/p>\n

create table email (id int auto_increment primary key, name text, content text, int size);\r\ncreate table email_trash (id int auto_increment primary key, name text, content text, int size);\r\n\r\ncreate table attachment (id int auto_increment primary key, email_id int, url text);\r\ncreate table attachment_trash (id int auto_increment primary key, email_id int, url text);<\/pre>\n
delimiter \/\/\r\ndrop procedure if exists fix_data_trash_magic \/\/<\/pre>\n
CREATE PROCEDURE `fix_data_trash_magic`()\r\nBEGIN\r\n\r\n    DECLARE done INT DEFAULT 0;\r\n\r\n    DECLARE _amount INT DEFAULT 0;\r\n    DECLARE _size INT;\r\n    DECLARE _id INT;\r\n    DECLARE _newid LONG;\r\n\r\n    DECLARE email_loop CURSOR FOR select id, size from email_trash where name = 'jemmy';\r\n\r\n    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;\r\n\r\n    OPEN email_loop;\r\n        REPEAT\r\n        FETCH email_loop INTO _id, _size;\r\n            IF NOT done THEN\r\n                insert into email(name, content, size) select name,content,size from email_trash where id = _id;\r\n\r\n                select last_insert_id() into _newid;\r\n\r\n                insert into attachment(email_id, url) select _newid,url from attachment_trash where email_id = _id;\r\n                \r\n                SET _amount = _size + _amount;\r\n            END IF;\r\n        UNTIL done END REPEAT;\r\n    CLOSE email_loop;\r\nEND<\/pre>\n
call fix_data_trash_magic()\/\/<\/pre>\n
\u53e6\u5916\u8fd8\u6709\u5e38\u7528\u7684\u51e0\u4e2a\u547d\u4ee4<\/p>\n
mysql> show procedure status;\r\nmysql> show create procedure procedure_name; \/\/ SHOW THE CREATING SQL OF THE SP<\/pre>\n
\u5305\u542b\u5f02\u5e38\u5904\u7406\uff0c\u4fdd\u5b58\u70b9\u7684\u5b58\u50a8\u8fc7\u7a0b<\/p>\n
DELIMITER ;;\r\nCREATE PROCEDURE ps_with_exception_etc(in_domain varchar(50), in_account varchar(32), in_passwd varchar(32), OUT out_no int, OUT out_msg varchar(50))\r\nBEGIN\r\n    DECLARE s_fullid varchar(100);\r\n\r\n    DECLARE EXIT HANDLER FOR SQLSTATE '23000'\r\n    BEGIN\r\n        SET out_no = -1;\r\n        SET @prc_work_error = 'Repeated key';\r\n        ROLLBACK TO sp_01;\r\n    END;\r\n\r\n    DECLARE EXIT HANDLER FOR SQLEXCEPTION\r\n    BEGIN\r\n        SET out_no = -1;\r\n        SET @prc_work_error = 'Unknown error';\r\n        ROLLBACK TO sp_01;\r\n    END;\r\n\r\n    -- \u58f0\u660e\u4e4b\u7c7b\u7684\u90fd\u5e94\u8be5\u653e\u5728\u524d\u9762\r\n\r\n    SET AUTOCOMMIT = 0;\r\n        SELECT concat(in_account, '@', in_domain) INTO s_fullid;\r\n        START TRANSACTION;\r\n        SAVEPOINT sp_01;\r\n        -- DO OO HERE\r\n        -- DO XX HERE\r\n        COMMIT;\r\n    SET out_no = 1;\r\nEND\r\n;;\r\nDELIMITER ;<\/pre>\n
\u5b58\u50a8\u8fc7\u7a0b\u4e2d\u7684\u52a8\u6001SQL
\n\u8fd9\u91cc\u53ea\u662f\u6f14\u793a\u52a8\u6001SQL\u7684\u5199\u6cd5\uff0c\u8981\u662f\u771f\u8fd9\u4e48\u5199\u592a\u6d6a\u8d39\u4e86\uff0c\u76f4\u63a5\u53ef\u4ee5\u5199\u51fa\u6765\u7684SQL\u5c31\u4e0d\u9700\u8981\u52a8\u6001SQL<\/p>\n
DELIMITER ;;\r\nCREATE PROCEDURE ps_with_dynamic_sql(in_domain varchar(50), in_account varchar(32), OUT out_no int, OUT out_msg varchar(50))\r\nBEGIN\r\n    DECLARE fullid varchar(100);\r\n\r\n    DECLARE DROP_USER_TABLEK varchar(500);\r\n    DECLARE DROP_USER_MAILS varchar(500);\r\n\r\n    DECLARE EXIT HANDLER FOR SQLEXCEPTION\r\n    BEGIN\r\n        SET out_no = -1;\r\n        SET @prc_work_error = 'Unknown error';\r\n        ROLLBACK TO sp_01;\r\n    END;\r\n\r\n    SET AUTOCOMMIT = 0;\r\n    START TRANSACTION;\r\n    SAVEPOINT sp_01;\r\n\r\n        SET @MyQuery = concat('DELETE FROM tablek WHERE uuid = ', in_account);\r\n        PREPARE DROP_USER_TABLEK from @MyQuery;\r\n\r\n        SET @MyQuery = concat('DELETE FROM mails WHERE uuid = ', in_account);\r\n        PREPARE DROP_USER_MAILS from @MyQuery;\r\n\r\n        EXECUTE DROP_USER_TABLEK;\r\n        EXECUTE DROP_USER_MAILS;\r\n    COMMIT;\r\n    SET out_no = 1;\r\nEND\r\n;;\r\nDELIMITER ;<\/pre>\n
\u8fd8\u6709\u4e2a\u7b80\u5355\u7684<\/p>\n
CREATE PROCEDURE `insert_data`(in item integer)\r\nBEGIN\r\n    DECLARE counter INT;\r\n    SET counter = item;\r\n    WHILE counter >= 1 DO\r\n        \/\/ DO YOUR BIZ HERE\r\n        SET counter = counter - 1;\r\n    END WHILE;\r\nEND<\/pre>\n
\u53e6\u5916\u5728\u5b58\u50a8\u8fc7\u7a0b\u4e2d\u58f0\u660e\u53d8\u91cf\uff0c\u6e38\u6807\uff0c\u5904\u7406\u5668\u662f\u6709\u4e00\u5b9a\u7684\u987a\u5e8f\u7684\uff0c\u5426\u5219\u4f1a\u62a5\u8bed\u6cd5\u9519\u8bef<\/p>\n
\u89e6\u53d1\u5668<\/p>\n
create trigger insert_after_addr\r\nafter insert on addr\r\nfor each row\r\nbegin\r\nset @aid=new.id;\r\ninsert into data(addr_id, type, value) values (@aid, 5, 'hello trigger');\r\nend<\/pre>\n
show triggers\/\/\r\ndrop trigger insert_after_addr\/\/<\/pre>\n
alter table data modify id int(22) not null auto_increment<\/pre>\n
CREATE trigger insert_before_tablek\r\n    before\r\n        insert on tablek\r\n    for each row\r\n        begin\r\n            set new.status = 25;\r\n        end<\/pre>\n<\/div><\/div>","protected":false},"excerpt":{"rendered":"
<p>\u7a81\u7136\u8981\u5f04MySQL\u5b58\u50a8\u8fc7\u7a0b<br \/> \u597d\u4e45\u6ca1\u6709\u600e\u4e48\u63a5\u89e6\u8fc7\u6570\u636e\u5e93\u5b58\u50a8\u8fc7\u7a0b\u4e86\uff0c\u90fd\u5fd8\u4e86\uff0c\u8bb0\u5728\u8fd9\u91cc<\/p> <pre class="brush: sql; title: ; notranslate" title="">create table email (id int auto_increment primary key, name text, content text, int size); create table email_trash (id int auto_increment primary key, name text, content text, int size); create table attachment (id int auto_increment primary key, email_id int, url text); create table attachment_trash (id int auto_increment … Continue reading MySQL\u5b58\u50a8\u8fc7\u7a0b\u5907\u5fd8<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_gfmr_meta_descriptions":[],"_gfmr_multilingual_taxonomy_terms":[],"footnotes":""},"categories":[13,8],"tags":[12],"class_list":["post-152","post","type-post","status-publish","format-standard","hentry","category-database","category-paste","tag-mysql"],"_links":{"self":[{"href":"https:\/\/guoh.ai\/lifelog\/wp-json\/wp\/v2\/posts\/152","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/guoh.ai\/lifelog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/guoh.ai\/lifelog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/guoh.ai\/lifelog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/guoh.ai\/lifelog\/wp-json\/wp\/v2\/comments?post=152"}],"version-history":[{"count":10,"href":"https:\/\/guoh.ai\/lifelog\/wp-json\/wp\/v2\/posts\/152\/revisions"}],"predecessor-version":[{"id":157,"href":"https:\/\/guoh.ai\/lifelog\/wp-json\/wp\/v2\/posts\/152\/revisions\/157"}],"wp:attachment":[{"href":"https:\/\/guoh.ai\/lifelog\/wp-json\/wp\/v2\/media?parent=152"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/guoh.ai\/lifelog\/wp-json\/wp\/v2\/categories?post=152"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/guoh.ai\/lifelog\/wp-json\/wp\/v2\/tags?post=152"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}