本文共 2684 字，大约阅读时间需要 8 分钟。
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 | #开山函数 hello world delimiter $$ create function test() returns varchar (20) begin return 'hello world!' ; end $$ delimiter ; #小测试 delimiter $$ create function func1() returns varchar (20) begin if hour (now())>=18 then return 'late' ; else return 'zao' ; end if; end $$ delimiter ; #全局变量 delimiter $$ create function func2() returns int begin set @i = 1; set @ sum = 0; while @i<=10 do set @ sum = @ sum + @i; set @i = @i + 1; end while; return @ sum ; end $$ delimiter ; delimiter $$ create function func3() returns int begin set @i = 1; set @ sum = 0; w:while @i<=10 do if @i = 5 then leave w; end if; set @ sum = @ sum + @i; set @i = @i + 1; end while w; return @ sum ; end $$ delimiter ; delimiter $$ create function func6() returns int begin set @i = 0; set @ sum = 0; w:while @i<10 do set @i = @i + 1; if @i = 5 then iterate w; end if; set @ sum = @ sum + @i; end while w; return @ sum ; end $$ delimiter ; #参数方式，局部变量 delimiter $$ create function hello( name varchar (10)) returns varchar (20) begin return concat( 'hello' , name ); end $$ delimiter ; #定义局部变量，注意mysql的跳出是需要指定循环的。 delimiter $$ create function func8() returns int begin declare i int default 0; declare total int default 0; w:while i<10 do set i = i + 1; if i = 5 then iterate w; end if; set total = total + i; end while w; return total; end $$ delimiter ; #以下写一个存储过程，生成一张任意条记录的表。 #生成随机字符串 delimiter $$ create function rand_string(n int ) returns varchar (255) begin declare chars_str varchar (100) default 'abcdefghigklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ' ; declare return_str varchar (255) default '' ; declare i int default 0; while i<n do set return_str = concat(return_str, substring (chars_str,floor(1+rand()*52),1)); set i = i + 1; end while; return return_str; end delimiter ; #生成随机数字 delimiter $$ create function rand_num() returns int (5) begin declare i int default 0; set i = floor(10+rand()*500); return i; end $$ delimiter ; #定义存储过程 delimiter $$ create procedure insert_emp(start int (10),max_num int (10)) begin declare i int default 0; set autocommit = 0; repeat set i = i+1; insert into emp values ((start+i),rand_string(6), 'SALESMAN' ,0001,2000,400,rand_num(),curdate()); until i = max_num end repeat; end |
本文转自shayang8851CTO博客，原文链接：http://blog.51cto.com/janephp/1288070，如需转载请自行联系原作者