oracle游标

create user jsx identified by 123;
grant create session,create any table,create any procedure to jsx;
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER>"
set sqlprompt "_CONNECT_IDENTIFIER>"
set sqlprompt "_USER>"
alter user jsx quota 100M on users;
connect jsx/123;




CREATE TABLE books(
    bookid NUMBER(6) PRIMARY KEY,
    booknum VARCHAR2(6),
    bookname VARCHAR2(60),
    author VARCHAR2(50),
    publish VARCHAR2(50),
    bookprice NUMBER(8,2),
    category CHAR(10),
    booktime DATE DEFAULT SYSDATE
  );




insert into books(bookid,booknum,bookname,author,bookprice)
values(21,'DB1007','ORACLE数据库','袁鹏飞',50);


set serveroutput on  --显示数据




利用游标显示books表中书价高于40的图书信息
DECLARE
 CURSOR jsx_yb(price NUMBER) IS
 SELECT booknum,bookname,author,bookprice
 FROM books
 WHERE bookprice > price
 ORDER BY booknum;
 v_books jsx_yb%ROWTYPE;
BEGIN
  OPEN jsx_yb(40);
LOOP
FETCH jsx_yb INTO v_books;
EXIT WHEN jsx_yb%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(
jsx_yb%ROWCOUNT || ':' ||
v_books.booknum || ' ' ||
v_books.bookname || ' ' ||
v_books.author || ' ' ||
v_books.bookprice);
END LOOP;
IF jsx_yb%ISOPEN THEN CLOSE jsx_yb;
END IF;
END;
/


利用游标FOR循环显示books表中书价高于40的图书信息
DECLARE
 CURSOR jsx_yb(price NUMBER) IS
 SELECT booknum,bookname,author,bookprice
 FROM books
 WHERE bookprice > price
 ORDER BY booknum;
BEGIN
  FOR v_books IN jsx_yb(40) LOOP
DBMS_OUTPUT.PUT_LINE(
jsx_yb%ROWCOUNT || ':' ||
v_books.booknum || ' ' ||
v_books.bookname || ' ' ||
v_books.author || ' ' ||
v_books.bookprice);
END LOOP;
END;
/




带子查询游标FOR循环显示books表中书价高于40的图书信息


BEGIN
  FOR v_books IN(
SELECT booknum,bookname,author,bookprice
FROM books
  WHERE bookprice > 40
  ORDER BY booknum)
LOOP
DBMS_OUTPUT.PUT_LINE(
v_books.booknum || ' ' ||
v_books.bookname || ' ' ||
v_books.author || ' ' ||
v_books.bookprice);
END LOOP;
END;
/
从scott用户下的dept和emp表中查询每个部门下以及该部门下的所有员工工资信


利用嵌套游标for循环，外层循环中


检索显示部门，内层循环检索显示该部门内员工信息。
alter user scott account lock;




BEGIN
  FOR r_d IN(SELECT * FROM dept) 
LOOP
DBMS_OUTPUT.PUT_LINE(r_d.deptno ||' '||r_d.dname);
FOR r_e IN(SELECT * FROM emp WHERE deptno=r_d.deptno)
LOOP
DBMS_OUTPUT.PUT_LINE(r_e.empno || ' ' ||r_e.ename || ' ' ||r_e.sal);
END LOOP;
END LOOP;
END;
/


用创建游标c_books1的方法实现显示books表的bookid列


DECLARE
 CURSOR c_books1 IS
 SELECT * FROM books;
 v1 c_books1%ROWTYPE;
BEGIN
  OPEN c_books1;
LOOP
FETCH c_books1 INTO v1;
EXIT WHEN c_books1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v1.bookid);
END LOOP;
IF c_books1%ISOPEN THEN CLOSE c_books1;
END IF;
END;
/


用创建游标 c_books2的方法实现显示books表中的全部列
DECLARE
 CURSOR c_books3 IS
 SELECT * FROM books;
 v1 c_books3%ROWTYPE;
BEGIN
  OPEN c_books3;
LOOP
FETCH c_books3 INTO v1;
EXIT WHEN c_books3%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v1.bookid || v1.booknum || v1.bookname || v1.bookprice);
END LOOP;
IF c_books3%ISOPEN THEN CLOSE c_books3;
END IF;
END;
/