检查 open cursor 返回值
解决方案 »
- oracle 批量sql语句中怎么定义变量
- PL/SQL Script
- 如何导出sql语句
- oracle机器 硬盘坏了 用硬盘工具 恢复了文件夹 应为没有备份
- 请教各位关于作业不执行的问题……
- 我能监控某IP地址对ORACLE的访问吗?
- SQL语句中返回上周(周一至周日的日期)怎么个写法?
- 在表中有一字段长度为Varchar2(20),但却只能输入10个中文字符!可输入20个英文字符!如何能该字段容纳20个中文字符!急急急!请大侠帮忙
- 救命啊!数据要怎么恢复?
- 如果将字符串型字段如REGDATE(8位,20161230)格式化为'2016-12-30'
- truncate table t_cjinfo_temp 放在存储过程中为啥出错?
- oracle的ODBC连接,unix下的,请教!
declare my_cur cursor for
select ... open my_cur
fetch next from my_cur into @tablestring
while @@FETCH_STATUS = 0
begin
...
declare columns_cur cursor for
select ...
open columns_cur
fetch next from columns_cur into @columnstring
while @@FETCH_STATUS = 0
begin
... fetch next from columns_cur into @columnstring
end
close columns_cur
DEALLOCATE columns_cur
close my_cur
DEALLOCATE my_cur
再顶
下午结账
CREATE OR REPLACE PROCEDURE sp_pdmxb_all_qxk(
p_cxr IN VARCHAR2,
p_date IN DATE,
p_wsf OUT NUMBER) IS
prior_date DATE;
ls_mxb checkmxb%rowtype;
sum_xse number;
cursor cur_mxb is
select * from checkmxb;
--
cursor cur_mfid is
SELECT a.CMFDATE,b.MFCODECUT,a.CMFSTATUS,a.CMFFLAG
FROM (select *
from chkmfid
union
select *
from dbusrgfc101.chkmfid@hdqt_db) a,manaframenew b
WHERE a.cmfmfid = b.mfcode and
a.cmfdate = p_date and
a.cmfflag = 'Y' and
b.mfmfid = ls_mxb.ckmmfid;
l_chkmfid cur_mfid%rowtype;
BEGIN
DELETE FROM CHECKMXB;
--
INSERT INTO CHECKMXB(CKMCXR,CKMMFID,CKMPZ,CKMGTJE,CKMSPGT,CKMKFJE,CKMSPKF,CKMJXJE,CKMJXPZ,CKMDXJE,CKMDXPZ,CKMPYPZ,CKMPYJE,CKMPKPZ,CKMPKJE,CKMXSE)
SELECT p_cxr,
A.mfid mfid,
nvl(A.kcpz,0) kcpz,
nvl(B.gtkcje,0) gtkcje,
nvl(B.gtspje,0) gtspje,
nvl(C.ckkcje,0) ckkcje,
nvl(C.ckspje,0) ckspje,
nvl(D.jxje,0) jxje,
nvl(D.jxpz,0) jxpz,
nvl(E.dxje,0) dxje,
nvl(E.dxpz,0) dxpz,
nvl(F.pypz,0) pypz,
nvl(F.pyje,0) pyje,
nvl(G.pkpz,0) pkpz,
nvl(G.pkje,0) pkje,
0 xse
FROM
(SELECT substr(vcksmfid,1,2) mfid,
count(distinct vcksgdid) kcpz
FROM VIEW_CHECKSTOCK_YS
WHERE VIEW_CHECKSTOCK_YS.VCKSRQ=p_date and
vckskchsjjje <> 0
GROUP BY substr(vcksmfid,1,2) ) A, (SELECT substr(vcksmfid,1,2) mfid,
sum(vckskchsjjje) gtkcje,
sum(vckspdhsjjje) gtspje
FROM VIEW_CHECKSTOCK_YS
WHERE vckset = '0001' AND
VIEW_CHECKSTOCK_YS.VCKSRQ=p_date
GROUP BY substr(vcksmfid,1,2)) B, (SELECT substr(vcksmfid,1,2) mfid,
sum(vckskchsjjje) ckkcje,
sum(vckspdhsjjje) ckspje
FROM VIEW_CHECKSTOCK_YS
WHERE vckset = '0000' AND
VIEW_CHECKSTOCK_YS.VCKSRQ=p_date
GROUP BY substr(vcksmfid,1,2)) C, (SELECT substr(vcksmfid,1,2) mfid,
sum(vckskchsjjje) jxje,
count(distinct vcksgdid) jxpz
FROM VIEW_CHECKSTOCK_YS
WHERE vckswmid = '1' AND
VIEW_CHECKSTOCK_YS.VCKSRQ=p_date and
vckskchsjjje <> 0
GROUP BY substr(vcksmfid,1,2)) D, (SELECT substr(vcksmfid,1,2) mfid,
sum(vckskchsjjje) dxje,
count(distinct vcksgdid) dxpz
FROM VIEW_CHECKSTOCK_YS
WHERE vckswmid = '2' AND
VIEW_CHECKSTOCK_YS.VCKSRQ=p_date and
vckskchsjjje <> 0
GROUP BY substr(vcksmfid,1,2)) E, (SELECT substr(vcksmfid,1,2) mfid,
count(vcksgdid) pypz,
sum(vckspdhsjjje - vckskchsjjje) pyje
FROM VIEW_CHECKSTOCK_YS
WHERE vckspdhsjjje > vckskchsjjje AND
VIEW_CHECKSTOCK_YS.VCKSRQ=p_date
GROUP BY substr(vcksmfid,1,2)) F, (SELECT substr(vcksmfid,1,2) mfid,
count(vcksgdid) pkpz,
sum(vckskchsjjje - vckspdhsjjje) pkje
FROM VIEW_CHECKSTOCK_YS
WHERE vckspdhsjjje < vckskchsjjje AND
VIEW_CHECKSTOCK_YS.VCKSRQ=p_date
GROUP BY substr(vcksmfid,1,2)) G
WHERE A.mfid=B.mfid(+) AND
A.mfid=C.mfid(+) AND
A.mfid=D.mfid(+) AND
A.mfid=E.mfid(+) AND
A.mfid=F.mfid(+) AND
A.mfid=G.mfid(+) ;
--盘点期销售额
open cur_mxb;
loop
fetch cur_mxb into ls_mxb;
exit when cur_mxb%notfound;
sum_xse:= 0;
open cur_mfid;
loop
fetch cur_mfid into l_chkmfid;
exit when cur_mfid%notfound;
--上次盘点日
begin
SELECT nvl(MAX(a.cmfdate),to_date('2002-01-01','YYYY-MM-DD'))
INTO prior_date
FROM (select *
from chkmfid
union
select *
from dbusrgfc101.chkmfid@hdqt_db) a,manaframenew b
WHERE a.cmfmfid = b.mfcode and
a.cmfdate < p_date and
a.cmfflag = 'Y' and
b.MFCODECUT = l_chkmfid.MFCODECUT;
EXCEPTION WHEN OTHERS THEN
prior_date := to_date('2002-01-01','YYYY-MM-DD');
end; begin
SELECT nvl(SUM(a.xssr),0) xse
into ls_mxb.ckmxse
FROM jxcmframeday a,manaframenew b
WHERE a.jmdmfid = b.mfcode and
a.jmddate > prior_date AND a.jmddate <= p_date and
b.MFCODECUT= l_chkmfid.MFCODECUT;
EXCEPTION WHEN OTHERS THEN
ls_mxb.ckmxse := 0;
end;
sum_xse := sum_xse + ls_mxb.ckmxse;
end loop;
close cur_mfid;
update checkmxb a
set a.ckmxse = sum_xse
where a.ckmcxr = ls_mxb.ckmcxr and
a.ckmmfid = ls_mxb.ckmmfid;
end loop;
close cur_mxb;
--未上柜品种数
begin
SELECT COUNT(distinct a.gwgbid)
INTO p_wsf
FROM goodswsg a
where a.gwpdrq = p_date ;
EXCEPTION WHEN OTHERS THEN
p_wsf := 0;
end;
commit;
END;
open cur_mxb;
loop
fetch cur_mxb into ls_mxb;
exit when cur_mxb%notfound;
sum_xse:= 0;
open cur_mfid;
loop
fetch cur_mfid into l_chkmfid;
exit when cur_mfid%notfound;
--上次盘点日
begin
SELECT nvl(MAX(a.cmfdate),to_date('2002-01-01','YYYY-MM-DD'))
INTO prior_date
FROM (select *
from chkmfid
union
select *
from dbusrgfc101.chkmfid@hdqt_db) a,manaframenew b
WHERE a.cmfmfid = b.mfcode and
a.cmfdate < p_date and
a.cmfflag = 'Y' and
b.MFCODECUT = l_chkmfid.MFCODECUT;
EXCEPTION WHEN OTHERS THEN
prior_date := to_date('2002-01-01','YYYY-MM-DD');
end; begin
SELECT nvl(SUM(a.xssr),0) xse
into ls_mxb.ckmxse
FROM jxcmframeday a,manaframenew b
WHERE a.jmdmfid = b.mfcode and
a.jmddate > prior_date AND a.jmddate <= p_date and
b.MFCODECUT= l_chkmfid.MFCODECUT;
EXCEPTION WHEN OTHERS THEN
ls_mxb.ckmxse := 0;
end;
sum_xse := sum_xse + ls_mxb.ckmxse;
end loop;
close cur_mfid;
update checkmxb a
set a.ckmxse = sum_xse
where a.ckmcxr = ls_mxb.ckmcxr and
a.ckmmfid = ls_mxb.ckmmfid;
end loop;
close cur_mxb;
SQL> declare
2 cursor v_cursor is select id from test1;
3 type v_refcursor is ref cursor;
4 v_temprefcursor v_refcursor;
5 v_str varchar2(50);
6 begin
7 for v_tempcursor in v_cursor loop
8 open v_temprefcursor for 'select distinct zyz1 from test3 where id=' ||
v_tempcursor.id;
9 loop
10 fetch v_temprefcursor into v_str;
11 dbms_output.put_line('v_str=' || v_str || ';');
12 exit when v_temprefcursor%notfound;
13 end loop;
14 close v_temprefcursor;
15 end loop;
16 end;
17 /
v_str=;
v_str=;
v_str=;
v_str=;
v_str=;
v_str=;
v_str=dfsdf;
v_str=dsfdsf%;
v_str=sdf;
v_str=sdf;
v_str=sdf;
v_str=sdf;
v_str=sdf;
v_str=sdf;
v_str=sdf;
v_str=sdf;
v_str=sdf;
v_str=;
v_str=;
v_str=;
v_str=;
v_str=;
v_str=;
v_str=;
v_str=;PL/SQL procedure successfully completed.