oracle pl sql 嵌套表

先创建表：

CREATE TABLE TB_USER
(
    ID INTEGER PRIMARY KEY,
    USER_NAME VARCHAR2(20) NOT NULL,
    USER_AGE INTEGER NOT NULL
);

CREATE SEQUENCE SEQ_USER
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
CACHE 10;

CREATE OR REPLACE TRIGGER TR_USER BEFORE INSERT ON TB_USER FOR EACH ROW
BEGIN
    SELECT SEQ_USER.NEXTVAL INTO :NEW.ID FROM DUAL;
END;

插入一些数据。

创建嵌套表：

CREATE OR REPLACE PROCEDURE PR_TABLE(USER_ID IN NUMBER) AS
TYPE SP_NAME_TABLE IS TABLE OF TB_USER.USER_NAME%TYPE INDEX BY BINARY_INTEGER;
SP_TABLE1 SP_NAME_TABLE;
SP_TABLE2 SP_NAME_TABLE;
SP_TABLE3 SP_NAME_TABLE;
BEGIN
  SELECT USER_NAME INTO SP_TABLE1(-1) FROM TB_USER WHERE ID=USER_ID;
  DBMS_OUTPUT.PUT_LINE(SP_TABLE1(-1));
  
  SELECT USER_NAME INTO SP_TABLE2(0) FROM TB_USER WHERE ID=USER_ID;
  DBMS_OUTPUT.PUT_LINE(SP_TABLE2(0));
  
  SELECT USER_NAME INTO SP_TABLE3(1) FROM TB_USER WHERE ID=USER_ID;
  DBMS_OUTPUT.PUT_LINE(SP_TABLE3(1));
END;

执行：

CALL PR_TABLE(1);

创建嵌套表的SQL：

TYPE SP_NAME_TABLE IS TABLE OF TB_USER.USER_NAME%TYPE INDEX BY BINARY_INTEGER;

嵌套表的下标可以是负数。