pl/sql %type和%rowtype區別

一：%Type功能

SQL> set serveroutput on
SQL> select * from temp_table;

   NUM_COL CHAR_COL
---------- --------------------------------------------------
         1 insert one
         2 insert two
         3 insert three

SQL> declare
  2  v_numcol temp_table.num_col%type;
  3  v_charcol temp_table.char_col%type;
  4  cursor c_alltable is
  5  select * from temp_table;
  6
  7  begin
  8  open c_alltable;
  9  loop
 10  fetch c_alltable into v_numcol,v_charcol;
 11  exit when c_alltable%notfound;
 12  dbms_output.put_line(v_numcol);
 13  dbms_output.put_line(v_charcol);
 14  end loop;
 15  end;
 16  /
1
insert one
2
insert two
3
insert three

PL/SQL 過程已成功完成。

SQL>

 

二：%RowType功能

SQL> declare
  2
  3  cursor c_alltable is
  4  select * from temp_table;
  5
  6  v_tablerecord c_alltable%rowtype;
  7
  8  begin
  9  open c_alltable;
 10  loop
 11  fetch c_alltable into v_tablerecord;
 12  exit when c_alltable%notfound;
 13  dbms_output.put_line(v_tablerecord.num_col);
 14  dbms_output.put_line(v_tablerecord.char_col);
 15  end loop;
 16  end;
 17  /
1
insert one
2
insert two
3
insert three

PL/SQL 過程已成功完成。

SQL>