create PROCEDURE automatch_proc
(
useridd varchar(36),
sessionid varchar(36),
recurll VARCHAR(36),
fileidd VARCHAR(36),
duration FLOAT,
filetypee INT
)
BEGIN
START TRANSACTION;
INSERT INTO audiofilelist_tb(useridd,sessionid,recurl,fileid,duration);
IF filetypee=0 THEN
insert into filematch_tb(fileid,guestid)select fileidd,userid from userlist_tb where userfavid=(select userfavid from userinfo_tb where userid =useridd);
ELSEIF filetypee=1 THEN
insert into filematch_tb(fileid,guestid)select fileidd, if(hostuserid=useridd,'hostuserid',if(guestuserid=useridd,'guestuserid','')) from conversationrelation_tb where sessionlistid=sessionid;
END IF;
COMMIT;
end;
(
useridd varchar(36),
sessionid varchar(36),
recurll VARCHAR(36),
fileidd VARCHAR(36),
duration FLOAT,
filetypee INT
)
BEGIN
START TRANSACTION;
INSERT INTO audiofilelist_tb(useridd,sessionid,recurl,fileid,duration);
IF filetypee=0 THEN
insert into filematch_tb(fileid,guestid)select fileidd,userid from userlist_tb where userfavid=(select userfavid from userinfo_tb where userid =useridd);
ELSEIF filetypee=1 THEN
insert into filematch_tb(fileid,guestid)select fileidd, if(hostuserid=useridd,'hostuserid',if(guestuserid=useridd,'guestuserid','')) from conversationrelation_tb where sessionlistid=sessionid;
END IF;
COMMIT;
end;
CREATE PROCEDURE automatch_proc
(
useridd VARCHAR(36),
sessionid VARCHAR(36),
recurll VARCHAR(36),
fileidd VARCHAR(36),
duration FLOAT,
filetypee INT
)
BEGIN
START TRANSACTION;
INSERT INTO audiofilelist_tb(useridd,sessionid,recurl,fileid,duration) VALUES (1,2,3,4,5);
IF filetypee=0 THEN
INSERT INTO filematch_tb(fileid,guestid) SELECT fileidd,userid FROM userlist_tb WHERE userfavid=(SELECT userfavid FROM userinfo_tb WHERE userid =useridd);
ELSEIF filetypee=1 THEN
INSERT INTO filematch_tb(fileid,guestid) SELECT fileidd, IF(hostuserid=useridd,'hostuserid',IF(guestuserid=useridd,'guestuserid','')) FROM conversationrelation_tb WHERE sessionlistid=sessionid;END IF;
COMMIT;
END$$
DELIMITER ;
INSERT INTO audiofilelist_tb(useridd,sessionid,recurl,fileid,duration);这样的sql语句能通吗?是不是少了values啊?
ELSE 与IF 要 空格
GO
DECLARE @msg varchar(255)
IF (SELECT COUNT(price)
FROM titles
WHERE title_id LIKE 'TC%' AND price BETWEEN 10 AND 20) > 0
BEGIN
SET NOCOUNT ON
SET @msg = 'There are several books that are a good value between $10 and $20. These books are: '
PRINT @msg
SELECT title
FROM titles
WHERE title_id LIKE 'TC%' AND price BETWEEN 10 AND 20
END
ELSE
BEGIN
SET NOCOUNT ON
SET @msg = 'There are no books between $10 and $20. You might consider the following books that are under $10.'
PRINT @msg
SELECT title
FROM titles
WHERE title_id LIKE 'TC%' AND price < 10
END