Mysql应用mysql存储过程简单实例
发布时间：2020-12-24 17:38:36 所属栏目：MySql教程 来源：网络整理
导读：《Mysql应用mysql存储过程简单实例》要点： 本文介绍了Mysql应用mysql存储过程简单实例，希望对您有用。如果有疑问，可以联系我们。 导读：例一,mysql存储过程： CREATE PROCEDURE test(IN myid INT(3),IN myname VARCHAR(22),IN myage INT(3)) if myid=0 TH
|
《Mysql应用mysql存储过程简单实例》要点： MYSQL数据库例一,mysql存储过程： 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存储过程： ? 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数据库调用： SELECT @groupName;? MYSQL数据库注：例一和例二中因为已经传入了参数值如：IN myid INT(3),那么就不必重复定义如：DECLARE myid int;不然这个myid应该始终是默认值0!!! MYSQL数据库例三,mysql存储过程： 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,sTime,special);??? ? ELSE? ?? UPDATE personutilizationtbl SET personutilizationtbl.utilizationPercent =? utilizationPercent,? personutilizationtbl.specialExplanation = special,personutilizationtbl.startTime = sTime? WHERE personutilizationtbl.utilizationId = utilizationId;?? ? END IF;? ? END? （编辑：晋中站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
