《Mysql应用mysql存储过程简单实例》要点：
本文介绍了Mysql应用mysql存储过程简单实例，希望对您有用。如果有疑问，可以联系我们。
导读：例一,mysql存储过程：
CREATE PROCEDURE test(IN myid INT(3),IN myname VARCHAR(22),IN myage INT(3)) if myid=0 THEN INSE...
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数据库例二,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
MYSQL数据库调用：
CALL getShang('ABC1122',@groupName);
SELECT @groupName;
MYSQL数据库注：例一和例二中因为已经传入了参数值如：IN myid INT(3),那么就不必重复定义如：DECLARE myid int;不然这个myid应该始终是默认值0!!!
MYSQL数据库例三,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
转载请注明本页网址：
http://www.vephp.com/jiaocheng/6198.html
