动态游标_record

最新推荐文章于 2021-01-18 21:04:16 发布

5icode.top

最新推荐文章于 2021-01-18 21:04:16 发布

阅读量916

点赞数

分类专栏： oracle 文章标签：动态游标_record

本文链接：https://blog.csdn.net/hzdxw/article/details/41659543

版权

oracle 专栏收录该内容

30 篇文章 0 订阅

订阅专栏

CREATE OR REPLACE PROCEDURE test_cur is
  type mycursor_type is ref cursor;
  c1 mycursor_type;
  ----使用
  --打开动态游标，再往下就都一样了
  type record_type is record(
        myid  emp.empno%type,
        myname  emp.ename%type,
        myjob  emp.job%type,
        mymgr  emp.mgr%type,
        mysal  emp.sal%type,
        mydeptid  emp.deptno%type
  );
  r_c1 record_type;
  --r_c1 emp%rowtype;
begin
  open c1 for
    select empno,ename,job,mgr,sal,deptno from emp;--注意列的个数
  loop
    fetch c1
      into r_c1;
    -- set serveroutput on;
    DBMS_OUTPUT.PUT_LINE(r_c1.myid||','||r_c1.myname||','||r_c1.myjob);
    exit when c1%notfound;
  end loop;
  close c1;
end;

SQL> exec test_cur;
 
begin test_cur; end;
 
ORA-06504: PL/SQL: 结果集变量或查询的返回类型不匹配
ORA-06512: 在 "SCOTT.TEST_CUR", line 20
ORA-06512: 在 line 2
 
SQL> exec test_cur
 
begin test_cur; end;
 
ORA-06504: PL/SQL: 结果集变量或查询的返回类型不匹配
ORA-06512: 在 "SCOTT.TEST_CUR", line 20
ORA-06512: 在 line 2
 
SQL> exec test_cur
 
7369,SMITH,CLERK
7499,ALLEN,SALESMAN
7521,WARD,SALESMAN
7566,JONES,MANAGER
7654,MARTIN,SALESMAN
7698,BLAKE,MANAGER
7782,CLARK,MANAGER
7788,SCOTT,ANALYST
7839,KING,PRESIDENT
7844,TURNER,SALESMAN
7876,ADAMS,CLERK
7900,JAMES,CLERK
7902,FORD,ANALYST
7934,MILLER,CLERK
7934,MILLER,CLERK
 
PL/SQL procedure successfully completed
 
SQL>

CREATE OR REPLACE PROCEDURE test_cur is
  type mycursor_type is ref cursor;
  c1 mycursor_type;
  ----使用
  --打开动态游标，再往下就都一样了
  type record_type is record(
        myid  emp.empno%type,
        myname  emp.ename%type,
        myjob  emp.job%type,
        mymgr  emp.mgr%type,
        myhire emp.hiredate%type,--
        mysal  emp.sal%type,
        myconn emp.comm%type,--
        mydeptid  emp.deptno%type
  );
  r_c1 record_type;
  --r_c1 emp%rowtype;
begin
  open c1 for
    select * from emp;--用星号* record 必须写全
  loop
    fetch c1
      into r_c1;
    -- set serveroutput on;
    DBMS_OUTPUT.PUT_LINE(r_c1.myid||' '||r_c1.myname||' '||r_c1.myjob);
    exit when c1%notfound;
  end loop;
  close c1;
end;

SQL> exec test_cur
 
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK
7934 MILLER CLERK
 
PL/SQL procedure successfully completed
 
SQL>