存储过程代码

 1.概念：存储过程（Stored Procedure）就是一段存储在数据库中执行某种功能的PL/SQL程序
跟java中的方法相类似，而且都需要调用，一次编译永久有效

过程主要的操作是：增删改查

2.存储过程代码

##创建一个存储过程
CREATE PROCEDURE test()
BEGIN
SELECT * from student_test;
end

call test()

##这是单条件查询
CREATE PROCEDURE testbyId(IN idtest INT)
READS SQL DATA
BEGIN 
SELECT * FROM student_test WHERE student_id=idtest;
END

SET @idtest=2;CALL testbyId(@idtest);

##多条件查询
CREATE PROCEDURE testbyIdand(IN idtest INT,IN ageTest INT)
READS SQL DATA
BEGIN 
SELECT * FROM student_test WHERE student_id=idtest AND student_age = ageTest;
END

set @idtest=1,@ageTest=19;CALL testbyIdand(@idtest,@ageTest)

##多条件查询
CREATE PROCEDURE testbyName(INOUT ageTest INT,IN studentNameTest VARCHAR(255))
READS SQL DATA
BEGIN 
SELECT * FROM student_test WHERE  student_age = ageTest AND student_name =studentNameTest;
END

set @studentNameTest='理想';CALL testbyName(@ageTest,@studentNameTest)
SELECT @ageTest;
DROP @ageTest;

CREATE PROCEDURE addTest(OUT idTest INT,IN nameTest VARCHAR(255),in ageTest INT,IN sexTest VARCHAR(255))
BEGIN 
 INSERT student_test()

CREATE PROCEDURE select_student(in sex int)
BEGIN
    IF(sex=0)
        THEN
            SELECT * FROM student_test WHERE student_sex = '男';
    ELSE    
            SELECT * FROM student_test WHERE student_sex = '女';
    END IF;
END;

CALL select_student(null)

create procedure select_student(in userId int)
begin
   declare username varchar(32) default '';
   if(userId%2=0)
   then 
      select name into username from users where id=userId;
      select username;
   else
      select userId;
      end if;
end;