【oracle】复合数据类型

【oracle】复合数据类型

1123-02 更新 复合数据类型
复合数据类型举例

1.record
set serveroutput on;
/* 复合数据类型——记录:for循环中 给 记录类型变量 赋值*/
declare
cursor v_cursor is
select t.serial_no,t.type_code,t.brand_code,t.brand_name from t_sales t;
--定义 记录类型（显示定义）
type t_record is record(
serial_no t_sales.serial_no%type,
type_code t_sales.type_code%type,
brand_code t_sales.brand_code%type,
brand_name t_sales.brand_name%type
);
--定义 记录变量
v_record t_record;
begin
for i_row in v_cur loop
--赋值方式1
--v_record := i_row;

--赋值方式2
v_record.serial_no := i_row.serial_no;
v_record.type_code := i_row.type_code;
v_record.brand_code := i_row.brand_code;
v_record.brand_name := i_row.brand_name;
dbms_output.put_line(v_record.serial_no||','||v_recod.type_code||','||v_record.brand_code||','||v_recoed.brand_name);
end loop;
exception
when others then
dbms_output.put_line(sqlcode||','||substr(sqlerrm,1,200));
end;
/

2.集合
2.1 index_by表
set serveroutput on;
/* 复合数据类型——index_by :定义（标量），赋值*/
declare
type _t_index_by is table of varchar2(30) index by binary_integer;
v_ib t_index_by;
begin
v_ib(-1) := 'hello';
v_ib(0) := 'world';
v_ib(2) := ' Kitty'; --index_by 是稀疏的（下标可以不连续）
dbms_output.put_line(v_ib(-1)||','||v_ib(0)||','||v_ib(2));
exception
dbms_output.put_line(sqlcode||','||substr(sqlerrm,1,200));
end;
/

set serveroutput on;
/* 复合数据类型——index_by :定义（记录），赋值*/
declare
type t_record is record(
emp_id tab_emp.emp_id%type,
emp_depart tab_emp.emp_depart%type,
emp_name tab_emp.emp_name%type,
emp_salary tab_emp.emp_salary%type
);

type t_index_by is table of t_record index by binary_integer;
v_ib t_index_by;
v_ib2 t_index_by;

cursor v_cursor is
select emp_id,emp_depart,emp_name,emp_salart from tab_emp where emp_id <= 4;
cursor v_cursor2 is
select emp_id,emp_depart,emp_name,emp_salart from tab_emp where emp_id <= 4;
begin
--二维数组 元素 的引用方式 collection_element.record_column
----赋值方式1. select into
select emp_id,emp_depart,emp_name,emp_salary
into v_ib(-1).emp_id, v_ib(-1).emp_depart, v_ib(-1).emp_name, v_ib(-1).emp_salary
from tab_emp where emp_id = 1;
dbms_output.put_line(v_ib(-1).emp_id||','|| v_ib(-1).emp_depart||','|| v_ib(-1).emp_name||','|| v_ib(-1).emp_salary);
----赋值方式2. :=
v_ib(-1).emp_id := v_ib(-1).emp_id + 10;
dbms_output.put_line(v_ib(-1).emp_id||','|| v_ib(-1).emp_depart||','|| v_ib(-1).emp_name||','|| v_ib(-1).emp_salary);
----赋值方式3. fetch into 逐行提取游标
open v_cursor;
loop
fetch v_cursor into v_ib(v_ib.last()+1);
exit when v_cursor%notfound;
dbms_output.put_line(v_ib(v_ib.last()).emp_id||','|| v_ib(v_ib.last()).emp_depart||','|| v_ib(v_ib.last()).emp_name||','|| v_ib(v_ib.last()).emp_salary);
end loop;
close v_cursor;
----赋值方式4. fetch bulk collect into 一次性提取游标
open v_cursor2;
fetch v_cursor2 bulk collect into v_ib2;
for i in 1..v_ib2.count() loop
dbms_output.put_line(v_ib(i).emp_id||','|| v_ib(i).emp_depart||','|| v_ib(i).emp_name||','|| v_ib(i).emp_salary);
end loop;
close v_cursor2;
exception
dbms_output.put_line(sqlcode||','||substr(sqlerrm,1,200));
end;
/

set serveroutput on;
/* 复合数据类型——index_by :定义（标量），函数*/
declare
type t_index_by is table varchar2(30) index_by binary_integer;
v_ib t_index_by;
begin
v_ib(-1) := 'hello';
v_ib(0) := 'world';
v_ib(2) := ' Kitty';
v_ib(3) := ' 3';
v_ib(4) := 4;
v_ib(1) := ' hi';

--函数
dbms_output.put_line(v_ib.count()); --元素个数
dbms_output.put_line(v_ib.limit()); --元素限制:null
dbms_output.put_line(v_ib.first()); --起始元素的下标 -1
dbms_output.put_line(v_ib.last()); --最后一个元素的下标 2
dbms_output.put_line(v_ib.next(v_ib.last())); --返回下一个元素的下标:null
dbms_output.put_line(v_ib.prior(v_ib.first())); --返回上一个元素的下标:null

/*index_by非法的方法
exist()
extend()
extend(n) --扩展元素到末尾（n:扩展元素个数）
extend(n,e_idx) --复制元素到末尾（e_idx:元素下标）
*/

dbms_output.put_line('元素个数:'||v_ib.count());
v_ib.delete(1);
dbms_output.put_line('v_ib.delete(1):'||v_ib.count());
--dbms_output.put_line('v_ib(1)='||v_ib(1)); --100ORA-01403: no data found
v_ib.delete(2,3);
dbms_output.put_line('v_ib.delete(2,3):'||v_ib.count());
v_ib.delete();
dbms_output.put_line('v_ib.delete():'||v_ib.count());
exception
dbms_output.put_line(sqlcode||','||substr(sqlerrm,1,200));
end;
/

2.2 nested_table
set serveroutput on;
/* 复合数据类型——nested_table:定义（标量），初始化，赋值*/
declare
type t_nested_table is table of varchar2(30);
v_ntab t_nested_table;

cursor v_cursor is
select emp_name from tab_emp where emp_id=1;
begin
--初始化:构造函数 ，格式:类型名([参数列表])
v_ntab := t_nested_table(); --初始化为 null，使用前需要先extend
v_ntab := t_nested_table('001','002','003'); --初始化为 3个元素
--赋值
----赋值方式1. select into
select 111,222 into v_ntab(1),v_ntab(2) from dual;
dbms_output.put_line(v_ntab(1)||','||v_ntab(2));
----赋值方式2. :=
v_ntab(1) := to_number(v_ntab(1))+1;
v_ntab(2) := v_ntab(2)+10; --oracle的隐式转换，运算符+ 当且仅当 左右操作数均为字符串时 才做 字符串加
dbms_output.put_line(v_ntab(1)||','||v_ntab(2));
----赋值方式3. fetch into
open v_cursor;
fetch v_cursor into v_ntab(3);
close v_cursor;
dbms_output.put_line(v_ntab(1)||','||v_ntab(2)||','||v_ntab(3));
exception
dbms_output.put_line(sqlcode||','||substr(sqlerrm,1,200));
end;
/

set serveroutput on;
/* 复合数据类型——nested_table :定义（记录），初始化，赋值*/
declare
type t_record is record(
emp_id tab_emp.emp_id%type,
emp_depart tab_emp.emp_depart%type,
emp_name tab_emp.emp_name%type,
emp_salary tab_emp.emp_salary%type
);
v_record01 t_record;
v_record02 t_record;

type t_nested_table is table of t_record;
v_ntab t_nested_table;

cursor v_cursor is
select emp_name from tab_emp where emp_id = 1;

cursor v_cursor2 is
select emp_id,emp_depart,emp_name,emp_salary from tab_emp where emp_id <= 3;
begin
select 1,'10','chris',5600 into v_record01 from dual;
select 2,'20','May',4700 into v_record02 from dual;
--初始化
v_ntab := t_nested_table();
v_ntab := t_nested_table(v_record01,v_record02,v_record02);
dbms_output.put_line(v_ntab(1).emp_id||','|| v_ntab(1).emp_depart||','||v_ntab(1).emp_name||','|| v_ntab(1).emp_salary);
dbms_output.put_line(v_ntab(2).emp_id||','|| v_ntab(2).emp_depart||','||v_ntab(2).emp_name||','|| v_ntab(2).emp_salary);
dbms_output.put_line(v_ntab(3).emp_id||','|| v_ntab(3).emp_depart||','||v_ntab(3).emp_name||','|| v_ntab(4).emp_salary);
--赋值
1. select into
select 3,'10','Stephen',4900 into v_ntab(3) from dual;
select 3,'10','Stephen',4900 into v_ntab(3).emp_id,v_ntab(3).emp_depart,v_ntab(3).emp_name,v_ntab(3).emp_salary from dual;

dbms_output.put_line(v_ntab(3).emp_id||','|| v_ntab(3).emp_depart||','||v_ntab(3).emp_name||','|| v_ntab(3).emp_salary);
2. :=
v_ntab(1).emp_id := v_ntab(1).emp_id +10;
dbms_output.put_line(v_ntab(1).emp);
3. fetch into
open v_cursor;
fetch v_cursor into v_ntab(3).emp_name;
close v_cursor;
dbms_output.put_line(v_ntab(3).emp_id||','|| v_ntab(3).emp_depart||','||v_ntab(3).emp_name||','|| v_ntab(3).emp_salary);
5. fetch bulk collect into
open v_cursor2;
fetch v_cursor2 bulk collect into v_ntab; --v_ntab初始化了3个元素，则有3个元素可以访问
close v_cursor2;
--nested_table默认下标连续
for i in 1..v_ntab.count() loop
dbms_output.put_line(v_ntab(i).emp_id||','|| v_ntab(i).emp_depart||','||v_ntab(i).emp_name||','|| v_ntab(i).emp_salary);
end loop;
exception
dbms_output.put_line(sqlcode||','||substr(sqlerrm,1,200));
end;
/

set serveroutput on;
/* 复合数据类型——nested_table:定义（标量），函数*/
declare
type t_nested_table is table of varchar2(30);
v_ntab t_nested_table;
begin
v_ntab := t_nested_table();
v_ntab := t_nested_table('001','002','003');
dbms_output.put_line(v_ntab(1)||','||v_ntab(2)||','||v_ntab(3));

--函数
dbms_output.put_line(v_ib.count()); --元素个数
dbms_output.put_line(v_ib.limit()); --元素限制:null
dbms_output.put_line(v_ib.first()); --起始元素的下标:默认为1
dbms_output.put_line(v_ib.last()); --最后一个元素的下标 2
dbms_output.put_line(v_ib.next(v_ib.last())); --返回下一个元素的下标:null
dbms_output.put_line(v_ib.prior(v_ib.first())); --返回上一个元素的下标:null

/*index_by非法的方法
exist()
*/

if v_ntab.count() <4 then
v_ntab.extend(2); --在末尾 扩展2个元素（元素值为null）
v_ntab.extend(3,1); --在末尾 扩展3个元素（元素值为下标为3的元素值的副本）
end if;
dbms_output.put_line(v_ntab.last());
dbms_output.put_line('取后3个元素：'||v_ntab(v_ntab.last())||','|| v_ntab(v_ntab.last()-1)||','|| v_ntab(v_ntab.last()-2) );

dbms_output.put_line('元素个数:'||v_ntab.count());
v_ntab.delete(1);
dbms_output.put_line('v_ntab.delete(1):'||v_ntab.count());
--dbms_output.put_line('v_ntab(1)='||v_ntab(1)); --100ORA-01403: no data found
v_ntab.delete(2,3);
dbms_output.put_line('v_ntab.delete(2,3):'||v_ntab.count());
v_ntab.delete();
dbms_output.put_line('v_ntab.delete():'||v_ntab.count());
begin
exception
dbms_output.put_line(sqlcode||','||substr(sqlerrm,1,200));
end;
/

 

 

2.3 vararry|varying array
set serveroutput on;
/* 复合数据类型——varray|varying array :定义（标量），初始化，赋值*/
declare
--type t_varray is varray(3) of varchar2(30);
type t_varray is varying array(3) of vatchar2(30);
v_varray t_varray;
cursor v_cursor is
select emp_name from tab_emp where emp_id = 1;
begin
--初始化
v_varray := t_varry();
v_varray := t_varray('001','002','003');
dbms_output.put_line(v_varray(1)||','||v_varray(2)||','||v_varray(3));
--赋值
----1. select into
select 111,222 into v_varray(1),v_varray(2) from dual;
dbms_output.put_line(v_varray(1)||','||v_varray(2));
----2. :=
v_ntab(1) := v_ntab(1) + 10;
dbms_output.put_line(v_varray(1));
----3. fetch into
open v_cursor;
fetch v_cursor into v_varray(3);
close v_cursor;
dbms_output.put_line(v_varray(1)||','|| v_varray(2)||','|| v_varray(3));
exception
dbms_output.put_line(sqlcode||','||substr(sqlerrm,1,200));
end;
/

set serveroutput on;
/* 复合数据类型——varray|varying array :定义（记录），初始化，赋值*/
declare
type t_record is record(
emp_id tab_emp.emp_id%type,
emp_depart tab_emp.emp_depart%type,
emp_name tab_emp.emp_name%type,
emp_salary tab_emp.emp_salary%type
);
v_record01 t_record;

--type t_varray is varray(3) of ;
type t_varray is varying array(3) of t_record;
v_array t_varray;

cursor v_cursor is
select emp_name from tab_emp where emp_id = 1;

cursor v_cursor2 is
select emp_id,emp_depart,emp_name,emp_salary from tab_emp where emp_id <= 3;
begin
select 1,'10','chris',5600 into v_record01 from dual;
v_record01.emp_id := 1;
v_record01.emp_depart := '10';
v_record01.emp_name := 'chris';
v_record01.emp_salary := 5600;
--初始化
v_varray := t_varray();
v_varray := t_varray(v_record01);
dbms_output.put_line(v_varray(1).emp_id||','|| v_varray(1).emp_depart||','||v_varray(1).emp_name||','|| v_varray(1).emp_salary);
--赋值
----1. select into
select 101,'10_10','chris',5600 into v_varray from dual;
dbms_output.put_line(v_varray(1).emp_id||','|| v_varray(1).emp_depart||','||v_varray(1).emp_name||','|| v_varray(1).emp_salary);
----2. :=
v_varray(1).emp_id := v_varray(1).emp_id +10;
dbms_output.put_line(v_varray(1).emp_id||','|| v_varray(1).emp_depart||','||v_varray(1).emp_name||','|| v_varray(1).emp_salary);
----3. fetch into
open v_cursor;
fetch v_cursor into v_varray(1);
close v_cursor;
dbms_output.put_line(v_varray(1).emp_id||','|| v_varray(1).emp_depart||','||v_varray(1).emp_name||','|| v_varray(1).emp_salary);
----4. fetch bulk collect into
--dbms_output.put_line(v_varray(2).emp_id); --未初始化，不能访问
v_varray := t_varray(v_record01,v_record01,v_record01); --max_size=3,且初始化了3个元素，则可以访问3个元素
open v_cursor2;
fetch v_cursor2 bulk collect into v_varray;
close v_cursor2;
for i in 1..v_varray.count() loop
dbms_output.put_line(v_varray(i).emp_id||','|| v_varray(i).emp_depart||','||v_varray(i).emp_name||','|| v_varray(i).emp_salary);
end loop;
exception
dbms_output.put_line(sqlcode||','||substr(sqlerrm,1,200));
end;
/

set serveroutput on;
/* 复合数据类型——varray|varying array :定义（标量），函数*/
declare
--type t_varray is varray(3) of varchar2(30);
type t_varray is varying array(3) of vatchar2(30);
v_varray t_varray;
begin
--初始化
v_varray := t_varray();
v_varray := t_varray('001','002');
--v_varray := t_varray('001','002','003','004'); --报错 ORA-06532:Subscript outside of limit. 因为max_size=3
dbms_output.put_line(v_varray(1)||','||v_varray(2));

--函数
dbms_output.put_line(v_varray.count()); --元素个数
dbms_output.put_line(v_varray.limit()); --元素限制:max_size
dbms_output.put_line(v_varray.first()); --起始元素的下标:默认为1
dbms_output.put_line(v_varray.last()); --最后一个元素的下标 2
dbms_output.put_line(v_varray.next(v_varray.last())); --返回下一个元素的下标:null
dbms_output.put_line(v_varray.prior(v_varray.first())); --返回上一个元素的下标:null

/*index_by非法的方法
exist()
delete(idx)
delect(idx1,idx2)
*/

v_varray.extend(1); --max_size限制内，扩展并初始化1个元素（元素值默认为null）
-- v_varray.extend(); --等价于 v_varray.extend(1);
dbms_output.put_line(v_varray.count()||'：'||v_varray(1)||','||v_varray(2)||','||v_varray(3));
v_varray(3) := '003'; --说明extend后 元素已经初始化了

v_varray.extend(1,2); -- max_size限制内，扩展并初始化1个元素（元素值默认为下标2元素的值的副本）
dbms_output.put_line(v_varray.count()||'：'||v_varray(1)||','||v_varray(2)||','||v_varray(3) ||','||v_varray(4) );

dbms_output.put_line('元素个数:'||v_varray.count());
v_varray.delete();
dbms_output.put_line(' v_varray.delete()后， 元素个数:'||v_varray.count());
exception
dbms_output.put_line(sqlcode||','||substr(sqlerrm,1,200));
end;
/

posted on 2018-04-05 11:59  绿Z 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/greenZ/p/8721830.html