Oracle索引表、集合 [实例]

巢新苗

--索引表的用法
--使用binary_integer

declare
type ename_table_type is table of scott.emp.ename%type
      index by binary_integer;
type ehiredate_table_type is table of scott.emp.hiredate%type
       index by binary_integer;
ename_table ename_table_type;
ehiredate_table ehiredate_table_type;
begin
select ename,hiredate into ename_table(0),ehiredate_table(0) from scott.emp
where scott.emp.empno=7369;
dbms_output.put_line(ename_table(0));
dbms_output.put_line(to_char(ehiredate_table(0),'yyyy-mm-dd hh24:mi:ss'));
end;

-- 使用 varchar2 索引表时

DECLARE

TYPE area_table_type IS TABLE OF NUMBER INDEX BY varchar(20);
area_index_table area_table_type;
BEGIN

area_index_table('上海') :=1;
area_index_table('北京') :=2;
area_index_table('天津') :=3;
dbms_output.put_line('地点 : '|| area_index_table.first );
dbms_output.put_line('地点 : '|| area_index_table('北京') );
dbms_output.put_line('地点 : '|| area_index_table.last );
END;



--   使用 嵌套表
-- 索引表的类型不能作为表列的类型，而嵌套表的类型可以作为表列的类型
DECLARE
TYPE ename_table_type IS TABLE OF SCOTT.emp.ename%TYPE;
ename_nest_table ename_table_type;
BEGIN
ename_nest_table :=ename_table_type('dongguoh','hello','third','hello','third'); -- 要使用 嵌套表，必需先初始化,下标从 1 开始
SELECT ename INTO ename_nest_table(2) FROM SCOTT.emp WHERE empno=&no;
dbms_output.put_line('雇员名 : ' || ename_nest_table(2));
END;


-- 在表列中使用 嵌套表作为表中的一个成员，那么必需先创建这个被嵌套表的类型    ,p166

CREATE TYPE phone_type IS TABLE OF VARCHAR2(20);

CREATE TABLE employee (
id NUMBER (4),
name VARCHAR2(10),
sal NUMBER(6,2),
phone phone_type    -- 注意这句
) NESTED TABLE phone STORE AS phone_table;

--在 嵌套表中插入数据 如下
BEGIN
      INSERT INTO employee VALUES(1,'dongguoh2',1000,phone_type('0471-345678','34567976')); -- 注意这里
END;
   
   
   
    --在 嵌套表中 检索 数据 ,先定义变量接收数据 ，如下 P167

DECLARE
   phone_table phone_type ;
BEGIN
   SELECT phone INTO phone_table FROM employee WHERE id=1;
   For i IN 1..phone_table.COUNT LOOP
       dbms_output.put_line('电话号码为 : '|| phone_table(i));   
   END LOOP;
END ;


--更新嵌套表列的数据 ，定义 变量 ,并用构造方法初始化


DECLARE
   phone_table phone_type := phone_type('0872-567890','5678906789','5678-567895678','5678957895');
BEGIN
   phone_table(1) :='123456789';
   phone_table(2) :='987643';
   UPDATE employee SET phone=phone_table WHERE id=1;
END ;



-- 使用可变数组 VARRAY

DECLARE
   TYPE ename_table_type IS VARRAY(20) OF emp.ename%TYPE;
   ename_table ename_table_type :=ename_table_type('dong'); -- 必需初始化
BEGIN
   SELECT ename INTO ename_table(1) FROM emp WHERE empno=&no;
   dbms_output.put_line('名字为 : ' || ename_table(1));    -- 注意下标不要超出范围
END ;

-- 多维集合的应用 P169

DECLARE
   TYPE array1 IS VARRAY(20) OF INT ;
   TYPE array2 IS VARRAY(10) OF array1;
   
   towArray array2 :=array2(array1(100,101,102),
                            array1(201,202),
                            array1(301,302,303,304));
BEGIN
FOR i IN 1..towArray.COUNT LOOP
      FOR j IN 1..towArray(i).COUNT LOOP
          DBMS_OUTPUT.PUT_LINE('ARRAY( ' || i || ',' || j || ') = ' || towArray(i)(j));   -- 注意 towArray(i)(j) 的写法
      END LOOP;
END LOOP;

END;


SELECT * FROM emp;
-- PL/SQL 记录表

DECLARE
TYPE emp_table_type IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
emp_table emp_table_type;
CURSOR cur_emp_table(v_empno NUMBER) IS SELECT * FROM emp WHERE empno<v_empno;
v_incr INT :=1;
BEGIN
-- 用FOR 语句的时候 隐式
/*
FOR i_cur IN cur_emp_table(&no) LOOP
     emp_table(v_incr) :=i_cur;
     DBMS_OUTPUT.PUT_LINE('编号 :'|| emp_table(v_incr).empno || ' 名称 :' || emp_table(v_incr).ename);
     v_incr:=v_incr+1;     
END LOOP;
*/
-- 显示的 LOOP

/*
OPEN cur_emp_table(&no);
LOOP
       FETCH cur_emp_table INTO emp_table(v_incr);
       v_incr:=v_incr+1;     
       EXIT WHEN cur_emp_table%NOTFOUND;

END LOOP;
CLOSE cur_emp_table;
*/

OPEN cur_emp_table(&1);
    FETCH cur_emp_table INTO emp_table(v_incr);
     v_incr:=v_incr+1;
   
    while cur_emp_table%FOUND LOOP
       FETCH cur_emp_table INTO emp_table(v_incr);
       v_incr:=v_incr+1;      
    END LOOP;
CLOSE cur_emp_table;
DBMS_OUTPUT.PUT_LINE('emp_table.first:'|| emp_table.first || ' 名称 :' || emp_table.LAST || '   v_incr: ' ||   v_incr);
     
FOR i IN emp_table.first..emp_table.LAST LOOP -- 注意这里的范围，当你取出的数据为空的时候会报错
     DBMS_OUTPUT.PUT_LINE('编号 :'|| emp_table(i).empno || ' 名称 :' || emp_table(i).ename);
END LOOP;
END ;