SQL中创建存储过程

CREATE PROCEDURE p_student_select() BEGIN SELECT * FROM student; END CALL p_student_select()

# out其实就是cpp里的引用变量，in就是值传递 CREATE PROCEDURE p_count(OUT count int) BEGIN SELECT COUNT(*) into count FROM student; END set @num = 10;	# 定义变量 CALL p_count(@num);		#我的数据有11条 SELECT @num as num;		#这里的num已经是11了 --这个就是一个调用参数的函数 CREATE PROCEDURE p_studnt_selectById(in id int) BEGIN SELECT * FROM student WHERE studentNo = id; END CALL p_studnt_selectById(4)

#两个参数是一样的用法 CREATE PROCEDURE p_result_test(out num int, in id int) BEGIN SELECT COUNT(*) INTO num FROM result WHERE studentNo = id; END set @num = 0; CALL p_result_test(@num, 3); SELECT @num as num

# declare的作用是用来定义变量 CREATE PROCEDURE p5() BEGIN DECLARE studentName VARCHAR(10);	#创建了一个变量 set @studentName = 'HELLO';		#给变量赋值 END CALL p5(); SELECT @studentName -- 说明了declare定义的变量可以在外部直接调用

#if语句没啥说的，代码直接能看懂 CREATE PROCEDURE p6(in id int) BEGIN IF(id = 0) THEN SELECT * FROM student; ELSE SELECT * FROM teacher; END IF; END

#看代码... CREATE PROCEDURE p7(in tab VARCHAR(10)) BEGIN CASE tab WHEN 'student' THEN SELECT * FROM student; WHEN 'teacher' THEN SELECT * FROM teacher; END CASE; END

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

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

CREATE PROCEDURE p11(in num int) BEGIN while num <10 do select num; set num=num + 1; end while; end< pre>