举例如何使用bulk collect into , extend(), multiset union,dbms_metadata.get ddl？


----BULK COLLECT INTO
DECLARE
  TYPE REC_NORMAL IS RECORD(
    COLUMN_NAME     USER_IND_COLUMNS.COLUMN_NAME%TYPE,
    COLUMN_POSITION USER_IND_COLUMNS.COLUMN_POSITION%TYPE);
  TYPE NNT_NORMAL IS TABLE OF REC_NORMAL;
     L_NNT_NORMAL1 NNT_NORMAL := NNT_NORMAL();
 
      SELECT UIC.COLUMN_NAME, UIC.COLUMN_POSITION
      BULK COLLECT
      INTO L_NNT_NORMAL1
      FROM USER_IND_COLUMNS UIC
     WHERE UIC.INDEX_NAME = 'XIE1MV_STYLE'
      AND NOT EXISTS (SELECT 1
              FROM USER_IND_EXPRESSIONS UIE
             WHERE UIC.INDEX_NAME = UIE.INDEX_NAME
               AND UIC.TABLE_NAME = UIE.TABLE_NAME);
           DBMS_OUTPUT.PUT_LINE(L_NNT_NORMAL1.COUNT);
     END;
     /



-------EXTEND()
DECLARE
TYPE NNT_VAR1 IS TABLE OF NUMBER;
L_NTT_VAR1 NNT_VAR1:=NNT_VAR1();
BEGIN
---DBMS_OUTPUT.put_line( L_NTT_VAR1.COUNT);
L_NTT_VAR1.EXTEND();
L_NTT_VAR1(1):=(888);
L_NTT_VAR1.EXTEND(3);
L_NTT_VAR1.EXTEND(3,1);
DBMS_OUTPUT.put_line( L_NTT_VAR1.COUNT);
DBMS_OUTPUT.put_line( L_NTT_VAR1.LAST);
FOR I IN 1..L_NTT_VAR1.COUNT
  LOOP
    DBMS_OUTPUT.PUT_LINE(I||':' ||TO_CHAR(L_NTT_VAR1(I)));
  END LOOP I;
END;
/
------如果不加EXTEND() 会有什么现象？
ORA-06532: Subscript outside of limit
ORA-06512: at line 68


-----MULTISET UNION
DECLARE
   TYPE   NNT_VAR1 IS TABLE OF NUMBER;
   NNT1   NNT_VAR1 := NNT_VAR1(21,31,78);
   NNT2   NNT_VAR1 := NNT_VAR1(10,20,30,40);
   NNT3   NNT_VAR1;
BEGIN
   NNT3 := NNT1 MULTISET UNION NNT2;
   DBMS_OUTPUT.PUT_LINE(NNT3.COUNT);
END;
/

DECLARE
   TYPE   NNT_VAR1 IS TABLE OF NUMBER;
   NNT1   NNT_VAR1 := NNT_VAR1(20,31);
   NNT2   NNT_VAR1 := NNT_VAR1(10,20,30,40);
   NNT3   NNT_VAR1;
BEGIN
   NNT3 := NNT1 MULTISET UNION DISTINCT NNT2;
   DBMS_OUTPUT.PUT_LINE(NNT3.COUNT);
END;
/

DECLARE
   TYPE   NNT_VAR1 IS TABLE OF NUMBER;
   NNT1   NNT_VAR1 := NNT_VAR1();
   NNT2   NNT_VAR1 := NNT_VAR1();
   NNT3   NNT_VAR1;
BEGIN
   NNT3 := NNT1 MULTISET UNION NNT2;
   DBMS_OUTPUT.PUT_LINE(NNT3.COUNT);
END;
/


FUNCTION get_ddl ( object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform. IN VARCHAR2 DEFAULT 'DDL') RETURN CLOB;

----dbms_metadata包中的get_ddl

----1.表或索引的ddl

SELECT DBMS_METADATA.GET_DDL('TABLE','MV_STYLE','POSUSER') FROM DUAL;
select dbms_metadata.get_ddl('INDEX','XIE1MV_STYLE','POSUSER') from dual;

----2.所有表，索引，存储过程的DDL

SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name)
FROM USER_OBJECTS u
where U.OBJECT_TYPE IN ('TABLE','INDEX','PROCEDURE');
 
------3.所有表空间的DDL

SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name)
FROM DBA_TABLESPACES TS;

------4.创建用户的DDL

SELECT DBMS_METADATA.GET_DDL('USER',U.username)
FROM DBA_USERS U;