游标步骤

最新推荐文章于 2019-05-06 21:52:53 发布

wdyr321

最新推荐文章于 2019-05-06 21:52:53 发布

阅读量629

点赞数

分类专栏： oracle 文章标签：游标 oracle

oracle 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

DECLARE
  v_jobid     employees.job_id%TYPE;     -- variable for job_id
  v_lastname  employees.last_name%TYPE;  -- variable for last_name
  CURSOR c1 IS SELECT last_name, job_id FROM employees 
                 WHERE REGEXP_LIKE (job_id, 'S[HT]_CLERK');
  v_employees employees%ROWTYPE;         -- record variable for row
  CURSOR c2 is SELECT * FROM employees 
                 WHERE REGEXP_LIKE (job_id, '[ACADFIMKSA]_M[ANGR]');
BEGIN
  OPEN c1; -- open the cursor before fetching
  LOOP
    FETCH c1 INTO v_lastname, v_jobid; -- fetches 2 columns into variables
    EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE( RPAD(v_lastname, 25, ' ') || v_jobid );
  END LOOP;
  CLOSE c1;
  DBMS_OUTPUT.PUT_LINE( '-------------------------------------' );
  OPEN c2;
  LOOP
    FETCH c2 INTO v_employees; -- fetches entire row into the v_employees record
    EXIT WHEN c2%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE( RPAD(v_employees.last_name, 25, ' ') ||
                               v_employees.job_id );
  END LOOP;
  CLOSE c2;
END;
/

1、声明变量

2、声明游标

3、打开游标

4、获取游标

5、关闭游标

另：在模拟操作过程中的错误 ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
 
 
  
  方法1：set serveroutput on size 10000000
  
  方法2：exec dbms_output.enable(999999999999999999999);经测试皆可用