Oracle游标处理数据、游标for循环、游标表达式

1、 游标处理数据

create table t1 as select * from all_objects;
commit;
insert into t1 select * from t1;
commit;
insert into t1 select * from t1;
commit;
create table t2 as select owner,object_id,object_name from all_objects where 1=2;

DECLARE	
CURSOR cur_dt is select owner,object_id,object_name from t1;
v_owner t1.owner%type;
v_objectid t1.object_id%type;
v_objectname t1.object_name%type;
BEGIN
 OPEN cur_dt;
  LOOP
    FETCH cur_dt
      INTO v_owner,v_objectid, v_objectname;
    EXIT WHEN cur_dt%NOTFOUND;
    insert into t2(owner,object_id,object_name) values(v_owner,v_objectid,v_objectname);
  END LOOP;
  COMMIT;
  CLOSE cur_dt;
END;
/




create table t(id number(4) primary key ,num varchar2(15),state number(1));
INSERT INTO t (id, num, state) SELECT rownum, abs(dbms_random.random), MOD(rownum, 2) + 1 FROM dual CONNECT BY rownum <= 1000;
更新num字段至15位，以state的开头，中间以0补齐
DECLARE
  CURSOR cur_dt IS
    SELECT id, num, state FROM t FOR UPDATE OF num;
  v_id    t.id%TYPE;
  v_num   t.num%TYPE;
  v_state t.state%TYPE;
BEGIN
  OPEN cur_dt;
  LOOP
    FETCH cur_dt
      INTO v_id, v_num, v_state;
    EXIT WHEN cur_dt%NOTFOUND;
    IF length(v_num) <= 14 THEN
      UPDATE t SET num = v_state || lpad(v_num, 14, '0') WHERE id = v_id;
      -- UPDATE t SET num = v_state || lpad(v_num, 14, '0') WHERE CURRENT OF cur_dt;
    ELSE
      UPDATE t SET num = v_state || substr(v_num, -14) WHERE id = v_id;
    END IF;
  END LOOP;
  COMMIT;
  CLOSE cur_dt;
END;
/


删除num字段尾号为奇数的记录
DECLARE
  CURSOR cur_dt IS
    SELECT id, num FROM t for update;
  v_id   t.id%TYPE;
  v_num  t.num%TYPE;
  v_type NUMBER;
BEGIN
  OPEN cur_dt;
  LOOP
    FETCH cur_dt
      INTO v_id, v_num;
    EXIT WHEN cur_dt%NOTFOUND;
    v_type := to_number(substr(v_num, -1));
    IF MOD(v_type, 2) = 1 THEN
      DELETE t --WHERE id = v_id;
      WHERE CURRENT OF cur_dt;
    END IF;
  END LOOP;
  COMMIT;
  CLOSE cur_dt;
END;
/

2、游标for循环

BEGIN
  FOR emp_record IN (SELECT employee_id, last_name FROM employees
                                         WHERE department_id = 30) LOOP
    DBMS_OUTPUT.PUT_LINE(emp_record.employee_id || ' ' || emp_record.last_name);
  END LOOP;
END;
/



--create table t1 as select * from all_objects where rownum<=10;
--create table t2 as select * from t1 where 1=2;
BEGIN
 FOR my_cur IN (SELECT * FROM t1) LOOP
    INSERT INTO t2 VALUES my_cur;
    COMMIT;
  END LOOP;
END;
/

3、游标表达式

DECLARE
  TYPE emp_cursor IS REF CURSOR;
  my_cursor emp_cursor;
  v_ename   emp.ename%TYPE;
  v_sal     emp.sal%TYPE;
  v_deptno  dept.deptno%TYPE := &p_deptno;
  v_dname   dept.dname%TYPE;
BEGIN
  SELECT dname INTO v_dname FROM dept WHERE deptno = v_deptno;
  dbms_output.put_line('Department:' || v_dname);
  OPEN my_cursor FOR
    SELECT ename, sal FROM emp WHERE deptno = v_deptno;
  LOOP
    FETCH my_cursor
      INTO v_ename, v_sal;
    EXIT WHEN my_cursor%NOTFOUND;
    dbms_output.put_line('name:' || v_ename ||'--'|| 'salary:' || v_sal);
  END LOOP;
  CLOSE my_cursor;
END;
/

申明：以上所有代码均来自墨天轮官网，便于后期自己查询使用