例一，mysql/procedure/ target=_blank class=infotextkey>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
