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 

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; 

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