oracle中数据类型rowtype,Table,Object,Record

---------------------------------------阶段1----------------------------------------------------------------------------------------------

SQL> declare v_dept_row dept%rowtype;
2 begin
3 v_dept_row.deptno:=50;
4 v_dept_row.dname:='repair';
5 v_dept_row.loc:='BeiJing';
6 insert into dept values v_dept_row;
7 end;
8 /

PL/SQL procedure successfully completed

SQL> select * from dept;

DEPTNO DNAME LOC
------ -------------- -------------
50 repair BeiJing
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> declare v_dept_row dept%rowtype;
2 begin
3 v_dept_row.deptno:=&deptno;
4 v_dept_row.dname:='procduct';
5 v_dept_row.loc:='Sydeny';
6 update dept set row=v_dept_row where deptno=v_dept_row.deptno;
7 end;
8 /

PL/SQL procedure successfully completed

SQL> select * from dept;

DEPTNO DNAME LOC
------ -------------- -------------
50 repair BeiJing
10 ACCOUNTING NEW YORK
20 procduct Sydeny
30 SALES CHICAGO
40 OPERATIONS BOSTON

---------------------------------------练习1----------------------------------------------------------------------------------------------
SQL> create or replace type phone_type is Table of varchar2(50);
2 /

Type created

SQL> create table tb_info(
2 id number(6) primary key,
3 vid varchar2(20) not null,
4 vname varchar2(20) not null,
5 vphone phone_type
6 )nested table vphone Store as phone_table;

Table created

SQL> insert into tb_info values(
2 1,'scce001','dog',
3 phone_type('110','119','120','911')
4 );
SQL> declare v_phone_type phone_type;
2 begin
3 select vphone into v_phone_type from tb_info where id=1;
4 for i in 1..v_phone_type.Count loop
5
5 Dbms_Output.put_line(v_phone_type(i));
6 end loop;
7 end;
8 /

110
119
120
911

---------------------------------------练习2----------------------------------------------------------------------------------------------
SQL> create or replace type obj_goods as Object
2 (
3 price number(5),
4 quantity varchar2(14),
5 name varchar2(50),
6 --定义方法
7 member Function getMustPay Return number,--实际付款
8 member Function getChange Return number--找零
9 );
10 /

Type created

SQL> create or replace type body obj_goods As
2 member Function getMustPay return number
3 is
4 begin
5 return Self.price *Self.quantity;
6 end;
7 member Function getChange return number
8 is
9 begin
10 return Self.price;
11 end;
12 end;
13 /

Type body created

SQL> declare v_obj_goods obj_goods;
2 begin
3 v_obj_goods:=obj_goods(40,'5','香烟');
4 Dbms_Output.put_line('商品数量：'||v_obj_goods.quantity);
5 Dbms_Output.put_line('商品名称：'||v_obj_goods.name);
6 Dbms_Output.put_line('商品总价：'||v_obj_goods.getMustPay);
7 Dbms_Output.put_line('商品单价：'||v_obj_goods.price);
8 end;
9 /

商品数量：5
商品名称：香烟
商品总价：200
商品单价：40

PL/SQL procedure successfully completed

---------------------------------------阶段2----------------------------------------------------------------------------------------------
SQL> create table test(
2 id number(20),
3 value varchar2(50)
4 );

Table created

declare
type test_record is Record
(
id number(5),
value varchar(10)
);
Type test is Table of test_record
index by Binary_integer;
v_test test;
v_starTime number(10);
v_endTime number(10);
begin
for i in 1..5000 loop
v_test(i).id:=i;
v_test(i).value:=to_char(floor(dbms_random.value(10,101)));
end loop;
v_starTime:=dbms_utility.get_time;
for i in v_test.first..v_test.last loop
insert into test values(v_test(i).id,v_test(i).value);
end loop;
v_endTime:=dbms_utility.get_time;
Dbms_Output.put_line('所用时间：'||(v_endTime-v_starTime)/100);
end;
/

declare
type test_record is Record
(
id number(5),
value varchar(10)
);
Type test1 is Table of test_record
index by Binary_integer;
v_test1 test1;
v_starTime number(10);
v_endTime number(10);
begin
for i in 1..5000 loop
v_test1(i).id:=i;
v_test1(i).value:=to_char(floor(dbms_random.value(10,101)));
end loop;
v_starTime:=dbms_utility.get_time;
forall i in v_test1.first..v_test1.last
insert into test values(v_test1(i).id,v_test1(i).value);
v_endTime:=dbms_utility.get_time;
Dbms_Output.put_line('所用时间：'||(v_endTime-v_starTime)/100);
end;
/
---------------------------------------阶段2练习1----------------------------------------------------------------------------------------------
declare
Type test1 is Table of dept%rowtype;
v_test1 test1;
begin
select * bulk collect into v_test1 from dept;
for i in v_test1.first..v_test1.last loop
Dbms_Output.put_line('部门名称：'||v_test1(i).dname);
end loop;
end;
/