/**/
/*******************游标使用的基本格式1***********************/
DECLARE
CURSOR mycur IS
SELECT * FROM emp;
myrecord emp % ROWTYPE;
BEGIN
IF mycur % ISOPEN THEN
OPEN mycur;
END IF ;
FETCH mycur INTO myrecord;
WHILE mycur % FOUND LOOP
DBMS_OUTPUT.PUT_LINE(mycur % ROWCOUNT ||| ' , ' || myrecord.empno || ' , ' || myrecord.ename);
FETCH mycur INTO myrecord;
END LOOP;
CLOSE mycur;
END ;
/
/**/ /*******************游标使用的基本格式2***********************/
DECLARE
CURSOR mycur IS
SELECT * FROM emp;
myrecord emp % ROWTYPE;
BEGIN
IF NOT mycur % ISOPEN THEN
OPEN mycur;
END IF ;
LOOP
FETCH mycur INTO myrecord;
EXIT WHEN mycur % NOTFOUND;
DBMS_OUTPUT.PUT_LINE(mycur % ROWCOUNT ||| ' , ' || myrecord.empno || ' , ' || myrecord.ename);
END LOOP;
CLOSE mycur;
END ;
/
/**/ /*******************游标 For Loop ***********************/
DECLARE
CURSOR mycur IS
SELECT * FROM emp;
BEGIN
FOR cur IN mycur LOOP
DBMS_OUTPUT.PUT_LINE(mycur % ROWCOUNT || ' , ' || cur.empno || ' , ' || cur.ename);
END LOOP;
END ;
/
/**/ /*******************参数化游标***********************/
DECLARE
CURSOR mycur(dept_no varchar2 ) IS -- 参数不定义长度和精度
SELECT empno,ename,dname FROM emp a INNER JOIN dept b ON a.deptno = b.deptno WHERE a.deptno = dept_no;
BEGIN
FOR cur IN mycur( ' 20 ' ) LOOP
DBMS_OUTPUT.PUT_LINE(mycur % ROWCOUNT || ' , ' || cur.empno || ' , ' || cur.ename || ' , ' || cur.dname);
END LOOP;
END ;
/
DECLARE
CURSOR mycur IS
SELECT * FROM emp;
myrecord emp % ROWTYPE;
BEGIN
IF mycur % ISOPEN THEN
OPEN mycur;
END IF ;
FETCH mycur INTO myrecord;
WHILE mycur % FOUND LOOP
DBMS_OUTPUT.PUT_LINE(mycur % ROWCOUNT ||| ' , ' || myrecord.empno || ' , ' || myrecord.ename);
FETCH mycur INTO myrecord;
END LOOP;
CLOSE mycur;
END ;
/
/**/ /*******************游标使用的基本格式2***********************/
DECLARE
CURSOR mycur IS
SELECT * FROM emp;
myrecord emp % ROWTYPE;
BEGIN
IF NOT mycur % ISOPEN THEN
OPEN mycur;
END IF ;
LOOP
FETCH mycur INTO myrecord;
EXIT WHEN mycur % NOTFOUND;
DBMS_OUTPUT.PUT_LINE(mycur % ROWCOUNT ||| ' , ' || myrecord.empno || ' , ' || myrecord.ename);
END LOOP;
CLOSE mycur;
END ;
/
/**/ /*******************游标 For Loop ***********************/
DECLARE
CURSOR mycur IS
SELECT * FROM emp;
BEGIN
FOR cur IN mycur LOOP
DBMS_OUTPUT.PUT_LINE(mycur % ROWCOUNT || ' , ' || cur.empno || ' , ' || cur.ename);
END LOOP;
END ;
/
/**/ /*******************参数化游标***********************/
DECLARE
CURSOR mycur(dept_no varchar2 ) IS -- 参数不定义长度和精度
SELECT empno,ename,dname FROM emp a INNER JOIN dept b ON a.deptno = b.deptno WHERE a.deptno = dept_no;
BEGIN
FOR cur IN mycur( ' 20 ' ) LOOP
DBMS_OUTPUT.PUT_LINE(mycur % ROWCOUNT || ' , ' || cur.empno || ' , ' || cur.ename || ' , ' || cur.dname);
END LOOP;
END ;
/