返回值为一个结果集代码,PLSQL代码库,德仔网

代码语言
代码分类
【PLSQL】返回值为一个结果集
作者:Dezai.CN / 发布于2011/9/14/ 968
<div>
<font color="#800080">create or replace procedure tjh_getprocessnumber(p_tlplantid VARCHAR2,
p_glplantid VARCHAR2,
p_inmanchinid VARCHAR2,
p_inplanyear VARCHAR2,
p_inplanmonth VARCHAR2,
P_CURSOR OUT TYPES.CURSORTYPE) is</font>
<font color="#800080">VPRODUCENUMBER tzz_contractdata.PRODUCENUMBER%TYPE; --1生产号
VPLANTLOTID tjh_plantproductionplan.PLANLOTID%TYPE; --2计划批次
VSTEELNUMBER tzz_contractdata.STEELNUMBER%TYPE; --3钢号
VSTANDARD tzz_contractdata.STANDARD%TYPE; --4标准
VSHAPE tzz_contractdata.SHAPE%TYPE; --5形状
VWEIGHT tzz_contractdata.ORDERAMOUNT%TYPE; --6重量
VSTEELNAME DT_CIMS.XS_GHZD.GH%TYPE; --7钢号名称
VSTANDARDNAME DT_CIMS.XS_BZZD.BZ%TYPE; --8标准名称
VXZ DT_CIMS.XS_XZZD.XZ%TYPE; --9形状名称
VSECTIONLENGTH tzz_contractdata.SECTIONLENGTH%TYPE; --10长
VSECTIONWIDTH tzz_contractdata.SECTIONWIDTH%TYPE; --11宽
VSECTIONHIGH tzz_contractdata.SECTIONHIGH%TYPE; --12长
VSPCS VARCHAR2(20); --13规格 
vduestatus tzz_contractdata.duestatus%TYPE; --14交货状态 
vcontractrequire tzz_contractdata.contractrequire%TYPE; --15合同要求
cproducenumber tzz_contractdata.PRODUCENUMBER%TYPE; --16游标中得到的生产号信息
countnumber number; --17统计函数中存在的个数
VFSTANDARD tzz_contractdata.additionalstandard%TYPE; --副标准
VLENGTH tzz_contractdata.length%TYPE; --长度
getnextplant varchar2(20); ---得到函数查询出的信息
NUMTABLE NUMBER; --记录表名的数量(查看是否有叫新建的临时表的表) 
NUMPERAMOUNT NUMBER; --PERAMOUT记录的数量 
STRLIST VARCHAR2(4000);</font>
<font color="#800080">cursor CURSTOCK is
select distinct b.producenumber
from tzz_contractdata a, tjh_plantproductionplan b
where a.producenumber = b.producenumber and
substr(a.machineid, 0, 2) = p_tlplantid and
a.pcyf = to_number(p_inplanmonth) and
b.produceyear = p_inplanyear and b.producemonth = p_inplanmonth and
b.plantid = p_tlplantid and a.contractstatus not like '已%' and
b.ifhavebill = '0';
strType CURSTOCK%rowtype;</font>
<font color="#800080">cursor CURSTODT is
select distinct b.producenumber
from tzz_contractdata a, tjh_plantproductionplan b
where a.producenumber = b.producenumber and
substr(a.machineid, 0, 2) = p_tlplantid and
a.pcyf = to_number(p_inplanmonth) and
b.produceyear = p_inplanyear and b.plantid = p_tlplantid and
a.contractstatus not like '已%' and b.ifhavebill = '0' and
b.processname in
(select tc.id
from TGY_STANDARDPROCESS tc
where substr(tc.equipmenttype, 0, 2) = p_tlplantid and
tc.ifkeyprocess = 1 and tc.equipmenttype = p_inmanchinid);
strTypeTwo CURSTODT%rowtype;</font>
<font color="#800080">begin
--建立一个临时表TJH_GETPLANTID，
SELECT COUNT(*)
INTO NUMTABLE
FROM USER_TABLES
WHERE TABLE_NAME = 'TJH_GETPLANTID';
IF NUMTABLE < 1 THEN

STRLIST := 'CREATE GLOBAL TEMPORARY TABLE TJH_GETPLANTID ' ||
'(PRODUCENUMBER 　 VARCHAR2(20),' ||
'PLANTLOTID NUMBER(10),' ||
'STEELNUMBER VARCHAR2(20),' ||
'STANDARD VARCHAR2(20),' ||
'FTANDARD VARCHAR2(20),' ||
'SHAPE VARCHAR2(20),' ||
'SPCS VARCHAR2(20),' ||
'orderamount NUMBER(10,4),' ||
/* 'STEELNAME VARCHAR2(50),' ||*/
/* 'STANDARDNAME VARCHAR2(50),' ||*/
/* 'XZ VARCHAR2(6),' ||*/
'SECTIONLENGTH NUMBER(10,4),' ||
'SECTIONWIDTH NUMBER(10,4),' ||
'SECTIONHIGH NUMBER(10,4),' ||
'LENGTH VARCHAR2(20),' ||
'duestatus VARCHAR2(20),' ||
'CONTRACTREQUIRE VARCHAR2(10))' ||
' ON COMMIT DELETE ROWS ';

EXECUTE IMMEDIATE STRLIST;
END IF;</font>
<font color="#800080">if (p_inmanchinid = '' or p_inmanchinid = null) then

open CURSTOCK;
fetch CURSTOCK
into strType;
cproducenumber := strType.producenumber;
while CURSTOCK%found loop
select count(*)
into countnumber
from (select F_GETNEXTPLANT(cproducenumber, p_tlplantid, p_glplantid)
from dual) get;
if (countnumber = 0) then
exit;
else
select F_GETNEXTPLANT(cproducenumber, p_tlplantid, p_glplantid)
into getnextplant
from dual;
if (getnextplant = '00') then
exit;
else
select distinct h.producenumber,
p.planlotid,
h.steelnumber,
h.standard,
h.additionalstandard as fstandard,
h.shape,
f_specs(h.shape,
h.sectionlength,
h.sectionwidth,
h.sectionhigh) as specs,
h.orderamount,
h.sectionlength,
h.sectionwidth,
h.sectionhigh,
h.length,
h.duestatus,
h.contractrequire
into VPRODUCENUMBER,
VPLANTLOTID,
VSTEELNUMBER,
VSTANDARD,
VFSTANDARD,
VSHAPE,
VSPCS,
VWEIGHT,
VSECTIONLENGTH,
VSECTIONWIDTH,
VSECTIONHIGH,
VLENGTH,
vduestatus,
vcontractrequire
from tzz_contractdata h, tjh_plantproductionplan p
where h.producenumber = p.producenumber and
h.producenumber = cproducenumber and
h.contractstatus not like '已%';

STRLIST := 'INSERT INTO TJH_GETPLANTID (' || 'PRODUCENUMBER,' ||
'PLANTLOTID,' || 'STEELNUMBER,' || 'STANDARD,' ||
'FSTANDARD,' || 'SHAPE,' || 'SPCS,' || 'orderamount,' ||
'SECTIONLENGTH,' || 'SECTIONWIDTH,' || 'SECTIONHIGH,' ||
'LENGTH,' || 'duestatus,' || 'contractrequire) ' ||
'VALUES (''' || VPRODUCENUMBER || ''',''' ||
VPLANTLOTID || ''',''' || VSTEELNUMBER || ''',''' ||
VSTANDARD || ''',''' || VFSTANDARD || ''',''' ||
VSHAPE || ''',''' || VSPCS || ''',''' || VWEIGHT ||
''',''' || VSECTIONLENGTH || ''',''' || VSECTIONWIDTH ||
''',''' || VSECTIONHIGH || ''',''' || VLENGTH ||
''',''' || vduestatus || ''',''' || vcontractrequire ||
''')';

EXECUTE IMMEDIATE STRLIST;

end if;
end if;
fetch CURSTOCK
into strType;
end loop;
CLOSE CURSTOCK;
else
open CURSTODT;
fetch CURSTODT
into strTypeTwo;
cproducenumber := strTypeTwo.producenumber;
while CURSTODT%found loop
select count(*)
into countnumber
from (select F_GETNEXTPLANT(cproducenumber, p_tlplantid, p_glplantid)
from dual) get;
if (countnumber = 0) then
exit;
else
select F_GETNEXTPLANT(cproducenumber, p_tlplantid, p_glplantid)
into getnextplant
from dual;
if (getnextplant = '00') then
exit;
else
select distinct h.producenumber,
p.planlotid,
h.steelnumber,
h.standard,
h.additionalstandard as fstandard,
h.shape,
f_specs(h.shape,
h.sectionlength,
h.sectionwidth,
h.sectionhigh) as specs,
h.orderamount,
h.sectionlength,
h.sectionwidth,
h.sectionhigh,
h.length,
h.duestatus,
h.contractrequire
into VPRODUCENUMBER,
VPLANTLOTID,
VSTEELNUMBER,
VSTANDARD,
VFSTANDARD,
VSHAPE,
VSPCS,
VWEIGHT,
VSECTIONLENGTH,
VSECTIONWIDTH,
VSECTIONHIGH,
VLENGTH,
vduestatus,
vcontractrequire
from tzz_contractdata h, tjh_plantproductionplan p
where h.producenumber = p.producenumber and
h.producenumber = cproducenumber and
h.contractstatus not like '已%';

STRLIST := 'INSERT INTO TJH_GETPLANTID (' || 'PRODUCENUMBER,' ||
'PLANTLOTID,' || 'STEELNUMBER,' || 'STANDARD,' ||
'FSTANDARD,' || 'SHAPE,' || 'SPCS,' || 'orderamount,' ||
'SECTIONLENGTH,' || 'SECTIONWIDTH,' || 'SECTIONHIGH,' ||
'LENGTH,' || 'duestatus,' || 'contractrequire) ' ||
'VALUES (''' || VPRODUCENUMBER || ''',''' ||
VPLANTLOTID || ''',''' || VSTEELNUMBER || ''',''' ||
VSTANDARD || ''',''' || VFSTANDARD || ''',''' ||
VSHAPE || ''',''' || VSPCS || ''',''' || VWEIGHT ||
''',''' || VSECTIONLENGTH || ''',''' || VSECTIONWIDTH ||
''',''' || VSECTIONHIGH || ''',''' || VLENGTH ||
''',''' || vduestatus || ''',''' || vcontractrequire ||
''')';

EXECUTE IMMEDIATE STRLIST;
end if;
end if;<img class="editorToolbarButtonOver" src="http://st.blog.163.com/style/common/htmlEditor/place.gif" __1315973864442__="ev_8251493592" alt="" />
fetch CURSTODT
into strTypeTwo;

end loop;
close CURSTODT;
end if;</font>
<font color="#800080">OPEN P_CURSOR FOR
select producenumber,
planlotid,
steelnumber,
standard,
fstandard,
shape,
specs,
orderamount,
sectionlength,
sectionwidth,
sectionhigh,
length,
duestatus,
contractrequire
from TJH_GETPLANTID;</font>
<font color="#800080">end tjh_getprocessnumber;</font>
</div>
试试其它关键字
　结果集　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)