mysql简单的存储过程实例_mysql存储过程简单实例

例一，mysql存储过程：

复制代码 代码示例:

CREATE PROCEDURE test(IN myid INT(3),IN myname VARCHAR(22),IN myage INT(3))

if myid=0

THEN

INSERT INTO a(name,age) VALUES(myname,myage);

ELSE

UPDATE a SET a.name=myname,a.age=myage WHERE a.id=myid;

END IF

例二，mysql存储过程：

复制代码 代码示例:

CREATE PROCEDURE getShang(IN worknum VARCHAR(10),OUT outName VARCHAR(20))

BEGIN

DECLARE ret int;

DECLARE p1 VARCHAR(10);

DECLARE p2 VARCHAR(10);

set ret = (SELECT gt.iparentgroup

FROM grouptbl gt,groupmembertbl gmt

WHERE gt.igroupid = gmt.igroupid

AND gmt.smemberid = worknum);

if ret = 0

THEN

set p1=(SELECT gt.sgroupname

FROM grouptbl gt,groupmembertbl gmt

WHERE gt.igroupid = gmt.igroupid

AND gmt.smemberid = worknum);

SET outName = p1;

ELSE

set p2 = (

SELECT grouptbl.sgroupname

FROM grouptbl WHERE grouptbl.igroupid =

(SELECT gt.iparentgroup

FROM grouptbl gt,groupmembertbl gmt

WHERE gt.igroupid = gmt.igroupid

AND gmt.smemberid = worknum)

);

SET outName = p2;

END IF;

END

调用：

复制代码 代码示例:

CALL getShang('ABC1122',@groupName);

SELECT @groupName;

注：例一和例二中因为已经传入了参数值如：IN myid INT(3),那么就不必重复定义如：DECLARE myid int;不然这个myid应该始终是默认值0！！！

例三，mysql存储过程：

复制代码 代码示例:

CREATE PROCEDURE modAdministrativeSystem(

IN personName VARCHAR(20),

IN project VARCHAR(100),

IN utilizationPercent FLOAT(3,2),

IN sTime date,

IN special VARCHAR(250)

)

BEGIN

DECLARE pjId INT;

DECLARE utilizationId INT;

set pjId = (

SELECT ppt.projectPersonId

FROM projectpersontbl ppt

WHERE ppt.projectId =

(

SELECT pt.projectId

FROM projecttbl pt

WHERE pt.projectName = project

)

AND ppt.personNumber =

(

SELECT p.worknum

FROM person p

WHERE p.name = personName

)

);

set utilizationId = (

SELECT put.utilizationId

FROM personutilizationtbl put

WHERE put.projectPersonId = pjId

AND put.startTime = sTime

);

if utilizationId is null

THEN

INSERT INTO personutilizationtbl(projectPersonId,utilizationPercent,startTime,specialExplanation)

VALUES(pjId,utilizationPercent,sTime,special);

ELSE

UPDATE personutilizationtbl SET personutilizationtbl.utilizationPercent =  utilizationPercent,

personutilizationtbl.specialExplanation = special,personutilizationtbl.startTime = sTime

WHERE personutilizationtbl.utilizationId = utilizationId;

END IF;

END