Mysql存储过程知识，案例

浏览数：23 / 时间：2015年06月12日

Mysql存储过程知识，案例：

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

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

create procedure delete_setting(in 
p_settingid integer)
begin
    delete
    from    setting
    where   settingid=p_settingid;
end
 
select `name` from mysql.proc where db = ‘your_db_name‘ 
and `type` = ‘PROCEDURE‘
 
 
 
show procedure status;
 
show create procedure proc_name;
 
show create function 
func_name;
 
局部变量
 
create  procedure test3(out number1 integer)
begin
declare number2 integer default 
100;
set number1=number2;
end
 
call test(@number)
 
select @number#
 
默认值为标量子查询
 
create procedure test2(out ta integer)
begin
declare tt integer default 
(select count(*) from `setting`);
set ta=tt;
end
 
流程控制
 
IF
 
create procedure getMax(in 
p1 integer,in 
p2 integer,out p3 integer)
begin
    if 
p1>p2 then
        set p3 = p1;
    elseif p1<p2 then
        set p3 = p2;
    else
        set p3=0;
    end if;
end
 
 
fibonnaci
 
create procedure fibonnaci(inout n1 integer,inout n2 integer,inout n3 integer)
begin
set n3=n1+n2;
if 
n3>1000 then
    set n3 = n3-1000;
end if;
set n1=n2;
set n2=n3;
end
 
create procedure largest(out t char(30))
begin
    if 
(select count(*) from product)>(select count(*) from productcategory) then
        set t="product";
    elseif (select count(*) from product)<(select count(*) from productcategory) then
        set t="productcategory";
    else
        set t = "equal";
    end if;
end
 
 
create procedure small_exit(out p1 integer,out p2 integer)
begin 
    set p1 = 1;
    set p2 = 2;
    block1:begin
        leave block1;
        set p2 = 3;
    end;
    set p1=4;
end
 
WHILE  计算两个相差的年月日
 
create procedure age(
        in 
start_date date,
        in 
end_date   date,
        out years integer,
        out monthes integer,
        out days integer
        )
begin
    declare next_date,previous_date date;
     
    set years = 0;
    set previous_date=start_date;
    set next_date =start_date+interval 1 year;
    while 
next_date<end_date do
        set years = years+1;
        set previous_date = next_date;
        set next_date = next_date+ interval 1 year;
    end while;
     
    set monthes = 0;
    set next_date = previous_date + interval 1 month;
    while 
next_date<end_date do
        set monthes = monthes +1;
        set previous_date=next_date;
        set next_date = previous_date+interval 1 month;
    end while;
end
 
————————————————————————
调用存储过程
建表
create table players_with_parents
(playerno   integer not null 
primary key,
 father_playerno integer,
 mother_playerno integer);
 
alter table players_with_parents add constraint fk_1 
foreign key (father_playerno) references player_with_parents (playerno);
 
alter table players_with_parents add
foreign key (mother_playerno) references player_with_parents(playerno);
 
增加外键约束；
 
insert into players_with_parents values(9,null,null),(8,null,null),(7,null,null),(6,null,null),(5,null,null)
,(4,8,9),(3,6,7),(2,4,5),(1,2,3)
 
create procedure total_number_of_parents(in_playerno integer,inout number integer)
begin
    declare v_father,v_mother integer;
    set v_father = 
    (select father_playerno from players_with_parents where playerno=in_playerno);
 
    set v_mother = 
    (select mother_playerno from players_with_parents where playerno=in_playerno);
     
    if 
v_father is not null 
then
        call total_number_of_parents(v_father,number);
        set number = number+1;
    end if;
     
    if 
v_mother is not null 
then
        call total_number_of_parents(v_mother,number);
        set number = number+1;
    end if;
end
 
set max_sp_recursion_depth=100; 
 
select into 
 
create procedure total_setting(out p1 integer)
begin
    select count(*) into p1 from `setting`;
end
 
create procedure get_setting(out v_name varchar(60),out v_value varchar(60))
begin
    select `name`,`value` into v_name,v_value from setting limit 0,1;
end
 
create procedure showErr(out p integer)
begin
    set p=1;
    insert into `setting` (`settingid`,`name`)values(12,‘vname‘);
    set p=2;
end
 
create procedure showErr(out p integer,out error integer)
begin
    declare continue 
handler for 
sqlstat ‘23000‘
        set error = ‘200000‘;
    declare continue 
handler for 
sqlstat ‘23001‘;
        set error = ‘200001‘;
    set error = ‘000000‘;
    set p=1;
    insert into `setting` (`settingid`,`name`)values(12,‘vname‘);
    set p=2;
end
 
declare continue 
handler for 
1062 set error=‘23000‘;
declare continue 
handler for 
1136 set error=‘21S01‘;
 
01 SQLWARING
02 NOT FOUND
SQLEXCEPTION 01,02以外的
 
create procedure addDate(out error char(5))
begin
    declare continue 
handler for 
sqlstate ‘23000‘ 
set error=‘23000‘;
    declare continue 
handler for 
sqlstate ‘21S01‘ 
set error=‘21S01‘;
    set error=‘00000‘;
    insert into `setting` values(15,‘test‘,‘testValue‘,10);
end
 
declare non_unique condition for 
sqlstate ‘23000‘;
ddeclare continue 
handler for 
non_unique set error=‘23000‘;
 
同一块中，相同的错误，不能有两个或多个处理程序；
 
带有游标的存储过程
declare c_players cursor for 
select playerno from players;
 
create procedure number_of_players(out number integer)
begin
    declare a_playerno integer;
    declare found boolean default 
true;
    declare c_players cursor for 
select playerno from players;
    declare continue 
handler for 
not found set found=false;
    set number=0;
    open c_players;
    fetch c_players into a_playerno;
    while 
found do
        set number =number+1;
        fetch c_players into a_playerno;
    end while
    close c_players;
end
 
 
create procedure number_of_setting(out number integer)
begin
    declare a_settingid integer;
    declare found boolean default 
true;
    declare c_settingids cursor for 
select settingid from setting;
    declare continue 
handler for 
not found set found=false;
    set number=0;
    open c_settingids;
    fetch c_settingids into a_settingid;
    while   
found do
        set number=number+1;
        fetch c_settingids into a_settingid;
    end while;
    close c_settingids;
end
 
create procedure delete_older_than_30()
begin
    declare v_age,v_playerno,v_years,v_months,v_days integer;
    declare v_birth_date date;
    declare found boolean default 
true;
    declare c_players cursor for
        select playerno,birth_date from players;
    declare continue 
handler for 
not found set found=false;
    open c_players;
    fetch c_players into v_playerno,v_birth_date;
    while 
found do
        call age(v_birth_date,now(),v_years,v_months,v_days);
        if 
v_years>30 then
            delete 
from penalties where playerno=v_playerno;
        end if;
        fetch c_players into v_playno,v_birth_date;
    end while;
    close c_players;
end
 
commit,rollback,start transaction;
 
 
create function 
test(t1 integer,t2 integer) returns integer
begin
    return 
t1+t2;
end