CREATE OR REPLACE PROCEDURE SP..(...)IS tempSQL varchar2(1000); TYPE CUR IS REF CURSOR; cur1 CUR; BEGIN tmpSQL:=''; tmpSQL:='SELECT A.empno,A.enameA.jobA.sal,B.DEPTNO,B.dnam,B.loc FROM emp A,dept B '; tmpSQL:=tmpSQL||'WHERE A.FIELD=B.FIELD AND ....'; OPEN cur1 FOR tmpSQL; WHILE TRUE LOOP FETCH cur1 INTO 自己申明的變量 EXIT WHEN cur1 %NOTFOUND; //要操作的代碼塊 END LOOP;END;
tempSQL varchar2(1000);
TYPE CUR IS REF CURSOR;
cur1 CUR;
BEGIN
tmpSQL:='';
tmpSQL:='SELECT A.empno,A.enameA.jobA.sal,B.DEPTNO,B.dnam,B.loc FROM emp A,dept B ';
tmpSQL:=tmpSQL||'WHERE A.FIELD=B.FIELD AND ....';
OPEN cur1 FOR tmpSQL;
WHILE TRUE LOOP
FETCH cur1 INTO 自己申明的變量
EXIT WHEN cur1 %NOTFOUND;
//要操作的代碼塊
END LOOP;END;