FUNCTION F_GETPARENT ( V_PID NUMBER, V_TBCODE VARCHAR2 )RETURN VARCHAR2 AS V_TBN VARCHAR2(60); V_PIDFLD VARCHAR2(60); V_SIDFLD VARCHAR2(60); V_SEQFLD VARCHAR2(60); SQLTXT VARCHAR2(400); V_TPPID NUMBER; V_TPSEQ NUMBER; V_TPTXT VARCHAR2(60); BEGIN SELECT A.TBN,A.PID,A.SID,NVL(A.SEQ,'0') INTO V_TBN,V_PIDFLD,V_SIDFLD,V_SEQFLD FROM DUAL LEFT JOIN (SELECT TBN,PID,SID, SEQ FROM SYS_TREETBS WHERE LOWER(TBCODE) = TRIM(LOWER(V_TBCODE))) A ON 1=1; SQLTXT := 'SELECT NVL(A.PID,-1),A.SEQ FROM DUAL LEFT JOIN (SELECT '||V_PIDFLD||' AS PID, '||V_SEQFLD||' AS SEQ FROM '|| V_TBN ||' WHERE '|| V_SIDFLD ||'='||V_PID ||') A ON 1=1';
DBMS_OUTPUT.put_line(SQLTXT);
EXECUTE IMMEDIATE SQLTXT INTO V_TPPID,V_TPSEQ;
V_TPTXT := '00';
WHILE(V_TPPID <> -1) LOOP V_TPTXT := '00'||'.'||TRIM(V_TPTXT); SQLTXT := 'SELECT NVL(A.PID,-1),A.SEQ FROM DUAL LEFT JOIN (SELECT '||V_PIDFLD||' AS PID, '||V_SEQFLD||' AS SEQ FROM '|| V_TBN ||' WHERE '|| V_SIDFLD ||'='||V_TPPID ||') A ON 1=1'; EXECUTE IMMEDIATE SQLTXT INTO V_TPPID,V_TPSEQ; END LOOP; RETURN V_TPTXT; END; 幫我看看,這樣子會死循環嗎?
FUNCTION F_GETPARENT ( V_PID NUMBER, V_TBCODE VARCHAR2 )RETURN VARCHAR2 AS V_TBN VARCHAR2(60); V_PIDFLD VARCHAR2(60); V_SIDFLD VARCHAR2(60); V_SEQFLD VARCHAR2(60); SQLTXT VARCHAR2(400); V_TPPID NUMBER; V_TPSEQ NUMBER; V_TPTXT VARCHAR2(60); BEGIN SELECT A.TBN,A.PID,A.SID,NVL(A.SEQ,'0') INTO V_TBN,V_PIDFLD,V_SIDFLD,V_SEQFLD FROM DUAL LEFT JOIN (SELECT TBN,PID,SID, SEQ FROM SYS_TREETBS WHERE LOWER(TBCODE) = TRIM(LOWER(V_TBCODE))) A ON 1=1; SQLTXT := 'SELECT NVL(A.PID,-1),A.SEQ FROM DUAL LEFT JOIN (SELECT ' ¦ ¦V_PIDFLD ¦ ¦' AS PID, ' ¦ ¦V_SEQFLD ¦ ¦' AS SEQ FROM ' ¦ ¦ V_TBN ¦ ¦' WHERE ' ¦ ¦ V_SIDFLD ¦ ¦'=' ¦ ¦V_PID ¦ ¦') A ON 1=1';
DBMS_OUTPUT.put_line(SQLTXT);
EXECUTE IMMEDIATE SQLTXT INTO V_TPPID,V_TPSEQ;
V_TPTXT := '00';
WHILE(V_TPPID <> -1) LOOP V_TPTXT := '00' ¦ ¦'.' ¦ ¦TRIM(V_TPTXT); SQLTXT := 'SELECT NVL(A.PID,-1),A.SEQ FROM DUAL LEFT JOIN (SELECT ' ¦ ¦V_PIDFLD ¦ ¦' AS PID, ' ¦ ¦V_SEQFLD ¦ ¦' AS SEQ FROM ' ¦ ¦ V_TBN ¦ ¦' WHERE ' ¦ ¦ V_SIDFLD ¦ ¦'=' ¦ ¦V_TPPID ¦ ¦') A ON 1=1'; EXECUTE IMMEDIATE SQLTXT INTO V_TPPID,V_TPSEQ; END LOOP; RETURN V_TPTXT; END; 好看些
我的oracle 10.2,Xp系统执行没问题!SQL> exec dbms_output.enable(20000);PL/SQL 过程已成功完成。SQL> set serveroutput on size 1000000
SQL>
Oracle 10.2SQL> exec dbms_output.enable(20000);PL/SQL procedure successfully completed.這樣子也OK,SQL運行時不行還是報錯
再增大点看看呢!
(
V_PID NUMBER,
V_TBCODE VARCHAR2
)RETURN VARCHAR2
AS
V_TBN VARCHAR2(60);
V_PIDFLD VARCHAR2(60);
V_SIDFLD VARCHAR2(60);
V_SEQFLD VARCHAR2(60);
SQLTXT VARCHAR2(400);
V_TPPID NUMBER;
V_TPSEQ NUMBER;
V_TPTXT VARCHAR2(60);
BEGIN
SELECT A.TBN,A.PID,A.SID,NVL(A.SEQ,'0') INTO V_TBN,V_PIDFLD,V_SIDFLD,V_SEQFLD FROM DUAL LEFT JOIN
(SELECT TBN,PID,SID, SEQ FROM SYS_TREETBS WHERE LOWER(TBCODE) = TRIM(LOWER(V_TBCODE))) A
ON 1=1;
SQLTXT := 'SELECT NVL(A.PID,-1),A.SEQ FROM DUAL LEFT JOIN (SELECT '||V_PIDFLD||' AS PID, '||V_SEQFLD||' AS SEQ FROM '|| V_TBN ||' WHERE '|| V_SIDFLD ||'='||V_PID ||') A ON 1=1';
DBMS_OUTPUT.put_line(SQLTXT);
EXECUTE IMMEDIATE SQLTXT INTO V_TPPID,V_TPSEQ;
V_TPTXT := '00';
WHILE(V_TPPID <> -1)
LOOP
V_TPTXT := '00'||'.'||TRIM(V_TPTXT);
SQLTXT := 'SELECT NVL(A.PID,-1),A.SEQ FROM DUAL LEFT JOIN (SELECT '||V_PIDFLD||' AS PID, '||V_SEQFLD||' AS SEQ FROM '|| V_TBN ||' WHERE '|| V_SIDFLD ||'='||V_TPPID ||') A ON 1=1';
EXECUTE IMMEDIATE SQLTXT INTO V_TPPID,V_TPSEQ;
END LOOP;
RETURN V_TPTXT;
END;
幫我看看,這樣子會死循環嗎?
(
V_PID NUMBER,
V_TBCODE VARCHAR2
)RETURN VARCHAR2
AS
V_TBN VARCHAR2(60);
V_PIDFLD VARCHAR2(60);
V_SIDFLD VARCHAR2(60);
V_SEQFLD VARCHAR2(60);
SQLTXT VARCHAR2(400);
V_TPPID NUMBER;
V_TPSEQ NUMBER;
V_TPTXT VARCHAR2(60);
BEGIN
SELECT A.TBN,A.PID,A.SID,NVL(A.SEQ,'0') INTO V_TBN,V_PIDFLD,V_SIDFLD,V_SEQFLD FROM DUAL LEFT JOIN
(SELECT TBN,PID,SID, SEQ FROM SYS_TREETBS WHERE LOWER(TBCODE) = TRIM(LOWER(V_TBCODE))) A
ON 1=1;
SQLTXT := 'SELECT NVL(A.PID,-1),A.SEQ FROM DUAL LEFT JOIN (SELECT ' ¦ ¦V_PIDFLD ¦ ¦' AS PID, ' ¦ ¦V_SEQFLD ¦ ¦' AS SEQ FROM ' ¦ ¦ V_TBN ¦ ¦' WHERE ' ¦ ¦ V_SIDFLD ¦ ¦'=' ¦ ¦V_PID ¦ ¦') A ON 1=1';
DBMS_OUTPUT.put_line(SQLTXT);
EXECUTE IMMEDIATE SQLTXT INTO V_TPPID,V_TPSEQ;
V_TPTXT := '00';
WHILE(V_TPPID <> -1)
LOOP
V_TPTXT := '00' ¦ ¦'.' ¦ ¦TRIM(V_TPTXT);
SQLTXT := 'SELECT NVL(A.PID,-1),A.SEQ FROM DUAL LEFT JOIN (SELECT ' ¦ ¦V_PIDFLD ¦ ¦' AS PID, ' ¦ ¦V_SEQFLD ¦ ¦' AS SEQ FROM ' ¦ ¦ V_TBN ¦ ¦' WHERE ' ¦ ¦ V_SIDFLD ¦ ¦'=' ¦ ¦V_TPPID ¦ ¦') A ON 1=1';
EXECUTE IMMEDIATE SQLTXT INTO V_TPPID,V_TPSEQ;
END LOOP;
RETURN V_TPTXT;
END;
好看些