首页 > 代码库 > Mysql存储过程知识，案例

Mysql存储过程知识，案例

2024-07-06 18:34:04   222人阅读

Mysql存储过程知识，案例：

?
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
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


联系
我们