我想请教大家一个问题,我写了个存储过程,怎么只能循环12次呢,12次以后,变量就是空值了,我做了个测试!
大家帮我看看那里出问题了,谢谢!
FUNCTION F_REPORT_ODREM
(
V_SORD DEV_ORDERMS.SORD%TYPE
)
RETURN MYREF
IS
RC 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(1000);
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;
BEGIN
FOR ITEM IN V_CURSOR LOOP
SQLTXT:=SQLTXT ||'( SELECT '||ITEM.FLDS||' FROM PUB_PRJTREE WHERE TRIDTP='||ITEM.TRIDTP||' AND PRJID='||V_PRJID||') AS '||ITEM.COLNAME||',';
END LOOP;
END;
V_LENGTH:=LENGTH(SQLTXT)-1;
SQLTXT:=SUBSTR(SQLTXT,1,V_LENGTH)||' FROM DUAL';
OPEN RC FOR SQLTXT ;
RETURN RC;
END F_REPORT_ODREM;
大家帮我看看那里出问题了,谢谢!
FUNCTION F_REPORT_ODREM
(
V_SORD DEV_ORDERMS.SORD%TYPE
)
RETURN MYREF
IS
RC 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(1000);
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;
BEGIN
FOR ITEM IN V_CURSOR LOOP
SQLTXT:=SQLTXT ||'( SELECT '||ITEM.FLDS||' FROM PUB_PRJTREE WHERE TRIDTP='||ITEM.TRIDTP||' AND PRJID='||V_PRJID||') AS '||ITEM.COLNAME||',';
END LOOP;
END;
V_LENGTH:=LENGTH(SQLTXT)-1;
SQLTXT:=SUBSTR(SQLTXT,1,V_LENGTH)||' FROM DUAL';
OPEN RC FOR SQLTXT ;
RETURN RC;
END F_REPORT_ODREM;
2楼的说得没有错。
吧sqltxt 定义为varchar2(32767)是个好习惯--不用怕浪费,oracle不会一下分配这么多的。
其次,你的过程至少应该有处理异常的部分,以便显示错误,除非你的过程的异常时交给外层程序来处理的。