mysql存儲過程fetchinto
從存儲過程返回表類型的值也有二種:
1.存儲過程使用浮標參數,即同時指定CURSOR VARYING OUTPUT項.調用者可以使用while及fetch循環遍歷該浮標.
2.直接將存儲過程返回的結果集插入到表中,即使用insert into 表名 exec 存儲過程.此種方式中注意存儲過程返回的結果集列與insert的列要完全對應,可以在insert中指定列名來保證對應關系.
------------------------------------------------------------------------------測試:------------------------------------------------------------------------------
----建立測試用的臨時表
create table #tmp (colx int,coly int)
insert into #tmp values(1,2)
insert into #tmp values(2,3)
insert into #tmp values(3,4)
select * from #tmpGO----創建返回遊標的存儲過程
create proc sp_c @cur CURSOR VARYING OUTPUTASbeginset @cur = CURSOR for select colx from #tmp
open @cur /*該過程返回遊標,該游標為colx列的查詢結果*/endGO----創建返回表的存儲過程
create proc sp_dasselect coly from #tmp /*該過程返回coly列的查詢結果*/go----創建用於調用以上二個存儲過程的存儲過程
create proc sp_easbegindeclare @x int
declare @cur cursor
----接收游標,並遍歷游標
EXEC sp_c @cur OUTPUT
fetch next from @cur into @x
while (@@FETCH_STATUS = 0)beginprint @xfetch next from @cur into @xENDclose @curdeallocate @cur
----將存儲過程返回的列值再重新插入源表中
❷ mysql中怎麼定時調存儲過程
那就用游標吧，代碼沒測試，建議先用測試數據測試一下能不能正常運行
delimiter//
createprocereCalcu_MonTotal()
begin
DECLAREv_datevarchar(16);
DECLAREv_totalint;
DECLAREv_tagnamevarchar(32);
SELECTDATE_FORMAT(DATE_ADD(NOW(),INTERVAL'-1'MONTH),'%Y-%m')intov_date;
DECLAREcursorc_Fetch
Forselecttagname,sum(daytotal)
FROMribao
WHEREddate>DATE_FORMAT(DATE_ADD(NOW(),INTERVAL'-1'MONTH),'%Y-%m')
andddate<DATE_FORMAT(NOW(),'%Y-%m')groupbytagname;
=1;
OPENc_Fetch;
REPEAT
FETCHc_FetchINTOv_tagname,v_total;
begin
insertintoyuetotal(tagname,ddate,yuetotal)values(v_tagname,v_date,v_total);
end;
UNTILstopFlag=1
ENDREPEAT;
CLOSEc_Fetch;
end
❸ mysql 存儲過程
你應該在做統計吧,估計你不會的就是mysql存儲過程的語法 我之前也寫過 很是郁悶 我給你一段代碼 是我用mysql寫過的一個存儲過程 你看看 主要是了解裡面的語法 看懂了 你所說的需求並不難
有看不懂的地方一起討論 :
begin
declare tikk datetime ;
declare done int default 0;
declare userid int default 0;
declare moleid int default 0;
declare couid int default 0;
declare mname varchar(255) ;
declare opsid int default 0;
declare c1 cursor for Select I_userID,I_operationID from space_operation_record where status<>0 group by I_userID,I_operationID order by createtime desc;
declare continue handler for sqlstate '02000' set done =1;
set tikk = now();
open c1;
repeat
fetch c1 into userid, opsid;
if not done then
select I_moleID from space_operation where status<>0 and ID=opsid into moleid;
if moleid <> '' then
select Nvc_identification from space_operation where status<>0 and ID=opsid into @identiftion;
if moleid > 0 then
Select Nvc_ename from space_mole where status<>0 and ID=moleid into mname;
else
set mname = 'space';
end if;
create temporary table if not exists sp_tab1(id bigint(20),Nvc_content MEDIUMTEXT,I_obyuID bigint(20),I_tID bigint(20),createtime datetime);
INSERT INTO sp_tab1 Select ID,Nvc_content,I_objectID,I_tmID,createtime from space_operation_record where status<>0 and I_operationID=opsid and I_userID=userid ;
select count(*) from sp_tab1 into couid;
set @ihod = 0;
set @listp = '';
set @listpp = '';
set @content0p = '';
set @content0 = '';
while couid > 0 do
select ID,Nvc_content,I_obyuID,createtime,I_tID into @iok,@conuiy,@objiplk,@crtimhr,@tmids from sp_tab1 where ID > @ihod order by ID asc limit 0,1;
if @iok <> '' then
if mname = 'blog' then
INSERT INTO space_operation_stat(I_operationID,I_userID,Nvc_content,D_stattime,createtime) VALUES (@iok,userid,@conuiy,@crtimhr,tikk);
elseif mname = 'team' then
if(@identiftion = 'addblog' || @identiftion = 'mdyblog') then
INSERT INTO space_operation_stat(I_operationID,I_userID,Nvc_content,D_stattime,I_tmID,createtime) VALUES (@iok,userid,@conuiy,@crtimhr,@tmids,tikk);
else
set @listpp = CONCAT(@listpp,CONCAT(@objiplk,','));
set @operarry1p = substring_index(@conuiy,'|',1);
set @operarry2p = substring_index(@conuiy,'|',-1);
set @content0p = CONCAT(@content0p,CONCAT(@operarry2p,SPACE(1)));
set @objlistp = substring(@listpp,1,length(@listpp)-1);
end if;
elseif mname = 'space' then
if(@identiftion = 'headphoto' || @identiftion = 'status') then
INSERT INTO space_operation_stat(I_operationID,I_userID,Nvc_content,D_stattime,I_tmID,createtime) VALUES (@iok,userid,@conuiy,@crtimhr,@tmids,tikk);
else
set @listppr = CONCAT(@listppr,CONCAT(@objiplk,','));
set @operarry1pr = substring_index(@conuiy,'|',1);
set @operarry2pr = substring_index(@conuiy,'|',-1);
set @content0pr = CONCAT(@content0pr,CONCAT(@operarry2pr,SPACE(1)));
set @objlistpr = substring(@listppr,1,length(@listppr)-1);
end if;
else
set @listp = CONCAT(@listp,CONCAT(@objiplk,','));
set @operarry1 = substring_index(@conuiy,'|',1);
set @operarry2 = substring_index(@conuiy,'|',-1);
set @content0 = CONCAT(@content0,CONCAT(@operarry2,SPACE(1)));
set @objlist = substring(@listp,1,length(@listp)-1);
end if;
set @ihod = @iok;
end if;
set couid = couid -1;
end while;
if @content0 <> '' then
set @contentp = CONCAT(@operarry1,concat('|',@content0));
Select createtime,ID into @uitimej,@IDjok from space_operation_record where status<>0 and I_operationID=opsid order by createtime desc limit 0,1;
if @uitimej <> '' then
INSERT INTO space_operation_stat(I_operationID,I_userID,Nvc_content,D_stattime,createtime,Nvc_objlist) VALUES(@iok,userid,@contentp,@crtimhr,tikk,@objlist);
end if;
end if;
if @content0p <> '' then
if @identiftion = 'addphoto' then
set @contentp = CONCAT(@operarry1p,CONCAT('|',@content0p));
else
set @contentp = CONCAT(@operarry1p,CONCAT(@content0p,'|'));
end if;
Select createtime,ID into @uitimej,@IDjok from space_operation_record where status<>0 and I_operationID=opsid order by createtime desc limit 0,1;
if @uitimej <> '' then
INSERT INTO space_operation_stat(I_operationID,I_userID,Nvc_content,D_stattime,createtime,Nvc_objlist,I_tmID) VALUES(@iok,userid,@contentp,@crtimhr,tikk,@objlistp,@tmids);
end if;
end if;
if @content0pr <> '' then
set @contentp = CONCAT(@operarry1p,concat('|',@content0pr));
Select createtime,ID into @uitimej,@IDjok from space_operation_record where status<>0 and I_operationID=opsid order by createtime desc limit 0,1;
if @uitimej <> '' then
INSERT INTO space_operation_stat(I_operationID,I_userID,Nvc_content,D_stattime,createtime,Nvc_objlist,I_tmID) VALUES(@iok,userid,@contentp,@crtimhr,tikk,@objlistp,@tmids);
end if;
end if;
delete from sp_tab1;
end if;
end if;
until done end repeat;
close c1;
drop temporary table if exists sp_tab1 ;
UPDATE space_operation_play SET status=0;
UPDATE space_operation_display SET status=0;
Select createtime into @ptimes from space_operation_stat where status<>0 order by createtime desc limit 0,1;
if @ptimes <>'' then
create temporary table if not exists sp_tab2(id bigint(20),Nvc_content MEDIUMTEXT,I_userID bigint(20),I_lyuID bigint(20),D_stattime datetime);
INSERT INTO sp_tab2 Select ID,Nvc_content,I_userID,I_tmID,D_stattime from space_operation_stat where status<>0 and createtime=@ptimes order by D_stattime desc limit 0,30;
select count(*) from sp_tab2 into @cou1id;
set @uoj = 0;
while @cou1id > 0 do
select ID,Nvc_content,I_userID,D_stattime,I_lyuID into @io1k,@conui1y,@objipl1k,@crtimh1r,@unlpa from sp_tab2 where ID > @uoj order by ID asc limit 0,1;
if @io1k <> '' then
INSERT INTO space_operation_play(I_statID,Nvc_content,D_stattime,I_userID,Createtime,I_tmID) VALUES (@io1k,@conui1y,@crtimh1r,@objipl1k,now(),@unlpa);
set @uoj = @io1k;
end if;
set @cou1id = @cou1id -1;
end while;
drop temporary table if exists sp_tab2 ;
end if;
end
❹ MySQL存儲過程創建，我錯在哪裡了
你這個要取輸入的ROWS以後的學生學號?
open cur;
FETCH cur into sno; 這里要用for循環啊。
不然你游標裡面的數據很多，不可能都保存到sno裡面的。
LOOP
FETCH cur into sno;
while FOUND DO
set ROWS=ROWS+1;
END LOOP
這樣你的ROWS才會實現不停＋1功能
❺ mysql存儲過程游標結果集時，數據沒有遍歷完整
CREATEDEFINER=`root`@`%`PROCEDURE`insertPresale`()
BEGIN
#Routinebodygoeshere...
DECLAREdoneINTDEFAULT0;/*用於判斷是否結束循環*/
DECLAREgoodsIdVARCHAR(255);#標記商品id
DECLAREflagINTDEFAULT0;#標記資料庫是否包含此條商品記錄
/*用於存儲結果集的記錄*/
/*定義游標*/
_idFROM`sys_goods_publish`WHEREpresale=1ANDpresale_time<=NOW();
/*定義設置循環結束標識done值怎麼改變的邏輯*/
=1;/*done=true;亦可*/
OPENidCur;/*打開游標*/
/*循環開始*/
REPEAT
#/*如果要fetch多列應該這樣寫，fetchcur/*對應下面的idCur*/
FETCHidCurINTOgoodsId;/*還可以fetch多列（假設結果集的記錄不是單列的話）*/
IFNOTdoneTHEN/*數值為非0，MySQL認為是true*/
SELECTCOUNT(*)INTOflagFROM`itemsinfonew`WHERETaoBaoitemId=goodsId;
IF(flag>0)THEN#如果資料庫中有爬取此條記錄則刪除已用戶發布的為准
DELETEFROM`itemsinfonew`WHERETaoBaoitemId=goodsId;
ENDIF;
INSERTINTO`itemsinfonew`(TaoBaoitemId,CouponID,CreateSourceName)
SELECTgoods_id,coupon_id,SUBSTRING("customer_fd",0.5)
FROM`sys_goods_publish`
WHEREpresale_time<=NOW()ANDgoods_id=goodsId;
UPDATE`sys_goods_publish`SETpresale=0WHEREpresale=1ANDpresale_time<=NOW()ANDgoods_id=goodsId;
ENDIF;
UNTILdoneENDREPEAT;
CLOSEidCur;/*關閉游標*/
❻ mysql 游標里fetch into存入的數據是全部還是一條
CREATE TABLE test_main (
id INT,
value VARCHAR(10),
PRIMARY KEY(id)
);
INSERT INTO test_main(id, value) VALUES (1, 'ONE');
INSERT INTO test_main(id, value) VALUES (2, 'TWO');
INSERT INTO test_main(id, value) VALUES (3, 'THREE');
DECLARE
-- 定義游標.
CURSOR c_test_main IS
SELECT id, value FROM test_main;
-- 保存游標數據的變數
v_main_data c_test_main%ROWTYPE;
BEGIN
-- 打開游標.
OPEN c_test_main;
LOOP
-- 填充數據(主表).
FETCH c_test_main INTO v_main_data;
-- 假如沒有檢索到(主表)數據，結束循環處理
Exit when c_test_main%NOTFOUND;
dbms_output.put_line(TO_CHAR(v_main_data.id)
|| ':' || v_main_data.value );
END LOOP;
-- 關閉游標
CLOSE c_test_main;
END;
❼ Mysql存儲過程
set temp1=SELECT str_split(log,'"',1);
改成
set temp1=str_split(log,'"',1);
❽ mysql 存儲過程 傳多個參數無法識別
存儲過程里的遞歸調用傳的是一個參數，這肯定不對
while(done = 0) do
call iterateCategory(b);
fetch c into b;
end while;
❾ Mysql里如何使用存儲過程
CREATE DEFINER 存儲過程名
@變數1 類型 ----這里個就是傳進來的
@變數2 類型 output ----這個是定義輸出變數
as
declared @變數 類型,@變數 類型 --這里是定義內部變數 只能這是過程用
if 條件
begin
#￥%……&*
end
else
begin
#￥%……&
end
-----不知道你要什麼
execute 資料庫名.dbo.過程名 -----這個是 在這個過程里 調用別的過程