（1）。存储过程DB2INST1.endtime
CREATE PROCEDURE DB2INST1.endtime (in a varchar(15), in b varchar(16), in f varchar(16), in d varchar(20),out ret char(1) )
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL 存储过程
------------------------------------------------------------------------
BEGIN
declare currenttimestamp timestamp;
set currenttimestamp = current timestamp;
insert into t_callinfo values(a,b,f,timestamp(d),currenttimestamp);
select paytype into ret from t_studentinfo where userid = a;
END
（2）。DB2INST1.LeaveMessage
CREATE PROCEDURE LeaveMessage(IN uid VARCHAR(15), IN sid VARCHAR(6),
IN stype CHAR(1), IN rid VARCHAR(15),
IN mmode CHAR(1), IN mtype SMALLINT,
IN msg VARCHAR(1000), IN msgtype SMALLINT,
OUT ret SMALLINT, OUT mid INT)
LANGUAGE SQL
---------------------------------------------------------------------------
--SQL存储过程：留言
---------------------------------------------------------------------------
BEGIN
DECLARE currenttimestamp TIMESTAMP;
DECLARE banji char(10);
SET currenttimestamp = CURRENT TIMESTAMP;
IF stype = '0' THEN
CASE msgtype
WHEN 1 THEN
SELECT COUNT(*)
INTO ret
FROM T_Account
WHERE UserID = rid;
IF ret = 0 THEN
SET ret = 1;
RETURN 0;
END IF;
IF (SELECT Active
FROM T_Account
WHERE UserID = uid) <> '1' THEN
SET ret = 2;
RETURN 0;
END IF;
INSERT INTO T_MessageInfo(SenderID, SSchoolID, SenderType, ReceiverID,
RSchoolID, MSGTime, MSGMode, MSGType, Message)
VALUES (uid, sid, stype, rid, sid, currenttimestamp,
mmode, mtype, msg);
SELECT MessageID
INTO mid
FROM T_MessageInfo
WHERE MSGTime = currenttimestamp
AND SenderID = uid;
WHEN 2 THEN
SELECT classid
INTO banji
FROM T_CLASS_TEACHER
WHERE userid = uid
AND teachtype='0';
INSERT INTO T_BulletinInfo(SenderID, SSchoolID, SenderType, ReceiverID,
RSchoolID, MSGTime, MSGMode, Message)
VALUES (uid, sid, '2', banji, sid, currenttimestamp,
mmode, msg);
SELECT MessageID
INTO mid
FROM T_BulletinInfo
WHERE MSGTime = currenttimestamp
AND SenderID = uid;
END CASE;
SET ret = 0;
END IF;
IF stype = '1' THEN
SELECT COUNT(*)
INTO ret
FROM T_Account
WHERE UserID = rid;
IF ret = 0 THEN
SET ret = 1;
RETURN 0;
END IF;
IF (SELECT Active
FROM T_Account
WHERE UserID = uid) <> '1' THEN
SET ret = 2;
RETURN 0;
END IF;
INSERT INTO T_MessageInfo(SenderID, SSchoolID, SenderType, ReceiverID,
RSchoolID, MSGTime, MSGMode, MSGType, Message)
VALUES (uid, sid, stype, rid, sid, currenttimestamp,
mmode, mtype, msg);
SELECT MessageID
INTO mid
FROM T_MessageInfo
WHERE MSGTime = currenttimestamp
AND SenderID = uid;
SET ret = 0;
END IF;
END
（3）。db2inst1.Login
CREATE PROCEDURE db2inst1.Login(IN uid VARCHAR(15), IN pwd VARCHAR(31),
IN caller VARCHAR(16),
OUT role SMALLINT, OUT ret SMALLINT)
LANGUAGE SQL
---------------------------------------------------------------------------
--SQL存储过程：登陆
---------------------------------------------------------------------------
BEGIN
SET ret = 9;
SELECT COUNT(*)
INTO ret
FROM T_Account
WHERE UserID = uid;
IF ret = 0 THEN
SET ret = 1;
RETURN 0;
END IF;
SELECT COUNT(*)
INTO ret
FROM T_Account
WHERE UserID = uid
AND PassWD = pwd;
IF ret = 0 THEN
SET ret = 2;
RETURN 0;
END IF;
IF (SELECT Active
FROM T_Account
WHERE UserID = uid
AND PassWD = pwd) = '0' THEN
SET ret = 3;
RETURN 0;
END IF;
IF (SELECT Active
FROM T_Account
WHERE UserID = uid
AND PassWD = pwd) = '2' THEN
IF (SELECT Phone
FROM T_StudentInfo
WHERE UserID = uid) = caller THEN
UPDATE T_Account
SET Active = '1'
WHERE UserID = uid;
ELSE
SET ret = 4;
RETURN 0;
END IF;
END IF;
SELECT Role
INTO role
FROM T_Account
WHERE UserID = uid;
SET ret = 0;
END
(4) 。db2inst1.QueryCallerNumber
CREATE PROCEDURE db2inst1.QueryCallerNumber ( out r_calleeID varchar(15), out r_calleeNumber varchar(16), out r_password varchar(30), out ret int)
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL 存储过程
------------------------------------------------------------------------
BEGIN
lock table t_dailinfo in exclusive mode;
select t1.calleeID
into r_calleeID
from
(select row_number() over(order by calleeID) r,t.calleeID
from t_dailinfo as t
where calltime < (current time + 10 second)
and calltime > (current time - 10 second)
and status = 0) as t1
where r < 2;
if r_calleeID is null then
set ret = 1;
return 0;
end if;
update t_dailinfo
set status = 1
where calleeID = r_calleeID;
select distinct calleenumber
into r_calleeNumber
from t_dailinfo
where calleeID = r_calleeID;
select passwd
into r_password
from t_account
where userid = r_calleeID;
set ret = 0;
commit;
END
(5)。DB2INST1.SetStatus
CREATE PROCEDURE DB2INST1.SetStatus ( IN CALLEEID VARCHAR(15))
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL 存储过程
------------------------------------------------------------------------
BEGIN
LOCK TABLE DB2INST1.DAILINFO IN EXCLUSIVE MODE;
UPDATE DB2INST1.T_DAILINFO SET STATUS = 0 WHERE CALLEEID = CALLEEID;
COMMIT;
END
（6）。DB2INST1.StartTime
CREATE PROCEDURE DB2INST1.StartTime ( out starttime varchar(20) )
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL 存储过程
------------------------------------------------------------------------
BEGIN
--declare currenttimestamp timestamp;
--set currenttimestamp = current timestamp;
set starttime = char(current timestamp);
END
(7)。DB2INST1.UpdateDialNotice
CREATE PROCEDURE UpdateDialNotice(IN uid VARCHAR(15), IN mid INT,
IN rid VARCHAR(15), IN sendtype SMALLINT,
IN msg VARCHAR(40), OUT ret SMALLINT)
LANGUAGE SQL
---------------------------------------------------------------------------
--SQL存储过程：将留言信息放到外呼通知表中
---------------------------------------------------------------------------
BEGIN
DECLARE cnumber VARCHAR(16);
DECLARE cname VARCHAR(20);
DECLARE ctime TIME;
DECLARE mctime SMALLINT;
SELECT NP.Phone, SI.Name, NP.DialTime, NP.DialNum
INTO cnumber, cname, ctime, mctime
FROM T_NoticePara NP, T_StudentInfo SI
WHERE NP.UserID = uid
AND SI.UserID = uid
AND NP.NoticeID = 1;
IF cnumber IS NULL THEN
SET ret = 1;
RETURN 0;
END IF;
INSERT INTO T_DailInfo
VALUES (mid, cnumber, '1', cname, ctime, mctime, 0, 0, sendtype, msg, rid);
SET ret = 0;
END