存储过程procedure 与 存储函数function

封装了sql语句

================================================

createprocedure 存储过程名(参数名 参数类型)returns 返回类型

         begin

              statement;

         end;

查看现有的存储过程

         showprocedure status ;

删除存储过程

         dropprocedure 存储过程名称

调用存储过程

         call存储过程的名称();

创建存储过程

delimiter //

create procedure simpleproc(out param1 int)

begin

         selectsum(yu_wen) into param1 from xue_sheng;

end

//

delimiter ;

调用存储过程

call simpleproc(@a);

select @a;

===================================================

带输入，输出参数的存储过程

drop procedure if exists simpleproc;

delimiter //

create procedure simpleproc(IN id int,OUTresult1 varchar(100))

begin

         selectxing_ming into result1 from xue_sheng where xue_sheng.id=id;

end

//

delimiter ;

call simpleproc( 1 ,@a);

select @a;

 

=======================================

存储函数里面声明变量和赋值，逻辑判断

drop procedure if exists simpleproc;

delimiter //

create procedure simpleproc(IN in_namevarchar(50),OUT result_1 varchar(150))

begin

         declaretemp_1 int;

         declaretemp_2 int default 60;

         select(yu_wen+shu_xue)/2 into temp_1 from xue_sheng where xing_ming=in_name;

         iftemp_1 >= temp_2 then

                   setresult_1 = '及格';

         else

                   setresult_1 = '不及格';

         endif;

end

//

delimiter ;

call simpleproc( '张三' ,@a);

select @a;

call simpleproc( '李四' ,@a);

select @a;

==============================================

存储函数

delimiter //

create function hello( s char(20)) returnsint

deterministic

begin

         declaretemp_sum int;

         selectyu_wen+shu_xue into temp_sum from xue_sheng where xing_ming=s;

         returntemp_sum;

end

//

delimiter ;

select hello('张三');

select hello('李四');