封装了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('李四');