MySQL教程彻底学懂存储过程

1銆佸瓨鍌ㄨ繃绋嬬殑鐩稿叧姒傚康

浠�涔堟槸瀛樺偍杩囩▼锛?br /> 绫讳技浜巎ava涓�鐨勬柟娉曪紝python涓�鐨勫嚱鏁般�?/p>

浣跨敤瀛樺偍杩囩▼鐨勫ソ澶勶細
1銆佹彁楂樹唬鐮佺殑閲嶇敤鎬э紱
2銆佺畝鍖栨搷浣滐紱
3銆佸噺灏戜簡缂栬瘧娆℃暟锛屽苟涓斿噺灏戜簡鍜屾暟鎹�搴撴湇鍔″櫒鐨勮繛鎺ユ�℃暟锛屾彁楂樹簡鏁堢巼銆傛敞鎰忥細骞虫椂姣忔墽琛屼竴鍙�sql璇�鍙ワ紝灏变細杩炴帴mysql鏈嶅姟鍣ㄤ竴娆★紱

瀛樺偍杩囩▼鐨勫惈涔夛細
涓�缁勯�勫厛缂栬瘧濂界殑SQL璇�鍙ョ殑闆嗗悎銆?/p>

2銆佸瓨鍌ㄨ繃绋嬬殑鐢ㄦ硶

1锛夊瓨鍌ㄨ繃绋嬬殑鍒涘缓璇�娉�

create procedure 瀛樺偍杩囩▼鍚?鍙傛暟鍒楄〃)
begin
     瀛樺偍杩囩▼浣?涓�缁勫悎娉曟湁鏁堢殑sql璇�鍙�)
end

2锛夊�瑰垱寤鸿��娉曠殑鐞嗚�?/h3>

鈶?鍙傛暟鍒楄〃锛氬弬鏁板垪琛ㄥ寘鍚?閮ㄥ垎

鍙傛暟妯″紡   鍙傛暟鍚?  鍙傛暟绫诲瀷

渚嬪�傦細in  stuname  varchar(20)

鈶?鍙傛暟妯″紡鍒嗙被

Win7待机不断网在空闲时不让其断网的设置方法	视觉中国为什么说自己是图片销售者，而不是维权者？
相机中的画面比例你选对了吗？	『摄影教程』七种必须掌握的简单人像布光技巧

in锛氳�ュ弬鏁板彲浠ヤ綔涓鸿緭鍏ワ紝涔熷氨鏄�璇ュ弬鏁帮紝闇�瑕佽皟鐢ㄦ柟浼犲叆鍊笺�?/p>

out锛氳�ュ弬鏁板彲浠ヤ綔涓鸿緭鍑猴紝涔熷氨鏄�璇ュ弬鏁帮紝鍙�浠ヤ綔涓鸿繑鍥炲�笺�?/p>

inout锛氳�ュ弬鏁版棦鍙�浠ヤ綔涓鸿緭鍏ュ張鍙�浠ヤ綔涓鸿緭鍑猴紝涔熷氨鏄�璇ュ弬鏁版棦闇�瑕佷紶鍏ュ�硷紝鍙堝彲浠ヨ繑鍥炲�笺�?/p>

鈶?濡傛灉瀛樺偍杩囩▼浣撲粎浠呭彧鏈変竴鍙ヨ瘽锛宐egin/end鍙�浠ョ渷鐣ャ�?/p>

鈪?瀛樺偍杩囩▼浣撲腑鐨勬瘡鏉�sql璇�鍙ョ殑缁撳熬锛岃�佹眰蹇呴』鍔犲垎鍙枫�?br /> 鈪?瀛樺偍杩囩▼鐨勭粨灏撅紝鍙�浠ヤ娇鐢╠elimiter閲嶆柊璁剧疆锛屽惁鍒欓兘鐢?;"浼氶�犳垚娣锋穯銆?br /> 璇�娉曪細delimiter 缁撴潫鏍囪��
渚嬪�傦細delimiter $  -- 琛ㄧず浠?浣滀负缁撳熬绗﹀彿銆?/p>

鈶?瀛樺偍杩囩▼鐨勮皟鐢ㄨ��娉?/p>

call 瀛樺偍杩囩▼鍚?瀹炲弬鍒楄〃);

3.绌哄弬鐨勫瓨鍌ㄨ繃绋?/h2>

"瀛樺偍杩囩▼鐨勬暣涓�鎵ц�岃繃绋嬶紝鏈�濂藉湪CMD绐楀彛涓�鎵ц�?
-- 鍒涘缓涓�涓�瀛樺偍杩囩�?
delimiter $
create procedure myp()
begin
    	insert into admin(username,`password`)
    	values ("tom","1111"),("jerry","2222"),
    	("jalen","3333"),("rose","4444"),("tonny","5555");
end $
-- 璋冪敤瀛樺偍杩囩▼
call myp()$
-- 鏌ョ湅缁撴灉銆?
select * from admin$

缁撴灉濡備笅锛?/p>

4.甯�in妯″紡鐨勫瓨鍌ㄨ繃绋?/h2>

带上口罩才看清移动、联通、电信的真面目，错怪你们了！	3分钟学会安装Win11 带你超越80%用户
微信撤回消息为何还要提示对方？腾讯解答真相	做手游错过最佳时机，一年收入17亿的英雄联盟，打不过王者荣耀？

1锛夋�堜緥锛氬垱寤哄瓨鍌ㄨ繃绋嬪疄鐜帮紝鏍规嵁閮ㄩ棬缂栧彿锛屾煡璇㈠�瑰簲鐨勯儴闂ㄥ悕绉般�?/p>

-- 鍒涘缓涓�涓�瀛樺偍杩囩�?
delimiter $
create procedure myp2(in num int)
begin
    select e.ename,d.dname
    from emp e 
    left join dept d on e.deptno=d.deptno
    where e.deptno=num;
end $
-- 璋冪敤瀛樺偍杩囩▼
call myp2(10)$

缁撴灉濡備笅锛?/p>

2锛夋�堜緥锛氬垱寤轰竴涓�瀛樺偍杩囩▼瀹炵幇锛岀敤鎴锋槸鍚︾櫥闄嗘垚鍔熴�?/p>

鎿嶄綔濡備笅锛?/p>

-- 鍒涘缓涓�涓�瀛樺偍杩囩�?
delimiter $
create procedure myp3(in username varchar(10),password varchar(10))
begin 
    declare result int;
    select count(*) into result
    from admin ad
    where ad.username=username
    and ad.password=password;
    select if(count(*) > 0,"鐧婚檰鎴愬姛","鐧婚檰澶辫触") 鐧婚檰鐘舵�?
end $
-- 璋冪敤瀛樺偍杩囩▼
call myp3('john','8888');

缁撴灉濡備笅锛?/p>

5.甯�out妯″紡鐨勫瓨鍌ㄨ繃绋?/h2>

1锛夋�堜�?锛氬垱寤轰竴涓�瀛樺偍杩囩▼锛屾牴鎹�濂崇�炲�撳悕锛岃繑鍥炲�瑰簲鐨勭敺绁炲�撳悕

“熬夜猝死”的元凶不是大脑，居然是它	李佳琦“底价协议”事件是否涉嫌垄断？
人脸识别被破解，手机里的钱还安全吗	为什么MacBook Pro看起来厚了？

-- 鍒涘缓涓�涓�瀛樺偍杩囩�?
delimiter $
create procedure myp4(in beautyName varchar(20),out boyName varchar(20))
begin 
    select b.boyName into boyName
    from beauty left join boys b
    on beauty.boyfriend_id=b.id
    where beauty.name=beautyName;
end $
-- 璋冪敤
# 閲嶆柊瀹氫箟涓�涓�鍙橀噺@boyname鎺ユ敹杩斿洖鍊糱oyName銆?
call myp4("璧垫晱",@boyname)$
select @boyname$

call myp4("鏌冲博",@boyname)$
select @boyname$

缁撴灉濡備笅锛?/p>

2锛夋�堜�?锛氬垱寤轰竴涓�瀛樺偍杩囩▼锛屾牴鎹�濂崇�炲悕锛岃繑鍥炲�瑰簲鐨勭敺绁炲悕鍜岀敺绁為瓍鍔涘�?/p>

-- 鍒涘缓涓�涓�瀛樺偍杩囩�?
delimiter $
create procedure myp5(in beautyName varchar(20),out boyName varchar(20),out userCP int)
begin 
    select b.boyName,b.userCP into boyName,userCP
    from beauty left join boys b
    on beauty.boyfriend_id=b.id
    where beauty.name=beautyName;
end $
-- 璋冪敤
# 閲嶆柊瀹氫箟涓�涓�鍙橀噺@boyname鎺ユ敹杩斿洖鍊糱oyName銆?
call myp5("璧垫晱",@boyname,@usercp)$
select @boyname,@usercp$

call myp5("鏌冲博",@boyname,@usercp)$
select @boyname,@usercp$

缁撴灉濡備笅锛?/p>

6.甯�inout妯″紡鐨勫瓨鍌ㄨ繃绋?/h2>

1锛夋�堜�?锛氫紶鍏�a鍜宐涓や釜鍊硷紝鏈�缁坅鍜宐閮界炕鍊嶅苟杩斿洖銆?/p>

-- 鍒涘缓涓�涓�瀛樺偍杩囩�?
delimiter $
create procedure myp6(inout a int ,inout b int)
begin 
    -- 灞�閮ㄥ彉閲忎笉鐢ㄥ姞@绗﹀彿銆?
    set a=a*2;
		set b=b*2;
end $
-- 璋冪敤
-- 鐗瑰埆娉ㄦ剰璋冪敤杩欎竴鍧楀効銆?
set @m=10$
set @n=20$
call myp6(@m,@n)$
select @m,@n$

缁撴灉濡備笅锛?/p>

7.瀛樺偍杩囩▼鐨勬�堜緥瑙ｆ�?/h2>

1锛夊垱寤哄瓨鍌ㄨ繃绋嬫垨鍑芥晥锛屽疄鐜颁紶鍏ョ敤鎴峰悕鍜屽瘑鐮侊紝鎻掑叆鍒癮dmin琛ㄤ腑銆?/p>

2锛夊垱寤哄偍杩囩▼鎴栧嚱鏁帮紝瀹炵幇浼犲叆濂崇�炵紪鍙凤紝杩斿洖濂崇�炲悕绉板拰濂崇�炵數璇濄�?/p>

3锛夊垱寤哄瓨鍌ㄥ瓨鍌ㄨ繃绋嬫垨鍑芥暟锛屽疄鐜颁紶鍏ヤ袱涓�濂崇�炵敓鏃ワ紝杩斿洖澶у皬銆?/p>

1锛夊垱寤哄瓨鍌ㄨ繃绋嬫垨鍑芥晥锛屽疄鐜颁紶鍏ョ敤鎴峰悕鍜屽瘑鐮侊紝鎻掑叆鍒癮dmin琛ㄤ腑銆?/p>

-- 鍒涘缓涓�涓�瀛樺偍杩囩�?
delimiter $
create procedure pro1(in username varchar(20),in userpwd varchar(20))
begin 
    insert into admin(username,`password`)
    values (username,userpwd);
end $

-- 璋冪敤瀛樺偍杩囩▼
call pro1("椴佹櫤娣?,"123abc")$
select * from admin$

缁撴灉濡備笅锛?/p>

2锛夊垱寤哄偍杩囩▼鎴栧嚱鏁帮紝瀹炵幇浼犲叆濂崇�炵紪鍙凤紝杩斿洖濂崇�炲悕绉板拰濂崇�炵數璇濄�?/p>

-- 鍒涘缓涓�涓�瀛樺偍杩囩�?
delimiter $
create procedure pro2(in id int,out beautyName varchar(20),out beautyPhone varchar(20))
begin
    select beauty.name,beauty.phone into beautyName,beautyPhone
    from beauty
    where beauty.id=id;
end $

-- 璋冪敤瀛樺偍杩囩▼
call pro2(2,@beautyname,@beautyphone)$
select @beautyname,@beautyphone$

call pro2(3,@beautyname,@beautyphone)$
select @beautyname,@beautyphone$

缁撴灉濡備笅锛?/p>

3锛夊垱寤哄瓨鍌ㄥ瓨鍌ㄨ繃绋嬫垨鍑芥暟锛屽疄鐜颁紶鍏ヤ袱涓�濂崇�炵敓鏃ワ紝杩斿洖澶у皬銆?/p>

-- 鍒涘缓涓�涓�瀛樺偍杩囩�?
delimiter $
create procedure pro3(in borndate1 datetime,in borndate2 datetime,out result int)
begin
    select datediff(borndate1,borndate2) into result;
end $

-- 璋冪敤瀛樺偍杩囩▼
call pro3("1993-8-12",now(),@result)$
select @result$

缁撴灉濡備笅锛?/p>

8.瀛樺偍杩囩▼鐨勫垹闄?/h2>

drop procedure 瀛樺偍杩囩▼鍚嶏紱

9.鏌ョ湅鏌愪釜瀛樺偍杩囩▼鐨勪俊鎭?/h2>

10.瀛樺偍杩囩▼妗堜緥鑰冩牳

1锛夊垱寤哄瓨鍌ㄨ繃绋嬫垨鍑芥暟瀹炵幇浼犲叆濂崇�炲悕绉帮紝杩斿洖锛氬コ绁濧ND鐢风�烇紝鏍煎紡鐨勫瓧绗︿�?br />    濡備紶鍏ワ細灏忔槶
   杩斿洖锛氬皬鏄瑼ND寮犳棤蹇?br /> 2锛夊垱寤哄瓨鍌ㄨ繃绋嬫垨鍑芥暟锛屾牴鎹�浼犲叆鐨勬潯鐩�鏁板拰璧峰�嬬储寮曪紝鏌ヨ��beauty琛ㄧ殑璁板綍銆?/p>

1锛夊垱寤哄瓨鍌ㄨ繃绋嬫垨鍑芥暟锛屽疄鐜颁紶鍏ュコ绁炲悕绉帮紝杩斿洖锛氬コ绁濧ND鐢风�烇紝鏍煎紡鐨勫瓧绗︿覆銆?/p>

delimiter $
create procedure test1(in beautyname varchar(20))
begin
    select concat(beauty.name,"AND",boys.boyName)
    from beauty left join boys
    on beauty.boyfriend_id=boys.id
    where beauty.name=beautyname;
end $

call test1("鏌冲博")$
call test1("璧垫晱")$

缁撴灉濡備笅锛?/p>

2锛夊垱寤哄瓨鍌ㄨ繃绋嬫垨鍑芥暟锛屾牴鎹�浼犲叆鐨勬潯鐩�鏁板拰璧峰�嬬储寮曪紝鏌ヨ��beauty琛ㄧ殑璁板綍銆?/p>

delimiter $
create procedure test2(in startIndex int,in length int)
begin
    select *
    from beauty
    limit startIndex,length;
end $
-- 姣忛〉鏄剧ず3鏉¤�板�?
-- 鏄剧ず绗?椤?
call test2(3,3)$
-- 鏄剧ず绗?椤?
call test2(6,3)$

缁撴灉濡備笅锛?/p>

浠ヤ笂灏辨槸MySQL绯诲垪褰诲簳瀛︽噦瀛樺偍杩囩▼鐨勮�︾粏鍐呭�癸紝鏇村�氬叧浜嶮ySQL瀛樺偍杩囩▼鐨勮祫鏂欒�峰叧娉ㄨ剼鏈�涔嬪�跺叾瀹冪浉鍏虫枃绔狅�?/p>