比如， 我想查詢(xún)A表的max(i) 和B表的 min(i)有沒(méi)有辦法寫(xiě)一個(gè)存儲(chǔ)過(guò)程 ，通過(guò)一次查詢(xún)，獲得兩個(gè)值 比如[max(i) , min(i)]的形式
1 回答
蝴蝶刀刀
TA貢獻(xiàn)1801條經(jīng)驗(yàn) 獲得超8個(gè)贊
給一個(gè)簡(jiǎn)單的例子給你
DROP PROCEDURE IF EXISTS insertStudents;
CREATE PROCEDURE insertStudents(IN P_SNAME VARCHAR(20))
BEGIN
DECLARE V_STUID INT DEFAULT 0;
SET @V_BIRTHDAY ='';
SET @NAME=P_SNAME;
SET @V_BIRTHDAY=date_format(NOW(), '%Y%m%d%H%i%s');
SELECT MAX(STUID) INTO V_STUID FROM Students;
if V_STUID is null then
set V_STUID=1;
else
SET V_STUID=V_STUID+1;
end if;
SET @ID=cast(V_STUID AS CHAR);-- 將數(shù)字轉(zhuǎn)化為字符
-- SET @ID=CONCAT(V_STUID,''); -- 將數(shù)字轉(zhuǎn)化為字符
SET @INSERTSQL=CONCAT('INSERT INTO Students(STUID,SNAME,BIRTHDAY) VALUES(?,?,?)');
start transaction;
PREPARE stmtinsert FROM @INSERTSQL;
EXECUTE stmtinsert USING @ID,@NAME,@V_BIRTHDAY;
savepoint tran_a;
SET V_STUID=V_STUID+1;
SET @ID=cast(V_STUID AS CHAR);-- 將數(shù)字轉(zhuǎn)化為字符
EXECUTE stmtinsert USING @ID,@NAME,@V_BIRTHDAY;
DEALLOCATE PREPARE stmtinsert;
rollback to tran_a;
commit;
END;
添加回答
舉報(bào)
0/150
提交
取消