Oracle PL/SQL (4) - 索引表INDEX BY

作者: 乘风破浪的姐姐 | 来源:发表于2020-04-01 19:05 被阅读0次

Oracle PL/SQL (4) - 索引表INDEX BY
1- Oracle Select语句
PL/SQL
JAVA学习笔记（PL/SQL）
PL_SQL_Oracle基础教程.pdf
PL/SQL基础
《MySQL技术内幕：InnoDB存储引擎》第四章表
oracle工具PL/SQL快速入门1简介
学习SQL的第八天（11月8日）
pl/sql

Oracle PL/SQL语言中索引表相当于JAVA中的数组，可以保存多个数据，并通过下标来访问。不同的是，索引表的下标可以是整数也可以是负数或字符串，索引表无需初始化，可以直接为指定索引赋值，开辟的索引表的索引不一定必须连续。

1、索引表的定义语法

TYPE  类型名称 IS TABLE OF 数据类型 [NOT NULL]
INDEX BY [PLS_INTEGER | BINARY_INTEGER | VARCHAR2(长度)];
声明表变量：<表变量名> <表类型名>；
引用：<表变量名>(<索引变量>)；

例如：

TYPE info_index IS TABLE OF varchar2(20) INDEX BY BINARY_INTEGER; --定义info_index 索引表

IS TABLE OF 相当于是数组,这里定义了一个数组类型info_index ;
VARCHAR2(20) 定义数组里面只能放字符串
INDEX BY BINARY_INTEGER 定义数组下标是整数

DECLARE
    TYPE info_index IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
    v_info info_index;
BEGIN
    v_info(1):='AAA';
    v_info(12):='BBB';
    DBMS_OUTPUT.put_line(v_info(1)||CHR(10) ||v_info(12));
END;

输出结果：
AAA
BBB

2、定义type型的索引表
使用IS TABLE OF获取同一事故下所有定损单的定损单号、定损金额。

declare  
  v_acc_no  emb.claim.accno%type;     
  type type_claimno is table of emb.claim.claimno%type;  
  type type_amount is table of emb.claim.totalestimate%type;  
    
  v_claimno              type_claimno:=type_claimno();  
  v_claimamount     type_amount:=type_amount();  
begin  
  v_acc_no  :='claim01';
  select t.claimno,t.totalestimate
  bulk collect into v_claimno , v_claimamount     
  from claim t where t.accno= v_acc_no  ;
  
  /*输出定损单信息*/  
  for  v_index in v_claimno.first .. v_claimno.last loop  
      dbms_output.put_line('定损单号：'||v_claimno(v_index)||' 定损总金额：'||v_claimamount(v_index));  
  end loop;  
end;

输出结果：
定损单号：claim01定损总金额：73446
定损单号：claim01_01定损总金额：128327

3、定义rowtype 型的索引表
例如：使用IS TABLE OF获取所有公司信息。

declare  
       type company_table_type is table of emb.company%rowtype index by binary_integer;  
       var_company_table company_table_type;  
    begin  
      select *  
      bulk collect into var_company_table  
      from emb.company;  
        
      /*输出公司信息*/  
      for i in 1..var_company_table.COUNT loop  
          dbms_output.put_line('公司code：'||var_company_table(i).cp_code||' 公司名称：'||var_company_table(i).cp_name||' 公司等级：'||var_company_table(i).cp_level);  
      end loop;  
    end;

输出结果：
公司code：10001400 公司名称：总公司公司等级：1
公司code：205 公司名称：深圳分公司公司等级：2
公司code：333 公司名称：测试分公司公司等级：2

4、使用记录类型操作索引表

declare
     TYPE claim_type IS RECORD(
      v_claim_accidentno      emb.claim.acc_no%TYPE,
      v_claim_totalamount     emb.claim.estimate_amount%TYPE,
      v_claim_assigndate      emb.claim.assign_date%TYPE         
     );
      
     TYPE claim_index IS TABLE OF claim_type INDEX BY PLS_INTEGER;
     v_claim claim_index;
     
begin
     v_claim(0).v_claim_accidentno:= '1111111';
     v_claim(0).v_claim_totalamount := '111';
     v_claim(0).v_claim_assigndate :=to_date('2019-2-26 11:14:25' , 'yyyy-mm-dd hh24:mi:ss');

     if v_claim.exists(0) then
           DBMS_OUTPUT.put_line('事故号:'||v_claim(0).v_claim_accidentno||'   定损总金额：'||NVL(v_claim(0).v_claim_totalamount,2)||'  任务分配时间：'||TO_CHAR(v_claim(0).v_claim_assigndate,'yyyy-mm-dd'));   
     end if;
end;

输出结果：
事故号:1111111 定损总金额：111 任务分配时间：2019-02-26
使用记录类型操作索引表，输出某个下标的结果

declare
     TYPE company_type IS RECORD(
      v_cp_code      emb.company.cp_code%TYPE,
      v_cp_name     emb.company.cp_name%TYPE,
      v_cp_level      emb.company.cp_level%TYPE         
     );
      
     TYPE company_index IS TABLE OF company_type INDEX BY PLS_INTEGER;
     v_company company_index;
     
begin
       select cp_code,cp_name,cp_level  bulk collect into v_company  from emb.company;  
       if v_company.exists(1)then
              dbms_output.put_line('公司code：'||v_company(1).v_cp_code||' 公司名称：'||v_company(1).v_cp_name||' 公司等级：'||v_company(1).v_cp_level);  
       end if ;
 end;

输出结果：
公司code：10001 公司名称：总公司公司等级：1
使用记录类型操作索引表，输出所有下标结果

declare
     v_sql  varchar2(500);

     TYPE company_type IS RECORD(
      v_cp_code      emb.company.cp_code%TYPE,
      v_cp_name     emb.company.cp_name%TYPE,
      v_cp_level      emb.company.cp_level%TYPE         
     );
      
     TYPE company_index IS TABLE OF company_type INDEX BY PLS_INTEGER;
     v_company company_index;
     
begin
      v_sql:='select cp_code,cp_name,cp_level from emb.company';  
      execute immediate v_sql bulk collect into v_company ;
      for i in 1..v_company.count 
       loop
          dbms_output.put_line('公司code：'||v_company(i).v_cp_code||' 公司名称：'||v_company(i).v_cp_name||' 公司等级：'||v_company(i).v_cp_level);  
       end loop;
end;

输出结果：
公司code：10001 公司名称：总公司公司等级：1
公司code：333 公司名称：测试分公司公司等级：2

5、多级索引表

declare  
  type al_table_type is table of int  
  index by binary_integer;  

  type nal_table_type is table of al_table_type  
  index by binary_integer;  
  
  nvl nal_table_type;  
--初始化  
begin  
  nvl(1)(1):=10;  
  nvl(1)(2):=5;  
  nvl(2)(1):=100;  
  nvl(2)(2):=50;  
  
  dbms_output.put_line('显示二维索引表的所有元素：');  
  for i in 1..nvl.count loop  
    for j in 1..nvl(i).count loop  
        dbms_output.put_line('nvl('||i||','||j||')='||nvl(i)(j));  
    end loop;  
  end loop;  
end;

输出结果：
显示二维索引表的所有元素：
nvl(1,1)=10
nvl(1,2)=5
nvl(2,1)=100
nvl(2,2)=50