plsql数据类型 record

记录类型（Record）

一个记录是一组相关的数据项，每个数据项都有自己的数据类型和名称，可以认为一个record可以储存一个表的一行的一列或者多列，他的fields关联表的列。可以定义record为一个数据库表的rowtype。可以在plsql块，函数，过程或者包内定义record类型，同时可以在定义的时候使用not null约束或者给定一个默认值。

例如：

SQL> set serveroutput on;

SQL>

SQL> declare

2 type tp_record_1 is record(

3 id number not null := 1,

4 name varchar2(20),

5 birthday date);

6 v_record_1 tp_record_1;

7 begin

8 v_record_1.id := 1;

9 v_record_1.name := 'yellow';

10 v_record_1.birthday := date '1983-06-18';

11 dbms_output.put_line(v_record_1.name);

12 end;

13 /



yellow



PL/SQL procedure successfully completed



也可以使用type类型：

SQL> declare

2 type tp_record_1 is record(

3 empno scott.emp.empno%type,

4 sal scott.emp.sal%type);

5 v_record_1 tp_record_1;

6 begin

7 select empno, sal into v_record_1 from scott.emp a where a.empno = 7369;

8 dbms_output.put_line(v_record_1.sal);

9 end;

10 /



800



PL/SQL procedure successfully completed



也可以结合index by table构建组合类型：

SQL> declare

2 type tp_record_1 is record(

3 empno scott.emp.empno%type,

4 sal scott.emp.sal%type);

5 type tp_indexby_table_1 is table of tp_record_1 index by pls_integer;

6 v_indexby_table_1 tp_indexby_table_1;

7 begin

8 select empno, sal bulk collect

9 into v_indexby_table_1

10 from scott.emp a

11 where rownum < 5;

12 for i in v_indexby_table_1.first .. v_indexby_table_1.last loop

13 dbms_output.put_line(v_indexby_table_1(i).sal);

14 end loop;

15 end;

16 /



123

800

1600

1250



PL/SQL procedure successfully completed



和集合类型一样record也有一系列支持的方法来进行对record元素的操作：

SQL> declare

2 type tp_record_1 is record(

3 empno scott.emp.empno%type,

4 sal scott.emp.sal%type);

5 type tp_indexby_table_1 is table of tp_record_1 index by pls_integer;

6 v_indexby_table_1 tp_indexby_table_1;

7 begin

8 select empno, sal bulk collect

9 into v_indexby_table_1

10 from scott.emp a

11 where rownum < 5;

12 v_indexby_table_1.delete(1);

13 dbms_output.put_line('v_indexby_table_1.count:'||v_indexby_table_1.count);

14 dbms_output.put_line('v_indexby_table_1.first:'||v_indexby_table_1.first);

15 for i in v_indexby_table_1.first .. v_indexby_table_1.last loop

16 dbms_output.put_line(v_indexby_table_1(i).sal);

17 end loop;

18 end;

19 /



v_indexby_table_1.count:3

v_indexby_table_1.first:2

800

1600

1250



PL/SQL procedure successfully completed



Table和record可以用作in或者out参数，下面是一个用作in参数的例子：



create table t_test_recod(id number ,name varchar2(10));

/

insert into t_test_recod select 1,'name1' from dual;

/

create or replace package pac_test is

type type_record is record(

id t_test_recod.id%type,

name t_test_recod.name%type);

type type_table is table of type_record index by binary_integer;



procedure pro_test_record(rec in type_record);

procedure pro_test_record_test;



procedure pro_test_table(tab in type_table);

procedure pro_test_table_test;



end pac_test;

/

create or replace package body pac_test is

procedure pro_test_record(rec in type_record) is

records type_record;

begin

select id, name into records from t_test_recod where id = rec.id;

dbms_output.put_line(records.id || records.name);

end pro_test_record;

procedure pro_test_record_test is

rec type_record;

begin

rec.id := 1;

rec.name := 'name1';

pro_test_record(rec);

end pro_test_record_test;



procedure pro_test_table(tab in type_table) is

v_tab type_table;

begin

v_tab := tab;

select id, name into v_tab(1) from t_test_recod where id = v_tab(1).id;

dbms_output.put_line(v_tab(1).id || v_tab(1).name);

end pro_test_table;



procedure pro_test_table_test is

v_tab type_table;

begin

v_tab(1).id := 1;

v_tab(1).name := 'name1';

pro_test_table(v_tab);

end pro_test_table_test;


end pac_test;

/