mysql函数与存储过程编写范例

整理文档很辛苦,赏杯茶钱您下走!

免费阅读已结束,点击下载阅读编辑剩下 ...

阅读已结束,您可以下载文档离线阅读编辑

资源描述

范例一:#################################################需求:向表t1插入一百万条数据#################################################dropPROCEDUREp16;DELIMITER//---声明分隔符,mysql默认的分隔符是分号。createprocedurep16()begindeclarevint;setv=1;LOOP_LABLE:loopINSERTintot1VALUES(v);setv=v+1;ifv=1000000thenleaveLOOP_LABLE;endif;endloop;end;//DELIMITER;--还原分隔符。执行该过程:callp16();范例二:#################################################需求:创建一个可以生成随机字符串的函数#################################################DROPFUNCTIONIFEXISTSrand_string;delimiter//CREATEFUNCTIONrand_string(l_numtinyintUNSIGNED,l_typetinyintUNSIGNED)RETURNSvarchar(127)BEGIN--Function:rand_string--Author:victdba@hotmail.com--Date:2015/5/11--l_num:Thelengthofrandomstring--l_type:Thestringtype--1.0-9--2.a-z--3.A-Z--4.a-zA-Z--5.0-9a-zA-Z--forexample:--mysqlselectrand_string(12,5)random_string;--+---------------+--|random_string|--+---------------+--|3KzGJCUJUplw|--+---------------+--1rowinset(0.00sec)DECLAREiintUNSIGNEDDEFAULT0;DECLAREv_charsvarchar(64)DEFAULT'0123456789';DECLAREresultvarchar(255)DEFAULT'';IFl_type=1THENSETv_chars='0123456789';ELSEIFl_type=2THENSETv_chars='abcdefghijklmnopqrstuvwxyz';ELSEIFl_type=3THENSETv_chars='ABCDEFGHIJKLMNOPQRSTUVWXYZ';ELSEIFl_type=4THENSETv_chars='abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';ELSEIFl_type=5THENSETv_chars='0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';ELSESETv_chars='0123456789';ENDIF;WHILEil_numDOSETresult=concat(result,substr(v_chars,ceil(rand()*(length(v_chars)-1)),1));SETi=i+1;ENDWHILE;RETURNresult;END;//delimiter;范例三:#################################################需求:利用上面的随机函数,创建一个可以向表中插入随机数据的存储过程#################################################dropprocedurepro_test;DELIMITER//createprocedurepro_test(invint)beginDECLAREiintUNSIGNEDDEFAULT1;LOOP_LABLE:loopINSERTintot1VALUES(i,rand_string(30,2));seti=i+1;ifi=vthenleaveLOOP_LABLE;endif;endloop;end;//delimiter;执行该存储过程让一个空表生成1千万条数据:callpro_test(10000000);范例四:##########################################################需求:更新一张表,更新之前表的数据如下:mysqlselect*froms1;+----+--------------+----------+------+|id|address|province|city|+----+--------------+----------+------+|1|广东深圳|NULL|NULL||2|湖南长沙|NULL|NULL||3|湖北武汉|NULL|NULL||4|广西桂林|NULL|NULL||5|香港|NULL|NULL||6|美国|NULL|NULL|+----+--------------+----------+------+更新之后,表的数据如下:mysqlselect*froms1;+----+--------------+----------+--------+|id|address|province|city|+----+--------------+----------+--------+|1|广东深圳|广东|深圳||2|湖南长沙|湖南|长沙||3|湖北武汉|湖北|武汉||4|广西桂林|广西|桂林||5|香港|NULL|NULL||6|美国|NULL|NULL|+----+--------------+----------+--------+##########################################################dropprocedurepro_splitdata;DELIMITER//createprocedurepro_splitdata()begin#declarevariableDECLAREv_idbigintDEFAULTnull;DECLAREv_address,vv_addressVARCHAR(30)DEFAULTnull;DECLAREdoneINTDEFAULTFALSE;#declarecursorDECLAREcur1CURSORFORselectidfroms1;#declarehandleDECLARECONTINUEHANDLERFORNOTFOUNDSETdone=TRUE;#opencursorOPENcur1;#startstheloopthe_loop:LOOPFETCHcur1INTOv_id;IFdoneTHENLEAVEthe_loop;ENDIF;SET@array_content=广东广西湖南湖北;SET@i=1;#得出数组成员总数SET@count=CHAR_LENGTH(@array_content)-CHAR_LENGTH(REPLACE(@array_content,'',''))+1;WHILE@i=@countDOsetvv_address=SUBSTRING_INDEX(SUBSTRING_INDEX(@array_content,'',@i),'',-1);selectaddressintov_addressfroms1whereid=v_idandaddresslikeconcat(vv_address,'%');setdone=FALSE;IFv_addressisnotnullTHENupdates1setprovince=vv_address,city=trim(vv_addressfromv_address)whereid=v_id;setv_address=null;ENDIF;SET@i=@i+1;ENDWHILE;ENDLOOPthe_loop;CLOSEcur1;end;//delimiter;执行存储过程完成表的更新:callpro_splitdata();

1 / 5
下载文档,编辑使用

©2015-2020 m.777doc.com 三七文档.

备案号:鲁ICP备2024069028号-1 客服联系 QQ:2149211541

×
保存成功