PROCEDURE P_REPORT_ODREM
(
V_SORD DEV_ORDERMS.SORD%TYPE,
O_CUR OUT MYREF
)
AS
V_CUR MYREF;
V_BUID DEV_ORDERMS.BUID%TYPE;
V_TPRJID PUB_PROJECTS.TPRJID%TYPE;--模版ID
V_PRJID DEV_ORDERMS.PRJID%TYPE;--專案ID
V_PSEQ SYS_SORDSCH.PSEQ%TYPE;--樣單進度對應表ID
V_LENGTH NUMBER;
SQLTXT VARCHAR2(4000);
BEGIN
SQLTXT:='SELECT';
/*1.根據樣單號查找所對應的prjid,BUID*/
SELECT A.PRJID ,A.BUID INTO V_PRJID,V_BUID FROM DUAL LEFT JOIN
(SELECT PRJID,BUID FROM DEV_ORDERMS WHERE SORD = V_SORD) A ON 1=1;
/*2.找專案模版ID*/
SELECT A.TPRJID INTO V_TPRJID FROM DUAL LEFT JOIN
(SELECT TPRJID FROM PUB_PROJECTS WHERE PRJID = V_PRJID) A ON 1=1;
/*3.樣單進度對應表ID*/
SELECT A.PSEQ INTO V_PSEQ FROM DUAL LEFT JOIN
(SELECT PSEQ FROM SYS_SORDSCH WHERE BUID = V_BUID AND TPRJID=V_TPRJID) A ON 1=1;
DECLARE CURSOR V_CURSOR IS SELECT TRIDTP,FLDS,COLNAME FROM SYS_SORDREP WHERE
PSEQ=V_PSEQ;我这个表里面有16条记录,可是下面循环到12次时,SQLTXT=''为空值不知道为什么 BEGIN
FOR ITEM IN V_CURSOR LOOP
SQLTXT:=SQLTXT ||'( SELECT '||ITEM.FLDS||' FROM PUB_PRJTREE WHERE TRIDTP='||ITEM.TRIDTP||' AND PRJID=B.PRJID'||') AS '||ITEM.COLNAME||',';
END LOOP;
END;
V_LENGTH:=LENGTH(SQLTXT)-1;
SQLTXT:=SUBSTR(SQLTXT,1,V_LENGTH)||' FROM PUB_PROJECTS B';
OPEN V_CUR FOR SQLTXT;
O_CUR:=V_CUR;
END P_REPORT_ODREM;
(
V_SORD DEV_ORDERMS.SORD%TYPE,
O_CUR OUT MYREF
)
AS
V_CUR MYREF;
V_BUID DEV_ORDERMS.BUID%TYPE;
V_TPRJID PUB_PROJECTS.TPRJID%TYPE;--模版ID
V_PRJID DEV_ORDERMS.PRJID%TYPE;--專案ID
V_PSEQ SYS_SORDSCH.PSEQ%TYPE;--樣單進度對應表ID
V_LENGTH NUMBER;
SQLTXT VARCHAR2(4000);
BEGIN
SQLTXT:='SELECT';
/*1.根據樣單號查找所對應的prjid,BUID*/
SELECT A.PRJID ,A.BUID INTO V_PRJID,V_BUID FROM DUAL LEFT JOIN
(SELECT PRJID,BUID FROM DEV_ORDERMS WHERE SORD = V_SORD) A ON 1=1;
/*2.找專案模版ID*/
SELECT A.TPRJID INTO V_TPRJID FROM DUAL LEFT JOIN
(SELECT TPRJID FROM PUB_PROJECTS WHERE PRJID = V_PRJID) A ON 1=1;
/*3.樣單進度對應表ID*/
SELECT A.PSEQ INTO V_PSEQ FROM DUAL LEFT JOIN
(SELECT PSEQ FROM SYS_SORDSCH WHERE BUID = V_BUID AND TPRJID=V_TPRJID) A ON 1=1;
DECLARE CURSOR V_CURSOR IS SELECT TRIDTP,FLDS,COLNAME FROM SYS_SORDREP WHERE
PSEQ=V_PSEQ;我这个表里面有16条记录,可是下面循环到12次时,SQLTXT=''为空值不知道为什么 BEGIN
FOR ITEM IN V_CURSOR LOOP
SQLTXT:=SQLTXT ||'( SELECT '||ITEM.FLDS||' FROM PUB_PRJTREE WHERE TRIDTP='||ITEM.TRIDTP||' AND PRJID=B.PRJID'||') AS '||ITEM.COLNAME||',';
END LOOP;
END;
V_LENGTH:=LENGTH(SQLTXT)-1;
SQLTXT:=SUBSTR(SQLTXT,1,V_LENGTH)||' FROM PUB_PROJECTS B';
OPEN V_CUR FOR SQLTXT;
O_CUR:=V_CUR;
END P_REPORT_ODREM;
(
V_SORD DEV_ORDERMS.SORD%TYPE,
O_CUR OUT MYREF
)
AS
V_CUR MYREF;
V_BUID DEV_ORDERMS.BUID%TYPE;
V_TPRJID PUB_PROJECTS.TPRJID%TYPE;--模版ID
V_PRJID DEV_ORDERMS.PRJID%TYPE;--專案ID
V_PSEQ SYS_SORDSCH.PSEQ%TYPE;--樣單進度對應表ID
V_LENGTH NUMBER;
SQLTXT VARCHAR2(4000);
BEGIN
SQLTXT:='SELECT';
/*1.根據樣單號查找所對應的prjid,BUID*/
SELECT A.PRJID ,A.BUID INTO V_PRJID,V_BUID FROM DUAL LEFT JOIN
(SELECT PRJID,BUID FROM DEV_ORDERMS WHERE SORD = V_SORD) A ON 1=1;
/*2.找專案模版ID*/
SELECT A.TPRJID INTO V_TPRJID FROM DUAL LEFT JOIN
(SELECT TPRJID FROM PUB_PROJECTS WHERE PRJID = V_PRJID) A ON 1=1;
/*3.樣單進度對應表ID*/
SELECT A.PSEQ INTO V_PSEQ FROM DUAL LEFT JOIN
(SELECT PSEQ FROM SYS_SORDSCH WHERE BUID = V_BUID AND TPRJID=V_TPRJID) A ON 1=1;
DECLARE CURSOR V_CURSOR IS SELECT TRIDTP,FLDS,COLNAME FROM SYS_SORDREP WHERE
PSEQ=V_PSEQ;
--我这个表里面有16条记录,可是下面循环到12次时,SQLTXT=''为空值不知道为什么 BEGIN
FOR ITEM IN V_CURSOR LOOP
SQLTXT:=SQLTXT ¦ ¦'( SELECT ' ¦ ¦ITEM.FLDS ¦ ¦' FROM PUB_PRJTREE WHERE TRIDTP=' ¦ ¦ITEM.TRIDTP ¦ ¦' AND PRJID=B.PRJID' ¦ ¦') AS ' ¦ ¦ITEM.COLNAME ¦ ¦',';
END LOOP;
--你的表里有16条数据,你的每个字段都有值吗?你赋值的时候是按字段处理的
--你有条件,是不是按条件筛选后就没有数据了
-- SQLTXT:=SQLTXT ¦ ¦'( SELECT ' ¦ ¦ITEM.FLDS ¦ ¦' FROM PUB_PRJTREE WHERE TRIDTP=' ¦ ¦ITEM.TRIDTP ¦ ¦' AND PRJID=B.PRJID' ¦ ¦') AS ' ¦ ¦ITEM.COLNAME ¦ ¦',';
--主要是上面这句话,你把这个串拿出来看看。是个select嵌套。
END;
V_LENGTH:=LENGTH(SQLTXT)-1;
SQLTXT:=SUBSTR(SQLTXT,1,V_LENGTH) ¦ ¦' FROM PUB_PROJECTS B';
OPEN V_CUR FOR SQLTXT;
O_CUR:=V_CUR;
END P_REPORT_ODREM;
CURSOR V_CURSOR IS SELECT * FROM hanm_chakuten_ken;
SQLTXT VARCHAR2(4000);
begin
SQLTXT := '';
for item in V_CURSOR loop
SQLTXT := SQLTXT || '( SELECT' || ITEM.UNSO_BIN_CD || ' FROM PUB_PRJTREE WHERE TRIDTP=' || ITEM.TODOFUKEN_CD || ' AND PRJID=B.PRJID' || ') AS ' || ITEM.CHAKUTEN_CD || ',';
end loop;
o_sql := SQLTXT;
end;
TRIDTP FLDS COLNAME
1 149 STKNO STKNO
2 148 MDCODE 刀模
3 156 TRSUBJECTS T3/T4
4 150 ACDATE 客廳
5 154 ACDATE T3剪
6 204 ACDATE T3轉T4
7 161 ACDATE 制令發放
8 162 TKBY T4負責人
9 209 ACDATE 樣品
10 163 ACDATE 裁斷
11 167 ACDATE 針車
12 164 ACDATE 成型
13 165 ACDATE 客人會義
14 178 ACDATE 客人報告
15 166 ACDATE 交期
16 210 TRDESCS 備注
CURSOR V_CURSOR IS SELECT TRIDTP,FLDS,COLNAME FROM SYS_SORDREP;
SQLTXT VARCHAR2(4000);
begin
SQLTXT := '';
for item in V_CURSOR loop
SQLTXT:=SQLTXT ¦ ¦'( SELECT ' ¦ ¦ITEM.FLDS ¦ ¦' FROM PUB_PRJTREE WHERE TRIDTP=' ¦ ¦ITEM.TRIDTP ¦ ¦' AND PRJID=B.PRJID' ¦ ¦') AS ' ¦ ¦ITEM.COLNAME ¦ ¦',';
end loop;
o_sql := SQLTXT;
end;你把这个导入你的DB测试一下。