璇﹁Вphp璋冪敤MySQL瀛樺偍杩囩▼鏂规硶_PHP鏁欑▼ | 甯涔嬪
1銆傝皟鐢ㄥ瓨鍌ㄨ繃绋嬬殑鏂规硶銆?/p>
a銆傚鏋滃瓨鍌ㄨ繃绋嬫湁 IN/INOUT鍙傛暟锛屽0鏄庝竴涓彉閲忥紝杈撳叆鍙傛暟缁欏瓨鍌ㄨ繃绋嬶紝璇ュ彉閲忔槸涓€瀵癸紝涓€涓猵hp鍙橀噺锛堜篃鍙互涓嶅繀锛屽彧鏄病鏈塸hp鍙橀噺鏃讹紝娌℃湁鍔炴硶杩涜鍔ㄦ€佽緭鍏ワ級锛屼竴涓狹ysql鍙橀噺銆?/p>
b銆傚鏋滃瓨鍌ㄨ繃绋嬫湁OUT鍙橀噺锛屽0鏄庝竴涓狹ysql鍙橀噺銆俶ysql鍙橀噺鐨勫0鏄庢瘮杈冪壒娈婏紝蹇呴』璁﹎ysql鏈嶅姟鍣ㄧ煡閬撴鍙橀噺鐨勫瓨鍦紝鍏跺疄涔熷氨鏄墽琛屼竴鏉ysql璇彞銆傚叆set @mysqlvar=$phpvar ;
c銆備娇鐢╩ysql_query()/mysql_db_query()鎵цmysql 鍙橀噺澹版槑璇彞銆?/p>
浠g爜濡備笅 | 澶嶅埗浠g爜 |
mysql_query("set @mysqlvar銆?$pbpvar銆?quot;); |
杩欐牱锛屽湪mysql鏈嶅姟鍣ㄩ噷闈㈠氨鏈変竴涓彉閲忥紝@mysqlar銆傚鏋滄椂IN鍙傛暟锛岄偅涔堝叾鍊煎彲浠ユ湁phpar浼犲叆銆?/p>
d銆?濡傛灉鏃跺瓨鍌ㄨ繃绋嬨€?/p>
1銆傛墽琛?call procedure锛堬級璇彞銆?/p>
涔熷氨鏄痬ysql_query("call proceduer([var1]...)");
2. 濡傛灉鏈夎繑鍥炲€硷紝鎵цselect @ar锛岃繑鍥炴墽琛岀粨鏋溿€?/p>
浠g爜濡備笅 | 澶嶅埗浠g爜 |
mysql_query("select @var)" |
鎺ヤ笅鏉ョ殑鎿嶄綔灏卞拰php鎵ц涓€鑸殑mysql璇彞涓€鏍蜂簡銆傚彲浠ラ€氳繃mydql_fetch_row()绛夊嚱鏁拌幏寰楃粨鏋溿€?/p>
涓嬮潰鎴戞€荤粨浜嗕竴浜涜皟鐢ㄥ瓨鍌ㄨ繃绋嬬殑瀹炰緥鏃犲弬鐨勫瓨鍌ㄨ繃绋?/p>
浠g爜濡備笅 | 澶嶅埗浠g爜 |
$sql = "call test.myproce();"; |
鍨嬩竴锛氳皟鐢ㄥ甫杈撳叆銆佽緭鍑虹被鍨嬪弬鏁扮殑鏂规硶
浠g爜濡備笅 | 澶嶅埗浠g爜 |
$returnValue = ''; |
绫诲瀷浜岋細璋冪敤甯﹀涓緭鍑虹被鍨嬪拰澶氫釜杈撳叆绫诲瀷鍙傛暟鐨勬柟娉?/p>
浠g爜濡備笅 | 澶嶅埗浠g爜 |
$userId = 0; |
绫诲瀷涓夛細璋冪敤甯﹁繑鍥炵粨鏋滈泦鐨勬柟娉?/p>
浠g爜濡備笅 | 澶嶅埗浠g爜 |
try { } catch ( Exception $e ) { |
绫诲瀷鍥涳細璋冪敤甯﹁繑鍥炲涓粨鏋滈泦鐨勬柟娉?鐩墠鍙兘閫氳繃mysqli鏉ュ疄鐜皛~)
浠g爜濡備笅 | 澶嶅埗浠g爜 |
//PHP |
瀹炰緥鍥涳細浼犲嚭鍙傛暟鐨刬nout瀛樺偍杩囩▼
浠g爜濡備笅 | 澶嶅埗浠g爜 |
$sql = " create procedure myproce4(inout sexflag int) begin SELECT * FROM user WHERE sex = sexflag; end; "; mysql_query($sql);//鍒涘缓涓€涓猰yproce4鐨勫瓨鍌ㄨ繃绋?br /> $sql = "set @sexflag = 1"; mysql_query($sql);//璁剧疆鎬у埆鍙傛暟涓? $sql = "call test.myproce4(@sexflag);"; mysql_query($sql);//璋冪敤myproce4鐨勫瓨鍌ㄨ繃绋?鍦╟md涓嬮潰鐪嬫晥鏋?/td> |
瀹炰緥浜旓細浣跨敤鍙橀噺鐨勫瓨鍌ㄨ繃绋?/p>
浠g爜濡備笅 | 澶嶅埗浠g爜 |
$sql = " create procedure myproce5(in a int,in b int) begin declare s int default 0; set s=a+b; select s; end; "; mysql_query($sql);//鍒涘缓涓€涓猰yproce5鐨勫瓨鍌ㄨ繃绋?br /> $sql = "call test.myproce5(4,6);"; mysql_query($sql);// |
璋冪敤myproce5鐨勫瓨鍌ㄨ繃绋?鍦╟md涓嬮潰鐪嬫晥鏋?/p>
瀹炰緥鍏細case璇硶
浠g爜濡備笅 | 澶嶅埗浠g爜 |
$sql = " create procedure myproce6(in score int) begin case score when 60 then select '鍙婃牸'; when 80 then select '鍙婅壇濂?; when 100 then select '浼樼'; else select '鏈煡鍒嗘暟'; end case; end; "; mysql_query($sql);//鍒涘缓涓€涓猰yproce6鐨勫瓨鍌ㄨ繃绋?br /> $sql = "call test.myproce6(100);"; mysql_query($sql);// |
璋冪敤myproce6鐨勫瓨鍌ㄨ繃绋?鍦╟md涓嬮潰鐪嬫晥鏋?/p>
瀹炰緥涓冿細寰幆璇彞
浠g爜濡備笅 | 澶嶅埗浠g爜 |
$sql = " create procedure myproce7() begin declare i int default 0; declare j int default 0; while i set j=j+i; set i=i+1; end while; select j; end; "; mysql_query($sql);//鍒涘缓涓€涓猰yproce7鐨勫瓨鍌ㄨ繃绋?br /> $sql = "call test.myproce7();"; mysql_query($sql);// |
璋冪敤myproce7鐨勫瓨鍌ㄨ繃绋?鍦╟md涓嬮潰鐪嬫晥鏋?/p>
瀹炰緥鍏細repeat璇彞
浠g爜濡備笅 | 澶嶅埗浠g爜 |
$sql = " create procedure myproce8() begin declare i int default 0; declare j int default 0; repeat set j=j+i; set i=i+1; until j>=10 end repeat; select j; end; "; mysql_query($sql);//鍒涘缓涓€涓猰yproce8鐨勫瓨鍌ㄨ繃绋?br /> $sql = "call test.myproce8();"; mysql_query($sql);// |
璋冪敤myproce8鐨勫瓨鍌ㄨ繃绋?鍦╟md涓嬮潰鐪嬫晥鏋?/p>
瀹炰緥涔濓細loop璇彞
浠g爜濡備笅 | 澶嶅埗浠g爜 |
$sql = " loop_label:loop |
璋冪敤myproce9鐨勫瓨鍌ㄨ繃绋?鍦╟md涓嬮潰鐪嬫晥鏋?/p>
瀹炰緥鍗侊細鍒犻櫎瀛樺偍杩囩▼
浠g爜濡備笅 | 澶嶅埗浠g爜 |
mysql_query("drop procedure if exists myproce");//鍒犻櫎test鐨勫瓨鍌ㄨ繃绋?/p> |
瀹炰緥鍗侊細瀛樺偍杩囩▼涓殑娓告爣