【SQL开发】PLSQL游标演示

一 显式游标

1 游标，不使用变量

DECLARE
  CURSOR cur_emp IS
    SELECT * FROM emp;

  v_emp cur_emp%ROWTYPE;

BEGIN
  OPEN cur_emp;

  LOOP
    FETCH cur_emp
      INTO v_emp;
  
    EXIT WHEN cur_emp%NOTFOUND;
  
    dbms_output.put_line(v_emp.ename);
  
  END LOOP;

  CLOSE cur_emp;

END;

2 使用变量

DECLARE
  CURSOR cur_emp(v_dept VARCHAR2) IS
    SELECT * FROM emp WHERE deptno = v_dept;

  v_emp cur_emp%ROWTYPE;

BEGIN
  OPEN cur_emp(10);

  LOOP
    FETCH cur_emp
      INTO v_emp;
  
    EXIT WHEN cur_emp%NOTFOUND;
  
    dbms_output.put_line(v_emp.ename);
  
  END LOOP;

  CLOSE cur_emp;

END;

3 变量

DECLARE
  v_dept NUMBER := 10;

  CURSOR cur_emp IS
    SELECT * FROM emp WHERE deptno = v_dept;

  v_emp cur_emp%ROWTYPE;

BEGIN

  OPEN cur_emp;

  LOOP
    FETCH cur_emp
      INTO v_emp;
  
    EXIT WHEN cur_emp%NOTFOUND;
  
    dbms_output.put_line(v_emp.ename);
  
  END LOOP;

  CLOSE cur_emp;

END;

4 游标批量作业

DECLARE

  CURSOR cur_emp IS
    SELECT * FROM emp;

  TYPE tab_emp IS TABLE OF cur_emp%ROWTYPE;

  v_emp tab_emp;

BEGIN

  OPEN cur_emp;

  LOOP
    FETCH cur_emp BULK COLLECT
      INTO v_emp;
  
    dbms_output.put_line('Row :' || cur_emp%ROWCOUNT);
  
    FOR j IN v_emp.first .. v_emp.last LOOP
      dbms_output.put_line(v_emp(j).ename);
    END LOOP;
  
    EXIT WHEN cur_emp%NOTFOUND;
  
  END LOOP;

  CLOSE cur_emp;

END;

5 批量游标分段处理

DECLARE
  CURSOR cur_emp IS
    SELECT * FROM emp;

  TYPE tab_emp IS TABLE OF cur_emp%ROWTYPE;

  v_emp tab_emp;
BEGIN

  OPEN cur_emp;

  LOOP
    FETCH cur_emp BULK COLLECT
      INTO v_emp LIMIT 3;
  
    dbms_output.put_line('Row :' || cur_emp%ROWCOUNT);
  
    FOR j IN v_emp.first .. v_emp.last LOOP
      dbms_output.put_line(v_emp(j).ename);
    END LOOP;
  
    EXIT WHEN cur_emp%NOTFOUND;
  
  END LOOP;

  CLOSE cur_emp;
END;

6 批量游标分段DML处理

DECLARE
  CURSOR cur_emp IS
    SELECT * FROM emp;

  TYPE tab_emp IS TABLE OF cur_emp%ROWTYPE;

  v_emp tab_emp;
BEGIN

  OPEN cur_emp;

  LOOP
    FETCH cur_emp BULK COLLECT
      INTO v_emp LIMIT 3;
  
    dbms_output.put_line('Row :' || cur_emp%ROWCOUNT);
  
    FORALL j IN v_emp.first .. v_emp.last
      UPDATE emp SET comm = v_emp(j).sal;
  
    COMMIT;
  
    EXIT WHEN cur_emp%NOTFOUND;
  
  END LOOP;

  CLOSE cur_emp;
END;

二 隐式游标

1 隐式游标属性

DECLARE
  v_qty NUMBER;

  v_flag BOOLEAN := FALSE;
BEGIN
  UPDATE emp SET comm = 0 WHERE empno = 7369;

  v_qty  := SQL%ROWCOUNT;
  v_flag := SQL%FOUND;

  COMMIT;

  dbms_output.put_line('Qty:' || v_qty);

  IF v_flag THEN
    dbms_output.put_line('Updated success!');
  ELSE
    dbms_output.put_line('Updated failed!');
  END IF;
END;

2 for loop形式隐式游标

BEGIN
  FOR rec IN (SELECT * FROM emp) LOOP
    IF rec.ename = 'JONES' THEN
      dbms_output.put_line(rec.ename || ' is good guy!');
    ELSE
      dbms_output.put_line(rec.ename || '''s manager is ' || rec.mgr);
    END IF;
  END LOOP;
END;

3 有返回值的隐式游标

DECLARE
  v_qty NUMBER;

  v_comm NUMBER;

  v_name VARCHAR2(100);
BEGIN
  UPDATE emp
     SET comm = 1000
   WHERE empno = 7369
  RETURNING ename INTO v_name;

  COMMIT;

  dbms_output.put_line(v_name);
END;

三 动态游标

1 定义动态游标

DECLARE
  TYPE cur IS REF CURSOR;
  v_cur cur;

  v_empno scott.emp.empno%TYPE;
  v_ename scott.emp.ename%TYPE;
  v_job   scott.emp.job%TYPE;

  v_sql VARCHAR2(1000);
BEGIN
  --v_sql := 'SELECT empno,ename,job  FROM scott.emp where empno in(7369,7499)';

  OPEN v_cur FOR
    SELECT empno, ename, job FROM scott.emp WHERE empno IN (7369, 7499);

  LOOP
    FETCH v_cur
      INTO v_empno, v_ename, v_job;
    EXIT WHEN v_cur%NOTFOUND;
    dbms_output.put_line('EmpNO.:' || v_empno || ' EName:' || v_ename ||
                         ' Job:' || v_job);
  END LOOP;

  CLOSE v_cur;
END;