目錄

• 創(chuàng)建SQL存儲(chǔ)過(guò)程需要使用到的語(yǔ)法
• 無(wú)參數(shù)
• 帶參
• 兩個(gè)參數(shù)
• declare關(guān)鍵字的使用
• IF語(yǔ)句
• case語(yǔ)句
• loop語(yǔ)句
• repeat
• while

創(chuàng)建SQL存儲(chǔ)過(guò)程需要使用到的語(yǔ)法

- 創(chuàng)建存儲(chǔ)過(guò)程

CREATE 存儲(chǔ)過(guò)程的名稱(參數(shù))
BEGIN
...需要執(zhí)行的SQL語(yǔ)句
END

- 調(diào)用

CALL 存儲(chǔ)過(guò)程的名稱(參數(shù))

個(gè)人看法，這就是一個(gè)函數(shù)...

無(wú)參數(shù)

CREATE PROCEDURE p_student_select()BEGIN SELECT * FROM student;ENDCALL p_student_select()

帶參

# out其實(shí)就是cpp里的引用變量，in就是值傳遞CREATE PROCEDURE p_count(OUT count int)BEGINSELECT COUNT(*) into count FROM student;ENDset @num = 10;	# 定義變量CALL p_count(@num);		#我的數(shù)據(jù)有11條SELECT @num as num;		#這里的num已經(jīng)是11了--這個(gè)就是一個(gè)調(diào)用參數(shù)的函數(shù)CREATE PROCEDURE p_studnt_selectById(in id int)BEGINSELECT * FROM student WHERE studentNo = id;ENDCALL p_studnt_selectById(4)

兩個(gè)參數(shù)

#兩個(gè)參數(shù)是一樣的用法CREATE PROCEDURE p_result_test(out num int, in id int)BEGINSELECT COUNT(*) INTO num FROM result WHERE studentNo = id;ENDset @num = 0;CALL p_result_test(@num, 3);SELECT @num as num

declare關(guān)鍵字的使用

# declare的作用是用來(lái)定義變量CREATE PROCEDURE p5()BEGINDECLARE studentName VARCHAR(10);	#創(chuàng)建了一個(gè)變量set @studentName = "HELLO";		#給變量賦值ENDCALL p5();SELECT @studentName-- 說(shuō)明了declare定義的變量可以在外部直接調(diào)用

IF語(yǔ)句

#if語(yǔ)句沒(méi)啥說(shuō)的，代碼直接能看懂CREATE PROCEDURE p6(in id int)BEGINIF(id = 0) THEN	SELECT * FROM student;ELSE	SELECT * FROM teacher;END IF;END

case語(yǔ)句

#看代碼...CREATE PROCEDURE p7(in tab VARCHAR(10))BEGINCASE tab	WHEN "student" THEN		SELECT * FROM student;	WHEN "teacher" THEN		SELECT * FROM teacher;END CASE;END

loop語(yǔ)句

CREATE PROCEDURE p9(IN id INT)BEGINaddloop: LOOP	set id = id + 1;	SELECT id;	IF id > 10 THEN		LEAVE addloop;	ELSE		SELECT * FROM student; 	END IF; END LOOP addloop;END

repeat

CREATE PROCEDURE p10(in count int)BEGINREPEAT	set count = count + 1;	SELECT count;UNTIL count > 10 END REPEAT;	#count >10跳出END

while

CREATE PROCEDURE p11(in num int)BEGINwhile num < 10 DO	SELECT num;	SET  num = num + 1; END WHILE;END

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持。