my sql存储过程 基本使用
时间：2021-07-01 10:21:17
帮助过：28人阅读
------------创建不带参数的存储过程-----------------
DELIMITER;;
drop PROCEDURE if EXISTS selectStudent;
create PROCEDURE ‘selectStudent‘()
BEGIN
select*from student;
end;;
DELIMITER;
--------------带参数的存储过程--------------------
drop PROCEDURE if EXISTS selectCity;
create PROCEDURE ‘selectCity‘(
in _cityID
varchar(
10)
--输入参数--
)
BEGIN
select *from student
where cityID
=_cityID;
end;
--------------带有输出参数的存储过程--------------------
drop PROCEDURE if EXISTS selectCity_Name;
CREATE PROCEDURE ‘selectCity_Name‘(
in _CityID
varchar(
10)
--输入参数,
out _City
varchar(
10)
--输出参数,
inout _CityIDName
varchar(
10)
--输入输出参数
)
BEGIN
select*from student
where cityID
=_CityID
and CityName
=_CityName
INTO _City;
end;
set @_CityID=‘1‘;
set @_CityIDName=‘郑州‘;
call selectCity_Name(@_CityID,
@_City,
@_CityIDName);
select @_CityIDName as ID,
@_City;
-----------带有通配符的存储过程------------
drop PROCEDURE if EXISTS selectCityLike;
create PROCEDURE ‘selectCityLike‘(
in _CityName
varchar(
10)
)
BEGIN
set @exec_sql =CONCAT("
select *from student
where name
like ‘%",_CityName,"%‘");
PREPARE stmt
from @exec_sql ;
--定义
EXECUTE stmt;
--执行预处理语句
DEALLOCATE PREPARE stmt;
--删除定义
end
-------循环语句：操作前检查结果----------
create PROCEDURE proc4()
BEGIN
declare var int;
set var=0;
WHILE var<6 DO
insert into t
VALUES(
var);
set var=var+1;
end while;
end
----------循环语句：操作后检查结果------------
create PROCEDURE proc5()
BEGIN
DECLARE v
int;
set v
=0;
REPEAT
insert into t
VALUES(v);
set v
=v
+1;
UNTIL v>=5
end repeat;
end
----------循环语句：loop..endloop------------
create PROCEDURE proc6()
BEGIN
declare v
int;
set v
=0;
loop_lable:LOOP
insert into values(v);
set v
=v
+1;
if v
>=5 THEN
LEAVE loop_lable;
end if;
end loop;
end
----------循环语句：loop..endloop-------------
create PROCEDURE proc7()
BEGIN
DECLARE v
int;
set v
=0;
loop_lable:LOOP
if v
=3 THEN
set v
=v
+1;
ITERATE loop_lable;--继续循环
end if;
insert into t
values(v);
set v
=v
+1;
if v
>=5 THEN
leave loop_lable;--跳出循环
end if;
end loop;
end;
my sql存储过程 基本使用
标签：存储过程 定义 入参 rom proc varchar 预处理 val end
