PL/SQL: How to create type from cursor

Question

Can I define a type from a cursor?

Currently I have to write the following in order to create a record variable from some table fields:

declare

    cursor cur
    is
        select 
            f_1,
            f_2,
            f_3,
            f_4
        from
            mytable
        where 
            1=0;

    myvar cur%rowtype;              -- use the cursor to declare myvar

begin
    null;
end;

I would like to write something like this:

declare

    cursor cur
    is
        select 
            f_1,
            f_2,
            f_3,
            f_4
        from
            mytable
        where 
            1=0;

    type mytype is cur%rowtype;     -- declare "mytype" from cursor
    myvar mytype;                   -- use "mytype" to declare "myvar"

begin
    null;
end;

This doesn't look useful in this trivial example, but can be useful in real problems.

An alternative is to manually create the record type:

declare

    type mytype is record           -- declare "mytype"
    (
        f_1    mytable.f_1%type,
        f_2    mytable.f_2%type,
        f_3    mytable.f_3%type,
        f_4    mytable.f_4%type
    );
    myvar mytype;                   -- use "mytype" to declare "myvar"

begin
    null;
end;

but it is more dirty to me (I have to repeat every field name twice, and the table name many times).

Answer 1

Can I define a type from a cursor?

Yes, you can define your own type that is based on cursor_name%rowtype record type(basically it will be a synonym in this situation), using subtype keyword, not the type one.

Here is an example:

set serveroutput on;
declare
  cursor c1 is
    select 1 as col1
         , 2 as col2
         , 3 as col3
     from dual;

  subtype mytype is c1%rowtype;
  l_myvar mytype;
begin
  open c1;
  fetch c1 into l_myvar;
  dbms_output.put(to_char(l_myvar.col1) || ' : ');
  dbms_output.put(to_char(l_myvar.col2) || ' : ');
  dbms_output.put_line(to_char(l_myvar.col3));
  close c1;
end;

Result:

anonymous block completed

1 : 2 : 3