·         麓麓陆篓麓娲⒇撀惩愺懇捖狢REATE ROUTINE权袨隆拢

·         汀褢禄髶喅锟接惩愺懇捖狝LTER ROUTINE权袨隆拢症赂锟竭椩堵�藲渊負鲁蛺蚨劥麓陆篓譄隆拢

·         执褠負鲁蛺髴�捖狤XECUTE权袨隆拢然露锟铰革拷邨远炉藲渊 負鲁蛺蚨劥麓陆篓譄隆拢同夜拢卢負鲁蛺髣瓐系脫QL SECURITY 蜆址藝DEFINER拢卢碳諍循詢赂臈映蛺蚋剮藠锟脚撁宦г�执褠負鲁蛺髞珡碌碌陆一前隆拢

20.2.1. CREATE PROCEDURE潞虄REATE FUNCTION

20.2.2. ALTER PROCEDURE潞虂LTER FUNCTION

20.2.3. DROP PROCEDURE潞虅ROP FUNCTION

20.2.4. SHOW CREATE PROCEDURE潞虛HOW CREATE FUNCTION

20.2.5. SHOW PROCEDURE STATUS潞虛HOW FUNCTION STATUS

20.2.6. CALL辕戮夂痑>

20.2.7. BEGIN ... END赂麓潞袚锟�

20.2.8. DECLARE辕戮夂痑>

20.2.9. 麓娲⒙惩愺棎的卞伩

20.2.10. 偷录锟巾磳巡

20.2.11. 鹿獠�

20.2.12. 路鲁炭贃乒鹿宅

·         碌卤一赂锟酵愹被碉拷卤拢卢一赂锟铰�碌脮SE db_name 卤禄执褠拢篓碌卤負鲁蛺髼枛鹿时停止执褠拢漏隆拢麓娲⒇撀惩愺厷德�/span>USE辕戮鍔甭猜徽娧�碌摹拢

·         牛驴蕭諍鹿詢私戮菘銊恍灺堵ㄘ撀惩愺劵÷Ｖ⒙渴捲甭辉�4咏詢一赂锟节德鼻八铰据裤棎碌艞映蛺颉１蓤纾�要咏詢一赂锟絜st私戮菘夂榡碌录/span>麓娲⒙惩愸凹猜郝�私f拢卢牛驴蕭諎碌CALL test.p()禄癯こt.f()隆拢

·         私戮菘銚喡筹拷颍瓝鞂悸官伮�碌艐锟铰绰⒇撀惩愺挷堵悸甭挥喡筹拷:p>

CREATE PROCEDURE sp_name ([proc_parameter[,...]])

    [characteristic ...] routine_body

CREATE FUNCTION sp_name ([func_parameter[,...]])

    RETURNS type

    [characteristic ...] routine_body

    proc_parameter:

    [ IN | OUT | INOUT ] param_name type

    func_parameter:

    param_name type

type:

    Any valid MySQL data type

characteristic:

    LANGUAGE SQL

  | [NOT] DETERMINISTIC

  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

  | SQL SECURITY { DEFINER | INVOKER }

  | COMMENT 'string'

routine_body:

    Valid SQL procedure statement or statements

症些辕戮浯绰铰�麓娲⒇撀惩愹·捖�諝MySQL 5.1讗麓麓陆篓負鲁蛺颍�辟愲繜詯CREATE ROUTINE权袨拢卢虏垄葤ALTER ROUTINE潞虆XECUTE权袨卤禄財露炉藲渊碳碌拇麓陆篓譄隆拢骚鹿锟阶喩曋韭孤ε溌甭徽娧�拢卢牛也驴蕜輴閽�SUPER权袨拢卢全虏蠑录/span>20.4陆丶/span>拢卢隆掳麓娲⒇撀惩愹粛绰ヂ仿⒙惩愹秳讹拷謻戮鹿娄艤”隆拢

默蓮碌兀卢負鲁蛺髷�德鼻八铰据库簶j隆拢要姆确碌匕覘映蛺髷瑨禄赂锟铰ㄋ铰据库簶j前4拢卢驴蕭諗诖麓陆篓負鲁蛺蚨厞卤潞髺付篓扦幕貣为db_name.sp_name隆拢

骚鹿锟窖材宦何勞铰�碌录/span>SQL潞炉私幕一夜拢卢露篓鹰負鲁蛺髪保卢牛穴要諝症赂锟街何嬫怀(潞茤屑涑ド�一赂锟今，夫暡仿⑹涸�路篓麓顝陈÷Ｂ德迸Ｌβ后陡撃椨惩愹秴娐甭后挸捖�虏鎵�隆拢为麓耍卢录麓使詯驴蕜艹锟姐棖醛驴锟接兦宦顾嚶铰ㄓ┴�潞帽輧夤概Ｘ斅悸郝德� 麓娲⒇撀惩愺垻撾郸諝碌脫QL潞炉私一夜碌艃锟�

詨(潞虐锟较婏拷鞓溗嚶寸暁隆拢骚鹿锟铰蚕婏拷膴鹿詢一赂锟较婏拷拢每赂锟斤拷隙录藝一赂锟较婏拷赂露篓为扦碳虏蠆锟节蚕婏拷掳使詢鹿丶锟絋禄駡崗UT

注英: 指露篓虏蠆锟� OUT, 禄駡崗UT 只露訍ROCEDURE藝潞戏篓碌摹拢拢篓FUNCTION虏蠆锟铰簧徫�藝IN虏蠆锟�:p>

RETURNS貣戮鍠慌溌队哢NCTION囟指露篓拢卢露院炉私露锟剿嚽孔喡的÷Ｌ荚�4指露篓潞炉私碌姆碌禄賭釕嵚Ｂ�露锟剿酵ヂ辟愲奔潞卢一赂锟絧an>RETURN value辕戮洹�

routine_body 掳锟铰�碌脫QL鹿锟戒。驴蕭諍鹿詢赂麓潞袚锟ぴ�路篓拢卢全虏蠑录a href="#begin-end" title="20.2.7. BEGIN ... END Compound Statement">20.2.7陆冢卢隆掳BEGIN ... END赂麓潞袚锟�”隆拢赂麓潞袚锟ぢ渴捲帮拷锟窖�禄路潞螁鍖悸抠柶结构辕戮洹Ｖ⑿┰�戮涠勗�路篓諝卤戮謧潞魟⒔陫溌Ｂ�戮趢锟较斅糰 href="#declare" title="20.2.8. DECLARE Statement">20.2.8陆冢卢隆掳DECLARE辕戮饣硃an>”潞思a href="#flow-control-constructs" title="20.2.12. Flow Control Constructs">20.2.12陆冢卢隆掳路鲁炭贃乒鹿宅隆卤隆拢

CREATE FUNCTION辕戮浔辉冋毬革拷ySQL掳姹臼�訑支鲁諘DF 拢篓財露篓鹰潞炉私拢漏隆拢全虏蠑录a href="http://book.chinaz.com/mysql5/extending-mysql.html#adding-functions" title="27.2. Adding New Functions to MySQL">27.2陆冢卢隆掳赂锟絃铜录詯潞炉私”隆拢 UDF录蛺锟铰持Ｂ�录麓使袞諝 詯聥麓娲⒙郝�私隆拢UDF禄岜簧徫�一赂锟铰柯存储潞炉私隆拢然露锟揭�蓛麓娲⒙郝�私垣UDF潞炉私鹿虏协幕貣驴占洹�

微虏驴麓娲⒙惩愹秳框綔铰�諝虏禄戮玫慕芦4咏色隆拢症陆芦諍循牛詢SQL之微碌艙饜挃卤釔绰存储鲁蛺颉�楋�壟溌蹬娗Ｂ�碌蹝禄赂锟铰�鲁讚饜挃藝PHP拢卢硬为潞虗脨HP咏圈潞輴隆拢卢袩鲁贪虏全拢卢葤驴蕭员禄路陆卤愣樓渡�隆拢硬为驴蚪潑枪芦驴陋碌模卢碳希位循露釃ぬ荚�覕也艤卤禄支鲁帧拢

骚鹿锟铰惑悷惩椵娗墩嵚�夜碌艎鍓�虏蠆锟铰�夜碌慕岷宦Ｂ�詹卤禄蓮为碳藝“确露篓碌娄rdquo;拢卢路驎簿螉纽ldquo;路葓路露篓”碌摹拢骚鹿锟皆惵革拷TERMINISTIC也没詯赂锟絋 DETERMINISTIC拢卢默蓮碌木螉茙OT DETERMINISTIC隆拢

为陆锟阶喡Ｂ�使詢NOW()潞炉私拢篓禄髮嫉艒卢鹰麓剩漏禄鸹泊1:place w:st="on">RAND()潞炉私禄岵宦辟捖�碌賷鹿碌膾禄赂锟酵愹笀埪仿堵�隆拢露訋OW()露锟铰讹拷謻戮掳锟戒祦虏垄卤禄纸确赂麓讍隆拢RAND() 只要諝一赂锟酵愹奔勠撀υ円宦聪捖猜会被纸确赂麓讍隆拢拢篓牛驴蕭园覘映蛺髺祼褗卤录涞伮何嬫己私讝負蓮为强讍摔色拢卢碳膰諝追麓詨袏葝卢夜碌摹拢拢漏

碌卤前4陆虏拢卢DETERMINISTIC蜆址卤禄陆詩埽卢碌芦禄鹿没詯卤禄詤禄炉鲁蛺髮簥鹿詢隆拢然露锟铰讹拷謻戮鹿娄艤卤禄諍循聥拢卢症赂锟斤拷斓�MySQL藝路窦〗詩輻映蛺蚨�掑。全虏蠑录a href="#stored-procedure-logging" title="20.4. Binary Logging of Stored Routines and Triggers">20.4陆丶/span>拢卢隆掳麓娲⒇撀惩愹粛绰ヂ仿⒙惩愹秳讹拷謻戮鹿娄艤”隆拢

一些蜆址汀鹿漏負鲁蛺髪簱膴锟桔斲惼徛⒙÷�CONTAINS SQL卤願矩撀惩愹不帮拷髳祳锟斤郡隆拢NO SQL卤願矩撀惩愹不帮拷辕戮洹�READS SQL DATA卤願矩撀惩愹奔郝�露聤锟斤郡拢卢碌芦虏禄掳锟斤拷锟ぢ÷�MODIFIES SQL DATA卤願�負鲁蛺虮己卢写私戮莸艙锟ぢ÷Ｉ�鹿锟絘n>症些蜆址没詯姆确赂锟�默蓮碌艎苾ONTAINS SQL隆拢

SQL SECURITY蜆址驴蕭論膧麓指露篓 負鲁蛺蚬創么麓陆篓負鲁蛺髺煹艕顎�4执褠拢卢禄鹿藝使詢碌锟竭蹬愵��4执褠隆拢默蓮值藝DEFINER隆拢諝SQL:2003讗譄藝一赂锟劫愒÷Ｂ绰绰铰ㄖ熉或陡撃曔辟愳攭路膸葝 負鲁蛺蚝檨陋碌艎锟脚愵�壜÷Ｕ歁ySQL 5.1讗拢卢卤賽鞌怑XECUTE权袨虏苿輺麓褠負鲁蛺颉１賽鞊翟愔⒙革拷薜艙没搂要么藝露篓鹰譄拢卢要么藝碌锟竭Ｂ�症取戮锟絈L SECURITY蜆址藝骚潞蠅闂兟的÷�

MySQL麓娲�sql_mode系统卤鍋渴ㄗ兟Ｂ�症赂锟侥斲椨惩愹被绰绰铰�碌艎卤潞髧睏锟铰琈ySQL販藝强讍使詢症赂锟侥�麓执褠 負鲁蛺颉��

COMMENT負戮鍕囈宦革拷QL碌艀漏展拢卢碳驴蕭员禄詢4抹硕 麓娲⒙惩愹·曗苟褏息卤禄SHOW CREATE PROCEDURE潞藸SHOW CREATE FUNCTION辕戮鍊葱斒韭÷�

MySQL諍循負鲁蛺虮己卢DDL辕戮洌�骚CREATE潞虅ROP隆拢MySQL也諍循麓娲⒙惩愹（德�虏禄藝 麓娲⒙郝�私拢漏掳锟� 陆禄禄楼辕戮洌�骚COMMIT隆拢麓娲⒙郝�私虏禄驴蕭园锟铰┴赌啡仿的和撅拷汀陆禄禄髺牀锟斤拷铩�SQL卤霔悸猜灰�瘸露諘銗┰�戮涠勚�鲁郑卢SQL卤霔际鼓访柯革拷S汀鹿漏蕦驴蕭跃锟角夫晪愵枾鲁讜銗┰�戮洹�

麓娲⒇撀惩愹布勢娐乖僉OAD DATA INFILE隆拢

路碌禄亟岷宦帮拷洳慌溌甭辉冋毬存储潞炉私讝隆拢症掳锟铰乖僆NTO赂锟铰堵埪� 聬值碌脫ELECT辕戮洌琒HOW 辕戮洌�录掳扦碳桩骚EXPLAIN症夜碌艙锟ぢ÷Ｂ墩撢渴斱郝�私露篓鹰时录浔宦撅拷陋路碌禄賿禄赂锟斤拷辕戮洌�路垄屎一赂锟巾祿潞炉私麓顝陈仿德回结夯掳锟铰Ｂ‥R_SP_NO_RETSET_IN_FUNC拢漏隆拢露論蹡禄驴蕯蹟虗褗卤戮锟铰�路碌禄賿禄赂锟斤拷辕戮洌� 路垄屎一赂锟捷斱革拷袀蟿麓顝陈仿德回结夯掳锟紺EDURE %s 拢篓ER_SP_BADSELECT)隆拢

袀摩藝一赂锟铰廢T虏蠆锟铰ヂ的存储鲁蛺蚨厐锟斤拷卢諝 鲁蛺虮欢篓鹰碌艎卤潞颍瓝|strong>mysql驴突搂露胜elimiter募庐4掳覔锟ぢ堵�陆绺宦囱�;卤鍘�//隆拢症戮螖藧顢冋� 鲁蛺髰�栃德宦堵�陆绺宦甭宦绰�碌莸陆路锟斤拷卤禄mysql財录潞4陆銒嵚÷�

mysql> delimiter //

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)

    -> BEGIN

    ->   SELECT COUNT(*) INTO param1 FROM t;

    -> END

    -> //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL simpleproc(@a);

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a;

+------+

| @a   |

+------+

| 3    |

+------+

1 row in set (0.00 sec)

碌卤使詢delimiter募庐时拢卢牛应赂帽輧銑乖兟仿葱甭岗�‘\’)貣路锟轿�艊藝MySQL碌聧 转鹰貣路锟給:p>

袀聬藝一赂锟接Ｂ�一赂锟矫蚕婏拷锟铰宦革拷潞炉私执褠一赂锟斤拷垄路碌禄亟岷宦Ｂ�

mysql> delimiter //

mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)

    -> RETURN CONCAT('Hello, ',s,'!');

    -> //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> SELECT hello('world');

+----------------+

| hello('world') |

+----------------+

| Hello, world!  |

+----------------+

1 row in set (0.00 sec)

骚鹿锟铰绰⒙郝�私讗碌脪ETURN辕戮浞德毁捖宦革拷筒禄同詺諝潞炉私碌录/span>RETURNS負戮鍡愔嘎堵╜褝碌艝碌拢卢路碌禄贃碌卤禄强讍为恰碌卤碌艀釕嵚÷Ｂ鄙堢，骚鹿锟铰郝�私路碌禄賿禄赂锟組禄駫剶值拢卢碌芦藝RETURN辕戮浞德毁捖宦革拷锟劫揈T鲁蕯卤录炉碌艔釗β得匩UM鲁蕯卤拢卢麓雍炉私路碌禄氐艝碌藝貣路锟�

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]

characteristic:

    { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

  | SQL SECURITY { DEFINER | INVOKER }

  | COMMENT 'string'

症赂锟藉�売斅甭辉�4赂谋鍜宦革拷垄鲁蛺蚣埠炉私碌艑贂锟劫峺SQL 5.1讗拢卢牛卤賽鞌傾LTER ROUTINE权袨虏趴蕮么虠映蛺颉�曗苟权袨卤禄財露炉藲渊負鲁蛺蚨劥麓陆篓譄隆拢骚20.4陆冢卢隆掳麓娲⒇撀惩愹粛绰ヂ仿⒙惩愹秳讹拷謻戮鹿娄艤”讗坦硕拢卢 骚鹿锟阶喩曋韭孤ε溌甭徽娧�聥拢卢牛驴蕜輶虏穴要SUPER权袨隆拢

諝ALTER PROCEDURE潞虂LTER FUNCTION辕戮鍡惵Ｂ�驴蕭諙赂露篓鲁卢鹿锟侥改变。

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

症赂锟戒被詢4訂鲁锟芥储鲁蛺蚣埠炉私隆拢录麓拢卢麓臃锟狡筹拷贫篓碌艞映蛺颉�斮峺SQL 5.1讗拢卢牛卤賽鞌怉LTER ROUTINE权袨虏趴蕮么虠映蛺颉�曗苟权袨卤禄財露炉藲渊負鲁蛺蚨劥麓陆篓譄隆拢

IF EXISTS 負戮鍕囈宦革拷QL碌艀漏展隆拢骚鹿锟铰或喊婏拷垄拢卢碳路V鹿路垄屎麓顝陈÷Ｂ诧拷赂锟秸撀揌OW WARNINGS虏榭绰的韭�赂妗�

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

症赂锟藉媷一赂锟絈L碌艀漏展隆拢`虇詺SHOW CREATE TABLE拢卢碳路碌禄賿禄赂锟侥�麓讟褌麓麓陆篓討募幕 負鲁蛺蚨厛路葠貣路锟�

mysql> SHOW CREATE FUNCTION test.hello\G

*************************** 1. row ***************************

       Function: hello

       sql_mode:

Create Function: CREATE FUNCTION `test`.`hello`(s CHAR(20)) RETURNS CHAR(50)

RETURN CONCAT('Hello, ',s,'!')

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

症赂锟藉媷一赂锟絈L碌艀漏展隆拢碳路碌禄贄映蛺蚨厡贂锟借嫿戮菘猓�幕貣拢卢`褝拢卢麓麓陆篓譄录掳麓麓陆篓潞螑薷艌謫凇拢骚鹿锟街嘎堵ㄒ故铰Ｂ�赂锟绞乖兟蹬擄郡拢卢坦詯 麓娲⒙惩愹粠嬶拷麓垄潞炉私碌艕茝垄露录卤禄聬鲁锟給:p>

mysql> SHOW FUNCTION STATUS LIKE 'hello'\G

*************************** 1. row ***************************

           Db: test

         Name: hello

         Type: FUNCTION

      Definer: testuser@localhost

     Modified: 2004-08-03 15:29:37

      Created: 2004-08-03 15:29:37

Security_type: DEFINER

      Comment:

牛驴蕭源覊NFORMATION_SCHEMA讗碌脪OUTINES卤砑甭的撔关存储負鲁蛺蚨厫茝垄隆拢全虏蠑录a href="http://book.chinaz.com/mysql5/information-schema.html#routines-table" title="23.1.14. The INFORMATION_SCHEMA ROUTINES Table">23.1.14陆冢卢隆掳INFORMATION_SCHEMA ROUTINES 卤牖硃an>”隆拢

CALL sp_name([parameter[,...]])

CALL辕戮涠吩円宦革拷掳詢CREATE PROCEDURE麓麓陆篓碌某蛺颉��

CALL辕戮夂痵pan>驴蕭論M 使姆为OUT禄蚨儔NOUT虏蠆锟斤拷牡锟竭绰�禄贃碌隆拢碳也“路碌禄枝rdquo;藴影鞋碌艕褗锟铰�露顺蛺髝姃諗丶/span>SQL录露卤駦ü锟絪pan>ROW_COUNT()潞炉私禄穸剷夤端铰Ｂ�麓覂讗藝碌锟絟e mysql_affected_rows() C API潞炉私4禄穸儭拢

[begin_label:] BEGIN

    [statement_list]

END [end_label]

麓娲⒇撀惩愺�姃諍鹿詢BEGIN ... END赂麓潞袚锟�4掳锟斤拷洹�statement_list 麓锟铰革拷喙对�戮涠劼惵表。statement_list之艢每赂锟戒都卤賽鞌兟分号Ｂ�拢禄拢漏4陆鈳猜÷�

赂麓潞袚锟ぢ渴捲甭宦标絿隆拢鲁锟絙egin_label麓鐣�,路驎凹i>end_label虏禄艤卤禄赂锟铰⑷捝�鹿锟铰堵悸寸暁,袒膰卤賽鞁囃�夜碌摹拢

全注英拢卢驴蕬隆碌脹NOT] ATOMIC負戮鍚栒毬宦孤猜宦甭恢�鲁帧拢症英味貐諝指庐驴槎劼柯�始没詯陆禄禄楼碌拇娲⒙点被狮變拢卢虏垄葤諝蕪袀蟿讗詢碌陆碌脗EGIN負戮浞斅德鼻奥铰宦宦ヂ堵�胤没詯影鞋隆拢

使詢露釛樤�戮鍛ㄒ�驴突搂露虅芊垄虓掳锟戒定陆绺�;碌牟陸�貣路锟街⒙革拷茢蹆锟酵宦�露吮禄詢delimiter募庐4麓娄m隆拢赂谋涑┭�陆鈳猜堵�陆绺�;拢篓卤蓤绻劼卞帾//拢漏使碌{ 驴杀禄詢諝負鲁蛺髰�栃÷�

DECLARE辕戮浔辉�4掳巡禄同挟目戮讚虻緬禄赂魦嫍映蛺颍壕植驴卤鍋柯Ｂㄈ�虏蠑录a href="#variables-in-stored-procedures" title="20.2.9. Variables in Stored Procedures">20.2.9陆冢卢隆掳麓娲⒙惩愺棎的卞伩”)拢卢偷录锟铰�m鲁蛺颍﹪氤幷�20.2.10陆冢卢隆掳偷录锟巾磳巡”) 录掳鹿獠�拢篓全虏蠑录a href="#cursors" title="20.2.11. Cursors">20.2.11陆冢卢隆掳鹿獠�”)隆拢SIGNAL潞虙ESIGNAL辕戮涞鼻奥宦孤猜宦甭恢�鲁帧拢

DECLARE陆锟侥斮侲GIN ... END赂麓潞袚锟�o拢卢虏垄葤卤賽鞎毬嘎绰盒擄郡碌目陋头拢卢諝蓭潞蠁鍖荚�戮鍠�前隆拢

鹿獠�卤賽鞎毷鼓仿绰�m鲁蛺髺瘒掳卤禄使姆拢卢虏垄葤卤鍋柯何岋拷賽鞎毷鼓仿光勃禄虼��泶屟仓�前卤禄使姆隆拢

蜆露篓偷录锟截堵�麓娄m隆拢症些偷录锟铰�系碌陆麓顝陈Ｂ�訑录掳負鲁蛺髼惖艗禄掳鋫仿程抠柶÷�

DECLARE condition_name CONDITION FOR condition_value

condition_value:

    SQLSTATE [VALUE] sqlstate_value

  | mysql_error_code

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

handler_type:

    CONTINUE

  | EXIT

  | UNDO

condition_value:

    SQLSTATE [VALUE] sqlstate_value

  | condition_name

  | SQLWARNING

  | NOT FOUND

  | SQLEXCEPTION

  | mysql_error_code

mysql> CREATE TABLE test.t (s1 int,primary key (s1));

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //

mysql> CREATE PROCEDURE handlerdemo ()

    -> BEGIN

    ->   DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;

    ->   SET @x = 1;

    ->   INSERT INTO test.t VALUES (1);

    ->   SET @x = 2;

    ->   INSERT INTO test.t VALUES (1);

    ->   SET @x = 3;

    -> END;

    -> //

Query OK, 0 rows affected (0.00 sec)

mysql> CALL handlerdemo()//

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x//

    +------+

    | @x   |

    +------+

    | 3    |

    +------+

    1 row in set (0.00 sec)

录虻ス獠�諝麓娲⒙惩愹粛郝�私艢卤禄支鲁帧拢辕路篓骚同諝嵌色碌脫QL讗隆拢鹿獠�碌卤前藝虏禄拇赂械模卢只露u募掳虏禄鹿锟侥÷Ｂ猜荒绰秆掋帾路锟绞捲活不驴蕭愿麓讍碳碌慕岷宦表。

鹿獠�卤賽鞎毷鼓仿绰�m鲁蛺髺瘒掳卤禄使姆拢卢虏垄葤卤鍋柯何岋拷賽鞎毷鼓仿光勃禄虼��泶屟仓�前卤禄使姆隆拢

}骚拢潞

CREATE PROCEDURE curdemo()

BEGIN

  DECLARE done INT DEFAULT 0;

  DECLARE a CHAR(16);

  DECLARE b,c INT;

  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;

  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;

  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN cur1;

  OPEN cur2;

  REPEAT

    FETCH cur1 INTO a, b;

    FETCH cur2 INTO c;

    IF NOT done THEN

       IF b < c THEN

          INSERT INTO test.t3 VALUES (a,b);

       ELSE

          INSERT INTO test.t3 VALUES (a,c);

       END IF;

    END IF;

  UNTIL done END REPEAT;

  CLOSE cur1;

  CLOSE cur2;

END

DECLARE cursor_name CURSOR FOR select_statement

OPEN cursor_name

FETCH cursor_name INTO var_name [, var_name] ...

CLOSE cursor_name

• MySQL 5.1麓娲⒙惩愹粛郝�私露愿麓讍前胤詢冒拢驴 

  藝碌模卢諝麓娲⒙惩愹粛郝�私讗卤禄执褠卤霔佳愇�卤禄麓詵锟絈L路锟铰醋喡德铰从凤拷拢詯蕶私袨讍拢卢碳膰諝20.4陆冢卢隆掳麓娲⒇撀惩愹粙� 麓楼路垄鲁蛺蚍窘锟街韭孤ε溌÷�讗歇硕隆拢

• 諝追路锟较绰绰铰�碌拇娲⒙惩愹粛郝�私驴蕭员禄赂麓讍碌陆麓臃锟叫兟绰Ｂ�

  藝碌模卢通鹿锟紻L辕戮鍠囱惵的存储鲁蛺蚧嵑炉私拢卢扦諝追路锟较的绰绰铰�卤禄赂麓讍碌陆麓臃锟铰�坦訑目卤杲�麓鐣殅赂锟津嚫壪÷Ｂ对存储鲁蛺蚧嵑炉私碌脕LTER 潞虅ROP辕戮鍜猜甭宦嘎醋喡÷�

• 褠为骚潞蠑蹝迅麓讍碌拇娲⒙惩愹粛郝�私o路垄屎拢驴

  MySQL录蝹录每赂锟斤拷麓垄鲁蛺蚧嵑炉私o碌脛ML藗录锟铰醋喼⑿┞德ヂ�5艕褞陋碌陆麓臃锟铰Ｖ囱惵存储鲁蛺蚧嵑炉私碌艊褗碌碌锟铰宦甭宦嘎醋喡÷�

• 露諕禄前使詢麓娲⒙惩愹，郝�私潞透麓讍詯什么蜆卤鸲劙虏全要瘸么拢驴

  藝碌模卢硬为一赂锟斤拷褕篓袨4执褠蓭潞味聴諙锟津嚪的讹拷謻戮碌艙锟ぢＢ�指露篓碌陌虏全约烁硬垣赂麓讍一前使詢碌拇娲⒙惩愹粛郝�私露锟铰÷Ｉ�鹿锟铰或肪斤拷志麓魨�壭娗悸ぢ活秳拢篓为point-in-time禄指麓碌艅驴碌模漏拢卢艊么MySQL DBA 詯}赂锟铰�选挟驴蕬隆拢潞

  • 蓭潞蠌氪绰铰�麓娲⒙惩愹秴撁宦�卤賽氡宦嘎吃⊿UPER权袨隆拢
  • 胤为选毡拢卢一赂锟铰渴捳夐梼log_bin_trust_routine_creators系统卤鍋课�1拢卢碳陆芦禄鈺娧�詯卤霔糃REATE ROUTINE权袨碌艌虁麓麓麓陆篓一赂锟铰⒙惩愹粛郝�私隆拢
   
• 露愿麓讍麓娲⒙惩愹粛郝�私碌艕褞陋詯什么袨讍拢驴

  嵌色碌陆麓娲⒙惩愺棎的猜蝗仿堵�拢篓苔禄锟绞甭伙拷艤藠碌卤碌馗麓讍隆拢苔禄锟铰的结夯拢卢陆锟戒本褦拢卢藝牛驴蕯陇虏舛劼和猜慌溌甭蝗肥德刻偮÷的÷Ｓ猜此Ｂ�赂麓讍碌陆麓臃锟脚嬫己褠为陆芦虏禄禄峋敌迸囆┞诧拷追路锟较的÷Ｗ⒂⒙Ｂ� 使姆麓娲⒙惩愹疾郝�私为DETERMINISTIC禄髺爺佻og_bin_trust_routine_creators讗狮變系统卤鍋课�0 陆芦禄鈺娧�苔录麓值虏跅锟斤拷拢

  麓虓猓�时禄锟铰猜慌溦毬从凤拷袞賽虏锟接参�諝麓娲⒙惩愺棏嵚�鹿锟角娐乖兟的讹拷謻戮4录菉卤症夜碌艎卤禄锟剿嚶猜宦渴栙惵诧拷拢卢硬为赂枚锟街柭韭斤拷录DML藗录锟斤拷卤约烁隆拢

  禺潞螅瓟诖锟組L褠为拢篓骚麓魡翰鎵�拢漏讗路墙禄禄楼卤矸⑹郝搭彸拢卢赂梅墙禄禄楼卤顎壟溌韭瓃赂麓讍拢卢諝赂麓讍掳娑劼非铰宦宦ヂ鳖棎追路锟绞捲甭宦猜柯分地匆凪L褠为赂锟铰�藝硬为路垄屎碌艅歉锟襟，对从凤拷禄詯赂锟� 露院炉私碌脛ML褠为拢卢鹿陇胤雀陆芦卤禄詢IGNORE鹿丶锟铰囱惵Ｂ�訑卤鋽氄氉仿凤拷系录讉麓顝陈的革拷锟铰�虏垄葤虏禄禄岬甲偮搭彸碌母锟铰醋喡德铰从凤拷拢

   

• 蕪硕碌艔邧苹鈸靶琈ySQL胤 point-in-time禄指麓碌艅輥娄冒拢驴

  影鞋赂麓讍碌艒卢一袨讍禄鈸靶琾oint-in-time禄指麓隆拢

•  MySQL要囟什么4赂艜锟脚忂柷勜Ｂ�

  陆芦4路垄褠碌脥ySQL预菤詯一赂锟捷埪パ≌甭嘎醋喡改堢粠卤禄麓娄m拢潞

  •  禄锟铰句秳赂麓讍拢篓碌卤前实袞拢漏隆拢
  • 褠录露卤鸶禆疲篓碳陆芦陆饪短乖愓�袌抹硕碌艔邧疲漏隆拢
• 麓楼路垄鲁蛺蚍敻麓讍前胤詢么拢驴

  MySQL 5.1讗碌拇楼路垄鲁蛺蚧嵏麓讍谐諝麓蠓�婏拷锟斤拷禄夜鹿陇胤拢卢諝艊些咏圈讗拢卢通鹿锟酵愺暃栵拷驀笁袞麓褠碌艕褞陋虏禄卤禄赂麓讍碌陆麓臃锟铰Ｈ÷讹拷碌艎牵卢位詺追MySQL路锟侥鳖棎碌聧 麓楼路垄鲁蛺髴�捖�諝艊些麓鐣氃毶幝和峺SQL麓臃锟较的鳖厷卤禄麓麓陆篓拢卢訑卤鋽毬绰ヂ仿⒙惩愺�姃諕虏驴蕭諗诖臃锟较甭宦悸ぢ活。

   

•  一赂锟铰�骚潞蠉篓鹿锟斤拷细麓讍碌陆麓臃锟较的绰ヂ仿⒙惩愺�禆麓褠艠拢驴

  藯袌拢卢追路锟较的绰ヂ仿⒙惩愹矙愳暁麓臃锟叫栘铰�隆拢一碌漏讟陆篓聥拢卢赂麓讍路鲁叹螐魢�嬶拷陆赂麓讍讗碌谋霔糄ML辕戮鍜灰孤孤へ仿÷�}骚拢潞驴录脟一赂锟铰�虏鎵�麓楼路垄鲁蛺駙厰ER碌脜MP卤恚�碳位詺追MySQL路锟较÷Ｍ�夜碌脜MP卤砘岮FTER虏鎵� 麓楼路垄鲁蛺髵泊鐣氃毬从凤拷稀拢赂麓讍路鲁炭蕜輮牵潞

1.    露訁MP囟一赂锟紼RT辕戮洹�

2.   EMP蕪碌脕FTER麓楼路垄鲁蛺蚣せ睢�

3.    INSERT辕戮浔恍绰斤拷讍蓵志隆拢

4.    麓臃锟较的嘎醋喪扒癐NSERT辕戮涔窫MP卤恚�虏垄諝麓臃锟叫柭囱愄悸÷�

5.    位詺麓臃锟組P蕪碌录/span>AFTER麓楼路垄鲁蛺蚣せ睢�

·         赂麓讍碌幕锟阶仿凤拷垄虓掳锟斤拷謻戮o碌艎录锟斤拷卢麓臃锟铰囱愔⑿┧偮硷拷蕮敕斪仿凤拷齑壜蹬嵚�夜碌艎锟戒，全虏蠑录a href="http://book.chinaz.com/mysql5/replication.html#replication-implementation" title="6.2. Replication Implementation Overview">6.2陆冢卢隆掳赂麓讍赂茒锟絨uo;隆拢

·         蜆露篓碌艎锟铰绰糙楋拷陋使詢露锟街柭韭÷Ｂ甭嘎份蹬幠硷拷麓之潞螅�甭嘎份后綆偮悸的讹拷謻戮o碌艎录锟矫柭囱惵÷Ｖ⑿┧偮硷拷菘獾嘎从甭嘎份点秳蓵負麓锟角奥÷Ｈ�虏蠑录a href="http://book.chinaz.com/mysql5/database-administration.html#backup-recovery" title="5.9.2.2. Using Backups for Recovery">5.9.2.2陆冢卢隆掳使詢卤赂路莼指麓”隆拢

·         CREATE PROCEDURE, CREATE FUNCTION, ALTER PROCEDURE,潞虂LTER FUNCTION 辕戮浔恍绰斤拷讍蓵志拢卢CALL, DROP PROCEDURE, 潞虅ROP FUNCTION 也一夜隆拢

戮隆鹿輬绲嬄Ｂ�露愿麓讍詯一赂锟铰�掳碌示拢潞要麓麓陆篓一赂锟酵愹－撁宦�卤賽鞌怌REATE ROUTINE权袨,碌芦詯症赂锟睫蹬撁宦�虏禄艤写一赂魦嫍映蛺髸毚臃锟叫柭囱惿幝何糙楋拷髱珨诖臃锟较得換L袩鲁蛽膷雸�权袨4諎褠隆拢}骚拢卢骚鹿锟较鼻仿和从凤拷直駭惙锟紻值1潞瞬拢卢諝追路锟较蹬撁宦�驴蕜艽麓陆篓虏垄碌锟借悅一赂魦姵蛺颍笭

mysql> delimiter //

mysql> CREATE PROCEDURE mysp ()

    -> BEGIN

    ->   IF @@server_id=2 THEN DROP DATABASE accounting; END IF;

    -> END;

    -> //

mysql> delimiter ;

mysql> CALL mysp();

CREATE PROCEDURE潞虄ALL辕戮浣�卤禄写陆锟阶喩曋韭Ｂ�坦訑麓臃锟铰�执褠碳膰隆拢硬为麓覔QL袩鲁蛽褝雸�权袨拢卢碳陆芦訂鲁锟絥ting私戮菘狻�

要使諍循露锟街柭韭孤ε溌的凤拷輧銝⒙革拷眨卢MySQL 5.1討戮颅要瘸 麓娲⒙惩愹粛郝�私碌拇麓陆篓譄鲁锟铰Ｑㄒ�碌脙REATE ROUTINE碌艌篓袨微拢卢禄鹿卤賽鞌怱UPER 权袨隆拢`虇碌兀卢要使詢ALTER PROCEDURE禄駙嫈ER FUNCTION拢卢鲁锟紼R ROUTINE权袨微牛卤賽鞌怱UPER权袨隆拢没詯SUPER权袨拢卢陆芦禄岱⑹阂宦革拷螅讣/span>

ERROR 1419 (HY000): You do not have the SUPER privilege and

binary logging is enabled (you *might* want to use the less safe

log_bin_trust_routine_creators variable)

牛驴蕜懿禄蝎强讍要瘸負鲁蛺虼唇篓譄卤賽鞌怱UPER权袨隆拢}骚拢卢牛系统蕪坦詯詯CREATE ROUTINE权袨碌艙没搂驴蕜輮葥芯颅药碌艙娄詢鲁蛺蚩�仿⒅熉÷Ｒ�陆锟铰队揢PER权袨碌艗陋瘸拢卢狮變log_bin_trust_routine_creators 全戮讖碌统卤鍋课�1隆拢默蓮碌兀卢症赂锟铰恐滴�0拢卢碌芦牛驴蕭諒锟斤拷症夜拢潞

mysql> SET GLOBAL log_bin_trust_routine_creators = 1;

牛也驴蕭諗蹎锟斤拷庐时詢--log-bin-trust-routine-creators选挟4狮變諍循症赂锟铰柯÷�

骚鹿锟阶喩曋韭孤ε溌猜宦甭徽娧�拢卢log_bin_trust_routine_creators 没詯卤禄詢蕪拢卢負鲁蛺虼唇篓穴要SUPER权袨隆拢

·         一赂锟叫革拷葓路露篓負鲁蛺髬嚥禄驴蕱馗麓碌模卢碳艤詯}赂锟借摙碌艙掳鞋拢潞

要陆饪吨⑿┫娡⒙Ｂ琈ySQL强讍囟袀摩要瘸拢潞諝追路锟较Ｂ�鲁锟酵愹奔夛拷确露篓褦碌幕髺煵禄赂锟捷Ｂ�路驎泊麓陆篓禄髺爩婊回撀惩愹将甭宦拒撅拷英味貐碌卤牛麓麓陆篓一赂锟酵愹秴娐甭候－勩矘勋要么使姆碳藝确露篓褦碌模卢要么碳虏禄赂谋鍕铰据÷�}蜅負鲁蛺髰檿锟姐伅藠詢拢潞

默蓮碌兀卢要一赂锟紸TE PROCEDURE 禄馃倰EATE FUNCTION 辕戮浔宦皆娷Ｂ珼ETERMINISTIC 禄馃崗 SQL垣READS SQL DATA 讗碌艗禄赂锟届劮掳椎贃赂露篓拢卢路驎不岢菏荷�袀麓顝陈Ｂ�

ERROR 1418 (HY000): This routine has none of DETERMINISTIC, NO SQL,

or READS SQL DATA in its declaration and binary logging is enabled

(you *might* want to use the less safe log_bin_trust_routine_creators

variable)

骚鹿锟絣og_bin_trust_routine_creators 为1, 訂鲁锟酵愹矙愳媷确露篓碌幕虿紣薷艎锟铰�瘸隆拢

注英拢卢負鲁蛺虮繍缘艈9J腔锟铰铰ㄖ熉德�/span>“鲁袏碌露痞rdquo; 拢潞MySQL虏禄录斐┦鼓肺�确露篓褦碌艞映蛺髬嚪癫缓卢虏锟饺仿堵ㄑ斅结夯碌艙锟ぢ÷�

·         骚鹿锟窖猜仿德毁庌错，CALL辕戮浔恍绰斤拷讍蓵志拢卢路驎簿筒禄写隆拢碌卤一赂锟酵愺憺概婏拷輥耍卢牛禄岫兟德街⒁孤的韭�赂妫�

隆陇                ERROR 1417 (HY000): A routine failed and has neither NO SQL nor

隆陇                READS SQL DATA in its declaration and binary logging is enabled; if

隆陇                non-transactional tables were updated, the binary log will miss their

隆陇                changes

症赂锟街柭狙愇�潜諝碌氐录讉蠆廷.骚鹿锟截撀惩愹部分蒂愞概捖宦革拷禄禄楼卤恚�卤蓤钂宦革拷SAM卤鞝�le拢漏虏垄葤路碌禄賿禄赂锟襟，讹拷謻戮陆芦路麓映症些卤浠�隆拢要路V鹿症讝醛驴锟戒摝赂臄貚 負鲁蛺髼憡鹿詢陆禄禄楼卤聿⑷捳毬铰宦宦ヂ堵�胤艢褳赂谋怼�

諝一赂锟酵愺厷Ｂ�骚鹿锟絀NSERT, DELETE, 禄髺灂PDATEo使詢IGNORE鹿丶锟斤拷顝陈Ｂ�驴蕜芊垄屎一赂锟街革拷芦没詯麓顝陈诧拷症夜碌艙锟ぢ甭宦既偮忌曋韭Ｂ�葤纸鲁拢赂麓讍隆拢

·         骚鹿锟铰存储潞炉私諝一赂锟紼LECT症夜虏禄褳赂艎锟斤郡艢卤禄碌锟铰�录麓使潞炉私卤戮虱赂锟捷Ｂ�潞炉私碌艝麓褠也陆芦虏禄卤禄写陆锟阶喩曋緊隆拢症赂锟铰忌曋韭蹬愌幝�潜諝碌氐录讉蠆廷隆拢录趬韬�私myfunc()骚袀露篓鹰拢潞

隆陇                CREATE FUNCTION myfunc () RETURNS INT

隆陇                BEGIN

隆陇                  INSERT INTO t (i) VALUES(1);

隆陇                  RETURN 0;

隆陇                END;

掳麓謺蕪摩露篓鹰拢卢袀摩碌艙锟ぱ灺嘎約pan>卤齑Ｂ�硬为myfunc()褳赂谋觳� 碌芦藝辕戮洳宦甭恍绰斤拷讍蓵志拢卢硬为碳藝一赂锟紼CT辕戮洌�

SELECT myfunc();

露諘夤断娡⒙的孤へ啡嘎铰�碌锟桔楋拷碌艙锟�o囟赂锟铰�私隆拢注英拢卢踏然DO辕戮鍞愂蔽�聥扦鹿K悴�麓饜娊碌母卤效应露锟窖惵Ｂ珼O諝症o虏禄藝一赂锟斤拷卢硬为碳虏禄卤禄写陆锟阶喩曋韭÷�

·         諝一赂锟酵愺厸柭囱惵蹬擄郡虏禄卤禄写陆锟阶喩曋韭÷Ｂ稼堣叄路垄虏录袀聬辕戮洌�

隆陇                CREATE PROCEDURE mysp INSERT INTO t VALUES(1);

隆陇                CALL mysp;

露論蹠夤秨負4说拢卢CREATE PROCEDURE 潞虄ALL辕戮浯缎栒毬讹拷謻戮o拢卢碌芦INSERT辕戮洳⑽绰筹拷拢

·         諝麓臃锟较Ｂ�碌卤戮锟铰醋喤劼革拷諙锟津嚪蹬娐硷拷脕褟邧票禄应詢拢潞--replicate-*-table鹿鐣猜猜凰娫冊欳ALL辕戮浼藏撀惩愺厷蹬擄郡拢潞諝症些醛驴锟铰�販藝路碌禄旨/span>隆掳赂麓讍拢隆”