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;
/