嵌套表

嵌套表是一种列类型，它以无特定顺序存储未指定数量的行。当一个嵌套表值从数据库检索到一个PL/SQL 嵌套表变量中时，PL/SQL会为这些行提供从 1 开始的连续索引。使用这些索引，可以访问嵌套表变量的各个行。语法是variable_name(index)。 支持的方法如下：

目前TDSQL PG不支持使用外部定义的嵌套表类型，只能使用select bulk collect into 示例： 嵌套表读取

drop table if exists emp;
CREATE TABLE EMP(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,ENAME VARCHAR2(20),JOB VARCHAR2(9),MGR NUMBER(4), HIREDATE DATE,SAL NUMBER(7,2),COMM NUMBER(7,2), DEPTNO NUMBER(4));
INSERT INTO EMP VALUES(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,200);
INSERT INTO EMP VALUES(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,300);
INSERT INTO EMP VALUES(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,300);
INSERT INTO EMP VALUES(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,200);
INSERT INTO EMP VALUES(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,300);
INSERT INTO EMP VALUES(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,300);
INSERT INTO EMP VALUES(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,100);
INSERT INTO EMP VALUES(7788,'SCOTT','ANALYST',7566,to_date('19-04-87','dd-mm-rr'),3000,NULL,200);
INSERT INTO EMP VALUES(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,100);
INSERT INTO EMP VALUES(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,300);
INSERT INTO EMP VALUES(7876,'ADAMS','CLERK',7788,to_date('23-05-87', 'dd-mm-rr'),1100,NULL,200);
INSERT INTO EMP VALUES(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,300);
INSERT INTO EMP VALUES(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,200);
INSERT INTO EMP VALUES(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,100);

drop table IF EXISTS dept;
create table dept (DEPTNO NUMBER(4), DNAME VARCHAR2(14), LOC VARCHAR2(13));
insert into dept VALUES(100, 'ACCOUNTING', 'NEW YORK');
insert into dept VALUES(200, 'RESEARCH', 'DALLAS');
insert into dept VALUES(300, 'SALES', 'CHICAGO');
insert into dept VALUES(300, 'SALES', 'NEW YORK');
insert into dept VALUES(200, 'ACCOUNTING', 'NEW YORK');
insert into dept VALUES(200, 'RESEARCH', 'CHICAGO');
insert into dept VALUES(100, 'ACCOUNTING', 'BOSTON');
insert into dept VALUES(400, 'OPERATIONS', 'BOSTON');

-- TestPoint：存储过程里使用动态数组的相关函数extend/first/count/last/exists/prior/limit/next
create or replace procedure nested_pro
as
declare 
    cursor emp_cursor is  select ename from emp;
    type emp_table is table of emp.ename%type;
    empTable emp_table:=emp_table();
    keyValue integer:=0;
begin
    empTable.extend;
    perform dbms_output.serveroutput('y');
    for tmp in emp_cursor 
    loop
        keyValue := keyValue+1;
        --EXTEND:追加1个空元素到集合
        -- bug,已经有一个空间了，还是报数据越界
        empTable.extend;
        empTable[keyValue] := tmp.ename;
    end loop;

    --FIRST：返回在使用整数下标集合的第一个（最小的）索引号
    keyValue := empTable.FIRST;

    raise notice '%',keyValue;
    raise notice '%',empTable.last;
    raise notice '%',empTable.count;

    WHILE keyValue IS NOT null 
    LOOP
        perform dbms_output.put_line('Emp name: ' || TO_CHAR(empTable[keyValue] || ' The emp index: ' || keyValue));
        --返回索引keyValue的下一个索引号
        keyValue := empTable.NEXT(keyValue);
    END LOOP;

    --LAST：返回在使用整数下标集合的最后一个（最大的）索引号
    -- bug,first=0
    keyValue:= empTable.LAST;
    WHILE keyValue IS NOT null 
    LOOP
        perform dbms_output.put_line('Emp name: ' || TO_CHAR(empTable[keyValue] || ' The emp index: ' || keyValue));
        --返回索引keyValue的上一个索引号
        keyValue := empTable.prior(keyValue);
    END LOOP;

    -- 打印最大值
    -- bug
    -- perform dbms_output.put_line('limit values:'||empTable.limit);
    raise notice 'limit values %', empTable.limit;

    -- 判断元素是否存在
    raise notice 'exists:%', empTable.exists(2);
end;
/
call nested_pro();