当前位置:   article > 正文

Oracle数据库- 5 集合_oracle 数据库有集合吗

oracle 数据库有集合吗

1:PL/SQL表

PL/SQL表类似于单列数据库表。PL/SQL表的数据行按照预定义顺序存储的,但是当使用变量来检索数据时,每行数据都会分配一个连续的下标,从1开始。
如下图:
在这里插入图片描述

PL/SQL表的两种类型:

  1. 联合数组(或者叫索引表)
  2. 嵌套表
1- 联合数组:

创建语法:

TYPE type_name IS TABLE OF element_type [not null] --方括号是可选的。
 INDEX by element_type;
 
v_table_name TYPE_NAME;
  • 1
  • 2
  • 3
  • 4

说明:
TYPE :是声明表结构
type_name :是第二句中所用的类型名,用于声明一个实际的表。
element_type:是 PL/SQL的数据类型。如 number ,varchar2 ,char 等。还可以这样写:Person.Name%TYPE (Person表 Name 列的数据类型)
示例

DECLARE
  TYPE name_type IS TABLE OF Person.Name%TYPE
       INDEX by BINARY_INTEGER; --BINARY_INTEGER 表示有符号整数
v_Name_tab  name_type;  
  • 1
  • 2
  • 3
  • 4

实例1:

SQL> DECLARE
  2    CURSOR alm_c is select alarm_code from alm_action where rownum<=10;
  3    TYPE code_type IS TABLE OF alm_action.alarm_code%TYPE
  4         INDEX by BINARY_INTEGER;
  5    v_code_tab  code_type;
  6    v_count integer :=0;
  7  
  8   begin
  9      for i in alm_c loop
 10      v_count:=v_count+1;
 11      v_code_tab(v_count):=i.alarm_code;
 12      dbms_output.put_line('CODE ['||v_count||'] :'||v_code_tab(v_count));
 13      end loop;
 14     end;
 15  /
 
CODE [1] :999999
CODE [2] :999999
CODE [3] :999999
CODE [4] :999999
CODE [5] :888888
CODE [6] :777777
CODE [7] :888888
CODE [8] :999999
CODE [9] :999999
CODE [10] :999999
 
PL/SQL procedure successfully completed
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28

示例2:在存储过程中使用:

create or replace procedure TABLE_TEST  --创建存储过程
 is
  lv_index number:=0;                                               --声明变量
  CURSOR cur_name is select ename from EMP ;                             --声明游标
	TYPE table_Name is table of emp.ename % TYPE INDEX by BINARY_INTEGER;   --声明集合类型
	var_names table_Name;                                                 --声明集合类型变量
begin
	
  for i in cur_name loop
		lv_index :=lv_index+1;
		var_names(lv_index):=i.ename;
		dbms_output.put_line('names ['||lv_index||'] :'||var_names(lv_index));
		end loop;
		
end;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

测试:
在这里插入图片描述

注意:引用不存在的下标会抛出:NO_DATA_FOUND异常。

2- 嵌套表:

创建语法:

TYPE type_name IS TABLE OF element_type [NO NULL];
v_table_name TYPE_NAME;
  • 1
  • 2

注意:这个声明非常类似于联合数组声明,只是没有 INDEX by BINARY_INTEGER 语句
element_type :数据类型。和联合数组一样,参见上面。
实例2:

SQL> DECLARE
  2    CURSOR alm_c is select alarm_code from alm_action where rownum<=10;
  3    TYPE code_type IS TABLE OF alm_action.alarm_code%TYPE; --定义嵌套表类型
  4    v_code_tab  code_type :=code_type(); --声明嵌套表类型变量,并初始化
  5    v_count integer :=0;
  6  
  7   begin
  8      for i in alm_c loop
  9      v_count:=v_count+1;
 10      v_code_tab.extend; --允许增加集合的大小
 11      v_code_tab(v_count):=i.alarm_code;
 12      dbms_output.put_line('CODE ['||v_count||'] :'||v_code_tab(v_count));
 13      end loop;
 14     end;
 15  /
 
CODE [1] :999999
CODE [2] :999999
CODE [3] :999999
CODE [4] :999999
CODE [5] :888888
CODE [6] :777777
CODE [7] :888888
CODE [8] :999999
CODE [9] :999999
CODE [10] :999999
 
PL/SQL procedure successfully completed
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28

extend :增加集合大小。
嵌套表的默认初始化:

 v_code_tab  code_type('xxxxx','xxxxx','xxxxx');
  • 1

多元素嵌套表:

--创建一个不可实例化的对象类型
create or replace type obj_table is object ( v_name varchar2(10),v_job  varchar2(9), v_sal  number(7,2)); 
  • 1
  • 2
--定义一个SQL模式级对象类型集合
create or replace type obj_table_list is table of obj_table;  
  • 1
  • 2
declare
  lv_list obj_table_list := obj_table_list(); --声明集合变量
  lv_index  integer :=1;   
begin
   for i in (select * from emp t where t.sal > 1000) loop
	  lv_list.EXTEND;
    lv_list(lv_index)	:=	obj_table(i.empno,i.job,i.sal);
    lv_index :=lv_index+1;	 
   end loop;
   
   for i in 1..lv_list.count loop
		 dbms_output.put_line('姓名:'||lv_list(i).v_name||' 职务:'||lv_list(i).v_job||' 薪资:'||lv_list(i).v_sal);
	 end loop;
   
	end;  
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

输出:

SQL> declare
  2    lv_list obj_table_list := obj_table_list(); --声明集合变量
  3    lv_index  integer :=1;
  4  begin
  5     for i in (select * from emp t where t.sal > 1000) loop
  6  	  lv_list.EXTEND;
  7      lv_list(lv_index)	:=	obj_table(i.empno,i.job,i.sal);
  8      lv_index :=lv_index+1;
  9     end loop;
 10  
 11     for i in 1..lv_list.count loop
 12  		 dbms_output.put_line('姓名:'||lv_list(i).v_name||' 职务:'||lv_list(i).v_job||' 薪资:'||lv_list(i).v_sal);
 13  	 end loop;
 14  
 15  	end;
 16  /
 
姓名:7499 职务:SALESMAN 薪资:1600
姓名:7521 职务:SALESMAN 薪资:1250
姓名:7566 职务:MANAGER 薪资:2975
姓名:7654 职务:SALESMAN 薪资:1250
姓名:7698 职务:MANAGER 薪资:2850
姓名:7782 职务:MANAGER 薪资:2450
姓名:7788 职务:ANALYST 薪资:3000
姓名:7839 职务:PRESIDENT 薪资:5000
姓名:7844 职务:SALESMAN 薪资:1500
姓名:7876 职务:CLERK 薪资:1100
姓名:7902 职务:ANALYST 薪资:3000
姓名:7934 职务:CLERK 薪资:1300
 
PL/SQL procedure successfully completed
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
3- varray(集合):

SQL作用域集合的创建:

create or replace type v_varray as  varray(3) of varchar2(20);
  • 1

上面创建了一个包含三个元素的 varray 集合。
该集合可以在匿名块程序和命名块程序中使用,示例:

SQL> create or replace type str_array as  varray(3) of varchar2(20);
  2  /
 
Type created
 
SQL> 
SQL> declare
  2  	lv_var1  str_array:=str_array('One','Tow','Three');
  3  	lv_var2  str_array:=str_array();
  4  	begin
  5  		for i in 1..lv_var1.count loop
  6  			lv_var2.extend;      --立即分配存储空间       
  7  			lv_var2(i):=lv_var1(i);
  8  			end loop;
  9  			for i in 1..lv_var2.count loop
 10  				dbms_output.put_line('集合2 【'||i||'】:'||'【'||lv_var2(i)||'】');
 11  				end loop;
 12  		end;
 13  /
 
集合2 【1】:【One】
集合2 【2】:【Tow】
集合2 【3】:【Three】
 
PL/SQL procedure successfully completed
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25

PL/SQL作用域标量变量变长数组:

SQL> declare
  2  type plsql_varray is varray(3) of varchar2(20);
  3  v_arr1 plsql_varray :=plsql_varray('one','tow','three');
  4  v_arr2 plsql_varray :=plsql_varray();
  5  begin
  6  	v_arr2.extend(v_arr1.count);
  7  	for i in 1..v_arr1.count loop
  8  		v_arr2(i):=v_arr1(i);
  9  		end loop;
 10  	 for i in 1..v_arr2.count loop
 11  			dbms_output.put_line('集合2 ['||i||'] ['||v_arr2(i)||']');
 12  			end loop;
 13  	end;
 14  /
 
集合2 [1] [one]
集合2 [2] [tow]
集合2 [3] [three]
 
PL/SQL procedure successfully completed
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20

5 - 多层集合

从 oracle 9i 开始,PL/SQL 允许创建元素类型为集合类型的集合,这种集合成为多层集合。
结构如下图:
在这里插入图片描述
上述变长数组有3个元素,其中每个元素都是有4个整数组成的变长数组。
创建示例:

declare
 type varray_type1 is varray(4) of integer;
 type varray_type2 is varray(3) of  varray_type1;
  • 1
  • 2
  • 3

示例用法:

SQL> declare
  2   type varray_type1 is varray(4) of integer;
  3   type varray_type2 is varray(3) of  varray_type1;
  4   arr1 varray_type1:=varray_type1(2,4,6,8);
  5   arr2 varray_type2:=varray_type2(arr1);
  6   begin
  7     dbms_output.put_line('动态整数集合');
  8     for i in 1..arr1.count loop
  9     dbms_output.put_line('arr1['||i||'] :'||arr1(i));
 10     end loop;
 11     arr2.extend;
 12     arr2(2):=varray_type1(1,3,5,7);
 13     dbms_output.put_line('动态数组是整数类型集合');
 14     for i in 1..arr2.count loop
 15         for j in 1..arr2(i).count loop
 16           dbms_output.put_line('arr1 ['||i||']['||j||'] :'||arr2(i)(j));
 17         end loop;
 18     end loop;
 19     end;
 20  /
 
动态整数集合
arr1[1] :2
arr1[2] :4
arr1[3] :6
arr1[4] :8
动态数组是整数类型集合
arr1 [1][1] :2
arr1 [1][2] :4
arr1 [1][3] :6
arr1 [1][4] :8
arr1 [2][1] :1
arr1 [2][2] :3
arr1 [2][3] :5
arr1 [2][4] :7
 
PL/SQL procedure successfully completed
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37

用法示例2:

SQL> declare
  2     type table_type1 is table of integer index by binary_integer;
  3     type table_type2 is table of table_type1 index by binary_integer;
  4     tab1 table_type1;
  5     tab2 table_type2;
  6  begin
  7    for i in 1..2 loop
  8      for j in 1..3 loop
  9        if i=1 then
 10          tab1(j):=j;
 11          else
 12            tab1(j):=4-j;
 13          end if;
 14          tab2(i)(j):=tab1(j);
 15          dbms_output.put_line('table2('||i||')('||j||'):'||tab2(i)(j));
 16        end loop;
 17    end loop;
 18    end;
 19  /
 
table2(1)(1):1
table2(1)(2):2
table2(1)(3):3
table2(2)(1):3
table2(2)(2):2
table2(2)(3):1
 
PL/SQL procedure successfully completed
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28

游标和集合示例:

表结构
在这里插入图片描述
存储过程

	create or replace procedure CURSOR_TEST
(
     v_time in varchar2
) as
  lv_Eime   date;
  lv_STIME  date;
  v_fromTime varchar2(32);
  v_endTime  varchar2(32);
  lv_index  number :=0;
  lv_temp   TEST_TABLE.NAME % TYPE;
  TYPE table_Name is table of TEST_TABLE.NAME % TYPE INDEX by BINARY_INTEGER;
  var_names table_Name;
   CURSOR cur_name(v_STime varchar2,v_ETime varchar2) is select t.name from TEST_TABLE t
          where to_date(to_char(t.time,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') >=to_date(v_STime,'YYYY-MM-DD HH24:MI:SS') and to_date(to_char(t.time,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') <=to_date(v_ETime,'YYYY-MM-DD HH24:MI:SS');
begin
       if v_time is not null then
         select to_date(v_time,'YYYY-MM-DD HH24:MI:SS') into  lv_Eime from dual;
         lv_STIME :=lv_Eime-10/24;
         dbms_output.put_line('S='||lv_STIME||' E='||lv_Eime);
         v_fromTime:=to_char(lv_STIME,'YYYY-MM-DD HH24:MI:SS');
         v_endTime:=to_char(lv_Eime,'YYYY-MM-DD HH24:MI:SS');

         open cur_name(v_fromTime,v_endTime);
         loop
					 fetch cur_name into lv_temp;
					 exit  when cur_name % notfound;
					  lv_index:=lv_index+1;
            var_names(lv_index):=lv_temp;
           dbms_output.put_line('names ['||lv_index||'] :'||var_names(lv_index));
					
					end loop;	

         close cur_name;
         else
            dbms_output.put_line('开始时间为空!');
         end if;
				 
				  if var_names.count>0 then
          dbms_output.put_line('--------------------');
          for i in 1..var_names.count loop
            dbms_output.put_line(var_names(i));
            end loop;
            dbms_output.put_line('--------------------');
        else
          dbms_output.put_line('--------------------');
          dbms_output.put_line('集合为空!');
          dbms_output.put_line('--------------------');
          end if;
  end;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49

执行结果:
在这里插入图片描述

%ROWTYPE 标记 记录变量类型

1 一般定义语法:

declare
  emp_row EMP %rowtype;
  • 1
  • 2

说明: EMP 为表名。上述定义了 记录变量 emp_row 与 EMP表记录行建立了联系,即 emp_row 中的每个域的数据类型都与 EMP 表中定义的每个列的数据类型对应。如果 EMP 表的数据类型发生变化,emp_row 也随之发生变化。

2 基于游标定义的记录表:

SQL> declare
  2    cursor emp_cur is select * from emp;
  3    c1 emp_cur %rowtype;
  4  begin
  5    open emp_cur;
  6    loop
  7      fetch emp_cur into c1;
  8      exit when emp_cur %notfound;
  9      dbms_output.put_line('姓名:'||c1.ename||' 编号:'||c1.empno||' 日期:'||c1.hiredate||' 工资:'||c1.sal||' 部门编号:'||c1.deptno);
 10    end loop;
 11	   close emp_cur ;
 12  end;
 13  /
 
姓名:SMITH 编号:7369 日期:1980-12-17 00:00:00 工资:800 部门编号:20
姓名:ALLEN 编号:7499 日期:1981-02-20 00:00:00 工资:1600 部门编号:30
姓名:WARD 编号:7521 日期:1981-02-22 00:00:00 工资:1250 部门编号:30
姓名:JONES 编号:7566 日期:1981-04-02 00:00:00 工资:2975 部门编号:20
姓名:MARTIN 编号:7654 日期:1981-09-28 00:00:00 工资:1250 部门编号:30
姓名:BLAKE 编号:7698 日期:1981-05-01 00:00:00 工资:2850 部门编号:30
姓名:CLARK 编号:7782 日期:1981-06-09 00:00:00 工资:2450 部门编号:10
姓名:SCOTT 编号:7788 日期:1987-04-19 00:00:00 工资:3000 部门编号:20
姓名:KING 编号:7839 日期:1981-11-17 00:00:00 工资:5000 部门编号:10
姓名:TURNER 编号:7844 日期:1981-09-08 00:00:00 工资:1500 部门编号:30
姓名:ADAMS 编号:7876 日期:1987-05-23 00:00:00 工资:1100 部门编号:20
姓名:JAMES 编号:7900 日期:1981-12-03 00:00:00 工资:950 部门编号:30
姓名:FORD 编号:7902 日期:1981-12-03 00:00:00 工资:3000 部门编号:20
姓名:MILLER 编号:7934 日期:1982-01-23 00:00:00 工资:1300 部门编号:10
 
PL/SQL procedure successfully completed
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30

3 使用显示方法定义记录表:

SQL> declare
  2    type emp_row is record
  3    (
  4      v_ename emp.ename %type,
  5      v_ejob emp.job %type,
  6      v_esal emp.sal %type,
  7      v_deptno emp.deptno %type
  8    );  --显示定义
  9    er emp_row;
 10    v_empsal emp.sal %type;
 11    v_index int :=0;
 12    begin
 13  		v_empsal:=1000;
 14      dbms_output.put_line('序号    姓名    职务    薪资    部门编号');
 15      for row_index in (select * from emp t where t.sal > v_empsal) loop
 16  			v_index:=v_index+1;
 17        er.v_ename:=row_index.ename;
 18        er.v_ejob:=row_index.job;
 19        er.v_esal:=row_index.sal;
 20        er.v_deptno:=row_index.deptno;
 21        dbms_output.put_line(v_index||'    '|| er.v_ename||'    '||er.v_ejob||'    '||er.v_esal||'    '||er.v_deptno);
 22  			end loop;
 23      exception
 24  		when others then
 25  		dbms_output.put_line(sqlcode||' : '||sqlerrm);
 26   end;
 27  /
 
序号    姓名    职务    薪资    部门编号
1    ALLEN    SALESMAN    1600    30
2    WARD    SALESMAN    1250    30
3    JONES    MANAGER    2975    20
4    MARTIN    SALESMAN    1250    30
5    BLAKE    MANAGER    2850    30
6    CLARK    MANAGER    2450    10
7    SCOTT    ANALYST    3000    20
8    KING    PRESIDENT    5000    10
9    TURNER    SALESMAN    1500    30
10    ADAMS    CLERK    1100    20
11    FORD    ANALYST    3000    20
12    MILLER    CLERK    1300    10
 
PL/SQL procedure successfully completed
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43

综合示例:

create or replace procedure TABLE_TEST
(
  v_in_beginTime varchar2
)
 is
  lv_index number:=0;    --声明变量
  lv_temp  emp.ename % type;
  lv_sum   number:=0;

  CURSOR cur_name(v_time varchar2) is
    select ename from EMP t where  to_date(to_char(t.hiredate,'YYYY-MM-DD'),'YYYY-MM-DD') >to_date(v_time,'YYYY-MM-DD')  ; --声明游标
  TYPE table_Name is table of emp.ename % TYPE INDEX by BINARY_INTEGER;   --声明集合类型
  var_names table_Name;     --声明集合类型变量

  cursor cur_table (v_name varchar2) is
    select t.sal,t.deptno from emp t where t.ename=v_name;
  c1  cur_table % rowtype;

begin
  --if not cur_name%ISOPEN then --如果游标没有打开,则打开游标
    --OPEN cur_name;
    --end if;
  if  v_in_beginTime is null then
			dbms_output.put_line('时间参数为空!');
      return;
  end if;
  open  cur_name(v_in_beginTime) ;
  if not cur_name%ISOPEN then
		dbms_output.put_line('游标打开失败!');
    return;
  end if;

  loop
		fetch cur_name into lv_temp;
		exit  when cur_name % notfound;
		lv_index:=lv_index+1;
    var_names(lv_index):=lv_temp;
    dbms_output.put_line('names ['||lv_index||'] :'||var_names(lv_index));
  end loop;
  close cur_name;

  if  var_names.count >0 then
	  for i in 1..var_names.count loop
		  open cur_table(var_names(i));
        fetch cur_table into c1;
        exit when cur_table %notfound;
        lv_sum:=lv_sum+c1.sal;
      close cur_table;
	  end loop;
  dbms_output.put_line('总工资:'||lv_sum);
  end if;

   exception
 		when others then
		dbms_output.put_line(sqlcode||' : '||sqlerrm);

end;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57

在这里插入图片描述

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/寸_铁/article/detail/793008
推荐阅读
相关标签
  

闽ICP备14008679号